Ü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 ..
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.
14.10.2023, 20:27 (Dieser Beitrag wurde zuletzt bearbeitet: 17.10.2023, 15:21 von Glausius.)
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.
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.
15.10.2023, 09:18 (Dieser Beitrag wurde zuletzt bearbeitet: 15.10.2023, 09:22 von maninweb.)
Moin,
das stimmt, diese Fälle wurden nicht abgefangen. Anbei eine angepasste Formel, die die Standardrate ausserhalb der Datumsgrenzen vom Basisbereich annimmt ...
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.
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?
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.
17.10.2023, 09:18 (Dieser Beitrag wurde zuletzt bearbeitet: 17.10.2023, 09:31 von 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ß
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
(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))