Excel裡SUM函式的延伸函式-看看你都會哪些

今天和大家分享SUM函式的延伸函式,一起學習Excel函式中SUMIF函式,SUMIFS函式,SUMPRODUCT函式,SUBTOTAL函式,AGGREGATE函式的用法。

Excel裡SUM函式的延伸函式-看看你都會哪些

SUM函式

最常用的求和函式,我每說一次,都有不少朋友評論說我囉嗦。要是所有的朋友都是這樣,那就太好了,因為都會用了才會這樣說。

如下圖所示,是一份產品記錄表,使用以下公式,即可計算所有物品的數量總和。

=SUM(D2:D16)

Excel裡SUM函式的延伸函式-看看你都會哪些

SUMIF函式

如下圖,我們要果要計算出部門廣州的所有品求和,我們就得用到SUNIF函數了。

這個函式的用法是:

=SUMIF(條件區域,指定的條件,求和區域)

下圖所示,要統計計”部門廣州”的所有產品總和,公式為:

=SUMIF(A2:A16,I2,E2:E14),也可以=SUMIF(A2:A16,”部門廣州”,E2:E14)

Excel裡SUM函式的延伸函式-看看你都會哪些

公式的意思是,如果A2:A16單元格區域中等於I2指定的部門“部門廣州”,就對E2:E16單元格區域對應的數值進行求和。

SUMIFS函式

SUMIFS函式多條件求和。這個函式的用法是:

=SUMIFS(求和區域,條件區域1,指定的條件1,條件區域2,指定的條件2,……)

第一引數指定要求和的區域,後面是一一對應的條件區域和指定條件,多個條件之間是同時符合的意思,可以根據需要,最多寫127對區域/條件。

如下圖所示,要計算部門是部門廣州,單價在21元以下的產品部和。

公式為:

=SUMIFS(E2:E16,A2:A16,I2,G2:G16,J2)

Excel裡SUM函式的延伸函式-看看你都會哪些

公式的意思是,如果A2:A16單元格區域中等於I2指定的部門“部門廣州”,並且G2:G16單元格區域中等於指定的條件“<21”,就對E2:D16列對應的數值求和。

SUMIF或是SUMIFS的判斷條件除了引用單元格中的內容,也可以直接寫在公式中:

=SUMIFS(E2:E16,A2:A16,“部門廣州”,G2:G16,“<21”)

SUMPRODUCT函式

該函式作用是將陣列間對應的資料相乘,並返回乘積之和。

如下圖所示,要計算採購所有物資的總金額,公式為:

=SUMPRODUCT(E2:E16,G2:G16)

Excel裡SUM函式的延伸函式-看看你都會哪些

公式中,將E2:E16的數量和G2:G16的單價分別對應相乘,然後將乘積求和,得出總金額。

使用SUMPRODUCT函式,還可以計算指定條件的乘積。

如下圖所示,要分別計算職工食堂和領導餐廳的物資採購金額。公式為:

=SUMPRODUCT((B$2:B$14=G2)*1,D$2:D$14,E$2:E$14)

公式先使用:A2:A16=”部門廣州”,依次判斷A列的部門是不是等於”部門廣州”指定的部門,得到一組由邏輯值TRUE和FALSE構成的記憶體陣列,然後將這一組邏輯值乘以1,邏輯值TRUE乘1,結果是1,邏輯值FALSE乘1,結果是0。

最後,將三個陣列的元素對應相乘後,再計算出乘積之和。

SUBTOTAL函式

僅對可見單元格彙總計算,能夠計算在篩選狀態下的求和。

如下圖,對F列的長度進行了篩選,使用以下公式可以計算出篩選後的數量之和。

=SUBTOTAL(9,F2:F11)

Excel裡SUM函式的延伸函式-看看你都會哪些

SUBTOTAL第一引數用於指定彙總方式,可以是1~11的數值,我們這裡的9是求和,透過指定不同的第一引數,可以實現平均值、求和、最大、最小、計數等多種計算方式。

如果第一引數使用101~111,還可以忽略手工隱藏行的資料,小夥伴們有空可以試試。

AGGREGATE函式

類似SUBTOTAL函式,功能相對更多,第一引數可以使用1到19的數值,來指定19種不同的彙總方式。第二引數使用1到7,來指定忽略哪些內容。

Excel裡SUM函式的延伸函式-看看你都會哪些

如下圖所示,已經對A列的部門進行了篩選,而且E列的長度計算結果有錯誤值,使用以下公式,可以對E列的長度求和。

=AGGREGATE(9,7,E2:E16)

AGGREGATE函式第一引數使用9,表示彙總方式為求和,第二引數使用7,表示忽略隱藏行和錯誤值。

作者:Excel使用實戰精粹

頂部