小课堂:真香警告!去除重复值一个函数搞定!
今日目标:
今天看到一个金句:
每天在几十个群里解答Excel的问题,答疑经验告诉我:
这句话说的TMD对啦!
很多人连「我还没准备好」这一步都还没到,直接伸手要答案。
这也不能完全怪他们,因为有些问题,确实很难找到答案。
比如Excel最常见的提取非重复值的问题。
上面这个表格中,要从「部门」和「姓名」中提取非重复值,要怎么做?
百度到的公式,大部分情况都让人看不懂。
=INDEX($B$2:$B$25,MATCH(0,COUNTIF(G$1:G1,$B$2:$B$25),0))&""今天教大家一个「真香」函数:UNIQUE。
一个函数搞定数据非重复值、唯一值的提取。
1- 去除重复值
直接动手看案例。
UNIQUE函数的作用,就是提取非重复值。基础的用法非常简单。
要提取「部门」中的非重复值,公式如下:
=UNIQUE(B2:B25)
就一个参数,选择要去除重复值的数据,直接返回不重复的部门清单。
一伸手,就能拿到,这种感觉太爽了。
2- 提取唯一值
有的人只打卡了1次,是什么原因?得单独拉出来分析一下。
把表格中,只出现过一次的数据提取出来,UNIQUE函数,只需要加2个参数:
=UNIQUE(C2:C25,0,1)
参数说明如下:
参数1:
要提取非重复值的区域
参数2:
如果按行提取非重复值,就输入0(默认)
如果按列提取非重复值,就输入1。
参数3:
如果提取只出现过1次的,就写1。
如果提取所有非重复值,就写0(默认)
有时候,越简单的东西,越容易被认为低廉。
对比一下,相同的效果用传统公式要这样写。
=OFFSET($A$1,MIN(IF(COUNTIF($H$1:H1,$A$2:$A$21)=0,ROW($A$2:$A$21)))-1,)
看完UNIQUE立马变的高大上了吧。
3- 分组去除重复值
UNIQUE有一个好兄弟:FILTER,它俩在一起有一个组合的名字:上天组合。
根据分组提取非重复值,是它俩的压轴曲目。
根据「部门」,提取「姓名」的唯一值。传统思路是这样的:
1- 用MATCH函数,找到第1个产品大类的单元格,A
2- 用COUNTIF函数,计算这个大类的个数N
3- 用OFFSET函数,从第A个位置引用N个单元,返回对应的「产品名称」
先不谈公式,光看这个思路,我就已经晕了。
用UNIQUE和FILTER的话,公式是这样的:
=UNIQUE(FILTER(C2:C25,B2:B25=G2))
公式从内到外依次拆解,大致的步骤是:
1- FILTER筛选数据
用FILTER函数,筛选「部门」对应的「姓名」,得到的结果是这样的:
=FILTER(C2:C25,B2:B25=G2)
提取出来的数据还有一些重复值,接下来用UNIQUE函数去重。
2- UNIQUE函数去除重复值
把FILTER筛选出来的数据,作为参数传递给UNIQUE函数,分组去重复数据就提取出来了。
=UNIQUE(FILTER(C2:C25,B2:B25=G2))
我想过买假发,想过去植发
怎么也没想到,提取非重复值,可以这样的方便啊!
4- 创建下拉菜单
Excel高手吧,都容易玩技术自嗨。
关键的问题是,提取非重复值干什么呢?
你都不知道,这玩意用来做Excel下拉菜单,有多好用。
下拉菜单的选项,根据输入的内容,自动更新,这个效果我做梦都想好几回了。
大致步骤是这样的:
1- UNIQUE函数提取非重复值
2- 数据验证添加下拉菜单
传统的方法,下拉菜单选项要选择对应的数据区域。
UNIQUE还有一个隐藏的身份:动态数组函数。也就是会根据提取内容数量,自动扩展填充区域。
返回的区域是动态的,而且,我们可以动态的获取这个区域,用一个简单的符号「#」,就轻松搞定。
你要知道,这个#号,过去的话要用到OFFSET函数来写公式:
=OFFSET(G6,1,,COUNTA($G$7:$G$18))
不忍回忆过去。
感谢天
感谢地
感谢office365给我们
UNIQUE神器
5- 总结
UNIQUE结合FILTER函数,还有很多有意思的玩法。
比如,可以制作多级下拉菜单:
而且很简单,菜单数据就像左边一样,非常整齐。
想知道怎么做的吗?答对下面的问题,我就告诉你
考考你
现在要提取「只有1条记录」的部门名称,公式应该怎么写?
不管你喜不喜欢今天的文章,请点击右下角的「在看」,好吗?
媳妇说,超过20个,今晚我就不用洗碗了。
我是拉小登,一个会设计表格的Excel老师