excelperfect
本文詳細(xì)講解數(shù)組公式的相關(guān)概念，為數(shù)組公式進(jìn)一步學(xué)習(xí)打下基礎(chǔ)。
基本概念
數(shù)組是兩個(gè)或多個(gè)項(xiàng)目的集合。在Excel中，有三種類型的數(shù)組：
1. 引用數(shù)組，包含一個(gè)以上的單元格引用，例如單元格區(qū)域、工作表引用和定義的名稱。
2. 由公式元素創(chuàng)建的數(shù)組，也稱作結(jié)果數(shù)組，是通過(guò)數(shù)組操作創(chuàng)建的一組項(xiàng)目。
3. 數(shù)組常量，一組硬編碼到公式中的值。
數(shù)組公式是一種包含對(duì)一組項(xiàng)目而非單個(gè)項(xiàng)目進(jìn)行運(yùn)算（數(shù)學(xué)、比較、連接或函數(shù)參數(shù)）的公式，并且運(yùn)算提供的結(jié)果是一組項(xiàng)目而不是單個(gè)項(xiàng)目。這種運(yùn)算被稱為數(shù)組運(yùn)算，以區(qū)別于聚合運(yùn)算。結(jié)果數(shù)組可用于大公式中的公式元素，也可以是公式傳遞到單元格區(qū)域中的最終結(jié)果。數(shù)組公式的結(jié)果可以是單個(gè)項(xiàng)目，也可以是一組項(xiàng)目。
理解數(shù)組公式的概念
在上述數(shù)組公式的概念中，核心是“執(zhí)行對(duì)一組項(xiàng)目而非單個(gè)項(xiàng)目的運(yùn)算”。我們以示例來(lái)講解這個(gè)概念。
如下圖1所示，我們想要計(jì)算這4天收盤(pán)價(jià)與開(kāi)盤(pán)價(jià)之間的變化的最大值。
圖1
圖1所示的工作表中的計(jì)算過(guò)程如下：
1. 在單元格E3中使用公式=D3-C3計(jì)算該天的變化值，這只是一個(gè)簡(jiǎn)單的單個(gè)值之間的減法運(yùn)算。
2. 同樣，使用減法運(yùn)算計(jì)算出其它3天的變化值。
3. 在單元格E8中，使用公式=MAX(E3:E6)求出這4天變化值中的最大值。
很顯然，這些公式都不是數(shù)組公式，因?yàn)樗鼈冎皇窃趩蝹€(gè)項(xiàng)目上執(zhí)行運(yùn)算，得到的結(jié)果也是單個(gè)項(xiàng)目。
輔助列
在上圖1所示的工作表中，要獲取這4天股價(jià)的最大變化值，我們先在列E中計(jì)算每天的變化值，然后再將這些值傳遞給MAX函數(shù)求得最大值。我們稱列E為輔助列，因?yàn)樗鼛椭覀儷@取需要計(jì)算股價(jià)變化最大值的值。由MAX函數(shù)執(zhí)行的運(yùn)算稱為聚合運(yùn)算，因?yàn)樗闅v所有值并計(jì)算出結(jié)果，這不是數(shù)組運(yùn)算，雖然它處理多個(gè)項(xiàng)目。
如果你的需求要求顯示每天的股價(jià)變化量，那么構(gòu)造輔助列，然后使用MAX函數(shù)聚合運(yùn)算是一種很好的解決方案。然而，如果你不想看到詳細(xì)的計(jì)算過(guò)程，只是想計(jì)算出最大變化量，或者有成千上萬(wàn)的數(shù)據(jù)，創(chuàng)建輔助列占用工作表空間且耗費(fèi)較大，那么可以使用單個(gè)單元格數(shù)組公式來(lái)解決。
單個(gè)單元格的數(shù)組公式
下圖2中，使用公式來(lái)計(jì)算4天股價(jià)變化的最大值。
圖2
圖2中使用了一個(gè)公式進(jìn)行計(jì)算，其過(guò)程如下：
1. 單元格D8中包含從單元格區(qū)域D3:D6中的值減去單元格區(qū)域C3:C6中的值的公式，執(zhí)行著兩組數(shù)值的減法運(yùn)算：D3:D6-C3:C6。
2. 減法運(yùn)算的結(jié)果是一組數(shù)值。然后，MAX函數(shù)從這組數(shù)值中取最大值作為結(jié)果。
這是一個(gè)數(shù)組公式，因?yàn)橹苯訉?duì)兩組項(xiàng)目執(zhí)行運(yùn)算，并且生成的結(jié)果是一組數(shù)字。
技巧：使用評(píng)估公式元素來(lái)顯示結(jié)果數(shù)組
在數(shù)組公式處于編輯狀態(tài)時(shí)，我們可以評(píng)估公式元素的計(jì)算結(jié)果。例如，在圖2所示的示例中，我們可以看看該公式中的D3:D6-C3:C6是否生成與圖1輔助列一致的結(jié)果。
1.選擇公式中的元素，如下圖3所示。
圖3
2.按F9鍵評(píng)估公式中所選的部分公式元素，如下圖4所示，數(shù)組元素與輔助列的值相同。
圖4
注意，為了避免無(wú)意中將硬編碼放置到公式，在按F9評(píng)估后，按Ctrl+Z來(lái)撤銷。
這是在創(chuàng)建數(shù)組公式時(shí)經(jīng)常使用的一個(gè)技巧，可以查看數(shù)組公式元素的結(jié)果，從而確保公式正確，特別是創(chuàng)建包含多個(gè)公式元素的大型數(shù)組公式時(shí)。
數(shù)組公式的正確輸入方式
在上面的示例中，如果你像輸入普通公式那樣，在輸入完后，按回車鍵，則會(huì)得到一個(gè)錯(cuò)誤值#VALUE!，如下圖5所示。
圖5
這表明，沒(méi)有正確地輸入數(shù)組公式。在Excel中，除少數(shù)幾個(gè)函數(shù)外（例如SUMPRODUCT、LOOKUP、AGGREGATE、INDEX），大部分函數(shù)都不能自動(dòng)處理數(shù)組操作。因此，必須告訴Excel該函數(shù)的參數(shù)包含數(shù)組運(yùn)算，希望Excel執(zhí)行數(shù)組運(yùn)算。通過(guò)使用特別的組合鍵Ctrl+Shift+回車鍵來(lái)告訴Excel執(zhí)行數(shù)組運(yùn)算。
也就是說(shuō)，在輸入完公式中的字符后，按Ctrl+Shift+回車鍵，這才真正完成了數(shù)組公式的輸入。此時(shí)，Excel會(huì)自動(dòng)在公式兩端加上花括號(hào)，如上圖2所示。
隱式交叉陷阱
如下圖6所示，只有單元格B8中輸入的數(shù)組公式才是正確的結(jié)果，其它的公式有的顯示錯(cuò)誤值#VALUE!，有的顯示數(shù)字。錯(cuò)誤值還好說(shuō)，如果我們輸入公式后獲得的是數(shù)字，可能我們以為得到了結(jié)果，但這個(gè)結(jié)果卻是錯(cuò)誤的。
圖6
觀察一下，除數(shù)組公式外，得到數(shù)值的公式都是在數(shù)字?jǐn)?shù)據(jù)的旁邊列。在數(shù)字的旁邊輸入的公式，Excel會(huì)使用相同行的對(duì)應(yīng)列進(jìn)行計(jì)算處理，這被稱作隱式交叉。如果我們不仔細(xì)，很可能認(rèn)為這樣的公式計(jì)算結(jié)果是對(duì)的，因此在使用數(shù)組公式時(shí)，在輸入完公式后，我們要留意公式欄中，是否在公式兩邊已添加了花括號(hào)。這也提醒我們，數(shù)組公式的最終輸入完成一定是以按下Ctrl+Shift+回車鍵為標(biāo)準(zhǔn)的。
數(shù)組公式的優(yōu)缺點(diǎn)
優(yōu)點(diǎn)
1. 數(shù)組公式有時(shí)是除了VBA外唯一的解決問(wèn)題的方式。
2. 數(shù)組公式能夠節(jié)省工作表空間（不需要一個(gè)或多個(gè)輔助列）。
3. 在多單元格中的數(shù)組公式難以刪除。
4. 給定了所需要的結(jié)果和環(huán)境條件，數(shù)組公式是最好的選擇。
缺點(diǎn)
1. 數(shù)組公式可能減慢公式計(jì)算時(shí)間，特別是對(duì)于包含具有大量單元格引用和計(jì)算的許多公式的工作表。
2. 大多數(shù)的數(shù)組公式都需要使用Ctrl+Shift+回車鍵，而這恰恰是最容易忘記的。
3. 使用的數(shù)組公式對(duì)于用戶來(lái)說(shuō)可能比較難以理解。
4. 數(shù)組公式的學(xué)習(xí)資料較少。
5. 創(chuàng)建數(shù)組公式有時(shí)相當(dāng)復(fù)雜。
確定是否有比數(shù)組公式更有效的方法
1. 是否有實(shí)現(xiàn)同樣目的的內(nèi)置函數(shù)？
2. 是否可以使用輔助列？
3. 是否可以使用輔助單元格？
4. 是否可以使用篩選、高級(jí)篩選或數(shù)據(jù)透視表？
5. 是否有不同的公式選項(xiàng)？對(duì)于包含具有大量單元格引用和計(jì)算的許多公式的工作表來(lái)說(shuō)，可能需要花時(shí)間設(shè)定不同的公式選項(xiàng)，以查看是否明顯更快。
6. 是否有替代的數(shù)組公式或者非數(shù)組公式是更有效的？
7. 是否可以使用VBA代替數(shù)組公式？
《Ctrl+Shift+Enter：MasteringExcel Array Formulas》學(xué)習(xí)筆記
完美Excel
歡迎在下面留言，完善本文內(nèi)容，讓更多的人學(xué)到更完美的知識(shí)。
聯(lián)系客服
