Come trovare valori comuni in 3 colonne in Excel?
Lavorare con i dati in Excel comporta spesso il confronto di liste per identificare voci condivise o duplicate. Mentre confrontare due colonne per individuare valori comuni è un'attività frequente, ci sono situazioni in cui è necessario determinare quali valori compaiono contemporaneamente in tre colonne separate. Ad esempio, quando si consolidano dati di sondaggi, si uniscono registri di vendita o si analizzano voci duplicate su più liste, è importante estrarre con precisione l'insieme di elementi presenti in tutte e tre le colonne, come mostrato nello screenshot sottostante. Questo articolo introduce diversi metodi pratici per risolvere questo problema in Excel, consentendoti di individuare in modo efficiente e affidabile i valori comuni tra tre colonne, sia che tu preferisca formule o VBA.
Trova valori comuni in 3 colonne con formule matrice
Macro VBA per estrarre valori presenti in tutte e tre le colonne
Trova valori comuni in 3 colonne con formule matrice
Per trovare ed estrarre valori comuni tra tre colonne, puoi utilizzare formule matrice progettate per cercare elementi che compaiono in tutti gli intervalli selezionati. Questo è particolarmente utile nei set di dati in cui non vuoi fare affidamento su componenti aggiuntivi di Excel o strumenti esterni.
Inserisci questa formula matrice in una cella vuota dove desideri visualizzare il primo valore comune:
=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0,1),0))))Come utilizzare questa formula matrice:
- Dopo aver inserito la formula nella cella selezionata, premi Shift + Ctrl + Enter (non solo Invio). Excel racchiuderà la formula tra parentesi graffe per indicare che si tratta di una formula matrice.
- Trascina la formula lungo la colonna fino a quando non compaiono celle vuote. Questo elencherà tutti i valori condivisi dalle tre colonne, e le celle vuote indicheranno che non ci sono ulteriori corrispondenze.
Note e spiegazioni dei parametri:
- Se preferisci una formula matrice diversa, anche questa restituisce tutti i valori unici presenti in tutte e tre le colonne:
Ancora una volta, ricorda di premere Shift + Ctrl + Enter dopo aver digitato o incollato la formula.=INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0,1,0)=2,0,1),0)) - In queste formule:
- A2:A10, B2:B8, C2:C9 sono gli intervalli di dati in ciascuna delle tre colonne che desideri confrontare.
- E1 si riferisce alla cella direttamente sopra dove inizia la tua formula (per la logica di esclusione). Regola i riferimenti alle celle in base ai tuoi intervalli di dati effettivi e al luogo in cui desideri che appaiano i risultati.
- Questi metodi funzionano bene per set di dati di dimensioni moderate, ma possono diventare lenti su volumi molto grandi a causa delle richieste di calcolo delle formule matrice.
- Fai attenzione a non modificare gli intervalli di origine a metà lavoro, poiché ciò può portare a risultati inaccurati o errori nelle formule.
- Se il risultato include righe vuote, significa che tutti i valori comuni sono stati estratti e le celle rimanenti non hanno ulteriori intersezioni.
Sblocca la Magia di Excel con Kutools AI
- Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
- Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
- Interpretazione delle Formule: Comprendi facilmente formule complesse.
- Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.
Macro VBA per estrarre valori presenti in tutte e tre le colonne
Se preferisci un approccio automatizzato che non richieda l'inserimento o la copia di formule complesse, puoi utilizzare VBA di Excel per scorrere i tuoi dati e visualizzare solo quei valori che sono presenti in ciascuna delle tre colonne. Questo metodo è particolarmente utile per set di dati molto grandi o quando si lavora con intervalli dinamici, poiché VBA è più efficiente nel gestire attività ripetitive e criteri personalizzati.
1. Fai clic su Sviluppatore > Visual Basic per aprire l'editor VBA (se la scheda Sviluppatore non è visibile, puoi abilitarla tramite File > Opzioni > Personalizza Barra Multifunzione).
2. Nell'editor VBA, fai clic su Inserisci > Modulo per creare un nuovo modulo. Quindi incolla il codice qui sotto nella finestra del modulo:
Sub FindCommonValuesThreeColumns()
Dim dict1 As Object
Dim dict2 As Object
Dim dict3 As Object
Dim resultDict As Object
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim cell As Range
Dim outputRow As Long
Dim key As Variant
On Error Resume Next
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")
Set resultDict = CreateObject("Scripting.Dictionary")
' Prompt the user to select the three column ranges
Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)
' Store all unique values from each column into corresponding dictionaries
For Each cell In rngA
If Not dict1.exists(cell.Value) And cell.Value <> "" Then
dict1.Add cell.Value, 1
End If
Next
For Each cell In rngB
If Not dict2.exists(cell.Value) And cell.Value <> "" Then
dict2.Add cell.Value, 1
End If
Next
For Each cell In rngC
If Not dict3.exists(cell.Value) And cell.Value <> "" Then
dict3.Add cell.Value, 1
End If
Next
' Check which values exist in all three dictionaries
For Each key In dict1.keys
If dict2.exists(key) And dict3.exists(key) Then
resultDict.Add key, 1
End If
Next
' Output result to next empty column on the active sheet
outputRow = 1
For Each key In resultDict.keys
Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
outputRow = outputRow + 1
Next
MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub3. Nella finestra VBA, con il modulo selezionato, premi F5 o fai clic sul pulsante Esegui (▶) per eseguire il codice. Ti verrà chiesto, in sequenza, di selezionare ciascuno dei tre intervalli di colonne che desideri confrontare. Usa il mouse per evidenziare le celle appropriate durante ogni richiesta.
4. La macro elaborerà le tue selezioni e produrrà tutti i valori presenti in tutte e tre le colonne nella colonna vuota successiva a destra del tuo set di dati corrente, a partire dalla prima riga.
Questo metodo è efficiente quando si lavora con set di dati grandi o dinamici e può essere facilmente esteso a quattro o più colonne duplicando la logica del dizionario. Ricorda sempre di salvare la cartella di lavoro prima di eseguire macro, poiché le modifiche non salvate non possono essere annullate se desideri tornare indietro.
I 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 oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in