在EXCEL表格里操作数据的时候,往往需要用到删除重复值,而如何删除一列数据里的重复值呢?最简单的办法当属标准功能了,选中数据列,点击“数据”选项卡里的重复值,就可以把重复的数据删除了,但凡事都有例外的情况,如果这列原始数据经常会更新,增加、删除或是修改,那我们就得每改一次原始数据,就得操作一次“删除重复值”,那能不能自动从原始数据里提取唯一值,不管原始数据如何变化,都能自己更新呢?当然可以,这就需要用到函数公式了。下面我们分别用两种方法来去除重复值,并看看它们之间的区别吧!
直接删除重复值
如果所示,A列有很多的编号,标记了颜色的都是重复数据,现在我们将数据复制到F列,全选,点击菜单栏上“数据-删除重复值”。
弹出窗口,选择列,取消勾选“数据包含标题”,因为F列没有标题。点击确定。
弹出删除结果,F列保留了唯一值。
此方法简单、快,唯一的毛病就是如果A列数据发生了变化后,不会自动更新。
使用公式提取唯一值
先上函数公式:=LOOKUP(1,0/(COUNTIF($C$1:C1,$A$2:$A$46)=0),$A$2:$A$46)
从公式里可以看出这里使用了两个函数进行了组合,LOOKUP查找函数和COUNTIF计算符合条件的单元格数目。
先看COUNTIF函数,COUNTIF($C$1:C1,$A$2:$A$46)=0,这里使用COUNTIF函数计算出当前行上方的编号和A列编号是否重复,不重复则返回0,符合“=0”的条件并返回逻辑值TRUE,否则返回FALSE。
在这里TRUE表示1,FALSE表示0,而用“0/”则遇到FALSE的情况会返回错误值#DIV/0!,因为0不能作为除数,
在使用LOOKUP函数进行查找,
这里使用了LOOKUP函数的经典用法“LOOKUP(1,0/(条件区域),查询区域)”
“0/”的目的就是把符合条件的变成0,其余的变成错误值,而LOOKUP函数在查询的时候会自动忽略错误值,如果条件里只有一个满足的,那刚好返回这个0对应位置所在的数据,如果条件里有多个满足,就会返回最后一个计算出来的0对应位置的数据。
注意这里的A2:A46作为固定数据源,不管是在COUNTIF还是在LOOKUP函数里都需要添加绝对引用符号,而COUNTIF里的第一个参数也需要添加绝对引用固定第一个单元格引用,第二个单元格引用不能添加绝对符号,这样公式才能往下拉动。
选中单元格,往下拖动公式,直到返回“#N/A”错误值。上方的编号就是从A列提取出来的唯一编号,因为LOOKUP多个符合条件的记录是返回最后一条的数据,故唯一值的顺序和原始数据的顺序是不一样的。
如果不想把错误值符号显示出来,这里可以添加一个IFERROR函数在前面,这样就能将错误值返回空白。完整公式为“=IFERROR(LOOKUP(1,0/(COUNTIF($C$1:C1,$A$2:$A$46)=0),$A$2:$A$46),"")”。
公式里的“1,0/”,可以替换为任意两个数字,但第一个数字要比第二个数字大即可。
修改A列的数据,C列会自动重新计算,自动更新。
最后,我们再来总结下:
如果只是简单的想要删除重复数据,使用标准的菜单功能,简单好用。
如果需要自动更新的提取唯一值,使用函数公式,保存下来,不理解也能套用,只需要修改里面的单元格引用地址即可。