大多「表哥表妹」使用表格是知其然而不知其所以然,所以遇见问题的时候就手足无措。

如果能了解清楚表格及数据的本质是什么,很多问题都会迎刃而解。

最近有一位学员在核对数据的时候,碰到了数据查重时的问题。

excel在不同表格之间查重(数据查重出问题)(1)

她的需求是这样的:只要在两列数据中存在重复的运单号,就标红显示。

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

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

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

身份证号、银行账号一样会碰到这个问题。因为超过 15 位,Excel 就会把它识别成天文数字,自动后面的数字。

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

选中两列数据。

选定两列数据(B2:C11区域):

excel在不同表格之间查重(数据查重出问题)(2)

❷ 新建条件格式规则。

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

excel在不同表格之间查重(数据查重出问题)(3)

❸ 添加规则公式。

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

公式包含两层:

① 计算当前单元格中数据,完整出现在整个区域中的次数,大于 1 次则符合条件。

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

其中,*是通配符,表示任意字符,B2&"*",用来代表包含 B2 的数据。

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

② 且当前单元格不能为空。

B2<>""

函数公式用得好,可以给条件格式插上翅膀,简直可以为所欲为~

excel在不同表格之间查重(数据查重出问题)(4)

在 Excel 里超长数字真的是个麻烦事。

我有个做 HR 的同学,有一次着急忙慌的来问我,身份证号为什么变成了 xxxx 000 的「乱码」。

就是因为数字超过了 15 位,被当成了天文数字,必须先设置成文本格式,再录入数据。

更悲催的是,他是录了 200 多个身份证后才发现这个问题。结果后面 3 位数全部被 Excel 洗成了 0。

只能重新录入……

操作不规范,真的会加班到想哭!

excel在不同表格之间查重(数据查重出问题)(5)

如何设计一个标准规范的表格?

碰到一个难以描述的不规范表格,又能如何快速处理?

关注我哦,后面继续为大家解答!

,