Skip to main content

Kutools for Office — 一套工具，五種功能。完成更多工作。

如何在Excel中串連唯一值？

Author Xiaoyang Last modified

在處理試算表時，經常會遇到需要從某列合併（串連）僅有唯一值的情況，或是編製總結唯一項目及其對應記錄的清單。處理重複項並呈現總結信息不僅能讓您的數據更整潔，還能使報告更加清晰且富有洞察力。在Excel中，有多種實用的方法可以實現這些目標，從使用內建函數到利用高級外掛程式或自訂代碼不等。本教程詳細探討了多種方法來串連唯一值以及列出唯一項目和其相關數據。所涵蓋的解決方案適用於不同的Excel版本和用戶偏好，幫助您為自己的情境選擇最佳方法。

僅串連列中的唯一值

列出唯一值並串連相對應的值

僅串連列中的唯一值

在進行Excel數據分析時，一項常見的任務是將列中的不同條目合併到單一儲存格中。這對於創建摘要報告、避免列表中的重複值或準備進一步處理的數據特別有用。選擇哪種方法取決於您的Excel版本、數據集大小以及您對公式或代碼的熟悉程度。以下方法解決了不同的需求，強調了每種方法的考慮因素，並提供了確保正確執行的實用技巧。

方法1：使用TEXTJOIN和UNIQUE函數

對於Excel365和Excel2021的用戶，TEXTJOIN和UNIQUE函數的引入使從列中組合唯一值變得既簡單又靈活。

當您的數據列是連續的並且您想快速將所有唯一項目合併到一個儲存格中，並選擇分隔符時，此解決方案最適合。它自動消除重複項，易於審核，並允許您根據需要更改範圍或分隔符。但是，請注意，此方法僅在最新的Excel版本中可用；舊版本不支持UNIQUE函數。

在要顯示結果的儲存格中，輸入以下公式（假設您的數據在A2:A18單元格中）：

=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))

apply TEXTJOIN and UNIQUE functions to concatenate unique values

此公式的解釋：
  • UNIQUE(A2:A18) 會過濾掉重複項，並僅返回範圍 A2:A18 中的唯一值。
  • TEXTJOIN(", ", TRUE, ...) 會將這些唯一值合併（串連）到一個儲存格中，並以逗號和空格分隔。TRUE 參數確保任何空白儲存格都會在串連中被忽略。

有用的提示與故障排除：

  • 確保您的Excel版本支持UNIQUE和TEXTJOIN函數。如果看到#NAME?錯誤，可能是因為您正在使用較舊的版本。
  • TEXTJOIN中使用的分隔符可以更改為您喜歡的任何內容，例如"; " 或 "|"。
  • 如果您在原始範圍內添加或刪除數據，公式會自動更新。
  • 為避免意外的額外空格或分隔符，請仔細檢查公式中的分隔符參數。

方法2：使用Kutools AI助手

當您需要一種更快、完全自動化的方法來串連唯一值——而不需要書寫公式——Kutools for Excel 的 "AI助手" 工具提供了一個實用的解決方案，可節省各技能水平用戶的時間。這種方法特別有助於不熟悉Excel高級公式的人，或者數據經常變化，需要重複操作的情況。

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

安裝 Kutools for Excel 後，通過點擊 "Kutools" > "AI助手" 打開 "Kutools AI助手" 面板來訪問此功能。

  1. 選擇包含您希望合併到單一儲存格中的值的儲存格，確保您的選擇符合您的數據意圖。
  2. 在聊天框中描述您的要求，例如，您可以鍵入：
    從選定範圍串連唯一值並用逗號分隔，將合併結果放入C2單元格中
  3. 按下 Enter 鍵或點擊 "發送" 按鈕。AI會分析您的請求，處理後，按下“執行”讓Kutools進行操作。結果將如所述返回。

注意事項和提示：

  • 檢查是否運行的是最新版本的Kutools以獲取所有AI功能。
  • 在文本命令中明確指出，包括分隔符和目標單元格，以便獲得最佳結果。
  • Kutools AI在大範圍或需要在各種數據集中重複的工作流程中特別高效。

方法3：使用用戶定義函數

對於需要高級靈活性、自定義分隔符或希望在多個工作簿中重用工具的用戶，使用VBA編寫用戶定義函數（UDF）是一種有效的方法來自動串連唯一值。此VBA解決方案兼容所有Excel版本，不受新函數可用性的限制。

  • 您應該在工作簿中啟用宏。
  • 將文件保存為"啟用宏"（.xlsm），以防未來繼續使用此VBA代碼。
  • 在運行新代碼之前，建議定期備份您的工作簿。

1. 按住ALT + F11打開Microsoft Visual Basic for Applications窗口。

2. 在VBA窗口中，點擊插入 > 模塊，然後複製並粘貼以下代碼：

VBA代碼：將唯一值串連到單一儲存格中：

Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
    Dim xCell As Range
    Dim xDic As Object
    Set xDic = CreateObject("Scripting.Dictionary")
    For Each xCell In xRg
        xDic(xCell.Value) = Empty
    Next
    ConcatUniq = Join$(xDic.Keys, xChar)
    Set xDic = Nothing
End Function

3. 返回您的工作表，在空白儲存格（例如C2）中輸入以下公式：

=ConcatUniq(A2:A18,",")

按下Enter確認。該儲存格將顯示指定範圍內的所有唯一值，並以逗號分隔。

concatenate unique values with vba code

  • 如果您的範圍不同，請據此調整A2:A18。
  • 如果需要不同的分隔符，請將公式中的","替換成您喜歡的符號（例如";"或|）。
  • 如果出現#NAME?錯誤，請檢查是否已啟用宏並且UDF名稱完全匹配。

提示：要在其他工作簿中重用此函數，請將VBA代碼複製到它們的模塊中。

方法4：使用高級Excel公式（替代方案）

在UNIQUE函數不可用的環境中（例如，在Excel 2016或Excel 2019中），您可以仍然使用經典IF、COUNTIF和TEXTJOIN函數陣列公式的更複雜組合來串連唯一值。這種方法可行，但由於其計算開銷，最好適用於較小的數據集。

1. 在目標儲存格（例如C2）中輸入以下陣列公式（輸入後按Ctrl+Shift+Enter而不是只按Enter）：

=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))

2. 如果您看到大括號{}出現在公式周圍，則表示公式已被正確輸入為陣列公式。該公式將返回從範圍A2:A18串連的唯一值，並以逗號分隔。

注意：此方法要求您調整範圍以匹配您的數據。對於非常大的範圍，計算時間可能會增加。如果您不熟悉陣列公式，請考慮嘗試上述VBA或外掛程式解決方案。

列出唯一值並串連相對應的值

在數據報告中，您可能不僅希望從某一列中提取唯一值，還希望將另一列中相對應的條目聚合或合併在一起。例如，整合每位銷售員銷售的所有產品，或編制與相同ID相關的所有條目。選擇正確的方法取決於數據的複雜性以及自動化、易用性或兼容性是否是您的優先考慮。

方法1：使用TEXTJOIN和UNIQUE函數

當您使用Excel 365或Excel 2021時，可以結合UNIQUE和FILTER函數與TEXTJOIN，實現一種穩健、完全基於公式的解決方案。此方法適用於這樣的情況：一個值可能與多個記錄相關，您希望得到一份由分隔符分隔的相關記錄列表。

1. 在空白列中，輸入以下公式以列出Column A中的所有唯一值：

=UNIQUE(A2:A17)

List unique values with formula

2. 現在，為了串連Column B中每個唯一條目的相對應值，在獨特值旁邊的下一列（例如，如果您的獨特值從D2開始，則為E2），輸入此公式並根據需要向下拖動：

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))

List unique and concatenate matched values with formula

此公式的解釋：
  • UNIQUE(A2:A17) 創建一個從Column A中提取的唯一項目陣列。
  • FILTER(B2:B17, A2:A17 = D2) 生成一個包含所有D2中唯一值的相對應Column B值的陣列。
  • TEXTJOIN(", ", TRUE, ...) 將這些相對應的值連接起來，並以逗號分隔。
  • 如果需要不同的分隔符，請相應地更改TEXTJOIN中的", "。
  • 為避免錯誤，請確保公式中的範圍長度相等，並且FILTER不會因缺少匹配而返回錯誤。
  • 隨著數據的變化，此方法會自動更新結果，因此適合用於動態摘要表格。

方法2：使用Kutools for Excel

Kutools for Excel 具有一個“高級合併行”工具，專門設計用於按唯一值對數據進行分組並將相對應的值與您選擇的分隔符合併。這對於想要圖形化解決方案且不熟練書寫公式或代碼的用戶來說非常適合。在處理大型數據集或需要頻繁重新分組時，例如在定期報告或持續數據維護中，這一工具尤為寶貴。

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

在進行更改之前，最好將原始數據複製到另一個位置以進行備份。然後按照以下步驟操作：

  • 選擇您希望組織的數據範圍。
  • 導航至 "Kutools" > "合併 & 分割" > "高級合併行"，如下圖所示：
    click Advanced Combine Rows feature of kutools
  • 在打開的對話框中：
    • 選擇要合併的重複列，將其設置為“主鍵”在“操作”列中。
    • 選擇您希望聚合的列（要串連的值）；在“操作”下的下拉列表中指定您喜歡的分隔符。
    • 點擊OK執行。
    specify the options in the dialog box

結果：

Kutools將根據您的設置重新組織數據，提取唯一條目並串連所有關聯的值。
List unique and concatenate matched values with kutools

  • 如果您犯了錯誤，使用Excel中的撤銷功能（Ctrl+Z）恢復。
  • 該過程適用於可能包含數百或數千條記錄的數據集，並支持各種分隔符。

方法3：使用VBA代碼

使用VBA腳本可以完全控制您提取和總結數據的方式。此方法兼容所有版本的Excel，特別適合定制工作流程、自動化或當UNIQUE或FILTER函數不可用時。如果數據結構經常變化，這個VBA解決方案可以輕鬆適應。

要使用下面的代碼，只需按照以下步驟操作：

1. 按ALT + F11打開VBA編輯器。

2. 轉到插入 > 模塊，然後將以下代碼粘貼到打開的模塊窗口中：

VBA代碼：列出唯一值並串連相對應的數據

Sub test()
    'updateby Extendoffice
    Dim xRg As Range
    Dim xArr As Variant
    Dim xCell As Range
    Dim xTxt As String
    Dim I As Long
    Dim xDic As Object
    Dim xOutputRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If xRg.Areas.Count > 1 Then
        MsgBox "Does not support multiple selections", , "Kutools for Excel"
        Exit Sub
    End If
    If xRg.Columns.Count <> 2 Then
        MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
    If xOutputRg Is Nothing Then Exit Sub
    xArr = xRg
    Set xDic = CreateObject("Scripting.Dictionary")
    xDic.CompareMode = 1
    For I = 1 To UBound(xArr)
        If Not xDic.Exists(xArr(I, 1)) Then
            xDic.Item(xArr(I, 1)) = xDic.Count + 1
            xArr(xDic.Count, 1) = xArr(I, 1)
            xArr(xDic.Count, 2) = xArr(I, 2)
        Else
            xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
        End If
    Next
    xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub

3. 按F5運行腳本。彈出窗口會要求您選擇數據範圍。確保您精確選擇兩列：第一列為唯一值，第二列為相對應的值。

vba code to select data range

4. 點擊OK，並選擇結果表應開始的第一個儲存格。

vba code to select cell to put the result

5. 點擊OK後，代碼將生成一個僅包含唯一值及其關聯串連數據的表格。

vba code to List unique and concatenate matched values

  • 如果收到有關列數的錯誤，請檢查您的選擇是否僅包含兩列。
  • 如果您的分隔符需要從逗號更改為其他符號，請根據需要調整代碼行xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2)。
  • 在運行新的VBA腳本之前，務必備份您的文件。

總之，Excel提供了多種方法來串連唯一值並合併相關數據。公式方法在現代Excel中快速且動態，而VBA和Kutools解決方案則提供了更廣泛的兼容性和更大的控制權。始終選擇適合您的數據規模、Excel版本和首選工作流程的方法。記住要在樣本數據上測試或備份您的工作，尤其是在嘗試基於腳本或批量操作時。欲了解更多Excel指南和進階技巧，請探索我們的完整教程系列。

最佳 Office 生產力工具

🤖 Kutools AI 助手：以智能執行為基礎，革新數據分析 ｜生成程式碼 ｜ 創建自訂公式 ｜ 分析數據並生成圖表 ｜ 調用 Kutools 增強函數…
熱門功能：查找、選取項目的背景色或標記重複值 ｜ 刪除空行 ｜ 合併列或單元格且不遺失數據 ｜ 四捨五入（免公式）...
高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
高級下拉列表：快速創建下拉列表 ｜依賴型下拉列表 ｜ 多選下拉列表...
列管理器：添加指定數量的列 ｜ 移動列 ｜ 切換隱藏列的顯示狀態 ｜ 比較區域及列...
精選功能：網格聚焦 ｜ 設計檢視 ｜ 增強編輯欄 ｜ 工作簿及工作表管理器 ｜ 資源庫（快捷文本） ｜ 日期提取器 ｜ 合併資料 ｜ 加密/解密儲存格 ｜ 按列表發送電子郵件 ｜ 超級篩選 ｜ 特殊篩選（篩選粗體/傾斜/刪除線...）...
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%，每日可幫您減少數百次鼠標點擊！

所有 Kutools 外掛，一次安裝

Kutools for Office 套裝整合了 Excel、Word、Outlook 和 PowerPoint 的外掛，外加 Office Tab Pro，非常適合需要跨 Office 應用程式協同作業的團隊。

Excel Word Outlook Tabs PowerPoint
  • 全合一套裝 — Excel、Word、Outlook及 PowerPoint 外掛 + Office Tab Pro
  • 一鍵安裝，一份授權 — 幾分鐘完成設置（支援 MSI）
  • 協同運作更順暢 — Office 應用間無縫提升生產力
  • 30 天全功能試用 — 無需註冊、無需信用卡
  • 最超值 — 一次購買，節省單獨外掛費用