Excel中的几个难题,我是如何轻松搞定的
两处闲悠 2020-11-27 04:48:35

问题难不难,除了知识储备,还有看问题的角度!

我们来看第一个“难题”,体育测试,每个人测试数次,现在需要根据每个人测试顺序,添加编号,比如张三,第一次是1,第二次就是2,依次类推!

难题1 | 按照出现的顺序编号




=COUNTIF($A$2:A2,A2)



Excel中的几个难题,我是如何轻松搞定的


知识点(已写专题)

1、COUNTIF函数的使用:函数 | COUNTIF用法大全

2、单元格引用方式:基础 | Excel中单元格的引用方式,读这篇就够了!

该方法同样适用于,重名检测,只要出现大于1的就是重复的姓名。

比如我们只需要第一次或者第二次的出现的,都可以直接筛选,非常方便!

比如我们要查找第二次的成绩,直接筛选2即可!


Excel中的几个难题,我是如何轻松搞定的


本案例如果是新手,可能会出现逐个姓名筛选,然后一个一个编号!如果数据较大,是一个很大的工作量!

就这上面的案例,我们继续,求出每个人最高的三次成绩平均值,如果不足三次直接取平均值!

难题2 | 最高的三次成绩平均值



第一步:按照根据姓名和成绩排序

注意成绩降序排序,从大到小!


Excel中的几个难题,我是如何轻松搞定的


第二步:使用公式AVERAGEIFS


=ROUND(AVERAGEIFS(C:C,B:B,"<=3",A:A,E2),)

Excel中的几个难题,我是如何轻松搞定的



Excel中的几个难题,我是如何轻松搞定的


如果你觉得上面的做法不够酷,可以使用公式

▼我是一条数组公式,请使用Ctrl+Shift+Enter录入我

=ROUND(AVERAGE(LARGE(IF($A$2:$A$15=$E2,$C$2:$C$15),ROW(INDIRECT("1:"&MIN(3,COUNTIF(A:A,E2)))))),)


有测试不满三次的情况!就按实际次数取平均值!用3和测试次数取小即可


Excel中的几个难题,我是如何轻松搞定的


当然,如果你是一个数据分析人员,懂Power Pivot,你还可以使用自己认为更酷、更合适的DAX来处理

▼最高三次成绩平均值度量值

=IF(HASONEVALUE('表1'[姓名]),ROUND(CALCULATE(AVERAGEX(TOPN(3,'表1','表1'[测试成绩],DESC),'表1'[测试成绩])),0),BLANK())



Excel中的几个难题,我是如何轻松搞定的


小编是一个DAX初学者!如果有什么写的不适合的地方,欢迎指正!

最近我们也一直在分享Power Query,要不我们也来写一下!

▼Power Query中的M函数依据简洁

= Table.Group(更改的类型,"姓名",{"最高三次成绩平均值",each Number.Round(List.Average(List.MaxN([测试成绩],3)),0)})



Excel中的几个难题,我是如何轻松搞定的


List.MaxN处理此类问题真是非常的简洁!M函数中有很多List函数,他们极大的丰富了数据的提取、整理和聚合!

难题3 | 一共有几人测试?



每个人有多次测试,所以应该对姓名去重计数

最简单的 ,自然就是数据透视表,拖拽一下即可搞定!


Excel中的几个难题,我是如何轻松搞定的


你依然觉得,不够酷,非要使用函数处理也行!


=SUMPRODUCT(1/COUNTIF($A$2:$A$15,$A$2:$A$15))


公式解读:COUNTIF去重计数解读


Excel中的几个难题,我是如何轻松搞定的


当然说回来,什么方法好?什么最简单!适合自己就是最好的!每个人所掌握的知识和方向不同,普通初入职场的办公小白,统一推荐第一种,能解决问题,比什么花里胡哨都重要!
版权声明
本文为[两处闲悠]所创,转载请带上原文链接,感谢
https://www.seoxiehui.cn/article-267788-1.html
相似文章

2021-08-09