我們可能熟悉使用INDEX、SMALL等在給定單列或單行數組的情況下，返回滿足一個或多個條件的值的列表。這是一項標準的公式技術。
我們之前講述了一種方法，給定由多個列組成的單元格區(qū)域，從該區(qū)域返回由所有非空單元格組成的單個列?？梢院苋菀椎仳炞C，在該公式中的單個條件可以擴展到多個條件，因此，我們現在有了從一維數組和二維數組中生成單列列表的方法。
那么，可以更進一步嗎？“三維”是經常應用于Excel中特定公式的通用術語，這些公式不僅可以對單列或單行進行操作，也可以對由多列或多行組成的單元格區(qū)域進行操作，還可以有效地對多個工作表進行操作。
本文提供了一種方法，在給定一個或多個相同布局的工作表的情況下，可以創(chuàng)建另一個“主”工作表，該工作表僅由滿足特定條件的所有工作表中的數據組成。并且，這里不使用VBA，僅使用公式。
假設我們想從下面三個工作表中整理數據：
工作表Sheet1：
圖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的這種公式構造，因為它實際上是我們執(zhí)行此類計算的唯一方法。上述公式轉換為：
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&{“Sheet1″,”Sheet2″,”Sheet3″}&”‘!D2:D10″),”Y”))
然后，將這組代表工作表名稱的文本字符串的兩端連接，在后面是所使用的工作表區(qū)域（D2:D10），在前面用單個撇號連接。盡管在工作表的名稱中不包含空格的情況下，并不需要這樣，但是這樣做將更好更通用。這樣，公式轉換為：
=SUMPRODUCT(COUNTIF(INDIRECT({“‘Sheet1’!D2:D10″,”‘Sheet2’!D2:D10″,”‘Sheet3’!D2:D10″}),”Y”))
因為COUNTIF函數能夠操作三維單元格區(qū)域，并且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”)))
這種公式構造可以有效地動態(tài)生成匯總小計，并且是使用標準的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函數變?yōu)椋?/p>
MMULT({1,0,0;1,1,0;1,1,1},{3;2;1})
結果是：
{3;5;6}
使用Arry1的值來替換主公式中的相應部分，先看看公式中的：
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)
唯一發(fā)生變化的是引用ROWS($1:4)而不是ROWS($1:1)，結果轉換為：
MATCH(TRUE,{3;5;6}>=4,0)
得到2，這樣將引用工作表Sheet2。
實際上，該技術的核心為：通過生成動態(tài)匯總小計數量的數組，該小計數量由來自每個工作表中符合條件（即在列D中的值為“Y”）的行數組成，然后將公式所在單元格相對行數與該數組相比較，以便有效地確定公式所在行中要指定的工作表。因此，主公式中的子句：
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，這實際上是整個解決方案中最難的部分，因為與我們將這種構造應用于由單列組成的數組不同（例如，在這種情況下，對于連續(xù)行，可以簡單地將參數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的值。
