如何比较两个单元格中的逗号分隔值并在 Excel 中返​​回重复值或唯一值？

-- 返回重复值

-- 返回唯一值

-- 返回重复值

-- 返回唯一值

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

最佳办公生产力工具

在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。 每天将您的工作效率提高50％，并减少数百次鼠标单击！