关注【秋叶 Excel】👆发送【插件】
免费领 Excel 必备 4 大插件
作者：小花
审核：玛奇鹅

编辑：毛毛
在以前的篇章中，小花分享过一些利用数学逻辑简化公式的知识点。
比如，用 MAX 和 MIN 替换 IF，再比如，用 1/COUNTIF 完成不重复计数。
今天，小花继续发功，带来几个数学逻辑强大的 Excel 公式，一起来动动脑筋吧！
现在关注【
秋叶 Excel
】

发送关键词【管理】
免费领 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】
回复关键词【礼包】
还能免费领办公神器
Office 模板和免商字体！
↓↓↓
继续阅读
阅读原文