Excel处理重复数据的5种方法【尖椒学院】

麦田守望者 · 2016-06-09 10:00 · office教程学习网
摘要:重复数据的处理是数据处理中很典型的工作。许多人往往对之望而却步,希望学习复杂的技巧。其实,对他们的处理都是一些最基础功能的应用,条件格式,数据有效性,删除重复项是基本功能,数据透视表也只是用到了其中最简单地用法。

在Excel中,我们经常要处理数据清单,例如,员工档案,产品明细。这种数据中有可能存在重复的数据,需要进行处理。基于不同的情况,对重复数据的处理是不同的。一般来说,我们可以把实际工作中对重复数据的处理归结为以下五种情况:

1. 识别重复数据(或者非重复数据)

2. 删除重复数据(每条数据保留一条)

3. 挑出不重复列表

4. 计数(不重复数据的个数)

5. 阻止重复数据的产生

我们分别来介绍如何处理这些情况。

1. 识别重复数据(或者非重复数据)

例如,在下面的表格中,我们记录了超市中所有销售的产品。

0955525002-0.png

我们需要基于这个数据进行后续的数据分析。这就要求这个数据中每个数据只能出现一次(唯一性)。但是,仅仅是凭眼睛观察我们也可以发现里面的数据不唯一。这时,我们需要找出其中重复的数据,然后有针对行的进行分析,看看是什么原因导致了这些数据的重复。

这种需求就是重复数据的识别。这里就用到了countif函数。

在物品名称列后面添加一个辅助列,然后再单元格C3中数据公式:

=COUNTIF($B$3:$B$47,B3)

如下图:

0955525C9-1.png

一定要注意其中的相对引用和绝对引用。

然后通过鼠标拖拽填充整个辅助列对应区域,将得到如下结果

0955526450-2.png

容易发现,辅助列中值为1的对应的是非重复数据,大于1的对应的是重复数据。

理解了这个方式,我们还可以用可视化的方法使所有重复数据变色显示,这样我们可以直接在表中找到这个数据。

方法是用条件格式。方法如下:

首先选中整个物品名称的数据,$B$3:$B$47。然后选择“开始”——>“条件格式”——>“新建规则”,在新建规则对话框中,选择“使用公式确定要设置格式的单元格”,然后在“为符合此公示的值设置格式”框中输入公式

=countif($B$3:$B$47,B3)>1

如下图:

0955523259-3.png

点击确定后,你将得到如下的结果

09555221G-4.png

可以看到,所有的重复数据都一目了然。

2. 删除重复数据

有时所有重复的数据是我们不需要的,我们需要删除重复数据,每条数据只保留一条。这时,最简单的方法就是使用Excel自带功能“删除重复项”。方法如下:

09555221A-5.png

鼠标选中任意产品,然后点击“数据”菜单中的工具“删除重复项”

得到如下对话框

0955523C9-6.png

点击确定,Excel会删除掉所有重复数据(只保留一条),并提示如下信息

095552O46-7.png

3. 挑出不重复列表

在2的处理中,我们实际上得到了不重复列表。不过与此同时,我们删掉了其他的数据。但有时候,我们需要得到不重复列表,同时希望保留其他的信息。例如,在如下图所示的数据中,我们的原始数据是销售记录,我们希望得到所有产品的列表。

0955524b4-8.png

我们就不能直接使用删除重复项得到不重复列表。

0955525O4-9.png

这时,最简单的方法就是数据透视表

我们在源数据中插入数据透视表,然后把“物品名称”放在行字段上,就可以得到一个不重复列表了

4. 不重复数据的计数

有时,我们不需要不重复列表,只希望得到所有的数据中有多少不重复的数据。例如,在上面的产品销售清单中,我们只希望知道共有多少种产品在销售。

这时,我们可以使用函数来解决。

还以3中的数据为例子,我们在数量后添加一个辅助列,并且在单元格E3中输入公式:

=1/COUNTIF($B$3:$B$47,B3)

通过鼠标拖拽,你可以得到如下图的结果

095552KX-10.png

在辅助列1的最后,输入公式

=sum(E3:E47)

可以得到结果25,即数据中有25种不同的产品在销售。

5. 阻止重复数据的产生

尽管有各种方法,重复数据处理起来还是意见麻烦事。有时,我们希望在数据输入时就尽量避免重复数据的产生,这时,我们就可以用到数据有效性了(在最近几个版本中,这个功能叫做数据验证)。

方法如下:

选中单元格B3:B27,然后依次点击“数据”——> “数据验证”,出现数据验证对话框,在其中的”允许“和“公式”处,分别按照下图输入:

0955523543-11.png

点击确定后,数据有效性生效,你在其中输入任何一个重复的产品,都会得到如下的提示信息

095552NH-12.png

总结

重复数据的处理是数据处理中很典型的工作。许多人往往对之望而却步,希望学习复杂的技巧。其实,对他们的处理都是一些最基础功能的应用,条件格式,数据有效性,删除重复项是基本功能,数据透视表也只是用到了其中最简单地用法。函数更是只用了countif,这是Excel中最基础的函数之一。实际上,Excel中大部分数据处理问题都是这些基础知识的综合应用。只用多练习,你也可以成为Excel能手。

尖椒.png

发送
获取验证码
确定
恭喜您投票成功