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

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))
可转换为：
INDEX(Sheet1!A2:F10,SMALL(IF(Sheet1!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))
先看看上面公式中的SMALL函数部分：
IF(Sheet1!D2:D10=”Y”,ROW(INDIRECT(“1:”&$G$1)))
转换为：
IF({“Y”;0;”Y”;”Y”;0;0;0;0;0}=”Y”,ROW(INDIRECT(“1:”&6)))
转换为：
IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6})
得到：
{1;FALSE;3;4;FALSE;FALSE;FALSE;FALSE;FALSE}
这里，可以看到数组中的1、3和4对应于工作表Sheet1列D中为“Y”的相对行号。现在要做的就是将该数组传递给SMALL函数并确定参数k，这实际上是整个解决方案中最难的部分，因为与我们将这种构造应用于由单列组成的数组不同（例如，在这种情况下，对于连续行，可以简单地将参数k增加1），而这里必须考虑：当要求Sheet2返回值时，以及要求Sheet3返回值时，该参数将被“重置”为1 。为此，这里使用：
IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))
为理解这个公式构造是如何工作的，我们可暂时将其作为独立的数组公式，输入到某单元格中，然后向下拖放以了解其生成的值。实际上，将该公式从A2向下拖至A7，可转换为：
IFERROR(1+{1,2,3,4,5,6}-LOOKUP({1,2,3,4,5,6},1+Arry1),{1,2,3,4,5,6})
转换为：
IFERROR(1+{1,2,3,4,5,6}-LOOKUP({1,2,3,4,5,6},1+{3;5;6}),{1,2,3,4,5,6})
转换为：
IFERROR(1+{1,2,3,4,5,6}-{#N/A,#N/A,#N/A,4,4,6},{1,2,3,4,5,6})
转换为：
=IFERROR({#N/A,#N/A,#N/A,1,2,1},{1,2,3,4,5,6})
得到：
{1,2,3,1,2,1}
正是我们需要的参数k的值，即在工作表Sheet1中匹配第1、第2和第3小的行，在工作表Sheet2中匹配第1和第2小的行，在工作表Sheet3中匹配第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))
可转换为：
INDEX(Sheet1!A2:F10,SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;FALSE;FALSE},1),COLUMNS($A:A))
转换为：
INDEX(Sheet1!A2:F10,1,COLUMNS($A:A))
COLUMNS($A:A)使得公式向右拖放时，可以为INDEX函数的参数column_num提供合适的值。在单元格A2中，COLUMNS($A:A)的值等于1，因此公式转换为：
INDEX(Sheet1!A2:F10,1,1)
即工作表Sheet1中单元格A2的值。

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

猜你喜欢