Excel如何将字段合并进行vlookup比对




先前写过一篇文章:《Excel如何vlookup两套账本传票核对》,当时所设想范例单纯,一笔传票对应一笔金额,所以是用传票号vlookup金额两相比对,实务上所遇到情况,通常会较为复杂。举例而言,可能两套账本传票金额都一致,但是有借贷方相反的情形;可能一笔传票两项分录,其中一项没有问题,但是另一项有差异;也有可能同样一张传票,这套账本有两项分录,另一套账本却有三项分录,凡此种种,如果想用Excel公式一次查找出差异,必须再进一步考虑设计,以下分享作法:

一、A帐中的应收帐款明细分类账。

A帐中的应收帐款明细分类账

二、B帐中的应收帐款明细分类账。标黄色部份是有A帐有差异的传票分录,在此想设计Excel公式,自动查找出差异项目。

标黄色部份是有A帐有差异的传票分录

三、考虑借贷方金额应该有所区别,利用IF判断函数:「=IF(D7=”借方”,E7,-E7)」,借方为正、贷方为负,如此符合会计一般惯例。

=IF(D7="借方",E7,-E7)

四、A帐中新增核对字段,直接以传票号vlookup带出B帐金额:「=VLOOKUP(A9,B帐1!$A$3:$F$10,6,0)」,「#N/A」表示B帐无此传票。

=VLOOKUP(A9,B帐1!$A$3:$F$10,6,0)

五、公式稍加修饰:「=G4-IFERROR(VLOOKUP(B4,B帐1!$A$3:$F$10,6,0),0)」。如此一来,资料查找不到,不会出现无法加总的「#N/A」,可以直接显示两相比较的差额,并且只要公式结果并非为零,表示有问题,相当一目了然。

=G4-IFERROR(VLOOKUP(B4,B帐1!$A$3:$F$10,6,0),0)

标红色传票分录,两套账本一致,但还是显示差额。这是因为vlookup函数特性,它是在范围内找到的第一笔马上回传,所以永远只会传回条件相符的第一笔资料。也就是税帐传票1407001的第一笔贷方金额-5,000,因此A帐减掉B帐的计算结果是9,000(4,000-(-5,000))。

六、为了突破函数本身限制,有必要将字段合并,简单方法为「=A3&D3&E3」,直接将「传票编号」、「借贷」、「金额」予以合并,或者利用相关函数:「=CONCATENATE(A3,D3,E3)」,两者结果相同。

=A3&D3&E3

七、所有关键字段合并之后,再次输入查找公式:「=IFERROR(VLOOKUP(G3,B帐!$G$3:$G$10,1,0),”B帐无”)」。这里利用了IFERROR的特性,如果查找不到,传回「B帐无」,使得公式计算结果更易于理解。

=IFERROR(VLOOKUP(G3,B帐!$G$3:$G$10,1,0),"B帐无")

将字段合并,如果疯狂一点,把所有字段都合并,可以准确核对出两套传票间的有无差异。但这么做,首先不符合会计以金额为主的核对原则;再者,以这篇文章的范例来看,B帐传票1408001有三笔一模一样的分录,A帐传票1408001只有两笔,像这种重复错误的情况,单纯vlookup查找函数没办法发现。较为完整并且合乎会计思惟的作法,是将两套账本依照传票号码,汇总成数据透视表,然后vlookup比对两者的金额差异。从这里可以体会到,设计Excel公式,了解数据特性和需求是最重要的第一步。


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


當前文章分類:
VLOOKUP