我是【桃大喵学习记】，欢迎大家关注哟~，每天为你分享职场办公软件使用技巧干货！

——首发于微信号：桃大喵学习记

昨天，跟大家分享了《Excel完成率计算神器，万能公式（完善版），轻松应对各种场景！》这篇教程，其中的万能公式发布后，又根据实际场景思考了一下，发现还有很多BUG不合理的地方。今天再跟大家分享一期“Excel计算完成率计算神器，万能公式（最终完善版）”，希望这次不再被啪啪打脸！！[打脸][打脸][捂脸][捂脸]也希望各位小伙伴留言讨论。

老规则，直接上干货,（最终完善版）完成率公式，可直接套用！

万能公式：

=TEXT(IFS(目标完成数值<0,2-实际完成数值/目标完成数值,目标完成数值=0,"N/A",TRUE,实际完成数值/目标完成数值),"0.00%")

备注：

公式中主要是考虑了目标完成数值小于0；目标完成数值等于零；目标完成数值大于0这三种场景。

实例：

如下图所示，需要按照B列的“实际支出”和B列的“预算支出”，来计算预算的完成率。

在目标单元格中输入公式：

=TEXT(IFS(A2<0,2-B2/A2,A2=0,"N/A",TRUE,A2/B2),"0.00%")

然后点击回车，下拉填充即可

解读：

上面公式利用IFS+TEXT函数组合实现在不同场景下计算完成率百分比并格式化显示。

1、IFS函数判断3个区间

①目标完成数值A2<0

目标完成数值A2<0时，完成率就是=2-实际完成数值/目标完成数值；

②目标完成数值A2=0

如果总数量为0，直接使用实际完成数值除以目标完成数值会导致除零错误（#DIV/0!）。为了避免这种情况，当总数量为0时，Excel单元格将显示"N/A"，而不是错误信息。这有助于保持数据的整洁和易于理解。

③目标完成数值A2>0,也就是除了上面2种场景，其它都是TRUE，返回结果为=实际完成数值/目标完成数值。

2、利用TEXT函数对百分比格式化显示

①TEXT函数：这个函数的作用是将数值按照指定的格式转换为文本。在这个公式中，TEXT函数将计算出的比率转换成百分比格式。

②"0.00%"：这是TEXT函数的格式代码。0.00%表示显示小数点后两位的百分比。例如，如果比率是0.25，那么显示的结果将是25.00%。

它来了，它来了，万能公式中的重点来了，当目标完成数值小于0时为什么完成率公式=2-实际完成数值/目标完成数值？

众所周知完成率等于实际完成数值占目标完成数值的百分比。

公式=实际完成数值/目标完成数值*100%

但是遇到目标完成数值是负数时，上面的公式就不灵了，这时就需要下面的公式。

公式=2-实际完成数值/目标完成数值*100%

比如实例中预算支出是-100，也可以看做是亏损100，当我们实际亏损了100，完成率就是100%；那么如果是亏损了110，那么完成率是多少呢，是110%或者是-110%，这显然是不合理不对的。

下面分几个场景帮大家理解和验证一下这个公式：

1、实际支出亏损70，也就是-70，也就是说我们实际上比预算“少亏损”了30。

也就等于说：实际比预算“多赚”30。

这时完成率应该是130%，就是在100%完成任务的情况下，又多挣了30。

完成率=[2-（-70/-100)]*100%=130%

2、实际支出亏损130，也就是-130，也就是说我们实际上比预算“多亏损”了30。

也就等于说：实际比预算“少赚”30。

这时完成率应该是70%，只完成了计划的70%。

完成率=[2-（-130/-100)]*100%=70%

3、实际没有亏损，还盈利了60，也可以说实际上比预算计划“少亏损”160

也就等于说：没有亏损100，实际比预算还“多赚”160。在100%完成任务后，又“多赚”160。

这时完成率应该是260%，

完成率=[2-（60/-100)]*100%=260%

当然上面的场景是为了帮助大家理解当“目标完成数值”是负数时公式的逻辑，其实，大家也可以直接套用公式即可。

（最终完善版）完成率公式：

=TEXT(IFS(目标完成数值<0,2-实际完成数值/目标完成数值,目标完成数值=0,"N/A",TRUE,实际完成数值/目标完成数值),"0.00%")

以上就是【桃大喵学习记】今天的干货分享~觉得内容对你有所帮助，别忘了动动手指点个赞哦~。大家有什么问题欢迎关注留言，期待与你的每一次互动，让我们共同成长！