excel 提取重复项 多种方法实操详解
在 excel 提取重复项,就是从一列或者多列数据里面,把出现次数大于等于两次的数据单独筛选出来,日常做名单核对订单核对客户台账的时候经常会用到这个需求,excel 里面实现这个操作有好几种思路,可以用条件格式先标记重复值,也可以用函数公式判断,还可以借助高级筛选以及数据透视表来提取,不同方法适合的数据规模不一样,小数据量优先函数公式,大批量数据推荐数据透视表,很多新手容易混淆提取重复项和删除重复项,删除重复项是只保留一条唯一值,提取重复项是专门把重复存在的数据找出来,这个核心区别一定要分清,如果是多列联合判断重复,需要把多列条件合并再做判断,不能只单独判断某一列。
方法对比表格
| 方法 | 特点 | 适用场景 |
|---|---|---|
| COUNTIF 函数 | 灵活可以输出重复次数,公式可下拉 | 单列数据,需要显示重复次数 |
| 条件格式 | 只做标记高亮,不会单独提取到新区域 | 快速肉眼识别重复项 |
| 高级筛选 | 原生菜单操作,不需要写公式 | 快速提取重复记录 |
| 数据透视表 | 自动统计频次,适合大批量数据 | 上万行大数据统计提取 |
详细操作步骤
方法一 COUNTIF 函数提取重复项
1 假设原始数据在 A 列,A2 开始是数据,在旁边辅助列 B2 输入公式 = COUNTIF (A:A,A2)
2 这个公式含义是统计 A 列里面,当前单元格这个内容一共出现多少次,按下回车,然后鼠标拖动填充柄向下填充整列
3 辅助列数值大于等于 2,就代表这条数据属于重复项
4 选中全部数据区域,打开筛选,对辅助列设置筛选条件大于等于 2,筛选出来的结果就是 excel 提取重复项的记录,可以复制到其他空白区域保存
5 如果想要直接标记重复文本,公式可以改成 = IF (COUNTIF (A:A,A2)>2,"重复","唯一")
方法二 数据透视表提取重复项
1 选中原始全部数据区域,点击顶部插入选项卡,选择数据透视表,指定存放位置
2 把原始字段拖进行区域,再把同一个字段拖到值区域,值汇总方式设置为计数
3 透视表会自动统计每一条数据出现的次数
4 对计数列添加筛选,筛选计数大于等于 2,剩下的数据就是所有重复项
5 这个方法优势是大数据运行稳定,不会因为公式过多造成表格卡顿
方法三 高级筛选提取重复项
1 先在空白单元格建立条件区域,条件需要提前搭配 COUNTIF 判断重复,适合有一定基础用户
2 点击数据选项卡,打开高级筛选,选择将筛选结果复制到其他位置
3 设置列表区域,条件区域,复制到的目标单元格,确认执行筛选
4 筛选完成之后,就单独得到重复记录
进阶要点
如果是多列同时满足才算重复,比如姓名和订单号两列同时一样才判定重复,COUNTIF 要改成 COUNTIFS 多条件计数公式,公式 = COUNTIFS (A:A,A2,B:B,B2)。注意 COUNTIF 存在通配符识别问题,如果单元格内容本身带有星号问号,统计的时候会识别成通配符,造成统计结果错误,这种情况建议改用 SUMPRODUCT 函数。如果原始数据后续新增内容,COUNTIF 整列引用可以自动更新,但是筛选结果不会自动刷新,需要重新执行筛选。条件格式只能高亮,不会自动导出单独列表,想要单独保存重复记录,仍然需要搭配筛选复制操作。
【常见问题】
问题 1 excel 提取重复项为什么 COUNTIF 统计结果不准确
回答 1 excel 提取重复项使用 COUNTIF 的时候,如果单元格内容包含星号问号这类通配符,函数会识别成通配符匹配规则,统计数值出错,这种情况换成 SUMPRODUCT 函数来做计数判断。
问题 2 excel 提取重复项可以多列联合判断重复吗
回答 2 excel 提取重复项支持多列联合判断,把 COUNTIF 替换成 COUNTIFS 多条件计数函数,多列全部匹配一致才判定为重复项,适合订单号姓名组合这类复合重复判断。
问题 3 excel 提取重复项之后能不能自动更新新增数据
回答 3 excel 提取重复项使用整列引用的 COUNTIF 公式,公式本身可以识别新增数据,但是筛选出来的结果不会自动追加,新增之后需要重新执行筛选,也可以改成动态区域公式实现自动更新。
【免责申明】
本内容仅为 excel 函数与数据处理操作教学参考,不同版本 excel 函数运算逻辑存在细微差异,统计结果请自行核对校验,本内容不对业务统计决策承担相关责任。
