我們可能熟悉使用INDEX、SMALL等在給定單列或單行數(shù)組的情況下，返回滿足一個(gè)或多個(gè)條件的值的列表。這是一項(xiàng)標(biāo)準(zhǔn)的公式技術(shù)。
我們述了一種方法，給定由多個(gè)列組成的單元格區(qū)域，從該區(qū)域返回由所有非空單元格組成的單個(gè)列。可以很容易地驗(yàn)證，在該公式中的單個(gè)條件可以擴(kuò)展到多個(gè)條件，因此，我們現(xiàn)在有了從一維數(shù)組和二維數(shù)組中生成單列列表的方法。
那么，可以更進(jìn)一步嗎？“三維”是經(jīng)常應(yīng)用于Excel中特定公式的通用術(shù)語(yǔ)，這些公式不僅可以對(duì)單列或單行進(jìn)行操作，也可以對(duì)由多列或多行組成的單元格區(qū)域進(jìn)行操作，還可以有效地對(duì)多個(gè)工作表進(jìn)行操作。
本文提供了一種方法，在給定一個(gè)或多個(gè)相同布局的工作表的情況下，可以創(chuàng)建另一個(gè)“主”工作表，該工作表僅由滿足特定條件的所有工作表中的數(shù)據(jù)組成。并且，這里不使用VBA，僅使用公式。
假設(shè)我們想從下面三個(gè)工作表中整理數(shù)據(jù)：
工作表Sheet1：
解決方案
首先，定義下面兩個(gè)名稱：
名稱：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”)))
可以根據(jù)實(shí)際情況，修改工作表列表和數(shù)據(jù)范圍（D2:D10）。
在工作表Master的單元格G1中，輸入下面的公式：
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
在工作表Master的單元格A2中輸入下面的數(shù)組公式：
=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)))
向下和向右拖放公式至合適的位置。
工作原理
先看看相對(duì)簡(jiǎn)單的單元格G1中的公式，該公式用于確定返回結(jié)果的數(shù)量：
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
如果不熟悉跨多個(gè)工作表使用公式的技術(shù)，那么應(yīng)記下使用INDIRECT的這種公式構(gòu)造，因?yàn)樗鼘?shí)際上是我們執(zhí)行此類計(jì)算的唯一方法。上述公式轉(zhuǎn)換為：
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&{“Sheet1″,”Sheet2″,”Sheet3″}&”‘!D2:D10″),”Y”))
然后，將這組代表工作表名稱的文本字符串的兩端連接，在后面是所使用的工作表區(qū)域（D2:D10），在前面用單個(gè)撇號(hào)連接。盡管在工作表的名稱中不包含空格的情況下，并不需要這樣，但是這樣做將更好更通用。這樣，公式轉(zhuǎn)換為：
=SUMPRODUCT(COUNTIF(INDIRECT({“‘Sheet1’!D2:D10″,”‘Sheet2’!D2:D10″,”‘Sheet3’!D2:D10″}),”Y”))
因?yàn)镃OUNTIF函數(shù)能夠操作三維單元格區(qū)域，并且SUMPRODUCT函數(shù)提供了必要的強(qiáng)制轉(zhuǎn)換，使得INDIRECT函數(shù)返回一組單元格引用，而不僅僅是一個(gè)，因此公式轉(zhuǎn)換為：
=SUMPRODUCT({3,2,1})
其中數(shù)組的值由3、2、1組成，與工作表Sheet1、Sheet2、Sheet3的列D中包含“Y”的數(shù)量一致。該公式的最后結(jié)果為：
6
接下來(lái)，看看單元格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函數(shù)中的前半部分很簡(jiǎn)單，如果拖放的行數(shù)超過(guò)了可能獲得的結(jié)果數(shù)量，則為空。
在公式中使用了定義的名稱Arry1：
=MMULT(0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets))))),TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”)))
這種公式構(gòu)造可以有效地動(dòng)態(tài)生成匯總小計(jì)，并且是使用標(biāo)準(zhǔn)的SUBTOTA/OFFSET函數(shù)組合的替代方法。
依次看看傳遞給MMULT函數(shù)的數(shù)組。第一個(gè)是：
0+(ROW(INDIRECT(“1:”&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT(“1:”&COUNTA(Sheets)))))
轉(zhuǎn)換為：
0+(ROW(INDIRECT(“1:”&3))>=TRANSPOSE(ROW(INDIRECT(“1:”&3))))
轉(zhuǎn)換為：
0+({1;2;3}>=TRANSPOSE({1;2;3}))
轉(zhuǎn)換為：
0+({1;2;3}>={1,2,3})
兩個(gè)正交數(shù)組進(jìn)行比較，一個(gè)是3行1列，一個(gè)是1行3列，得到一個(gè)3行3列的數(shù)組，該數(shù)組由9個(gè)TRUE/FALSE值組成：
0+({TRUE,FALSE,FALSE;TRUE,TRUE,FALSE;TRUE,TRUE,TRUE})
轉(zhuǎn)換為1/0值組成的數(shù)組：
{1,0,0;1,1,0;1,1,1}
另外一個(gè)傳遞給MMULT函數(shù)的數(shù)組是：
TRANSPOSE(COUNTIF(INDIRECT(“‘”&Sheets&”‘!D2:D10″),”Y”))
轉(zhuǎn)換為：
TRANSPOSE({3,2,1})
得到：
{3;2;1}
因此，MMULT函數(shù)變?yōu)椋?/p>
MMULT({1,0,0;1,1,0;1,1,1},{3;2;1})
結(jié)果是：
{3;5;6}
使用Arry1的值來(lái)替換主公式中的相應(yīng)部分，先看看公式中的：
INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&”‘!A2:F10”)
轉(zhuǎn)換為：
INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,{3;5;6}>=1,0))&”‘!A2:F10”)
轉(zhuǎn)換為：
INDIRECT(“‘”&INDEX(Sheets,1)&”‘!A2:F10”)
轉(zhuǎn)換為：
INDIRECT(“‘”&”Sheet1″&”‘!A2:F10”)
轉(zhuǎn)換為：
INDIRECT(“‘Sheet1’!A2:F10”)
因此，可以看到，對(duì)于A2中的公式，將返回Sheet1。例如，如果解構(gòu)單元格A5中的公式，那么公式中的MATCH構(gòu)造將如下所示：
MATCH(TRUE,Arry1>=ROWS($1:4),0)
唯一發(fā)生變化的是引用ROWS($1:4)而不是ROWS($1:1)，結(jié)果轉(zhuǎn)換為：
MATCH(TRUE,{3;5;6}>=4,0)
得到2，這樣將引用工作表Sheet2。
實(shí)際上，該技術(shù)的核心為：通過(guò)生成動(dòng)態(tài)匯總小計(jì)數(shù)量的數(shù)組，該小計(jì)數(shù)量由來(lái)自每個(gè)工作表中符合條件（即在列D中的值為“Y”）的行數(shù)組成，然后將公式所在單元格相對(duì)行數(shù)與該數(shù)組相比較，以便有效地確定公式所在行中要指定的工作表。因此，主公式中的子句：
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))
可轉(zhuǎn)換為：
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函數(shù)部分：
IF(Sheet1!D2:D10=”Y”,ROW(INDIRECT(“1:”&$G$1)))
轉(zhuǎn)換為：
IF({“Y”;0;”Y”;”Y”;0;0;0;0;0}=”Y”,ROW(INDIRECT(“1:”&6)))
轉(zhuǎn)換為：
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}
這里，可以看到數(shù)組中的1、3和4對(duì)應(yīng)于工作表Sheet1列D中為“Y”的相對(duì)行號(hào)。現(xiàn)在要做的就是將該數(shù)組傳遞給SMALL函數(shù)并確定參數(shù)k，這實(shí)際上是整個(gè)解決方案中最難的部分，因?yàn)榕c我們將這種構(gòu)造應(yīng)用于由單列組成的數(shù)組不同（例如，在這種情況下，對(duì)于連續(xù)行，可以簡(jiǎn)單地將參數(shù)k增加1），而這里必須考慮：當(dāng)要求Sheet2返回值時(shí)，以及要求Sheet3返回值時(shí)，該參數(shù)將被“重置”為1。為此，這里使用：
IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))
為理解這個(gè)公式構(gòu)造是如何工作的，我們可暫時(shí)將其作為獨(dú)立的數(shù)組公式，輸入到某單元格中，然后向下拖放以了解其生成的值。實(shí)際上，將該公式從A2向下拖至A7，可轉(zhuǎn)換為：
IFERROR(1+{1,2,3,4,5,6}-LOOKUP({1,2,3,4,5,6},1+Arry1),{1,2,3,4,5,6})
轉(zhuǎn)換為：
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})
轉(zhuǎn)換為：
IFERROR(1+{1,2,3,4,5,6}-{#N/A,#N/A,#N/A,4,4,6},{1,2,3,4,5,6})
轉(zhuǎn)換為：
=IFERROR({#N/A,#N/A,#N/A,1,2,1},{1,2,3,4,5,6})
得到：
{1,2,3,1,2,1}
正是我們需要的參數(shù)k的值，即在工作表Sheet1中匹配第1、第2和第3小的行，在工作表Sheet2中匹配第1和第2小的行，在工作表Sheet3中匹配第1小的行。
現(xiàn)在，回到主公式中的子句：
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))
可轉(zhuǎn)換為：
INDEX(Sheet1!A2:F10,SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;FALSE;FALSE},1),COLUMNS($A:A))
轉(zhuǎn)換為：
INDEX(Sheet1!A2:F10,1,COLUMNS($A:A))
COLUMNS($A:A)使得公式向右拖放時(shí)，可以為INDEX函數(shù)的參數(shù)column_num提供合適的值。在單元格A2中，COLUMNS($A:A)的值等于1，因此公式轉(zhuǎn)換為：
INDEX(Sheet1!A2:F10,1,1)
即工作表Sheet1中單元格A2的值。
