INDEX Match zwischen 2 Daten

[ExcelDuck]

Guten Tag!
Ich bin neu hier und komme mit der Hoffnung auf Hilfe.

Ich möchte folgendes Scenario abbilden:

Gegeben sind Raten die zwischen verschiedenen Datumsgrenzen gelten und eine Standardrate:

zB
01-01-2023  -  05-03-2023  =  20.000USD
05-05-2023  -  20-06-2023  =  23.000USD

Standardrate: 17.000USD

Über eine Index Formel wollte ich diesen Betrag gern anderen Datumsgrenzen zu ordnen (welche variieren können):

zB 
05-01-2023 - 05-04-2023
= 20.000 vom 01.01. bis 05.03.
JETZT kommts:
Vom 05.03. bis zum 05.04. soll er entweder gucken, ob noch andere Raten (s.o.) vorhanden sind, wenn nicht -> Angenommene Standardrate verwenden.

Sprich
05.01. bis 05.03. = 20.000USD  (59tage)
05.03. bis 05.04. = 17.000USD  (31tage)

=durchschnittsrate für den zeitraum:  18.966,6667USD

Ausgabe soll sein:
05-01-2023 - 05-04-2023
18.966,67 USD

Habt ihr eine Idee wie ich das umsetzen könnte? 

Ich dachte schon daran alle Tage in einem Extrablatt einzeln per INDEX MATCH zu befüllen und dann Durchschnitte zu bilden über gewisse Zeiträume oder degleichen ..

Ich würde ich sehr über Eure Ideen freuen !!

LG ExcelDuck

[maninweb]

Hallo,

anbei mein Vorschlag. Durchaus möglich, dass es vielleicht an der einen oder anderen Stelle einfacher geht.

PHP-Code:

in C2 steht 17000
in A4 steht 05.01.2023
in B4 steht 05.04.2023

in E4:G5 stehen die Basiswerte, also E4 = 01.01.2023, F4 = 05.03.2023, G4 = 20000 usw. 


Dann kannst Du folgende Formel in C4 verwenden ...

PHP-Code:

C4=LET(vnStart; A4;
       vnEnde; B4;
       vnBasis; E4:G5;
       vnBasisStandard; C2;
       vnZeitreihe; SEQUENZ(vnEnde - vnStart + 1; 1; vnStart);
       vnZeitreiheBasis; SEQUENZ(MAX(INDEX(vnBasis; 0; 2)) - MIN(INDEX(vnBasis; 0; 1)) + 1; 1; MIN(INDEX(vnBasis; 0; 1)));
       vnIndex; SCAN(0; vnZeitreiheBasis; LAMBDA(A;V; SUMME((V > INDEX(vnBasis; 0; 1) - 1) * (V < INDEX(vnBasis; 0; 2) + 1)))) * 
                XVERGLEICH(vnZeitreiheBasis; INDEX(vnBasis; 0; 1); -1);
       vnMatrix; MATRIXERSTELLEN(ZEILEN(vnIndex); 4; LAMBDA(X;Y; LET(N; INDEX(vnIndex; X; 1);
                 WAHL(Y; INDEX(vnZeitreiheBasis; X; 1); N; WENN(N > 0; INDEX(INDEX(vnBasis; 0; 3); N; 1); vnBasisStandard); 
                 WENNFEHLER(VERGLEICH(INDEX(vnZeitreiheBasis; X; 1); vnZeitreihe; 0); 0)))));
       vnFilter; FILTER(vnMatrix; INDEX(vnMatrix; 0; 4) > 0; 0);
       vnErgebnis; SUMME(INDEX(vnFilter; 0; 3)) / ZEILEN(vnFilter); vnErgebnis) 


Kurze Erläuterung: aus den Datumsbereichen wird jeweils eine Zeitreihe gebildet, also auf jeden einzelnen Tag expandiert. Danach wird für die
Zeitreihe der Basis pro einzelner Tag geschaut, ob der Tag in den Basisdaten enthalten ist und ein Index gebildet. Anschließend wird eine Matrix
gebildet, wo dann auch die Tage des gesuchten Zeitraums in der Basis-Zeitreihe gesucht werden. Parallel dazu werden die USD-Werte übernommen.
Schließlich wird die Matrix gefiltert, sodass nur der gesuchte Zeitraum übrig bleibt. Der Rest ist dann selbsterklärend.

Bei mir kommt allerdings als Durchschnittswert 18.978,02 raus, denn es sind 60 Tage à 20K statt 59. Anbei auch die Datei.

  Raten zwischen 2 Daten.xlsx (Größe: 16,63 KB / Downloads: 12)

[ExcelDuck]

Genial .. Meine nächste Aufgabe liegt darin, dass zu verstehen! Das muss ich erstmal in meinen Kopf hinein bekommen. Die LET, SEQUENCE, LAMBDA, XMATCH und FILTER Funktionen habe ich noch nie genutzt. 

Vielen Dank für Deine Mühe!!  

[ExcelDuck]

Ich glaube ein Sachverhalt , der jetzt noch nicht funktioniert ist wenn "vnStart; A4; vnEnde; B4" vor dem Bereich der ersten manuellen Rate liegt.

zB

Erste Rate:
01/01/2023
05/03/2023
20000

15/12/2022
02/01/2023
Ergebnis: 20000,00 

vom 15/12/22 bis zum 01/01 müsste also die Standardrate angenommen werden. Wenn der Bereich komplett vor der ersten Rate liegt erscheint ein Fehler, dieser wäre (vielleicht unelegant) mir IFERROR zu lösen denke ich.

[maninweb]

Moin,

das stimmt, diese Fälle wurden nicht abgefangen. Anbei eine angepasste Formel, die die Standardrate ausserhalb der Datumsgrenzen vom Basisbereich annimmt ...

PHP-Code:

C4=LET(vnStart; A4; 
       vnEnde; B4; 
       vnBasis; E4:G5; 
       vnBasisStandard; C2; 
       vnZeitreihe; SEQUENZ(vnEnde - vnStart + 1; 1; vnStart); 
       vnZeitreiheBasis; SEQUENZ(MAX(INDEX(vnBasis; 0; 2); vnEnde) - MIN(INDEX(vnBasis; 0; 1); vnStart) + 1; 1; MIN(INDEX(vnBasis; 0; 1); vnStart)); 
       vnIndex; SCAN(0; vnZeitreiheBasis; LAMBDA(A;V; SUMME((V > INDEX(vnBasis; 0; 1) - 1) * (V < INDEX(vnBasis; 0; 2) + 1)))) * 
                WENNFEHLER(XVERGLEICH(vnZeitreiheBasis; INDEX(vnBasis; 0; 1); -1); 0); 
       vnMatrix; MATRIXERSTELLEN(ZEILEN(vnIndex); 4; LAMBDA(X;Y; LET(N; INDEX(vnIndex; X; 1); 
                 WAHL(Y; INDEX(vnZeitreiheBasis; X; 1); N; WENN(N > 0; INDEX(INDEX(vnBasis; 0; 3); N; 1); vnBasisStandard); 
                 WENNFEHLER(VERGLEICH(INDEX(vnZeitreiheBasis; X; 1); vnZeitreihe; 0); 0))))); 
       vnFilter; FILTER(vnMatrix; INDEX(vnMatrix; 0; 4) > 0; 0); 
       vnErgebnis; SUMME(INDEX(vnFilter; 0; 3)) / ZEILEN(vnFilter); vnErgebnis) 


Gruß

[Luschi]

Hallo ExcelDuck,

die Excel-365-Lösung von Mourad ist ja schon ein gewaltiges Werk von ineinander verschlungener Array-Funktionen. Bei einen kurzen Test im kostenlosen Excel-Web kamen die erwartbaren Ergebnisse heraus. Ich hoffe ja sehr, daß M$ es schafft, einen wesentlich verbesserten Advanced-Formel-Editor zu entwickeln, der in der Lage ist, ähnlich wie die F9-Taste Teilfunktions-Ergebnisse anzuzeigen, um die Prüfung solcher Monsterformeln zu vereinfachen.
Aus diesem Grunde bin ich das Problem mal mit Excel-Power-Query angegangen, muß aber auch zugeben, das die Lösung keine leichte PQ-Kost ist und der größte Teil der PQ-Schritte nicht mit den normalen PQ-Editor-Menü-Funktionen zu bewältigen ist.

Gruß von Luschi
aus klein-Paris

[neopa]

Hallo ExcelDuck,

Luschis Ergebnisse seiner eingestellten PQ-Lösung haben mich veranlaßt mich mit der Aufgabenstellung doch etwas näher zu befassen. Offensichtlich aber interpretierte Luschi Deine Zielstellung mE anders (zu einfach).

Aber bereits Deine Ergebnisvorgabe in #1 von 18.966,67 USD für Deine Daten stimmen nicht mit dem von maninweb ermittelten Ergebniswert überein. Deine weitere Angabe in #4, das für den 15.12.22 bis zum 2.01.23 als Ergebnis 20.000 ergeben sollen, verwirrten mich nun völlig. Ist/war das evtl. ein Schreibfehler, oder?

Ich "arbeite" ausschließlich mit älteren XL-Versionen. Excel-Web nutze ich nicht. Somit kann ich das Ergebnis der Formel von maninweb in seiner korrigierten Formel nicht mit Deiner Vorgabe vergleichen. Ich geh aber davon aus, daß diese wie das seiner erste Formel korrekt sein dürfte. Den Ergebniswert seiner 1. Formel konnte ich aus seiner eingestellten Datei entnehmen. Dieser entsprach ja dem von Dir erwarteten nun doch.

@ maninweb,

Du hattest geschrieben: " Durchaus möglich, dass es vielleicht an der einen oder anderen Stelle einfacher geht"
Nachfolgend hab ich mich versucht daran gehalten. Ich komme nachfolgend mit nur 3 Funktionen aus, die es schon in "Uralt-Excelversionen" gab. Davon habe ich zwei Funktionen in einer kleinen im Namensmanager definierten "benannten Formel" (in XL365 sicher auch einfach mit LET() realisierbar) für wiederholende Formelteile eingesetzt, damit ich die Formel etwas kürzer darstellen kann.

Nachfolgend hab ich meine Lösungsformel für das erste Datenbeispiel eingestellt, für das ich auf das gleiche Ergebnis komme wie Du mit Deiner ersten Formel. 

Für den Zeitraum 15.12.22 bis 2.1.23 ermittele ich mit dieser Formel als Ergebnis 17315,79. Was ermittelt Deine Formel?

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
2		Von	Bis	Wert	Ergebnis Ø:
3	Tage:	05.01.2023	05.04.2023	17000	18978,02
4
5	Rate1	01.01.2023	05.03.2023	20000
6	Rate2	05.05.2023	20.06.2023	23000
7

Name	Bezug
_Tage	=ZEILE(INDEX(!$A:$A;!$B$3):INDEX(!$A:$A;!$C$3))

Zelle	Formel
E3	=SUMMENPRODUKT((_Tage>=B$5)*(_Tage<=C5)*D5+(_Tage>=B$6)*(_Tage<=C6)*D6+((_Tage<B5)+(_Tage>C$5)*(_Tage<B6)+(_Tage>C6))*D3)/(C3-B3+1)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Bin gespannt aber trotzdem erst morgen wieder online.

[LCohen]

Die "Komplexität" entsteht erst, weil die Datumsbereiche nicht vorher excelgerecht gebildet werden. Das tun wir jetzt:

A1:C2: (nicht excelgerecht!)
01.01.2023 05.03.2023 20
05.05.2023 20.06.2023 23

F1:G5: (excelgerecht!)
00.01.1900 17
01.01.2023 20
06.03.2023 17
05.05.2023 23
21.06.2023 17
13.10.2173 00

Die Formel dafür
F1#: =LET(
x;A1:D2+{0.1.0.17};
y;SORTIEREN(VSTAPELN({0.17};SPALTENWAHL(x;{1.3});SPALTENWAHL(x;{2.4})));
y)
erklärt sich wie folgt:
1. Wir nehmen D1:D2 (leer) zu A1:C2 hinzu,
2. addieren 1 zum Bis-Datum als neues Von-Datum hinzu sowie 17 als Default-USD und
3. stapeln/sortieren die Spalten A;C und B;D zuzüglich einem Ausgangswert 17 bei Datum 0 und einem Enddatum 99999 mit Wert 0 hinzu.
(Letzteres dient zur Intervallbildung für nach dem 21.06.2023 liegende Endwerte)

Wie bei maninweb nun:
A4: Von-Datum
B4: Bis-Datum (exklusive)

Mit der Lupo1-Formel MAX(;MIN-MAX) holen wir uns die Einzelintervalle gewichtet summiert raus (= gewünschtes USD-Ergebnis):
C4: =LET(v;A4;b;B4;SUMME(
NACHZEILE(WEGLASSEN(F1#;-1;-1)+WEGLASSEN(F1#;1;-1)%%%;
LAMBDA(a;MAX(;MIN(KÜRZEN(REST(a;1)/1%%%);b)-MAX(KÜRZEN(a);v))))*WEGLASSEN(F1#;-1;1))/(b-v))

Wer es mag, schiebt beides - wie oben bei maninweb - noch zusammen, so dass die "excelgerechte Datumsstaffel" (s.o.) nicht mehr zu sehen ist. F1# wird also gelöscht und es ist nur noch das Ergebnis in C4 zu sehen.
C4: =LET(
v;A4;
b;B4;
x;A1:D2+{0.1.0.17};
y;SORTIEREN(VSTAPELN({0.17;99999.0};SPALTENWAHL(x;{1.3});SPALTENWAHL(x;{2.4})));
SUMME(NACHZEILE(WEGLASSEN(y;-1;-1)+WEGLASSEN(y;1;-1)%%%;
 LAMBDA(a;MAX(;MIN(KÜRZEN(REST(a;1)/1%%%);b)-MAX(KÜRZEN(a);v))))*WEGLASSEN(y;-1;1))/(b-v))

So komme ich auf nur noch 16 statt ca. 33 verwendete Funktionen innerhalb des "Alles-in-einer-Formel-Monsters". Man muss es aber trotzdem selbst aufbauen, um es inhaltlich zu verstehen. Ich kann die Bedenken von Luschi und neopa insofern nachvollziehen.

[maninweb]

Moin,

@Luschi: schöne PQ-Lösung.
@neopa: auch schöne Lösung. Ich erhalte mit meiner Formel dieselben Ergebnisse wie Du (18.978,02 und 17.315,79). Wenn ich das richtig sehe, wäre es dann etwas schwieriger bei 3 Raten-Intervallen (?).
@LCohen: stimmt, deutlich kürzer. Ich finde es ja immer wieder schön, wie man in Excel mit unterschiedlichen Lösungen zum Ergebnis kommt.

Gruß

[LCohen]

Wer es mag, schiebt beides - wie oben bei maninweb - noch zusammen, so dass die "excelgerechte Datumsstaffel" (s.o.) nicht mehr zu sehen ist. F1# wird also gelöscht und es ist nur noch das Ergebnis in C4 zu sehen.
C4: =LET(
v;A4;
b;B4;
x;A1:D2+{0.1.0.17};
y;SORTIEREN(VSTAPELN({0.17;99999.0};SPALTENWAHL(x;{1.3});SPALTENWAHL(x;{2.4})));
SUMME(NACHZEILE(WEGLASSEN(y;-1;-1)+WEGLASSEN(y;1;-1)%%%;
 LAMBDA(a;MAX(;MIN(KÜRZEN(REST(a;1)/1%%%);b)-MAX(KÜRZEN(a);v))))*WEGLASSEN(y;-1;1))/(b-v))

C4: =LET(
v;A4;
b;B4;
x;A1:D2+{0.1.0.17};
y;SORTIEREN(VSTAPELN({0.17;99999.0};SPALTENWAHL(x;{1.3});SPALTENWAHL(x;{2.4})));
u;WEGLASSEN(y;-1;-1);
o;WEGLASSEN(y;1;-1);
z;WENN(b>o;o;b)-WENN(u>v;u;v);
SUMME((z>0)*z*WEGLASSEN(y;-1;1))/(b-v))

ist sehr viel klarer und reduziert die Funktionenzahl noch einmal deutlich von 16 auf 11.