在 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 函数运算逻辑存在细微差异,统计结果请自行核对校验,本内容不对业务统计决策承担相关责任。