学习Excel技术,关注微信公众号:
excelperfect
在《Excel公式技巧54:在多个工作表中查找最大值最小值》中,我们在MAX/MIN函数中使用多工作表引用来获取最大值/最小值。现在更进一步,我们想要获取最大值/最小值所在的工作表名称。
我们仍然使用上篇文章的示例,工作表Sheet1、Sheet2和Sheet3中的数据分别如下图1至图3所示。
图1
图2
图3
我们知道这3个工作表中的最小值1位于工作表Sheet2,最大值150位于工作表Sheet3,那么如何使用公式获取对应的工作表名称呢?
首先,在工作表result的单元格区域A2:A4中分别输入工作表名称Sheet1、Sheet2、Sheet3。在单元格D2中输入数组公式:
=INDEX(A2:A4,MATCH(TRUE,COUNTIF(INDIRECT("'"& A2:A4 & "'!A1:D4"),C2)>0,0))
结果如下图4所示。
图4
公式中的:
COUNTIF(INDIRECT("'" & A2:A4& "'!A1:D4"),C2)
分别统计各个工作表中值为单元格C2中的值的个数,得到数组:
{0;1;0}
然后判断该数组元素是否大于0,得到数组:
{FALSE;TRUE;FALSE}
代入MATCH函数中,得到:
MATCH(TRUE, {FALSE;TRUE;FALSE},0)
显然,返回2。
代入INDEX函数中,得到:
INDEX(A2:A4,2)
结果为单元格A3中的值:
Sheet2
同理,在单元格D3中的数组公式为:
=INDEX(A2:A4,MATCH(TRUE,COUNTIF(INDIRECT("'"& A2:A4 & "'!A1:D4"),C3)>0,0))
得到结果:
Sheet3
欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。
欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料。
完美Excel社群2020.9.22动态
#Excel公式练习# 使用公式对数字排序(续)