01.01.2022, 20:26
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
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
