Come confrontare i valori separati da virgole in due celle e restituire valori duplicati o univoci in Excel?
Come mostrato nello screenshot qui sotto, ci sono due colonne: Colonna1 e Colonna2, ogni cella nella colonna contiene numeri separati da virgole. Per confrontare i numeri separati da virgola in Colonna1 con il contenuto della cella nella stessa riga di Colonna2 e restituire tutti i valori duplicati o univoci, cosa potresti fare?
Questo tutorial fornisce due metodi per aiutarti a svolgere questa attività.
Confronta i valori separati da virgole in due celle e restituisci valori duplicati o univoci con le formule
Questa sezione fornisce due formule per confrontare i valori separati da virgola in due celle e restituire i valori duplicati o univoci tra di loro.
Note:: Le seguenti formule funzionano solo in Excel per 365. Se utilizzi altre versioni di Excel, prova a utilizzare il metodo VBA di seguito.
Prendi le due colonne precedenti come esempio, per confrontare i numeri separati da virgola in Colonna1 con i numeri separati da virgola nella stessa riga di Colonna2 e restituire valori duplicati o univoci, procedi come segue.
Restituisce valori duplicati
1. Seleziona una cella per produrre i numeri duplicati tra le due celle specificate con numeri separati da virgola, in questo caso, seleziono la cella D2, quindi inserisci la formula di seguito e premi il tasto entrare chiave. Seleziona la cella della formula e trascinala Maniglia di riempimento automatico verso il basso per ottenere i numeri duplicati tra le celle nelle altre righe.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Restituisce valori univoci
Per restituire i numeri univoci tra le due celle specificate con numeri separati da virgole nella stessa riga, la seguente formula può essere d'aiuto.
1. Seleziona una cella per produrre i numeri univoci, in questo caso, seleziono la cella E2, quindi inserisci la formula di seguito e premi il tasto entrare chiave. Seleziona la cella della formula e trascinala Maniglia di riempimento automatico verso il basso per ottenere i numeri univoci tra le celle nelle altre righe.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Note:
Confronta due colonne con valori separati da virgole e restituisci valori duplicati o univoci con VBA
La funzione definita dall'utente fornita in questa sezione consente di confrontare i valori separati da virgole in due celle specificate e restituire i valori duplicati o i valori univoci tra di loro. Si prega di fare quanto segue.
Prendi lo stesso esempio di cui sopra, per confrontare i numeri separati da virgola in Colonna1 con i numeri separati da virgola nella stessa riga di Colonna2 e restituire valori duplicati o univoci, prova la funzione definita dall'utente in questa sezione.
1. Nella cartella di lavoro di apertura, premere il altro + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.
2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulie copia il seguente codice VBA nel file Modulo (codice) finestra.
Codice VBA: confronta i valori separati da virgola in due celle e restituisce valori duplicati/unici
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. Dopo aver incollato il codice nel file Modulo (codice) finestra, vai a fare clic Strumenti > Riferimenti per aprire il Riferimenti - VBAProject finestra, controlla il file Runtime di script Microsoft casella e quindi fare clic su OK pulsante.
4. premi il altro + Q i tasti per chiudere il file Microsoft Visual Basic, Applications Edition finestra.
5. Ora è necessario applicare due funzioni separatamente per restituire i valori duplicati e univoci da due celle con valori separati da virgola.
Restituisce un valore duplicato
Seleziona una cella per produrre i numeri duplicati, in questo esempio, seleziono la cella D2, quindi inserisci la formula qui sotto e premi il entrare chiave per ottenere i numeri duplicati tra le celle A2 e B2.
Seleziona la cella della formula e trascina verso il basso la sua maniglia di riempimento automatico per ottenere i numeri duplicati tra le celle nelle altre righe.
=COMPARE(A2,B2,TRUE)
Restituisce valori univoci
Selezionare una cella per produrre i numeri univoci, in questo esempio, seleziono la cella E2, quindi inserisco la formula sottostante e premo il entrare chiave per ottenere i numeri univoci tra le celle A2 e B2.
Seleziona la cella della formula e trascina verso il basso la sua maniglia di riempimento automatico per ottenere i numeri univoci tra le celle nelle altre righe.
=COMPARE(A2,B2,FALSE)
I migliori strumenti per la produttività in ufficio
Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Fai clic qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
- Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
