标签:Excel公式
NPV是一个财务函数,基于一系列将来的收(正值)支(负值)现金流和一贴现率,返回一项投资的净现值。其语法为:
NPV(rate,value1,value2,…)
本来是计算净现值的函数,然而,却可另作它用。
如下图1所示,列A中有一系列带有单位的数值,现在想要求其中的数字之和。
图1
怎么办呢?一种思路是,如图1所示先将列A中单元格内的数字提取到列B中,然后求和。
可以使用NPV函数来实现。在单元格B1中输入数组公式:
=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET($A$1,,,LEN(A1))),1)%,''))
拖动复制至数据单元格末尾即可。注意,这是一个数组公式,输入完后要按Ctrl+Shift+Enter组合键。
NPV的神奇之处在于NPV计算公式,其中每项乘以(1+rate)^n的倒数,其中n是序列中的第n项,例如,(1+rate)^1、(1+rate)^2等。通过使用不同的rate值,可以得到不同的结果。在本例中,使用-0.9,提供给1+rate=1+(-0.9)=0.1,所以得到类似于{0.1;0.01;0.001;0.0001;0.00001}的值,取倒数得到{10;100;1000;10000;100000},等等,再加上NPV忽略文本值,因此可以得到了期望的结果。
下面,我们来具体分析一下。例如上图1中,单元格B3中的公式为:
=NPV(-0.9,,IFERROR(MID(A3,1+LEN(A3)-ROW(OFFSET($A$1,,,LEN(A3))),1)%,''))
其中,
IFERROR(MID(A3,1+LEN(A3)-ROW(OFFSET($A$1,,,LEN(A3))),1)%,'')
生成数组:
{'';'';0.04;0.03;0.02;0.01}
这样,公式就转化为:
=NPV(-0.9,, {'';'';0.04;0.03;0.02;0.01})
由于NPV函数忽略参数为空单元格、逻辑值、数字的文本表示式、错误值或不能转化为数值的文本,因此,上式相当于:
{10;100;1000;10000;100000}*{;0.04;0.03;0.02;0.01}
即:
10*0+100*0.04+1000*0.03+10000*0.02+100000*0.01
=1234
小结
本文给我的最大冲击不是公式本身,而是思路。一个函数有它本身的用途,然而,如果思路打开,也许这个函数会得到更广泛且有想象力的应用。当然,前提是我们首先要熟悉函数的基本运作原理,能够熟练地运用它们,打造扎实的基本功。
联系客服