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

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

昨天，跟大家分享了《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个区间

①目标完成数值B2<0

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

②目标完成数值B2=0

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

③目标完成数值B2>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，也可以理解为“少赚”10，那么完成率是多少呢，是110%或者是-110%，这显然是不合理不对的。

下面分几个场景帮大家理解和验证一下目标完成数值是负数时的公式：

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

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

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

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

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

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

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

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

3、预算支出是-100，可看做是亏损100；实际没有亏损，还盈利了60，也可以说实际上比预算计划“少亏损”160

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

这时完成率应该是260%，

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

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

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

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

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