贊贊小屋

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序

Excel資料分析時常用到排序指令,本文以應收帳款逾期管控表為例,介紹以函數公式完成報表自動排序,會用到COUNTIF、LARGE、ROW、SUMPRODUCT、INDIRECT、IFERROR等較為進階的函數。

Excel排序是會計實務工作中很常用的工具,無論審計查帳、公司財務分析都會強調重大性原則,交易金額越大、越是值得重視,金額太小除了性質特殊,否則可以忽略。除了金額之外,也經常會就日期作排序,例如逾期應收,希望將逾期越久的帳款排在越上面,因為這是必須重點追踪的異常項目。像這樣的情況,通常很習慣使用Excel的排序指令,不過贊贊小屋在《會計人的Excel小教室》系列作品中,常常分享如何設計函數完成指令任務,因為方法是越多越好,以下便具體介紹以函數公式自動排序逾期應收帳款:

一、怎麼還沒付錢的應收帳款

應收帳款逾期明細表,有個欄位是標明是否逾期,後面兩欄是逾期天數和逾期金額。

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序 INDIRECT函數 第1張

二、條件計數必學的COUNTIF函數

在後面新增一欄,輸入:「=COUNTIF($E$2:E2,E2)」,此公式巧妙利用固定儲存格的方式,將第一個「$E$2」固定住,隨著公式往下複製,變成是E2到E3中E3出現次數、E2到E4中E4出現的次數,剛好可以計算出各個項目到目前為止出現的次數,可說是COUNTIF函數的經典用法之一。在這裡的範例是能找出有相同逾期天數的帳款,無逾期顯示為零,例如逾期60天有三筆帳款,依序顯示為「1」、「2」、「3」。

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序 INDIRECT函數 第2張

三、奇妙組合的LARGE及ROW函數

輸入另一個公式:「=LARGE($E$2:$E$9,ROW()-1)」LARGE函數能找出某個範圍第幾大的值,參數一「$E$2:$E$9」是逾期天數範圍,從「函數引數」視窗可以清楚看到其陣列值,參數二表示第幾大,這個使用:「ROW()-1」,配合一開始為第二列拉下來,剛好是從1遞增的數列(1,2,3,……)。公式結果如圖所示,等於是以函數方式將逾期天數從大排到小,最下面兩項為「#NUM!」,這是因為「逾期天數」只有六項,並不存在第七大或第八大這個東西。

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序 INDIRECT函數 第3張

四、COUNTIF函數的經典設計

上個步驟已經成功排序逾期天數的基礎上,接下來進一步再於I欄設計函數公式:「=COUNTIF($H$2:H2,H2)」,這裡I欄的作用和第二步驟G欄是一樣的,因為逾期天數會有相同重複的情形,例如60天有三筆,必須藉助COUNTIF計算並標示相同的逾期天數。

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序 INDIRECT函數 第4張

五、意想不到的SUMPRODUCT陣列函數

接下來公式較為複雜:「=SUMPRODUCT(($E$2:$E$9=H4)*($G$2:$G$9=I4)*ROW($A$2:$A$9))」。這裡可以將SUMPRODUCT函數視為多條件的VLOOKUP,而且SUMPRODUCT本身帶有陣列特性,所以即使公式中設有範圍陣列,毋須像一般陣列公式最後以「Ctrl+Shift+Enter」產生左右大括號,變換性質為陣列計算。

為清楚說明公式,以J4為例,條件一「($E$2:$E$9=H4)」表示E欄中要等於H4,如此E4、E6、E7都合乎資格,亦即逾期天數要是60天,條件二「($G$2:$G$9=I4)」表示G欄中要等於I4,如此G6和G8都符合條件,亦即帳款出現次數為2次,利用條件成立為1,條件不成立為0,以Excel特殊的邏輯值化規則,陣列中僅有第六筆「SA005」合乎前面兩個條件,兩者相乘為1X1,其餘銷貨單號因為至少有一個條件不成立為零,零乘以多少結果皆為零。

在公式前兩項條件的基礎上,第三個參數「ROW($A$2:$A$9)」在A欄第六筆為是6,其餘列號資料都是零,所以最終J4儲存格SUMPRODUCT公式計算結果為6。

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序 INDIRECT函數 第5張

六、INDIRECT神抓手與IFERROR偵錯器

得到了依照條件排序的列號,再使用INDIRECT函數抓取資料:「=IFERROR(INDIRECT(“A”&$J2),””)」。顧名思義,INDIRECT函數是以間接方式連結儲存格,這裡的「”A”」,表示引用A欄,「&」後面的「$J2」,表示要引用A欄中的第幾列,「$」作用是把欄位固定位,不會隨著儲存格拖曳而變動,「IFERROR(……,””)」是指計算出現錯誤時顯示空白,例如K8和K9,把公式往下拉的結果如圖所示。

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序 INDIRECT函數 第6張

七、乾坤大挪移後的重新排序報表

以上個步驟為基礎,繼續開展下去,便可以將原來的資料複製過來,呈現出來的報表,已經依照逾期天數排序。

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序 INDIRECT函數 第7張

既有排序,何需SUMPRODUCT及INDIRECT函數

熟悉Excel操作的人,應該都有使用過非常便利的排序功能,這一節花了很多心思設計函數公式,最終達到相同效果。這樣做的缺點是一開始架公式比較麻煩,但優點是一旦架好了,可以重覆利用,往後只要有新的資料,直接把內容貼值進去,馬上就會得到排序好的報表,所以特別適用於例行性的報表,例如這篇文章使用的逾期應收範例。

這節範例也分享了以SUMPRODUCT函數公式執行多條件VLOOKUP任務,VLOOKUP在實務工作中普遍用於依條件查找,雖然很方便,但也有些先天限制,例如只能以搜尋表格中第一欄作為條件,在特殊場合需要多條件查找時,便可以運用這節範例類似的SUMPRODUCT函數公式。

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

博客來網路書店網址:

Excel應收帳款逾期管控表:COUNTIF與INDIRECT函數自動排序 INDIRECT函數 第8張