外链论坛

 找回密码
 立即注册
搜索
查看: 18|回复: 2

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

[复制链接]

3037

主题

3万

回帖

9910万

积分

论坛元老

Rank: 8Rank: 8

积分
99109078
发表于 2024-10-2 14:49:45 | 显示全部楼层 |阅读模式

微X扫码观看全套Excel、Word、PPT视频

下图是这位粉丝供给的每次平均消费计算规则。

我将这个文件中的内容提炼出来做了一个例表,如下图所示。

简单梳理一下:表中C列为当月每笔实质消费金额。此刻需要针对区别消费笔数计算每次的平均消费额。

重点难点在于怎样去除指定个数的最高和最低消费。这个问题处理咱们能够经过IF函数进行判断返回关键数值X。

下面咱们将拆分所有判断要求,依次跟大众分享一下处理过程。

消费次数少于4的状况

消费笔数少于4的状况下则计算这几次消费额的平均金额,这个要求还是比较简单的。只需要经过COUNT、AVERAGE这两个函数就可完成。

函数公式:=IF(COUNT(C:C)<4,AVERAGE(C:C),"不满足")

如下图所示:

公式解析:经过COUNT(C:C)函数统计消费次数。而后运用IF函数判断是不是满足少于4这个要求倘若满足要求则计算这几笔消费的平均金额,倘若不满足要求则返回文字说明“不满足”。

消费次数少于6的状况

倘若消费次数少于6次去掉最高的一次消费后求剩余的消费金额平均值。

函数公式:=IF(COUNT(C:C)<6,(SUM(C:C)-MAX(C:C))/(COUNT(C:C)-1),"不满足")

公式解析:经过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三键完成)

公式解析:

(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三键完成)

消费次数超过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三键完成)

公式解析:

(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))))))

【总结】

看到最后的公式,估计非常多人都要崩溃了。

简易的公式?有,其实消费次数越少,公式越简单。最初的固定个数求和相对简单,然则后面消费次数超过20后,需求去掉最高消费和最低消费为动态数值时难度增大……

扫一扫添加老师微X

在线咨询Excel课程

Excel教程关联举荐

天天核对上千条数据,从未看错一行!只因运用了这个Excel神器

一样是countifs函数,为何同事却使得比你好?原由这儿

Excel教程:媲美excel查询替换,却少有人晓得它!

想要跟随滴答老师全面系统学习Excel,不妨关注《1星期Excel直通车》视频课《Excel极速贯通班》。

1星期Excel直通车》视频课

包括Excel技巧、函数公式、

数据透视表、图表。

一次购买,永久学习。

最实用接地气的Excel视频课

1星期Excel直通车》

风趣易懂,快速有效带您7天学会Excel

38 节视频大课

(已更新完毕,可永久学习)

理论+实操一应俱全

主讲老师: 滴答

Excel技术大神,资深培训师;

课程粉丝100万+;

研发有《Excel小白脱白系列课》

        《Excel极速贯通班》。

原价299元

限时特价99元,随时涨价

少喝两杯咖啡,少吃两袋零食

就能习得受用一辈子的Excel职场技能!

  长按下面二维码立即购买学习

购课后,加客服微X:blwjymx2领取练习课件

让工作提速百倍的「Excel极速贯通班」

↓ 点击阅读原文,可直接购买。    





上一篇:Excel教程:excel多需需要平均值工作案例
下一篇:PPT知识贩卖——关于表格数据的导入设置技巧
回复

使用道具 举报

3039

主题

3万

回帖

9606万

积分

论坛元老

Rank: 8Rank: 8

积分
96065878
发表于 2024-10-4 16:01:39 | 显示全部楼层
你的见解独到,让我受益匪浅,非常感谢。
回复

使用道具 举报

2983

主题

3万

回帖

9910万

积分

论坛元老

Rank: 8Rank: 8

积分
99109240
发表于 2024-10-16 17:14:18 | 显示全部楼层
感谢楼主的分享!我学到了很多。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

站点统计|Archiver|手机版|小黑屋|外链论坛 ( 非经营性网站 )|网站地图

GMT+8, 2024-11-18 11:41 , Processed in 0.154362 second(s), 21 queries .

Powered by Discuz! X3.4

Copyright © 2001-2023, Tencent Cloud.