excel表格同一列去重(Excel中多行多列数据去重有高招)

一些数据会重复出现在表格的不同行列中。如老师任课表,由于一些老师会在多个班级任教,因此其姓名会在表中重复出现,现在需要将所有一线任课老师的姓名从表中提取出来,这就会涉及去重问题。如何实现去重呢?下面笔者以Excel 2019为例介绍具体的操作方法。假设学校无重名的老师,若有则需要先标注以示区别(如张三1,张三2)。

文| 俞木发

○ 方法1. 删除重复值法

  用Excel内置的“删除重复值”去重很方便。不过,这个方法要求数据均在一列才行。因此对于多行多列的数据,需要先将去重数据归集在一列中。比如下面是某校老师任课表,现在需要在J列中列出所有任课老师的去重名单(图1)。

excel表格同一列去重(Excel中多行多列数据去重有高招)(1)

(图1)

  定位到B10单元格并输入公式“=C2”,然后向右填充到H10单元格,选中B10:H10数据区域,向下填充公式,直到B列单元格中出现数字0为止,这样在B列中便可以引用全部老师的姓名(图2)。

excel表格同一列去重(Excel中多行多列数据去重有高招)(2)

(图2)

  公式解释:

  这里使用“=”在B10单元格中开始引用下一列的数据,公式下拉后B10:H10就会依次引用各自下一列的数据,直到没有数据为止(单元格显示0),所以最终在B列中可以引用所有任课老师的数据。

  继续选中B2:B57区域(总共56条数据,B58单元格中的数字为0)中的数据并复制,接着定位到J2单元格,依次点击“开始→粘贴→值”,选中J列中的数据,依次点击“数据→删除重复值”,在弹出的窗口中勾选“列J”,点击“确定”(图3)。

excel表格同一列去重(Excel中多行多列数据去重有高招)(3)

(图3)

  这样J列中的重复值就自动被剔除,在该列中就可以保留不重复的老师名单了(图4)。如果后续名单发生了变化,只要重复上述操作,然后再次执行去重操作即可。

excel表格同一列去重(Excel中多行多列数据去重有高招)(4)

(图4)

○ 方法2. 函数法 

  上述方法是手动去重,如果名单发生变化,还需要再次去重。如果要实现去重的自动化,可以借助于函数来实现。

  定位到K2单元格并输入公式“=OFFSET(B$2,MOD(ROW(A1)-1,8),INT((ROW(A1)-1)/8))”,然后下拉填充到单元格显示数字0为止(图5)。

excel表格同一列去重(Excel中多行多列数据去重有高招)(5)

(图5)

  公式解释:

  先使用MOD函数对“(行数-1)”值和除数“8”(对应原始数据包含老师名单的行数,如本例是8行,第2行-第9行)取余,然后将其作为OFFSET函数偏移的列号。因为原始数据为8行,所以每8行会向右偏移1列引用。接着使用INT函数对“(行数-1)/8”数值向下取整,将其作为OFFSET函数偏移的行号数据。引用的基准是B$2(行锁定),这样下拉公式时,OFFSET就会在K列依次引用B2:H10区域中的数据。

  继续定位到L2单元格,输入公式“=IFERROR(INDEX($K$2:$K$100,MATCH(,COUNTIF($L$1:L1,$K$2:$K$100),)),"")”,然后定位到公式地址栏,按下“Ctrl Shift Enter”组合键完成数组公式的输入,接着下拉填充公式,直到单元格显示为0,完成去重名单的提取(图6)。

excel表格同一列去重(Excel中多行多列数据去重有高招)(6)

(图6)

  公式解释:

  先使用COUNTIF函数以“$L$1:L1”为计数条件,计数区域是“$K$2:$K$100”。这里K100数字至少要比图5中OFFSET函数引用时出现的数字0单元格行号的数字要大。然后将这个计数作为MATCH函数的引用数值,再将其作为INDEX函数引用的行号值。最后在外层嵌套IFERROR函数,对没有引用数值的单元格显示为空。这样作为数组公式使用时,就可以对$K$2:$K$100区域的数据完成去重操作。

○ 方法3. VBA法  

  多行多列数据去重,实际操作是先将数据组成一列,然后去重,在VBA中可以借助于RemoveDuplicates函数来快速实现。

  先到“https://share.weiyun.com/BYDj7Qhx”下载所需的代码,接着按下“Alt F11”快捷键打开VBA编辑窗口,依次点击“插入→模块”,将下载的代码粘贴到代码框中(图7)。

excel表格同一列去重(Excel中多行多列数据去重有高招)(7)

(图7)

  代码解释:

  先设置行列变量,列内容是第2列→第8列(即B:H列),行内容是第2行→第9行(请根据实际单元格内容设置)。然后遍历这些行列中的内容,将其提取到I列中保存,最后使用RemoveDuplicates函数对I列的内容去重。

  返回到Excel窗口中,依次点击“开发工具→宏→去重”,点击“执行”,这样VBA代码就会将所有老师的数据复制到I列并完成去重操作了(图8)。CF

excel表格同一列去重(Excel中多行多列数据去重有高招)(8)

(图8)

原文刊登于2022 年 10 月 1 日出版《电脑爱好者》第 19 期

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。