標簽：Excel公式

NPV是一個財務函數(shù)，基于一系列將來的收（正值）支（負值）現(xiàn)金流和一貼現(xiàn)率，返回一項投資的凈現(xiàn)值。其語法為：

NPV(rate,value1,value2,…)

本來是計算凈現(xiàn)值的函數(shù)，然而，卻可另作它用。

如下圖1所示，列A中有一系列帶有單位的數(shù)值，現(xiàn)在想要求其中的數(shù)字之和。

圖1

怎么辦呢？一種思路是，如圖1所示先將列A中單元格內的數(shù)字提取到列B中，然后求和。

可以使用NPV函數(shù)來實現(xiàn)。在單元格B1中輸入數(shù)組公式：

=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET($A$1,,,LEN(A1))),1)%,''))

拖動復制至數(shù)據(jù)單元格末尾即可。注意，這是一個數(shù)組公式，輸入完后要按Ctrl+Shift+Enter組合鍵。

NPV的神奇之處在于NPV計算公式，其中每項乘以(1+rate)^n的倒數(shù)，其中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}的值，取倒數(shù)得到{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)%,'')

生成數(shù)組：

{'';'';0.04;0.03;0.02;0.01}

這樣，公式就轉化為：

=NPV(-0.9,, {'';'';0.04;0.03;0.02;0.01})

由于NPV函數(shù)忽略參數(shù)為空單元格、邏輯值、數(shù)字的文本表示式、錯誤值或不能轉化為數(shù)值的文本，因此，上式相當于：

{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

小結

本文給我的最大沖擊不是公式本身，而是思路。一個函數(shù)有它本身的用途，然而，如果思路打開，也許這個函數(shù)會得到更廣泛且有想象力的應用。當然，前提是我們首先要熟悉函數(shù)的基本運作原理，能夠熟練地運用它們，打造扎實的基本功。