Tengo dos mesas. El primero contiene tarifas condicionales indicativas para un número específico de días (títulos de columna)
| Fecha | 1 día | 7 días | 14 dias | 30 dias | 90 dias |
|---|---|---|---|---|---|
| 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 |
Y la segunda tabla en la que necesito calcular la tarifa real para un número determinado de días.
| Fecha | Días | Tasa |
|---|---|---|
| 12.01.2021 | 21 | ? |
| 13.01.2021 | 74 | ? |
| 15.01.2021 | 40 | ? |
Como puede ver, se dan tarifas condicionales para algunos días y tarifas reales para otros. Entre otras cosas, se dan tarifas condicionales para cada día y tarifas reales solo para algunos de ellos. ¿Cómo puedo calcular esta tarifa? Veo el uso de la interpolación lineal aquí, por ejemplo: si el número real de días es 21, entonces la tasa condicional más cercana se da para 14 días y necesito extrapolar este valor varios días en el futuro. Leí sobre =forecast, pero no hace lo que necesito. ¿Existe alguna función en Excel que me ayude a hacer esto o necesito escribir un script VBA para esto?
Respuesta1
Oficina 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))
Sorprendentemente, la interpolación lineal es bastante difícil de realizar en Excel.
Esto utiliza la función Pronóstico de Excel =FORECAST(x,known_y's,known_x's).
El =INDEXy =MATCHseleccione los dos puntos relevantes en known_y'sy known_x's.
Esto funcionará en Excel 2007, pero =FORECAST.LINEARdebe cambiarse a =FORECAST(se depreciará pronto).
Respuesta2
Necesitamos obtener una proporción de dónde caen los días entre el punto de ajuste y multiplicar esa proporción por la diferencia entre los valores superior e inferior, luego agregar el valor inferior a ese resultado.
Un paso que debe realizarse es cambiar los encabezados al número real.
En OFFICE 365 podemos usar LET para simplificar los pasos:
=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)
sin dejar:
=(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))
Como puede ver, se vuelve muy difícil de manejar y de manejar.
Otro método con LET es usar FORECAST.LINEAR en lugar de matemáticas para obtener la respuesta después de obtener las x e y iniciales y finales:
=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)))
