二维码 购物车
部落窝在线教育欢迎您!

爷们福音:算老婆每次平均花费的Excel居家爱心公式!

 

作者:龚春光来源:部落窝教育发布时间:2018-10-10 14:49:05点击:205

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

今天分享的Excel居家爱心公式大家一定要珍藏!对于把Excel“用数据说话”的观念带回家的男人们、女人们来说,这个公式能让家庭更加美满,支出更平衡,因为它是一种智慧算法、爱心算法。绝不把眼睛盯在金额最高的那次、那几次消费上——这是爱心!也绝不把眼睛盯在金额最低的消费上——这是智慧!回到工作上,这个公式还能帮助你搞定招标价格关键值计算,复杂评分平均值统计,抽检样本平均值统计等等。

最近收到某粉丝求助,问题是如何统计老婆每次平均花费。

下图是这位粉丝提供的每次平均消费计算规则——态度很端正,爱心满满,充满了智慧啊!

 

某粉丝家庭成员每次消费金额计算规则

 

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

 

 

简单梳理一下:表中C列为当月每笔实际消费金额。现在需要针对不同消费笔数计算每次的平均消费额。

 

主要难点在于如何去除指定个数的最高和最低消费。这个问题解决后我们就可以通过IF函数进行判断返回关键数值X

 

下面我们将拆分所有判断条件,依次跟大家分享一下解决过程。

 

1.消费次数小于4的情况

消费笔数小于4的情况下则计算这几次消费额的平均金额,这个条件还是比较简单的。只需要通过COUNTAVERAGE这两个函数即可完成。

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

如下图所示:

 

 

公式解析:通过COUNTC:C)函数统计消费次数。然后使用IF函数判断是否满足小于4这个条件,如果满足条件则计算这几笔消费的平均金额,如果不满足条件则返回文字说明“不满足”。

 

2.消费次数小于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次则去掉两个最高消费和一个最低的消费后求平均消费金额

2SUM(LARGE(C:C,{1,2}))数组公式含义为通过LARGE函数返回第一个最大值和第二个最大值,然后通过SUM对这两个数据求和。(SUM(C:C)-MIN(C:C)-SUM(LARGE(C:C,{1,2})))/(COUNT(C:C)-3)表示所有消费金额汇总后减去2个最高消费以及一个最低消费后的平均金额。

 

4.消费次数小于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三键完成)

 

 

5.消费次数超过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)根据第一步中计算的最高消费笔数构建SUMLARGE(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个,笔者将在下篇为大家奉献更带劲的爱心公式。

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

说明: http://www.itblw.com/ewebeditor/uploadfile/20180914164059384001.png

相关推荐:

Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读

《Excel教程:sumifs函数常量数组简化公式

《SUM函数特殊应用:序号填充、提成比例计算、打印标签制作