Excel如何vlookup查找應收帳款最晚收款日 - 會計

Excel如何vlookup查找應收帳款最晚收款日

網誌圖文版：

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

會計上的應收帳款明細帳，都會有一個應收款日，有時候我們需要根據明細
表，彙總各個地區客戶的最晚收款日。首先會想到用vlookup去串，但是首先
有個問題，vlookup只能依照一個特定的欄位資料去查找，如果是有一組(兩
個以上)的欄位，例如像是(地區,客戶)這樣的組合，vlookup會比較麻煩。即
使查找條件解決了，接下來還有個問題，vlookup只會查找出相對資料的第一
筆，有時候在原始資料中，相同的查找條件有好幾筆，但我們要的不一定是
第一筆。例如在應收帳款明細表裡，相同的地區客戶，有很多筆帳款，但我
們只想要最晚一筆的應收款日，在這種情況，簡單套用vlookup沒辦法達成預
期效果。以下，想藉由實務上遇到的案例，分享如何巧妙運用vlookup：

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

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

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

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

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

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

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

八、再次輸入公式：「=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」，這是陣列公式基本用法，以後有機會，想寫
些關於陣列的分享文章。


延伸閱讀（應收帳款系列）：

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

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

Excel如何設定組別分類自動更新：

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

Excel如何格式化條件設定組別分類：

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

--


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

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


--