Excel公式與函數之美07:公式中的王者——數組公式

Excel中有一類稱作數組公式的公式,相對比較複雜,有點難以理解,很多人望而卻步,然而數組公式很強大,能夠完成很多不可思議的任務,絕對值得學習。

 

使用數組公式,可以判斷數據是否與指定區域中的數據相匹配,可以統計單元格區域中不重複值的數量,可以提取單元格區域中的不重複數據,將列數據轉換為行數據,…,等等。本文只是簡單地介紹數組公式,讓感興趣的朋友對其有所了解,為進一步的研究和應用打下基礎。

 

為什麼要學習和使用數組公式

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

 

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

 

什麼是數組公式

在認識數組公式之前,先看看通常對於下圖1所示的工作表求總銷售額的過程。

圖1

 

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

圖2

 

在單元格C7中輸入公式:

=SUM(B2:B5*C2:C5)

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

 

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

 

注意,當我們按下Ctrl Shift Enter鍵完成輸入後,Excel會自動在公式兩側添加上花括弧{},無須手工輸入它們。

 

數組公式原理

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

首先,公式中的B2:B5與C2:C5分別被單元格數據替換成數組:

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

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

然後,兩個數組對應元素相乘得到:

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

最後,數組作為SUM函數的參數求和,得到最後的結果2270。

 

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

選擇一列中任意9個單元格,輸入下面的數組公式:

=ROW(1:9)

結果如下圖3所示。

圖3

該公式產生一個數組{1;2;3;4;5;6;7;8;9},並將它們依次輸入到單元格。

 

注意,輸入完公式後,一定要記得按下Ctrl Shift Enter鍵。

本文中,凡是要求輸入數組公式的,都是指在輸入完公式內容後再按Ctrl Shift Enter鍵,這才完成了數組公式的輸入。

 

再看一個例子,了解數組公式的邏輯運算。

如下圖4所示的工作表,記錄著各種產品由不同的銷售人所售賣的數量。

圖4

想要計算張三或者李四所銷售的手機數量,可以使用數組公式:

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

結果如下圖5所示。

圖5

這個公式創建了3個數組:

第一個數組是一系列的TRUE或FALSE值,是由單元格區域A2:A10中的數據與「手機」比較後的結果。注意,Excel會將「手機」的數量擴展到與所比較的單元格數量相同(下面的相同)。結果數組為:{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}。

第二個數組由一系列的0或1組成,是由單元格B2:B10中的數據與「張三」和「李四」比較後的運算結果。其中,單元格區域B2:B10與「張三」比較生成一系列TRUE或FALSE值組成的數組:{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE},與「李四」比較也生成一系列TRUE或FALSE值組成的數組:{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}。將這兩個數組相加,此時Excel將TRUE轉換為1,FALSE轉換為0,得到數組{1;0;1;1;1;1;1;1;0}。

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

 

然後,將這3個數組對應的元素相乘。與前面數組相加相同,在數組相乘時,Excel將TRUE和FALSE分別轉換為1和0。因此,3個數組相乘的公式為:

{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個數組相乘的結果也是一個數組,其每個元素為這3個數組對應元素相乘的結果,即:

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

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

 

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

圖6

可以看出:

  • 數組公式中將乘法(*)用於邏輯與,即遵守與AND操作相同的規則;將加法( )用於邏輯或,遵守與OR操作相同的規則。MOD運算模擬異或XOR操作。

  • 在進行算術運算時,Excel會將TRUE和FALSE轉換成數值1和0。

 

數組公式示例

在完美Excel微信公眾號(公眾號名:excelperfect)中,有很多數組公式應用示例可供參考學習。下面列舉3個簡單的示例,幫助讀者進一步理解數組公式原理。

 

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

在圖7所示的工作表中,求及格的學生成績的平均值,使用數組公式:

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

圖7

這個數組公式使用IF函數來測試多個單元格。比較單元格區域B2:B9中的數值是否大於60,返回一個包含布爾值的數組{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},然後IF函數根據該數組中的值,如果為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被擴展成一個與前面數組相匹配的適合大小的數組。

在IF函數測試完成後,得到下面的中間結果:

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

AVERAGE函數忽略布爾值(TRUE或FALSE),僅對數值求平均值。

 

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

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

可以使用數組公式:

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

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

 

示例3:計算數值單元格中各數字之和

在單元格A1中的數值為12345,要通過公式得出1 2 3 4 5的值。

可以使用數組公式:

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

 

LEN函數計算單元格A1中字元的長度,因此上面的公式變為:

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

INDIRECT函數返回對第1至6行的引用:

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

然後擴展成為:

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

MID函數依次取單元格A1中的字元,每次1個:

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

將1與數字文本相乘將其轉換為數字:

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

最後,SUM函數對數字數組求和。

 

結語

數組公式大大擴展了公式的能力,為Excel的應用開闢了一片新天地。然而,要想精通並熟練運用數組公式,需要大量深入的練習,但絕對值得學習。

 


推薦閱讀:

你覺得特有用的excel 公式或技巧是什麼?
如何在excel中實現單格內多數據分解到單格單數據?
Excel公式練習8:獲取單元格區域中的不重複值
EXCEL計算結果都是0.2,為什麼用 IF 函數判斷返回的結果不一樣?
excel保留一位小數時,如何設置當小數為0時不顯示0,而是顯示整數呢?

TAG:公式 | 函數 | Excel公式 | Excel | 數組 |