如何在 Excel 中比較兩個儲存格內的逗號分隔值，並傳回其中的重複值或唯一值？

作者Siluvia修改日期2024-11-12

如下圖所示，有兩欄——欄位 1 與欄位 2，每欄的每個儲存格皆包含以逗號分隔的數字。若要將欄位 1 中的逗號分隔數字與同一列欄位 2 的儲存格內容進行比對，並傳回所有重複值或唯一值，該如何操作？

本教學提供兩種方法，助您輕鬆完成此任務。

比較兩個儲存格中的逗號分隔值

使用公式比較兩個儲存格中的逗號分隔值，並傳回重複或唯一值

-—— 傳回重複值

-—— 傳回唯一值

使用 VBA 比較兩個儲存格中的逗號分隔值，並傳回重複或唯一值

-—— 傳回重複值

-—— 傳回唯一值

使用公式比較兩個儲存格中的逗號分隔值，並傳回重複或唯一值

本節提供兩種公式，協助您比較兩個儲存格中的逗號分隔值，並找出兩者之間的重複項目或唯一值。

注意：下列公式僅適用於 Excel for 365. 若您使用的是其他版本的 Excel，請嘗試下方的 VBA 方法。

以上述兩欄為例，若要比較欄位 1 與同一列欄位 2 中的逗號分隔數字，並傳回重複值或唯一值，請依照下列步驟操作。

範例資料

傳回重複值

1. 選取一個儲存格，用來輸出兩個指定儲存格之間以逗號分隔的重複數字。在此範例中，我選取儲存格 D2，接著輸入下方公式並按下 Enter 鍵。選取該公式儲存格，並將其自動填滿控制點向下拖曳，即可快速取得其他列儲存格之間的重複數字！

=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))

比較以傳回重複值

傳回唯一值

若要在同一欄中傳回兩個指定儲存格之間以逗號分隔的唯一數字，請使用下列公式。

1. 選取一個儲存格以輸出唯一數字，在此範例中我選擇儲存格 E2，接著輸入下方公式並按下 Enter 鍵。選取該公式儲存格，並將其自動填滿控制點向下拖曳，即可快速取得其他列對應的唯一數字！

=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))

比較以傳回唯一值

注意事項：

1) 上述兩個公式僅適用於 Excel for 365. 若您使用的 Excel 版本不是 Excel for 365，請嘗試下列 VBA 方法。

2) 待比較的儲存格必須位於同一列或同一欄中且彼此相鄰。

透過 KUTOOLS AI 解鎖 Excel 的神奇功能

智慧執行：透過簡易指令，輕鬆執行儲存格操作、分析資料，並建立圖表！
自訂公式：打造專屬公式，讓您的工作流程更順暢！
VBA 編碼：輕鬆撰寫並套用 VBA 程式碼，立即提升工作效率！
公式解析：輕鬆掌握複雜公式！
文字翻譯：輕鬆打破試算表中的語言隔閡！

運用 AI 驅動的工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

使用 VBA 比較兩個欄位中的逗號分隔值，並傳回重複或唯一值

本節所提供的使用者定義函數，可協助您比較兩個指定儲存格中的逗號分隔值，並傳回兩者之間的重複值或唯一值。請依照下列步驟操作。

沿用上述範例，若要將欄位 1 中的逗號分隔數字與同一列欄位 2 中的逗號分隔數字進行比較，並傳回重複或唯一值，請嘗試本節的使用者定義函數。

範例資料

1. 在已開啟的工作簿中，按下 Alt+F11 鍵以開啟 Microsoft Visual Basic for Applications 視窗。

2. 在 Microsoft Visual Basic for Applications 視窗中，按一下插入 > 模組，並將下列 VBA 程式碼複製到模組（程式碼）視窗中。

將程式碼複製並貼上到模組中

VBA 程式碼：比較兩個儲存格中的逗號分隔值，並傳回重複/唯一值

Private Function COMPARE(Rng1, Rng2 As Range, Op As Boolean)
'Updated by Extendoffice 20221019
    Dim R1Arr As Variant
    Dim R2Arr As Variant
    Dim Ans1 As String
    Dim Ans2 As String
    Dim Separator As String
    Dim d1 As New Dictionary
    Dim d2 As New Dictionary
    Dim d3 As New Dictionary
    Application.Volatile

    Separator = ", "
    
    R1Arr = Split(Rng1.Value, Separator)
    R2Arr = Split(Rng2.Value, Separator)
    
    Ans1 = ""
    Ans2 = ""
    
    For Each ch In R2Arr
        If Not d2.Exists(ch) Then
            d2.Add ch, "1"
        End If
    Next
    
    If Op Then
        For Each ch In R1Arr
            If d2.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans1 = Ans1 & ch & Separator
                End If
            End If
        Next
        If Ans1 <> "" Then
            Ans1 = Mid(Ans1, 1, Len(Ans1) - Len(Separator))
        End If
        COMPARE = Ans1
    Else
        For Each ch In R1Arr
            If Not d1.Exists(ch) Then
                d1.Add ch, "1"
            End If
        Next
        
        For Each ch In R1Arr
            If Not d2.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans2 = Ans2 & ch & Separator
                End If
            End If
        Next
        For Each ch In R2Arr
            If Not d1.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans2 = Ans2 & ch & Separator
                End If
            End If
        Next
        If Ans2 <> "" Then
            Ans2 = Mid(Ans2, 1, Len(Ans2) - Len(Separator))
        End If
        COMPARE = Ans2
    End If

End Function

3. 貼上程式碼模組（程式碼）視窗後，按一下工具 > 參考，開啟 參考 – VBAProject視窗，勾選 Microsoft Scripting Runtime 核取方塊，再按一下確定按鈕即可完成設定！

按一下 [工具] > [參考]，然後勾選 [Microsoft Scripting Runtime] 方塊

4. 按下 Alt+Q 鍵，即可關閉 Microsoft Visual Basic for Applications 視窗。

5. 現在您需要分別套用兩個函數，從兩個包含以逗號分隔值的儲存格中，傳回重複值與唯一值。

傳回重複值

選取一個儲存格以輸出重複數字，在此範例中我選取儲存格 D2，接著輸入下方公式並按下 Enter 鍵，即可取得儲存格 A2 與 B2 之間的重複數字。

選取該公式儲存格，並向下拖曳自動填滿控制點，即可取得其他列儲存格中的重複數字。

=COMPARE(A2,B2,TRUE)

使用公式傳回重複值

傳回唯一值

選取一個儲存格以輸出唯一數字，在此範例中我選取儲存格 E2，接著輸入下方公式並按下 Enter 按下鍵，即可取得儲存格 A2 與 B2 之間的唯一數字。

選取該公式儲存格，並向下拖曳自動填滿控制點，即可取得其他列儲存格之間的唯一數字。

=COMPARE(A2,B2,FALSE)

使用公式傳回唯一值

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多