Skip to main content

如何從 Excel 的多個列中提取唯一值?

Author: Xiaoyang Last Modified: 2025-08-06
A screenshot of an Excel dataset containing multiple columns with some repeated values

如果您經常處理分散在 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 是列引用。根據需要調整這些引用以適應您自己的工作表佈局。
如果您的實際數據位於其他位置,別忘了更新這些範圍。

A screenshot showing how to use an array formula to extract unique values in Excel

2. 輸入公式後,不要只按 Enter,而是同時按 Ctrl + Shift + Enter 確認其為數組公式。正確執行時,公式欄中會出現大括號 {} 包圍您的公式。然後,從 E2 向下拖動填充柄。繼續拖動直到出現空白單元格,這表明沒有更多唯一值可供提取。這個過程確保所有唯一值都將顯示在目標列中。

A screenshot showing unique values extracted using the array formula in Excel

此公式的解釋:
  1. $A$2:$C$9:指定要檢查唯一值的整個單元格集合。
  2. 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)以防止意外選擇。
  3. MIN(...): 找到最低的索引號碼,有效地定位數據中下一個可用的唯一值的位置。
  4. TEXT(...,"R0C00"): 使用 R1C1 样式将索引转换为有效的单元格引用。
  5. INDIRECT(...): 将上述创建的单元格引用转换为数据范围中的值。
  6. &"": 强制将公式结果视为文本,确保没有格式化意外。
此方法适用于所有 Excel 版本。然而,正确使用数组公式(通过 Ctrl + Shift + Enter)非常重要,否则它们可能无法产生预期的结果。此外,对于大数据集,数组公式可能会降低计算速度,因此建议将其用于中等大小的表格以获得最佳性能。

 
使用适用于 Excel 365、Excel 2021 及更新版本的公式从多个列中提取唯一值

如果您使用 Excel 365、Excel 2021 或更新版本,则可以访问动态数组函数,这提供了一种更简单、更直观的方法来从多个列中提取唯一值。UNIQUETOCOL 函数使得跨列合并数据并一步消除重复项变得更加容易和快速——这对于处理不断更新或更大数据集的用户来说尤其有用。

要使用这种方法,只需选择一个空白单元格(例如,E2,或者您希望结果出现的任何地方),输入此公式,然后按 Enter

=UNIQUE(TOCOL(A2:C9,1))

按下 Enter 后,来自范围 A2:C9 的所有唯一值将自动溢出到公式下方的单元格中。这一功能特别高效——输出会随着源数据的变化动态更新,为您节省手动刷新步骤。

A screenshot showing the UNIQUE function in Excel extracting unique values from multiple columns

参数解释:
  • TOCOL(A2:C9,1): 将多个列中的值范围转换为单列,自动移除空白单元格。
  • UNIQUE(...): 仅提取每个值一次,提供一个干净的、无重复的列表。
提示:如果您的数据集可能会变化,使用这种动态解决方案可确保您始终拥有最新的唯一条目列表。此方法仅在 Microsoft 365、2021 及更新版本中可用——如果使用旧版本,请参考上面的数组公式。
如果遇到任何 #SPILL! 错误,请检查是否有合并单元格或现有数据阻止输出范围,因为动态数组需要公式单元格下方的清晰空间以显示所有结果。
 

使用 Kutools AI 助手从多个列中提取唯一值

如果您希望采用更简化的方法并减少手动操作,Kutools for Excel 中的 Kutools AI 助手可以帮助您轻松从多个列中提取唯一值。如果您不熟悉公式或希望避免公式错误的风险,这种方法尤其有价值。Kutools AI 助手会解释您的指令并自动处理数据,非常适合初学者和寻求点击几下即可快速解决的用户。

注意:要体验 Kutools AI 助手,请确保下载并安装 Kutools for Excel。Kutools 是一款用户友好的插件,具有广泛的自动化功能。

安装后,点击 Kutools AI > AI 助手以打开“Kutools AI 助手”窗格:

  1. 在聊天框中输入您的请求,例如:“从范围 A2:C9 中提取唯一值,忽略空白单元格,并将结果放置在 E2 开始:”
  2. 点击“发送”或按 Enter,然后在 AI 分析请求后,只需点击“执行”即可运行。结果将立即出现在您的工作表中,就在您指示的确切位置。

提示:如果您的数据提取工作流程有所不同,或者您希望使用自然语言处理功能,此解决方案非常有用。请记住,如果您的原始数据不完全一致,请仔细检查提取的列表是否有空白单元格,因为根据您的 AI 请求详情,空白条目可能会被包括或过滤掉。

A GIF demonstrating how Kutools AI Aide extracts unique values from multiple columns in Excel

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

使用数据透视表从多个列中提取唯一值

数据透视表是另一种方便的方法,用于提取唯一值,特别是如果您喜欢使用可视化工具并希望汇总或进一步分析唯一项(例如计数)。这种方法简单直接,不需要公式。但是,它需要一些设置步骤和轻微的数据重新排列,尤其是当涉及的列具有不同的标题时。

以下是使用数据透视表提取唯一值的建议过程:

1. 在数据左侧插入一个新的空白列。例如,如果您的数据从 B 列开始,则插入一个新 A 列。这种调整有助于确保正确的范围合并。

A screenshot showing the addition of a blank column before using a Pivot Table in Excel

2. 选择数据集中的任意单元格,按 Alt + D,然后迅速按 P 启动“数据透视表和数据透视图向导”。在向导的第一步中,选择“多重合并区域”。这允许您将众多列中的值合并为一个汇总字段。

A screenshot of the PivotTable and PivotChart Wizard with 'Multiple consolidation ranges' selected

3. 点击下一步,然后选择“为我创建一个单一页面字段。”这一步将所有数据组织为一个组,以便更容易提取唯一值。

A screenshot showing 'Create a single page field for me' selected in the Pivot Table Wizard

4. 在下一步中,选择整个数据范围(包括新的空白列),点击添加按钮将您的选择加入“所有范围”列表,然后点击下一步

A screenshot of the Pivot Table Wizard with data range selection

5. 在向导的最后一步中,选择您希望放置数据透视表的位置(新工作表或现有工作表),然后点击完成以生成数据透视表报告。

A screenshot showing where to place the Pivot Table report in Excel

6. 在新的数据透视表中,取消选中“选择要添加到报告的字段”部分中的所有字段以清除默认视图。

A screenshot of a created Pivot Table in Excel for unique value extraction

7. 最后,将“值”字段拖到区域。数据透视表将以整齐的单列形式显示原始多列范围中的所有唯一值。

A screenshot showing unique values extracted using a Pivot Table in Excel

优点: 这种方法简单,不需要公式知识,同时还可以通过内置的数据透视表功能(如计数、分组或过滤)进一步分析唯一项。
局限性: 数据需要初步整理,如果您的源数据集更新,您必须刷新数据透视表以查看新的唯一值。

使用 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 运行代码。将出现一个对话框提示您选择数据范围。选择所有相关列(包括任何带有空白单元格的列)。

A screenshot of the VBA prompt for selecting a data range in Excel

4. 点击确定后,另一个提示会询问您在哪里输出唯一值。指定一个顶部单元格,您希望在此列出结果(例如,E2)。

A screenshot of the VBA prompt for selecting the output cell in Excel

5. 点击确定,宏将自动运行。所有唯一值将从您指定的位置开始出现。

A screenshot showing unique values extracted using VBA in Excel

提示:如果您的数据集中包含大量空白或数据类型,请仔细检查输出是否有意外的重复或遗漏。还建议在运行 VBA 之前保存您的工作簿,尤其是如果您不熟悉宏的话。

故障排除和实用建议:
  • 如果您在使用公式时收到 #VALUE!#SPILL! 错误,请检查您的范围并确保输出区域为空。
  • 始终检查数据范围内的隐藏行或合并单元格,因为这些可能会影响唯一值提取的正确性。
  • 数组和动态数组公式会随着更改自动更新,但高级筛选和数据透视表解决方案可能需要手动刷新或重新运行。
  • 对于重复任务,考虑使用 VBA 自动化提取以保持一致性并提高速度。
  • 在应用任何大规模提取或自动化例程之前备份您的数据,尤其是在复杂的工作簿中。

更多相关文章:

  • 统计列表中的唯一值和不同值数量
  • 假设您有一个长列表,其中包含一些重复项,并且您想统计某一列中唯一值的数量(即只出现一次的值)或总的不同值数量,如左截图所示。本文介绍了在 Excel 中统计唯一值和不同条目的高效方法。
  • 仅允许在 Excel 中输入唯一值
  • 如果您希望在工作表列中仅允许唯一条目并防止重复值,本文介绍了一些实用技术来强制执行 Excel 中的唯一性规则。
  • 基于条件在 Excel 中汇总唯一值
  • 例如,您可能需要根据相邻列中的名称汇总“订单”列中的唯一值,如截图所示。本文讨论了结合唯一值和条件计算的方法。

最佳 Office 辦公效率工具

🤖 Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions
熱門功能查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入...
高級 LOOKUP多條件查找|多值查找|多表查找|模糊查找...
高級下拉列表快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ...
列管理器添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ...
精選功能網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)...
前15 大工具集12 款文本工具添加文本刪除特定字符,...)|50+ 種圖表 類型甘特圖,...)|40+ 實用 公式基於生日計算年齡,...)|19 款插入工具插入QR码按路徑插入圖片,...)|12 款轉換工具金額轉大寫匯率轉換,...)|7 款合併和分割工具高級合併行分割儲存格,...)| ...以及更多
使用 Kutools 支援你的語言——支援英語、西班牙語、德語、法語、中文及40 多種語言!

利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...


Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆

  • 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
  • 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
  • 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!