關注【新精英充電站】能力提升看得見！

Excel 中陣列公式非常有用， 可建立產生多值或對一組值而不是單個值進行操作的公式。掌握陣列公式的相關技能技巧， 當在不能使用工作表函式直接得到結果， 又需要對一組或多組資料進行多重計算時，方可大顯身手。

下面將介紹在 Excel 2019 中陣列公式的使用方法，包括輸入和編輯陣列、瞭解陣列的計算方式等。

1．認識陣列公式

陣列公式是相對於普通公式而言的， 可以認為陣列公式是 Excel 對公式和陣列的一種擴充， 換句話說， 陣列公式是 Excel 公式中一種專門用於陣列的公式型別。

陣列公式的特點就是所引用的引數是陣列引數， 當把陣列作為公式的引數進行輸入時， 就形成了陣列公式。

與普通公式的不同之處在於，

陣列公式能透過輸入的單一公式， 執行多個輸入的操作併產生多個結果， 而且每個結果都將顯示在一個單元格中。

普通公式

（如【=SUM（B2：D2）】【=B8+C7+D6】 等）

只佔用一個單元格， 且只返回一個結果。

而陣列公式可以佔用一個單元格， 也可以佔用多個單元格， 陣列的元素可多達6500 個。

它對一組數或多組數進行多重計算， 並返回一個或多個結果。

因此， 可以將陣列公式看成是有多重數值的公式， 它會讓公式中有對應關係的陣列元素同步執行相關的計算，或者在工作表的相應單元格區域中同時返回常量陣列、 區域陣列、 記憶體陣列或命名陣列中的多個元素。

2．輸入陣列公式

在 Excel 中， 陣列公式的顯示是用大括號【{}】 括住以區分普通Excel 公式。要使用陣列公式進行批次資料的處理， 首先要學會建立陣列公式的方法， 具體操作步驟如下。

Step 01 如果希望陣列公式只返回一個結果， 可先選擇儲存計算結果的單元格。如果陣列公式要返回多個結果，可選擇需要儲存陣列公式計算結果的單元格區域。

Step 02 在編輯欄中輸入陣列的計算公式。

Step 03 公式輸入完成後， 按【Ctrl+Shift+Enter】 組合鍵， 鎖定輸入的陣列公式並確認輸入。

其 中 第 3 步 使 用【Ctrl+Shift+Enter】 組合鍵結束公式的輸入是最關鍵的， 這相當於使用者在提示 Excel 輸入的不是普通公式， 而是陣列公式，需要特殊處理， 此時 Excel 就不會用常規的邏輯來處理公式了。

在 Excel 中， 只要在輸入公式後按【Ctrl+Shift+Enter】 組合鍵結束公式， Excel 就會把輸入的公式視為一個數組公式， 會自動為公式新增大括號【{}】， 以區別於普通公式。

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

3．使用陣列公式的規則

在輸入陣列公式時， 必須遵循相應的規則， 否則公式將會出錯， 無法計算出資料的結果。

（1） 輸入陣列公式時， 應先選擇用來儲存計算結果的單元格或單元格區域。如果計算公式將產生多個計算結果， 必須選擇一個與完成計算時所用區域大小和形狀都相同的區域。

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

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

（4） 要編輯或清除陣列公式，需要選擇整個陣列公式所涵蓋的單元格區域， 並激活編輯欄（也可以單擊陣列公式所包括的任一單元格， 這時陣列公式會出現在編輯欄中， 它的兩邊有 {} 符號， 單擊編輯欄中的陣列公式， 它兩邊的 {} 符號就會消失）， 然後在編輯欄中修改陣列公式， 或者刪除陣列公式， 操作完成後按【Ctrl+Shift+Enter】 組合鍵計算出新的資料結果。

（5） 如果需要將陣列公式移動至其他位置， 需要先選中整個陣列公式所涵蓋的單元格區域， 然後把整個區域拖放到目標位置， 也可透過【剪下】 和【貼上】 命令進行陣列公式的移動。

（6） 對於陣列公式的範疇應引起注意， 在輸入數值公式或函式的範圍時， 其大小及外形應該與作為輸入資料的範圍的大小和外形相同。如果存放結果的範圍太小， 就看不到所有的運算結果；如果存放結果的範圍太大， 有些單元格就會出現錯誤資訊【#N/A】。

4．陣列公式的計算方式

為了以後能更好地運用陣列公式， 還需要了解陣列公式的計算方式，根據陣列運算結果的多少， 將陣列計算分為多單元格陣列公式的計算和單個單元格陣列公式的計算兩種。

（1）多單元格陣列公式>>>

在 Excel 中使用陣列公式可產生多值或對應一組值而不是單個值進行操作的公式， 其中能產生多個計算結果並在多個單元格中顯示出來的單一陣列公式， 稱為【多單元格陣列公式】。在資料輸入過程中出現統計模式相同， 而引用單元格不同的情況時，就可以使用多單元格陣列公式來簡化計算。需要聯合多單元格陣列的情況主要有以下幾種情況。

技術看板

多單元格陣列公式主要進行批次計算， 可節省計算的時間。輸入多單元格陣列公式時， 應先選擇需要返回資料的單元格區域， 選擇的單元格區域的行列數應與返回陣列的行列數相同。否則， 如果選中的區域小於陣列返回的行列數， 將只顯示該單元格區域的返回值， 其他的計算結果將不顯示。如果選擇的區域大於陣列返回的行列數，那超出的區域將會返回【#N/A】值。因此，在輸入多單元格陣列公式前，需要了解陣列結果是幾行幾列。

①陣列與單一資料的運算

一個數組與一個單一資料進行運算， 等同於將陣列中的每一個元素均與這個單一資料進行計算， 並返回同樣大小的陣列。

例如， 在【年度優秀員工評選表】工作簿中， 要為所有員工的當前平均分上累加一個印象分， 透過輸入陣列公式快速計算出員工評選累計分的具體操作步驟如下。

Step 01 輸入計算公式。開啟素材檔案年度優秀員工評選表 。xlsx，選擇 I2：I12 單元格區域， 在編輯欄中輸入【=H2：H12+B14】， 如圖所示。

Step 02 檢視計算結果。按【Ctrl+Shift+Enter】 組合鍵後， 可看到編輯欄中的公式變為【{=H2：H12+B14}】， 同時會在 I2：I12 單元格區域中顯示出計算的陣列公式結果， 如圖所示。

技術看板

該案例中的陣列公式相當於在 I2單元格中輸入公式【=H2+$B$14】，然後透過拖動填充控制柄複製公式到I3：I12 單元格區域中。

② 一維橫向陣列或一維縱向陣列之間的計算

一維橫向陣列或一維縱向陣列之間的運算， 也就是單列與單列陣列或單行與單行陣列之間的運算。

相比陣列與單一資料的運算，只是參與運算的資料都會隨時變動而已， 其實質是兩個一維陣列對應元素間進行運算， 即第一個陣列的第一個元素與第二個陣列的第一個元素進行運算， 結果作為陣列公式結果的第一個元素， 然後第一個陣列的第二個元素與第二個陣列的第二個元素進行運算， 結果作為陣列公式結果的第二個元素， 接著是第三個元素……直到第N 個元素。一維陣列之間進行運算後，返回的仍然是一個一維陣列， 其行、列數與參與運算的行列陣列的行列數相同。

例如， 在【銷售統計表】 工作簿中， 需要計算出各產品的銷售額， 即讓各產品的銷售量乘以其銷售單價。透過輸入陣列公式可以快速計算出各產品的銷售額， 具體操作步驟如下。

Step 01 輸入計算公式。開啟素材檔案銷售統計表 。xlsx，選擇 H3： H11 單元格區域，在編輯欄中輸入【=F3：F11*G3：G11】， 如圖所示。

Step 02 檢視計算結果。按【Ctrl+Shift+Enter】 組合鍵後， 可看到編輯欄中的公式變為【{=F3：F11*G3：G11}】， 在H3：H11 單元格區域中同時顯示出計算的陣列公式結果， 如圖所示。

技術看板

該案例中 F3：F11*G3：G11 是兩個一維陣列相乘， 返回一個新的一維陣列。該案例如果使用普通公式進行計算， 透過複製公式也可以得到需要的結果， 但若需要對 100 行甚至更多行資料進行計算， 僅複製公式就會比較麻煩

③一維橫向陣列與一維縱向陣列的計算

一維橫向陣列與一維縱向陣列進行運算後， 將返回一個二維陣列， 且返回陣列的行數同一維縱向陣列的行數相同、 列數同一維橫向陣列的列數相同。返回陣列中第 M 行第 N 列的元素是一維縱向陣列的第 M 個元素和一維橫向陣列的第 N 個元素運算的結果。具體的計算過程可以透過檢視一維橫向陣列與一維縱向陣列進行運算後的結果來進行分析。

例如， 在【產品合格量統計】工作表中已經將生產的產品數量輸入為一組橫向陣列， 並將預計的可能合格率輸入為一組縱向陣列， 需要透過輸入陣列公式計算每種合格率可能性下不同產品的合格量， 具體操作步驟如下。

Step 01 輸入計算公式。開啟素材檔案產品合格量統計 。xlsx，選擇 B2：G11 單元格區域，在編輯欄中輸入【=B1：G1*A2：A11】， 如圖所示。

Step 02 檢視計算結果。按【Ctrl+Shift+Enter】組合鍵後，可看到編輯欄中的公式變為【{=B1：G1*A2：A11}】，在B2：G11 單元格區域中同時顯示出計算的陣列公式結果，如圖所示。

④

行數（或列數） 相同的單列（或單行） 陣列與多行多列陣列的計算

單列陣列的行數與多行多列陣列的行數相同時， 或者單行陣列的列數與多行多列陣列的列數相同時， 計算規律與一維橫向陣列或一維縱向陣列之間的運算規律大同小異， 計算結果將返回一個多行列的陣列， 其行列數與參與運算的多行多列陣列的行列數相同。單列陣列與多行多列陣列計算時， 返回陣列的第 M 行第 N 列的資料等於單列陣列的第 M 行的資料與多行多列陣列的第 M 行第 N 列的資料的計算結果；單行陣列與多行多列陣列計算時， 返回陣列的第 M 行第N 列的資料等於單行陣列第 N 列的資料與多行多列陣列第 M 行第 N 列資料的計算結果。

例如， 在【生產完成率統計】 工作表中已經將某一週預計要達到的生產量輸入為一組縱向陣列， 並將各產品的實際生產數量輸入為一個二維陣列， 需要透過輸入陣列公式計算每種產品每天的實際完成率， 具體操作步驟如下。

Step 01 輸入公式。開啟素材檔案生產完成率統計 。xlsx，合併 B11：G11 單元格區域， 並輸入相應的文字，選擇 B12：G19 單元格區域，在編輯欄中輸入【=B3：G9/A3：A9】， 如圖所示。

Step 02 檢視資料公式計算結果。按【Ctrl+Shift+Enter】 組合鍵後， 可看到編輯欄中的公式變為【{=B3：G9/A3：A9}】，在 B12：G19 單元格區域中同時顯示出計算的陣列公式結果，如圖所示。

Step 03 設定百分比格式。為整個結果區域設定邊框線，在第 11 行單元格的下方插入一行單元格， 並輸入相應的文字，選擇 B12：G19 單元格區域，單擊【開始】 選項卡【數字】 組中的【百分比樣式】 按鈕 ，讓計算結果顯示為百分比樣式， 如圖所示。

⑤

行列數相同的二維陣列間的運算

行列相同的二維陣列之間的運算， 將生成一個新的同樣大小的二維陣列。其計算過程等同於第一個陣列第一行的第一個元素與第二個陣列第一行的第一個元素進行運算， 結果為陣列公式的結果陣列第一行的第一個元素， 接著是第二個， 第三個……直到第 N 個元素。

例如， 在【月考平均分統計】 工作表中已經將某些同學前 3 次月考的成績分別統計為一個二維陣列， 需要透過輸入陣列公式計算這些同學 3 次考試的每科成績平均分， 具體操作步驟如下。

Step 01 輸入公式。開啟素材檔案月考平均分統計 。xlsx，選擇B13：D18 單元格區域，在編輯欄中輸入【=（B3：D8+G3：I8+L3：N8）/3】，如圖所示。

Step 02 檢視計算結果。按【Ctrl+Shift+Enter】組合鍵後，可看到編輯欄中的公式變

為【{=（B3：D8+G3：I8+L3：N8）/3}】，在 B13：D18 單元格區域中同時顯示出計算的陣列公式結果，如圖所示。

技術看板

使用多單元格陣列公式的優勢在於：①能夠保證在同一個範圍內的公式具有同一性， 防止使用者在操作時無意間修改到表格的公式。建立此類公式後， 公式所在的任何單元格都不能被單獨編輯， 否則將會開啟提示對話方塊， 提示使用者不能更改陣列的某一部分；②能夠在一個較大範圍內快速生成大量具有某種規律的資料；③陣列透過陣列公式運算後生成的新陣列（通常稱為【記憶體陣列】） 儲存在記憶體中，因此使用陣列公式可以減少記憶體佔用，加快公式的執行時間。

（2）單個單元格陣列公式>>>

透過前面對陣列公式計算規律的講解和案例分析， 不難發現， 一維陣列公式經過運算後， 得到的結果可能是一維的， 也可能是多維的， 存放在不同的單元格區域中。有二維陣列參與的公式計算， 其結果也是一個二維陣列。總之， 陣列與陣列的計算， 返回的將是一個新的陣列， 其行數與參與計算的陣列中行數較大的陣列的行數相同， 列數與參與計算的陣列中列數較大的陣列的列數相同。

以上兩個陣列公式有一個共同點， 其講解的陣列運算都是普通的公式計算， 如果將陣列公式運用到函式中， 結果又會如何？實際上， 上面得出的兩個結論都會被顛覆。將陣列用於函式計算中， 計算的結果可能是一個值， 也可能是一個一維陣列或二維陣列。

函式的內容將在後面的章節中進行講解， 這裡先用一個簡單的例子來進行說明。例如， 沿用【銷售統計表】工作表中的資料， 下面使用一個函式來完成對所有產品的總銷售利潤進行統計， 具體操作步驟如下。

Step 01 計算銷售利潤。開啟素材檔案銷售統計表 。xlsx，合併F13：G13 單元格區域， 並輸入相應文字，選擇 H13 單元格，在編輯欄中輸入【=SUM（F3：F11*G3：G11）*H1】，如圖所示。

Step 02 檢視計算結果。按【Ctrl+Shift+Enter】 組合鍵後， 可看到編輯欄中的公式變為【=】， 在 H13 單元格中同時顯示出計算的陣列公式結果，如圖所示。

技術看板

當運算中存在著一些只有透過複雜的中間運算過程才會得到的結果時，就必須結合使用函式和陣列了。

本例的陣列公式先在記憶體中執行計算， 將各商品的銷量和單價分別相乘， 然後將陣列中的所有元素用 SUM函式彙總， 得到總銷售額， 最後乘以H1 單元格的利潤率得出最終結果。

本例 中 的 公 式 還 可 以 用SUMPRODUCT函式來代替，輸 入【=SUMPRODUCT（F3：F11* G3：G11）* H1】即可。SUMPRODUCT 函 數 的所有引數都是陣列型別的引數， 直接支援多項計算，具體應用參考後面的章節