Excel数据去重,竟然这么简单?从入门到精通的终极指南!

醉香说职场 2024-05-21 11:23:34

我是【桃大喵学习记】,欢迎大家关注哟~,每天为你分享职场办公软件使用技巧干货!

最近,好多小伙伴都在问“Excel数据去重”的方法,其实这方面的教程已经分享很多次了。但今天,我就来个大总结,把各种使用场景都打包成一篇超详细的教程!篇幅有点长,所以建议大家先收藏起来,以后随时翻看!

数据去重必学函数UNIQUE函数介绍

功能:UNIQUE函数可以去除重复值保留唯一值

语法:=UNIQUE(数组,[按列],[仅出现一次])

第1参数:数组就是返回唯一值的数组数据区域;

第2参数:按列是可选参数,指定比较的方式,设置为TRUE将比较列并返回唯一值,设置为FALSE (或省略) 将比较行并返回唯一值;

第3参数:[仅出现一次]可选参数,一般直接省略即可。

备注:FILTER函数需更新至WPS Office最新版本使用

基本用法:

如下图所示,左侧是名单信息,我们需要去掉里面的重复值。

在目标单元格中输入公式:

=UNIQUE(A1:A8)

然后点击回车,即可

解读:

公式中A1:A8就是要去重的数据区域;省略了第二参数默认按行去重并返回唯一值;省略的三参数默认是FALSE返回唯一选项。

当然如果是去重数据是按行横向排列的话,就必须把第二参数设置为TRUE,就是按列去重。

在目标单元格中输入公式:

=UNIQUE(B2:I2,TRUE)

高级用法一:计数不重复人数

在目标单元格中输入公式:

=COUNTA(UNIQUE(A2:A8))

然后点击回车即可

当然如果想计算重复的人数,可以使用姓名总数减去唯一值的个数即可

公式:=COUNTA(A2:A8)-COUNTA(UNIQUE(A2:A8))

高级用法二:动态提取不重复数据

如下图所示,这是一份参会名单,并且参会人员有可能随时添加新的人员信息,我们需要实时提取不重复数据以便于后期进行数据统计。

下面直接上干货,在目标单元格中输入公式:

=DROP(UNIQUE(A:A),-1,0)

然后点击回车即可,如果参会名单变动,提取的不重复数据也会自动更新。

解读:

①上面公式使用DROP函数和UNIQUE函数组合,主要是为了实现根据参会人员变动,达到动态提取不重复数据的效果。

②首先使用UNIQUE(A:A)函数提取A列参会人员名称中不重复的数据,因为选择的是这列数据,使用UNIQUE函数后会在结果下方多出一个数字0。出现这种情况的原因是UNIQUE函数在对A列最底部的空白单元格去重时,会默显示数字0。

③最后再使用DROP函数去掉去重数据的最后一行数据0,第2参数-1就是从下往上删除一行;第3个参数为0,表示删除0列,这样就最终得到我们想要的去重的结果了。

高级用法三:按条件提取不重复数据

如下图所示,这是一个假期值班表格,我们需要根据所属“门店”这个条件,筛选出不重复的“值班经理”名单。

在目标单元格中输入公式:

=UNIQUE(FILTER(B2:B9,A2:A9=E2,"无数据"))

然后点击回车即可

解读:

1、公式中首先通过FILTER函数,按条件筛选出指定门店的值班经理名单,然后再通过UNIQUE函数提取出不重复的名单数据即可。

2、上面实例中是按单个条件提取不重复数据,我们也可以进行多条件提取不重复数据,只需正确设置FILTER函数第2个参数即可

①如果需要多个条件同时满足,就用*把多个条件连接

条件1*条件2*条件N

例如:(A2:A9=E2)*(C2:C9=D2)

②如果需要多个条件满足任意一个,就用+把多个条件连接

条件1+条件2+条件N

例如:(A2:A9=E2)+(C2:C9=D2)

高级用法四:实现中国式排名

首先介绍一下什么是中国式排名呢?

举个例子比如说公司一共有10名员工进行成绩考核,如果9个人考核成绩都是90分,你是89分,按照国际惯用的排名法则:9 个人考核成绩并列第一,你第10名;但是,如果按中国式排名:9 个人考核成绩并列第一,你第2名。所以中国式排名就不能直接RANK函数,RANK函数只适用于美式排名,不适用于中国式排名。

如下图所示,这是一年级学生成绩,我们需要对学生成绩进行中国式排名,我们可以看到赵金龙、孙二娘都是98分,并列第2名,后面的张飞92是第3名。

巧用MATCH+UNIQUE函数组合进行中国式排名(重复数据只占一个排序号)

方法:

第一步、

开始中国式排名前,首先要对C列“成绩”数据进行降序排列,按成绩从高往低排列,如下图所示

第二步、

然后在D2单元格中输入输入公式:

=MATCH(C2,UNIQUE(C$2:C$14),0)

然后点击回车,下拉填充数据即可

解读:

①在进行排名之前先对成绩进行从高往低降序排列,这样最高分就在最上面

②公式中UNIQUE(C$2:C$14)通过去重函数,把重复的成绩去掉。需要主要是时选择C2:C14后需要按两次F4键(锁行不锁列,当然可以进行完全引用按一次F4键)

③在通过MATCH查找每个成绩在UNIQUE(C$2:C$14)中的行号,也就是排序编号。如果成绩相同它们就占用同一个排序编号。

高级用法五:对多行多列数据去重

如下图所示,这是一个参会姓名名单,是多行多列数据,我们需要去掉重复数据

在目标单元格中输入公式:

=UNIQUE(TOCOL(A2:C6,3))

然后点击回车即可

解读:

①使用UNIQUE函数只能对单列或者单行数据进行去重处理,无法对多列多行数据进行去重。

②公式中先使用TOCOL(A2:C6,3)把数据区域转换成一列数据,第2参数设置成3是忽略空格和错误值,然后再通过UNIQUE函数对数据进行去重处理。这样就可以就轻松实现了对多行多列数据去重。

③TOCOL函数介绍

功能:将二维数组转化成一列数据

语法:=TOCOL(数组,[忽略特殊值],[通过列扫描])

第1参数:数组就是要转化成一列显示的数据

第2参数:忽略特殊值

如果输入0:不忽略特殊值

输入1:忽略空白单元格

输入2:忽略错误值

输入3:忽略空白单元格和错误值

第3参数:通过列扫描,FALSE,按行,TRUE按列,如果省略默认按行

以上就是【桃大喵学习记】今天的干货分享~觉得内容对你有帮助,记得顺手点个赞哟~。我会经常分享职场办公软件使用技巧干货!大家有什么问题欢迎留言关注!

1 阅读:27

醉香说职场

简介:职场啥都得懂