数据核对总出问题？学会这样做，你比同事更早下班！（二）

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

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

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

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

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

01

核对产品上下架

提问：对比 7 月和 8 月的产品，查找哪些产品是 8 月新上架的？哪些是已经下架的？

该怎么做？

答疑人：玉霞助教

思路分析：

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

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

再核对 D 列的商品。

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

解决方法 ❶：

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

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

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

B2 单元格公式为：

=IF(COUNTIF($A$2:$A$14,D2)>0,'','新产品上架')

▲左右滑动查看

C2 单元格公式为：

=IF(COUNTIF($A$2:$A$14,D2)>0,'','新产品上架')

同理，E2 单元格公式为：

=IF(COUNTIF($A$2:$A$14,D2)>0,'','新产品上架')

▲左右滑动查看

F2 单元格公式为：

COUNTIF(A2:A14,D2)

解决方法 ❷：

构建辅助列「C 列」，用 VLOOKUP 函数查找；

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

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

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

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

C2 单元格公式为：

IFERROR(VLOOKUP(A2,$D$2:$D$14,1,0),'')

▲左右滑动查看

B2 单元格公式为：

IF(IFERROR(VLOOKUP(A2,$D$2:$D$14,1,0),'')='','已经下架','')

▲左右滑动查看

特别提示：函数的书写，符号均需为英文状态下格式；

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

02

核对两表

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

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

解决方法 ❶：

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

解决方法 ❷：

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

05

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

提问：想要标记重复值高亮，为什么出来的结果却是错的？

答疑人：黄群金 King 老师

原因分析：

会一点 Excel 的都知道。

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

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

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

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

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

解决方法：

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

❶ 选中两列数据。

❷ 新建条件格式规则。

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

❸ 添加规则公式。

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

▲左右滑动查看

公式包含两层：

计算当前单元格中数据。

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

COUNTIF($B$2:$C$11,'*'&B2&'*')>1

▲左右滑动查看

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

B2&'*'，用来代表包含 B2 的数据。

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

且当前单元格不能为空。

B2<>''

没想到吧？

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