Excel 中 NPV 函数使用技巧分享

熟悉财务和投资的小伙伴，对 NPV 一定不会陌生，NPV，即净现值，它是衡量投资收益的重要指标，一般来说，一项投资的净现值越大，收益越好。

Excel 中的 NPV 函数就是专门为计算净现值量身定制的。

但，就是这样一个看似平淡正经的函数，竟也硬生生让 Excel 大佬们玩出了花活！

想知道 NPV 函数的另类用法吗？

跟上小花的节奏，带你领略不一样的 NPV 函数！

1、交叉求差的行家

小 K 管理四家门店，每天都需要通过各门店的收支表计算总利润，每个门店的收支都用正数表示，如下图，如何设置 C10 单元格的计算公式才最简洁呢？

常规的思路肯定是用 SUMIF 函数来处理，公式如下：

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

尽管这个公式相较于两个 SUMIF 函数相减的公式，已经简化了许多，但如果你使用 NPV 函数来处理这个问题，其公式的间洁程度绝对能惊掉你的下巴！

不信，一起瞧瞧？

NPV 隔行求差公式：

=-NPV(-2,C2:C9)

公式说明：

该公式是基于 NPV 函数的计算原理和数学逻辑而设置的。

首先，我们需要先了解 NPV 函数的计算原理，即参数 2 的每个值 value 都乘以 1/（1+rate）的指定次放（当前值的序数值）再求和。

公式中，我们将 rate 设定为-2，于是 1/（1+rate）等于-1。

基于-1 的奇数次方为-1、偶数次方为 1 这一原理，NPV 函数第二参数的每一个奇数项取其相反数，偶数项取其本身，从而实现交叉求差运算。

由于这样计算的结果是收入为负、支出为正，因此需要在公式前添加负号。

同时由于 NPV 输出结果默认为货币，所以需重新设置单元格数字格式为【常规】。

2、混合文本取数能手

混合文本取数是一个 Excel 公式难题，你一定见过很多不同的解法，但你可能不知道，NPV 函数也可以解决这个问题，特别是从一堆混乱数据中提取所有数值并进行组合，NPV 可谓得心应手。

如下例，由于记录过程时间仓促，导致客户信息和客户电话号码混杂，且格式随意，没有明确规则，唯一能确定的是，客户的电话号码是记录完全、顺序无误的。

这种情况下，该如何提取客户电话号码呢？

公式如下：

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

公式说明：

①ROW($1:$100)：返回 1:100 的有序数组

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

③--②：将 MID 提取的数字转化成数值

④IFERROR(③,"")：MID 提取的非数字通过双负号转化为数值或出现拼写错误#NAME?，此处使用 IFERROR 将其转换为空，于是 MID 提出的所有字符中，仅所有数字被保留，其余均为空。

⑤NPV(9,④,1)：将 rate 设定为 9，于是 1/（1+rate）等于 1/10，④中所有数字 {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，即实现了将所有数字在小数点后依次排列的效果。为避免最后一位数为 0，导致后续计算出错，NPV 增加一个 value 值 1，从而实现在数字串的最后一位后添加数字 1，0.17789123245 被转化为 0.177891232451。

⑥MID(⑤,3,100)：提取小数点后的所有数字

⑦--⑥：同③，将 MID 提取的数字转化成数值

⑧{=(⑦-1)/10}：去除⑤中额外添加的尾数 1。最外围的大括号表示这是一个数组公式，需要同时按 Ctrl+Shift+Enter 才能正确计算。

3、数字倒排大师

有朋友问过小花，如何把 10 个数字组成的编码倒序排列？

小花给出的答案让他震惊不已！没错，就是 NPV 函数。

公式如下：

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

公式说明：

MID (A2,ROW ($1:$10),1) 将 A2 单元格中的数字逐一提取出来，通过双负号「--」将文本型数字转化为数值型数字，以便 NPV 进行计算。

将 NPV 函数的参数 rate 设定为-0.9，于是 1/（1+rate）等于 10，A2 中的所有数字被依次乘以 10,100,1000......，再相加，就实现了将第 1 个数字填列在十位上，第 2 个数字填列在百位数，第 3 个数字填列在千位上，以此类推。最后将公式的整体除以 10，即可完成数字倒排。

以上就是小花分享的关于 NPV 函数的三种另类用法，你学会了吗？回顾一下吧：

❶ rate 设定为-2，可实现交叉求差；

❷ rate 设定为 9，能够完成混合文本取数；

❸ rate 设定为-0.9，轻松拿下数字倒排。

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：小花

广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。