Как интерполировать значения в Excel

Question

У меня есть две таблицы. Первая содержит индикативные условные ставки на определенное количество дней (заголовки столбцов)

Дата	1 день	7 дней	14 дней	30 дней	90 дней
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

И вторая таблица, в которой мне нужно рассчитать реальную ставку за заданное количество дней

Дата	Дни	Ставка
12.01.2021	21	?
13.01.2021	74	?
15.01.2021	40	?

Как вы видите, для некоторых дней даны условные ставки, а для других — реальные. Среди прочего, условные ставки даны для каждого дня, а реальные ставки только для некоторых из них. Как мне рассчитать эту ставку? Я вижу здесь использование линейной интерполяции, например, если реальное количество дней равно 21, то ближайшая условная ставка дана для 14 дней, и мне нужно экстраполировать это значение на несколько дней в будущем. Я читал о =forecast, но она не делает то, что мне нужно. Есть ли функция в Excel, которая поможет мне это сделать, или мне нужно написать для этого скрипт VBA?

Answer 1

Офис 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))

ОтИнтерполировать с помощью Excel

Удивительно, но линейную интерполяцию довольно сложно выполнить в Excel.

Здесь используется функция прогноза Excel =FORECAST(x,known_y's,known_x's).

и выберите соответствующие две точки на =INDEXи .=MATCHknown_y'sknown_x's

Это будет работать в Excel 2007, но =FORECAST.LINEARего необходимо изменить на =FORECAST(скоро устареет).

Answer 2

нам нужно получить соотношение того, где находятся дни между заданным значением, и умножить это соотношение на разницу между верхним и нижним значениями, а затем прибавить нижнее значение к полученному результату.

Один из шагов, который необходимо предпринять, — это изменение заголовков на фактические числа.

В OFFICE 365 мы можем использовать LET для упрощения шагов:

=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)

без 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))

Как видите, это становится очень громоздким и трудным в управлении.

---

Другой метод с LET — использовать FORECAST.LINEAR вместо math, чтобы получить ответ после получения начальных и конечных x и y:

=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)))