Come confrontare valori separati da virgola presenti in due celle e ottenere i duplicati o i valori univoci in Excel?
Come mostrato nello screenshot seguente, ci sono due colonne – Colonna1 e Colonna2; ogni cella della colonna contiene numeri separati da virgola. Per confrontare i numeri separati da virgola nella Colonna1 con il contenuto della cella corrispondente nella stessa riga della Colonna2 e restituire tutti i valori duplicati o univoci, cosa si può fare?
Questa guida ti presenta due metodi per svolgere questa operazione in modo semplice ed efficace.
Confrontare valori separati da virgola in due celle e restituire valori duplicati o univoci con formule
Questa sezione presenta due formule per confrontare i valori separati da virgola presenti in due celle e ottenere quelli duplicati o univoci tra di esse.
Nota: Le formule seguenti funzionano esclusivamente in Excel per 365. Se utilizzi altre versioni di Excel, prova il metodo VBA descritto di seguito.
Prendendo come esempio le due colonne sopra riportate, per confrontare i numeri separati da virgola nella Colonna1 con quelli presenti nella stessa riga della Colonna2 e ottenere i valori duplicati o univoci, procedi come segue.
Restituisci Valori duplicati
1. Seleziona una cella in cui visualizzare i numeri duplicati tra le due celle specificate, con valori separati da virgola; in questo caso, ho scelto la cella D2. Inserisci quindi la formula seguente e premi il tasto Invio. Seleziona la cella contenente la formula e trascina la sua maniglia di riempimento automatico verso il basso per ottenere i numeri duplicati tra le celle delle 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, «»)))
Restituisci valori univoci
Per ottenere i numeri univoci tra le due celle specificate, contenenti valori separati da virgola nella stessa riga, puoi utilizzare la formula seguente.
1. Seleziona una cella in cui visualizzare i numeri univoci; in questo caso, ho scelto la cella E2. Inserisci la formula seguente e premi il tasto Invio. Poi seleziona la cella con la formula e trascina la sua maniglia di riempimento automatico verso il basso per generare numeri univoci nelle celle delle righe successive.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Note:
Sblocca la magia di Excel con KUTOOLS AI
- Esecuzione intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici, il tutto con comandi semplici e intuitivi.
- Formule personalizzate: crea formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA in modo semplice e immediato.
- Interpretazione delle formule: Comprendi con facilità anche le formule più complesse.
- Traduzione del testo: Superate le barriere linguistiche direttamente nei vostri fogli di calcolo.
Confrontare due colonne con valori separati da virgola e restituire valori duplicati o univoci con VBA
La funzione definita dall'utente inclusa in questa sezione ti permette di confrontare i valori separati da virgola presenti in due celle specifiche e di ottenere i valori duplicati o univoci tra di esse. Procedi nel modo seguente.
Utilizzando lo stesso esempio sopra riportato, per confrontare i numeri separati da virgola nella Colonna1 con quelli nella stessa riga della Colonna2 e restituire valori duplicati o univoci, prova la funzione definita dall'utente descritta in questa sezione.
1. Nella cartella di lavoro aperta, premi i tasti Alt+F11per aprire la finestra di Microsoft Visual Basic, Applications.
2. Nella finestra di Microsoft Visual Basic, Applications, fai clic su Inserisci>Modulo, quindi copia il codice VBA seguente nella finestra del Modulo (Codice).
Codice VBA: Confrontare valori separati da virgola in due celle e restituire valori duplicati/univoci
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 nella finestra del Modulo (Codice), vai su Strumenti > Riferimenti per aprire la finestra Riferimenti – VBAProject, seleziona la casella Microsoft Scripting Runtime e fai clic sul pulsante OK.
4. Premi i tasti Alt+Q per chiudere la finestra di Microsoft Visual Basic, Applications.
5. Ora devi applicare due funzioni distinte per restituire, rispettivamente, i valori duplicati e quelli univoci da due celle contenenti valori separati da virgola.
Restituisci valore duplicato
Seleziona una cella in cui visualizzare i numeri duplicati; in questo esempio seleziono la cella D2, quindi inserisco la formula seguente e premo il tasto Invioper ottenere i numeri duplicati tra la cella A2 e B2.
Seleziona la cella contenente la formula e trascina la sua maniglia di riempimento automatico verso il basso per replicare i numeri nelle celle delle righe successive.
=COMPARE(A2,B2,TRUE)
Restituisci valori univoci
Seleziona una cella in cui visualizzare i numeri univoci; in questo esempio seleziono la cella E2, quindi inserisco la formula seguente e premo il tasto Invioper ottenere i numeri univoci compresi tra la cella A2 e la cella B2.
Seleziona la cella contenente la formula e trascina la sua maniglia di riempimento automatico verso il basso per generare numeri univoci nelle celle delle righe successive.
=COMPARE(A2,B2,FALSE)
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- 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 all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi