熟悉財務(wù)和投資的小伙伴，對 NPV 一定不會陌生，NPV，即凈現(xiàn)值，它是衡量投資收益的重要指標，一般來說，一項投資的凈現(xiàn)值越大，收益越好。

Excel 中的 NPV 函數(shù)就是專門為計算凈現(xiàn)值量身定制的。

但，就是這樣一個看似平淡正經(jīng)的函數(shù)，竟也硬生生讓 Excel 大佬們玩出了花活！

想知道 NPV 函數(shù)的另類用法嗎？

跟上小花的節(jié)奏，帶你領(lǐng)略不一樣的 NPV 函數(shù)！

交叉求差的行家

小 K 管理四家門店，每天都需要通過各門店的收支表計算總利潤，每個門店的收支都用正數(shù)表示，如下圖，如何設(shè)置 C10 單元格的計算公式才最簡潔呢？

常規(guī)的思路肯定是用 SUMIF 函數(shù)來處理，公式如下：

• =SUM(C2:C9)-2*SUMIF(B2:B9,"支出",C2:C9)
  

盡管這個公式相較于兩個 SUMIF 函數(shù)相減的公式，已經(jīng)簡化了許多，但如果你使用 NPV 函數(shù)來處理這個問題，其公式的間潔程度絕對能驚掉你的下巴！

不信，一起瞧瞧？

NPV 隔行求差公式：

• =-NPV(-2,C2:C9)
  

公式說明：該公式是基于 NPV 函數(shù)的計算原理和數(shù)學邏輯而設(shè)置的。

首先，我們需要先了解 NPV 函數(shù)的計算原理，即參數(shù) 2 的每個值 value 都乘以 1/（1+rate）的指定次放（當前值的序數(shù)值）再求和。

公式中，我們將 rate 設(shè)定為-2，于是 1/（1+rate）等于-1。

基于-1 的奇數(shù)次方為-1、偶數(shù)次方為 1 這一原理，NPV 函數(shù)第二參數(shù)的每一個奇數(shù)項取其相反數(shù)，偶數(shù)項取其本身，從而實現(xiàn)交叉求差運算。

由于這樣計算的結(jié)果是收入為負、支出為正，因此需要在公式前添加負號。

同時由于 NPV 輸出結(jié)果默認為貨幣，所以需重新設(shè)置單元格數(shù)字格式為【常規(guī)】。

混合文本取數(shù)能手

混合文本取數(shù)是一個 Excel 公式難題，你一定見過很多不同的解法，但你可能不知道，NPV 函數(shù)也可以解決這個問題，特別是從一堆混亂數(shù)據(jù)中提取所有數(shù)值并進行組合，NPV 可謂得心應(yīng)手。

如下例，由于記錄過程時間倉促，導致客戶信息和客戶電話號碼混雜，且格式隨意，沒有明確規(guī)則，唯一能確定的是，客戶的電話號碼是記錄完全、順序無誤的。

這種情況下，該如何提取客戶電話號碼呢？

公式如下：

• {=(--MID(NPV(9,IFERROR(--MID(A2,ROW($1:$100),1),""),1),3,100)-1)/10}
  

公式說明：

①ROW($1:$100)：返回 1:100 的有序數(shù)組

②MID(A2,①,1)：逐一提取 A2 中的每個字符

③--②：將 MID 提取的數(shù)字轉(zhuǎn)化成數(shù)值

④IFERROR(③,"")：MID 提取的非數(shù)字通過雙負號轉(zhuǎn)化為數(shù)值或出現(xiàn)拼寫錯誤#NAME?，此處使用 IFERROR 將其轉(zhuǎn)換為空，于是 MID 提出的所有字符中，僅所有數(shù)字被保留，其余均為空。

⑤NPV(9,④,1)：將 rate 設(shè)定為 9，于是 1/（1+rate）等于 1/10，④中所有數(shù)字{1;7;7;8;9;1;2;3;2;4;5}依次除以 10，100，1000......，得到{0.1;0.07;0.007;0.0008;0.00009;......}，相加后得到 0.17789123245，即實現(xiàn)了將所有數(shù)字在小數(shù)點后依次排列的效果。為避免最后一位數(shù)為 0，導致后續(xù)計算出錯，NPV 增加一個 value 值 1，從而實現(xiàn)在數(shù)字串的最后一位后添加數(shù)字 1，0.17789123245 被轉(zhuǎn)化為 0.177891232451。

⑥MID(⑤,3,100)：提取小數(shù)點后的所有數(shù)字

⑦--⑥：同③，將 MID 提取的數(shù)字轉(zhuǎn)化成數(shù)值

⑧{=(⑦-1)/10}：去除⑤中額外添加的尾數(shù) 1。最外圍的大括號表示這是一個數(shù)組公式，需要同時按 Ctrl+Shift+Enter 才能正確計算。

數(shù)字倒排大師

有朋友問過小花，如何把 10 個數(shù)字組成的編碼倒序排列？

小花給出的答案讓他震驚不已！沒錯，就是 NPV 函數(shù)。

公式如下：

• {=NPV(-0.9,--MID(A2,ROW($1:$10),1))/10}
  

公式說明：MID(A2,ROW($1:$10),1)將 A2 單元格中的數(shù)字逐一提取出來，通過雙負號「--」將文本型數(shù)字轉(zhuǎn)化為數(shù)值型數(shù)字，以便 NPV 進行計算。

將 NPV 函數(shù)的參數(shù) rate 設(shè)定為-0.9，于是 1/（1+rate）等于 10，A2 中的所有數(shù)字被依次乘以 10,100,1000......，再相加，就實現(xiàn)了將第 1 個數(shù)字填列在十位上，第 2 個數(shù)字填列在百位數(shù)，第 3 個數(shù)字填列在千位上，以此類推。最后將公式的整體除以 10，即可完成數(shù)字倒排。
以上就是小花分享的關(guān)于 NPV 函數(shù)的三種另類用法，你學會了嗎？回顧一下吧：

? rate 設(shè)定為-2，可實現(xiàn)交叉求差；

??rate 設(shè)定為 9，能夠完成混合文本取數(shù)；

??rate 設(shè)定為-0.9，輕松拿下數(shù)字倒排。

細心的你或許會發(fā)現(xiàn)，最后一個案例中 A3 單元格的尾數(shù)為 0，倒排后最前面的 0 會被省略，但小花的結(jié)果卻是正確的，你知道是怎么做到的嗎？

快開動腦筋想想吧，留言說出你的答案！

2 分鐘，3 步驟，秒懂一個 Excel 新技能~

如果你想學習更多關(guān)于函數(shù)的知識，更多 Excel 小技巧！

那我推薦你參加秋葉《3 天 Excel 集訓營》

大神帶你學習表格飛速排版、數(shù)據(jù)高效整理、圖表美化設(shè)計……?。?/p>

和志同道合的小伙伴一起交流進步~

僅需 3 天

你就可能成為 Excel 高手！

趕緊掃碼咨詢吧！！

*廣告