与 30万 粉丝一起学Excel
VIP学员的问题,姓名跟金额两边都一样,要根据这2个条件核对数据,左边显示一样,右边显示对应的序号。
关于对账,卢子以前发过很多,但是数据源都是规范的,而这个却是不规范。一共有2个难点。
1.左边的姓名和地址并没有统一的分隔符号,右边的姓名存在空格。
右边的存在空格,这个比较容易处理,直接用查找替换或者用函数替换掉。
=SUBSTITUTE(H2," ",)
左边的没有统一的分隔符号,就不能借助MID+FIND处理。不过却可以根据LOOKUP+FIND查找右边的姓名,间接提取出来。
=LOOKUP(1,0/FIND($K$2:$K$7,B2),$K$2:$K$7)
语法:
=LOOKUP(1,0/FIND(姓名的区域,地址和姓名的单元格
),姓名的区域)
找不到就显示错误值,这个不方便后期设置公式,可以再嵌套IFERROR让错误值显示空白。
=IFERROR(LOOKUP(1,0/FIND($K$2:$K$7,B2),$K$2:$K$7),"")
2.右边的金额是大写的,跟左边不一样。
还好,VIP学员说金额不存在小数点,都是整数。整数要统一格式很简单,一个TEXT就能处理。
=TEXT(C2,"[DBNum2]")
TEXT的第2参数,其实是通过设置单元格格式得到的,不用刻意去记。特殊,中文大写数字。
点自定义就看到代码,复制前面部分就行,当然全部复制也可以。
3.整理完,开始对账。
格式统一,现在要对账就很简单了。
左边是核对是否一样,用COUNTIFS多条件判断,次数为1就是一样。
=IF(COUNTIFS(K:K,E2,I:I,F2)=1,"一样","")
右边是要查找对应序号,用LOOKUP多条件查找。
=IFERROR(LOOKUP(1,0/((K2=$E$2:$E$7)*(I2=$F$2:$F$7)),$A$2:$A$7),"")
实际卢子在帮学员的时候是没用辅助列,不过太难了,怕你看晕,于是用辅助列进行简化。
最后,做表一定要规范,要不然写公式写到晕。
|
有话要说...