公式是Excel中以等號開頭的可以得到一個結果的等式,公式以等號“=”開頭,公式中可以包括函數、運算符、引用和常量。相對于普通公式而言,在Excel中還有另一種形式的公式:數組公式。
數組公式可以對兩組或兩組以上的數據執行運算,執行多項計算後返回一個或多個結果。每一組數據就是一個數組,數組可以是同一行或者同一列中的多個數據,也可以是一個多行多列的區域中的數據。數組公式可以在小空間內進行大量計算時使用,它可以替代許多重複的公式,並由此節省內存。
數組公式通常也被稱作 CSE (Ctrl+Shift+Enter組合鍵) 公式,因爲輸入公式後不是只按 Enter 鍵,而是要按 Ctrl+Shift+Enter組合鍵 完成公式的輸入。
與普通公式相同,數組公式同樣需要以等號“=”開頭,在創建數組公式時,有兩種不同的方式,針對一個單元格創建的數組公式和針對單元格區域創建的數組公式。
一、在多個單元格中使用數組公式進行計算數組公式就是指對兩組或多組參數進行多重計算,並返回一個或多個結果的計算公式。使用數組公式時,要求每個數組參數必須有相同數量的和與列。在多個單元格中使用數組公式進行計算的具體操作方法如下:
![](http://image.uc.cn/s/wemedia/s/upload/2024/96778ae7188029baaad8de1211d1da6d.png)
圖1
例如上圖1所示,有一個【銷售訂單】工作表;(1)選擇存放結果的F5:F10單元格區域,輸入等號【=】;(2)移動鼠標選擇要參與計算的第一個單元格區域D5:D10,如下圖2所示:
![](http://image.uc.cn/s/wemedia/s/upload/2024/c42c5cb12dc63341713dcae5245656a7.png)
圖2
(3)參照上述操作方法,繼續輸入運算符號(乘號*),並拖動鼠標選擇要參與計算的單元格區域E5:E10,如下圖3示:
![](http://image.uc.cn/s/wemedia/s/upload/2024/d9e5d0a0d138f4914dae5fa655659e81.jpg)
圖3
按【Ctrl+Shift+Enter】組合鍵,得出數組公式計算結果,如下圖4所示。
![](http://image.uc.cn/s/wemedia/s/upload/2024/ec5884a879f170bf1e4d29811d70e5b8.jpg)
圖4
在Excel中,顯示的數組公式是用大括號“{}”括起來的,以區分于普通的的Excel公式。上例完整操作過程如下動圖5示:
![](http://image.uc.cn/s/wemedia/s/upload/2024/4601fb54af2122e228c9f6ab3eef53f6.gif)
圖5
二、在單個單元格中使用數組公式進行計算在編輯工作表時,還可以在單個單元格中輸入數組公式,以便完成多步計算,具體的操作方法如下:
![](http://image.uc.cn/s/wemedia/s/upload/2024/7f44b36c71c33d72141bde6f2f2ce6a5.jpg)
圖6
有如上圖6所示的銷售訂單,現要計算【銷售總額】顯示到E11單元格中中,常規算法是:算出每一項,然後再累加。這裏我們使用數組計算方式來求,具體操作步驟如下:
(1)在單元格E11中輸入公式【=sum()】,再將光標定位到括號括號內,如下圖7所示:
![](http://image.uc.cn/s/wemedia/s/upload/2024/322107a590fbc47de15c8d7d80791890.jpg)
圖7
(2)拖動鼠標選擇要參與計算的第1個單元格區域,然後輸入運算符號【乘號*】,再拖動鼠標選定要參與計算的第2個單元格區域,如下圖8所示:
![](http://image.uc.cn/s/wemedia/s/upload/2024/3a39e9546873db4a3a0d18386ef4f231.jpg)
圖8
(3)按【Ctrl+Shift+Enter】組合鍵確認計算,得出數組計算結果,如下圖9所示:
![](http://image.uc.cn/s/wemedia/s/upload/2024/8a8330bf629bec085d279634893a4053.jpg)
圖9
注:在單個單元格中使用數組公式計算數據時,單元格不能是合並後的,否則會彈出提示數組公式無效錯誤;同時:公式輸入時的{}是不能手工輸入的。
上例完整的操作過程如下面動圖10所示:
![](http://image.uc.cn/s/wemedia/s/upload/2024/a58bf09d49d732151ef3e234c730d864.gif)
圖10
三、對數組中N個最大值求和![](http://image.uc.cn/s/wemedia/s/upload/2024/455adb46a26de139c722e92c52005c02.jpg)
圖11
當有多列數據時,在不排序的情況下,需要將這些數據中最大或最小的N個數據進行求和時,就要使用數組公式來實現。例如:如上圖11所示,要在多列數據中,對最大的5個數據進行求和運算,具體的操作方法如下:
在C12單元格中輸入公式【=SUM(LARGE(B2:C11,ROW(INDIRECT("1:5"))))】,然後按下【Ctrl+Shift+Enter】組合鍵,即可得出最大的的5個數據的求和結果,如下圖12示:
![](http://image.uc.cn/s/wemedia/s/upload/2024/ff0beb3dc18b79f7f0dd0c62b0e5362e.jpg)
圖12
注:在上例操作中,用到的幾個函數含義如下:
(1) INDIRECT:取1-5行;
(2) ROW:等到{1,2,3,4,5}數組;
(3) LARGE:求最大的5個數據並組成數組;
(4) SUM:將LARGE求得的數組進行求和。
爲了便于理解,還可以將公式簡化成【=SUM(LARGE(B2:C11,{1,2,3,4,5}))】。若要對最小的5個數據進行求和和運算,可輸入公式:【=SUM(SMALL(B2:C11,ROW(INDIRECT("1:5"))))】或【=SUM(SMALL(B2:C11,{1,2,3,4,5}))】。
我是鑒水魚老師,關注我,持續分享更多的Excel操作技巧。
謝謝分享!