如何从Excel的多列中提取唯一值？
如果你经常处理分布在Excel多列中的数据集，你可能会遇到某些值在同一列内或不同列之间重复的情况。在许多报表或数据分析任务中，有必要识别并提取所有唯一值——即在整个选定区域内仅出现一次的值，无论它们位于何处。手动完成此操作可能既耗时又容易出错，特别是当处理大型数据集或复杂表格时。幸运的是，Excel提供了多种方法来高效地提取这些唯一值。
本指南介绍了几种基于你的Excel版本和个人偏好的解决方案——例如适用于所有版本的公式、适用于最新版本的动态数组公式、使用Kutools AI助手以获得直接结果、使用数据透视表进行视觉整合，以及在复杂场景下通过VBA代码实现自动化提取。
使用公式从多列中提取唯一值
有时你希望使用Excel内置函数实现这种提取。本节详细介绍了如何使用两种方法实现：一种适用于所有Excel版本的数组公式，另一种适用于较新版本（如Excel 365和Excel 2021）的动态数组公式。这些方法非常适合当你需要一个基于公式的直接解决方案，要求随着数据变化频繁更新，或者需要避免外部插件或代码时使用。
使用适用于所有Excel版本的数组公式从多列中提取唯一值
为了兼容所有Excel版本，即使你的Excel不支持动态数组，也可以使用数组公式从多个列中提取唯一值。这种方法利用了INDIRECT、TEXT、MIN、IF、COUNTIF、ROW和COLUMN函数的组合，使其适应各种数据结构非常灵活。
假设你的数据位于A2:C9范围内。要从E2单元格开始提取唯一值，请按照以下步骤操作：
1. 点击单元格E2（或输出范围的第一个单元格），然后输入以下数组公式：
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
- A2:C9 是你要从中提取唯一值的数据区域。
- E1:E1 指向你第一个输出单元格上方的单元格，这是跟踪哪些条目已经输出所必需的。
- $2:$9 是你的数据的行引用；$A:$C 是列引用。根据需要调整这些以适应你自己的工作表布局。
2. 输入公式后，不要仅仅按Enter键，而是同时按下Ctrl + Shift + Enter来确认它是一个数组公式。正确完成后，公式栏中公式周围会出现大括号{}。然后，从E2向下拖动填充柄。继续拖动直到出现空白单元格，这表明没有更多唯一值可提取。这个过程确保所有唯一值都将显示在目标列中。
- $A$2:$C$9：指定要检查唯一值的整个单元格集。
- IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8):
- $A$2:$C$9<>"" 确保忽略空白单元格。
- COUNTIF($E$1:E1,$A$2:$C$9)=0 确保只包括新的（尚未提取的）值。
- 如果两个条件都为真，则相应的输出是基于单元格的行和列生成唯一索引号的计算。
- 如果任一条件为假，则公式返回一个非常大的数字（7^8）以防止意外选择。
- MIN(...): 识别最低的索引号，有效地定位数据中下一个可用唯一值的位置。
- TEXT(...,"R0C00"): 使用R1C1样式将索引转换为有效的单元格引用。
- INDIRECT(...): 将上面创建的单元格引用转换为来自你数据区域的值。
- &"": 强制公式结果被视为文本，确保没有格式化意外。
使用适用于Excel 365、Excel 2021及更新版本的公式从多列中提取唯一值
如果你使用Excel 365、Excel 2021或更新版本，你可以访问动态数组函数，这些函数提供了更简单和更直观的方法来从多列中提取唯一值。UNIQUE和TOCOL函数使得跨列合并数据并在单一步骤中消除重复项变得更加容易和快速——这对于那些处理不断更新或更大数据集的人来说特别有用。
要使用此方法，只需选择一个空白单元格（例如E2，或者你希望结果显示的任何地方），输入此公式，然后按Enter：
=UNIQUE(TOCOL(A2:C9,1))按下Enter后，范围A2:C9中的所有唯一值将自动溢出到公式下方的单元格中。这个功能特别高效——当源数据发生变化时，输出会动态更新，从而省去了手动刷新的步骤。
- TOCOL(A2:C9,1): 将多列的值范围转换为单列，自动移除空白单元格。
- UNIQUE(...): 提取每个值仅一次，提供干净的、去重的列表。
使用Kutools AI助手从多列中提取唯一值
如果你想要更简化的方法并减少手动操作，Kutools for Excel中的Kutools AI助手可以帮助你轻松地从多列中提取唯一值。如果你不熟悉公式或想避免公式错误的风险，这种方法尤其有价值。Kutools AI助手会解读你的指令并自动处理数据，这非常适合初学者和希望点击几下就能快速解决问题的用户。
安装后，点击Kutools AI > AI助手打开“Kutools AI助手”窗格：
- 在聊天框中输入你的请求，例如：“从范围A2:C9中提取唯一值，忽略空白单元格，并将结果放在E2开始：”
- 点击“发送”或按Enter，然后在AI分析请求后，只需点击“执行”即可运行。结果将立即出现在你的工作表中，就在你指示的确切位置。
提示：如果你的数据提取工作流有所不同或想要自然语言处理功能，这个解决方案非常有用。请记住，如果原始数据不完全一致，请仔细检查提取的列表是否有空白单元格，因为空白条目可能根据你的AI请求细节被包含或过滤掉。
使用数据透视表从多列中提取唯一值
数据透视表是另一种方便的提取唯一值的方法，特别是如果你喜欢使用可视化工具并希望汇总或进一步分析唯一项目，例如计数出现次数。这种方法简单直接，不需要公式。然而，它需要一些设置步骤和轻微的数据重新排列，特别是当涉及的列有不同标题时。
以下是使用数据透视表提取唯一值的建议过程：
1. 在数据左侧插入一个新的空白列。例如，如果数据从B列开始，插入新列A。这种调整有助于确保正确的范围合并。
2. 选择数据集内的任意单元格，按Alt + D，然后迅速按P启动“数据透视表和数据透视图向导”。在向导的第一步中，选择“多重合并区域”。这允许你将多个列中的值合并到一个汇总字段中。
3. 单击下一步，然后选择“为我创建单个页面字段”。此步骤将所有数据组织为单个组，以便更容易提取唯一值。
4. 在下一步中，选择整个数据范围（包括新的空白列），单击添加按钮将你的选择带入“所有范围”列表，然后单击下一步。
5. 在向导的最后一步，选择你希望放置数据透视表的位置（新工作表或现有工作表），然后单击完成以生成数据透视表报告。
6. 在新的数据透视表中，取消选中“选择要添加到报告的字段”部分中的所有字段以清除默认视图。
7. 最后，将“值”字段拖到行区域。数据透视表将以整齐的单列形式显示原始多列范围中的所有唯一值。
局限性： 数据需要初步整理，如果源数据集更新，你必须刷新数据透视表才能看到新的唯一值。
使用VBA代码从多列中提取唯一值
在需要自动化提取或处理大型不规则数据集的情况下，使用VBA（Visual Basic for Applications）代码可以提供快速且可重复使用的解决方案。这对于对Excel VBA编辑器有基本了解的用户，或对于希望最小化手动操作的重复任务来说非常理想。VBA还可以比数组公式更高效地处理大量数据。
1. 按Alt + F11打开VBA编辑器。在出现的“Microsoft Visual Basic for Applications”窗口中，点击插入>模块以添加一个新模块。
2. 在新模块中，粘贴以下代码：
VBA：从多列中提取唯一值
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. 按F5运行代码。对话框将提示你选择数据范围。选择所有相关列（包括任何空白单元格）。
4. 单击确定后，另一个提示询问在哪里输出唯一值。指定一个顶部单元格，你希望在那里列出结果（例如E2）。
5. 单击确定，宏将自动运行。所有唯一值将从你指定的位置开始出现。
- 如果在使用公式时收到#VALUE!或#SPILL!错误，请检查你的范围并确保输出区域清晰。
- 始终检查数据范围内的隐藏行或合并单元格，因为这些可能会影响唯一值提取的正确性。
- 数组和动态数组公式会随着更改自动更新，但高级筛选和数据透视表解决方案可能需要手动刷新或重新运行。
- 对于重复任务，考虑使用VBA自动化提取以保持一致性和速度。
- 在应用任何大规模提取或自动化程序之前备份你的数据，特别是在复杂的电子表格中。
更多相关文章：
- 统计列表中的唯一值和不同值的数量
- 假设你有一个很长的值列表，其中有一些重复项，你想要统计有多少唯一值（仅出现一次的值）或总计的不同值存在于某一列中，如左截图所示。本文解释了在Excel中统计唯一值和不同条目的有效方法。
- 在Excel中基于条件提取唯一值
- 假设你想要从B列中仅提取基于A列特定条件的唯一名称，产生如截图所示的结果。本教程演示了在提取唯一值时应用条件的方法。
- 仅允许Excel中的唯一值
- 如果你想在一个工作表列中仅允许唯一条目并防止重复值，本文介绍了在Excel中实施唯一性规则的实用技术。
- 在Excel中基于条件求和唯一值
- 例如，你可能需要根据相邻列中的名称，在“订单”列中仅对唯一值求和，如截图所示。本文讨论了结合唯一值和条件计算的方法。
最佳Office办公效率工具
| 🤖 | Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数…… |
| 热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入…… | |
| 高级LOOKUP：多条件VLookup|多值VLookup|多表查找|模糊查找…… | |
| 高级下拉列表：快速创建下拉列表 |依赖下拉列表 | 多选下拉列表…… | |
| 列管理器： 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域与列…… | |
| 特色功能：网格聚焦 |设计视图 | 增强编辑栏 | 工作簿及工作表管理器 | 资源库（自动文本） | 日期提取 | 合并数据 | 加密/解密单元格 | 按名单发送电子邮件 | 超级筛选 | 特殊筛选（筛选粗体/倾斜/删除线等）…… | |
| 15大工具集：12项 文本工具（添加文本、删除特定字符等）|50+种 图表 类型（甘特图等）|40+实用 公式（基于生日计算年龄等）|19项 插入工具（插入二维码、从路径插入图片等）|12项 转换工具（小写金额转大写、汇率转换等）|7项 合并与分割工具（高级合并行、分割单元格等）| …… |
通过Kutools for Excel提升您的Excel技能，体验前所未有的高效办公。 Kutools for Excel提供300多项高级功能，助您提升效率并节省时间。 点击此处获取您最需要的功能……
Office Tab为Office带来多标签界面，让您的工作更加轻松
- 支持在Word、Excel、PowerPoint中进行多标签编辑与阅读。
- 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
- 可提升50%的工作效率，每天为您减少数百次鼠标点击！
所有Kutools加载项，一键安装
Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。
- 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
- 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
- 协同更高效——提升Office应用间的整体工作效率
- 30天全功能试用——无需注册，无需信用卡
- 超高性价比——比单独购买更实惠