如何在 Microsoft Excel 中使用 SUBTOTAL 函數
什麼是 Excel 中的小計?
通過創建公式或使用小計功能使用 SUBTOTAL 函數,只需幾步即可獲得所需的計算結果。
使用 Excel 小計函數
您可以使用 Excel SUBTOTAL 函數創建一個公式,該函數可以靈活地包含或排除已隱藏的行。
公式的語法 SUBTOTAL(function_number, reference 1, reference 2,...)
是需要前兩個參數的地方。您可以根據需要為其餘參數使用其他單元格引用或命名範圍。
該 function_number
參數允許您使用相應的編號插入 11 個函數之一。前 11 個包含隱藏行,而後 11 個不包含隱藏行。因此,只需插入所需功能的編號以及您希望如何處理隱藏行。
作為 SUBTOTAL 函數的示例,我們將使用以下公式對單元格範圍 B2 到 B4 中的銷售額求和:
=SUBTOTAL(9,B2:B4)
對於下一個示例,我們隱藏了第 4 行和第 5 行。使用第一個公式,我們可以使用數字 9 作為第一個參數來獲得我們的總和,以包括隱藏數據。
=SUBTOTAL(9,B2:B6)
現在,我們將從總數中排除那些隱藏的行,使用數字 109 作為我們的第一個參數。
=SUBTOTAL(109,B2:B6)
獲得小計後,您可以再次使用 SUBTOTAL 函數在底部進行總計。或者,您可以使用 SUM 函數添加小計。
再舉一個例子,我們將使用 average 函數通過以下公式包含隱藏的第 3 行和第 4 行:
=SUBTOTAL(1,C2:C6)
接下來,我們將使用此公式排除隱藏行:
=SUBTOTAL(101,C2:C6)
SUBTOTAL 函數的注意事項
在 Excel 中使用 SUBTOTAL 函數時需要注意以下幾點:
- 該函數適用於列或垂直範圍,不適用於行或水平範圍。
- 如果在引用參數區域中嵌套了小計,Excel 會在小計結果中忽略這些小計。
- 使用篩選器時,篩選出的數據將從小計結果中排除。
使用小計功能
在 Excel 中使用 SUBTOTAL 函數的另一種方法是使用小計功能。這樣,您可以自動添加計算並同時對項目進行分組。Excel 使用 SUBTOTAL 函數為您完成此操作。讓我們看幾個例子。
使用按月對銷售額進行小計的功能,我們可以按月對數據進行分組,然後在底部對每個組求和並得出總計。
選擇要分組和小計的所有行。轉到“數據”選項卡,然後在“大綱”下拉菜單中選擇“小計”。
當“小計”框打開時,選擇您希望如何添加小計。
- 在每次更改時:選擇要用於分組的列。在這裡,我們選擇了月份,以便在工作表中的月份發生變化時創建一個新組。
- 使用函數:選擇要使用的函數,例如總和、平均值、最小值、最大值或其他選項。對於我們的示例,我們選擇了 Sum。
- 將小計添加到:選中要計算小計的位置的複選框。對於我們的示例,我們選擇了銷售。
(可選)根據需要選中底部的框以獲取其他項目。單擊“確定”。
然後您將看到您的數據更新以對行進行分組和小計,並在底部創建一個總計。使用加號、減號和數字按鈕折疊或展開組以便於查看。
作為另一個例子,我們將使用學生成績。我們將使用小計和分組功能來顯示每個學生的平均成績。這是過程。
選擇行,轉到數據,然後在大綱下拉菜單中選擇“小計”。
在 Subtotal 框中,我們將在更改下拉列表中選擇 Student,在函數列表中選擇 Average。然後我們將選中等級框。單擊“確定”。
我們現在將行按學生分組,每個學生的平均成績和底部的總體平均成績。同樣,您可以使用左側的按鈕折疊和展開組。
如果您決定在使用小計功能後取消對行的分組,行將恢復正常。但是,那些包含 Excel 使用的 SUBTOTAL 函數的文件將保留供您繼續使用,或者如果您願意,只需將其刪除。
如果您計劃使用多個組,小計功能可能會變得複雜。但是,如果您對此功能感興趣,這些基本示例應該可以幫助您入門。
需要注意的一件事是您不能使用此功能添加 Excel 表格小計。如果您的數據在表格中,您可以如前所述插入 SUBTOTAL 的 Excel 公式,或者將表格轉換為單元格區域以使用該功能。如果選擇後者,您將失去表格功能。
既然您知道如何在 Excel 中插入小計,請查看如何刪除重複行。
發佈留言