Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

INDEX Match zwischen 2 Daten
#1
Guten Tag!
Ich bin neu hier und komme mit der Hoffnung auf Hilfe. Smile

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
Antworten Top
#2
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 Basiswertealso E4 01.01.2023F4 05.03.2023G4 20000 usw

Dann kannst Du folgende Formel in C4 verwenden ...

PHP-Code:
C4=LET(vnStartA4;
       vnEndeB4;
 
      vnBasisE4:G5;
       vnBasisStandardC2;
       vnZeitreiheSEQUENZ(vnEnde vnStart 11vnStart);
       vnZeitreiheBasisSEQUENZ(MAX(INDEX(vnBasis02)) - MIN(INDEX(vnBasis01)) + 11MIN(INDEX(vnBasis01)));
       vnIndexSCAN(0vnZeitreiheBasisLAMBDA(A;VSUMME((INDEX(vnBasis01) - 1) * (INDEX(vnBasis02) + 1)))) * 
                XVERGLEICH(vnZeitreiheBasisINDEX(vnBasis01); -1);
       vnMatrixMATRIXERSTELLEN(ZEILEN(vnIndex); 4LAMBDA(X;YLET(NINDEX(vnIndexX1);
                 WAHL(YINDEX(vnZeitreiheBasisX1); NWENN(0INDEX(INDEX(vnBasis03); N1); vnBasisStandard); 
                 WENNFEHLER(VERGLEICH(INDEX(vnZeitreiheBasisX1); vnZeitreihe0); 0)))));
       vnFilterFILTER(vnMatrixINDEX(vnMatrix04) > 00);
       vnErgebnisSUMME(INDEX(vnFilter03)) / 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.

.xlsx   Raten zwischen 2 Daten.xlsx (Größe: 16,63 KB / Downloads: 12)
Microsoft Excel Expert · Microsoft Most Valuable Professional (MVP) :: 2011-2019 & 2020-2022 :: 10 Awards
https://de.excel-translator.de/translator :: Online Excel-Formel-Übersetzer :: Funktionen :: Fehlerwerte :: Argumente :: Tabellenbezeichner
[-] Folgende(r) 1 Nutzer sagt Danke an maninweb für diesen Beitrag:
  • ExcelDuck
Antworten Top
#3
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!!  43
Antworten Top
#4
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.
Antworten Top
#5
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(vnStartA4
       vnEndeB4
       vnBasisE4:G5
       vnBasisStandardC2
       vnZeitreiheSEQUENZ(vnEnde vnStart 11vnStart); 
       vnZeitreiheBasisSEQUENZ(MAX(INDEX(vnBasis02); vnEnde) - MIN(INDEX(vnBasis01); vnStart) + 11MIN(INDEX(vnBasis01); vnStart)); 
       vnIndexSCAN(0vnZeitreiheBasisLAMBDA(A;VSUMME((INDEX(vnBasis01) - 1) * (INDEX(vnBasis02) + 1)))) * 
                WENNFEHLER(XVERGLEICH(vnZeitreiheBasisINDEX(vnBasis01); -1); 0); 
       vnMatrixMATRIXERSTELLEN(ZEILEN(vnIndex); 4LAMBDA(X;YLET(NINDEX(vnIndexX1); 
                 WAHL(YINDEX(vnZeitreiheBasisX1); NWENN(0INDEX(INDEX(vnBasis03); N1); vnBasisStandard); 
                 WENNFEHLER(VERGLEICH(INDEX(vnZeitreiheBasisX1); vnZeitreihe0); 0))))); 
       vnFilterFILTER(vnMatrixINDEX(vnMatrix04) > 00); 
       vnErgebnisSUMME(INDEX(vnFilter03)) / ZEILEN(vnFilter); vnErgebnis

Gruß
Microsoft Excel Expert · Microsoft Most Valuable Professional (MVP) :: 2011-2019 & 2020-2022 :: 10 Awards
https://de.excel-translator.de/translator :: Online Excel-Formel-Übersetzer :: Funktionen :: Fehlerwerte :: Argumente :: Tabellenbezeichner
Antworten Top
#6
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


Angehängte Dateien
.xlsx   Tarif_01c.xlsx (Größe: 21,34 KB / Downloads: 10)
Antworten Top
#7
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'
ABCDE
2VonBisWertErgebnis Ø:
3Tage:05.01.202305.04.20231700018978,02
4
5Rate101.01.202305.03.202320000
6Rate205.05.202320.06.202323000
7

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

ZelleFormel
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.
Gruß Werner
.. , - ...
Antworten Top
#8
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.
WIN/MSO schicken angeblich alle 5 Sekunden Deinen Screen heim zu Papa (recall-Klausel). 
Antworten Top
#9
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ß
Microsoft Excel Expert · Microsoft Most Valuable Professional (MVP) :: 2011-2019 & 2020-2022 :: 10 Awards
https://de.excel-translator.de/translator :: Online Excel-Formel-Übersetzer :: Funktionen :: Fehlerwerte :: Argumente :: Tabellenbezeichner
Antworten Top
#10
(17.10.2023, 04:14)LCohen schrieb: 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.
WIN/MSO schicken angeblich alle 5 Sekunden Deinen Screen heim zu Papa (recall-Klausel). 
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste