贊贊小屋

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序

Excel資料分析常常用到篩選與排序,可是原始報表的項次編號會因此跑掉,本文以應付帳款明細表為例,介紹設計COUNTA功能的SUBTOTAL函數公式,報表保持連續編號。

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

一、應付帳款明細表

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

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序 加總函數 第1張

二、篩選欄位跑掉

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

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序 加總函數 第2張

三、排序欄位跑掉

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

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序 加總函數 第3張

四、COUNT函數

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

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序 加總函數 第4張

五、COUNT欄位跑掉

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

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序 加總函數 第5張

六、SUBTOTAL函數篩選

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

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序 加總函數 第6張

七、SUBTOTAL函數排序

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

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序 加總函數 第7張

八、SUBTOTAL完整用法

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

Excel善用SUBTOTAL函數,應付帳款明細表篩選後自動排序 加總函數 第8張

保留原始資料的好習慣

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

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

https://www.books.com.tw/products/0010817518