Summe in verschachtelten Ranges

[ExcelDuck]

Guten Tag Zusammen,

ich habe ein "Problem". Mir gelingt es nicht eine schlankere Lösung für meinen Anwendungsfall zu finden. 

Ich habe einen spezifischen $ pro Tag Wert, welcher in bestimmen Datums Ranges variiert. Dazu gibt es ein Input Bereich (a5:c6). Falls der betrachtete Datumsabschnitt nicht durch einen dieser Werte abgedeckt wird, soll der Long Term Wert (c7) genommen werden. Ich habe es durch eine elend Lange pro Tag Abfrage + SUMIFS realisiert. Aber die Lösung ist unelegant und bläht die Datei unnötig auf. Mir geht es darum, dass ich die Werte von (L6:Y6) gern verschlankt hätte ohne mich auf F-Y beziehen zu müssen.  Die Formel möchte mich aber nicht gelingen bisher.

Ich habe die Excel Datei einmal angehängt. und freue mich über euren Input.

LG!

[HKindler]

Hi,

lege deinen Wertebereich anders an, so dass man mit den Verweis-Funktionen arbeiten kann.

Ich bin hingegangen und habe den Bereich A12:B16 verwendet. In Spalte A steht das Datum, ab dem der Wert in Spalte B gilt. Das sieht denn so aus:

Code:

.   A           B
11  Datum ab    Wert
12  01.01.23    17000
13  26.12.23    12500
14  02.01.24    17000
15  30.03.25    15000
16  02.01.26    17000

Die Formel für die Auswertung lautet dann

Code:

=SUMME(XVERWEIS(SEQUENZ(M5-L5;;L5);$A$12:$A$16;$B$12:$B$16;;-1;2))

[EA1950]

Hallo,

ich habe dir in der beiliegenden Datei in den Namensmanager die benutzerdefinierte Funktion IntervallSumme eingefügt. Wie sie verwendet wird, kannst an deinen grünen Zellen sehen, zB. in Zelle L6:=IntervallSumme(L5;M5;$A$5:$C$7)
Sie ist wie folgt aufgebaut:

Code:

IntervallSumme = LAMBDA(Von; Bis; VonBisWrt; LET(
    SpF; SEQUENZ(Bis-Von;1;Von);
    vonG;INDEX(VonBisWrt;1;1);
    bisG;INDEX(VonBisWrt;1;2);
    wrtG;INDEX(VonBisWrt;1;3);
    vonH;INDEX(VonBisWrt;2;1);
    bisH;INDEX(VonBisWrt;2;2);
    wrtH;INDEX(VonBisWrt;2;3);
    wrtI;INDEX(VonBisWrt;3;3);
    SpGHI; NACHZEILE(SpF; LAMBDA(dt; LET(
        wrtsg;(vonG<=dt)*(dt<bisG)*wrtG;
        wrtsh;WENN(wrtsg=0; (vonH<=dt)*(dt<bisH)*wrtH; wrtsg);
        WENN(wrtsh=0;WrtI;wrtsh)
    )));
    SUMME(SpGHI)
))

Dazu benötigst du keine Daten im Zellbereich F:I

[ExcelDuck]

Hallo Helmut,

tolle Lösung, mit der ich gut arbeiten kann !

Vielen Dank!

---

Hallo Anton, oh wow. Vielen Dank für die Mühe. 

Das werde ich mir jetzt mal genauer ansehen, das ist ja ziemlich clever gemacht. 

Vielen Dank, klappt wunderbar!

[Ego]

Hallo E...

noch eine Lösung auf Basis deiner Bereichslisten:

=LET(
X;SEQUENZ(M5-L5;1;L5;1);
Y;SUMMEWENNS($C$5:$C$6;$A$5:$A$6;"<="&X;$B$5:$B$6;">"&X);
SUMME(WENN(Y=0;$C$7;Y))
)

[oee]

  Excel Clever Forum Beispiel2.xlsx (Größe: 148,66 KB / Downloads: 4)