Excel中有一類稱作數(shù)組公式的公式，相對(duì)比較復(fù)雜，有點(diǎn)難以理解，很多人望而卻步，然而數(shù)組公式很強(qiáng)大，能夠完成很多不可思議的任務(wù)，絕對(duì)值得學(xué)習(xí)。
使用數(shù)組公式，可以判斷數(shù)據(jù)是否與指定區(qū)域中的數(shù)據(jù)相匹配，可以統(tǒng)計(jì)單元格區(qū)域中不重復(fù)值的數(shù)量，可以提取單元格區(qū)域中的不重復(fù)數(shù)據(jù)，將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)，…，等等。本文只是簡單地介紹數(shù)組公式，讓感興趣的朋友對(duì)其有所了解，為進(jìn)一步的研究和應(yīng)用打下基礎(chǔ)。
為什么要學(xué)習(xí)和使用數(shù)組公式
使用數(shù)組公式，可以使Excel完成我們認(rèn)為不可能的任務(wù)，或者說要使用VBA才能完成的任務(wù)，并且在有些情形下，數(shù)組公式可能是一個(gè)非常有效的解決方案。當(dāng)然，好奇心也會(huì)驅(qū)使我們想要學(xué)習(xí)進(jìn)階去創(chuàng)建更高級(jí)的公式，并且在學(xué)會(huì)數(shù)組公式的基本使用后想要?jiǎng)?chuàng)建更有效的數(shù)組公式。在掌握數(shù)組公式后，在面對(duì)創(chuàng)建Excel解決方案時(shí)又多了一份新的武器。我想，這可能是想要學(xué)習(xí)數(shù)組公式的一些原因。
在某些情形下，除非使用VBA，使用數(shù)組公式可能是實(shí)現(xiàn)目的的唯一辦法。在使用普通的公式時(shí)，我們可能需要輔助列或一些中間步驟，然而，數(shù)組公式可以提供一次性的解決方案，省掉中間步驟。當(dāng)然，高級(jí)篩選、數(shù)據(jù)透視表、以及新增的一些數(shù)組函數(shù)能夠解決使用數(shù)組公式實(shí)現(xiàn)的任務(wù)，但是當(dāng)輸入發(fā)生變化時(shí)它們不能自動(dòng)更新或者存在某些局限，然而數(shù)組公式能夠立即更新，這是其一大優(yōu)勢(shì)。
什么是數(shù)組公式
在認(rèn)識(shí)數(shù)組公式之前，先看看通常對(duì)于下圖1所示的工作表求總銷售額的過程。
圖1
正如圖1中所看到的，要求這四種水果的總銷售額，先使用公式求出每種水果的銷售額，然后相加即可?？偣彩褂昧?/span>4個(gè)公式。其實(shí)，我們可以只使用一個(gè)公式來求總銷售額，如圖2所示。
圖2
在單元格C7中輸入公式：
=SUM(B2:B5*C2:C5)
然后同時(shí)按下Ctrl Shift Enter鍵完成公式輸入。
這個(gè)公式就是數(shù)組公式，即有操作運(yùn)算符，運(yùn)算一組數(shù)據(jù)而不是單個(gè)數(shù)據(jù)，傳遞的結(jié)果也是一組數(shù)組，而最終的結(jié)果可能是單個(gè)的數(shù)據(jù)，也可能是一組數(shù)據(jù)。
注意，當(dāng)我們按下Ctrl Shift Enter鍵完成輸入后，Excel會(huì)自動(dòng)在公式兩側(cè)添加上花括號(hào){}，無須手工輸入它們。
數(shù)組公式原理
仍以上文所示的工作表為例，看看數(shù)組公式的計(jì)算過程，從而了解其運(yùn)算原理。
首先，公式中的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})
然后，兩個(gè)數(shù)組對(duì)應(yīng)元素相乘得到：
=SUM({580;420;220;300})
最后，數(shù)組作為SUM函數(shù)的參數(shù)求和，得到最后的結(jié)果2270。
可以看到，數(shù)組公式是處理一組或一系列數(shù)據(jù)而不是單個(gè)數(shù)據(jù)的公式。它能夠返回單個(gè)的值，如本例所示，也能夠返回一組數(shù)據(jù)，如下面的例子。
選擇一列中任意9個(gè)單元格，輸入下面的數(shù)組公式：
=ROW(1:9)
結(jié)果如下圖3所示。
圖3
該公式產(chǎn)生一個(gè)數(shù)組{1;2;3;4;5;6;7;8;9},并將它們依次輸入到單元格。
注意，輸入完公式后，一定要記得按下Ctrl Shift Enter鍵。
本文中，凡是要求輸入數(shù)組公式的，都是指在輸入完公式內(nèi)容后再按Ctrl Shift Enter鍵，這才完成了數(shù)組公式的輸入。
再看一個(gè)例子，了解數(shù)組公式的邏輯運(yùn)算。
如下圖4所示的工作表，記錄著各種產(chǎn)品由不同的銷售人所售賣的數(shù)量。
圖4
想要計(jì)算張三或者李四所銷售的手機(jī)數(shù)量，可以使用數(shù)組公式：
=SUM((A2:A10='手機(jī)')*((B2:B10='張三') (B2:B10='李四'))*(C2:C10))
結(jié)果如下圖5所示。
圖5
這個(gè)公式創(chuàng)建了3個(gè)數(shù)組：
第一個(gè)數(shù)組是一系列的TRUE或FALSE值，是由單元格區(qū)域A2:A10中的數(shù)據(jù)與“手機(jī)”比較后的結(jié)果。注意，Excel會(huì)將“手機(jī)”的數(shù)量擴(kuò)展到與所比較的單元格數(shù)量相同（下面的相同）。結(jié)果數(shù)組為：{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}。
第二個(gè)數(shù)組由一系列的0或1組成，是由單元格B2:B10中的數(shù)據(jù)與“張三”和“李四”比較后的運(yùn)算結(jié)果。其中，單元格區(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}。將這兩個(gè)數(shù)組相加，此時(shí)Excel將TRUE轉(zhuǎn)換為1，FALSE轉(zhuǎn)換為0，得到數(shù)組{1;0;1;1;1;1;1;1;0}。
第三個(gè)數(shù)組由單元格C2:C10中的數(shù)值組成，即{1200;200;300;120;220;50;600;100;800}。
然后，將這3個(gè)數(shù)組對(duì)應(yīng)的元素相乘。與前面數(shù)組相加相同，在數(shù)組相乘時(shí)，Excel將TRUE和FALSE分別轉(zhuǎn)換為1和0。因此，3個(gè)數(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個(gè)數(shù)組相乘的結(jié)果也是一個(gè)數(shù)組，其每個(gè)元素為這3個(gè)數(shù)組對(duì)應(yīng)元素相乘的結(jié)果，即：
{1200;0;300;0;0;0;600;0;0}
該數(shù)組作為SUM函數(shù)的參數(shù)得到最終的結(jié)果為2100（=1200 300 600），即張三和李四銷售的手機(jī)數(shù)量。
詳細(xì)的運(yùn)算過程如下圖6所示。
圖6
可以看出：
數(shù)組公式中將乘法（*）用于邏輯與，即遵守與AND操作相同的規(guī)則；將加法（ ）用于邏輯或，遵守與OR操作相同的規(guī)則。MOD運(yùn)算模擬異或XOR操作。
在進(jìn)行算術(shù)運(yùn)算時(shí)，Excel會(huì)將TRUE和FALSE轉(zhuǎn)換成數(shù)值1和0。
數(shù)組公式示例
在完美Excel微信公眾號(hào)（公眾號(hào)名：excelperfect）中，有很多數(shù)組公式應(yīng)用示例可供參考學(xué)習(xí)。下面列舉3個(gè)簡單的示例，幫助讀者進(jìn)一步理解數(shù)組公式原理。
示例1：計(jì)算及格的學(xué)生成績的平均值
在圖7所示的工作表中，求及格的學(xué)生成績的平均值，使用數(shù)組公式：
=AVERAGE(IF(B2:B9>60,B2:B9,FALSE))
圖7
這個(gè)數(shù)組公式使用IF函數(shù)來測(cè)試多個(gè)單元格。比較單元格區(qū)域B2:B9中的數(shù)值是否大于60，返回一個(gè)包含布爾值的數(shù)組{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}，然后IF函數(shù)根據(jù)該數(shù)組中的值，如果為TRUE則返回B2:B9中的值，為FALSE則返回FALSE。擴(kuò)展后的公式如下：
=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被擴(kuò)展成一個(gè)與前面數(shù)組相匹配的適合大小的數(shù)組。
在IF函數(shù)測(cè)試完成后，得到下面的中間結(jié)果：
=AVERAGE({89;92;78;FALSE;88;FALSE;95;FALSE})
AVERAGE函數(shù)忽略布爾值（TRUE或FALSE），僅對(duì)數(shù)值求平均值。
示例2：計(jì)算排名前3的學(xué)生成績之和
仍以圖7所示的工作表為例，要求前3名的學(xué)生成績之和。
可以使用數(shù)組公式：
=SUM(LARGE(B2:B9,ROW(1:3)))
其中，ROW(1:3)返回?cái)?shù)組{1;2;3}。LARGE函數(shù)依次取單元格區(qū)域B2:B9中的最大值、第2大及第3大值，返回?cái)?shù)組{95;92;89}，然后將其作為SUM函數(shù)的參數(shù)求和。
示例3：計(jì)算數(shù)值單元格中各數(shù)字之和
在單元格A1中的數(shù)值為12345，要通過公式得出1 2 3 4 5的值。
可以使用數(shù)組公式：
=SUM(1*MID(A1,ROW(INDIRECT('1:'& LEN(A1))),1))
LEN函數(shù)計(jì)算單元格A1中字符的長度，因此上面的公式變?yōu)椋?/span>
=SUM(1*MID(A1,ROW(INDIRECT('1:6')),1))
INDIRECT函數(shù)返回對(duì)第1至6行的引用：
=SUM(1*MID(A1,ROW(1:6),1))
然后擴(kuò)展成為：
=SUM(1*MID(A1,{1;2;3;4;5;6},1))
MID函數(shù)依次取單元格A1中的字符，每次1個(gè)：
=SUM(1*{“1”;”2”;”3”;”4”;”5”;”6”})
將1與數(shù)字文本相乘將其轉(zhuǎn)換為數(shù)字：
=SUM({1;2;3;4;5;6})
最后，SUM函數(shù)對(duì)數(shù)字?jǐn)?shù)組求和。
結(jié)語
數(shù)組公式大大擴(kuò)展了公式的能力，為Excel的應(yīng)用開辟了一片新天地。然而，要想精通并熟練運(yùn)用數(shù)組公式，需要大量深入的練習(xí)，但絕對(duì)值得學(xué)習(xí)。
聯(lián)系客服
