熟悉財(cái)務(wù)和投資的小伙伴,對(duì) NPV 一定不會(huì)陌生,NPV,即凈現(xiàn)值,它是衡量投資收益的重要指標(biāo),一般來說,一項(xiàng)投資的凈現(xiàn)值越大,收益越好。
Excel 中的 NPV 函數(shù)就是專門為計(jì)算凈現(xiàn)值量身定制的。
但,就是這樣一個(gè)看似平淡正經(jīng)的函數(shù),竟也硬生生讓 Excel 大佬們玩出了花活!
想知道 NPV 函數(shù)的另類用法嗎?
跟上小花的節(jié)奏,帶你領(lǐng)略不一樣的 NPV 函數(shù)!
1、交叉求差的行家
小 K 管理四家門店,每天都需要通過各門店的收支表計(jì)算總利潤,每個(gè)門店的收支都用正數(shù)表示,如下圖,如何設(shè)置 C10 單元格的計(jì)算公式才最簡潔呢?
常規(guī)的思路肯定是用 SUMIF 函數(shù)來處理,公式如下:
=SUM(C2:C9)-2*SUMIF(B2:B9,"支出",C2:C9)
盡管這個(gè)公式相較于兩個(gè) SUMIF 函數(shù)相減的公式,已經(jīng)簡化了許多,但如果你使用 NPV 函數(shù)來處理這個(gè)問題,其公式的間潔程度絕對(duì)能驚掉你的下巴!
不信,一起瞧瞧?
NPV 隔行求差公式:
=-NPV(-2,C2:C9)
公式說明:
該公式是基于 NPV 函數(shù)的計(jì)算原理和數(shù)學(xué)邏輯而設(shè)置的。
首先,我們需要先了解 NPV 函數(shù)的計(jì)算原理,即參數(shù) 2 的每個(gè)值 value 都乘以 1/(1+rate)的指定次放(當(dāng)前值的序數(shù)值)再求和。
公式中,我們將 rate 設(shè)定為-2,于是 1/(1+rate)等于-1。
基于-1 的奇數(shù)次方為-1、偶數(shù)次方為 1 這一原理,NPV 函數(shù)第二參數(shù)的每一個(gè)奇數(shù)項(xiàng)取其相反數(shù),偶數(shù)項(xiàng)取其本身,從而實(shí)現(xiàn)交叉求差運(yùn)算。
由于這樣計(jì)算的結(jié)果是收入為負(fù)、支出為正,因此需要在公式前添加負(fù)號(hào)。
同時(shí)由于 NPV 輸出結(jié)果默認(rèn)為貨幣,所以需重新設(shè)置單元格數(shù)字格式為【常規(guī)】。
2、混合文本取數(shù)能手
混合文本取數(shù)是一個(gè) Excel 公式難題,你一定見過很多不同的解法,但你可能不知道,NPV 函數(shù)也可以解決這個(gè)問題,特別是從一堆混亂數(shù)據(jù)中提取所有數(shù)值并進(jìn)行組合,NPV 可謂得心應(yīng)手。
如下例,由于記錄過程時(shí)間倉促,導(dǎo)致客戶信息和客戶電話號(hào)碼混雜,且格式隨意,沒有明確規(guī)則,唯一能確定的是,客戶的電話號(hào)碼是記錄完全、順序無誤的。
這種情況下,該如何提取客戶電話號(hào)碼呢?
公式如下:
{=(--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 中的每個(gè)字符
③--②:將 MID 提取的數(shù)字轉(zhuǎn)化成數(shù)值
④IFERROR(③,""):MID 提取的非數(shù)字通過雙負(fù)號(hào)轉(zhuǎn)化為數(shù)值或出現(xiàn)拼寫錯(cuò)誤#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,即實(shí)現(xiàn)了將所有數(shù)字在小數(shù)點(diǎn)后依次排列的效果。為避免最后一位數(shù)為 0,導(dǎo)致后續(xù)計(jì)算出錯(cuò),NPV 增加一個(gè) value 值 1,從而實(shí)現(xiàn)在數(shù)字串的最后一位后添加數(shù)字 1,0.17789123245 被轉(zhuǎn)化為 0.177891232451。
⑥MID(⑤,3,100):提取小數(shù)點(diǎn)后的所有數(shù)字
⑦--⑥:同③,將 MID 提取的數(shù)字轉(zhuǎn)化成數(shù)值
⑧{=(⑦-1)/10}:去除⑤中額外添加的尾數(shù) 1。最外圍的大括號(hào)表示這是一個(gè)數(shù)組公式,需要同時(shí)按 Ctrl+Shift+Enter 才能正確計(jì)算。
3、數(shù)字倒排大師
有朋友問過小花,如何把 10 個(gè)數(shù)字組成的編碼倒序排列?
小花給出的答案讓他震驚不已!沒錯(cuò),就是 NPV 函數(shù)。
公式如下:
{=NPV(-0.9,--MID(A2,ROW($1:$10),1))/10}
公式說明:
MID (A2,ROW ($1:$10),1) 將 A2 單元格中的數(shù)字逐一提取出來,通過雙負(fù)號(hào)「--」將文本型數(shù)字轉(zhuǎn)化為數(shù)值型數(shù)字,以便 NPV 進(jìn)行計(jì)算。
將 NPV 函數(shù)的參數(shù) rate 設(shè)定為-0.9,于是 1/(1+rate)等于 10,A2 中的所有數(shù)字被依次乘以 10,100,1000......,再相加,就實(shí)現(xiàn)了將第 1 個(gè)數(shù)字填列在十位上,第 2 個(gè)數(shù)字填列在百位數(shù),第 3 個(gè)數(shù)字填列在千位上,以此類推。最后將公式的整體除以 10,即可完成數(shù)字倒排。
以上就是小花分享的關(guān)于 NPV 函數(shù)的三種另類用法,你學(xué)會(huì)了嗎?回顧一下吧:
? rate 設(shè)定為-2,可實(shí)現(xiàn)交叉求差;
? rate 設(shè)定為 9,能夠完成混合文本取數(shù);
? rate 設(shè)定為-0.9,輕松拿下數(shù)字倒排。
本文來自微信公眾號(hào):秋葉 Excel (ID:excel100),作者:小花
廣告聲明:文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接(包括不限于超鏈接、二維碼、口令等形式),用于傳遞更多信息,節(jié)省甄選時(shí)間,結(jié)果僅供參考,IT之家所有文章均包含本聲明。