Excel如何用index函數自動排序資料 - 會計

Excel如何用index函數自動排序資料

網誌圖文版：

http://www.b88104069.com/archives/4070

會計工作實務上，排序是很常用工具。無論是審計查帳或者公司財務分析，
我們強調重大性原則，交易金額越大的交易越值得重視，金額太小的可以忽
略。除了金額之外，也經常就日期作排序，例如逾期帳款，我們希望將逾期
越久的排在越上面，因為這是必須重點追踪的異常項目。針對這個需求，會
計人應該很習慣使用Excel的排序功能，以下要介紹的，是如何用index函數
達到自動排序：

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

二、在後面新增一欄，輸入：「=COUNTIF（$E$2:E7,E7）」這個公式能找出
有相同逾期天數的帳款。並無逾期的，顯示為零，60天都有三筆帳款逾期，
依序顯示為「1」、「2」、「3」。

三、輸入另一個公式：「｛
=LARGE(IF($D$2:$D$9="Y",$E$2:$E$9),ROW()-1)｝」LARGE函數能找出某個
範圍第幾大的值，參數一是範圍，這裡弄了一個IF陣列函數，「$D$2：$D$9
」有標註「Y」，取「$E$2：$E$9」相對的值，所以LARGE的範圍便是「
30,30,60,60,60,90」。參數二表示第幾大，這個使用：「ROW（）－1」，配
合一開始為第二列拉下來，剛好是從1遞增的數列（1,2,3,……）。公式結果
如圖所示，有一點必須特別說明，因為這是陣列公式，記得最後在資料編輯
列輸入組合鍵：「Crtl＋Shift＋Enter」。

四、接下來公式較為複雜：「
=SUMPRODUCT(($E$2:$E$9=H4)*($G$2:$G$9=COUNTIF($H$2:H4,H4))*ROW($E$
2:$E$9))」。可以將Sumproduct函數視為多條件的Vlookup，以I4為例，條件
一是E欄中等於H4，E4、E6、E7都合乎資格，條件二是取出現次數相同的帳款
，COUNTIF（$H$2：H4,H4）的值是2，所以是取G2到G9中等於2的儲存格，條
件一和條件二合起來，便是E6儲存格，而Sumproduct要取的是ROW（$E$2：
$E$9），也就是列數，因此I4的計算結果是「6」，表示E6位於第六列的意思
。

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

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

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


延伸閱讀：

Excel如何以名稱及Index函數整理工作表清單：

http://www.b88104069.com/archives/4060

Excel如何以Index函數查找傳票摘要：

http://www.b88104069.com/archives/3307

Excel如何sumif自動加總應收帳款：

http://www.b88104069.com/archives/3735

--


周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室： https://www.facebook.com/acctexcel


--