簡單地介紹Excel中的數(shù)組公式為進一步的研究和應用打下基礎

Excel中有一類稱作數(shù)組公式的公式，有點難以理解，但很強大，能夠完成很多不可思議的任務。

然而，要想精通并熟練運用數(shù)組公式，需要大量的練習。本文只是簡單地介紹數(shù)組公式，讓感興趣的朋友對其有所了解，為進一步的研究和應用打下基礎。

為什么要學習和使用數(shù)組公式

既然數(shù)組公式比較復雜且難于理解，那為什么還要學習和使用數(shù)組公式呢？

使用數(shù)組公式，可以使Excel完成我們認為不可能的任務，或者說要使用VBA才能完成的任務，并且在有些情形下，數(shù)組公式可能是一個非常有效的解決方案。當然，好奇心也會驅(qū)使我們想要學習進階去創(chuàng)建更高級的公式，并且在學會數(shù)組公式的基本使用后想要創(chuàng)建更有效的數(shù)組公式。在掌握數(shù)組公式后，在面對創(chuàng)建Excel解決方案時又多了一份新的武器。我想，這可能是想要學習數(shù)組公式的一些原因。

在某些情形下，除非使用VBA，使用數(shù)組公式可能是實現(xiàn)目的的唯一辦法。在使用普通的公式時，我們可能需要輔助列或一些中間步驟，然而，數(shù)組公式可以提供一次性的解決方案，省掉中間步驟。當然，高級篩選、數(shù)據(jù)透視表、以及新增的一些數(shù)組函數(shù)能夠解決使用數(shù)組公式實現(xiàn)的任務，但是當輸入發(fā)生變化時它們不能自動更新或者有局限，然而數(shù)組公式能夠立即更新，這是其一大優(yōu)勢。

我們可以使用數(shù)組公式來判斷數(shù)據(jù)是否與指定區(qū)域中的數(shù)據(jù)相匹配，可以統(tǒng)計單元格區(qū)域中不重復值的數(shù)量，可以提取單元格區(qū)域中的不重復數(shù)據(jù)，將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)，…，等等。

什么是數(shù)組公式

在認識數(shù)組公式之前，先看看通常對于下圖1所示的工作表求總銷售額的過程。

 圖1

正如圖1中所看到的，要求這四種水果的總銷售額，先使用公式求出每種水果的銷售額，然后相加即可。總共使用了4個公式。其實，我們可以只使用一個公式來求總銷售額，如圖2所示。

 圖2

在單元格C7中輸入公式：

=SUM(B2:B5*C2:C5)

然后同時按下Ctrl+Shift+Enter鍵完成公式輸入。

這個公式就是數(shù)組公式，即有操作運算符，運算一組數(shù)據(jù)而不是單個數(shù)據(jù)，傳遞的結果也是一組數(shù)組，而最終的結果可能是單個的數(shù)據(jù)，也可能是一組數(shù)據(jù)。

注意，當我們按下Ctrl+Shift+Enter鍵完成輸入后，Excel會自動在公式兩側(cè)添加上花括號{}，無須手工輸入它們。

數(shù)組公式原理

仍以上文所示的工作表為例，看看數(shù)組公式的計算過程，從而了解其運算原理。

首先，公式中的B2:B5與C2:C5分別被單元格數(shù)據(jù)替換成數(shù)組：

=SUM({5.8;1.2;1.1;3.5}*C2:C5)

=SUM({5.8;1.2;1.1;3.5}*{100;350;200;300})

然后，兩個數(shù)組對應元素相乘得到：

=SUM({580;420;220;300})

最后，數(shù)組作為SUM函數(shù)的參數(shù)求和，得到最后的結果2270。

可以看到，數(shù)組公式是處理一組或一系列數(shù)據(jù)而不是單個數(shù)據(jù)的公式。它能夠返回單個的值，如本例所示，也能夠返回一組數(shù)據(jù)，如下面的例子。

選擇一列中任意9個單元格，輸入下面的數(shù)組公式：

=ROW(1:9)

結果如下圖3所示。

 圖3

該公式產(chǎn)生一個數(shù)組{1;2;3;4;5;6;7;8;9},并將它們依次輸入到單元格。

注意，輸入完公式后，一定要記得按下Ctrl+Shift+Enter鍵。

本文中，凡是要求輸入數(shù)組公式的，都是指在輸入完公式內(nèi)容后再按Ctrl+Shift+Enter鍵，這才完成了數(shù)組公式的輸入。

再看一個例子，了解數(shù)組公式的邏輯運算。

如下圖4所示的工作表，記錄著各種產(chǎn)品由不同的銷售人所售賣的數(shù)量。

 圖4

想要計算張三或者李四所銷售的手機數(shù)量，可以使用數(shù)組公式：

=SUM((A2:A10="手機")*((B2:B10="張三")+(B2:B10="李四"))*(C2:C10))

結果如下圖5所示。

 圖5

這個公式創(chuàng)建了3個數(shù)組：

第一個數(shù)組是一系列的TRUE或FALSE值，是由單元格區(qū)域A2:A10中的數(shù)據(jù)與“手機”比較后的結果。注意，Excel會將“手機”的數(shù)量擴展到與所比較的單元格數(shù)量相同（下面的相同）。結果數(shù)組為：{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}。

第二個數(shù)組由一系列的0或1組成，是由單元格B2:B10中的數(shù)據(jù)與“張三”和“李四”比較后的運算結果。其中，單元格區(qū)域B2:B10與“張三”比較生成一系列TRUE或FALSE值組成的數(shù)組：{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}，與“李四”比較也生成一系列TRUE或FALSE值組成的數(shù)組：{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}。將這兩個數(shù)組相加，此時Excel將TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0，得到數(shù)組{1;0;1;1;1;1;1;1;0}。

第三個數(shù)組由單元格C2:C10中的數(shù)值組成，即{1200;200;300;120;220;50;600;100;800}。

然后，將這3個數(shù)組對應的元素相乘。與前面數(shù)組相加相同，在數(shù)組相乘時，Excel將TRUE和FALSE分別轉(zhuǎn)換為1和0。因此，3個數(shù)組相乘的公式為：

{1;0;1;0;0;0;1;0;1}*{1;0;1;1;1;1;1;1;0}*{1200;200;300;120;220;50;600;100;800}

這3個數(shù)組相乘的結果也是一個數(shù)組，其每個元素為這3個數(shù)組對應元素相乘的結果，即：

{1200;0;300;0;0;0;600;0;0}

該數(shù)組作為SUM函數(shù)的參數(shù)得到最終的結果為2100（=1200+300+600），即張三和李四銷售的手機數(shù)量。

詳細的運算過程如下圖6所示。

 圖6

可以看出：

數(shù)組公式中將乘法（*）用于邏輯與，即遵守與AND操作相同的規(guī)則；將加法（+）用于邏輯或，遵守與OR操作相同的規(guī)則。MOD運算模擬異或XOR操作。

在進行算術運算時，Excel會將TRUE和FALSE轉(zhuǎn)換成數(shù)值1和0。

數(shù)組公式示例

下面列舉兩個簡單的示例，更多的示例參見后面鏈接的內(nèi)容。

示例1：計算及格的學生成績的平均值

在圖7所示的工作表中，求及格的學生成績的平均值，使用數(shù)組公式：

=AVERAGE(IF(B2:B9>60,B2:B9,FALSE))

 圖7

這個數(shù)組公式使用IF函數(shù)來測試多個單元格。比較單元格區(qū)域B2:B9中的數(shù)值是否大于60，返回一個包含布爾值的數(shù)組{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}，然后IF函數(shù)根據(jù)該數(shù)組中的值，如果為TRUE則返回B2:B9中的值，為FALSE則返回FALSE。擴展后的公式如下：

=AVERAGE(IF({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},{89;92;78;56;88;58;95;55},{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}))

注意，在原公式最后的FALSE被擴展成一個與前面數(shù)組相匹配的適合大小的數(shù)組。

在IF函數(shù)測試完成后，得到下面的中間結果：

=AVERAGE({89;92;78;FALSE;88;FALSE;95;FALSE})

AVERAGE函數(shù)忽略布爾值（TRUE或FALSE），僅對數(shù)值求平均值。

示例2：計算排名前3的學生成績之和

仍以圖7所示的工作表為例，要求前3名的學生成績之和。

可以使用數(shù)組公式：

=SUM(LARGE(B2:B9,ROW(1:3)))

其中，ROW(1:3)返回數(shù)組{1;2;3}。LARGE函數(shù)依次取單元格區(qū)域B2:B9中的最大值、第2大及第3大值，返回數(shù)組{95;92;89}，然后將其作為SUM函數(shù)的參數(shù)求和。

示例3：計算數(shù)值單元格中各數(shù)字之和

在單元格A1中的數(shù)值為12345，要通過公式得出1+2+3+4+5的值。

可以使用數(shù)組公式：

=SUM(1*MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1))

LEN函數(shù)計算單元格A1中字符的長度，因此上面的公式變?yōu)椋?/p>

=SUM(1*MID(A1,ROW(INDIRECT("1:6")),1))

INDIRECT函數(shù)返回對第1至6行的引用：

=SUM(1*MID(A1,ROW(1:6),1))

然后擴展成為：

=SUM(1*MID(A1,{1;2;3;4;5;6},1))

MID函數(shù)依次取單元格A1中的字符，每次1個：

=SUM(1*{“1”;”2”;”3”;”4”;”5”;”6”})

將1與數(shù)字文本相乘將其轉(zhuǎn)換為數(shù)字：

=SUM({1;2;3;4;5;6})

最后，SUM函數(shù)對數(shù)字數(shù)組求和

版權聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權內(nèi)容。