如何在 Excel 中插入值

Question

我有两张表格。第一张包含特定天数的指示性条件利率（列标题）

日期	1天	7天	14天	30天	90 天
2021 年 1 月 11 日	4.36	4.36	4.30	4.49	4.64
2021 年 1 月 12 日	4.32	4.34	4.36	4.48	4.63
2021 年 1 月 13 日	4.25	4.32	4.34	4.43	4.60
2021 年 1 月 14 日	4.26	4.35	4.35	4.36	4.58
2021 年 1 月 15 日	4.33	4.35	4.37	4.34	4.57

第二张表需要计算给定天数的实际利率

日期	天	速度
2021 年 1 月 12 日	21	？
2021 年 1 月 13 日	74	？
2021 年 1 月 15 日	40	？

如您所见，某些天数给出了条件利率，而其他天数给出了实际利率。除其他事项外，每天都给出了条件利率，但只有其中某些天数给出了实际利率。我如何计算这个利率？我看到这里使用了线性插值，例如 - 如果实际天数为 21，则给出的最近条件利率为 14 天，我需要推断未来几天的这个值。我读过=forecast，但它不能满足我的需要。Excel 中是否有可以帮助我执行此操作的函数，或者我需要为此编写 VBA 脚本吗？

Answer 1

Office 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选取=MATCH相关的两个点。known_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 而不是数学来在获取开始和结束 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)))