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

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

數組公式是相對于普通公式而言的， 可以認為數組公式是 Excel 對公式和數組的一種擴充， 換句話說， 數組公式是 Excel 公式中一種專門用于數組的公式類型。

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

與普通公式的不同之處在于， 數組公式能通過輸入的單一公式， 執行多個輸入的操作并産生多個結果， 而且每個結果都将顯示在一個單元格中。

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

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

在 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 個單元格位置（選擇區域的左上角單元格） 顯示一個計算結果。

在輸入數組公式時， 必須遵循相應的規則， 否則公式将會出錯， 無法計算出數據的結果。

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

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

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

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

（5） 如果需要将數組公式移動至其他位置， 需要先選中整個數組公式所涵蓋的單元格區域， 然後把整個區域拖放到目标位置， 也可通過【剪切】 和【粘貼】 命令進行數組公式的移動。

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

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

（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 行甚至更多行數據進行計算， 僅複制公式就會比較麻煩。

（3） 一維橫向數組與一維縱向數組的計算

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

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

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

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

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

單列數組的行數與多行多列數組的行數相同時， 或者單行數組的列數與多行多列數組的列數相同時， 計算規律與一維橫向數組或一維縱向數組之間的運算規律大同小異， 計算結果将返回一個多行列的數組， 其行列數與參與運算的多行多列數組的行列數相同。 單列數組與多行多列數組計算時， 返回數組的第 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 單元格區域，單擊【開始】 選項卡【數字】 組中的【百分比樣式】 按鈕 ，讓計算結果顯示為百分比樣式， 如圖所示。

（5） 行列數相同的二維數組間的運算

行列相同的二維數組之間的運算， 将生成一個新的同樣大小的二維數組。 其計算過程等同于第一個數組第一行的第一個元素與第二個數組第一行的第一個元素進行運算， 結果為數組公式的結果數組第一行的第一個元素， 接着是第二個， 第三個……直到第 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】 組合鍵後， 可看到編輯欄中的公式變為【={SUM(F3:F11*G3:G11)* H1}】， 在 H13 單元格中同時顯示出計算的數組公式結果，如圖所示。

技術看闆

當運算中存在着一些隻有通過複雜的中間運算過程才會得到的結果時，就必須結合使用函數和數組了。

本例的數組公式先在内存中執行計算， 将各商品的銷量和單價分别相乘， 然後将數組中的所有元素用 SUM函數彙總， 得到總銷售額， 最後乘以H1 單元格的利潤率得出最終結果。

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

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!