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

将区域中的值按出现次数的多少提取到一列

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

对于一个包含重复数值的区域,要将其中的数值按出现次数的多少提取到一列,通常可以用包含MODE函数的数组公式。例如数值区域为A1:E10,要将其中的数值提取到G列,如图。

 用MODE函数按出现次数提取区域中的数值示例 

在G2中输入:

=MODE(IF(COUNTIF($G$1:G1,A$1:E$10)=0,A$1:E$10))

公式输入完毕按Ctrl+Shift+Enter结束,然后拖动填充柄向下填充公式,直到出现“#N/A”为止。

受MODE函数的限制,该公式仅对数值有效,且不能提取只出现一次的数值。要将区域中所有的数据按出现次数提取到一列,可用VBA代码来实现。如将下图A1:E10区域中的文本按出现次数多少提取到G列:

 VBA按出现次数提取区域中的文本示例 

按Alt+F11,打开VBA编辑器,在代码窗口中输入下列代码并运行,即可G列得到按出现次数降序排序的文本,在H列得到对应文本的出现次数。

Sub 按出现次数排序()
  Dim d As Object
  Dim Arr
  Dim i As Integer, j As Integer
  Application.ScreenUpdating = False
 
  '提取不重复值并计算出现次数
  Set d = CreateObject("Scripting.Dictionary")
  Arr = Range("A1:E10")
  For i = 1 To UBound(Arr, 1)
For j = 1 To UBound(Arr, 2)
   If Not d.Exists(Arr(i, j)) Then
  d.Add Arr(i, j), 1
   Else
  d.Item(Arr(i, j)) = d.Item(Arr(i, j)) + 1
   End If
Next
  Next
 
  '输出并排序
  Range("G2").Resize(d.Count) = Application.Transpose(d.keys)
  Range("H2").Resize(d.Count) = Application.Transpose(d.items)
  Range("G2:H2").Resize(d.Count).Sort key1:=Range("H2"), Order1:=xlDescending
 
  Set d = Nothing
  Application.ScreenUpdating = True
End Sub

说明:如果文本出现的次数相同,将按先行后列的顺序依次提取。

提取一列中出现次数最多和最少的文本数据 问题:提取一列中出现次数最多和最少的文本数据
回答:...是文本,可以用下面的公式,假如数据在A1:A100区域中: 提取出现次数最多的文本: =INDEX(A1:A100,MODE(MATCH(A1:A100,A1:A100,))) 或下面数组公式: =INDEX(A1:A100,MATCH(MAX(COUNTIF(A1:A100,A1:A100)),COUNTIF(A1:A100,A1:A100),)) 提取出现次数最少的文本...
从一列中按出现次数多少顺序提取数据 问题:从一列中按出现次数多少顺序提取数据
回答:...包含许多重复的数据,要从该列中按出现次数多少的顺序提取数据,可以用下面的数组公式。假如数据在A2:A21区域中,如图。 在B2单元格中输入下列数组公式,公式输入完毕后按Ctrl+Shift+Enter结束。 =INDEX($A$2:$A$21, MATCH(MAX(COUNTI...
统计指定数字在区域中的出现次数 问题:统计指定数字在区域中的出现次数
回答:...要在一列包含文本格式数值的区域中统计某个数字出现的次数,就无法使用COUNTIF函数。这时可用下面的公式,假如数据在A2:A11区域中,要统计的数字在B2单元格。本例统计数字“0”在A2:A11区域中出现的次数。 要统计的数字在...
根据指定次数重复Excel2007单元格的内容 问题:根据指定次数重复Excel2007单元格的内容
回答:...法。 案例演示 ①下表中我们可以看到销售人员和重复次数,先在C2单元格输入公式: =SUM($B$2:B2)-ROW(A1) ②回车得到结果1,拖动填充柄,向下填充,直到出现0为止。然后点击数据选项卡里面的排序按钮。 ③弹出排序提醒对...
从多行多列区域中提取不重复值到一列 问题:从多行多列区域中提取不重复值到一列
回答:要将一个多行多列区域中不重复的数据提取到一列,即对于重复的数据只提取一次,最直接的做法是先将该区域的数据转到一列中,再利用Excel2007/2010中的删除重复项功能去掉重复值。另外,还可使用数据透视表、数组公式和VBA...
不计重复值提取区域中的第N个最大值 问题:不计重复值提取区域中的第N个最大值
回答:在Excel中提取区域内第N个最大值可以用LARGE函数,其语法为: LARGE(array, k) 例如在区域A1:C9中包含1-27共27个数字,要获取第4个最大值。 公式: =LARGE(A1:C9,4) 返回结果“24”,这是区域中不包含重复值的情况。有时区域中包含...
相关推荐: