Cum se compară valorile separate prin virgulă în două celule și se returnează valori duplicate sau unice în Excel?
După cum se arată în captura de ecran de mai jos, există două coloane - Coloana1 și Coloana2, fiecare celulă din coloană conține numere separate prin virgulă. Pentru a compara numerele separate prin virgulă din Coloana1 cu conținutul celulei din același rând din Coloana2 și pentru a returna toate valorile duplicate sau unice, ce ați putea face?
Acest tutorial oferă două metode pentru a vă ajuta să îndepliniți această sarcină.
Comparați valorile separate prin virgulă în două celule și returnați valori duplicate sau unice cu formule
Această secțiune oferă două formule pentru a ajuta la compararea valorilor separate prin virgulă din două celule și pentru a returna valorile duplicate sau unice între ele.
notițe: Următoarele formule funcționează numai în Excel pentru 365. Dacă utilizați alte versiuni de Excel, încercați să utilizați metoda VBA de mai jos.
Luați ca exemplu cele două coloane de mai sus, pentru a compara numerele separate prin virgulă din Coloana 1 cu numerele separate prin virgulă din același rând al Coloanei 2 și pentru a returna valori duplicate sau unice, vă rugăm să procedați după cum urmează.
Returnează valori duplicate
1. Selectați o celulă pentru a scoate numerele duplicate între cele două celule specificate cu numere separate prin virgulă, în acest caz, selectez celula D2, apoi introdu formula de mai jos și apăsați butonul Intrați cheie. Selectați celula formulei și trageți-o Mâner de completare automată jos pentru a obține numerele duplicate între celulele din celelalte rânduri.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Returnează valori unice
Pentru a returna numerele unice dintre cele două celule specificate cu numere separate prin virgulă în același rând, următoarea formulă poate ajuta.
1. Selectați o celulă pentru a scoate numerele unice, în acest caz, selectez celula E2, apoi introdu formula de mai jos și apăsați butonul Intrați cheie. Selectați celula formulei și trageți-o Mâner de completare automată jos pentru a obține numerele unice dintre celulele din celelalte rânduri.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
notițe:
Comparați două coloane cu valori separate prin virgulă și returnați valori duplicate sau unice cu VBA
Funcția definită de utilizator furnizată în această secțiune ajută la compararea valorilor separate prin virgulă din două celule specificate și returnarea valorilor duplicate sau a valorilor unice între ele. Vă rugăm să faceți după cum urmează.
Luați același exemplu ca mai sus, pentru a compara numerele separate prin virgulă din Coloana 1 cu numerele separate prin virgulă din același rând al Coloanei 2 și pentru a returna valori duplicate sau unice, vă rugăm să încercați funcția definită de utilizator din această secțiune.
1. În registrul de lucru de deschidere, apăsați pe Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
2. În Microsoft Visual Basic pentru aplicații fereastră, faceți clic pe Insera > Moduleși copiați următorul cod VBA în fișierul Modul (Cod) fereastră.
Cod VBA: comparați valorile separate prin virgulă în două celule și returnați valori duplicate/unice
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. După lipirea codului în Modul (Cod) fereastra, du-te la clic Instrumente > Referinte pentru a deschide Referințe - VBAProject fereastră, verificați Runtime Microsoft Scripting apoi faceți clic pe OK butonul.
4. apasă pe Alt + Q tastele pentru a închide Microsoft Visual Basic pentru aplicații fereastră.
5. Acum trebuie să aplicați două funcții separat pentru a returna valorile duplicate și unice din două celule cu valori separate prin virgulă.
Returnează valoarea duplicată
Selectați o celulă pentru a scoate numerele duplicate, în acest exemplu, selectez celula D2, apoi introdu formula de mai jos și apăsați butonul Intrați pentru a obține numerele duplicate între celula A2 și B2.
Selectați celula formulei și trageți în jos mânerul de completare automată pentru a obține numerele duplicate între celulele din celelalte rânduri.
=COMPARE(A2,B2,TRUE)
Returnează valori unice
Selectați o celulă pentru a scoate numerele unice, în acest exemplu, selectez celula E2, apoi introdu formula de mai jos și apăsați butonul Intrați pentru a obține numerele unice dintre celula A2 și B2.
Selectați celula formulei și trageți mânerul de completare automată în jos pentru a obține numerele unice dintre celulele din celelalte rânduri.
=COMPARE(A2,B2,FALSE)
Cele mai bune instrumente de productivitate de birou
Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp. Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...
Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
