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

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

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)行多重計(jì)算， 并返回一個或多個結(jié)果。

因此， 可以將數(shù)組公式看成是有多重?cái)?shù)值的公式， 它會讓公式中有對應(yīng)關(guān)系的數(shù)組元素同步執(zhí)行相關(guān)的計(jì)算，或者在工作表的相應(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é)果， 可先選擇保存計(jì)算結(jié)果的單元格。 如果數(shù)組公式要返回多個結(jié)果，可選擇需要保存數(shù)組公式計(jì)算結(jié)果的單元格區(qū)域。

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

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ū)域的左上角單元格） 顯示一個計(jì)算結(jié)果。

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

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

（1） 輸入數(shù)組公式時， 應(yīng)先選擇用來保存計(jì)算結(jié)果的單元格或單元格區(qū)域。 如果計(jì)算公式將產(chǎn)生多個計(jì)算結(jié)果， 必須選擇一個與完成計(jì)算時所用區(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ū)域中， 既不能編輯、 清除或移動單個單元格， 也不能插入或刪除其中的任何一個單元格。 這是因?yàn)閿?shù)組公式所涉及的單元格區(qū)域是一個整體， 只能作為一個整體進(jìn)行操作。 例如， 只能把整個區(qū)域同時刪除、 清除， 而不能只刪除或清除其中的一個單元格。

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

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

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

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

技術(shù)看板

多單元格數(shù)組公式主要進(jìn)行批量計(jì)算， 可節(jié)省計(jì)算的時間。 輸入多單元格數(shù)組公式時， 應(yīng)先選擇需要返回?cái)?shù)據(jù)的單元格區(qū)域， 選擇的單元格區(qū)域的行列數(shù)應(yīng)與返回?cái)?shù)組的行列數(shù)相同。 否則， 如果選中的區(qū)域小于數(shù)組返回的行列數(shù)， 將只顯示該單元格區(qū)域的返回值， 其他的計(jì)算結(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)行計(jì)算， 并返回同樣大小的數(shù)組。

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

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

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

技術(shù)看板

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

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

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

相比數(shù)組與單一數(shù)據(jù)的運(yùn)算，只是參與運(yùn)算的數(shù)據(jù)都會隨時變動而已， 其實(shí)質(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)計(jì)表】 工作簿中， 需要計(jì)算出各產(chǎn)品的銷售額， 即讓各產(chǎn)品的銷售量乘以其銷售單價。通過輸入數(shù)組公式可以快速計(jì)算出各產(chǎn)品的銷售額， 具體操作步驟如下。

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

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

技術(shù)看板

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Step 02 查看計(jì)算結(jié)果。 按【Ctrl+Shift+Enter】組合鍵后，可看到編輯欄中的公式變?yōu)椤緖=(B3:D8+G3:I8+L3:N8)/3}】，在 B13:D18 單元格區(qū)域中同時顯示出計(jì)算的數(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ù)組公式計(jì)算規(guī)律的講解和案例分析， 不難發(fā)現(xiàn)， 一維數(shù)組公式經(jīng)過運(yùn)算后， 得到的結(jié)果可能是一維的， 也可能是多維的， 存放在不同的單元格區(qū)域中。 有二維數(shù)組參與的公式計(jì)算， 其結(jié)果也是一個二維數(shù)組。 總之， 數(shù)組與數(shù)組的計(jì)算， 返回的將是一個新的數(shù)組， 其行數(shù)與參與計(jì)算的數(shù)組中行數(shù)較大的數(shù)組的行數(shù)相同， 列數(shù)與參與計(jì)算的數(shù)組中列數(shù)較大的數(shù)組的列數(shù)相同。

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

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

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

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

技術(shù)看板

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

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

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