Excel中处理重复数据的5种方法

 2016年4月9日 |  0 条评论 |   342

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

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

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

3. 挑出不重复列表

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

5. 阻止重复数据的产生

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

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

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

Excel中处理重复数据的5种方法

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

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

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

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

如下图:

Excel中处理重复数据的5种方法

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

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

Excel中处理重复数据的5种方法

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

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

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

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

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

如下图:

Excel中处理重复数据的5种方法

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

Excel中处理重复数据的5种方法

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

2. 删除重复数据

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

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

Excel中处理重复数据的5种方法

得到如下对话框

Excel中处理重复数据的5种方法

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

Excel中处理重复数据的5种方法

3. 挑出不重复列表

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

Excel中处理重复数据的5种方法

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

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

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

Excel中处理重复数据的5种方法

4. 不重复数据的计数

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

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

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

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

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

Excel中处理重复数据的5种方法

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

=sum(E3:E47)

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

5. 阻止重复数据的产生

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

方法如下:

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

Excel中处理重复数据的5种方法

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

Excel中处理重复数据的5种方法

总结

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

文章字数统计:1740 | 百度已收录

  |  pc蛋蛋信誉大群/office, 幸运28夜场微信群/excel  


微部落博客编辑整理发布,如有侵犯您的版权,请提供相关版权证明,博主将立即删除。
·博客大全  博客网址之家  博客导航  来路IP首页展示!   ·免费收录  ·免费推广你的博客   114.vprol.com
如果本文对您有用就 打个赏吧微信 OR 支付宝 扫描二维码
pay_weixin     pay_weixin
金额随意,您的支持是我的动力~

草根站长,博客导航,博客大全,博客网站,增加网站流量
回复 取消

欢迎评论发言,灌水及广告评论将被定期删除!