Excel 中數(shù)組公式非常有用， 可建立產(chǎn)生多值或?qū)σ唤M值而不是單個值進(jìn)行操作的公式。 掌握數(shù)組公式的相關(guān)技能技巧， 當(dāng)在不能使用工作表函數(shù)直接得到結(jié)果， 又需要對一組或多組數(shù)據(jù)進(jìn)行多重計算時，方可大顯身手。

下面將介紹在 Excel 2019 中數(shù)組公式的使用方法，包括輸入和編輯數(shù)組、了解數(shù)組的計算方式等。

1．認(rèn)識數(shù)組公式

數(shù)組公式是相對于普通公式而言的， 可以認(rèn)為數(shù)組公式是 Excel 對公式和數(shù)組的一種擴(kuò)充， 換句話說， 數(shù)組公式是 Excel 公式中一種專門用于數(shù)組的公式類型。

數(shù)組公式的特點(diǎn)就是所引用的參數(shù)是數(shù)組參數(shù)， 當(dāng)把數(shù)組作為公式的參數(shù)進(jìn)行輸入時， 就形成了數(shù)組公式。

與普通公式的不同之處在于，數(shù)組公式能通過輸入的單一公式， 執(zhí)行多個輸入的操作并產(chǎn)生多個結(jié)果， 而且每個結(jié)果都將顯示在一個單元格中。

普通公式（如【=SUM(B2:D2)】【=B8+C7+D6】 等） 只占用一個單元格， 且只返回一個結(jié)果。 而數(shù)組公式可以占用一個單元格， 也可以占用多個單元格， 數(shù)組的元素可多達(dá)6500 個。 它對一組數(shù)或多組數(shù)進(jìn)行多重計算， 并返回一個或多個結(jié)果。

因此， 可以將數(shù)組公式看成是有多重數(shù)值的公式， 它會讓公式中有對應(yīng)關(guān)系的數(shù)組元素同步執(zhí)行相關(guān)的計算，或者在工作表的相應(yīng)單元格區(qū)域中同時返回常量數(shù)組、 區(qū)域數(shù)組、 內(nèi)存數(shù)組或命名數(shù)組中的多個元素。

2．輸入數(shù)組公式

在 Excel 中， 數(shù)組公式的顯示是用大括號【{}】 括住以區(qū)分普通Excel 公式。 要使用數(shù)組公式進(jìn)行批量數(shù)據(jù)的處理， 首先要學(xué)會建立數(shù)組公式的方法， 具體操作步驟如下。

Step 01 如果希望數(shù)組公式只返回一個結(jié)果， 可先選擇保存計算結(jié)果的單元格。 如果數(shù)組公式要返回多個結(jié)果，可選擇需要保存數(shù)組公式計算結(jié)果的單元格區(qū)域。

Step 02 在編輯欄中輸入數(shù)組的計算公式。

Step 03 公式輸入完成后， 按【Ctrl+Shift+Enter】 組合鍵， 鎖定輸入的數(shù)組公式并確認(rèn)輸入。

其 中 第 3 步 使 用【Ctrl+Shift+Enter】 組合鍵結(jié)束公式的輸入是最關(guān)鍵的， 這相當(dāng)于用戶在提示 Excel 輸入的不是普通公式， 而是數(shù)組公式，需要特殊處理， 此時 Excel 就不會用常規(guī)的邏輯來處理公式了。

在 Excel 中， 只要在輸入公式后按【Ctrl+Shift+Enter】 組合鍵結(jié)束公式， Excel 就會把輸入的公式視為一個數(shù)組公式， 會自動為公式添加大括號【{}】， 以區(qū)別于普通公式。

輸入公式后， 如果在第 3 步按【Enter】 鍵， 則輸入的只是一個簡單的公式， Excel 只在選擇的單元格區(qū)域的第 1 個單元格位置（選擇區(qū)域的左上角單元格） 顯示一個計算結(jié)果。

3．使用數(shù)組公式的規(guī)則

在輸入數(shù)組公式時， 必須遵循相應(yīng)的規(guī)則， 否則公式將會出錯， 無法計算出數(shù)據(jù)的結(jié)果。

（1） 輸入數(shù)組公式時， 應(yīng)先選擇用來保存計算結(jié)果的單元格或單元格區(qū)域。 如果計算公式將產(chǎn)生多個計算結(jié)果， 必須選擇一個與完成計算時所用區(qū)域大小和形狀都相同的區(qū)域。

（2） 數(shù)組公式輸入完成后， 按【Ctrl+Shift+Enter】 組合鍵， 這時在公式編輯欄中可以看見 Excel 在公式的兩邊加上了 {} 符號， 表示該公式是一個數(shù)組公式。 需要注意的是， {}符號是由 Excel 自動加上去的， 不用手動輸入 {}； 否則， Excel 會認(rèn)為輸入的是一個正文標(biāo)簽。 但如果想在公式中直接表示一個數(shù)組， 就需要輸入{} 符號將數(shù)組的元素括起來。 例如，【=IF({1,1},D2:D6,C2:C6)】 公式中數(shù)組 {1,1} 的 {} 符號就是手動輸入的。

（3） 在數(shù)組公式所涉及的區(qū)域中， 既不能編輯、 清除或移動單個單元格， 也不能插入或刪除其中的任何一個單元格。 這是因為數(shù)組公式所涉及的單元格區(qū)域是一個整體， 只能作為一個整體進(jìn)行操作。 例如， 只能把整個區(qū)域同時刪除、 清除， 而不能只刪除或清除其中的一個單元格。

（4） 要編輯或清除數(shù)組公式，需要選擇整個數(shù)組公式所涵蓋的單元格區(qū)域， 并激活編輯欄（也可以單擊數(shù)組公式所包括的任一單元格， 這時數(shù)組公式會出現(xiàn)在編輯欄中， 它的兩邊有 {} 符號， 單擊編輯欄中的數(shù)組公式， 它兩邊的 {} 符號就會消失）， 然后在編輯欄中修改數(shù)組公式， 或者刪除數(shù)組公式， 操作完成后按【Ctrl+Shift+Enter】 組合鍵計算出新的數(shù)據(jù)結(jié)果。

（5） 如果需要將數(shù)組公式移動至其他位置， 需要先選中整個數(shù)組公式所涵蓋的單元格區(qū)域， 然后把整個區(qū)域拖放到目標(biāo)位置， 也可通過【剪切】 和【粘貼】 命令進(jìn)行數(shù)組公式的移動。

（6） 對于數(shù)組公式的范疇?wèi)?yīng)引起注意， 在輸入數(shù)值公式或函數(shù)的范圍時， 其大小及外形應(yīng)該與作為輸入數(shù)據(jù)的范圍的大小和外形相同。 如果存放結(jié)果的范圍太小， 就看不到所有的運(yùn)算結(jié)果； 如果存放結(jié)果的范圍太大， 有些單元格就會出現(xiàn)錯誤信息【#N/A】。

4．?dāng)?shù)組公式的計算方式

為了以后能更好地運(yùn)用數(shù)組公式， 還需要了解數(shù)組公式的計算方式，根據(jù)數(shù)組運(yùn)算結(jié)果的多少， 將數(shù)組計算分為多單元格數(shù)組公式的計算和單個單元格數(shù)組公式的計算兩種。

（1）多單元格數(shù)組公式

在 Excel 中使用數(shù)組公式可產(chǎn)生多值或?qū)?yīng)一組值而不是單個值進(jìn)行操作的公式， 其中能產(chǎn)生多個計算結(jié)果并在多個單元格中顯示出來的單一數(shù)組公式， 稱為【多單元格數(shù)組公式】。 在數(shù)據(jù)輸入過程中出現(xiàn)統(tǒng)計模式相同， 而引用單元格不同的情況時，就可以使用多單元格數(shù)組公式來簡化計算。 需要聯(lián)合多單元格數(shù)組的情況主要有以下幾種情況。

技術(shù)看板

多單元格數(shù)組公式主要進(jìn)行批量計算， 可節(jié)省計算的時間。 輸入多單元格數(shù)組公式時， 應(yīng)先選擇需要返回數(shù)據(jù)的單元格區(qū)域， 選擇的單元格區(qū)域的行列數(shù)應(yīng)與返回數(shù)組的行列數(shù)相同。 否則， 如果選中的區(qū)域小于數(shù)組返回的行列數(shù)， 將只顯示該單元格區(qū)域的返回值， 其他的計算結(jié)果將不顯示。 如果選擇的區(qū)域大于數(shù)組返回的行列數(shù)，那超出的區(qū)域?qū)祷亍?N/A】值。因此，在輸入多單元格數(shù)組公式前，需要了解數(shù)組結(jié)果是幾行幾列。

①數(shù)組與單一數(shù)據(jù)的運(yùn)算

一個數(shù)組與一個單一數(shù)據(jù)進(jìn)行運(yùn)算， 等同于將數(shù)組中的每一個元素均與這個單一數(shù)據(jù)進(jìn)行計算， 并返回同樣大小的數(shù)組。

例如， 在【年度優(yōu)秀員工評選表】工作簿中， 要為所有員工的當(dāng)前平均分上累加一個印象分， 通過輸入數(shù)組公式快速計算出員工評選累計分的具體操作步驟如下。

Step 01 輸入計算公式。打開素材文件年度優(yōu)秀員工評選表 .xlsx，選擇 I2:I12 單元格區(qū)域， 在編輯欄中輸入【=H2:H12+B14】， 如圖所示。

Step 02 查看計算結(jié)果。 按【Ctrl+Shift+Enter】 組合鍵后， 可看到編輯欄中的公式變?yōu)椤緖=H2:H12+B14}】， 同時會在 I2:I12 單元格區(qū)域中顯示出計算的數(shù)組公式結(jié)果， 如圖所示。

技術(shù)看板

該案例中的數(shù)組公式相當(dāng)于在 I2單元格中輸入公式【=H2+$B$14】，然后通過拖動填充控制柄復(fù)制公式到I3:I12 單元格區(qū)域中。

② 一維橫向數(shù)組或一維縱向數(shù)組之間的計算

一維橫向數(shù)組或一維縱向數(shù)組之間的運(yùn)算， 也就是單列與單列數(shù)組或單行與單行數(shù)組之間的運(yùn)算。

相比數(shù)組與單一數(shù)據(jù)的運(yùn)算，只是參與運(yùn)算的數(shù)據(jù)都會隨時變動而已， 其實質(zhì)是兩個一維數(shù)組對應(yīng)元素間進(jìn)行運(yùn)算， 即第一個數(shù)組的第一個元素與第二個數(shù)組的第一個元素進(jìn)行運(yùn)算， 結(jié)果作為數(shù)組公式結(jié)果的第一個元素， 然后第一個數(shù)組的第二個元素與第二個數(shù)組的第二個元素進(jìn)行運(yùn)算， 結(jié)果作為數(shù)組公式結(jié)果的第二個元素， 接著是第三個元素……直到第N 個元素。 一維數(shù)組之間進(jìn)行運(yùn)算后，返回的仍然是一個一維數(shù)組， 其行、列數(shù)與參與運(yùn)算的行列數(shù)組的行列數(shù)相同。

例如， 在【銷售統(tǒng)計表】 工作簿中， 需要計算出各產(chǎn)品的銷售額， 即讓各產(chǎn)品的銷售量乘以其銷售單價。通過輸入數(shù)組公式可以快速計算出各產(chǎn)品的銷售額， 具體操作步驟如下。

Step 01 輸入計算公式。 打開素材文件銷售統(tǒng)計表 .xlsx，選擇 H3: H11 單元格區(qū)域，在編輯欄中輸入【=F3:F11*G3:G11】， 如圖所示。

Step 02 查看計算結(jié)果。 按【Ctrl+Shift+Enter】 組合鍵后， 可看到編輯欄中的公式變?yōu)椤緖=F3:F11*G3:G11}】， 在H3:H11 單元格區(qū)域中同時顯示出計算的數(shù)組公式結(jié)果， 如圖所示。

技術(shù)看板

該案例中 F3:F11*G3:G11 是兩個一維數(shù)組相乘， 返回一個新的一維數(shù)組。 該案例如果使用普通公式進(jìn)行計算， 通過復(fù)制公式也可以得到需要的結(jié)果， 但若需要對 100 行甚至更多行數(shù)據(jù)進(jìn)行計算， 僅復(fù)制公式就會比較麻煩。

（3） 一維橫向數(shù)組與一維縱向數(shù)組的計算

一維橫向數(shù)組與一維縱向數(shù)組進(jìn)行運(yùn)算后， 將返回一個二維數(shù)組， 且返回數(shù)組的行數(shù)同一維縱向數(shù)組的行數(shù)相同、 列數(shù)同一維橫向數(shù)組的列數(shù)相同。 返回數(shù)組中第 M 行第 N 列的元素是一維縱向數(shù)組的第 M 個元素和一維橫向數(shù)組的第 N 個元素運(yùn)算的結(jié)果。 具體的計算過程可以通過查看一維橫向數(shù)組與一維縱向數(shù)組進(jìn)行運(yùn)算后的結(jié)果來進(jìn)行分析。

例如， 在【產(chǎn)品合格量統(tǒng)計】工作表中已經(jīng)將生產(chǎn)的產(chǎn)品數(shù)量輸入為一組橫向數(shù)組， 并將預(yù)計的可能合格率輸入為一組縱向數(shù)組， 需要通過輸入數(shù)組公式計算每種合格率可能性下不同產(chǎn)品的合格量， 具體操作步驟如下。

Step 01 輸入計算公式。 打開素材文件產(chǎn)品合格量統(tǒng)計 .xlsx，選擇 B2:G11 單元格區(qū)域，在編輯欄中輸入【=B1:G1*A2:A11】， 如圖所示。

Step 02 查看計算結(jié)果。按【Ctrl+Shift+Enter】組合鍵后，可看到編輯欄中的公式變?yōu)椤緖=B1:G1*A2:A11}】，在B2:G11 單元格區(qū)域中同時顯示出計算的數(shù)組公式結(jié)果，如圖所示。

（4） 行數(shù)（或列數(shù)） 相同的單列（或單行） 數(shù)組與多行多列數(shù)組的計算

單列數(shù)組的行數(shù)與多行多列數(shù)組的行數(shù)相同時， 或者單行數(shù)組的列數(shù)與多行多列數(shù)組的列數(shù)相同時， 計算規(guī)律與一維橫向數(shù)組或一維縱向數(shù)組之間的運(yùn)算規(guī)律大同小異， 計算結(jié)果將返回一個多行列的數(shù)組， 其行列數(shù)與參與運(yùn)算的多行多列數(shù)組的行列數(shù)相同。 單列數(shù)組與多行多列數(shù)組計算時， 返回數(shù)組的第 M 行第 N 列的數(shù)據(jù)等于單列數(shù)組的第 M 行的數(shù)據(jù)與多行多列數(shù)組的第 M 行第 N 列的數(shù)據(jù)的計算結(jié)果； 單行數(shù)組與多行多列數(shù)組計算時， 返回數(shù)組的第 M 行第N 列的數(shù)據(jù)等于單行數(shù)組第 N 列的數(shù)據(jù)與多行多列數(shù)組第 M 行第 N 列數(shù)據(jù)的計算結(jié)果。

例如， 在【生產(chǎn)完成率統(tǒng)計】 工作表中已經(jīng)將某一周預(yù)計要達(dá)到的生產(chǎn)量輸入為一組縱向數(shù)組， 并將各產(chǎn)品的實際生產(chǎn)數(shù)量輸入為一個二維數(shù)組， 需要通過輸入數(shù)組公式計算每種產(chǎn)品每天的實際完成率， 具體操作步驟如下。

Step 01 輸入公式。 打開素材文件生產(chǎn)完成率統(tǒng)計 .xlsx，合并 B11:G11 單元格區(qū)域， 并輸入相應(yīng)的文本，選擇 B12:G19 單元格區(qū)域，在編輯欄中輸入【=B3:G9/A3:A9】， 如圖所示。

Step 02 查看數(shù)據(jù)公式計算結(jié)果。按【Ctrl+Shift+Enter】 組合鍵后， 可看到編輯欄中的公式變?yōu)椤緖=B3:G9/A3:A9}】，在 B12:G19 單元格區(qū)域中同時顯示出計算的數(shù)組公式結(jié)果，如圖所示。

Step 03 設(shè)置百分比格式。為整個結(jié)果區(qū)域設(shè)置邊框線，在第 11 行單元格的下方插入一行單元格， 并輸入相應(yīng)的文本，選擇 B12:G19 單元格區(qū)域，單擊【開始】 選項卡【數(shù)字】 組中的【百分比樣式】 按鈕 ，讓計算結(jié)果顯示為百分比樣式， 如圖所示。

（5） 行列數(shù)相同的二維數(shù)組間的運(yùn)算

行列相同的二維數(shù)組之間的運(yùn)算， 將生成一個新的同樣大小的二維數(shù)組。 其計算過程等同于第一個數(shù)組第一行的第一個元素與第二個數(shù)組第一行的第一個元素進(jìn)行運(yùn)算， 結(jié)果為數(shù)組公式的結(jié)果數(shù)組第一行的第一個元素， 接著是第二個， 第三個……直到第 N 個元素。

例如， 在【月考平均分統(tǒng)計】 工作表中已經(jīng)將某些同學(xué)前 3 次月考的成績分別統(tǒng)計為一個二維數(shù)組， 需要通過輸入數(shù)組公式計算這些同學(xué) 3 次考試的每科成績平均分， 具體操作步驟如下。

Step 01 輸入公式。 打開素材文件月考平均分統(tǒng)計 .xlsx，選擇B13:D18 單元格區(qū)域，在編輯欄中輸入【=(B3:D8+G3:I8+L3:N8)/3】，如圖所示。

Step 02 查看計算結(jié)果。 按【Ctrl+Shift+Enter】組合鍵后，可看到編輯欄中的公式變?yōu)椤緖=(B3:D8+G3:I8+L3:N8)/3}】，在 B13:D18 單元格區(qū)域中同時顯示出計算的數(shù)組公式結(jié)果，如圖所示。

技術(shù)看板

使用多單元格數(shù)組公式的優(yōu)勢在于： ①能夠保證在同一個范圍內(nèi)的公式具有同一性， 防止用戶在操作時無意間修改到表格的公式。 創(chuàng)建此類公式后， 公式所在的任何單元格都不能被單獨(dú)編輯， 否則將會打開提示對話框， 提示用戶不能更改數(shù)組的某一部分； ②能夠在一個較大范圍內(nèi)快速生成大量具有某種規(guī)律的數(shù)據(jù)； ③數(shù)組通過數(shù)組公式運(yùn)算后生成的新數(shù)組（通常稱為【內(nèi)存數(shù)組】） 存儲在內(nèi)存中，因此使用數(shù)組公式可以減少內(nèi)存占用，加快公式的執(zhí)行時間。

（2）單個單元格數(shù)組公式

通過前面對數(shù)組公式計算規(guī)律的講解和案例分析， 不難發(fā)現(xiàn)， 一維數(shù)組公式經(jīng)過運(yùn)算后， 得到的結(jié)果可能是一維的， 也可能是多維的， 存放在不同的單元格區(qū)域中。 有二維數(shù)組參與的公式計算， 其結(jié)果也是一個二維數(shù)組。 總之， 數(shù)組與數(shù)組的計算， 返回的將是一個新的數(shù)組， 其行數(shù)與參與計算的數(shù)組中行數(shù)較大的數(shù)組的行數(shù)相同， 列數(shù)與參與計算的數(shù)組中列數(shù)較大的數(shù)組的列數(shù)相同。

以上兩個數(shù)組公式有一個共同點(diǎn)， 其講解的數(shù)組運(yùn)算都是普通的公式計算， 如果將數(shù)組公式運(yùn)用到函數(shù)中， 結(jié)果又會如何？ 實際上， 上面得出的兩個結(jié)論都會被顛覆。 將數(shù)組用于函數(shù)計算中， 計算的結(jié)果可能是一個值， 也可能是一個一維數(shù)組或二維數(shù)組。

函數(shù)的內(nèi)容將在后面的章節(jié)中進(jìn)行講解， 這里先用一個簡單的例子來進(jìn)行說明。 例如， 沿用【銷售統(tǒng)計表】工作表中的數(shù)據(jù)， 下面使用一個函數(shù)來完成對所有產(chǎn)品的總銷售利潤進(jìn)行統(tǒng)計， 具體操作步驟如下。

Step 01 計算銷售利潤。 打開素材文件銷售統(tǒng)計表 .xlsx，合并F13:G13 單元格區(qū)域， 并輸入相應(yīng)文本，選擇 H13 單元格，在編輯欄中輸入【=SUM(F3:F11*G3:G11)*H1】，如圖所示。

Step 02 查看計算結(jié)果。 按【Ctrl+Shift+Enter】 組合鍵后， 可看到編輯欄中的公式變?yōu)椤?{SUM(F3:F11*G3:G11)* H1}】， 在 H13 單元格中同時顯示出計算的數(shù)組公式結(jié)果，如圖所示。

技術(shù)看板

當(dāng)運(yùn)算中存在著一些只有通過復(fù)雜的中間運(yùn)算過程才會得到的結(jié)果時，就必須結(jié)合使用函數(shù)和數(shù)組了。

本例的數(shù)組公式先在內(nèi)存中執(zhí)行計算， 將各商品的銷量和單價分別相乘， 然后將數(shù)組中的所有元素用 SUM函數(shù)匯總， 得到總銷售額， 最后乘以H1 單元格的利潤率得出最終結(jié)果。

本例 中 的 公 式 還 可 以 用SUMPRODUCT函數(shù)來代替，輸 入【=SUMPRODUCT(F3:F11* G3:G11)* H1】即可。SUMPRODUCT 函 數(shù) 的所有參數(shù)都是數(shù)組類型的參數(shù)， 直接支持多項計算，具體應(yīng)用參考后面的章節(jié)。