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))}


有系統、全面性的學習Excel職場應用,:會計人的Excel小教室PressPlay頻道


當前文章分類:
VLOOKUP