当前位置:首页 > 教育 > 正文

对账遇到这2个难点,头都大了!

与 30万 粉丝一起学Excel


VIP学员的问题,姓名跟金额两边都一样,要根据这2个条件核对数据,左边显示一样,右边显示对应的序号。

对账遇到这2个难点,头都大了!


关于对账,卢子以前发过很多,但是数据源都是规范的,而这个却是不规范。一共有2个难点。

1.左边的姓名和地址并没有统一的分隔符号,右边的姓名存在空格。
右边的存在空格,这个比较容易处理,直接用查找替换或者用函数替换掉。
=SUBSTITUTE(H2," ",)

对账遇到这2个难点,头都大了!


左边的没有统一的分隔符号,就不能借助MID+FIND处理。不过却可以根据LOOKUP+FIND查找右边的姓名,间接提取出来。
=LOOKUP(1,0/FIND($K$2:$K$7,B2),$K$2:$K$7)

对账遇到这2个难点,头都大了!


语法:
=LOOKUP(1,0/FIND(姓名的区域,地址和姓名的单元格 ),姓名的区域) 找不到就显示错误值,这个不方便后期设置公式,可以再嵌套IFERROR让错误值显示空白。 =IFERROR(LOOKUP(1,0/FIND($K$2:$K$7,B2),$K$2:$K$7),"")

对账遇到这2个难点,头都大了!


2.右边的金额是大写的,跟左边不一样。
还好,VIP学员说金额不存在小数点,都是整数。整数要统一格式很简单,一个TEXT就能处理。
=TEXT(C2,"[DBNum2]")

对账遇到这2个难点,头都大了!


TEXT的第2参数,其实是通过设置单元格格式得到的,不用刻意去记。特殊,中文大写数字。

对账遇到这2个难点,头都大了!


点自定义就看到代码,复制前面部分就行,当然全部复制也可以。

对账遇到这2个难点,头都大了!


3.整理完,开始对账。

格式统一,现在要对账就很简单了。

左边是核对是否一样,用COUNTIFS多条件判断,次数为1就是一样。 =IF(COUNTIFS(K:K,E2,I:I,F2)=1,"一样","")

对账遇到这2个难点,头都大了!


右边是要查找对应序号,用LOOKUP多条件查找。
=IFERROR(LOOKUP(1,0/((K2=$E$2:$E$7)*(I2=$F$2:$F$7)),$A$2:$A$7),"")

对账遇到这2个难点,头都大了!


实际卢子在帮学员的时候是没用辅助列,不过太难了,怕你看晕,于是用辅助列进行简化。
最后,做表一定要规范,要不然写公式写到晕。

对账遇到这2个难点,头都大了!

你可能想看:

有话要说...

取消
扫码支持 支付码