Lange Excel-Formel vereinfachen?

Andreas Nie-Fröhlich · 26. April 2018

Hallo,

nach einiger Arbeit habe ich eine recht lange Excel-Formel gebaut. Sie funktioniert, und ich bin soweit zufrieden damit. Jetzt frage ich mich, ob es etwas bringt, wenn ich diese Formel vereinfache bzw. die Berechnungsschritte reduziere. Zwei Möglichkeiten fallen mir ein:

1. Die sich wiederholenden Bestandteile in eigene Spalten auslagern, damit diese dort nur einmal berechnet werden und dann in der Hauptformel nur jeweils eingefügt werden, statt sie jedesmal neu zu berechnen.

2. Die Formel in ein "Wenn/Dann"-Konstrukt umbauen, damit die Berechnung früher abgebrochen wird, statt sie komplett durchrechnen zu lassen.

Würde eine Umstellung einen Geschindigkeitsvorteil bringen? Oder ist es Excel egal, wie verschachtelt die Formel ist? Aktuell werden 1.500 Zeilen derart berechnet.

Infos zu der Tabelle:

Vereinfacht gesagt berechnet die Formel für eine Liste von Aufträgen pro Monat eines Jahres die abzurechenden Summen basierend auf einer Auftragstabelle, in der die Aufträge mit Beträgen, Anfangs- und Enddaten sowie Abrechnungszyklen (Monat, Quartal, Halbjahr, Jahr) aufgeführt sind.

Aufsplitten lässt sich die Formel in Blöcke:

Summe der Beträge eines Auftrags * (Ist Anfangsmonat schon erreicht?) * (Ist Endmonat schon erreicht?) * (Ist Abrechnungszyklus erreicht oder hat ein Auftrag mitten in seinem Zyklus begonnen?) * (Zyklusmodifikator - (Anzahl Anfangsmonate nach Zyklus) -(Anzahl Endmonate nach Zyklus))

Der Übersicht halber (wenn man davon überhaupt noch sprechen kann) habe ich die wiederkehrenden Bestandteile farbig markiert:

Die Bestandteile im Einzelnen:

Aktueller Monat (TabHelpAuftragsanzahl ist die Summe aller vorhandenen Aufträge):

REST(ZEILE(G3)-2;TabHelpAuftragsanzahl)

Abrechnungszyklus (Zyklusermittlung; z.B. 1 für Monat, 3 für Quartal, etc):

SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH)

Monatsbestimmung basierend auf Abrechnungszyklus):

REST(QUOTIENT(ZEILE(G3)-1;TabHelpAuftragsanzahl);12)

Zum Schluss noch die Formel im Klartext:

=SUMME(INDIREKT("Aufträge!D" & 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl)&":G"& 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl)))*(DATUM([@Jahr];REST(QUOTIENT(ZEILE(G2)-1;TabHelpAuftragsanzahl);12)+1;1)>=INDIREKT("Aufträge!N" & 3 +REST(ZEILE(G2)-2;TabHelpAuftragsanzahl)))*(DATUM([@Jahr];REST(QUOTIENT(ZEILE(G2)-1;TabHelpAuftragsanzahl);12)+1;1)<INDIREKT("Aufträge!O" & 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl)))*ODER(NICHT(REST(REST(QUOTIENT(ZEILE(G2)-2;TabHelpAuftragsanzahl);12)+(SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH));SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH))); DATUM([@Jahr];REST(QUOTIENT(ZEILE(G2)-1;TabHelpAuftragsanzahl)+1;12);1)=DATUM(JAHR(INDIREKT("Aufträge!N" & 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl)));MONAT(INDIREKT("Aufträge!N" & 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl)));1))*(SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH)-WENNFEHLER(REST(MONAT(INDIREKT("Aufträge!N" & 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl)))-1;SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH))*NICHT(QUOTIENT(DATEDIF(INDIREKT("Aufträge!N" & 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl));DATUM([@Jahr];REST(QUOTIENT(ZEILE(G2)-1;TabHelpAuftragsanzahl);12)+SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH);1);"m");SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH)));0)-WENNFEHLER((SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH)-REST(MONAT(INDIREKT("Aufträge!O" & 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl)));SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH)))*NICHT(QUOTIENT(DATEDIF(DATUM([@Jahr];REST(QUOTIENT(ZEILE(G2)-1;TabHelpAuftragsanzahl);12);1);INDIREKT("Aufträge!O" & 3 + REST(ZEILE(G2)-2;TabHelpAuftragsanzahl));"m");SVERWEIS([@Abrechnung];TabHelpAbrechnungen;2;FALSCH)));0))

Gruß
Andreas

Andreas Nie-Fröhlic

danke für den Link. Ich habe die Formel auf drei Spalten aufgeteilt, was aber bedingt etwas gebracht hat. Mehr hat es gebracht, die einzelnen Testspalten, die ich noch drin hatte, zu löschen.

INDIREKT benutze ich leider wie Kamelle auf 'nem Rosenmontagsumzug. Es kommt in fast 7.500 Zeilen vor. Allerdings weiß ich nicht, wie ich davon weg kommen kann.

Folgendes Szenario: ich habe Aufträge in einer ähnlichen Tabelle wie diese:

[table][tr][td]Auftrag[/td][td]Kunde[/td][td]Start[/td][td]Ende[/td][td]Monatsbetrag[/td][td]Abrechnung[/td][/tr][tr][td]00001[/td][td]Meier[/td][td]01.01.2015[/td][td][td]150,00 €[/td][td]monatlich[/td][/tr][tr][td]00002[/td][td]Müller[/td][td]01.05.2018[/td][td]30.11.2018[/td][td] 45,00 €[/td][td]quartalsweise[/td][/tr][tr][td]00003[/td][td]Huber[/td][td]01.01.2018[/td][td][td]100,00 €[/td][td]halbjährlich[/td][/tr][/table]

Um das Ganze z.B. in einer Pivot analysieren zu können, möchte ich wissen, welche Summe ich pro Kunde pro Monat berechnen muss. Also transformiere ich die obige Formel in dieses Format:

[table][tr][td]Monat[/td][td]Auftrag[/td][td]Betrag[/td][td]Formel für "Auftrag"[/td][/tr][tr][td]2018/01[/td][td]00001[/td][td]150,00 €[/td][td]=INDIREKT("Aufträge!A" & 2 + REST(ZEILE(B2)-1;ANZAHL2(Aufträge!A:A))[/td][/tr][tr][td]2018/01[/td][td]00002[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/01[/td][td]00003[/td][td]600,00 €[/td][td]...[/td][/tr][tr][td]2018/02[/td][td]00001[/td][td]150,00 €[/td][td]...[/td][/tr][tr][td]2018/02[/td][td]00002[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/02[/td][td]00003[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/03[/td][td]00001[/td][td]150,00 €[/td][td]...[/td][/tr][tr][td]2018/03[/td][td]00002[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/03[/td][td]00003[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/04[/td][td]00001[/td][td]150,00 €[/td][td]...[/td][/tr][tr][td]2018/04[/td][td]00002[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/04[/td][td]00003[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/05[/td][td]00001[/td][td]150,00 €[/td][td]...[/td][/tr][tr][td]2018/05[/td][td]00002[/td][td]90,00 €[/td][td]...[/td][/tr][tr][td]2018/05[/td][td]00003[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/06[/td][td]00001[/td][td]150,00 €[/td][td]...[/td][/tr][tr][td]2018/06[/td][td]00002[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/06[/td][td]00003[/td][td]0,00 €[/td][td]...[/td][/tr][tr][td]2018/07[/td][td]00001[/td][td]150,00 €[/td][td]...[/td][/tr][tr][td]2018/07[/td][td]00002[/td][td]135,00 €[/td][td]...[/td][/tr][tr][td]2018/07[/td][td]00003[/td][td]600,00 €[/td][td]...[/td][/tr][/table]

Die Formel geht immer wieder durch die Auftragstabelle und sucht sich die entsprechenden Werte Monat für Monat raus. In der Summen-Formel passiert natürlich noch ein bisschen mehr Magie als in der Auftragsformel, schließlich müssen die Summen korrekt von
Fall zu Fall gebildet werden.

Gibt es also eine Möglichkeit, wie man diese kompakte Auftragsauflistung irgendwie in ein Format überführen kann, welches Zwecks Analyse "pivotisiert" werden kann, ohne auf INDIREKT zurückgreifen zu müssen?

Andreas Killer

Worin liegt es eigentlich begründet, dass INDIREKT so ineffizient ist?

Hatte ich schon erwähnt:

Außerdem solltest Du "volatile" Funktionen wie INDIREKT vermeiden, die werden bei jeder Berechnung immer neu berechnet.

Andreas.

Andreas Nie-Fröhlic

Hi,

danke! INDEX statt INDIREKT. Da hätte ich auch drauf kommen können. Manchmal sieht man den Wald vor lauter Bäumen nicht. Ich hab das komplette Workbook auf INDEX umgestellt. Läuft.

Worin liegt es eigentlich begründet, dass INDIREKT so ineffizient ist? Liegt es daran, dass der Bezug von INDIREKT erst zur Laufzeit aufgelöst wird und somit keine Optimierungen im Hintergrund vorgenommen werden können?

Andreas Killer

Da sehe ich jetzt nicht das Problem, ich nehme mal Deine Daten und ändere mal die Autragsnummer zur besseren optischen Unterscheidung und formatiere das ganze als Tabelle "Daten", das macht die anderen Formeln leichter (da braucht man dann nicht rechnen
um die Überschriften zu überspringen und die Bereiche stimmen von alleine).

Mein 2tes Blatt sieht dann so aus:

Und als Formeln haben wir:

A2:  =REST(ZEILE(A1)-1;ANZAHL2(Daten[Auftrag]))

B2:  1.1.2018

B3:  =EDATUM(B2;WENN(A3=0;1;0))

C2:  =INDEX(Daten[Auftrag];A2+1)

D2:  =SUMMENPRODUKT((B2>=Daten[Start])*(ODER(B2<=Daten[Ende];Daten[Ende]=""))*(C2=Daten[Auftrag])*Daten[Monatsbetrag])

Runterziehen und fertig... naja fast.

Die Formel in D2 mit einem zusätzlichem Multiplikator zu versehen der dann schon mal "quartal und halbjährlich" nach dem Datum richtig summiert überlasse ich Dir, die "Magie" kannst Du aus Deiner Tabelle übernehmen.

Andreas.

Andreas Killer

Der Punkt 1. bringt was alles andere nicht. Schau Dir mal diesen Artikel an:

https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

Außerdem solltest Du "volatile" Funktionen wie INDIREKT vermeiden, die werden bei jeder Berechnung immer neu berechnet.

Andreas.

Hi,

willkommen im Windows Forum!

Lange Excel-Formel vereinfachen? - Similar Threads - Lange Excel Formel	Forum	Datum
EXCEL Formel EXCEL Formel: Moin MoinMeine TabelleDatum Stunden GESAMT Uhrzeit VON Uhrzeit BIS Tagstunden NachtstundenIch bräuchte eine Formel um die Tagstunden und die Nachtstunden anzuzeigen :D Ich bin irgendwie zu blöd...	Microsoft Office	16. November 2023
Excel Formel Excel Formel: Excel-FormelIch möchte diese sechs Bedingungen verwenden, um das Retourenergebnis basierend auf meinen Verkäufen zu beurteilenWasserfreiWasser abgeschnittenneue...	Microsoft Office	11. Juli 2022
Tabelleneingabe vereinfachen Tabelleneingabe vereinfachen: Gibt es eine Tastenkombination, mit der man in der letzten Zeile einer Tabelle am Ende einer Zeileneingabe mehrere Zelleninhalte wurden per Tastatur eingegeben eine Kopie dieser lfd. Zeile als...	Microsoft Office	21. Dezember 2020
Excel Formel Excel Formel: Ich versuche mit dem Befehl =ZÄHLENWENN(C3:N3;1)-ZÄHLENWENN(O3:Z3;1) ein Ergebnis zu erhalten. Allerdings erkennt Excel den hinteren, fett markierten Teil nicht mehr als Formel. Die Werte im...	Microsoft Office	12. April 2019
Excel Wenn Formel Excel Wenn Formel: Hi, kurze Frage: Ich würde gerne eine Formel in eine Tabelle einsetzen: Spalte A Ist-Stunden Spalte B Soll-Stunden Spalte C Die Plus- oder Minusstunden die daraus resultieren... Hilfe, es...	Microsoft Office	12. Februar 2019
Formel vereinfach / kürzen möglich!? Formel vereinfach / kürzen möglich!?: Hallo zusammen, ich habe hier eine ziemlich lange formel, die ich gerne vereinfachen würde, wenn möglich. es ist alles konstant bis auf den Bereich E3:E30 (alternativ auch über...	Microsoft Office	10. Januar 2017
Excel Formel Excel Formel: Hallo!! Ich habe da mal eine frage. Wie bekomme ich die Formel =Wenn(F4="T";20;"");oder(F4="N";20;"") zum laufen??? bin um jede Hilfe dankbar!! MFG Matthias	Microsoft Office	4. Januar 2017

Lange Excel-Formel vereinfachen?