SUBTOTAL 函數：公式以及如何在 Excel 中使用 SUBTOTAL 函數

Excel 中的 SUBTOTAL 函數是什麼？Excel中的小計公式是什麼？讓我們透過 LuckyTemplates.com 來了解一下吧！

Microsoft Excel 有許多用於計算總計的函數，SUBTOTAL 就是其中之一。但是，小計與 SUM 函數不同。這不是一個正常的金額。相反，它用於計算列表或資料庫中的小計、計算平均值等。

SUBTOTAL函數主要內容

• 什麼是 SUBTOTAL 函數以及它的用途是什麼？
• Excel 中的小計公式
• 如何使用 Excel 中的 SUBTOTAL 函數
  • 1. 計算過濾後的行總數 
  • 2. 統計過濾後的非空白儲存格數量
  • 3.忽略嵌套小計公式中的值
• 在 Excel 中使用 SUBTOTAL 函數之前要考慮的事項
• 在Excel中執行SUBTOTAL函數時如何出錯

什麼是 SUBTOTAL 函數以及它的用途是什麼？

乍一看，您可能會想：SUBTOTAL 函數的用途是什麼？畢竟，使用SUMIF或AVERAGE等現有函數可以輕鬆地從一系列單元格中找到小計。

雖然您不一定需要使用 SUBTOTAL，但它使分析目標數據變得更加容易。您可以使用 SUBTOTAL 將公式插入到一系列單元格中，而不會影響總和，因為 SUBTOTAL 會忽略包含 SUBTOTAL 公式的其他單元格。

SUBTOTAL 可與其他 Excel 功能搭配使用，例如篩選儲存格。如果按值篩選表格，SUBTOTAL 公式會更新、識別篩選器並排除對應的儲存格。SUBTOTAL 對於忽略隱藏值也很有用，這是其他函數（如 SUM）無法做到的。

幸運的是，您可以使用其他函數作為 SUBTOTAL 公式的一部分。有 11 種不同的小計方法可用，每種方法都適用於現有的 Excel 函數。如果您想要一個適用於篩選單元格並忽略其他 SUBTOTAL 公式的 SUM 公式，最好使用 SUBTOTAL，儘管資料透視表可以以相同的方式運作。

Excel 中的小計公式

SUBTOTAL 函數的工作原理是根據另一個數學函數計算一系列單元格的總值。SUBTOTAL 使用多達 11 個不同的數學函數（從 AVERAGE 到 VAR.P）來找到適當的總值，同時在函數參數內工作（即忽略其他 SUBTOTAL 公式）。

SUBTOTAL 函數的語法為=SUBTOTAL(function_num, ref1, [ref2],…)。在那裡：

• Function_num：從 1 到 11 和 101 到 111 的數字指定將使用哪個函數在 SUBTOTAL 中進行計算。
• Ref1、Ref2、...：1 個或多個儲存格或用於計算小計的儲存格範圍，最多 254 個。

筆記：

• SUBTOTAL 函數旨在計算垂直列的資料。
• 如果參數 ref1, ref2,... 包含 SUBTOTAL 函數，它們將被忽略以避免重複計算。
• 如果 function_num 為 1 到 11，則 SUBTOTAL 函數計算時會在資料集中包含隱藏值，因為包含該範圍的行是隱藏的。如果function_num從101到111，SUBTOTAL函數只計算資料集中的非隱藏值（不計算隱藏行中的值）。
• 對於被 Filter 隱藏的資料區域，SUBTOTAL 將被忽略。

如何使用 Excel 中的 SUBTOTAL 函數

1. 計算過濾後的行總數 

為了更好地理解這種類型的總和，請參閱文章在Excel中計算過濾列表值的總和。基本上，這種情況下的 SUBTOTAL 函數將是：

=小計(9,_範圍)

這裡的 pham_range 是過濾資料後想要求和的區域。

2. 統計過濾後的非空白儲存格數量

我們將使用 SUBTOTAL 3 或 SUBTOTAL 103。但是，如果存在隱藏行，則必須使用 SUBTOTAL 103 來準確計算可見的非空白儲存格。

例如，上面的資料表將隱藏 2 行 4 和 5。當使用 SUBTOTAL 3 或 SUBTOTAL 103 時，將產生 2 個不同的結果。

我們輸入公式，然後Excel會自動顯示函數集供您選擇，無需記住。

使用SUBTOTAL 3時的結果將為 3，包括該行中的隱藏儲存格。

至於SUBTOTAL 103，它只會顯示我們看到的非空白儲存格，忽略隱藏行。

3.忽略嵌套小計公式中的值

例如，我們將計算A1和A2倉庫中布料總公斤數的平均值。

倉庫 A2= SUBTOTAL(1,C2:C4) 的平均公式得出結果 19。

倉庫A1 = SUBTOTAL(1,C5:C7)的平均公式的結果為 38。

但是，在計算2個倉庫的布料總數的平均值時，2個倉庫的平均結果將被忽略。我們有公式=SUBTOTAL(1,C2:C9)，結果會自動刪除先前計算的平均結果。

在 Excel 中使用 SUBTOTAL 函數之前要考慮的事項

雖然 SUBTOTAL 有其優點，但在開始使用此函數之前您應該考慮一些事項，包括以下內容：

• 如前所述，使用 1-11 作為 function_num 參數將確保 SUBTOTAL 包含隱藏值，而 101-111 會忽略它們。
• 使用 1-11 或 101-111 以外的值將導致 Excel 傳回 #VALUE! 錯誤。對於 3D 儲存格參考（其中跨多個工作表的相同儲存格在一個範圍內引用）也會發生這種情況。
• 當使用水平儲存格區域（例如 A1:D1）時，會自動包含隱藏值（無論 function_num 參數值為何）。這是 SUBTOTAL 函數的限制，無法克服。
• 對過濾後的資料使用 SUBTOTAL 將確保始終忽略隱藏值，無論使用什麼 function_num 參數。
• SUBTOTAL 可以用作其他函數（包括包含 IF 函數的公式）的一部分的巢狀函數。
• 如果另一個 SUBTOTAL 公式位於資料範圍 ref1 內，則 SUBTOTAL 會忽略它並將其從整體運算中排除。
• Excel 允許在小計公式中使用最多 254 個儲存格區域，但此公式只需要 1 個儲存格區域即可運作。

在Excel中執行SUBTOTAL函數時如何出錯

在Excel中計算SUBTOTAL函數時，經常會遇到一些基本錯誤：

• #VALUE!：函數識別碼不在 1-11 或 101-111 範圍內或具有 3D 參考。
• #DIV/0!：當特定總和必須除以 0 時發生（例如，計算不包含數值的儲存格範圍的平均值或標準差）。
• ＃姓名？SUBTOTAL 函數名稱拼字錯誤。

關於如何在 Microsoft Excel 中使用 SUBTOTAL 函數的一些你可能不知道的提示

提示 1：假設您希望確保任一倉庫中的所有 T 卹顏色、每種尺寸都有貨。您可以按照以下步驟操作：

步驟1：點選小計。請記住，此步驟是為現有小計資料添加更多條件。

步驟 2：從下拉式選單中選擇「計數」 ，然後從「將小計欄位新增至」欄位中選擇「大小」。然後，取消選取替換目前小計。點擊「確定」後，您將收到以下資料表：

這有助於確保您擁有足夠數量的不同維度，並且可以以不需要重複操作的方式對資料進行分類。

提示 2：按列對資料進行排序總是有用的。

提示 3：幫助您在第一行中新增標籤。

提示 4：如果要匯總數據，請取消勾選「插入小計時在數據下方匯總」複選框。- 插入 SUBTOTAL 功能時彙總以下數據。

簡而言之，Excel中的小計函數是什麼？

顧名思義，Excel 中的 SUBTOTAL 函數用於計算小計。Excel SUBTOTAL 函數傳回所提供值的聚合結果。SUBTOTAL 可以計算總和、平均值、計數、最大值…此 Excel 還可以包含或排除隱藏行中的值。

• 目的：計算清單或資料庫中的小計。
• 傳回值： 代表小計類型的數字。
• 爭論：
  • function_num- 一個數字，指定使用哪個函數來計算列表中的小計。
  • ref1- 指定範圍或對小計的引用。
  • ref2- [可選] 命名範圍或對小計的引用。

使用 SUBTOTAL 函數可以幫助您快速分析資料集，而無需依賴更複雜的資料透視表。如果遇到困難，您也可以使用功能區「資料」標籤中的小計功能自動建立小計公式，然後您可以在其他地方編輯或複製該公式。

看更多：

• Excel中的SUMPRODUCT函數：計算對應值的總乘積
• 如何在 Excel 中合併 2 列全名和名字而不遺失內容