數(shù)組是Excel中一個非常重要的概念，如LINEST()是做線性加歸的函數(shù)，它接受數(shù)組作為參數(shù)，返回值也是一個數(shù)組。為了徹底的弄清楚這樣的函數(shù)，好好的研究了一下Excel的數(shù)組及數(shù)組函數(shù)，并作了總結。

1.Excel中的數(shù)組

數(shù)組（array，繁體版稱作陣列），是由數(shù)據(jù)元素組成的集合，數(shù)據(jù)無素可以是數(shù)值,文本,日期,邏輯,錯誤值等。數(shù)據(jù)元素以行和列的形式組織起來，構成一個數(shù)據(jù)矩陣。

在EXCEL中，根據(jù)構成元素的不同，可以把數(shù)組分為常量數(shù)組和單元格區(qū)域數(shù)組。

1.1.常量數(shù)組

常量數(shù)組可以同時包含多種數(shù)據(jù)類型。它用{}將構成數(shù)組的常量括起來，行中的元素用逗號“，”分隔，行之間用分號“;”分隔。數(shù)組常量不能包含其他數(shù)組、公式或函數(shù)。當輸入如下所示的公式時，Excel 將顯示警告消息：{1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)}。另外，數(shù)值不能包含百分號、貨幣符號、逗號或圓括號。

例如，{2,"greensky",TRUE,#N/A, }是一個1行4列的常量數(shù)組，而{1,2,3;4,5,6}則為一個2行3列的常量數(shù)組。

1.2.單元格區(qū)域數(shù)組

單元格區(qū)域數(shù)組則是通過對一組連續(xù)的單元格區(qū)域進行引用而得到的數(shù)組。在數(shù)組公式中{A1:B4}是一個4行2列的單元格區(qū)域數(shù)組。

1.3.Excel數(shù)組的維數(shù)

數(shù)組作為數(shù)據(jù)的組織形式本身可以是多維的，而且眾多編程都支持多維數(shù)組，但是Excel的公式中最高只支持2維數(shù)組（也就是行列構成的數(shù)組）。關于這個結論，在網(wǎng)絡搜索不到明確的答案，做這個結論基于以下兩點：

index函數(shù)也有這樣的格式index(reference,row_num,column_num,area_num)，它提供了第三個引用參數(shù)，是不是說明Excel提供了對3維數(shù)組的引用呢？答案是否定的，reference可以使用多個區(qū)域（區(qū)域之間有逗號隔開）構成的參數(shù)，第三個參數(shù)area_num是用于指定reference中第幾個區(qū)域的，如INDEX((A1:C6,A8:C11),1,1,2)中第三個參數(shù)只是指定區(qū)域A8:C11而已。

Excel　VBA支持多維數(shù)組。

1.4.數(shù)組的引用

在運算過程中，可能需要引用數(shù)組中的某一個元素、某一行或某一列。下面介紹元素、行、列的引用方法。

• 元素的引用：

對于1維數(shù)組index(array, c)，對于2維數(shù)組index(array, r, c)。其中r表示第幾行，c表示第幾列，一般為正整數(shù)。

當r為小數(shù)時，會舍棄小數(shù)點后的部公（非四舍五入），如index(array, 1.2, 2.9)等同于index(array, 1, 2)。編程語言一般默認以截取的方式處理小數(shù)轉整數(shù)的操作，這樣可以提高效率，Excel做這樣的處理也是在意料之內(nèi)的。

• 行引用

index(array, r, 0)返回第r行數(shù)據(jù)構成的數(shù)組。

• 列引用

Index(array, 0 ,c)返回第c列數(shù)據(jù)構成的數(shù)組。

特殊的，index(array, 0, 0)將返回整個2維數(shù)組。而index(index(array, r, 0),c)、index(index(array, 0, c), r)和index(array, r, c)相同，都可以返回第r行第c列的元素。

1.5.數(shù)組的算術運算 和 數(shù)組的擴展

當數(shù)組進行運算（加、減、乘、除、冪）時，要求兩個數(shù)組具有相同的維數(shù)，如果是2維數(shù)組還要求兩數(shù)組的行數(shù)相同，列數(shù)也相同。

滿足這個條件的兩個數(shù)組的算術運算（加、減、乘、除、冪）等于對應元素之間進行數(shù)學運算。如兩個具有3行3列的2維數(shù)組之間的加法運算為：

如果參與運算的兩個數(shù)據(jù)的維數(shù)不同、行數(shù)不同或列數(shù)不同，Excel會對數(shù)據(jù)的維數(shù)和行列進行擴展，以滿足上述的要求而進行運算。擴展的規(guī)則如下：

根據(jù)這些原則，一個M1行、N1列的數(shù)組和一個M2行、N2列的數(shù)組運算后，得到的是max(M1, M2)行、max(N1, N2)列的數(shù)組。這個結論包含了所有類型數(shù)據(jù)的運算。例如{1,2,3;4,5,6}和{7,8,9,10}運算，前者M1=2,N1=3，后者M1=1,N2=4，結果為2行4列的數(shù)組。

數(shù)據(jù)擴展后，出現(xiàn)了一此數(shù)據(jù)空位，如計算下列數(shù)組時

首先要把(1  2)擴展為

這時出現(xiàn)了一些數(shù)據(jù)空位，那么Excel如何填充這些空位的呢？這里也有規(guī)則可循，個人用實驗的方法總結如下：

因此，上邊那個例子，(1  2)應該擴展為：

因此計算的結果為：

有了這些規(guī)則，就可以進行任意數(shù)組、常數(shù)間的算術運算了。

2.數(shù)組公式

2.1.什么是數(shù)組公式

數(shù)組公式是指可以在數(shù)組的一項或多項上執(zhí)行多個計算的公式。數(shù)組公式可以返回多個結果，也可返回一個結果。

2.2.數(shù)組公式的輸入

輸入數(shù)組公式后，同時按下Ctrl+Shift+Enter，數(shù)組公式的外面會自動加上大括號{}以和普通公式區(qū)分。{}不是手工輸入的，那樣Excel會認為您輸入的是文本格式。數(shù)組公式每次編輯過后，都要同時按下Ctrl+Shift+Enter以完成輸入。

2.3.多單元格公式和單元格公式

如果把數(shù)組公式的返回值放入到一個單元格區(qū)域內(nèi)可以呈現(xiàn)返回數(shù)組的各個元素的值，這樣的公式稱為多單元格公式。而位于單元格中的數(shù)組公式稱為單元格公式。

多單格公式在使用時，需遵循以下原則：

2.4.數(shù)組公式的優(yōu)點

2.5.數(shù)組公式的語法

數(shù)組公式主要使用標準公式語法。它們都以等號開始，可以在數(shù)組公式中使用任何內(nèi)置 Excel 函數(shù)。使用數(shù)組公式的主要不同之處在于:

• 必須按 Ctrl+Shift+Enter 輸入公式
• 輸入多單元格公式時，必須首先選擇用于保存結果的單元格區(qū)域

2.6.數(shù)組函數(shù)示例

1. 計算一個區(qū)域內(nèi)，不重復的數(shù)據(jù)的個數(shù)。（個人認為這是數(shù)組函數(shù)最精彩的例子，正是這個例子激發(fā)了我學習數(shù)組公式的興趣）

{=SUM(1 / COUNTIF(A1:A100, A1:A100) }，這里假設區(qū)域為A1：A100。

分析：COUNTIF(range, criteria)在range范圍內(nèi)查找criteria，并返回它的個數(shù)。假設A1單元格內(nèi)的數(shù)據(jù)為X在A1:A100范圍內(nèi)共有N個數(shù)據(jù)X。那么數(shù)組公式對這100個單元格逐個計算1 / COUNTIF(A1:A100, 某一單元格)，當對A1計算時，返回結果1/N，在這100次計算里，每遇到數(shù)據(jù)X就回返回一個1/N，共返回N個1/N，求和為1。也就是說，有多少個不同的數(shù)據(jù)就返回多少個1，恰好就是不重復的數(shù)據(jù)的個數(shù)。這個公式是多么簡潔優(yōu)美！不過再美好的事物也是有缺點的，統(tǒng)計區(qū)域內(nèi)不得有空單元格，否則返回#DIV/0!錯誤?？梢杂孟旅娴臄?shù)組公式解決這個問題：

{=SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100))}

當然這個問題也可以用sumproduct()、frequency()或match()實現(xiàn)。

2. 計算1+2+3+…..+100的和。（假設不知道等差求和公式）

{=sum(row(A1:A100))} 或 {=sum(row(1:100))}

注意：row()的運算結果可以做很多函數(shù)的參數(shù)。

3. 條件求和

• 求產(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)，則不能工作。起初這另我很不解，但仔細研究and()發(fā)現(xiàn)，and()處理常量數(shù)組和單元格區(qū)域數(shù)組的方式是不同的。當處理常量數(shù)組時，運算結果還是數(shù)組。如=index({TRUE,TRUE},{TURE,FALSE},2)結果為FALSE。當處理受單元格區(qū)域數(shù)組時，會把數(shù)組內(nèi)的所有的數(shù)據(jù)都取出來作為多個參數(shù)，運算最終結果不是一個數(shù)組，而僅僅是一個邏輯值。如=index(and(B2:B15="BB", MONTH(C2:C15)=8),1)會得到#VALUE!。

因此，{=SUM(IF(and(B2:B15="BB", MONTH(C2:C15)=8), (E2:E15), 0))}這個公式在運算時，and(B2:B15="BB", MONTH(C2:C15)=8)的計算值為FALSE，最終計算結果為零。

另外，邏輯值是可以直接參與算術運算的，這時TRUE=1,FALSE=0，因此下面這個公式可以簡化為：

{=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))}

這個公式可以簡化為：

{=SUM(((B2:B15="BB")+(B2:B15="CC"))*E2:E15*D2:D15)}

－The end－