Office: (Office 365) SUMMENPRODUKT dynamisch aus bestimmten Tabellenblättern

Zu deiner geposteten Formel:
=LET(jahr;JAHR($A101);monat;MONAT($A101);datum;INDIREKT(jahr&"!$A$3:$A$368";FALSCH);wert;INDIREKT(jahr&"!$E$3:$E$368";FALSCH);filter;FILTER(wert;((MONAT(datum)=monat)*(JAHR(datum)=jahr)));WENNFEHLER(SUMME(FILTER(filter;ISTZAHL(filter)));""))
NIEMALS reservierte Wörter wie "jahr" oder "monat" usw als Variablennamen benutzen. Das kann böse enden, da jahr und monat Excel-Befehle sind.

 

Ok, danke für den Hinweis - hab schon selber drüber nachgedacht, aber weil es bei mir funktioniert hat, habe ich es erstmal so gelassen. Werde es aber abändern.

 

naja, Groß und klein kann Excel schon auseinanderhalten. Böse enden wirds nicht.

Aber es kann zu Verwechslungen führen. Es lohnt sich auf jeden Fall bei LET() Variablen über ein x wie xDatum xMinute ... nachzudenken. Und bei LAMBDA(), MAP() und co.Zählvariablen mit i,j ... wie in VBA gängig zu nutzen. Und vielleicht einfache Parameter mit pWasAuchImmer zu nutzen. Das hilft zumindest beim Debuggen. Das ist nur so eine Meinung/Vorschlag meinerseits.

Gruß Uwe

 

"naja, Groß und klein kann Excel schon auseinanderhalten" ???
Excel ist es egal, ob du im Eingabefeld z.B. "Filter" klein schreibst - Excel korrigiert das zu gross.
Teste doch mal das:
=monat(heute())

 

Vielen Dank für eure Hinweise, werde das so berücksichtigen zukünftig. Habe die Variablen jetzt abgeändert.

Eine Frage habe ich noch:

Code:

=WENNFEHLER(WENN(JAHR(A81)=JAHR(HEUTE());B93-SUMME('2025'!$E$3:INDEX('2025'!$E:$E;VERGLEICH(HEUTE();'2026'!$A:$A;0)));B93-B73);"")

=LET(vorjahr;A79-1;aktjahr;A79;WENNFEHLER(WENN(JAHR(A79)=JAHR(HEUTE());B93-SUMME(vorjahr&"!$E$3:INDEX(vorjahr&"!$E:$E";VERGLEICH(HEUTE();aktjahr&"!$A:$A";0))");B93-B73);""))

Mit der obersten Formel vergleiche ich den Vorjahreswert (zB. Strom bis 06.01.2025) mit dem heutigen (06.01.2026) und bilde die Differenz. Das klappt auch wunderbar.

Ich wollte diese auch abändern, sodass diese dynamisch ist - mit der unteren LET Formel. Aber irgendwie scheitere ich an den Bezüge zu den einzelnen Tabellenblättern (wahrscheinlich die "" falsch gesetzt) - aber ich kriegs nicht hin. Hat jemand einen Rat?

 

bei mir korrigierts der Editor in =MONAT(HEUTE()). Die Korrektur passiert auch im Parameter Name_Wert. In der Variable Name nicht.
Deshalb kanns schon auseinanderhalten. Aber über ein xMonat o.ä. für Variablen kann man denke schon mal nachdenken.

Gruß Uwe

 

Poste die Datei mit Wunschergebnis.

 

Siehe Zellen B-F37

Von den Bezugsfehler in den einzelnen Jahrestabellen nicht abschrecken, das kommt weil ich Tabellen zum anonymisieren rausgenommen habe.

 

Und was genau soll mir die Datei jetzt sagen?
Wo ist deine Formel und wo die Wunschergebnisse ???

 

Die Ergebnisse sind in der Tabelle Monatsablesung in den Zellen B37 bis F37, mit der alten Formel.
Die Ergebnisse sind mit Stand heute auch richtig.

Meine "Wunschformel" mit LET ist in Post #35

 

Hallo,

Onur hat O365 und kann das noch ein ganzes Stückchen effizienter bauen. Da kann man statt mit Zellbereichen und Hstapeln dies mit Matrizen in Schleife bauen und braucht dann nicht die Index/Vergleich oder SummeWenn Krücke.

Mit O2024 ist der effizienteste Weg so (effizienter als Index/Vergleich):

Code:

=LET(
    xVor; B15:F15;
    xAkt; B34:F34;
    xJahr; JAHR(A22);
    xHeute; HEUTE();
    xJakt;'2026'!A2:A369;
    xSum; HSTAPELN(
        SUMMEWENN(xJakt;"<="&xHeute;'2025'!E2:E369);
        SUMMEWENN(xJakt;"<="&xHeute;'2025'!I2:I369);
        SUMMEWENN(xJakt;"<="&xHeute;'2025'!T2:T369);
        SUMMEWENN(xJakt;"<="&xHeute;'2025'!X2:X369);
        SUMMEWENN(xJakt;"<="&xHeute;'2025'!AB2:AB369));
    WENNFEHLER(WENN(xJahr = JAHR(xHeute);xAkt - xSum;xAkt - xVor);""))

Ich habe absichtlich dies eingerückt, damit du das dir mal in Ruhe durchgehen kannst und falls du Lust hast dies der Reihe nach mal zu in Ruhe zu debuggen.
Auch wenn du das wohl nicht willst habe ich in deine letzte Datei die LAMBDA zu Übezwecken mal der Sache angepasst und eingebaut. Eigentlich hätte man den Rest unten dran parametrisiert mitgeben können, wenn da nicht die Formel in Zeile 37 wäre.

Gruß Uwe

 

Danke für deine Antwort, aber ich habe mein Anliegen glaube nicht richtig oder ausführlich genug geschildert.

Momentan greife ich ja die Daten aus den einzelnen Jahresblättern über den Zellbezug '2025'!... ab.

Eigentlich möchte ich nur diesen Bezug "automatisieren", weil ich dann die ganze Sektion für 2026 in der Tabelle Monatsablesung kopiere, unter 2026 einfüge, die "Überschrift" in A20 in 2027 ändere und die Jahrestabelle von 2026 kopiere, in 2027 umbenennen und dann soll sich der Bezug halt automatisch ändern, angepasst auf das Jahr 2027 und das Vorjahr 2026. Ich möchte halt keine "starren" Bezüge wie '2025'!... oder '2026'!... mehr.

 

Hallo,
das kannst du doch machen. Alles was ich dir in diese Datei hinterlassen habe ist ohne $ also keine Fixierung von Zellbereichen.

Du kannst auch einfach die Blattnamen ändern. Diese Formeln im Blatt Monatsablesung passen sich einfach an.
Einzig was noch nicht vorgegeben war sind die Datumswerte im Blatt Monatsablesung in Spalte A und die Jahresüberschriften.
Dies per Sequenz aus passender Zelle in Blatt des Vorjahres auszugeben ist nun wirklich simpel. Und die Jahresüberschriften sind ebenso einfach zu dynamisieren.

Was das Einfügen und die damit bei falschem Vorgehen entstehenden Bezugsfehler anlangt kannst du nur selbst in deiner Vorgehensweis beeinflussen.
Richtige Vorgehensweise: Blockweise außerhalb der Spalten, welche Formeln enthalten Kopieren --> Werte einfügen --> Fertig. Und schon hast du saubere Daten.

In den Blättern Vorjahr und aktuelles Jahr kann man auch noch via Sequenz das komplette Jahr ausgeben lassen. Dann braucht es nur noch eine einzige Vorgabe (Blatt Vorgaben): 31.12. des Vorjahres. Alles andere ist dynamisch nebst Schaltjahr.
Hier noch die Datei mit dem restlichen zu dynamisierenden Kram.

Gruß Uwe

 

Hallo, danke für deine Antwort - aber ich glaube wir reden aneinander vorbei - bzw. Ich drücke mich wahrscheinlich missverständlich aus...

Ich möchte den Bezug '2025' auf das Tabellenblatt nicht jedes Mal händisch ändern, also das ich daraus 2026 oder 2027 machen muss, sondern er soll dynamisch sein, zB mit INDIREKT - und und der in Post 35 angedachten LET Funktion.

 

wieso, das brauchst du so wie ich es angelegt habe nicht.
Die Funktion Indirekt sollte man bei großen Datenmengen auf Grund der Volatilität dieser Funktion möglichst vermeiden oder besser erst gar nicht versuchen.

Gruß Uwe