Microsoft Excel

Herbers Excel/VBA-Archiv

Dem Formelwahnsinn entkommen...

Betrifft: Dem Formelwahnsinn entkommen... von: cH_rI_sI
Geschrieben am: 15.10.2020 12:08:36

Guten Morgen!

Ich habe eine Arbeitsmappe, welche eigentlich nur aus Formeln besteht und daher nicht sehr performant ist (Berechnung dauert gerne mal 5 - 10 min.) . Daher möchte ich die Performance optimieren, bin aber nicht sicher, welcher Weg der Beste ist bzw. wie man das realisiert.

Ich habe hier schonmal die Frage gestellt, wie man die Formel

=$B2/MAX($B:$B) 
ersetzen kann und da hat mir Chris_L einen Vorschlag mit Power Query gemacht. Wie ich aber den Zeitbezug (von GJ Beginn bis GJ Ende) anhand der Konstanten (eigenes Blatt) mit Power Query realisieren kann, weiß ich leider nicht und hoffe daher auf Eure Unterstützung.
Ich habe hier echt keinen Tau wie ich das angehe...

Außerdem stellt sich für mich die Frage, ob Power Query der richtige Weg ist, aber soweit ich verstanden habe, kann man die Prozentsatzberechnung ausgehend von der Max-Menge nicht mit (Power-)Pivot lösen, daher hat Chris_L dies vermutlich mit Power Query gelöst. Aber ist Power Query gleich performant wie Power Pivot? Mir kommt die Datenaktualisierung länger vor als wenn man was mit Power Pivot macht...

Anbei die Beispieldatei:

https://www.herber.de/bbs/user/140888.xlsx

Vielen Dank schonmal für Eure Bemühungen!

Lg,

Chrisi

Betrifft: AW: Dem Formelwahnsinn entkommen...
von: Daniel
Geschrieben am: 15.10.2020 13:56:23

Hi
ein Ansatz zur Performance-Verbesserung ist, dass man sich überlegt, welche berechneten Werte innheralb einer Formel, die für eine ganze Spalte gilt, sind denn in allen Zeilen gleich.
die Berechnungen, die sich nicht verändern, sollte man eine Hilfszelle auslagern und sich in der Formel nur noch auf diese Hilfszelle beziehen.

in deinem Beispiel wäre das Max(B:B).
das ändert sich nicht.
also solltest du diese Berechnung in eine andere Zelle auslagern (z.B X1) und dann in der Formel nur noch: =$B2/$X$1 rechnen. Damit reduzierst du die Häufigkeit, mit der die Auswertung Max(B:B) durchgeführt werden muss.

der nächste Ansatz ist dein SummeWenns mit 2 Bedingungen, Lieferant und Zeitraum

dies könnte man jetzt dadurch beschleunigen, dass man
die Datenbasis nach einer dieser Bedingung sortiert (in deinem Fall der Zeitraum) und dann die Grenzzeilennummern für die benötigten Zeiträume in einer Hilfsspalte ermittelt.
dann kann man das SummeWenns dadurch entlasten, dass man den Zellbereich auf die benötigten Zeilen einschränkt (dabei hilft INDEX) und dann als Bedingung nur noch der Lieferant übrig bleibt.
somit hat man weniger Bedingugnen im SummeWenn und jedes SummeWenn muss nicht mehr jede Zeile der Tabelle durchgehen, sondern nur noch einen kleineren Teil.

das Formelwerk wird zwar etws komplizierter, aber man kann damit die Rechenlast reduzieren.

Gruß Daniel

Betrifft: AW: Dem Formelwahnsinn entkommen...
von: cH_rI_sI
Geschrieben am: 15.10.2020 14:17:40

Hallo Daniel,

vielen Dank für deinen Ratschlag.
Würdest Du also auf PowerQuery ganz verzichten? Ist für mich eh noch ungewohnt bzw. kompliziert. Außerdem kommt mir die Hintergrundabfrage (Aktualisierung) sehr lange vor - also auch nicht sehr performant...

Wann würde man dann Power Query einsetzen?
Power Pivot verwende ich schon häufiger - dies könnte man in Kombination mit Power Query nur nachrangig verwenden oder (d.h. Pivot als Datenbasis für Power Query geht ja nicht)?

Mir gefällt dein Ansatz gut, da ich mich mit Formeln ganz gut auskenne, aber Power Query noch ziemlich Neuland ist...
Was sagen denn die anderen dazu? Wie ist Eure Meinung?
Danke!

Lg

Betrifft: AW: Dem Formelwahnsinn entkommen...
von: cH_rI_sI
Geschrieben am: 15.10.2020 14:42:07

Eine Anmerkung noch zur Beispieldatei - ich habe in der richtigen Datei ca. 10 versch. Datenquellen (mit teilweise 100.000 bis 250.000 Zeilen), welche ich auswerte (SUMMEWENNS, ZÄHLENWENNS, ...) und dann jeweils auch noch einen Prozentsatz bilde (anhand der MAX-Formel). Die Prozentsätze wiederum verwende ich in einer Gesamtauswertung, wo ich auch noch eine Gewichtung einfließen lasse.

Es geht also nicht nur um eine Datenquelle...

Vielleicht ändert dies die Sichtweise, wie man das am besten auswertet (optimierte Formeln durch Hilfszeilen/-tabellen vs. Power Query).

Falls nun jemand Power Query als den richtigen Weg ansieht, wäre es nett, wenn mir jemand anhand der Beispieldatei zeigen könnte, wie dies zu realisieren ist.

Danke!

Betrifft: AW: Dem Formelwahnsinn entkommen...
von: Yal
Geschrieben am: 15.10.2020 15:08:35

summewenns, zählenwenns mit 2 Bedingungen,
10 Datenquellen mit bis zu 250k Datensätze,

das sind 2 Merkmale, die zeigen, dass Du Excel verlassen solltest und auf Datenbank umstellen solltest.
Access ist der Anfang, ab er schnell wird es Richtung MS SQL gehen (die Express Version ist fürs Ausprobieren kostenlos).

VG
Yal

Betrifft: AW: Dem Formelwahnsinn entkommen...
von: cH_rI_sI
Geschrieben am: 15.10.2020 17:47:21

Danke für die Antwort!







Ich dachte, dass man mittlerweile Access durch Excel unter Verwendung von Power Pivot & Query ersetzen kann. Access-Kurs hatte ich schonmal aber habe bis jetzt mit Excel weitergemacht, da ich mir hier zuhause fühle... Aber wenn es sein muss, werde ich mir Access wieder einmal anschauen müssen.







Noch andere Meinungen?

Betrifft: AW: Dem Formelwahnsinn entkommen...
von: cH_rI_sI
Geschrieben am: 16.10.2020 07:01:27

Guten Morgen!

Was ich auch noch fragen wollte... Wäre nicht z.B. Power BI eine Alternative zu Access?
Aber das wiederum kann man ja mit Power Query vergleichen oder? Vermutlich also doch der Schwenk zu Access notwendig... Seht ihr das auch so?

Lg

Betrifft: ein grundsätzlicher Tipp...
von: Oberschlumpf
Geschrieben am: 16.10.2020 09:44:27

Hi Chrisi,

hab mir mal alle Beiträge durchgelesen.
Und dabei stellte ich fest, dass du mit fast jeder deiner Antworten eine weitere Frage hattest.
So kommen in diesem einem Thread also so ca 5-6 Fragen vor, auf die du gern eine Antwort hättest.

Klar, kann ich verstehen....aber...

Ich würde dir vorschlagen, in - jedem einzelnen - Thread auch immer nur eine Frage zu stellen.
Wenn diese beantwortet ist, erstellst du einen neuen Thread mit der nächsten Frage...usw.

Und außerdem schlage ich vor, vor dem Absenden noch mal alles Korrektur zu lesen, denn zu viele Tippfehler machen das Lesen nicht einfacher.

Machen kannst du natürlich, was du willst. All das hier sind nur Vorschläge von mir.

Ciao
Thorsten

Betrifft: AW: ein grundsätzlicher Tipp...
von: cH_rI_sI
Geschrieben am: 19.10.2020 08:51:33

closed