Ich habe zwei Tabellen. Die erste enthält indikative bedingte Tarife für eine bestimmte Anzahl von Tagen (Spaltenüberschriften)
| Datum | 1 Tag | 7 Tage | 14 Tage | 30 Tage | 90 Tage |
|---|---|---|---|---|---|
| 11.01.2021 | 4.36 | 4.36 | 4.30 | 4,49 | 4,64 |
| 12.01.2021 | 4.32 | 4.34 | 4.36 | 4,48 | 4,63 |
| 13.01.2021 | 4,25 | 4.32 | 4.34 | 4.43 | 4,60 |
| 14.01.2021 | 4.26 | 4,35 | 4,35 | 4.36 | 4,58 |
| 15.01.2021 | 4.33 | 4,35 | 4.37 | 4.34 | 4,57 |
Und die zweite Tabelle, in der ich den Realkurs für eine bestimmte Anzahl von Tagen berechnen muss
| Datum | Tage | Rate |
|---|---|---|
| 12.01.2021 | 21 | ? |
| 13.01.2021 | 74 | ? |
| 15.01.2021 | 40 | ? |
Wie Sie sehen, werden für einige Tage bedingte Kurse und für andere reale Kurse angegeben. Unter anderem werden für jeden Tag bedingte Kurse und nur für einige davon reale Kurse angegeben. Wie kann ich diesen Kurs berechnen? Ich sehe hier die Verwendung linearer Interpolation, beispielsweise: Wenn die tatsächliche Anzahl der Tage 21 beträgt, wird der nächste bedingte Kurs für 14 Tage angegeben und ich muss diesen Wert mehrere Tage in die Zukunft extrapolieren. Ich habe darüber gelesen =forecast, aber es tut nicht, was ich brauche. Gibt es in Excel eine Funktion, die mir dabei hilft, oder muss ich dafür ein VBA-Skript schreiben?
Antwort1
Büro 365:
=FORECAST.LINEAR(B7,
INDEX(B2:F2,MATCH(B7,$B$1:$F$1,1)):INDEX(B2:F2,MATCH(B7,$B$1:$F$1,1)+1),
INDEX($B$1:$F$1,MATCH(B7,$B$1:$F$1,1)):INDEX($B$1:$F$1,MATCH(B7,$B$1:$F$1,1)+1))
Überraschenderweise ist die lineare Interpolation in Excel ziemlich schwierig.
Hierbei wird die Excel-Prognosefunktion verwendet =FORECAST(x,known_y's,known_x's).
=INDEXWählen Sie anschließend die =MATCHbeiden relevanten Punkte auf known_y'sund aus known_x's.
Dies funktioniert unter Excel 2007, =FORECAST.LINEARmuss aber in =FORECAST(wird bald veraltet) geändert werden.
Antwort2
Wir müssen ein Verhältnis ermitteln, bei dem die Tage zwischen den Sollwerten liegen, dieses Verhältnis mit der Differenz zwischen den oberen und unteren Werten multiplizieren und dann den unteren Wert zu diesem Ergebnis hinzufügen.
Ein Schritt, der durchgeführt werden muss, ist das Ändern der Überschriften in die eigentliche Nummer.
In OFFICE 365 können wir LET verwenden, um die Schritte zu vereinfachen:
=LET(head,$B$1:$F$1,
lkp,B7,
rng,$B$2:$F$4,
dt,$A$2:$A$4,
rw,INDEX(rng,MATCH(A7,dt,0),0),
mtch,MATCH(lkp,head),
mtchp1,mtch+1,
fst,INDEX(head,,mtch),
scd,INDEX(head,,mtchp1),
fct,(lkp-fst)/(scd-fst),
vlue1,INDEX(rw,,mtch),
vlue2,INDEX(rw,,mtchp1),
(vlue2-vlue1)*fct+vlue1)
ohne das let:
=(INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1)+1)- INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1)))* ((B7- INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1)))/( INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1)+1)- INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1))))+ INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1))
Wie Sie sehen, wird es sehr unhandlich und schwer zu verwalten.
Eine andere Methode mit LET besteht darin, FORECAST.LINEAR anstelle von Mathematik zu verwenden, um die Antwort zu erhalten, nachdem die Start- und End-x- und -y-Werte ermittelt wurden:
=LET(head,$B$1:$F$1,
lkp,B7,
rng,$B$2:$F$4,
dt,$A$2:$A$4,
rw,INDEX(rng,MATCH(A7,dt,0),0),
mtch,MATCH(lkp,head),
mtchp1,mtch+1,
fst,INDEX(head,,mtch),
scd,INDEX(head,,mtchp1),
vlue1,INDEX(rw,,mtch),
vlue2,INDEX(rw,,mtchp1),
FORECAST.LINEAR(lkp,CHOOSE({1,2},vlue1,vlue2),CHOOSE({1,2},fst,scd)))
