Excel技巧，​​Excel中线性插值法的两个应用实例！

示例1：一维线性插值的应用--投资回收期计算

投资回收期就是指通过资金回流量来回收投资的年限。如果项目建成投产后各年的净收益（即净现金流量）均相同，则静态投资回收期的公式为： 投资金额/年净现金流量；如果项目建成投产后各年的净收益不相同，则静态投资回收期可根据累计净现金流量求得，也就是在累计净现金流量由负值转向正值之间的年份,其计算公式为：累计净现金流量开始出现正值的年份数-1+上一年累计净现金流量的绝对值/出现正值年份的净现金流量。

如图所示是某个投资项目在12年内的现金流入流出情况，其中第1年为投资年度，该年度只有投资成本，现需求出该项目的投资回收期。

由于完全收回投资时净收益累计值为0，在该表中没有直接的对应值，故需采用插值法进行计算。

在C2单元格中定义净收益累计值的公式为:=SUM($B$2:B2)，并将该公式应用到该列中其余单元格。

在F2单元格中定义如下公式：=TREND(SMALL(A2:A14,MATCH(F1,C2:C14)+),SMALL(C2:C14,MATCH(F1,C2:C14)+),F1,)

公式解析：TREND函数原本是用于返回线性趋势值。TREND函数的这一特性可以用于线性插值法的计算。

SMALL(A2:A14,MATCH(F1,C2:C14)+)这部分使用MATCH、SMALL等函数，根据净收益累计值为0，在该表中模糊查出该值所对应的年限临界区间为（9,10）。

SMALL(C2:C14,MATCH(F1,C2:C14)+)这部分根据净收益累计值为0在表中查出净收益累计值的临界区间值为（-193.4，80.7）。

最后使用TREND函数返回线性趋势值的特点计算出投资回收期9.705582年。

提示：该示例还可以用如下公式解决此类插值法计算

=TREND(OFFSET($A$1,MATCH(F1,C$2:C$14,1),,2,),OFFSET($C$1,MATCH(F1,C$2:C$14,1),,2,),F1)

示例2：二维线性插值的应用—根据两个变量进行定价

如图所示是某种物料的定价表，需根据物料的重量和长度数据进行定价，但是由于物料的重量和长度具有可变性，即都不是固定的值。

在表中分别定义名称“长度”和“重量”，公式分别为：

=OFFSET(Sheet1!$A$1,,COUNTA(Sheet1!$1:$1))

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A),)

在表中分别定义名称可变的“长度值”、“重量值”，公式分别为：

=MATCH(Sheet1!$A$1,Sheet1!$A$1:长度)

=MATCH(Sheet1!$A$1,Sheet1!$A$1:重量)

在J2单元格中定义如下公式：

=TREND(IF(I2=重量,OFFSET(A1,重量值,长度值-(H2=长度),,2),CHOOSE(,TREND(OFFSET(A1,重量值,长度值,2),OFFSET(A1,重量值,,2),I2),TREND(OFFSET(A1,重量值,长度值+(H2<>长度),2),OFFSET(A1,重量值,,2),I2))),OFFSET(A1,,长度值-(H2=长度),,2),H2)

提示：利用长度的模糊值确定横向的价格区间，再利用重量的模糊值确定纵向的价格区间，这两个区间再交叉就得到了一个新价格了，如图所示。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；