Excel教程：你说求平均值很简单？那你把这个做出来瞧瞧-天涯论坛

1fy07h 发表于 2024-10-2 14:49:45

Excel教程：你说求平均值很简单？那你把这个做出来瞧瞧

微X扫码观看全套Excel、Word、PPT视频
<img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;">
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMySq3RV8kgfHv7ATM9bFWeuSaPbTa4NeibP73k3icKmD0bNiaqLebXlP1OicQFvNT76SYRaz4nKzqRqXA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">下图是这位粉丝供给的每次平均消费计算规则。
<img src="https://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMy7AaD0faIG11xFb5TqJRtj08Y5ibW9aXndjwAzlapr8hX3ztlqyR1zQC5COp3m1cOH8mTov6TAy1Q/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
我将这个文件中的内容提炼出来做了一个例表，如下图所示。
<img src="https://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMy7AaD0faIG11xFb5TqJRtjiaCaOWWlGibB4ezuENicrwHCbYEWFtNv4cYnVf7nXzHDZ7xGrxTdQnP8A/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
简单梳理一下：表中C列为当月每笔实质消费金额。此刻需要针对区别消费笔数计算每次的平均消费额。
重点难点在于怎样去除指定个数的最高和最低消费。这个问题处理后咱们就能够经过IF函数进行判断返回关键数值X。
下面咱们将拆分所有判断要求，依次跟大众分享一下处理过程。１消费次数少于4的状况
消费笔数少于4的状况下则计算这几次消费额的平均金额，这个要求还是比较简单的。只需要经过COUNT、AVERAGE这两个函数就可完成。
函数公式：=IF(COUNT(C:C)<4,AVERAGE(C:C),"不满足")
如下图所示：
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMy7AaD0faIG11xFb5TqJRtjwHDBx7icV1Vhiao9dqngoicrFBLn3jgVgNpsXCmnnoiazYLWhpz6vk86zg/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
公式解析：经过COUNT(C:C)函数统计消费次数。而后运用IF函数判断是不是满足少于4这个要求，倘若满足要求则计算这几笔消费的平均金额，倘若不满足要求则返回文字说明“不满足”。２消费次数少于6的状况
倘若消费次数少于6次去掉最高的一次消费后求剩余的消费金额平均值。
函数公式：=IF(COUNT(C:C)<6,(SUM(C:C)-MAX(C:C))/(COUNT(C:C)-1),"不满足")
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMy7AaD0faIG11xFb5TqJRtjtE4TjGjLOZJRBeHFK7zjVvBVOhicqk3gFC58PjOr8noRP5PArPz8xwA/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
公式解析：经过COUNT(C:C)函数统计消费次数。倘若消费次数少于6次则返回消费总额减去最高一次消费后求平均金额，倘若不满足要求则返回文字说明“不满足”。3消费次数少于9的状况
倘若消费次数少于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三键完成）
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMy7AaD0faIG11xFb5TqJRtjZEkhpH8pqaSTqlO2d6dic28R1DMFfoJhic3yXpia7VfdRxeXYWg8WHSLw/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
公式解析：
（1）经过COUNT(C:C)函数统计消费次数，而后运用IF函数判断消费次数是不是少于9次。倘若少于9次则去掉两个最高消费和一个最低的消费后求平均消费金额
（2）SUM(LARGE(C:C,{1,2}))数组公式含义为经过LARGE函数返回第1个最大值和第二个最大值，而后经过SUM对这两个数据求和。(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2})))/(COUNT(C:C)-3)暗示所有消费金额汇总后减去2个最高消费以及一个最低消费后的平均金额。４消费次数少于20的状况
其实这条和第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三键完成）
<img src="https://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMy7AaD0faIG11xFb5TqJRtjFLnML9tsPzz6yzdNL1YlrRTPdhpnU4rb7tqU1575wSTRiaFX4aQfsxQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">５消费次数超过20的状况
倘若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三键完成）
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMy7AaD0faIG11xFb5TqJRtjQ5Tpibia4upRfoNMn55NIGEY0WJibMiavHSNzKgclbQR1hvqKGtfKxUbMA/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
公式解析：
（1）首要经过COUNT(C:C)*15%来计算需要去除的最高的N笔消费，这儿需要经过ROUND函数进行取整。最后函数公式：ROUND(COUNT(C4:C100)*15%,0)。同理咱们经过ROUND(COUNT(C4:C100)*10%,0)来计算去掉最低的N笔消费。
（2）按照第1步中计算的最高消费笔数构建SUM(LARGE(C1:C100,ROW(1:N))这般的数组公式，咱们经过ROW函数来做为LARGE的第二个参数，这般咱们就能达到动态求和的目的。其中N为第1步中计算的去掉最高消费笔数，经过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))))))
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMy7AaD0faIG11xFb5TqJRtjfeLFeAQoR57UggfiaBia0QqwTcN4s1gvgztB3aMr8dSBWwKQvzYeqKTA/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">【总结】
看到最后的公式，估计非常多人都要崩溃了。
有无简易的公式？有，其实消费次数越少，公式越简单。最初的固定个数求和相对简单，然则后面消费次数超过20后，需求去掉最高消费和最低消费为动态数值时难度增大……扫一扫添加老师微X
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMyYb0ibwdwET82juoHrUumhsLPhic0z905HzLwLsyrPtwAGQUjnLSr8YlXa2vSPElNvR4Wq1VveOfgA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
在线咨询Excel课程<img src="https://mmbiz.qpic.cn/mmbiz_png/b96CibCt70iabwjyojLhA03PtxUnkNPREnYMIXQoTgtW6libdMibQVNic3eZhU08VyWfSQWdI82zIYSzSTq4Y3YV9nQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
Excel教程关联举荐
<a style="color: black;">天天核对上千条数据，从未看错一行！只因运用了这个Excel神器</a>
<a style="color: black;">一样是countifs函数，为何同事却使得比你好？原由在这儿！</a>
<a style="color: black;">Excel教程：媲美excel查询替换，却少有人晓得它！</a>
想要跟随滴答老师全面系统学习Excel，不妨关注《1星期Excel直通车》视频课或《Excel极速贯通班》。
《1星期Excel直通车》视频课
包括Excel技巧、函数公式、
数据透视表、图表。
一次购买，永久学习。
最实用接地气的Excel视频课
《1星期Excel直通车》
风趣易懂，快速有效，带您7天学会Excel
38 节视频大课
（已更新完毕，可永久学习）
理论+实操一应俱全
主讲老师： 滴答
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMw9HqsFgNibg3wrgklctzmiaton0P5Rr0efiaGAzyScpqGmtKMTX4m2HhJh3Kalv4BqrRgmDsZGzLcWw/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
Excel技术大神，资深培训师；
课程粉丝100万+；
研发有《Excel小白脱白系列课》
        《Excel极速贯通班》。
原价299元
限时特价99元，随时涨价
少喝两杯咖啡，少吃两袋零食
就能习得受用一辈子的Excel职场技能！
  长按下面二维码立即购买学习
<img src="https://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMx9yReibpHJNVMvB4RK0ibylhqJV98X8KPzyuDoGR91TqrCvRqWORseRvyAxNs4lZj72RITRTgeV1Kg/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
购课后，加客服微X：blwjymx2领取练习课件
<img src="https://mmbiz.qpic.cn/mmbiz_gif/rsVOmqahQG2qmwem3aaV0rPAvnpEurHH2qpvdBuBKE0QIjPlNkjEcHkbWib67hjjzvrILvMiaVAbkcgnKWQicYOvQ/640?tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;">
让工作提速百倍的「Excel极速贯通班」
↓ 点击阅读原文，可直接购买。

b1gc8v 发表于 2024-10-4 16:01:39

你的见解独到，让我受益匪浅，非常感谢。

7wu1wm0 发表于 2024-10-16 17:14:18

感谢楼主的分享！我学到了很多。

页: [1]

天涯论坛's Archiver

Excel教程：你说求平均值很简单？那你把这个做出来瞧瞧