发布日期:2024-07-22 02:54 点击次数:175
送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
图片
小伙伴们好,今天来和大家大家分享一道比较烧脑的题目。原题目是这样子的:
图片
题目要求根据日期来统计值班的人数。由于每个日期中都有重复值和无人员值班的情况,因此这道题目还要去重及排除不相关数据。
这道题目不能使用COUNTIF函数来处理。如果题目中的数据在同一列中,那么我们就可以使用去重及按条件统计等相关的操作了。问题是,如何才能让数据排列在一列中呢?
01这个时候我们其实可以使用多维引用。
图片
在单元格G2中输入下列公式,三键回车并向下拖曳即可。
=COUNT(0/(MATCH(T(INDIRECT(TEXT(MOD(SMALL(IF((LEN($B$2:$C$8)>1)*($A$2:$A$8=E2),ROW($A$2:$A$8)/1%+COLUMN(B:C)*10001),ROW(INDIRECT("1:"&COUNT(0/((LEN($B$2:$C$8)>1)*($A$2:$A$8=E2)))))),10^4),"r0c00"),)),T(INDIRECT(TEXT(MOD(SMALL(IF((LEN($B$2:$C$8)>1)*($A$2:$A$8=E2),ROW($A$2:$A$8)/1%+COLUMN(B:C)*10001),ROW(INDIRECT("1:"&COUNT(0/((LEN($B$2:$C$8)>1)*($A$2:$A$8=E2)))))),10^4),"r0c00"),)),)=ROW($1:$9)))
公式相对比较复杂,稍稍做一下解释吧。
T(INDIRECT(TEXT(MOD(SMALL(IF((LEN($B$2:$C$8)>1)*($A$2:$A$8=E2),ROW($A$2:$A$8)/1%+COLUMN(B:C)*10001),ROW(INDIRECT("1:"&COUNT(0/((LEN($B$2:$C$8)>1)*($A$2:$A$8=E2)))))),10^4),"r0c00"),))这部分是这条公式的核心部分。它提取7月15所有的人员,结果为{"张三";"李四";"张三";"张三";"赵六"}。在这个基础上就可以使用MATCH函数对其进行不重复人员的提取了,可以使用IF+MATCH+ROW的经典操作来完成
COUNT(0/((LEN($B$2:$C$8)>1)*($A$2:$A$8=E2)))部分来确定需要提取的人员的数量
ROW(INDIRECT("1:"&COUNT())是一个基本操作技巧,它返回一个自然数序列作为SMALL函数的第二参数,作用是精确提取值班人员,屏蔽错误值
最外层的T函数用来降维
02上面这条公式太长了。与其花大量时间写个长火车,不如来研究一下INDIRECT函数。
图片
在单元格K2中输入下列公式,三键回车并向下拖曳即可。
=COUNT(1/FREQUENCY(ROW(A:A),MATCH(B$2:C$8,T(INDIRECT(TEXT(ROW($1:$90),"r0c0"),)),)*(A$2:A$8=E2)*(B$2:C$8<>"无")))-1
注意,这条公式不能写在前9列中,否则会引起循环引用。
思路:
由于源数据在单元格区域A1:C8中,因此可以试着构造一个地址的内存数组。TEXTROW ($1:$90),"r0c0")就是这个目的
接下来使用INDIRECT函数对上述地址中的内容引用。这一步其实就是将源数据中所有的数据都放到了一列中,N函数降维后就能利用MATCH函数来查找源数据在这列中的位置信息了。这部份就替代了上个公式中多维引用那部份
再下来,就可以利用FREQUENCY函数来多条件统计了。这部份在FREQUENCY函数相关的帖子有详细的介绍,这里不再赘述了
从今天这个例子中,INDIRECT函数通过对单元格的引用,极大地缩短了公式长度,降低了复杂程度。多维引用确实是很好的方法,但也一定要用在合适的地方。
-END-
图片
推荐阅读处理较难的题目时,换一个思路也许就柳暗花明了!
什么问题SUBSTITUTE函数也无法解决?别急,还有一件秘密武器!
总结篇--SUBSTITUTE函数实用终极帖
函数功底深不深,就看会的套路多不多。怎么样,来试一试吧!
这道统计题目很特别,攻克它用的都是硬核技巧!
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报。