Excel如何vlookup合併欄位排序,搜尋應收帳款最晚收款日




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

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

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

應收帳款明細表

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

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

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

=CONCATENATE(A2,B2)

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

=VLOOKUP(C2,明細!$C$2:$F$9,4,0)

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

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

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

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

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

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

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

=VLOOKUP(C2,明細!$D$2:$G$9,4,0)

九、最後來個彩蛋。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」,這是陣列公式基本用法。以後有機會,再來寫些關於陣列的文章。

{=MAX(IF(明細3!$A$2:$A$9='9'!A2,明細3!$B$2:$B$9='9'!B2)*(明細3!$E$2:$E$9))}




當前文章延伸閱讀:
VLOOKUP