[算表] Excel如何将字段合并进行vlookup比对

楼主: moodyblue   2016-07-17 12:05:49
Excel如何将字段合并进行vlookup比对
网志图文版:
http://www.b88104069.com/archives/4089
先前写过一篇文章:《Excel如何vlookup两套帐本传票核对》,当时所设想
范例单纯,一笔传票对应一笔金额,所以是用传票号vlookup金额两相比对,
实务上所遇到情况,通常会较为复杂。举例而言,可能两套帐本传票金额都
一致,但是有借贷方相反的情形;可能一笔传票两项分录,其中一项没有问
题,但是另一项有差异;也有可能同样一张传票,这套帐本有两项分录,另
一套帐本却有三项分录,凡此种种,如果想用Excel公式一次查找出差异,必
须再进一步考量设计,以下分享作法:
一、A帐中的应收帐款明细分类帐。
二、B帐中的应收帐款明细分类帐。标黄色部份是有A帐有差异的传票分录,
在此想设计Excel公式,自动查找出差异项目。
三、考量借贷方金额应该有所区别,利用IF判断函数:“=IF(D7="借方
",E7,-E7)”,借方为正、贷方为负,如此符合会计一般惯例。
四、A帐中新增核对字段,直接以传票号vlookup带出B帐金额:“=VLOOKUP
(A9,B帐1!$A$3:$F$10,6,0)”,“#N/A”表示B帐无此传票。
五、公式稍加修饰:“=G4-IFERROR(VLOOKUP(B4,B帐1!$A$3:$F$10,6,0
),0)”。如此一来,资料查找不到,不会出现无法加总的“#N/A”,可
以直接显示两相比较的差额,并且只要公式结果并非为零,表示有问题,相
当一目了然。
标红色传票分录,两套帐本一致,但还是显示差额。这是因为vlookup函数特
性,它是在范围内找到的第一笔马上回传,所以永远只会传回条件相符的第
一笔资料。也就是税帐传票1407001的第一笔贷方金额-5,000,因此A帐减掉
B帐的计算结果是9,000(4,000-(-5,000))。
六、为了突破函数本身限制,有必要将字段合并,简单方法为“=A3&D3&
E3”,直接将“传票编号”、“借贷”、“金额”予以合并,或者利用相关
函数:“=CONCATENATE(A3,D3,E3)”,两者结果相同。
七、所有关键字段合并之后,再次输入查找公式:“=IFERROR(VLOOKUP(
G3,B帐!$G$3:$G$10,1,0),"B帐无")”。这里利用了IFERROR的特性,如果
查找不到,传回“B帐无”,使得公式计算结果更易于理解。
将字段合并,如果疯狂一点,把所有字段都合并,可以准确核对出两套传票
间的有无差异。但这么做,首先不符合会计以金额为主的核对原则;再者,
以这篇文章的范例来看,B帐传票1408001有三笔一模一样的分录,A帐传票
1408001只有两笔,像这种重复错误的情况,单纯vlookup查找函数没办法发
现。较为完整并且合乎会计思惟的作法,是将两套帐本依照传票号码,汇总
成枢纽分析表,然后vlookup比对两者的金额差异。从这里可以体会到,设计
Excel公式,了解资料特性和需求是最重要的第一步。
延伸阅读(vlookup核对查找):
Excel如何vlookup两套帐本传票核对:
http://www.b88104069.com/archives/1706
Excel两个报表如何以vlookup交叉核对:
http://www.b88104069.com/archives/4006
Excel如何资料剖析后vlookup查找:
http://www.b88104069.com/archives/1092

Links booklink

Contact Us: admin [ a t ] ucptt.com