Hur jämför man kommaseparerade värden i två celler och returnerar dubbla eller unika värden i Excel?
Som visas i skärmdumpen nedan finns det två kolumner - Kolumn1 och Kolumn2, varje cell i kolumnen innehåller kommaseparerade nummer. Vad kan du göra för att jämföra de kommaseparerade siffrorna i kolumn1 med cellinnehållet i samma rad i kolumn2 och returnera alla dubbletter eller unika värden?
Denna handledning innehåller två metoder som hjälper dig att utföra denna uppgift.
Jämför kommaseparerade värden i två celler och returnera dubbla eller unika värden med formler
Det här avsnittet innehåller två formler som hjälper dig att jämföra de kommaseparerade värdena i två celler och returnera de dubbla eller unika värdena mellan dem.
Anmärkningar: Följande formler fungerar bara i Excel för 365. Om du använder andra versioner av Excel, prova att använda VBA-metoden nedan.
Ta de två kolumnerna ovan som ett exempel, för att jämföra de kommaseparerade siffrorna i Kolumn1 med de kommaseparerade siffrorna i samma rad i Kolumn2 och returnera dubbla eller unika värden, gör så här.
Returnera dubbletter av värden
1. Välj en cell för att mata ut dubbletttalen mellan de två specificerade cellerna med kommaseparerade siffror, i det här fallet väljer jag cell D2, anger sedan formeln nedan och trycker på ange nyckel. Markera formelcellen och dra dess Autofyllhandtag ner för att få dubblettnumren mellan cellerna i de andra raderna.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Returnera unika värden
För att returnera de unika talen mellan de två angivna cellerna med kommaseparerade siffror på samma rad, kan följande formel hjälpa.
1. Välj en cell för att mata ut de unika siffrorna, i det här fallet väljer jag cell E2, anger sedan formeln nedan och trycker på ange nyckel. Markera formelcellen och dra dess Autofyllhandtag ner för att få de unika siffrorna mellan cellerna i de andra raderna.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Anmärkningar:
Jämför två kolumner med kommaseparerade värden och returnera dubbletter eller unika värden med VBA
Den användardefinierade funktionen som tillhandahålls i det här avsnittet hjälper till att jämföra de kommaseparerade värdena i två specificerade celler och returnera dubblettvärdena eller unika värden mellan dem. Vänligen gör enligt följande.
Ta samma exempel som ovan, för att jämföra de kommaseparerade siffrorna i Kolumn1 med de kommaseparerade siffrorna i samma rad i Kolumn2 och returnera dubbletter eller unika värden, pröva den användardefinierade funktionen i det här avsnittet.
1. I den inledande arbetsboken, tryck på andra + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.
2. I Microsoft Visual Basic för applikationer fönstret klickar Insert > Modulerna, och kopiera följande VBA-kod i Modul (kod) fönster.
VBA-kod: Jämför kommaseparerade värden i två celler och returnera dubbletter/unika värden
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 Function3. Efter att ha klistrat in koden i Modul (kod) fönster, gå till klicka verktyg > Referensprojekt att öppna Referenser - VBAProject fönstret, kontrollera Microsoft Scripting Runtime rutan och klicka sedan på OK knapp.
4. tryck på andra + Q för att stänga Microsoft Visual Basic för applikationer fönster.
5. Nu måste du använda två funktioner separat för att returnera de dubbla och unika värdena från två kommaseparerade värdeceller.
Returnera dubblettvärde
Välj en cell för att mata ut dubbletttalen, i det här exemplet väljer jag cell D2, anger sedan formeln nedan och trycker på ange för att få dubblettnumren mellan cell A2 och B2.
Välj formelcellen och dra dess Autofyll-handtag nedåt för att få dubbletttalen mellan cellerna i de andra raderna.
=COMPARE(A2,B2,TRUE)
Returnera unika värden
Välj en cell för att mata ut de unika talen, i det här exemplet väljer jag cell E2, anger sedan formeln nedan och trycker på ange för att få de unika siffrorna mellan cell A2 och B2.
Välj formelcellen och dra dess Autofyll-handtag nedåt för att få de unika siffrorna mellan cellerna i de andra raderna.
=COMPARE(A2,B2,FALSE)
Bästa kontorsproduktivitetsverktyg
Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid. Klicka här för att få den funktion du behöver mest...
Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare
- Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
- Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
- Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
