Excel vlookup排序用法:應收帳款符合條件傳回最後一筆

Excel Vlookup用法是符合條件傳回特定值,而且它會傳回第一筆,有時候需要搜尋資料後傳回最後一筆。本文以應收帳款最晚收款日為例,介紹Vlookup排序的組合用法。

會計上的應收帳款明細帳,都有一個應收款日,有時我們需要根據明細表,彙總各個地區客戶的最晚收款日。Excel操作上,直覺會想到用vlookup去串,可是首先有個問題,vlookup只能依照一個特定的欄位資料去查找,如果是有一組(兩個以上)的欄位,例如像是(地區,客戶)這樣的組合,vlookup不太方便。而即使查找條件解決了,接下來還有個問題:有時候在原始資料中,相同的查找條件有好幾筆,而我們要的,不一定是第一筆,vlookup卻只會查找相對資料的第一筆。

例如,在應收帳款明細表裡,相同地區客戶,有很多筆帳款,但我們只想要最晚一筆的應收款日,在這種情況,簡單套用vlookup沒辦法達成預期效果。以下,想藉由實務上遇到的案例,介紹如何巧妙運用vlookup:

一、應收帳款明細表

應收帳款明細表,有「地區、客戶、帳款(編號)、應收金額、應收款日」等欄位。這是一個很適合Excel處理的報表資料,如果ERP系統跑出來或是查核帳客戶前端部門給的資料,不是這樣的形式,建議都先「修理」一下,方便接續作資料整理彙總。

應收帳款明細表

二、客戶最晚收款日

如圖所示,針對應收帳款明細表,想整理出一份清單,顯示各個地區客戶最晚的收款日。

顯示各個地區客戶最晚的收款日

三、Excel Vlookup用法

遇到這種情形,想要在Excel符合條件傳回特定值,第一個想到的是vlookup緃向查找函數,這個函數功能是同一列資料中,可以查找某欄位符合特定值的某一列中,傳回同一列相對應其它特定欄位的資料。這麼講相當艱澀,但只要有實際用過vlookup的,都會知道其實很容易理解,而且很好用。不過如同在這個例子所看到的,vlookup只能以某一欄作為查找條件,所以遇到需要兩個以上欄位作為組合條件時,必須先把各個欄位拼裝起來,中規中矩的公式為「=CONCATENATE(A2,B2)」,簡單易懂的公式為「=A2&B2」。

Excel vlookup排序用法:應收帳款符合條件傳回最後一筆 1

四、Excel符合條件傳回

解決了查找條件的問題,套用vlookup輸入公式:「=VLOOKUP(C2,明細!$C$2:$F$9,4,0)」很快會發現帶出來資料不是我們想要的,因為vlookup還有個特性,它只會傳回符合條件的第一筆資料,而我們想要的,不僅僅是符合「地區+客戶」的收款,還要是「最晚收款日」。

Excel vlookup排序用法:應收帳款符合條件傳回最後一筆 3

五、Excel排序工具

理解了問題的癥結點,直接的解決方法隨之而來。既然vlookup只會傳回第一筆資料,那也許可以先整理原始資料,讓我們想要的資料,都先往上排,問題迎刃而解。以文章範例而言,要找最晚的收款日,那就先把資料「排序」,收款日越晚的,排在越上面,不就OK了!到Excel上方功能模塊,「常用」、「排序與篩選」、「自訂排序」。

那就先把資料「排序」,收款日越晚的,排在越上面

六、最新到最舊排序

在跳出來的功能視窗中,依照我們需要,排序方式選擇「應收款日」,排序對象維持預設的「值」,順序改成「最新到最舊」。

排序方式選擇「應收款日」,排序對象維持預設的「值」

七、最後一筆排序

按下排序功能視窗的「確定」之後,看看報表,已經變成是依照應收款日排序,最晚的在最上面了。

看看報表,已經變成是依照應收款日排序

八、Vlookup最後一筆

再次輸入公式:「=VLOOKUP(C2,明細!$D$2:$G$9,4,0)」,噹噹噹,不就它了嗎!

Excel vlookup排序用法:應收帳款符合條件傳回最後一筆 5

九、Excel 陣列公式

最後來個彩蛋。Excel用CONCATENATE、用vlookup、用排序,都是Excel初階者思惟(說我自己啦),中階者會弄陣列,高階者會開發VBA。以本篇文章案例而言,高高手一看,不就是個陣列公式:「{=MAX(IF(明細3!$A$2:$A$9=’9′!A2,明細3!$B$2:$B$9=’9′!B2)*(明細3!$E$2:$E$9))}」,一次全套解決不囉嗦,有興趣讀者可以試試,注意到先輸入:「=MAX(IF(明細3!$A$2:$A$9=’9′!A2,明細3!$B$2:$B$9=’9′!B2)*(明細3!$E$2:$E$9))」然後再按「Ctrl+Shift+Enter」,這是陣列公式基本用法。以後有機會,再來寫些關於陣列的文章。

Excel vlookup排序用法:應收帳款符合條件傳回最後一筆 7

簡單組合完成複雜任務

本篇文章先介紹Vlookup一般用法,但發現到沒辦法符合實務範例的需求,這是Vlookup本身搜尋資料後符合條件傳回第一筆的特性,為了突破限制,先將原始資料排序,達到反置Vlookup排序的效果。在文章後面同時有補充陣列公式用法,不過很明顯如果能夠善用基本函數指令,稍加組合運用,山不轉路轉,最終還是能完成複雜任務。

加強學習:贊贊小屋Vlookup文章