數(shù)組是Excel中一個(gè)非常重要的概念，如LINEST()是做線性加歸的函數(shù)，它接受數(shù)組作為參數(shù)，返回值也是一個(gè)數(shù)組。為了徹底的弄清楚這樣的函數(shù)，好好的研究了一下Excel的數(shù)組及數(shù)組函數(shù)，并作了總結(jié)。
1.Excel中的數(shù)組
數(shù)組（array，繁體版稱作陣列），是由數(shù)據(jù)元素組成的集合，數(shù)據(jù)無素可以是數(shù)值,文本,日期,邏輯,錯(cuò)誤值等。數(shù)據(jù)元素以行和列的形式組織起來，構(gòu)成一個(gè)數(shù)據(jù)矩陣。
在EXCEL中，根據(jù)構(gòu)成元素的不同，可以把數(shù)組分為常量數(shù)組和單元格區(qū)域數(shù)組。
1.1.常量數(shù)組
常量數(shù)組可以同時(shí)包含多種數(shù)據(jù)類型。它用{}將構(gòu)成數(shù)組的常量括起來，行中的元素用逗號(hào)“，”分隔，行之間用分號(hào)“;”分隔。數(shù)組常量不能包含其他數(shù)組、公式或函數(shù)。當(dāng)輸入如下所示的公式時(shí)，Excel 將顯示警告消息：{1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)}。另外，數(shù)值不能包含百分號(hào)、貨幣符號(hào)、逗號(hào)或圓括號(hào)。
例如，{2,"greensky",TRUE,#N/A, }是一個(gè)1行4列的常量數(shù)組，而{1,2,3;4,5,6}則為一個(gè)2行3列的常量數(shù)組。
1.2.單元格區(qū)域數(shù)組
單元格區(qū)域數(shù)組則是通過對(duì)一組連續(xù)的單元格區(qū)域進(jìn)行引用而得到的數(shù)組。在數(shù)組公式中{A1:B4}是一個(gè)4行2列的單元格區(qū)域數(shù)組。
1.3.Excel數(shù)組的維數(shù)
數(shù)組作為數(shù)據(jù)的組織形式本身可以是多維的，而且眾多編程都支持多維數(shù)組，但是Excel的公式中最高只支持2維數(shù)組（也就是行列構(gòu)成的數(shù)組）。關(guān)于這個(gè)結(jié)論，在網(wǎng)絡(luò)搜索不到明確的答案，做這個(gè)結(jié)論基于以下兩點(diǎn)：
Excel不支持形如={{…},{…}}或={{…};{…}}的書寫格式，所以無法表示3維或多維數(shù)組。
在Excel中提供對(duì)數(shù)組引用的函數(shù)為index()，它的格式為index(array,row_num,column_num)，它只有行列兩個(gè)參數(shù)，而沒有引用高于2維數(shù)組的參數(shù)。
index函數(shù)也有這樣的格式index(reference,row_num,column_num,area_num)，它提供了第三個(gè)引用參數(shù)，是不是說明Excel提供了對(duì)3維數(shù)組的引用呢？答案是否定的，reference可以使用多個(gè)區(qū)域（區(qū)域之間有逗號(hào)隔開）構(gòu)成的參數(shù)，第三個(gè)參數(shù)area_num是用于指定reference中第幾個(gè)區(qū)域的，如INDEX((A1:C6,A8:C11),1,1,2)中第三個(gè)參數(shù)只是指定區(qū)域A8:C11而已。
Excel VBA支持多維數(shù)組。
1.4.數(shù)組的引用
在運(yùn)算過程中，可能需要引用數(shù)組中的某一個(gè)元素、某一行或某一列。下面介紹元素、行、列的引用方法。
元素的引用：
對(duì)于1維數(shù)組index(array, c)，對(duì)于2維數(shù)組index(array, r, c)。其中r表示第幾行，c表示第幾列，一般為正整數(shù)。
當(dāng)r為小數(shù)時(shí)，會(huì)舍棄小數(shù)點(diǎn)后的部公（非四舍五入），如index(array, 1.2, 2.9)等同于index(array, 1, 2)。編程語言一般默認(rèn)以截取的方式處理小數(shù)轉(zhuǎn)整數(shù)的操作，這樣可以提高效率，Excel做這樣的處理也是在意料之內(nèi)的。
行引用
index(array, r, 0)返回第r行數(shù)據(jù)構(gòu)成的數(shù)組。
列引用
Index(array, 0 ,c)返回第c列數(shù)據(jù)構(gòu)成的數(shù)組。
特殊的，index(array, 0, 0)將返回整個(gè)2維數(shù)組。而index(index(array, r, 0),c)、index(index(array, 0, c), r)和index(array, r, c)相同，都可以返回第r行第c列的元素。
1.5.數(shù)組的算術(shù)運(yùn)算 和 數(shù)組的擴(kuò)展
當(dāng)數(shù)組進(jìn)行運(yùn)算（加、減、乘、除、冪）時(shí)，要求兩個(gè)數(shù)組具有相同的維數(shù)，如果是2維數(shù)組還要求兩數(shù)組的行數(shù)相同，列數(shù)也相同。
滿足這個(gè)條件的兩個(gè)數(shù)組的算術(shù)運(yùn)算（加、減、乘、除、冪）等于對(duì)應(yīng)元素之間進(jìn)行數(shù)學(xué)運(yùn)算。如兩個(gè)具有3行3列的2維數(shù)組之間的加法運(yùn)算為：
如果參與運(yùn)算的兩個(gè)數(shù)據(jù)的維數(shù)不同、行數(shù)不同或列數(shù)不同，Excel會(huì)對(duì)數(shù)據(jù)的維數(shù)和行列進(jìn)行擴(kuò)展，以滿足上述的要求而進(jìn)行運(yùn)算。擴(kuò)展的規(guī)則如下：
維數(shù)擴(kuò)展：如果兩數(shù)據(jù)的維數(shù)不同時(shí)，維數(shù)低的數(shù)據(jù)會(huì)被擴(kuò)展為和維數(shù)高的數(shù)據(jù)的維數(shù)相同。維數(shù)的擴(kuò)展有兩種類型，常數(shù)向1維數(shù)組或2維數(shù)組的擴(kuò)展、1維數(shù)組向2維數(shù)組擴(kuò)展。
行列擴(kuò)展：如果兩數(shù)據(jù)的行數(shù)不同，行數(shù)低的數(shù)據(jù)會(huì)擴(kuò)展為和行數(shù)高的數(shù)據(jù)的行數(shù)相同。如果列數(shù)不同，列擴(kuò)展的規(guī)則和行擴(kuò)展相同。如果行列均不相同，那么行列都需要擴(kuò)展。
根據(jù)這些原則，一個(gè)M1行、N1列的數(shù)組和一個(gè)M2行、N2列的數(shù)組運(yùn)算后，得到的是max(M1, M2)行、max(N1, N2)列的數(shù)組。這個(gè)結(jié)論包含了所有類型數(shù)據(jù)的運(yùn)算。例如{1,2,3;4,5,6}和{7,8,9,10}運(yùn)算，前者M(jìn)1=2,N1=3，后者M(jìn)1=1,N2=4，結(jié)果為2行4列的數(shù)組。
數(shù)據(jù)擴(kuò)展后，出現(xiàn)了一此數(shù)據(jù)空位，如計(jì)算下列數(shù)組時(shí)
首先要把(1 2)擴(kuò)展為
這時(shí)出現(xiàn)了一些數(shù)據(jù)空位，那么Excel如何填充這些空位的呢？這里也有規(guī)則可循，個(gè)人用實(shí)驗(yàn)的方法總結(jié)如下：
對(duì)常數(shù)所有的擴(kuò)展，空位都填寫該常數(shù)。
當(dāng)行數(shù)組進(jìn)行行擴(kuò)展時(shí)，擴(kuò)展出來的每一行的數(shù)據(jù)和首行相同；當(dāng)行數(shù)組進(jìn)行列擴(kuò)展時(shí)，擴(kuò)展列的數(shù)據(jù)填寫錯(cuò)誤值#N/A。
當(dāng)列數(shù)組進(jìn)行列擴(kuò)展時(shí)，擴(kuò)展出來的每一列的數(shù)據(jù)和首列相同；當(dāng)列數(shù)組進(jìn)行行擴(kuò)展時(shí)，擴(kuò)展行的數(shù)據(jù)填寫錯(cuò)誤值#N/A。
當(dāng)2維的數(shù)組進(jìn)行行擴(kuò)展或列擴(kuò)展時(shí)，擴(kuò)展出的數(shù)據(jù)都填寫錯(cuò)誤值#N/A。
因此，上邊那個(gè)例子，(1 2)應(yīng)該擴(kuò)展為：
因此計(jì)算的結(jié)果為：
有了這些規(guī)則，就可以進(jìn)行任意數(shù)組、常數(shù)間的算術(shù)運(yùn)算了。
2.數(shù)組公式
2.1.什么是數(shù)組公式
數(shù)組公式是指可以在數(shù)組的一項(xiàng)或多項(xiàng)上執(zhí)行多個(gè)計(jì)算的公式。數(shù)組公式可以返回多個(gè)結(jié)果，也可返回一個(gè)結(jié)果。
2.2.數(shù)組公式的輸入
輸入數(shù)組公式后，同時(shí)按下Ctrl+Shift+Enter，數(shù)組公式的外面會(huì)自動(dòng)加上大括號(hào){}以和普通公式區(qū)分。{}不是手工輸入的，那樣Excel會(huì)認(rèn)為您輸入的是文本格式。數(shù)組公式每次編輯過后，都要同時(shí)按下Ctrl+Shift+Enter以完成輸入。
2.3.多單元格公式和單元格公式
如果把數(shù)組公式的返回值放入到一個(gè)單元格區(qū)域內(nèi)可以呈現(xiàn)返回?cái)?shù)組的各個(gè)元素的值，這樣的公式稱為多單元格公式。而位于單元格中的數(shù)組公式稱為單元格公式。
多單格公式在使用時(shí)，需遵循以下原則：
必須在輸入公式之前選擇用于保存結(jié)果的單元格區(qū)域，這個(gè)區(qū)域最好和返回的數(shù)組尺寸相同，否則在超出數(shù)組尺寸范圍外的單元格填寫#N/A。
不能更改多單元格數(shù)組公式中部分單元格的內(nèi)容，但可以移動(dòng)或刪除整個(gè)數(shù)組公式。換言之，要編輯數(shù)組公式，需先刪除現(xiàn)有公式再重新開始。
不能向多單元格數(shù)組公式中插入空白單元格（包括插入新行、列、單元格）或刪除其中的單元格。這和“不能更改多單元格數(shù)組公式中部分單元格的內(nèi)容”是一致的。
2.4.數(shù)組公式的優(yōu)點(diǎn)
簡潔性：數(shù)組公式可以同對(duì)多個(gè)數(shù)據(jù)執(zhí)行多個(gè)運(yùn)算。解決一個(gè)復(fù)雜的問題可以只需要一個(gè)公式，而用普通公式可能需要多步運(yùn)算，甚至要填加輔助列。不過，數(shù)組公式可以用幾個(gè)一般公式和一些過渡數(shù)據(jù)來代替，從而達(dá)到同樣的目的，所以千萬不要迷信數(shù)組公式，以免走火入魔！
一致性：多單元格數(shù)組公式中，點(diǎn)擊任一個(gè)單元格，看到的公式內(nèi)容都是相同的。這種一致性可以保證更高的準(zhǔn)確性。
安全性：不能覆蓋多單元格數(shù)組公式的組成部分，可以防止誤操作。
文件小：通?？梢允褂脝蝹€(gè)數(shù)組公式，而不必用多個(gè)中間公式。這在數(shù)據(jù)量很大的時(shí)候效果才會(huì)明顯。
2.5.數(shù)組公式的語法
數(shù)組公式主要使用標(biāo)準(zhǔn)公式語法。它們都以等號(hào)開始，可以在數(shù)組公式中使用任何內(nèi)置 Excel 函數(shù)。使用數(shù)組公式的主要不同之處在于:
必須按 Ctrl+Shift+Enter 輸入公式
輸入多單元格公式時(shí)，必須首先選擇用于保存結(jié)果的單元格區(qū)域
2.6.數(shù)組函數(shù)示例
1. 計(jì)算一個(gè)區(qū)域內(nèi)，不重復(fù)的數(shù)據(jù)的個(gè)數(shù)。（個(gè)人認(rèn)為這是數(shù)組函數(shù)最精彩的例子，正是這個(gè)例子激發(fā)了我學(xué)習(xí)數(shù)組公式的興趣）
{=SUM(1 / COUNTIF(A1:A100, A1:A100) }，這里假設(shè)區(qū)域?yàn)锳1：A100。
分析：COUNTIF(range, criteria)在range范圍內(nèi)查找criteria，并返回它的個(gè)數(shù)。假設(shè)A1單元格內(nèi)的數(shù)據(jù)為X在A1:A100范圍內(nèi)共有N個(gè)數(shù)據(jù)X。那么數(shù)組公式對(duì)這100個(gè)單元格逐個(gè)計(jì)算1 / COUNTIF(A1:A100, 某一單元格)，當(dāng)對(duì)A1計(jì)算時(shí)，返回結(jié)果1/N，在這100次計(jì)算里，每遇到數(shù)據(jù)X就回返回一個(gè)1/N，共返回N個(gè)1/N，求和為1。也就是說，有多少個(gè)不同的數(shù)據(jù)就返回多少個(gè)1，恰好就是不重復(fù)的數(shù)據(jù)的個(gè)數(shù)。這個(gè)公式是多么簡潔優(yōu)美！不過再美好的事物也是有缺點(diǎn)的，統(tǒng)計(jì)區(qū)域內(nèi)不得有空單元格，否則返回#DIV/0!錯(cuò)誤?？梢杂孟旅娴臄?shù)組公式解決這個(gè)問題：
{=SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100))}
當(dāng)然這個(gè)問題也可以用sumproduct()、frequency()或match()實(shí)現(xiàn)。
2. 計(jì)算1+2+3+…..+100的和。（假設(shè)不知道等差求和公式）
{=sum(row(A1:A100))} 或 {=sum(row(1:100))}
注意：row()的運(yùn)算結(jié)果可以做很多函數(shù)的參數(shù)。
3. 條件求和
ABCD
1產(chǎn)品編號(hào)生產(chǎn)日期產(chǎn)品單價(jià)產(chǎn)品數(shù)量
2AA2009/6/151100
3AA2009/6/201125
4BB2009/6/302150
5BB2009/7/102175
6CC2009/6/153200
7CC2009/7/203225
8AA2009/7/301250
9AA2009/8/101275
10BB2009/8/152300
11BB2009/8/202325
12CC2009/10/103350
13CC2009/10/153400
14DD2009/10/204425
15DD2009/10/304500
求產(chǎn)品BB的8月份產(chǎn)量
{=SUM(IF((B2:B15="BB")*(MONTH(C2:C15)=8), E2:E15, 0))}
注意：這里if用的判斷條件為(B2:B15="BB")*(MONTH(C2:C15)=8)，如果把這部分改為and(B2:B15="BB", MONTH(C2:C15)=8)，則不能工作。起初這另我很不解，但仔細(xì)研究and()發(fā)現(xiàn)，and()處理常量數(shù)組和單元格區(qū)域數(shù)組的方式是不同的。當(dāng)處理常量數(shù)組時(shí)，運(yùn)算結(jié)果還是數(shù)組。如=index({TRUE,TRUE},{TURE,FALSE},2)結(jié)果為FALSE。當(dāng)處理受單元格區(qū)域數(shù)組時(shí)，會(huì)把數(shù)組內(nèi)的所有的數(shù)據(jù)都取出來作為多個(gè)參數(shù)，運(yùn)算最終結(jié)果不是一個(gè)數(shù)組，而僅僅是一個(gè)邏輯值。如=index(and(B2:B15="BB", MONTH(C2:C15)=8),1)會(huì)得到#VALUE!。
因此，{=SUM(IF(and(B2:B15="BB", MONTH(C2:C15)=8), (E2:E15), 0))}這個(gè)公式在運(yùn)算時(shí)，and(B2:B15="BB", MONTH(C2:C15)=8)的計(jì)算值為FALSE，最終計(jì)算結(jié)果為零。
另外，邏輯值是可以直接參與算術(shù)運(yùn)算的，這時(shí)TRUE=1,FALSE=0，因此下面這個(gè)公式可以簡化為：
{=SUM((B2:B15="BB")*(MONTH(C2:C15)=8)*E2:E15)}
求產(chǎn)品BB和CC的總產(chǎn)值
{=SUM(IF((B2:B15="BB")+(B2:B15="CC"),E2:E15*D2:D15))}
這個(gè)公式可以簡化為：
{=SUM(((B2:B15="BB")+(B2:B15="CC"))*E2:E15*D2:D15)}
－The end－
