贊贊小屋

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

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

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

後面兩欄是逾期天數和逾期金額

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

=COUNTIF($E$2:E7,E7)

三、輸入另一個公式:「{=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」。

{=LARGE(IF($D$2:$D$9="Y",$E$2:$E$9),ROW()-1)}

四、接下來公式較為複雜:「=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位於第六列的意思。

=SUMPRODUCT(($E$2:$E$9=H4)*($G$2:$G$9=COUNTIF($H$2:H4,H4))*ROW($E$2:$E$9))

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

=IFERROR(INDIRECT("B"&$J2),"")

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

呈現出來的報表,已經依照逾期天數排序

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

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1