如何從 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 中提取唯一值
- 假设您想根据 A 列中的特定条件从 B 列中提取唯一名称,如截图所示。本教程演示了在提取唯一值时应用条件的方法。
- 仅允许在 Excel 中输入唯一值
- 如果您希望在工作表列中仅允许唯一条目并防止重复值,本文介绍了一些实用技术来强制执行 Excel 中的唯一性规则。
- 基于条件在 Excel 中汇总唯一值
- 例如,您可能需要根据相邻列中的名称汇总“订单”列中的唯一值,如截图所示。本文讨论了结合唯一值和条件计算的方法。
最佳 Office 辦公效率工具
🤖 | Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions… |
熱門功能:查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入... | |
高級 LOOKUP:多條件查找|多值查找|多表查找|模糊查找... | |
高級下拉列表:快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ... | |
列管理器: 添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ... | |
精選功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)... | |
前15 大工具集:12 款文本工具(添加文本,刪除特定字符,...)|50+ 種圖表 類型(甘特圖,...)|40+ 實用 公式(基於生日計算年齡,...)|19 款插入工具(插入QR码,按路徑插入圖片,...)|12 款轉換工具(金額轉大寫,匯率轉換,...)|7 款合併和分割工具(高級合併行,分割儲存格,...)| ...以及更多 |
利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...
Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆
- 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
- 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
- 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!