Excel 使用者經常需要靈活的方式來彙總資料，這些方式要能回應篩選或分組。傳統的函數如 SUM()、AVERAGE() 或 COUNT() 是根據範圍內的所有儲存格計算結果，無論某些列是否被隱藏或篩選掉。

幾十年來，SUBTOTAL() 函數一直是首選解決方案，它提供了一種動態替代方案，能根據當前可見內容自動調整其計算。然而，掌握它需要理解複雜的語法和函數代碼。如今，現代的AI 工具提供了一條更簡單的途徑來獲得相同結果，讓您可以使用簡單語言而非公式。

在本指南中，我們將涵蓋這兩種方法。我們將全面解析強大的 SUBTOTAL() 函數，然後展示像 Excelmatic 這樣的 AI 代理如何能在極短時間內實現相同的動態計算。

Excel 中的 SUBTOTAL() 函數是什麼？

SUBTOTAL() 函數計算資料集的彙總值，例如總和、平均值、計數和其他統計運算。它與標準 Excel 函數的不同之處在於其動態工作方式——根據可見或篩選的內容改變其結果。根據您指定的函數編號，該函數可以忽略手動隱藏的列和透過篩選隱藏的列。

當您對資料套用篩選或手動隱藏某些列時，SUBTOTAL() 會自動調整其計算，僅包含可見的儲存格。這種行為使其成為在篩選資料集中建立摘要列、建構互動式儀表板或產生需要根據使用者選擇更新的報告的絕佳選擇。

該函數支援與 Excel 標準函數相同的統計運算——包括總和、平均值、計數、最大值、最小值和其他幾種——但它們被打包成一個單一的、具有情境感知能力的函數，能適應您資料的當前狀態。

語法和支援的運算

SUBTOTAL() 函數遵循一個簡單的語法結構：

=SUBTOTAL(function_num, ref1, [ref2], ...)

第一個參數 function_num 決定了計算類型以及函數處理隱藏列的方式。其餘參數（ref1、ref2 等）是您要包含在計算中的儲存格範圍。您最多可以指定 254 個不同的範圍。

函數編號根據其處理手動隱藏列的方式分為兩個不同的類別：

函數編號	運算	包含手動隱藏的列	排除手動隱藏的列
1 / 101	平均值	1	101
2 / 102	計數	2	102
3 / 103	計數非空值	3	103
4 / 104	最大值	4	104
5 / 105	最小值	5	105
6 / 106	乘積	6	106
7 / 107	標準差（樣本）	7	107
8 / 108	標準差（母體）	8	108
9 / 109	總和	9	109
10 / 110	變異數（樣本）	10	110
11 / 111	變異數（母體）	11	111

函數編號 1-11 在其計算中包含手動隱藏列的儲存格，而函數編號 101-111 則排除它們。然而，這兩個範圍總是忽略被篩選隱藏的列。

例如，SUBTOTAL(9, A2:A10) 計算 A2:A10 的總和，包含任何手動隱藏的列，而 SUBTOTAL(109, A2:A10) 則從總和計算中排除手動隱藏的列。在這兩種情況下，篩選掉的列都會自動從結果中排除。

動態計算實戰：兩種方法

讓我們透過一個包含電子產品和家具店銷售資料的實際範例，看看如何計算篩選資料的摘要。

我們的樣本資料包含兩個類別的七種產品。在未套用任何篩選的情況下，SUBTOTAL(109,C2:C8) 公式和常規的 SUM(C2:C8) 公式顯示相同的結果：3525（所有銷售額的總和）。

現在，讓我們篩選資料，僅顯示「Electronics」產品。

方法 1：傳統的 SUBTOTAL() 公式

套用篩選後，請注意我們計算結果的關鍵差異：

• SUBTOTAL 公式顯示 3000 - 自動調整為僅加總可見的「Electronics」列。
• 常規 SUM 公式仍顯示 3525 - 繼續包含隱藏的「Furniture」項目。

這個並排比較展示了 SUBTOTAL() 的核心優勢：它能動態回應篩選，這對於製作回應式報告至關重要。

方法 2：AI 驅動的 Excelmatic 方法

無需編寫和除錯公式，您只需提問即可獲得相同結果。使用像 Excelmatic 這樣的 AI 代理，過程要簡單得多：

1. 上傳您的 Excel 檔案。

2. 用簡單語言提問。 對於此範例，篩選後，您可以問：

   可見資料的「Sales」欄總和是多少？

Excelmatic 會立即分析可見資料並給您答案：3000。

對比很明顯：SUBTOTAL() 要求您知道正確的語法和函數編號（是 9 還是 109？）。Excelmatic 能從自然語言理解您的意圖，無需任何公式知識即可提供相同準確、動態的結果。

SUBTOTAL() 如何處理隱藏和篩選的資料

SUBTOTAL() 函數以特定方式處理不同類型的隱藏資料，因此在建構公式時理解這些行為至關重要。

SUBTOTAL() 與篩選掉的列

當您對資料集套用篩選時，SUBTOTAL() 總是從其計算中排除被篩選掉的列，無論您使用哪個函數編號。這種行為是自動的，並且在所有函數編號（1-11 和 101-111 範圍）中保持一致。

SUBTOTAL() 與手動隱藏的列

手動隱藏的列根據您選擇的函數編號以不同方式處理。當您右鍵點選列號並選擇「隱藏」時，您就是在手動隱藏這些列。

• 使用函數編號 1-11 會包含這些手動隱藏的列在計算中。
• 使用函數編號 101-111 會排除它們。

這為您提供了細粒度控制，但這是需要記住的另一層複雜性。相比之下，AI 工具通常根據視覺上呈現的內容進行計算，或者您可以在提示中指定「包含手動隱藏的列」以獲得更直接的控制。

SUBTOTAL() 與水平和垂直範圍

SUBTOTAL() 函數最適合處理垂直資料範圍（資料欄）。雖然它可以處理水平範圍，但其隱藏行為針對基於列的操作進行了優化，因為 Excel 的篩選和列隱藏功能是垂直工作的。

當您在同一範圍內巢狀 SUBTOTAL() 公式時，該函數會自動跳過其他 SUBTOTAL() 結果以避免重複計算。這對於分層摘要很有用。

SUBTOTAL() 函數的進階用法

使用 SUBTOTAL() 建立動態下拉式摘要

您可以將 SUBTOTAL() 與 IF() 函數結合使用來建立互動式摘要。為此，您需要先設定一個下拉式清單（例如在儲存格 D1 中），其中包含 "Sum"、"Average"、"Count" 等選項。

然後，您需要編寫一個複雜的巢狀 IF 公式來更改計算：

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))

AI 替代方案： 使用 Excelmatic，您可以完全跳過公式。您只需為每個計算提出一個新問題：

• 「可見列的銷售總和是多少？」
• 「可見列的平均銷售價格是多少？」
• 「計算可見產品的數量。」

這種對話式方法要直觀得多，且無需任何設定。

Excel 表格和大綱中的 SUBTOTAL()

Excel 表格在「總計列」功能中自動使用 SUBTOTAL()。當您將資料轉換為表格（Ctrl+T）並啟用總計列時，Excel 預設會插入 SUBTOTAL(109, …)，當您篩選時它會動態更新。大綱功能（資料 > 小計）也依賴 SUBTOTAL() 來建立分層摘要。這些是利用該函數功能的強大內建功能。

SUBTOTAL() 的常見陷阱和限制

了解 SUBTOTAL() 的限制有助於您避免錯誤，並突顯出現代工具在哪些方面提供更好的體驗。

• 使用無效函數編號導致 #VALUE! 錯誤： 使用 1-11 或 101-111 範圍之外的函數編號會傳回 #VALUE! 錯誤。這是一個常見的拼寫錯誤，可能會破壞您的報告。
• 隱藏欄與隱藏列的行為差異： SUBTOTAL() 總是包含隱藏欄中的資料。如果您期望它像處理列一樣處理，這可能會導致意外結果。
• 不支援 3D 參照： SUBTOTAL() 無法參照跨多個工作表的範圍（Sheet1:Sheet3!A1:A10）。這對於合併報告來說是一個主要限制。然而，Excelmatic 可以同時分析來自多個上傳檔案或工作表的資料，使跨工作表分析變得簡單直接。
• 錯誤值仍包含在計算中： SUBTOTAL() 不會忽略包含 #N/A 或 #DIV/0! 等錯誤的儲存格，這可能會使您的整個結果無效。您必須先清理資料，或者使用更複雜的 AGGREGATE() 函數。
• 垂直資料方向效果最佳： 該函數是為標準的基於欄的資料設計的，對於水平佈局可能會有不可預測的行為。

Subtotal() vs. AGGREGATE() vs. AI 代理

多年來，AGGREGATE() 函數一直是解決 SUBTOTAL() 限制的答案。AGGREGATE() 提供更多函數（總共 19 個），並且關鍵是，可以設定為忽略錯誤值。

• SUBTOTAL()：適用於對乾淨、篩選過的資料進行基本的動態計算。
• AGGREGATE()：更適合包含錯誤的資料集，或者當您需要更進階的統計函數（如 MEDIAN 或 PERCENTILE）時。
• AI 代理（如 Excelmatic）：最簡單且最強大的選項。它們處理篩選資料，通常可以自動忽略錯誤，跨多個工作表工作，並且不需要公式語法——只需簡單的英文問題。

使用 SUBTOTAL() 時的專業技巧

如果您選擇公式路徑，這些技巧將幫助您成功：

始終先排序

使用 Excel 內建的「資料 > 小計」功能時，請先按分組欄對資料進行排序，以確保分組中斷清晰。

在欄的第一列使用標籤

描述性標題使您的報告更易於解釋，並有助於 Excel 的自動化工具正確工作。

注意摘要放置位置（上方或下方）

Excel 的小計工具可以將摘要放在每個組的上方或下方。選擇最適合您報告受眾的格式。

隱藏 vs. 篩選列以實現精確控制

了解篩選（臨時性）和手動隱藏（半永久性）之間的區別，以獲得您期望的結果，並據此選擇您的函數編號（例如 109）。

結論

學習使用 SUBTOTAL() 是一項寶貴的 Excel 技能，使您能夠建立有彈性、動態的報告。它代表了從像 SUM() 這樣的靜態函數向前邁出的重要一步。

然而，資料分析的格局正在演變。對於需要快速、準確答案，但又不想陷入語法、函數代碼和錯誤處理泥潭的專業人士來說，像 Excelmatic 這樣的 AI 代理提供了一個引人注目的替代方案。透過將簡單的語言問題轉化為強大的資料分析，這些工具使從資料中獲取洞察的能力大眾化，讓您可以專注於結果，而不是公式。能夠提問「僅可見列的總計是多少？」並立即獲得正確答案，這對於效率來說是一個改變遊戲規則的能力。

無論您選擇掌握 SUBTOTAL() 的複雜性，還是擁抱 AI 的簡單性，執行動態計算的能力都是 Excel 中有效資料分析的基石。

👉 跳過公式，立即獲得動態洞察。免費試用 Excelmatic。

---

為什麼我應該在 SUBTOTAL() 中使用函數編號 109 而不是 9？

函數編號 9 在計算中包含手動隱藏的列，而 109 則排除它們。當您希望同時忽略篩選掉和手動隱藏的列時，請選擇 109。

Excel 中 SUBTOTAL() 和 SUM() 有什麼區別？

SUBTOTAL() 會自動從計算中排除篩選掉的列，而 SUM() 則包含所有儲存格，無論是否篩選。SUBTOTAL() 還會忽略其他小計公式以防止重複計算。

我應該何時使用像 SUBTOTAL() 這樣的公式，何時使用 AI 工具？

當您需要建立永久性、結構化的報告，且計算邏輯必須嵌入儲存格時，請使用 SUBTOTAL()。當您需要進行快速、臨時的分析、探索資料，或者希望避免複雜公式並獲得即時答案時，請使用 AI 工具。

SUBTOTAL() 可以處理跨多個工作表的資料嗎？

不行，SUBTOTAL() 僅適用於同一工作表內的範圍。對於多工作表分析，您需要使用變通方法或可以處理多個檔案/工作表的 AI 工具。

我可以在一個 SUBTOTAL() 公式中包含多少個範圍？

您可以在一個 SUBTOTAL() 公式中指定最多 254 個不同的範圍。每個範圍都作為函數編號之後的單獨參數添加。

我應該何時選擇 AGGREGATE() 而不是 SUBTOTAL()？

當您的資料包含需要排除的錯誤值，或者當您需要超出 SUBTOTAL() 提供的 11 種基本運算之外的統計函數時，請使用 AGGREGATE()。