有哪些不为人知的公式简化技巧？这3个太牛X了！

点击蓝字【秋叶 Excel】👆

发送【交流】

立即加入秋叶 Excel 读者群！

本文作者：小花

本文审核：玛奇鹅

本文编辑：竺兰

在以前的篇章中，小花分享过一些利用数学逻辑简化公式的知识点。

比如，用 MAX 和 MIN 替换 IF，再比如，用 1/COUNTIF 完成不重复计数。

今天，小花继续发功，带来几个数学逻辑强大的 Excel 公式，一起来动动脑筋吧！

插值法求值

问题 1 来自一位地产运营的同事：

「请问老师，如何使完成率在 60%-100%之间，线性计 0-100 分，超过 100%计 100 分，低于 60%计 0 分？」

我们先用常规思路来拆解下这个插值法求值问题。

首先，我们需要判断大小，如果小于 60%，则等于 0，如果大于 100%，则等于 100。

其次，是对 60%-100%之间的数值，采用插值法按 0-100 分进行线性赋分。

按这种思路，我们可以使用 IF 函数嵌套公式来完成。

实际上，这位朋友也正是按照这个思路来设置公式的。

我们一起来看看，她是如何做的？

=IF(A2<60%,0,IF(A2>100%,100,TREND({0;100},{0.6;1},A2)))

▲ 左右滑动查看

不得不说，这个公式还是有亮点的。

它使用了趋势函数 TREND 来简化在 0-100 分中差值区分的方法。

但其实如果我们切换到数学角度来思考这个问题，你会发现公式的设置还可以更简单。

首先，我们不考虑 60%-100%的区间限制，直接将所有完成率插值法转化为得分。

得分

Y=（X-60%）*（100-0）/(100%-60%)=(X-0.6)*250

▲ 左右滑动查看

其次，我们需要排除无效分数。

因为当完成率小于 60%时，上式计算结果小于 0，当完成率大于 100%时，上式计算结果大于 100，这两种情况都是无效分数，需要分别用 0 或 100 替代。

只有当完成率在 60%到 100%之间，计算结果为 0-100 分，才是有效分数。

我们把所有可能的结果都绘制在一条数轴上，会更方便我们理解这个问题的数学本质。

你没看错，无论哪种情况，最终得分都是 0，Y 和 100 的中位数，神奇吧！

于是，中位数函数 MEDIAN顺理成章地成为这类插值问题的高级解法。

我们只需将 0，100 和 Y 的算法做为 MEDIAN 的三个参数，即可完成插值法公式的设置。

=MEDIAN(0,100,(A2-0.6)*250)

净胜分计算

问题 2 来自一位行政的小美女：

「请问老师，我要怎么快速用比分计算出净胜分？」

我们还是先用常规思路来拆解下这个问题。

首先，需要分别提取杠号「-」前后的数据，这里需要用到 LEFT，RIGHT，FIND 和 LEN 函数来完成。然后将两个数字进行相减。

需要使用这么多函数的嵌套公式，这可难倒我们的行政小美女了！

没事，我们一起来帮她写出这个公式：

=LEFT(B2,FIND("-",B2)-1)-RIGHT(B2,LEN(B2)-FIND("-",B2))

▲ 左右滑动查看

公式说明：FIND 函数返回杠号「-」在 B2（「2-1」）中出现的位置 2，LEN 返回 B2 文本的长度 3，FIND-1（2-1=1）就是杠号「-」左边数字的长度，LEN-FIND（3-2）则是杠号「-」右边数字的长度。

再分别使用 LEFT 和 RIGHT 将左右边指定长度的数字截取出来，相减即可得到净胜分。

切换到数学角度。

「2-1」或许会有点陌生，但如果加个符号，「2-1i」，就显得非常眼熟了，没错，这在数学上，叫做复数。

计算净胜分时，我们可以用复数的除法运算来处理。

知识点忘记了？没事，我们一起复习下。

如果用复数 1+i 作为除数 c+di，即 c=1，d=1，那么：

了解了复数的除法运算，解决净胜分问题就很简单了。

由 B2 构建的复数「2-1i」作为被除数 a+bi，1+i 作为除数，则 2-1i/（1+i）的实部为（2-1）/2=1 的两倍就是净胜分。

由此，我们需要使用到两个函数，IMREAL 函数和 IMDIV 函数。

我们只需将 B2&"i"和"1+i"作为 IMDIV 的两个参数，计算出商，再嵌套 IMREAL 来取其实部，借此完成净胜分公式的设置。

=IMREAL(IMDIV(B2&"i","1+i"))*2

最接近值

问题 3 来自一名研究生朋友：

「老师，我想找出预测值最接近平均值的专家，可以使用自动计算吗？」

同样地，我们还是先尝试用常规思路来剖析并解决这个问题。

❶ 需要求均值，使用 AVERAGE 即可轻松完成：

① 均值公式

AVERAGE(B2:B6)

❷ 需要计算每个预测值与均值之差的绝对值以及这些绝对值的最小值，我们使用 ABS 和 MIN 来完成。

② 绝对值公式

ABS(B2:B6-①)

③ 最小值公式

=MIN(②)

❸ 需要找到最小值所在位置的序数，我们使用 MAX+IF+ROW 来完成。

④ 位置值公式

MAX(IF(②=③,ROW(1:5),))

公式说明：通过 IF 函数比对绝对值和最小值，相等的返回对应的序数（ROW(1:5)计算得出一组 1-5 的序数，作为逻辑为 TRUE 的返回值）；不相等则返回空（即 0）。

由此，IF 函数返回一组 0 和某一个 1-5 的序数组成的数组，再用 MAX 取得最大值，就是绝对值等于最小值的预测值所在的位置序数。

❹ 最后，我们根据序数值查询得到专家姓名，此时使用 INDEX 最为简便。

⑤ 查询公式

{=INDEX(A2:A6,④)}

需要注意的是，由于此处运用的是数组运算，必须同时按【Ctrl+Shift+Enter】 来完成公式输入。

我们将上述思路过程整合起来，就形成了一个完整的最接近值公式：

{=INDEX(A2:A6,MAX(IF(ABS(B2:B6-AVERAGE(B2:B6))=MIN(ABS(B2:B6-AVERAGE(B2:B6))),ROW($1:$5),)))}

▲ 左右滑动查看

如此复杂的公式，能否使用数学知识来优化呢？当然可以。

频数统计函数 FREQUENCY 就可以搞定最接近值问题。

咋一看，可能你还理解不了 FREQUENCY 的计算原理，我稍加说明一下，你就懂了。

❶ FREQUENCY 先将参数 Bins 间隔数组插入到 Data 统计数组中作为间隔点。

❷ 根据间隔点，统计数轴上每一段中的数值个数，并将该频数赋予间隔点，重新按 Bins 间隔数组各间隔点数值顺序排列，即可得到 FREQUENCY 的最终返回值。

这里需要注意，插值点乱序，并不影响 FREQUENCY 根据间隔点统计频数，间隔点对应的返回值始终为该间隔段的数值个数。

我们打乱一下上述案例的顺序，你会发现，统计结果也是正确的。

了解了 FREQUENCY 统计的数学理论基础，再来看问题 3——最接近值问题，新的解决思路就呼之欲出了。

① 首先，还是同样，需要计算均值和绝对值，

ABS(B2:B6-AVERAGE(B2:B6))

② 其次，将 FREQUENCY 函数的第一个参数 data_array 设置为 0，将上述①式作为的第二个参数，由此，完成了对数组{0}的频数统计。

由于①式总是大于等于 0 的，所以 0 在数轴上必然落于①式最小值所在的间隔段，也就是说，FREQUENCY 的返回值仅在①式最小值位置返回 1，其余均为 0。

FREQUENCY(0,①)

③FREQUENCY 返回数组中唯一的 1 所在的位置即为最接近值。于是，我们立刻想到经典的 LOOKUP（1,0/...)用法。

=LOOKUP(1,0/②,A2:A6)

将上述思路过程连贯起来，最接近值公式就完成啦。

=LOOKUP(1,0/FREQUENCY(0,ABS(B2:B6-AVERAGE(B2:B6))),A2:A6)

总结一下

本文，我们用数学的逻辑解决了 3 个 Excel 疑难问题：

❶ 用 MEDIAN 计算中位数，轻松计算插值法考核得分；

❷ 先用 IMDIV 求复数的商，再用 IMREAL 取实部，巧妙求出净胜分；

❸ 使用 FREQUENCY 统计频率，判断出 0 值位置，直接锁定最接近值。

数学知识如此强大，你裂开了吗？你还见过其他让你的逻辑处理器崩溃的公式吗？不妨留言吐槽吐槽吧！

看完文章，同学们是不是感觉还没有学够？

Excel 需要更深度系统的学习，不然你就完全没办法会到它的魅力。

机会通常是留给有准备的人，行动起来！一起来成就更好的自己！

秋叶家的《3 天 Excel 集训体验营》，双十一来临之际，限时特惠~  

扫码下方二维码报名后添加班班即可参与~  

《秋叶 Excel 3 天集训营》

早学习，早受益！

前 50 名仅需 1 元

不再被加班支配，充实自我就现在！

报名即送【35 个常用函数说明】

赶紧扫码预约吧！👇👇👇

大家一起学起来！

欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

↓↓↓

遇到有价值的文章

不放过 !

👇👇👇

动动小手

分享给朋友~

👇👇👇