Excel數組和數組函數

數組是Excel中一個非常重要的概念，如LINEST()是做線性加歸的函數，它接受數組作為參數，返回值也是一個數組。為了徹底的弄清楚這樣的函數，好好的研究了一下Excel的數組及數組函數，並作了總結。1.Excel中的數組數組（array，繁體版稱作陣列），是由數據元素組成的集合，數據無素可以是數值,文本,日期,邏輯,錯誤值等。數據元素以行和列的形式組織起來，構成一個數據矩陣。在EXCEL中，根據構成元素的不同，可以把數組分為常量數組和單元格區域數組。1.1.常量數組常量數組可以同時包含多種數據類型。它用{}將構成數組的常量括起來，行中的元素用逗號「，」分隔，行之間用分號「;」分隔。數組常量不能包含其他數組、公式或函數。當輸入如下所示的公式時，Excel 將顯示警告消息：{1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)}。另外，數值不能包含百分號、貨幣符號、逗號或圓括弧。例如，{2,"greensky",TRUE,#N/A, }是一個1行4列的常量數組，而{1,2,3;4,5,6}則為一個2行3列的常量數組。1.2.單元格區域數組單元格區域數組則是通過對一組連續的單元格區域進行引用而得到的數組。在數組公式中{A1:B4}是一個4行2列的單元格區域數組。1.3.Excel數組的維數數組作為數據的組織形式本身可以是多維的，而且眾多編程都支持多維數組，但是Excel的公式中最高只支持2維數組（也就是行列構成的數組）。關於這個結論，在網路搜索不到明確的答案，做這個結論基於以下兩點：Excel不支持形如={{…},{…}}或={{…};{…}}的書寫格式，所以無法表示3維或多維數組。在Excel中提供對數組引用的函數為index()，它的格式為index(array,row_num,column_num)，它只有行列兩個參數，而沒有引用高於2維數組的參數。index函數也有這樣的格式index(reference,row_num,column_num,area_num)，它提供了第三個引用參數，是不是說明Excel提供了對3維數組的引用呢？答案是否定的，reference可以使用多個區域（區域之間有逗號隔開）構成的參數，第三個參數area_num是用於指定reference中第幾個區域的，如INDEX((A1:C6,A8:C11),1,1,2)中第三個參數只是指定區域A8:C11而已。Excel　VBA支持多維數組。1.4.數組的引用在運算過程中，可能需要引用數組中的某一個元素、某一行或某一列。下面介紹元素、行、列的引用方法。元素的引用：對於1維數組index(array, c)，對於2維數組index(array, r, c)。其中r表示第幾行，c表示第幾列，一般為正整數。當r為小數時，會捨棄小數點後的部公（非四捨五入），如index(array, 1.2, 2.9)等同於index(array, 1, 2)。編程語言一般默認以截取的方式處理小數轉整數的操作，這樣可以提高效率，Excel做這樣的處理也是在意料之內的。行引用index(array, r, 0)返回第r行數據構成的數組。列引用Index(array, 0 ,c)返回第c列數據構成的數組。特殊的，index(array, 0, 0)將返回整個2維數組。而index(index(array, r, 0),c)、index(index(array, 0, c), r)和index(array, r, c)相同，都可以返回第r行第c列的元素。1.5.數組的算術運算 和 數組的擴展當數組進行運算（加、減、乘、除、冪）時，要求兩個數組具有相同的維數，如果是2維數組還要求兩數組的行數相同，列數也相同。滿足這個條件的兩個數組的算術運算（加、減、乘、除、冪）等於對應元素之間進行數學運算。如兩個具有3行3列的2維數組之間的加法運算為：如果參與運算的兩個數據的維數不同、行數不同或列數不同，Excel會對數據的維數和行列進行擴展，以滿足上述的要求而進行運算。擴展的規則如下：維數擴展：如果兩數據的維數不同時，維數低的數據會被擴展為和維數高的數據的維數相同。維數的擴展有兩種類型，常數向1維數組或2維數組的擴展、1維數組向2維數組擴展。行列擴展：如果兩數據的行數不同，行數低的數據會擴展為和行數高的數據的行數相同。如果列數不同，列擴展的規則和行擴展相同。如果行列均不相同，那麼行列都需要擴展。根據這些原則，一個M1行、N1列的數組和一個M2行、N2列的數組運算後，得到的是max(M1, M2)行、max(N1, N2)列的數組。這個結論包含了所有類型數據的運算。例如{1,2,3;4,5,6}和{7,8,9,10}運算，前者M1=2,N1=3，後者M1=1,N2=4，結果為2行4列的數組。數據擴展後，出現了一此數據空位，如計算下列數組時首先要把(12)擴展為這時出現了一些數據空位，那麼Excel如何填充這些空位的呢？這裡也有規則可循，個人用實驗的方法總結如下：對常數所有的擴展，空位都填寫該常數。當行數組進行行擴展時，擴展出來的每一行的數據和首行相同；當行數組進行列擴展時，擴展列的數據填寫錯誤值#N/A。當列數組進行列擴展時，擴展出來的每一列的數據和首列相同；當列數組進行行擴展時，擴展行的數據填寫錯誤值#N/A。當2維的數組進行行擴展或列擴展時，擴展出的數據都填寫錯誤值#N/A。因此，上邊那個例子，(12)應該擴展為：因此計算的結果為：有了這些規則，就可以進行任意數組、常數間的算術運算了。2.數組公式2.1.什麼是數組公式數組公式是指可以在數組的一項或多項上執行多個計算的公式。數組公式可以返回多個結果，也可返回一個結果。2.2.數組公式的輸入輸入數組公式後，同時按下Ctrl+Shift+Enter，數組公式的外面會自動加上大括弧{}以和普通公式區分。{}不是手工輸入的，那樣Excel會認為您輸入的是文本格式。數組公式每次編輯過後，都要同時按下Ctrl+Shift+Enter以完成輸入。2.3.多單元格公式和單元格公式如果把數組公式的返回值放入到一個單元格區域內可以呈現返回數組的各個元素的值，這樣的公式稱為多單元格公式。而位於單元格中的數組公式稱為單元格公式。多單格公式在使用時，需遵循以下原則：必須在輸入公式之前選擇用於保存結果的單元格區域，這個區域最好和返回的數組尺寸相同，否則在超出數組尺寸範圍外的單元格填寫#N/A。不能更改多單元格數組公式中部分單元格的內容，但可以移動或刪除整個數組公式。換言之，要編輯數組公式，需先刪除現有公式再重新開始。不能向多單元格數組公式中插入空白單元格（包括插入新行、列、單元格）或刪除其中的單元格。這和「不能更改多單元格數組公式中部分單元格的內容」是一致的。2.4.數組公式的優點簡潔性：數組公式可以同對多個數據執行多個運算。解決一個複雜的問題可以只需要一個公式，而用普通公式可能需要多步運算，甚至要填加輔助列。不過，數組公式可以用幾個一般公式和一些過渡數據來代替，從而達到同樣的目的，所以千萬不要迷信數組公式，以免走火入魔！一致性：多單元格數組公式中，點擊任一個單元格，看到的公式內容都是相同的。這種一致性可以保證更高的準確性。安全性：不能覆蓋多單元格數組公式的組成部分，可以防止誤操作。文件小：通常可以使用單個數組公式，而不必用多個中間公式。這在數據量很大的時候效果才會明顯。2.5.數組公式的語法數組公式主要使用標準公式語法。它們都以等號開始，可以在數組公式中使用任何內置 Excel 函數。使用數組公式的主要不同之處在於:必須按 Ctrl+Shift+Enter 輸入公式輸入多單元格公式時，必須首先選擇用於保存結果的單元格區域2.6.數組函數示例1. 計算一個區域內，不重複的數據的個數。（個人認為這是數組函數最精彩的例子，正是這個例子激發了我學習數組公式的興趣）{=SUM(1 / COUNTIF(A1:A100, A1:A100) }，這裡假設區域為A1：A100。分析：COUNTIF(range, criteria)在range範圍內查找criteria，並返回它的個數。假設A1單元格內的數據為X在A1:A100範圍內共有N個數據X。那麼數組公式對這100個單元格逐個計算1 / COUNTIF(A1:A100, 某一單元格)，當對A1計算時，返回結果1/N，在這100次計算里，每遇到數據X就回返回一個1/N，共返回N個1/N，求和為1。也就是說，有多少個不同的數據就返回多少個1，恰好就是不重複的數據的個數。這個公式是多麼簡潔優美！不過再美好的事物也是有缺點的，統計區域內不得有空單元格，否則返回#DIV/0!錯誤。可以用下面的數組公式解決這個問題：{=SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100))}當然這個問題也可以用sumproduct()、frequency()或match()實現。2. 計算1+2+3+…..+100的和。（假設不知道等差求和公式）{=sum(row(A1:A100))} 或 {=sum(row(1:100))}注意：row()的運算結果可以做很多函數的參數。3. 條件求和ABCD1產品編號生產日期產品單價產品數量2AA2009/6/1511003AA2009/6/2011254BB2009/6/3021505BB2009/7/1021756CC2009/6/1532007CC2009/7/2032258AA2009/7/3012509AA2009/8/10127510BB2009/8/15230011BB2009/8/20232512CC2009/10/10335013CC2009/10/15340014DD2009/10/20442515DD2009/10/304500求產品BB的8月份產量{=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()發現，and()處理常量數組和單元格區域數組的方式是不同的。當處理常量數組時，運算結果還是數組。如=index({TRUE,TRUE},{TURE,FALSE},2)結果為FALSE。當處理受單元格區域數組時，會把數組內的所有的數據都取出來作為多個參數，運算最終結果不是一個數組，而僅僅是一個邏輯值。如=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)}求產品BB和CC的總產值{=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－
推薦閱讀：