表格用了篩選，求和是否應改用SUBTOTAL？

來源：通商軟件2025-07-082025-07-08

ERP系統 & MES 生產管理系統

10萬用戶實施案例,ERP 系統實現微信、銷售、庫存、生產、財務、人資、辦公等一體化管理

在Excel中進行數據篩選時，正確地使用求和功能是非常重要的。尤其是在篩選數據時，是否應該使用`SUBTOTAL`而非傳統的`SUM`函數，這個問題需要仔細分析。本文將詳細探討為什么在進行數據篩選后，選擇`SUBTOTAL`函數進行求和是更加合適的，比較兩者的區別以及如何有效地使用`SUBTOTAL`來處理篩選后的數據。

1. 為什么選擇SUBTOTAL函數而非SUM函數？

在Excel中，`SUM`函數和`SUBTOTAL`函數都能夠幫助用戶進行數據求和。但是在進行篩選時，它們的表現卻有所不同。`SUM`函數計算的數據包含了所有單元格，無論這些單元格是否被篩選出來。而`SUBTOTAL`函數則與篩選條件緊密配合，它只會對可見的數據進行求和，不受隱藏行的影響。

舉個例子，假設你有一個包含多個產品銷售數據的Excel表格，并且已經應用了篩選功能，僅顯示出某一特定區域的產品數據。如果你使用`SUM`函數，它會將所有行的數值相加，包括那些被隱藏的行。而使用`SUBTOTAL`函數，它只會對篩選后仍然可見的行進行求和，避免了隱藏行的數據被錯誤地計入結果。

2. SUM和SUBTOTAL函數的功能差異

`SUM`函數是一個最常用的求和工具，但它的缺點在于無法識別篩選后的數據。它直接對所有選擇的單元格進行求和，這可能導致在數據篩選過程中出現計算誤差。而`SUBTOTAL`函數則提供了多種不同的計算方式，不僅能進行求和，還能執行平均值、計數、最大值、最小值等操作。`SUBTOTAL`的功能更為靈活，它可以通過不同的函數代碼來完成各種類型的計算，下面是常見的`SUBTOTAL`函數代碼及其含義：

– 1：求平均值

– 2：計數

– 3：計數非空單元格

– 9：求和

– 10：最大值

– 11：最小值

其中，代碼9對應的就是常規的求和功能。

3. 如何在篩選后使用SUBTOTAL進行求和？

要在篩選后使用`SUBTOTAL`函數進行求和，用戶只需按照以下步驟進行操作：

1. 應用篩選條件：首先，用戶需要對數據應用篩選。例如，篩選出某些特定日期、產品類別或銷售區域。

2. 使用SUBTOTAL函數：在篩選后的數據區域，選擇一個單元格輸入公式`=SUBTOTAL(9,范圍)`。其中，`9`表示求和功能，`范圍`是你想要求和的單元格范圍。值得注意的是，`SUBTOTAL`函數會自動忽略那些被篩選掉的行。

例如，假設你要對A2到A20的銷售數據進行求和，而這些數據已經通過篩選顯示了一部分結果。你可以在某個空白單元格中輸入`=SUBTOTAL(9, A2:A20)`，Excel會自動計算所有可見數據的和，而跳過那些被隱藏的數據行。

4. 使用SUBTOTAL時的一些注意事項

在使用`SUBTOTAL`時，有幾點需要特別注意：

– 篩選狀態的影響：`SUBTOTAL`函數依賴于數據的篩選狀態。因此，任何更改篩選條件（如隱藏某些行或改變篩選規則）都會直接影響結果。

– 不同類型的計算：`SUBTOTAL`不僅僅可以用來求和，還可以用來計算其他類型的統計數據（如平均值、最大值、最小值等）。因此，你可以根據需要靈活選擇不同的函數代碼來完成不同類型的數據分析。

– 公式復制的注意：如果你在一個區域內應用了`SUBTOTAL`函數并且進行了篩選，記得在復制和粘貼公式時，確保引用的單元格范圍保持正確，否則可能會導致錯誤的計算結果。

5. SUBTOTAL和其他函數的比較

除了`SUM`和`SUBTOTAL`，在Excel中，還有一些其他函數也可以用來求和或執行其他統計操作，如`SUMIF`、`SUMIFS`、`AVERAGEIF`等。與`SUBTOTAL`不同的是，`SUMIF`和`SUMIFS`可以基于條件進行求和，但它們不會自動排除隱藏的數據行。在篩選數據時，`SUMIF`和`SUMIFS`仍然會計算被隱藏行的數據，這可能會影響最終的結果。

而`SUBTOTAL`則特別設計用于篩選后的數據分析，能夠排除隱藏行。因此，`SUBTOTAL`是處理篩選后數據時最推薦的函數之一。