Note: The other languages of the website are Google-translated. Back to English

如何从Excel中的列范围中动态提取唯一值列表?

对于定期更改值的列范围,无论更改如何,您始终需要从该范围中获取所有唯一值。 如何制作唯一值的动态列表? 本文将向您展示如何处理它。

使用公式从列范围动态提取唯一值列表
使用VBA代码从列范围动态提取唯一值列表


使用公式从列范围动态提取唯一值列表

如下图所示,您需要从范围B2:B9中动态提取唯一值列表。 请尝试以下数组公式。

1.选择一个空白单元格,例如D2,在其中输入以下公式,然后按 按Ctrl + 转移 + 输入 同时键。 (B2:B9是您要提取唯一值的列数据,D1是您的公式所在的上方单元格)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2.继续选择单元格D2,然后向下拖动“填充手柄”以获取指定范围内的所有唯一值。

现在,将提取列范围B2:B9中的所有唯一值。 当此范围内的值更改时,唯一值列表将立即动态更改。

在excel中轻松选择并突出显示所有唯一值:

新的 选择重复和唯一单元格 实用程序 Kutools for Excel 可以帮助您轻松选择并突出显示所有唯一值(包括第一个重复项)或仅出现一次的唯一值,以及您需要的重复值,如下图所示。
立即下载 Kutools for Excel! (30-天免费试用)


使用VBA代码从列范围动态提取唯一值列表

您还可以使用以下VBA代码从列范围中动态提取唯一值列表。

1。 按 其他 + F11 同时打开 Microsoft Visual Basic应用程序 窗口。

2。 在里面 Microsoft Visual Basic应用程序 窗口中,单击 插页 > 模块。 然后将以下VBA代码复制并粘贴到 模块 窗口。

VBA代码:从范围中提取唯一值列表

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

备注:在代码中,D2是您将找到唯一值列表的单元格。 您可以根据需要进行更改。

3.回到工作表,单击 插页 > 形状 > 长方形。 看截图:

4.在工作表中绘制一个矩形,然后输入一些需要在其上显示的单词。 然后右键单击它并选择 分配宏 从右键单击菜单中。 在里面 分配宏 对话框中,选择 创建唯一列表宏名 框,然后单击 OK 按钮。 看截图:

5.现在,单击矩形按钮, Kutools for Excel 弹出对话框,请选择包含您需要提取的唯一值的范围,然后单击 OK 按钮。

从现在开始,您可以重复上述步骤5以自动更新唯一值列表。


相关文章:


最佳办公效率工具

Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及您以前使用过的任何东西; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不会丢失数据; 拆分单元格内容; 合并重复的行/列...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 超过300种强大功能. 支持 Office / Excel 2007-2021 和 365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能 30 天免费试用。 60 天退款保证。
kte选项卡201905

Office选项卡为Office带来了选项卡式界面,使您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
按评论排序
注释 (35)
还没有评分。 成为第一位评论!
该评论由网站上的主持人最小化
感谢您的指导。 使用公式方法,如果要添加类别限定符,您将如何更改公式? 假设在 C 列中,您区分该项目是水果还是蔬菜。 您将如何更改代码以仅对独特的水果进行分类并排除蔬菜? 我尝试使用 (LIST RANGE,"CATEGORY") 的第二个 countifs 标准将 COUNTIF 替换为 COUNTIFS,但它返回空白。 我需要扩展我的阵列并合并 VLOOKUP 吗?
该评论由网站上的主持人最小化
我擅长 excel,但我真的很想弄清楚上述公式的工作原理和原因(它适用于我使用它的目的,但我必须理解为什么)。 有时我对使用数组有点困惑,所以任何用白痴术语解释都会非常有帮助的问候
该评论由网站上的主持人最小化
这个公式已经过时并且不起作用。 我实际上只是设置了这个精确的 excel 表,看看我是否可以让这个公式起作用,但它没有。
该评论由网站上的主持人最小化
嘿家伙,
您使用哪个 Office 版本?
该评论由网站上的主持人最小化
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - 发现这可以从另一个站点工作...

使用 Ctrl+Shift+Enter 获取数组函数(大括号)。 拖动复制粘贴公式,直到显示#NA。 我的数据集在 Column-Q 中,它被比较以查看它是否存在于 Column-V 的唯一列表中,该列表不断地沿着同一列延伸。
该评论由网站上的主持人最小化
美好的一天。
请使用 abobv 公式列出 Q 列的所有唯一值,然后使用他的公式 =IF(D2=V1,"Match","No match") 比较第 Q 列中的唯一值是否与同一行中的 V 列进行比较.
该评论由网站上的主持人最小化
您好,感谢您的帮助。

我正是需要这个功能,但我的“唯一值”列表需要跨列而不是行扩展,因此行向下的扩展列表对我不起作用。

我如何修改此公式以使“唯一值”列表在我将其拖过列时展开?

抵消()?
转置()?
Indirect() 带有与列而不是行的引用连接的绝对引用字符串?


再次感谢!
该评论由网站上的主持人最小化
亲爱的瑞安,
这个公式 =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter 可以帮助您解决问题。
请看下面的截图:
查看附件 (1 / 5)
该评论由网站上的主持人最小化
此外,无论出于何种原因,原始公式提供:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

返回“循环引用”警告并且不会计算..
该评论由网站上的主持人最小化
亲爱的瑞安,
您使用哪个 Office 版本? 该公式在我的 Office 2016 和 2013 中运行良好。
该评论由网站上的主持人最小化
我以前发生过这种情况 - 我的解决方法是我将公式输入到单元格 D1 中(相当于我正在使用的工作表)。 无论 $D:$1 对应于哪个单元格,您都需要在下面的单元格 - D2 中输入它。 抱歉,如果这不是您收到错误的原因
该评论由网站上的主持人最小化
有关让 VBA 选项与 Excel 2016 for macOS 一起使用的任何提示? 我已按照步骤操作; 但是,当我运行宏时,什么也没有发生。 谢谢!
该评论由网站上的主持人最小化
达尔琼斯,
请尝试下面的 VBA 代码,让我知道它是否适合您。 谢谢!

子 CreateUniqueList()
将 xRng 调暗为范围
将 xLastRow 变暗
将 xLastRow2 变暗
将 I 调暗为整数
' On Error Resume Next
Set xRng = Application.InputBox("请选择范围:", "Kutools for Excel", Selection.Address, , , , , 8)
如果 xRng 什么都不是,则退出 Sub
出错时继续下一步
xRng.复制范围(“D2”)
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
对于 I = 1 到 xLastRow2
如果 ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" 那么
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
结束如果
下一页
END SUB
该评论由网站上的主持人最小化
嗨水晶,
我正在尝试使用唯一值列表的 VB 版本并遇到问题。
我要从中创建唯一值列的范围是引用不同选项卡的所有公式。
如何获得要转移的价值而不是公式?
该评论由网站上的主持人最小化
亲爱的迈克:
请将您的公式引用转换为绝对值,然后应用 VB 脚本。
该评论由网站上的主持人最小化
我有同样的问题,除了我的公式引用列名并且不能转换为绝对值。
如何更改 vba 以粘贴值而不是公式?
该评论由网站上的主持人最小化
您将如何添加多个条件,例如,如果您只想在日期仅为 9 月 12 日的情况下添加到动态列表中?

我正在 MATCH 公式中尝试“&”,但它不起作用。

例如,根据您的示例:
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
这会引发错误或创建重复项。

或者,我读过“+”可能会起作用,尽管我无法让它起作用。 或使用小。

想法?
该评论由网站上的主持人最小化
亲爱的扎克,
抱歉,我帮不上忙,您可以在我们的论坛中发布您的问题: https://www.extendoffice.com/forum.html 从我们的专业人员那里获得更多的 Excel 支持。
该评论由网站上的主持人最小化
您将如何添加第二个变量? 例如,我希望一列中的所有唯一元素在另一列中也共享相似的值。 在您的示例中,想象一个标题为“部门”的第三列,其中包含产品、肉类等值。我意识到这些都是农产品,但希望你明白我的意思。 您会将 CountIF 公式修改为 COUNTIFS 还是以其他方式修改它?
该评论由网站上的主持人最小化
嗨,马特
请试试这个公式 =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Yes","")。
假设两个比较的列表分别是A列和C列,如果唯一值只留在A列而不在C列,则在B列显示Yes; 而如果 B 列不返回任何内容,则表示相应的值同时保留在 A 列和 C 列中。
该评论由网站上的主持人最小化
感谢您的回复.. 但是如果显示为“是”,则很想提取该唯一值。您能否建议我使用公式来提取不同列中的唯一值。
该评论由网站上的主持人最小化
如果我在 Mac 上最新版本的 Excel 上为一千行 Excel 表执行此操作,它永远不会返回。 第一行有效,但是当我复制下来时,excel 进入计算模式,现在已经有两个多小时没有返回值了。

关于如何为将返回 2 或 50 个唯一值的大型列表(最多 60k 行)执行此操作的任何想法?

我在“数字”应用程序中模拟了这个,它在那里工作得很好,只需要几分钟就可以计算出来。 它只是在 Excel 中花费了很长时间,我想知道它是否会完成。 我计划让它在一夜之间“运行”,看看会发生什么。
该评论由网站上的主持人最小化
检查您的计算选项。 它需要设置为自动。 文件 > 选项 > 公式 > 计算选项 > 工作簿计算(自动选择)
该评论由网站上的主持人最小化
我试图将公式拖到我的实际数据之外,这样我就可以输入不同大小的数据集,而不必调整任何东西。 但是,我的实际数据结束后的最后一行总是返回“0”。 我正在对相邻列中的其他内容使用唯一值,并且 0 导致最后一个值重复(当我删除 0 时,该值不再重复)。 知道如何解决这个问题吗? 我也在使用 Office 365 商业版
该评论由网站上的主持人最小化
你好,谢谢你的帮助。
现在,我怎样才能让我的值也按字母顺序排序? (我不想在我的主表上使用过滤器)
我应该使用 COUNTIFS 而不是 COUNTIF 吗?
请帮忙
该评论由网站上的主持人最小化
嗨亚历克西斯,
抱歉,无法使用公式同时按字母顺序对提取的值进行排序。 感谢您的评论。
该评论由网站上的主持人最小化
我正在使用 =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") 公式,这很棒对于一列,但我的数据分布在一系列列和行中。 我可以编辑公式以包含整个区域吗? 我的数据从 AC4 到 AR60...
该评论由网站上的主持人最小化
我尝试了 VBA 代码和公式。 代码 VBA 运行良好,但我无法保存带有宏的文件。 但问题是我无法使公式起作用。 有人有想法吗? 谢谢
该评论由网站上的主持人最小化
嗨夏洛特,
感谢您的评论。 您可以通过将工作簿保存为启用宏的 Excel 工作簿来保留带有宏的文件以供将来使用。
对于公式问题,请您提供数据的屏幕截图吗? 感谢您的评论。
该评论由网站上的主持人最小化
非常感谢
该评论由网站上的主持人最小化
如何使 vba 代码适用于使用另一个公式的范围?在 BI 列上有一个公式,参考 D 和 E 列。
如果我使用将代码应用于 L 列(比方说),(显然,正确修改代码中的单元格)宏返回应用于列 M 和 N 的公式......那么它可以工作,但不是我想要的!如何保留 B 列中的值?谢谢
该评论由网站上的主持人最小化
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote 数据集。 Een goed alternatief is het gebruik van een draaitabel。 Kies dan alleen de rijlabels,je krijgt dan een lijstje 遇到了 unieke waarden。 Het kan zijn dat je wat extra 的 krijgt "(leeg)" bijvoorbeeld。 Je kunt deze er vervolgens uitfilteren。 Helaas kun je maar op 1 标准过滤器。 Ook daar zijn wel weer oplossingen voor, maar dat is wat complex。
该评论由网站上的主持人最小化
我希望能够做同样的事情,除了使用两个单独的列范围(B2:B9)以及(D2:D9)这可能吗?
该评论由网站上的主持人最小化
嗨安东尼,
您可以将结果放在与原始数据相同的列中。 如本例中的 B 列。
但是您需要在公式中引用结果单元格的顶部单元格,如下所示。
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
查看附件 (1 / 5)
该评论由网站上的主持人最小化
con este procedimiento de filtro se hace de forma muy rapida

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se define un filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destino, que en mi caso fue la d59

Range("A59:A239").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("D56:D57"), CopyToRange:=Range("D59"), Unique:=True
这里还没有评论
留下你的意见
以访客身份发帖
×
评价此帖子:
0   产品特性
建议地点

关注我们

版权所有 © 2009 - extendoffice.com。 | 版权所有。 供电 ExtendOffice。 | 网站地图
Microsoft和Office徽标是Microsoft Corporation在美国和/或其他国家的商标或注册商标。
受Sectigo SSL保护