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

=SUMPRODUCT(COUNTIF(INDIRECT({“‘Sheet1’!D2:D10″,”‘Sheet2’!D2:D10″,”‘Sheet3’!D2:D10″}),”Y”))
因为COUNTIF函数能够操作三维单元格区域，并且SUMPRODUCT函数提供了必要的强制转换，使得INDIRECT函数返回一组单元格引用，而不仅仅是一个，因此公式转换为：
=SUMPRODUCT({3,2,1})
其中数组的值由3、2、1组成，与工作表Sheet1、Sheet2、Sheet3的列D中包含“Y”的数量一致。该公式的最后结果为：
6
接下来，看看单元格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)))
在IF函数中的前半部分很简单，如果拖放的行数超过了可能获得的结果数量，则为空。
在公式中使用了定义的名称Arry1：
=MMULT(0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets))))),TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”)))
这种公式构造可以有效地动态生成汇总小计，并且是使用标准的SUBTOTA/OFFSET函数组合的替代方法。
依次看看传递给MMULT函数的数组。第一个是：
0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets)))))
转换为：
0+(ROW(INDIRECT(“1:”&3))>=TRANSPOSE(ROW(INDIRECT(“1:”&3))))
转换为：
0+({1;2;3}>=TRANSPOSE({1;2;3}))
转换为：
0+({1;2;3}>={1,2,3})
两个正交数组进行比较，一个是3行1列，一个是1行3列，得到一个3行3列的数组，该数组由9个TRUE/FALSE值组成：
0+({TRUE,FALSE,FALSE;TRUE,TRUE,FALSE;TRUE,TRUE,TRUE})
转换为1/0值组成的数组：
{1,0,0;1,1,0;1,1,1}
另外一个传递给MMULT函数的数组是：
TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
转换为：
TRANSPOSE({3,2,1})
得到：
{3;2;1}
因此，MMULT函数变为：
MMULT({1,0,0;1,1,0;1,1,1},{3;2;1})
结果是：
{3;5;6}
使用Arry1的值来替换主公式中的相应部分，先看看公式中的：
【excel技巧：在主工作表中汇总多个工作表中满足条件的值】INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!A2:F10”)
转换为：
INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,{3;5;6}>=1,0))&”‘!A2:F10”)
转换为：
INDIRECT(“‘”&INDEX(Sheets,1)&”‘!A2:F10”)
转换为：
INDIRECT(“‘”&”Sheet1″&”‘!A2:F10”)
转换为：
INDIRECT(“‘Sheet1’!A2:F10”)
因此，可以看到，对于A2中的公式，将返回Sheet1 。例如，如果解构单元格A5中的公式，那么公式中的MATCH构造将如下所示：
MATCH(TRUE,Arry1>=ROWS($1:4),0)
唯一发生变化的是引用ROWS($1:4)而不是ROWS($1:1)，结果转换为：
MATCH(TRUE,{3;5;6}>=4,0)
得到2，这样将引用工作表Sheet2 。
实际上，该技术的核心为：通过生成动态汇总小计数量的数组，该小计数量由来自每个工作表中符合条件（即在列D中的值为“Y”）的行数组成，然后将公式所在单元格相对行数与该数组相比较，以便有效地确定公式所在行中要指定的工作表。因此，主公式中的子句：

excel技巧：在主工作表中汇总多个工作表中满足条件的值( 二 )

猜你喜欢