数据核对总出问题?学会这样做,你比同事更早下班!(二)

私信回复关键词【UP】,获取VLOOKUP函数用法教程合集,一看就会!


大家好,我是秋小 E~这里是秋叶 Excel 的【问答精华】专栏!


问题主要来自秋叶 Excel 的同学,回答由讲师、助教共同完成;


每周一期,每期主题不同,希望能更有针对性地解决大家的共性问题。


今天的主题是——数据核对。


来看看这些小问题,你有没有遇到过,当时又是怎么解决的?


01

核对产品上下架


提问:对比 7 月和 8 月的产品,查找哪些产品是 8 月新上架的?哪些是已经下架的?


该怎么做?


数据核对总出问题?学会这样做,你比同事更早下班!(二)


答疑人:玉霞助教


思路分析:


先核对 A 列的商品是否在 D 列。


如果在,就显示为空,否则显示为已经下架。


再核对 D 列的商品。


如果不在 A 列,说明是 8 月份新上架的商品,显示「上架」,否则为空。


解决方法 ❶:


构建辅助列「C 列」,用 COUNTIF 函数条件计数,再嵌套于 IF 函数显示核对结果。


对于 7 月商品,辅助列「C 列」数值如果大于 0,显示为空,否则显示为已经下架;


对于 8 月商品,辅助列结果如果等于 0,显示为新产品上架,否则显示为空。


B2 单元格公式为:

<code>=IF(COUNTIF($A$2:$A$14,D2)>0,"","新产品上架")/<code>

▲左右滑动查看


C2 单元格公式为:

<code>=IF(COUNTIF($A$2:$A$14,D2)>0,"","新产品上架")/<code>


数据核对总出问题?学会这样做,你比同事更早下班!(二)


同理,E2 单元格公式为:

<code>=IF(COUNTIF($A$2:$A$14,D2)>0,"","新产品上架")/<code>

▲左右滑动查看


F2 单元格公式为:

<code>COUNTIF(A2:A14,D2)/<code>


解决方法 ❷:


构建辅助列「C 列」,用 VLOOKUP 函数查找;


因部分数据查找不到,会显示错误值#N/A。


所以,嵌套 IFERROR 函数,设定返回错误值#N/A 的,显示为空,再嵌套 IF 函数显示核对结果


和「解决方法❶」一样,对于 7 月商品,辅助列如果为空,显示为已下架,否则显示为空;


对于 8 月商品,辅助列结果为空,显示为新产品上架,否则显示为空。


C2 单元格公式为:

<code>IFERROR(VLOOKUP(A2,$D$2:$D$14,1,0),"")/<code>

▲左右滑动查看


B2 单元格公式为:

<code>IF(IFERROR(VLOOKUP(A2,$D$2:$D$14,1,0),"")="","已经下架","")/<code>

▲左右滑动查看


数据核对总出问题?学会这样做,你比同事更早下班!(二)


特别提示:函数的书写,符号均需为英文状态下格式;


VLOOKUP 函数的第二个参数,查找区域需加 $ 锁定。


02

核对两表


提问:请问查找两张独立表里的重复数据?


数据核对总出问题?学会这样做,你比同事更早下班!(二)


答疑人:拉登老师、玉霞助教


解决方法 ❶:


将两个表格合并到一起,然后用条件格式,快速标记重复值。


数据核对总出问题?学会这样做,你比同事更早下班!(二)


解决方法 ❷:


使用 VLOOKUP 函数,将两个表格相同的内容匹配到一起,查询结果为#N/A,代表是不重复的。


数据核对总出问题?学会这样做,你比同事更早下班!(二)


05

数据太长,重复值标记不对


提问:想要标记重复值高亮,为什么出来的结果却是错的?


数据核对总出问题?学会这样做,你比同事更早下班!(二)


答疑人:黄群金 King 老师


原因分析:


会一点 Excel 的都知道。


利用条件格式的重复值选项,可以突出显示重复的数据,只要点 2 下鼠标就可以快速完成。


可是,这个功能,在碰到超过 15 位的数字时会失效


例如,案例中的 18 位运单号,只要前面 15 位数一样,后面 3 位不管是什么,Excel 都把它当成重复数据了。


身份证号、银行账号一样会碰到这个问题。


因为超过 15 位,Excel 就会把它识别成天文数字,自动后面的数字。


解决方法:


解决方法也简单,需要用 Countif 函数来救场,突破条件格式的功能限制。


❶ 选中两列数据。


❷ 新建条件格式规则。


点击【开始】-【条件格式】,新建规则,选择最后一个规则类型「使用公式」。


❸ 添加规则公式。

<code>=AND(COUNTIF($B$2:$C$11,"*"&B2&"*")>1,B2<>"")/<code>

▲左右滑动查看


公式包含两层:


计算当前单元格中数据。


完整出现在整个区域中的次数,大于 1 次,则符合条件。

<code>COUNTIF($B$2:$C$11,"*"&B2&"*")>1/<code>

▲左右滑动查看


其中,*是通配符,表示任意字符。


B2&"*",用来代表包含 B2 的数据。


强行将当前单元格里的数据当成文本来计数,超过 1 个就代表有重复。


且当前单元格不能为空。

<code>B2<>""/<code>


数据核对总出问题?学会这样做,你比同事更早下班!(二)


没想到吧?


平时以为很简单的数据核对,居然还有这么多使用场景,解决办法也是多种多样!


私信回复关键词【UP】,获取VLOOKUP函数用法教程合集,一看就会!


分享到:


相關文章: