杏彩开户
栏目分类
热点资讯
关于杏彩开户

你的位置:杏彩开户 > 关于杏彩开户 > 写了老长老长的公式,不如潜心研究INDIRECT函数

写了老长老长的公式,不如潜心研究INDIRECT函数

发布日期: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函数实用终极帖

函数功底深不深,就看会的套路多不多。怎么样,来试一试吧!

这道统计题目很特别,攻克它用的都是硬核技巧!

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报。

友情链接:

Powered by 杏彩开户 @2013-2022 RSS地图 HTML地图