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

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

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

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

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

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

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

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

四、COUNTIF函數的經典設計

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

五、意想不到的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。

六、INDIRECT神抓手與IFERROR偵錯器

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

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

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

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

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

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

每天學習，每天充電：Excel函數文章合集。