• 欢迎访问速搜资源吧,如果在网站上找不到你需要的资源,可以在留言板上留言,管理员会尽量满足你!

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

Excel技巧 admin 2年前 (2019-08-03) 679次浏览 已收录 0个评论

日常工作中,经常需要对数据进行核对。

比如说条件格式、行内容差异、高级筛选和函数公式法等等。

可是,即使是一名老司机,也会偶尔栽倒坑里。

不信,接下来给你看我的血泪史。

01

第一坑之高级筛选

下图,上半部分是已经报名参加活动的人员清单,下部分是全部人员清单。

现在,想知道还有哪些人没有报名。

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

解决这个问题的方法有很多,如果使用高级筛选的话,方法如下:

在【数据】选项卡下选择【高级】,在弹出的对话框中,设置 A6:C16 为列表区域,A1:A4 为条件区域。

点击【确认】后,选中筛选出的结果,填充颜色,然后取消筛选。

这时,没有底色填充的,就是还没有报名的人员。

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

最后,结果如下:

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

可是奇怪,「刘文浩」没有报名,怎么也填充了底色?

哪一步操作错了?

在看答案之前,建议你先思考一分钟。

好吧,假装已经过去了一分钟。

此例中,结果出错的原因,是高级筛选条件区域,默认是模糊匹配的,只要是包含条件中的关键字,就会被筛选出来。

当高级筛选「刘文」的时候,「刘文」和「刘文浩」都会被筛选出来。

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

所以,这个例子,使用高级筛选并不合适,建议使用函数公式 COUNTIF 来操作。

添加辅助列,在 D7 单元格输入公式:

=COUNTIF($A$2:$A$4,A7)

然后,向下填充,筛选结果为 0 的,就是还没有报名的人员。

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

02

第二坑之 COUNTIF

刚刚,我们用 COUNTIF 函数,填了一个高级筛选的坑,那么,COUNTIF 本身就没有坑了吗?

我们接下来就继续看。

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

上图中,是某公司员工的身份证号信息,想知道是否有重复录入。

如果选择使用 COUNTIF 函数,统计出现的次数,来判断是否重复的话,理论上也是可行的。

在 B2 单元格输入公式:

=COUNTIF($A$2:$A$10,A2)

然后,向下填充,当结果为 2 的时候,即重复录入。

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

可是,怎么又错了?

上图中标黄的单元格,明明是两个不同的身份证号!

这是因为,Excel 只识别前 15 位数字,当位数大于 15 的时候,后面的位数会默认为 0。

可是,这里的身份证号是文本格式,怎么也出错了?

这里只能说,Excel 聪明过头了,有时候会将文本型数字,当做数值来计算。

那么怎么解决呢?

只需要,让身份证号连接通配符「*」就可以强制告诉 Excel,我是文本,你不要转换我。

通配符只能对文本型数据进行统计,其他类型的数据,使用通配符无效。

「*」星号在 Excel 中是通配符,表示任意的数量字符。

比如说「刘*」,就表示任意刘开头的人员姓名。

【Excel技巧】避开Excel这些坑,数据核对效率翻倍

(PS:COUNTIF 与 COUNTIFS、SUMIF、SUMIFS 都有这种问题,并且解决方法也和 COUNTIFS 一样)

以上,就是我曾经的血泪史,你中招了木有?

另外,再写这篇文章的时候,我还发现:

当使用【条件格式】中的【重复值】,来查找是否有重复的时候,对 15 位以上数字也是无效的。

写这篇文章的主要目的,不是告诉大家数据核对有哪些方法,而是希望,能避免大家掉坑。

或者,当出现错误的时候,知道错在哪里。


速搜资源网 , 版权所有丨如未注明 , 均为原创丨转载请注明原文链接:【Excel技巧】避开Excel这些坑,数据核对效率翻倍
喜欢 (0)
[361009623@qq.com]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址