您的位置: 首页 > EXCEL技巧 > Excel函数 >

从多行多列区域中提取不重复值到一列

时间:2013-12-14 整理:docExcel.net

要将一个多行多列区域中不重复的数据提取到一列,即对于重复的数据只提取一次,最直接的做法是先将该区域的数据转到一列中,再利用Excel2007/2010中的删除重复项功能去掉重复值。另外,还可使用数据透视表、数组公式和VBA的方法来提取,本文以Excel 2010为例分别介绍如下,示例文件下载地址:http://www.excel123.cn/Soft/201210/38.html

假如数据在A1:C11区域,其中还包含一些空单元格,如图所示。

从区域中提取不重复值示例

方法一:利用数据透视表

利于数据透视表的多重合并计算功能,将上述数据区域作为数据透视表的多重合并计算数据区域,把“值”字段放到在数据透视表的行区域中,即可得到不重复值。步骤如下:

1.在区域左侧插入空白列。如果原数据区域不是从A列开始,则可跳过此步骤。在本例中,由于原数据区域包含工作表的第一列(A列),需先在该区域的左侧插入一个空白列,作为数据透视表的行字段。

在原区域左侧插入一个空白列

2.选择数据区域中的某个单元格,按Alt+D,再按P键,打开“数据透视表和数据透视图向导”,选择“多重合并计算数据区域”项,单击“下一步”。

选择多重合并计算数据区域

3.再次单击“下一步”,在弹出的对话框中选择并添加数据区域。在选择区域时注意要包含原数据区域的左侧列及标题列。本例为包含上述空白列的A1:D11。

在对话框中添加区域

单击“下一步”,在弹出的对话框中选择数据透视表存放的位置,单击“完成”按钮。本例将数据透视表放置在当前工作表F3单元格开始的区域中。

4.在数据透视表的字段列表中取消选择所有字段,然后再次勾选“值”字段,或将“值”字段拖入到“行”标签区域中,即可在F列得到原区域中的不重复值。

取消所有字段后再次勾选”值“字段

方法二:使用数组公式

假如数据在A1:C11区域,在E2单元格中输入数组公式:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$11<>"")*(COUNTIF($E$1:E1,$A$2:$C$11)=0),ROW($2:$11)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

公式输入完毕,按Ctrl+Shift+Enter结束。然后拖动填充柄向下填充公式,直到出现空白为止。

数组公式得到区域中的不重复值

如果区域中的数据全部为数字和空单元格,还可用下面的数组公式:

=LARGE(IF($A$2:$C$11="","",$A$2:$C$11),SUM(COUNTIF($A$2:$C$11,$E$1:E1))+1)

方法三:使用VBA

下面的VBA代码利用Dictionary对象中的关键字具有唯一性的特点得到不重复值。使用方法是按Alt+F11打开VBA编辑器,在代码窗口中粘贴下列代码并运行,即可在F列可得到A2:C11区域中的不重复值。

Sub Uniquedata()
Dim rCell As Range
'创建Dictionary对象
Set d = CreateObject("Scripting.Dictionary")
'遍历数据区域的单元格
For Each rCell In Range("A2:C11")
  '判断单元格是否为空
  If rCell <> "" Then
   '如果Dictionary对象中不包含指定的关键字就添加该关键字和条目
   If Not d.exists(rCell.Value) Then d.Add rCell.Value, rCell.Value
  End If
Next
'清除指定列内容
Range("F2:F" & Range("F2").End(xlDown).Row).ClearContents
'将Dictionary对象中的条目写入指定列
Range("F2").Resize(d.Count) = WorksheetFunction.Transpose(d.Items)
End Sub

公式法将多行多列数据转换到单列 问题:公式法将多行多列数据转换到单列
回答:在Excel中将多行多列数据转换到单列的方法有很多,本文介绍用公式进行转换的方法。假如Sheet1表的A2:E7区域有6行5列的数据需要转换到Sheet2表的A列,如图所示。   一、按先行后列的顺序转换 即先提取第一行的数据,再依...
用VBA将多行多列区域转为一列 问题:用VBA将多行多列区域转为一列
回答:有时需要在Excel中将某个多行多列的区域转换到一列中,下面的VBA代码可以实现这个目的。1.按快捷键Alt+F11,打开VBA编辑器,在右侧代码窗口中粘贴下列代码:Sub RangeToOneCol()Dim TheRng, TempArrDim i As Integer, j As Integer, elemCount As IntegerO...
不计重复值提取区域中的第N个最大值 问题:不计重复值提取区域中的第N个最大值
回答:...A1:A14),4) 公式输入完毕后按Ctrl+Shift+Enter结束。 如果某个多行多列的区域中包含重复值,例如数据在A1:C9区域中,要提取不计重复值的第4个最大值,用下面的数组公式: =LARGE(IF(LOOKUP(COLUMN(A:C)+COLUMNS(A:C)*(ROW($1:$9)-1),ROW(1:27),FREQUE...
数组公式提取区域中的重复值 问题:数组公式提取区域中的重复值
回答:   有时需要要提取多行多列区域中的重复数据到某列,如下图所示,A2:C11包含一些重复的水果名称(已用深红色进行了标记),要将重复的名称提取到E列。 在E2中输入数组公式: =INDIRECT(TEXT(MIN(IF((COUNTIF($A$2:$C$11,$A$2:$C...
提取出Excel2013表格的列重复项 问题:提取出Excel2013表格的列重复项
回答:打开一份Excel表格,多行多列,数据庞大,任务要求是找出列重复项。如果单凭眼力一个一个去查找,显然不可取,不科学。下面,我来教大家一种简单的处理技巧。 下面的例子是公司部门统计出上下半年都领取的人员名单。...
VBA入门基础语句详解 问题:VBA入门基础语句详解
回答:...:ActiveCell.EntireRow.SelectActiveCell.EntireColumn.Select选择相邻的多行/多列:Columns( A:C ).SelectRows( 1:5 ).Select选择不相邻的多行/多列:注意:和选择相邻的多行/多列不同,使用 Range 而不是 Columns/Row
相关推荐: