瞧瞧人家Excel函数高手的二人世界：一面爱心满满一面狠抓消费可控制
全套Excel视频教程，扫码观看
编按：
连续的节日，对Excel的表哥表妹的家庭生活是个考验。来学学人家函数高手的生活智慧吧：一面喊着老婆第一，一面狠狠算着老婆的消费……
重点不是公式怎么写，而是看高手的核算规则！！
最近聊天说到节日消费，有高手分享了自己的经验：我永远支持老婆消费，但我也总是把消费单统计给她看……结果你们知道的，老婆一面对我满心的打kiss，一面也开始自己节制消费……
下图是这位高手提供的家庭消费次平均花费统计——态度很端正，爱心满满，充满了智慧啊！
某粉丝家庭成员每次消费金额计算规则
我将这个文件中的内容提炼出来做了一个例表，如下图所示。
简单梳理一下：表中C列为当月每笔实际消费金额。现在需要针对不同消费笔数计算每次的平均消费额。
主要难点在于如何去除指定个数的最高和最低消费。这个问题解决后我们就可以通过IF函数进行判断返回关键数值X。
下面我们将拆分所有判断条件，依次跟大家分享一下解决过程。
消费笔数小于4的情况下则计算这几次消费额的平均金额，这个条件还是比较简单的。只需要通过COUNT、AVERAGE这两个函数即可完成。
函数公式：=IF(COUNT(C:C)<4,AVERAGE(C:C),“不满足”)
如下图所示：
公式解析：通过COUNT（C:C）函数统计消费次数。然后使用IF函数判断是否满足小于4这个条件，如果满足条件则计算这几笔消费的平均金额，如果不满足条件则返回文字说明“不满足”。
如果消费次数小于6次去掉最高的一次消费后求剩余的消费金额平均值。
函数公式：=IF(COUNT(C:C)<6,(SUM(C:C)-MAX(C:C))/(COUNT(C:C)-1),"不满足")
公式解析：通过COUNT(C:C)函数统计消费次数。如果消费次数小于6次则返回消费总额减去最高一次消费后求平均金额，如果不满足条件则返回文字说明“不满足”。
如果消费次数小于9就要去掉两个最高消费和一个最低的消费后求平均消费金额。
这个条件相比前面两个条件难度增加了，我们需要通过LARGE函数求最高的2次消费金额之和。
函数公式：{=IF(COUNT(C:C)<9,(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2})))/(COUNT(C:C)-3),"不满足")}（此函数为数组公式，输入完成后需要按ctrl+shift+enter三键完成）
公式解析：
（1）通过COUNT(C:C)函数统计消费次数，然后使用IF函数判断消费次数是否小于9次。如果小于9次则去掉两个最高消费和一个最低的消费后求平均消费金额
（2）SUM(LARGE(C:C,{1,2}))数组公式含义为通过LARGE函数返回第一个最大值和第二个最大值，然后通过SUM对这两个数据求和。(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2})))/(COUNT(C:C)-3)表示所有消费金额汇总后减去2个最高消费以及一个最低消费后的平均金额。
其实这条和第3条基本一致，主要的区别在于第3条是去掉两个最高消费金额，而这里是去掉3个最高消费金额。
所以数组公式SUM(LARGE(C:C,{1,2}))需要改成SUM(LARGE(C:C,{1,2,3}))即可。
函数公式：{=IF(COUNT(C:C)<20,(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2,3})))/(COUNT(C:C)-4),"不满足")} （此函数为数组公式，输入完成后需要按ctrl+shift+enter三键完成）
如果4个条件都不满足那么就作为其他。这里则需要做两个修正：
（1）消费金额降序后取出最高的15%消费金额，举例如果消费笔数是100家那么就要降序去掉前面15家;
（2）对报价升序排列去掉10%最低的。
这个条件相比前面的难度又增加了，因为我们需要让LAGRE函数的第二个参数根据消费的次数实时变化。
函数公式：{=((SUM(C:C)-SUM(LARGE(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*15%,0)))))-SUM(SMALL(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*10%,0)))))))/(COUNT(C:C)-ROUND(COUNT(C:C)*15%,0)-ROUND(COUNT(C:C)*10%,0))}（此公式为数组公式，输入完成后按ctrl+shift+enter三键完成）
公式解析：
（1）首先通过COUNT(C:C)*15%来计算需要去除的最高的N笔消费，这里需要通过ROUND函数进行取整。最终函数公式：ROUND(COUNT(C4:C100)*15%,0)。同理我们通过ROUND(COUNT(C4:C100)*10%,0)来计算去掉最低的N笔消费。
（2）根据第一步中计算的最高消费笔数构建SUM（LARGE(C1:C100,ROW(1:N)）这样的数组公式，我们通过ROW函数来作为LARGE的第二个参数，这样我们就能达到动态求和的目的。其中N为第一步中计算的去掉最高消费笔数，通过INDIRECT函数引用。最终通过SUM(LARGE(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*15%,0)))))函数公式完成最高的N笔消费金额汇总。
（3）同理通过SMALL函数完成最低的N笔消费汇总。函数公式：SUM(SMALL(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*10%,0)))))))。
（4）最后用消费总额减去（2）和（3）的计算结果求平均消费金额即可。注意：求平均时要通过COUNT(C:C)减去最高的N笔消费和最低的N笔消费，不能直接除以所有消费笔数。即(COUNT(C:C)-ROUND(COUNT(C:C)*15%,0)-ROUND(COUNT(C:C)*10%,0))这部分函数公式。
最后我们将这几个条件的函数公式完成合并嵌套。函数公式：=IF(COUNT(C:C)<4,AVERAGE(C:C),IF(COUNT(C:C)<6,(SUM(C:C)-MAX(C:C))/(COUNT(C:C)-1),IF(COUNT(C:C)<9,(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2})))/(COUNT(C:C)-3),IF(COUNT(C:C)<20,(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2,3})))/(COUNT(C:C)-4),((SUM(C:C)-SUM(LARGE(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*15%,0)))))-SUM(SMALL(C:C,ROW(INDIRECT("1:"&ROUND(COUNT(C:C)*10%,0)))))))/(COUNT(C:C)-ROUND(COUNT(C:C)*15%,0)-ROUND(COUNT(C:C)*10%,0))))))
看到最后的公式，估计很多人都要崩溃了：难道真的是爱心越大，公式越长吗？
有没有简易的公式？有，老婆消费次数越少，公式越简单（希望粉丝的老婆不要看到这里啊~~）……最初的固定个数求和相对简单，但是后面消费次数超过20后，要求去掉最高消费和最低消费为动态数值时难度增大……
为爱心公式点赞超过200个，笔者将在下篇为大家奉献更带劲的爱心公式。