Excel公式：求投资回收期

excelperfect

引言：本文的练习整理自chandoo.org。多练习，这是我们从小就在使用的学习方法。在练习的过程中，认真思考，不断尝试，以此来磨练自己的公式与函数应用技能，也让研究Excel的大脑时刻保持着良好的状态。同时，想想自己怎么解决这个问题，看看别人又是怎样解决的，从而快速提高Excel公式应用水平。

本文提供的公式很奇怪，所使用的函数也不常用，但对于数据分析来说值得研究。

本次的练习是：示例数据如下图1所示。

图1

第3行是投资额，第5行是投资回收的累计现金流。根据第5行的数据求投资回收期。

要求不能使用易失性函数。

请写下你的公式。

注：投资回收期的计算公式：

=n + (ABS(a)/b)

其中，n代表出现第一个正的累计现金流之前的年份；a代表最后一个负的累计现金流值；b代表第一个正的累计现金流对应年份的投资额。

因此，对于本示例来说，应该是：

=2+(ABS(C5)/D3)

=2+(ABS(-65900)/95830)

=2.687676

解决方案

公式1：

=LOOKUP(0,A5:F5,A2:F2-A5:F5/B3:F3)

公式2：

=PERCENTRANK(A5:F5,,20)*MAX(A2:F2)

公式3：数组公式。

=FORECAST(,IF((LOOKUP(,A5:F5,A2:F2)-A2:F2+0.5)^2<1,A2:F2),A5:F5)

公式4：数组公式。

=PERCENTRANK.INC(PROB(TRANSPOSE(COLUMN(A3:F3))*{1,0,0,0},TRANSPOSE(A3:F3)*{1,0,0,0}+{0,9,-9,1}^99,1,TRANSPOSE(COLUMN(A3:F3))),0,6)*F2

或者：

=PERCENTRANK.INC(SUMIF(A2:F2,'<='&A2:F2,A3:F3),0,6)*F2

或者：

=PERCENTILE.INC(A2:F2,PERCENTRANK.INC(SUMIF(A2:F2,'<='&A2:F2,A3:F3),0,20))