如何在 Microsoft Excel 中使用 SUBTOTAL 函數

如何在 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)

Excel 中的基本小計公式

對於下一個示例,我們隱藏了第 4 行和第 5 行。使用第一個公式,我們可以使用數字 9 作為第一個參數來獲得我們的總和,以包括隱藏數據。

=SUBTOTAL(9,B2:B6)

包含隱藏行的 SUBTOTAL 函數

現在,我們將從總數中排除那些隱藏的行,使用數字 109 作為我們的第一個參數。

=SUBTOTAL(109,B2:B6)

SUBTOTAL 函數排除隱藏行

獲得小計後,您可以再次使用 SUBTOTAL 函數在底部進行總計。或者,您可以使用 SUM 函數添加小計。

再舉一個例子,我們將使用 average 函數通過以下公式包含隱藏的第 3 行和第 4 行:

=SUBTOTAL(1,C2:C6)

包含隱藏行的 SUBTOTAL 函數

接下來,我們將使用此公式排除隱藏行:

=SUBTOTAL(101,C2:C6)

SUBTOTAL 函數排除隱藏行

SUBTOTAL 函數的注意事項

在 Excel 中使用 SUBTOTAL 函數時需要注意以下幾點:

  • 該函數適用於列或垂直範圍,不適用於行或水平範圍。
  • 如果在引用參數區域中嵌套了小計,Excel 會在小計結果中忽略這些小計。
  • 使用篩選器時,篩選出的數據將從小計結果中排除。

使用小計功能

在 Excel 中使用 SUBTOTAL 函數的另一種方法是使用小計功能。這樣,您可以自動添加計算並同時對項目進行分組。Excel 使用 SUBTOTAL 函數為您完成此操作。讓我們看幾個例子。

使用按月對銷售額進行小計的功能,我們可以按月對數據進行分組,然後在底部對每個組求和並得出總計。

選擇要分組和小計的所有行。轉到“數據”選項卡,然後在“大綱”下拉菜單中選擇“小計”。

大綱菜單中的小計

當“小計”框打開時,選擇您希望如何添加小計。

  • 在每次更改時:選擇要用於分組的列。在這裡,我們選擇了月份,以便在工作表中的月份發生變化時創建一個新組。
  • 使用函數:選擇要使用的函數,例如總和、平均值、最小值、最大值或其他選項。對於我們的示例,我們選擇了 Sum。
  • 將小計添加到:選中要計算小計的位置的複選框。對於我們的示例,我們選擇了銷售。

(可選)根據需要選中底部的框以獲取其他項目。單擊“確定”。

Sum 函數的小計設置

然後您將看到您的數據更新以對行進行分組和小計,並在底部創建一個總計。使用加號、減號和數字按鈕折疊或展開組以便於查看。

Sum 函數的小計

作為另一個例子,我們將使用學生成績。我們將使用小計和分組功能來顯示每個學生的平均成績。這是過程。

選擇行,轉到數據,然後在大綱下拉菜單中選擇“小計”。

在 Subtotal 框中,我們將在更改下拉列表中選擇 Student,在函數列表中選擇 Average。然後我們將選中等級框。單擊“確定”。

平均函數的小計設置

我們現在將行按學生分組,每個學生的平均成績和底部的總體平均成績。同樣,您可以使用左側的按鈕折疊和展開組。

平均函數的小計

如果您決定在使用小計功能後取消對行的分組,行將恢復正常。但是,那些包含 Excel 使用的 SUBTOTAL 函數的文件將保留供您繼續使用,或者如果您願意,只需將其刪除。

未分組行的小計平均值

如果您計劃使用多個組,小計功能可能會變得複雜。但是,如果您對此功能感興趣,這些基本示例應該可以幫助您入門。

需要注意的一件事是您不能使用此功能添加 Excel 表格小計。如果您的數據在表格中,您可以如前所述插入 SUBTOTAL 的 Excel 公式,或者將表格轉換為單元格區域以使用該功能。如果選擇後者,您將失去表格功能。

既然您知道如何在 Excel 中插入小計,請查看如何刪除重複行。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *