Excel公式技巧61：插值公式技术

excelperfect

一个项目从2013年开始投资，相关数据如下图1所示，求该项目的投资回收期？也就是说，累计现金流等于0的那个时间点。

图1

从工作表中可以看出，该项目的投资回收期在2015年至2016年之间，且更接近于2016年。

可以以时间为横坐标，现金流数据为纵坐标，绘制图表后，测量横坐标上的时间来近似求出；也可以使用三角形等比公式来精确求得。这里使用公式来计算。

在单元格D8中输入公式：

=(FORECAST(0,OFFSET(C4,,MATCH(0,C6:G6)-1,1,2),OFFSET(C6,,MATCH(0,C6:G6)-1,1,2))-C4)/365

得到该项目投资回收期。

公式的关键在FORECAST函数，包含有3个参数。其中：

1.参数x：0

2.参数known_y’s：OFFSET(C4,,MATCH(0,C6:G6)-1,1,2)

3.参数known_x’s：OFFSET(C6,,MATCH(0,C6:G6)-1,1,2)

FORECAST函数通过插值找到未知的x值。

在公式中：

MATCH(0,C6:G6)

在单元格区域C6:G6中查找值0，返回-9所在的位置3，这样：

OFFSET(C4,,MATCH(0,C6:G6)-1,1,2)

转换为：

OFFSET(C4,,2,1,2)

得到单元格区域：E4:F4

同理，公式中的：

OFFSET(C6,,MATCH(0,C6:G6)-1,1,2)

得到单元格区域E6:F6

因此，公式中的FORECAST函数转换为：

=FORECAST(0, E4:F4, E6:F6)

返回2015年10月13日，即收支平衡的日期。

再通过减去开始日期并除以一年的天数365天来计算年数，即公式：

=(FORECAST(0, E4:F4, E6:F6)-C4)/365

得到结果：

2.78

注：这是在Chandoo.org论坛上看到的一个贴子，特整理于此，供学习参考。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。