Excel删除重复项与给它们标记颜色，查找筛选两列或整行都相同的重复数据

在 excel 中，如果要给重复项（重复数据）标记颜色，可以用条件格式；如果要删除重复数据，既可以用Excel删除重复项功能又可以用高级筛选；如果要查找（筛选）两列或以上同时相同的数据与一行都相同的数据，需要用公式。

Excel删除重复项功能既可以只删除一列的重复项又可以把整行重复的都删除。而要查找两列或以上甚至一行都相同的数据，需用几个函数组合实现，例如用 IF + Sum + Value 函数组合实现。

一、Excel重复项标记颜色

（一）用“突出显示单元格规则”标记

1、单击列号 A，选中第一列，选择“开始”选项卡，单击“条件格式”，在弹出的菜单中依次选择“突出显示单元格规则”→ 重复值，打开“重复值”窗口，单击“确定”，则 A 列所有重复值用“浅红填充色深红色文本”标记，操作过程步骤，如图1所示：

图1

2、如果要用另一种颜色填充，可单击“重复值”窗口右边的下拉列表框，选择一种颜色；如果没有满足要求的颜色，选择最后的“自定义格式”，打开“设置单元格格式”窗口，选择“填充”选项卡，选择一种颜色。

提示：打开“重复值”窗口，也可以用快捷键 Alt + H + L + H + D，按键方法为：按住 Alt，分别按一次 H、L、H 和 D。

（二）用“仅对唯一值和重复值设置格式”标记

1、单击列号 A，选中 A 列，当前选项卡为“开始”，单击“条件格式”，在弹出的菜单中选择“新建规则”，打开“新建格式规则”窗口，选择“仅对唯一值和重复值设置格式”，“全部设置格式”选择“重复”，单击“格式”，打开“设置单元格格式”窗口，选择“填充”选项卡，选择一种颜色（如“绿色”），单击“确定”两次，则 A 列用绿色标出所有重复项；操作过程如图2所示：

图2

2、打开“新建格式规则”窗口也可以用快捷键 Alt + H + L + N，按键方法为：按住 Alt，依次按一次 H、L 和 N。

提示：这个标记方法只能标记一列中的重复数据，不能标记一行都重复的数据。

（三）去掉标记颜色的方法

1、选中 A 列，当前选项卡为“开始”，单击“条件格式”，在弹出的菜单中依次选择“清除规则”→ 清除所选单元格的规则，则 A 列所有颜色标记被清除，操作过程步骤，如图3所示：

图3

2、直接按快捷键 Alt+ H + L + C + S 也可以清除所选单元格的标记颜色，按键方法为：按住 Alt，分别按一次 H、L、C 和 S。如果要清除整个表格的颜色标记，可以按快捷键  Alt+ H + L + C + E，按键方法与上面一样。

二、Excel删除重复项，合并重复数据

（一）用“删除重复项”删除

1、删除一行的重复项。选中表格其中一个单元格，选择“数据”选项卡，单击“删除重复项”，打开“删除重复项”窗口，列出有三个字段，勾选的表示把该字段计入重复项，保持勾选表格所有字段以把重复行都删除，单击“确定”，弹出一个提示两个重复项被删除小窗口，单击“确定”，则重复的两行（第 8 和 11 行）都被删除，操作过程步骤，如图4所示：

图4

2、只删除一列的重复项。单击列号 A 选中 A 列，按住 Alt，依次按 A 和 M，打开“删除重复项警告”窗口，选择“以当前选区域排序”，单击“删除重复项”，打开“删除重复项”窗口，单击“确定”，在弹出的提示已删除重复项窗口中单击“确定”，则只有 A 列的重复项被删除，与 A 列对应的其它列数据并没有被删除；操作过程步骤，如图5所示：

图5

提示：如果选择“扩展选定区域”，则删除一行的重复项，相当于图4的操作。

3、以两列计重复项，删除一行的重复项。选中表格其中一个单元格，按住 Alt，分别按一次 A 和 M，打开“删除重复项”窗口，单击“姓名”不勾选它，只勾选“部门和职务”，单击“确定”，弹出 4 个重复项被删除提示小窗口，单击“确定”，则 B 和 C 两列都重复的项被删除，分别是第 3 和 第 8 行与第 6 和第 9 行；操作过程步骤，如图6所示：

图6

提示：如果同时勾选两个以上字段，则只有勾选的字段相同才算相同，并且是删除一行而不是仅删除所选字段。

（二）用“高级筛选”删除

1、单击列号 A 选中 A 列，并按住左键拖到 C 列，选中表格，选择“数据”选项卡，单击“排序和筛选”右上角的“高级”，打开“高级筛选”窗口，单击“选择不重复的记录”勾选它，单击“确定”，则重复项（重复行）被删除；操作过程步骤，如图7所示：

图7

2、打开“高级筛选”窗口也可以按快捷键 Alt + A + Q，按键方法为：按住 Alt，依次按一次 A 和 Q。

三、Excel用公式查找重复项（筛选相同数据）

（一）仅查找一列的重复项

1、假如要查找 A 列员工“姓名”是否有重复项。双击 D2 单击格，把公式 =IF(COUNTIF(A$2:A$11,A2)>1,"重复","") 复制到 D2，按回车，返回空；选中 D2，把鼠标移到 D2 右下角的单元格填充柄上，鼠标变为黑色加号后，双击左键，则把剩余行都标记出是否重复；操作过程步骤，如图8所示：

图8

2、公式 =IF(COUNTIF(A$2:A$11,A2)>1,"重复","") 说明：

A、A2 表示对列和行都是相对引用，往下拖时，A2 会自动变为 A3、A4、……；往右拖时，A2 会自动变为 B2、C2、……。

B、A$2 表示对列相对引用、对行绝对引用，往下拖时，A2 不会变为 A3、A4、……；但往右拖时，A2 会自动变为 B2、C2、……；A$11 与A$2 是一个意思。A$2:A$11 作用是：无论怎么往下拖，始终确保在 A2:A11 中统计。

C、COUNTIF(A$2:A$11,A2)>1 为 If 的条件，当公式在 D2 时，统计 A2 在 A2 至 A11 中的个数；当公式在 D3 时，它变为 COUNTIF(A$2:A$11,A3)>1，即统计 A3 在 A2 到 A11 中的个数，其它的以此类推。

D、由于 COUNTIF(A$2:A$11,A2) 的统计结果为 1，1>1 不成立，因此返回 If 的第三个参数 ""，即返回空。而 COUNTIF(A$2:A$11,A3) 的统计结果为 2，2>1 成立，因此返回 If 的第二个参数，即返回“重复”。

提示：如果要统计两列或以上都相同的重复项，不能这么写公式 =IF(COUNTIFS(A$2:A$11,A2,B$2:B$11)>1,"重复","")，因为这样是统计每列中的重复项，而不是先统计出 A 列的重复项，再在 B 列统计与 A 列重复项对应的值。

　　（二）查找两列或整行同时相同的重复项（两列对应行或整行重复才算重复项）

1、先查找两列同时相同的重复项，例如：A3 与 A11 重复且 B3 与 B11 重复。双击 D2 单元格，把公式 =IF(SUM(--(A$2:A$11&B$2:B$11=A2&B2))>1,"重复","") 复制到 D2，按 Ctrl + Shift + 回车，返回空，双击 D2 的单元格填充柄，返回剩余行的重复标记结果；操作过程步骤，如图9所示：

图9

2、公式 =IF(SUM(--(A$2:A$11&B$2:B$11=A2&B2))>1,"重复","") 说明：

A、公式为数组公式，所以要按 Ctrl + Shift + 回车。

B、A$2:A$11 以数组形式返回 A2:A11 中的值，B$2:B$11 以数组形式返回 B2:B11 中的值。A$2:A$11&B$2:B$11 把 A2:A11 返回的值与B2:B11 返回的值连接起来，例如第一次返回 A2 和 B2，A2&B2 为"李秀丽财务部"，第二次返回 A3 和 B3,A3&B3 为"林语彤销售部"，其它的以此类推，最后返回一个 A 列与 B 列对应行合并的数组。

C、则 A$2:A$11&B$2:B$11=A2&B2 变为 {"李秀丽财务部";"林语彤销售部";"黄子辛销售部";"王青瑗行政部";"赵云祥财务部";"黄子辛销售部";"刘月芹行政部";"黄晨昊销售部";"林语彤销售部"}=A2&B2，接着，第一次取数组的第一个元素 "李秀丽财务部" 与 A2&B2（即"李秀丽财务部"）比较，由于它们相等，因此返回 True；第二次取数组的第二个元素 "林语彤销售部" 与 A2&B2，由于不相等，因此返回 False，其它的以此类推，最后返回 {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。

D、则 SUM(--(A$2:A$11&B$2:B$11=A2&B2)) 变为 SUM(--({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}))，进一步计算，把数组中的逻辑值转为数值并把它们相加，结果为 1，-- 作用是把文本或逻辑值转为数值以便计算，它相当于 Value 函数。Sum函数在这里用于统计两列对应行都相同的个数。

E、则公式变为 =IF(1>1,"重复","")，1>1 为 If 的条件，由于它不成立，所以返回 If 的第三个参数 ""（即空）。

3、查找整行都重复的重复项。只需把每列用 & 连接起来即可实现，公式可以这样写：=IF(SUM(--(A$2:A$11&B$2:B$11&C$2:C$11=A2&B2&C2))>1,"重复","")。

4、把上面的公式改 =SUM(--($A$1:$A$11&$B$1:$B$11&$C$1:$C$11=A1&B1&C1))>1，可以用颜色标记一行都重复的项，操作过程步骤，如图10所示：

图10

操作过程步骤说明：单击列号 A 选中 A 列，并拖到 C 列，选中表格，按住 Alt，依次按一次 H、L 和 N，打开“新建格式规则”窗口，选择“使用公式确定要设置格式的单元格”，把公式 =SUM(--($A$1:$A$11&$B$1:$B$11&$C$1:$C$11=A1&B1&C1))>1 复制到“为符合此公式的值设置格式”下的输入框中，单击“格式”，打开“设置单元格格式”窗口，选择“填充”选项卡，选择“绿色”，单击两次“确定”，则 A 列的重复项用绿色标记，双击 B8，输入 0，A8 和 A5 的标记颜色被去掉，说明只有一行中的所有字段相同才标记绿色。

提示：由于公式合并了每列对应行的数据，虽然只标记了 A 列的重复数据，但所标记的都是一行都重复的。

（三）重复项第一项标记 1，第二项后标记大于 1

1、同样以查找两列和整行同时相同的数据为例。双击 D2 单元格，把公式 =SUM(--($A$2:A2&$B$2:B2=A2&B2)) 复制到 D2，按回车，返回1，双击 D2 的单元格填充柄，则返回剩余行的统计结果，数值大于 1 的为重复项；操作过程步骤，如图11所示：

图11

2、公式 =SUM(--(A$2:A2&B$2:B2=A2&B2)) 说明：

A、A$2:A2 用于统计到当前行，当公式在 D2 时，A$2:A2 返回 A2，B$2:B2 返回 B2，A$2:A2&B$2:B2=A2&B2 变为 A2&B2=A2&B2，等式成立，返回 True，则公式变为 =SUM(--(TRUE))，进一步计算变为 =SUM(1)，因此结果返回 1。-- 的作用上面已经介绍。

B、当公式在 D3 时，公式变为 =SUM(--(A$2:A3&B$2:B3=A3&B3))，与上面“（二）查找两列或整行同时相同的重复项”的公式是一个意思。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。