Frage zu Performance

[mike2022]

Guten Tag allerseits und ein gesundes Jahr 2022 an alle !

Ich habe eine Frage zur Performance eines Sheets - Performance meint: welche Berechnungszeiten sind länger und kosten mehr Rechner-Power.

Excel 365, Windows 10P

Ich habe ein Sheet mit 125 Spalten und unbekannten Zeilen.
Aus den einzelnen Spalten werden diverse Auswertungen gezogen, teils einfache SUMME, aber auch SUMMEWENN(s) mit diversen Kennzahlen aus anderen Spalten.

Einziges Problem ist, dass die Anzahl der Zeilen dynamisch ist. Zum Jahresanfang sind es jetzt 489, aber es waren zum 31.12. auch schon ~412000.

Um die Formeln flexibel zu gestalten war die Lösung bisher, einfach von 250.000 Zeilen auszugehen (und erst wenn diese erreicht sind wurde das Sheet geteilt, also quasi eine Kopie erstellt und dort mit 250001 Zeile begonnen - und die Formeln entsprechend auf diese Zellenadressen dann fix einzugeben.

Ich habe das nun dynamisch geändert. Einmal mit dynamischen Bereich (Bereichsnamen vergeben). Funktioniert von den Formeln her (Berechnungen stimmen), aber es dauert gefühlt ewig (teilweise > 3Min) bis das Sheet bei 1 Änderung neu berechnet wurde.

Daraufhin habe ich die dyn. Bereiche verworfen und arbeite mit relativen Adressen

z.B. 
summewenn(indirekt(adresse(11;spalte()))indirekt(adresse($a$5;spalte();"Kriterium";indirekt(adresse(11;spalte()+12)):indirekt(adresse($a$5;spalte()+12)))

 .... $a$5 beinhaltet die letzte gefüllte Zeilennummer, 11 ist die erste Zeilennummer mit Daten ...

Funktioniert hinsichtlich Berechnung ebenfalls, aber auch hier ist die Zeitdauer der Berechnung sehr hoch, aber ca. 20% geringer als mit dyn. Bereichen.

Im Sheet selbst sind ca. 200 solcher diversen Auswertungen enthalten, die zahl ist aber nicht fix, kann also auch mehr werden.

Um eine einigermaßen vergleichbare Messung vornehmen zu können, habe ich das Sheet auf Onedrive gespeichert (lokal gehen die Berechnungen um gut 30% schneller, warum auch immer, da Berechnung immer im lokalen RAM stattfindet).

Meine Fragen:
welche Lösung würden die Profis bevorzugen, primär hinsichtlich der Performance bei Berechnungen ?
gibt es eventuell bei den dyn. Bereichen bessere Methoden als die von mir benutzte ?

ich definiere dyn. Bereiche wie folgt: am Beispiel Spalte H

SQ_MEASURE!$H$11:BEREICH.VERSCHIEBEN(SQ_MEASURE!$H$11;VERGLEICH(" ";SQ_MEASURE!$H:$H;-1)-5;0)

Danke für ein paar gute Tipps !

Mike

[RPP63]

Moin!
Egal ob INDIREKT() oder BEREICH.VERSCHIEBEN():
Beide sind volatil und werden grundsätzlich bei irgendeiner Änderung angestoßen!
Zwei Anmerkungen:
SUMMEWENN() müsste auch ohne Performance-Einbruch mit ganzen Spalten funktionieren.

Falls doch, ermittelt man einen dynamischen Bereichsnamen sehr viel besser mit INDEX()



Gruß Ralf

[mike2022]

danke für das Feedback.

Die Auswertungen sind schon sehr vielschitiger. Summe, Summewenn, Zählen, Mittelwert, Maximum, Statistikfunktionen etc. 

Mit INDEX habe ich es gerade an einem Beispiel versucht .... leider bin ich nicht ganz zufrieden, weil ich Dein Beispiel übernommen habe.

Bei mir müsste ich wohl für jede Spalte einen dyn. Bereich erstellen (mein_bereich_SpalteA, mein_Bereich_SpalteB ....). So jedenfalls hatte ich es mit meiner Syntax-

Summe(mein_bereich_SpalteA) bzw. Summewenn(mein_bereich_SpalteB;Kriterium;mein_Bereich_SpalteC) .....

In meinem Sheet laufen die Auswertungen imm auf eine Spalte bezogen, nur teilweise mit Kriterien aus anderen Spalten.

Habe in Deinem Beispiel einen mein_bereich über alle Spalten erstellt und dann mit einem Index aus dem Bereich mein_Bereich direkt die jew. Spalte angesprochen (ähnlich wie SVERWEIS,WVERWEIS).

Das war - wie ich vermute - wohl falsch.

Also konkrete Frage:
ich muss für jede Spalte einen eigenen dyn. Bereich erstellen ?
- oder -
gibt es eine Möglichkeit aus nur einem mein_Bereich über alle Spalten mittels eines Parameters direkt die gewünschten Spalten anzusprechen ?

[RPP63]

gibt es eine Möglichkeit aus nur einem mein_Bereich über alle Spalten mittels eines Parameters direkt die gewünschten Spalten anzusprechen ?

Selbstverständlich!
mein_Bereich ist ja momentan A2:E11
=SUMME(INDEX(mein_Bereich;;4))
ermittelt die Summe aus D2:D11

[Flotter Feger]

Strg-T - Tabelle ?

Da braucht es kein 'Bereiche' ...

[mike2022]

wunderbar  funktioniert.

Ich hatte die Syntax falsch benutzt und deshalb ist es auf einen Fehler gelaufen.

Die Performance ist schon sichtbar besser, allerdings immer noch deutlich langsamer als bei einem rein lokalen Dokument.

Aber das ist nicht so schlimm - die Index-Variante ist jedenfalls deutlich besser als die indirekt(adresse(... 
Danke nochmal.

[mike2022]

Hallo nochmal,

ich habe bei der Umstellung auf INDEX leider ein paar Probleme, die ich absolut nicht lösen kann.

Beigefügter Screenshot zeigt Originalsheet verkürzt.
In Zelle W6 steht eine Summenformel für Spalte G von Zeile 9 bis Zeile in Zelle W5 (also bis Zeile 22)

Darunter in W7 die entsprechende Summenformel mit Index.

Problem:
die Index-Summe addiert nur bis Zeile 15.
Füge ich in Zelle A23 ein neues Label hinzu (alle anderen Spalten leer), dann wird Zeile 16 zusätzlich addiert usw.
Die Summe hinkt also immer 3 Zeilen hinterher. Definiere ich testweise den dyn. Bereich nur auf 1 Spalte und schreibe dort die Zahlen rein, dann funktioniert es.

Dann noch 2 allgemeine Fragen:
bei "bedingte Formatierung" kann man sicherlich keinen dyn. Bereich angeben (weder via Index, noch Vergleich oder Indirekt. Gibt es da eine Möglichkeit trotzdem die
bedingte Formatierung ohne fix vorgegebene Zieladresen (im Bereich "Anwenden auf") hinzubekommen ?

Und zweite Frage:
Im Beispielsheet eine Summewenn-Funktion zu generieren in der Form

Summewenn(index(dyn_Bereich_2;;3);"FGGK";index(HQ_area_01;;3))
dyn_Bereich_2 = $AA$9:index($bc:$bc;anzahl2($aa:$aa))

Eigentlich müsste das ja funktionieren, ich bekomme aber immer BEZUG-Fehler, im Debugging verweist die Hilfefunktion auf eine leer Zelle in AB1.

Danke für ein paar hilfreiche Tipps !