Wie bekomme ich diese Excel-Funktion schneller?

...komplette Frage anzeigen

4 Antworten

Hast Du über die Option einer Pivottabelle schon nachgedacht? Die fachliche Logik in der Formel hab ich noch nicht ganz durchdrungen. Aber Filter auf Monat/Jahr sollte gehen, ggf. braucht es für die anderen Kriterien eine Hilfsspalte in der Datenquelle: Da hinterlegst Du die Prüfungen, so dass WAHR oder FALSCH in der Hilfsspalte steht. Die kannst Du in der Pivottabelle als Filterkriterium nutzen

Nur eben beim googeln aufgefallen, aber noch nicht selbst überprüft: Anscheinend ist summewenns performanter als summenprodukt. Die Datumsabfrage in dieser Form wüsste ich aber nicht mit summewenns zu lösen.

DH!

Soweit ich das sehe, wird in A3 und B3 Jahr und Monat angegeben, für das das Summenprodukt berechnet werden soll. Mit Summewenns könnte man das etwa so machen:

=SUMMEWENNS(Summenbereich; Datumsspalte; ">="& DATUM(A3;B3;1); Datumsspalte; "<="& MONATSENDE(DATUM(A3;B3;1);0))

Damit grenzt man den Kriterienbereich mit Anfangs- und Enddatum eines Monats ein.

2
@DeeDee07

das ist eine clevere Lösung, auf so was hatte ich gehofft. Denn summewenns scheint schon deutlich schneller zu sein als summenprodukt.

Ich habe das nur mal rustikal getestet mit Stoppuhr, also keine Laboruntersuchung: 1 Mio Zeilen, 2 Spalten, also keine richtig komplexe Logik.
10 Formeln summenprodukt liefern nach ~5 s ein Ergebni
10 Formel summewenns nach 2,5

Pivot kann ich so nicht testen, da müsste mehr Logik abgebildet werden. Ist für Dich auch mehr Umbauarbeit Chris, wenn Du bisher alles per Formel hast.

2
@Ninombre

hab grad Richtung Feierabend mal getestet, wie lange es so dauert, wenn ich die weiteren Auswertungen mit der üblichen Formel mache... 35 min und läuft noch :-)

Ich fahr jetzt runter und teste am Montag mal die Varianten von euch. 2,5 zu 5 scheint erst mal nicht besonders viel, aber ich glaube, das exponansiert sich...

Und ja... um Pivot habe ich mich bis jetzt immer gedrückt... aber vielleicht auch mal ansehenswert.

Ich geb am Mo. mal ne Rückmeldung. Danke schon mal für eure Mühen.

1
@Funfroc

Moinsen, endlich geschafft.

Das Datum so über die Summewenn abzufragen bin ich einfach ums verrecken nicht drauf gekommen. Hätte aber auch keinen derart krassen Performance-Unterschied erwartet.

Endergebnis: Meine fertige Datei, die in 6 Tabellenblättern die Auswertungen unter Berücksichtigung diverser Filter für Mitarbeiter, Teams, Abteilungen und Gesamt macht braucht beim Laden jetzt ca. 20 Sekunden.

Vielen Dank DeeDee. Wenn du mir noch eine eigene Antwort reinstellst, geb ich dir gern auch noch ne hilfreichste.

LG, Chris

1
@Funfroc

Danke für das Feedback und die Anerkennung. Wenn hier die für dich hilfreichste Antwort steckt, zeichne sie aus. Mir geht es nicht um Punkte oder neudeutsch "fame", zumal hier nicht nur ich beigetragen habe. Ninombre gibt so viele gute Antworten, da ist das ok.

0

Du könntest mal versuchen das Semikolon durch ein Mal zu ersetzen, ich glaube aber eher nicht, dass das was bringt für die Performance.
Sonst fällt mir höchstens noch ein, das Ganze über eine Hilfsspalte zu lösen, in der Du schon mal das Produkt der Wahrheitswerte zusammenfasst und dann nur noch diese Hilfsspalte und die Werte mit SUMMENPRODUKT zusammenfasst. Aber auch da bin ich nicht sicher, ob das was bringt.

Habe mich jetzt für die Variante von DeeDee entschieden, da ich so ohne Hilfsspalten arbeiten kann.

Danke trotzdem für den Einsatz deines Hirnschmalzes. :-)

1

So wie ich das einschätze, sind die beiden Datumsfunktionen die jenigen, die das ganze so verzögern. Mach mal zwei Hilfsspalten, in denen Du Jahr und Monat separat rausziehst oder beziehe auch den Tag noch mit ein (ohne Matrixformel),

=JAHR('Gesamtliste'!$H2)=$A3)  und

=MONAT('Gesamtliste'!$H2)=$B3 

Du weisst ja, Formelzelle auswählen, Doppelklick aufs Ausfüllkästchen: Die Formel wird bis zum Ende der Einträge kopiert.

Die Formel kann ruhig so stehenbleiben, Smmenprodukt kannst Du dann auf die Spalten vbeziehen, von denen nur ihre Werte gerechnet werden.

Ich nehme an, dass dieses Herausziehen aus der Matrix die Werte für Summenprodukt schneller verarbeitbar macht.

Datumsfunktionen sind eine riesige Bremse in Matrixformeln, das sie zig Unterschleifen und Verweise ausführen müssen

Datumsfunktionen sind eine riesige Bremse

Das gilt nicht für zB =Rest(Tageszahl;7), da sind keine Umwege nötig. (Auch Rest(Datum;7) dürfte noch wenig belastet sein, da reicht vermutlich ein Verweis von Datum auf die Zahl in einer Liste mit intern schätzungsweise 80.000 Zeilen und zwei Spalten, und das dementsprechend direkt programmiert.

(da hat sich mal herausgestellt, dass sowas von Intel in der Hardware programmiert geliefert wurde, um Windows konkurrenzlos schnell zu machen, was MS wider besseres Wissen vorher bestritten hatte - und das mit Chips von vor 20 Jahren!).


0
@Iamiam

oder beziehe auch den Tag noch mit ein (ohne Matrixformel)

das war falsch, brauchst du bei Verrechnung von Jahr und Monat ja gar nicht

0
@Iamiam

Habe mich jetzt für die Variante von DeeDee entschieden, da ich so ohne Hilfsspalten arbeiten kann.

Danke trotzdem für deine Antwort... und ich hab mal wieder ein paar Sachen mehr im Kopf, die meine Freundin immer so böse als "unnützes Wissen" bezeichnet. ^__^

0
@Funfroc

wohingegen die Affären einiger Stars und in welche Kleider sie sich gerade notdürftig einhüllen etc. ein sehr nützliches Wissen zu sein scheinen? ;)

Ich frag mich ja manchmal auch sowas, aber für mich bildet das den Kreuzworträtsel-Effekt: Die Macher wissen längst, was rauskommt, aber ich will eben dahinterkommen...

0

Das schreit nach einer Datenbanklösung!

Günter

Was möchtest Du wissen?