前文回顧：菜鳥(niǎo)的Excel函數(shù)修煉手冊(cè) 第3課 從發(fā)現(xiàn)規(guī)律開(kāi)始——公式三板斧第三式
4.1 CSE公式是個(gè)什么鬼
在之前的學(xué)習(xí)中，我們知道了什么是公式：公式是Excel中以等號(hào)開(kāi)頭的可以得到一個(gè)結(jié)果的等式，公式以等號(hào)“=”開(kāi)頭，公式中可以包括函數(shù)、運(yùn)算符、引用和常量。
相對(duì)于普通公式而言，在Excel中還有另一種形式的公式：數(shù)組公式。
數(shù)組公式通常也被稱作 CSE (Ctrl-Shift-Enter) 公式，因?yàn)檩斎牍胶蟛皇侵话?Enter 鍵，而是要按 Ctrl-Shift-Enter 完成公式的輸入。
數(shù)組公式可以對(duì)兩組或多組數(shù)據(jù)執(zhí)行運(yùn)算，執(zhí)行多項(xiàng)計(jì)算后返回一個(gè)或多個(gè)結(jié)果。每一組數(shù)據(jù)就是一個(gè)數(shù)組，數(shù)組可以是同一行或者同一列中的多個(gè)數(shù)據(jù)，也可以是一個(gè)多行多列的區(qū)域中的數(shù)據(jù)。
與普通公式相同，數(shù)組公式同樣需要以等號(hào)“=”開(kāi)頭，在創(chuàng)建數(shù)組公式時(shí)，有兩種不同的方式，針對(duì)一個(gè)單元格創(chuàng)建的數(shù)組公式和針對(duì)單元格區(qū)域創(chuàng)建的數(shù)組公式。
4.2 數(shù)組公式的兩種形態(tài)
4.2.1 單元格數(shù)組公式
下面通過(guò)一個(gè)常見(jiàn)的示例了解單元格數(shù)組公式，如圖4-1所示，C列是商品單價(jià)，D列是銷售數(shù)量，使用數(shù)組公式=SUM(C2:C11*D2:D11)可以直接計(jì)算出總銷售額。
圖4-1
使用數(shù)組公式時(shí)，不需要單獨(dú)計(jì)算出每個(gè)商品的銷售額，數(shù)組公式 =SUM(C2:C11*D2:D11) 與 公式=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11) 的意義相同。
數(shù)組公式輸入完成后，同時(shí)按下 Ctrl-Shift-Enter三個(gè)鍵之后，會(huì)自動(dòng)在公式兩端添加大括號(hào)，并計(jì)算出結(jié)果。如果手動(dòng)鍵入大括號(hào)，公式將轉(zhuǎn)換為文本字符串，并不會(huì)得到結(jié)果，如圖4-2所示。
圖4-2
4.2.2 區(qū)域數(shù)組公式
與單元格數(shù)組公式不同，使用區(qū)域數(shù)組公式時(shí)，需要先選中要?jiǎng)?chuàng)建公式的單元格區(qū)域，例如要計(jì)算每個(gè)商品的銷售額，需要先選中E2到E17這個(gè)區(qū)域，然后輸入公式=C2:C11*D2:D11，如圖4-3所示。
圖4-3
同時(shí)按下Ctrl-Shift-Enter三個(gè)鍵，所選單元格會(huì)同時(shí)創(chuàng)建公式，結(jié)果如圖4-4所示。
圖4-4
這個(gè)例子中，使用普通公式=C2*D2下拉，與使用數(shù)組公式=C2:C11*D2:D11表面看上去沒(méi)什么區(qū)別，實(shí)際上使用數(shù)組公式有以下幾個(gè)優(yōu)點(diǎn)：
1. 一致性：顯示公式后，您將看到相同的公式，如圖4-5所示，這種一致性可以使公式具有更高的準(zhǔn)確性。
圖4-5
2. 安全性：不能單獨(dú)修改或者刪除其中某個(gè)單元格的數(shù)組公式。例如單擊單元格 E3，然后按 Delete刪除公式時(shí)，就會(huì)出現(xiàn)提示，如圖4-6所示。
圖4-6
如果要修改或者刪除區(qū)域數(shù)組公式，必須選擇整個(gè)單元格區(qū)域（E2到E11），然后更改整個(gè)數(shù)組的公式，修改后依然需要同時(shí)按Ctrl-Shift-Enter三個(gè)按鍵確認(rèn)對(duì)公式的更改。
3.高效：使用普通公式（例如=C2*D2, =C3*D3, =C4*D4…），要使用11個(gè)不同的公式，使用數(shù)組公式只需要一個(gè)相同的公式，而計(jì)算得出的結(jié)果并無(wú)差別。
4.3 公式有數(shù)組，常量也有數(shù)組
數(shù)組常量是數(shù)組公式的組成部分?？梢酝ㄟ^(guò)輸入一組常量后手動(dòng)用大括號(hào)將這些常量括起來(lái)創(chuàng)建數(shù)組常量，類似于：={1,2,3,4,5}。
數(shù)組常量經(jīng)常用來(lái)簡(jiǎn)化公式，例如要計(jì)算銷售額排在前三名的金額合計(jì)，可以使用公式=SUM(LARGE(E2:E11,{1,2,3}))，結(jié)果如圖4-7所示。
圖4-7
LARGE函數(shù)的功能是返回一組數(shù)字中的第幾個(gè)最大值，例如LARGE(E2:E11,4)就可以得到E2:E11這個(gè)區(qū)域中第四大的值。在LARGE(E2:E11,{1,2,3})中，第二個(gè)參數(shù)使用了常量數(shù)組，相當(dāng)于三個(gè)LARGE的效果，分別是LARGE(E2:E11,1)、LARGE(E2:E11,2)和LARGE(E2:E11,3)，可以利用F9鍵看到結(jié)果，如圖4-8所示。
圖4-8
使用了一個(gè)常量數(shù)組，就把三個(gè)LARGE組合成一個(gè)，達(dá)到了簡(jiǎn)化公式的目的。
需要注意的是，在圖4-7所示的公式中，并沒(méi)有按三鍵輸入公式，結(jié)果依然正確。
如果把常量數(shù)組中的1、2、3放在單元格中，公式修改為=SUM(LARGE(E2:E11,G1:G3))，則必須按三鍵輸入公式，否則就會(huì)得到錯(cuò)誤值，如圖4-9所示。
圖4-9
這是因?yàn)槌Ａ繑?shù)組是明確的數(shù)組，Excel直接會(huì)進(jìn)行數(shù)組運(yùn)算，而引用單元格區(qū)域作為數(shù)組時(shí)，必須通過(guò)三鍵的形式告訴Excel要按數(shù)組去運(yùn)算。
如果分不清什么時(shí)候需要三鍵的話，那就統(tǒng)統(tǒng)都按三鍵完成，一定是沒(méi)問(wèn)題的。
4.4 讓人頭暈?zāi)垦５木S度
4.4.1 數(shù)組的逗號(hào)和分號(hào)
要理解數(shù)組之間的運(yùn)算原理，必須了解數(shù)組的維度和方向。
如果使用逗號(hào)分隔各個(gè)項(xiàng)，將創(chuàng)建水平數(shù)組（一行）。如果使用分號(hào)分隔項(xiàng)，將創(chuàng)建垂直數(shù)組（一列）。若要?jiǎng)?chuàng)建二維數(shù)組，應(yīng)在每行中使用逗號(hào)分隔項(xiàng)，并使用分號(hào)分隔每行。
示例1. {1,2,3,4}是一個(gè)單行數(shù)組，數(shù)組中的值以逗號(hào)分隔。選擇同一行的六個(gè)單元格，輸入公式={1,2,3,4}，同時(shí)按下Ctrl-Shift-Enter結(jié)果如圖4-10所示。
圖4-10
因?yàn)閿?shù)組中只有四個(gè)數(shù)據(jù)，所以前四個(gè)單元格依次顯示數(shù)組中對(duì)應(yīng)的數(shù)據(jù)，后面兩個(gè)單元格是錯(cuò)誤值。
選中同一列的六個(gè)單元格，輸入同樣的公式，結(jié)果如圖4-11所示。
圖4-11
因?yàn)閿?shù)組的方向與單元格的方向不一致，所以單元格中得到的都是數(shù)組中第一個(gè)數(shù)據(jù)。
示例2. {1;2;3;4}是一個(gè)單列數(shù)組，數(shù)組中的值以分號(hào)分隔。選擇同一行的六個(gè)單元格，輸入公式={1;2;3;4}，同時(shí)按下Ctrl-Shift-Enter結(jié)果如圖4-12所示。
圖4-12
因?yàn)閿?shù)組的方向與單元格的方向不一致，所以單元格中得到的都是數(shù)組中第一個(gè)數(shù)據(jù)。
選中同一列的六個(gè)單元格，輸入同樣的公式，結(jié)果如圖4-13所示。
圖4-13
因?yàn)閿?shù)組中只有四個(gè)數(shù)據(jù)，所以前四個(gè)單元格依次顯示數(shù)組中對(duì)應(yīng)的數(shù)據(jù)，后面兩個(gè)單元格是錯(cuò)誤值。
示例3. {1,2,3,4;5,6,7,8}是一個(gè)兩行四列的數(shù)組，逗號(hào)代表同一行，分號(hào)代表同一列。在一個(gè)單元格區(qū)域中，同時(shí)按下Ctrl-Shift-Enter三鍵輸入公式={1,2,3,4;5,6,7,8}，結(jié)果如圖4-14所示。
圖4-14
在區(qū)域左上角的兩行四列單元格中，顯示數(shù)組中對(duì)應(yīng)的值，超過(guò)這個(gè)范圍的其他單元格都是錯(cuò)誤值。
示例1和示例2中只有一個(gè)方向（單行或單列）的數(shù)組是一維數(shù)組，示例3中同時(shí)存在兩個(gè)方向的數(shù)組是二維數(shù)組。
4.4.2 數(shù)組的運(yùn)算規(guī)則
1.單值與數(shù)組的運(yùn)算規(guī)則：?jiǎn)沃蹬c數(shù)組中的每個(gè)值進(jìn)行計(jì)算，得到與數(shù)組同方向同緯度的數(shù)據(jù)。
示例1.數(shù)字3與一維數(shù)組{1,2,3,4}相乘，公式為=3*{1,2,3,4}，結(jié)果如圖4-15所示。
圖4-15
示例2.數(shù)字3與二維數(shù)組{1,2,3,4;5,6,7,8}相乘，公式為=3*{1,2,3,4;5,6,7,8}，結(jié)果如圖4-16所示。
圖4-16
2.同向一維數(shù)組的運(yùn)算規(guī)則：兩個(gè)數(shù)組中對(duì)應(yīng)的數(shù)據(jù)分別計(jì)算，進(jìn)行計(jì)算的兩個(gè)數(shù)組中的數(shù)據(jù)個(gè)數(shù)必須相同，否則多余部分返回錯(cuò)誤值。
示例3.數(shù)組{1;2;3}+{4;5;6}返回{5;7;9}，結(jié)果如圖4-17所示。
圖4-17
示例4.數(shù)組{1,2,3,4}+{4,5,6}返回{5,7,9,#N/A}，結(jié)果如圖4-18所示。
圖4-18
3. 異向一維數(shù)組的運(yùn)算規(guī)則：數(shù)組1的每一個(gè)數(shù)據(jù)與數(shù)組2的每一個(gè)數(shù)據(jù)分別運(yùn)算并返回結(jié)果，得到兩個(gè)數(shù)組的行數(shù)*列數(shù)個(gè)元素，也就是M行數(shù)組與N列數(shù)組運(yùn)算結(jié)果為M*N的矩陣數(shù)組。
示例5. 數(shù)組{1;2;3}+{4,5,6,7,8}，執(zhí)行1+4、1+5……1+8、2+4、2+5……3+8，結(jié)果如圖4-19所示。
圖4-19
4. 一維數(shù)組與二維數(shù)組的運(yùn)算規(guī)則：二維數(shù)組其中一個(gè)方向與一維數(shù)組同向同尺寸，比如M*N的二維數(shù)組與M行或者N列的數(shù)組運(yùn)算，在同向的運(yùn)算類似同向一維數(shù)組之間的位置對(duì)應(yīng)，在異向的運(yùn)算則類似單值與數(shù)組之間的一一運(yùn)算。超出尺寸范圍都返回錯(cuò)誤值。
示例6. 數(shù)組{1,2,3,4}+{1,2,3,4,5;6,7,8,9,10}即四列一維數(shù)組與兩行五列二維數(shù)組相加，列方向上兩個(gè)數(shù)組運(yùn)算按位置一一對(duì)應(yīng)的，不足部分用#N/A補(bǔ)齊，行方向上則是一對(duì)多的運(yùn)算，結(jié)果如圖4-20所示。
圖4-20
提示：結(jié)果中的2、4、6、8為第一個(gè)數(shù)組中的數(shù)據(jù)與第二個(gè)數(shù)組中的1、2、3、4相加所得；結(jié)果中的7、9、11、13為第一個(gè)數(shù)組中的數(shù)據(jù)與第二個(gè)數(shù)組中的6、7、8、9相加所得；其他位置無(wú)法對(duì)應(yīng)均返回錯(cuò)誤值。
5. 二維數(shù)組之間的運(yùn)算規(guī)則：要求參加運(yùn)算的二維數(shù)組具有相同的尺寸，相同位置的數(shù)據(jù)一一對(duì)應(yīng)進(jìn)行計(jì)算，無(wú)法對(duì)應(yīng)的位置則會(huì)返回錯(cuò)誤值。
示例7. 數(shù)組{1,2;3,4}+{1,2,3,4,5;6,7,8,9,10}即兩行兩列的數(shù)組與兩行五列二維數(shù)組相加，數(shù)組中相同位置一一對(duì)應(yīng)相加，無(wú)法對(duì)應(yīng)的部分用#N/A補(bǔ)齊，結(jié)果如圖4-21所示。
圖4-21
數(shù)組的維度和運(yùn)算規(guī)則屬于公式應(yīng)用中比較有難度的部分，初學(xué)階段只需要了解逗號(hào)和分號(hào)與數(shù)組方向之間的關(guān)系，掌握一維數(shù)組之間的計(jì)算規(guī)則即可，隨著使用經(jīng)驗(yàn)的增加，再慢慢去理解其他計(jì)算規(guī)則。
下節(jié)預(yù)告
上課方式：QQ群視頻直播，課后可以下載視頻反復(fù)看。
報(bào)名費(fèi)用：9.9元體驗(yàn)一周實(shí)戰(zhàn)課程，5月1日起包月價(jià)格調(diào)整為50元，包年價(jià)300元終身價(jià)666元維持不變。
掃碼加Excel交流群
交流心得 解決問(wèn)題
驗(yàn)證信息：Excel
聯(lián)系客服
