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

发布日期：2024-07-22 02:54 点击次数：170

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

【置顶公众号】或者【设为星标】及时接收更新不迷路

图片

小伙伴们好，今天来和大家大家分享一道比较烧脑的题目。原题目是这样子的：

图片

题目要求根据日期来统计值班的人数。由于每个日期中都有重复值和无人员值班的情况，因此这道题目还要去重及排除不相关数据。

这道题目不能使用COUNTIF函数来处理。如果题目中的数据在同一列中，那么我们就可以使用去重及按条件统计等相关的操作了。问题是，如何才能让数据排列在一列中呢？

这个时候我们其实可以使用多维引用。

图片

在单元格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函数用来降维

上面这条公式太长了。与其花大量时间写个长火车，不如来研究一下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-

图片