SUBTOTAL用法有很多種方式，其中最強大的是它可以排除掉隱藏值，在報表排序篩選時一定會用到，本文以如何連續編號為範例介紹，補充完整的22種函數功能。

職場真實的案例需求

通常ERP系統跑出來的報表都會有個序號，代表每筆資料的流水編號，這是完整有規範報表必然的特性之一。然而在經過Excel資料整理統計分析之後，尤其是排序或篩選，原本的流水編號會跑掉，本節以應付帳款明細表為例，分享如何設計函數公式避免此情形。

詳細圖文步驟教學

Step 1 應付帳款明細表

相當簡化的應付帳款明細表，其中作為流水編號的「項次」，基本上是依照「帳款日期」排序而來。

Step 2 報表篩選問題

Excel資料統計分析免不了篩選，篩選之後會有個小麻煩，原本作為流水編號的「項次」欄位會跑掉。

Step 3 排序編號跑掉

Excel資料統計分析也免不了排序，排序之後同樣會有個小麻煩，原本作為流水編號的「項次」欄位照樣跑掉。

Step 4 COUNTA函數

設計函數公式：「=COUNTA(A$2:A2)」，COUNTA函數作用為「計算範圍中非空白儲存格的數目」，這裡以「$」固定的方式，將游標移到B2儲存格右下角變成小黑十字架時，按住往下拖曳複製公式，便是計算各個儲存格從A2到目前列號的垂直範圍的非空白儲存格數目。例如以B2來說，便是A2到A2，計算結果為「1」，以B11來說，便是A2到A11，計算結果為「10」，如此等於重新建立流水編號，而且如同圖片所示，即使將原始報表依照「本幣金額」由大到小排序，A欄的「項次」順序會跑掉，但B欄依照函數計算結果，仍然會是依序的流水編號。

Step 5 公式有所不足

COUNTA函數雖然排序沒有問題，但如果是篩選的話，因為有資料被隱藏了，公式中相對應的列數並沒有變，所以篩選會變成跟「項次」一樣是跑掉的狀態。

Step 6 強化篩選功能

為了加強COUNTA函數於篩選狀態的不足，須另外設計公式：「=SUBTOTAL(103,A$1:A1)」，如圖所示，利用SUBTOTAL函數可以達到篩選後仍然流水編號。

Step 7 完美解決方案

承上個步驟，不僅僅是篩選，重新排序後，SUBTOTAL也可以執行和COUNTA相同的任務，因此SUBTOTAL相對而言是較為完整理想的函數公式，關於此函數的用法於下個步驟說明。

Step 8 SUBTOTAL用法

延用本章上一節第四步驟和第七步驟相同方法，於SUBTOTAL公式的函數引數視窗左下角，點選超連結到該函數的說明文件網頁，可以非常清楚地瞭解SUBTOTAL的用法，第二個參數為加總範圍，第一個參數為加總方法，而且還可以設定是否忽略隱藏值，本節範例為「103」，代表「忽略隱藏的值」且使用COUNTA函數，計算結果如同本節第六步驟及第七步驟所示。

應該保有的1個好習慣

這一節介紹如何設計COUNTA和SUBTOTAL函數，以便於排序或篩選後重新建立流水編號，像這樣完美的函數公式看似可以取代原有的流水編號了，不過最後在這裡補充一點，即使已經有了函數公式欄位，原有的項次編號建議不要因此刪除，因為和建立完整Excel公式模型一樣的道理，將所編製報表和原始資料分開，保留原始資料沒有任何變動，有問題或需要更新時較好處理。以這一節的範例而言，保留了原有的項次編號，隨時以此項次欄位排序，便可以回到最一開始的報表狀態，再擴大而言，就算原始報表沒有流水編號，在預知資料處理會改變原始報表順序時，也可以利用簡單的函數公式另外建立流水編號，讀者有興趣可以自行嘗試看看。

多學一招：Excel成本計算：篩選平均值及Subtotal條件計算平均數。

微軟說明頁面：SUBTOTAL 函數。

本篇文章相關的講解影片：

想學習更多Excel技巧嗎？歡迎前往贊贊小屋Excel教學中心。