Excel Performance von Summenprodukt und Summewenn?
Ich hab da mal eine mal eine Frage und zwar habe ich eine Tabelle ca. 400-500 Zeilen lang (für 1. gesamtes Jahr), die mit Daten (Datum) und ein einer anderen Spalte mit Zahlen (gefahrene Kilometer) gefüllt werden, in einem anderen Tabellenblatt habe ich eine Statistik angelegt in der ich die aufsummierten Zahlen innerhalb eines Monats ausgebe, dazu habe ich 2 verschiedene Formeln probiert
=SUMMEWENNS(INDIREKT("'"&$A12&"'!$G:$G");INDIREKT("'"&$A12&"'!$a:$a");">="&DATUM($A12;SPALTE()-1;1);INDIREKT("'"&$A12&"'!$a:$a");"<="&DATUM($A12;SPALTE();0))
=SUMMENPRODUKT((INDIREKT("'"&$A4&"'!$a:$a")>=DATUM($A4;SPALTE()-1;1))*1;(INDIREKT("'"&$A4&"'!$a:$a")<=DATUM($A4;SPALTE();0))*1;INDIREKT("'"&$A4&"'!$G:$G"))
Im Klartest steht da drin, prüfe ob in der Spalte A ein Datum steht, das zwischen dem 1 und dem letzten des jeweilig ausgewählten Monats liegt in A4 steht einfach das jeweilige Jahr. und addiere dann in der gleiche Zeile alle Werte in der Spalte G zusammen.
Als Bild sieht das dann so aus:
Wenn ich nun eine neue Zeile anlege und das Datum oder den Kilometerstand eintrage, rechnet Excel (unten steht das der 8 Threads in Bearbeitung und wieviel % angeschlossen) ca.5sek bevor ich irgendwas weiteres tippen kann.
Kennt einer eine Formel die ggf. performanter ist. Die einzige Optimierung die mir noch einfällt, wäre nicht A:A und G:G zu schreiben sondern nur A5:A1000 zu nehmen, aber da ich die Gesamtlänge nicht so genau kenne, habe ich halt mit A:A gearbeitet, so fehlt mir sicher kein Wert.
Bedanke mich im Voraus für Eure Hilfe
LG Gipfelstürmer
4 Antworten
Hallo Gipfelstürmer,
ich würde die Funktion Teilergebnis unter Daten / Gliederung / Teilergebnis oder mein ExcelAddin dazu nehmen. Als Beispiel habe ich eine Tabelle erstellt und über Teilergebnis mit der Überschriftenzeile:
diese Liste erzeugen lassen:
Die Liste habe ich um die Spalte Anzahl, in der in jeder Zelle eine 1 steht, und um die Spalte Monat, in der in jeder Zeile neben dem Datum, mit der Excelfunktion Monat, aus der Zelle davor, der laufende Monat, errechnet. Die Funktion Teilergebnis ersetzt gnadenlos die Quelltabelle. Deshalb sollte diese vorher dupliziert und nur mit der Duplikatkattabelle, weiter gearbeitet werden. Mit der Funktion Teilergebnis wird die Liste entsprechend der Vorgabe erstellt. Der Solitär muss ruhig gestellt bleiben. Das Ergebnis auf dem zusammengeklappten Excelblatt ergibt dann genau das Ziel der Gruppe.
LG
Siegried
Unbedingt auf PIVOT umstellen.
Bei mir geht es in die Hunderttausend Zeilen und ich war zunächst auch mit SUMMEWENN(S) unterwegs. War nur noch Kaffee trinken weil Excel ewig rechnete.
soviele Zeilen werden es bei mir Gott sei dank, maximal 550 pro Jahr Tendenz eher fallend.
Schalte die automatische Neuberechnung aus und stell die Neuberechnung auf manuell.
Dann wird nicht nach jeder einzelnen Eingabe alles komplett neu berechnet, sondern erst dann, wenn du die F9-Taste drückst.
Übrigens: Hast du auch schon mal drüber nachgedacht, deine gesamte Auswertung nicht über Formeln, sondern mit einer Pivot-Tabelle zu machen?
Ehrlicher Weise nicht, mit Pivottabellen, hab ich mich bisher nur sehr rudimentär beschäftigt, daher kamen mir die gar nicht in den Sinn und die kann ich glaube ich nicht so schön strukturieren, ich hatte zumindest immer mit zu kämpfen gerade mit den Anpassen der Spalten, welche wo ist etc.
Ja das mit der Neuberechnung kannte ich, ich konnte mich bisher nur noch nie erinnern, dass die Berechnung überhaupt merkbar gewesen wäre, daher dachte ich, dass es vielleicht an einer ungünstigen Formel liegt.
Grundsätzlich kommt es auf den Aufbau Deiner Daten an. Es könnte sich die Funktion
- Summewenns oder
- der XVerweis
anbieten.
Vom Summenprodukt habe ich mich vor langer Zeit verabschiedet.
ein Datum steht, das zwischen dem 1 und dem letzten des jeweilig ausgewählten Monats liegt in
Dabei könnte es hilfreich sein in einer Hilfsspalte z.B. den Monat aus dem jeweiligen Datum zu separieren.
Den Xverweis müsste ich mal privat testen, da der mir auf Arbeit in Excel 2019 nicht zur Verfügung steht.