excel技巧：在主工作表中汇总多个工作表中满足条件的值 _知识经验

我们可能熟悉使用INDEX、SMALL等在给定单列或单行数组的情况下，返回满足一个或多个条件的值的列表。这是一项标准的公式技术。
在《Excel公式练习32：将包含空单元格的多行多列单元格区域转换成单独的列并去掉空单元格》中，我们述了一种方法，给定由多个列组成的单元格区域，从该区域返回由所有非空单元格组成的单个列。可以很容易地验证，在该公式中的单个条件可以扩展到多个条件，因此，我们现在有了从一维数组和二维数组中生成单列列表的方法。
那么，可以更进一步吗？“三维”是经常应用于Excel中特定公式的通用术语，这些公式不仅可以对单列或单行进行操作，也可以对由多列或多行组成的单元格区域进行操作，还可以有效地对多个工作表进行操作。
本文提供了一种方法，在给定一个或多个相同布局的工作表的情况下，可以创建另一个“主”工作表，该工作表仅由满足特定条件的所有工作表中的数据组成。并且，这里不使用VBA，仅使用公式。
假设我们想从下面三个工作表中整理数据：
工作表Sheet1：

文章插图
图1
工作表Sheet2：

文章插图
图2
工作表Sheet3：

文章插图
图3
想要创建一个主工作表Master，其数据来源于上面三个工作表中列D中的值为“Y”的数据：

文章插图
图4
解决方案
首先，定义下面两个名称：
名称：Sheet3
引用位置：={“Sheet1″,”Sheet2″,”Sheet3”}
名称：Arry1
引用位置：=MMULT(0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets))))),TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”)))
可以根据实际情况，修改工作表列表和数据范围（D2:D10）。
在工作表Master的单元格G1中，输入下面的公式：
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
在工作表Master的单元格A2中输入下面的数组公式：
=IF(ROWS($1:1)>$G$1,””,INDEX(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!A2:F10”),SMALL(IF(INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!D2:D10″)=”Y”,ROW(INDIRECT(“1:”&$G$1))),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))),COLUMNS($A:A)))
向下和向右拖放公式至合适的位置。
工作原理
先看看相对简单的单元格G1中的公式，该公式用于确定返回结果的数量：
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
如果不熟悉跨多个工作表使用公式的技术，那么应记下使用INDIRECT的这种公式构造，因为它实际上是我们执行此类计算的唯一方法。上述公式转换为：
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&{“Sheet1″,”Sheet2″,”Sheet3″}&”‘!D2:D10″),”Y”))
然后，将这组代表工作表名称的文本字符串的两端连接，在后面是所使用的工作表区域（D2:D10），在前面用单个撇号连接。尽管在工作表的名称中不包含空格的情况下，并不需要这样，但是这样做将更好更通用。这样，公式转换为：