Come usare CERCA.VERT per cercare e concatenare più valori corrispondenti in Excel?
Quando si utilizza CERCA.VERT in Excel, la funzione restituisce solitamente solo il primo valore corrispondente al criterio di ricerca specificato. Tuttavia, in molti scenari comuni può essere necessario recuperare e unire tutti i valori associati a una stessa chiave: ad esempio, elencare tutti gli studenti di una classe o tutti i prodotti appartenenti a una determinata categoria. Poiché la funzione CERCA.VERT standard non supporta questa operazione in modo nativo, ci si potrebbe chiedere come cercare e concatenare più risultati corrispondenti direttamente in un’unica cella. Di seguito sono illustrate diverse metodologie pratiche ed efficienti per raggiungere questo obiettivo, adatte a varie versioni di Excel e alle diverse preferenze degli utenti.
Cercare con CERCA.VERT e concatenare più valori corrispondenti in Excel
Cercare con CERCA.VERT e concatenare più valori corrispondenti con le funzioni TEXTJOIN e FILTRO
Se utilizzi Excel 365 o Excel 2021, la combinazione delle funzioni TEXTJOIN e FILTRO offre un approccio efficiente, basato su formule, per cercare con CERCA.VERT e concatenare tutti i valori corrispondenti. Questa soluzione è ideale per set di dati dinamici e in continua evoluzione, poiché il risultato si aggiorna automaticamente non appena cambiano i dati di origine. È particolarmente consigliata quando la tua versione di Excel supporta la funzione FILTRO, disponibile esclusivamente nelle versioni più recenti di Office.
Nella cella di destinazione, inserisci la seguente formula, quindi trascinala verso il basso per applicarla anche alle righe successive. Tutti i valori corrispondenti verranno estratti e combinati in un’unica cella. Guarda lo screenshot:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, «»)Questa parte della formula esamina ogni valore nell'intervallo $A$2:$A$16; se corrisponde al valore in D2, include il valore corrispondente da $B$2:$B$16 nella matrice dei risultati.
- $B$2:$B$16L'intervallo da cui verranno recuperati i valori corrispondenti.
- $A$2:$A$16=D2La condizione in base alla quale vengono selezionati i valori: verranno elaborati soltanto i record in cui l’intervallo $A$2:$A$16 corrisponde al contenuto della cella D2.
- TEXTJOIN(", ", TRUE, ...): Questa funzione prende l'output della funzione FILTRO (una matrice di corrispondenze) e li concatena in una singola stringa di testo, separata dal delimitatore specificato (virgola e spazio), ignorando automaticamente le voci vuote.
- ",": imposta virgola e spazio come separatore; puoi personalizzare questo simbolo in base alle tue esigenze, ad esempio utilizzando il punto e virgola o le interruzioni di riga.
- TRUE: Fa sì che le celle vuote vengano ignorate durante il processo di combinazione, garantendo un risultato ordinato e ben formattato.
Nota speciale: Questo metodo richiede Excel 365 o Excel 2021 e non è compatibile con le versioni precedenti (ad esempio Excel 2019, 2016 o versioni ancora più datate). Assicurati sempre di verificare la tua versione di Excel prima di applicare questa soluzione.
Suggerimento: Se il valore da cercare (ad esempio, D2) cambia o vengono aggiunti nuovi elementi corrispondenti nell’intervallo dati, il risultato si aggiorna automaticamente—senza alcun intervento aggiuntivo richiesto.
Limitazioni potenziali: Con set di dati molto grandi, il tempo di calcolo della formula potrebbe aumentare. Inoltre, assicurati che negli intervalli di ricerca o di risultato non siano presenti celle unite, poiché potrebbero causare errori nella formula.
Cercare con CERCA.VERT e concatenare più valori corrispondenti con Kutools per Excel
Se i metodi basati su formule integrate risultano troppo complessi o se la tua versione di Excel non supporta funzioni avanzate come TEXTJOIN e FILTRO, Kutools per Excel ti offre una soluzione grafica intuitiva e facile da usare. La funzione Ricerca uno-a-molti di Kutools ti permette di trovare e unire in modo rapido tutti i risultati corrispondenti in pochi semplici passaggi, rendendola ideale sia per principianti che per utenti esperti. Con Kutools non devi scrivere formule o codici complessi ed è particolarmente vantaggioso quando lavori con set di dati ampi o in continua evoluzione, che richiedono ricerche e aggregazioni frequenti.
Dopo aver installato Kutools per Excel, seguire i passaggi indicati di seguito:
Fare clic su Kutools > Super RICERCA.VERT > Ricerca uno-a-molti (restituisci più risultati) per aprire la finestra di configurazione. All’interno di questa finestra, puoi configurare rapidamente le impostazioni di ricerca e output seguendo questi semplici passaggi:
- Selezionare le celle di destinazione per i risultati concatenati e le celle contenenti i valori da cercare;
- Indicare l'intervallo della tabella che contiene sia la colonna delle chiavi di ricerca che quella dei risultati;
- Specificare quale colonna contiene le chiavi di ricerca (Colonna chiave) e quale colonna contiene i valori da concatenare (Colonna di restituzione);
- Fai clic sul pulsante OK per confermare le impostazioni ed elaborare i dati.
Risultato: Kutools visualizzerà ora tutti i valori corrispondenti concatenati nella cella di output selezionata. Guarda lo screenshot:
Questo metodo è altamente raccomandato per chi preferisce lavorare direttamente dall’interfaccia di Excel, senza dover ricorrere a formule o codici complessi. Riduce inoltre il rischio di errori nelle formule e aumenta la produttività nella gestione di attività ripetitive di ricerca e concatenazione.
Cercare con CERCA.VERT e concatenare più valori corrispondenti con una funzione definita dall’utente
Per gli utenti esperti di VBA (Visual Basic for Applications) o per chi utilizza versioni precedenti di Excel prive del supporto per le matrici dinamiche e la funzione FILTRO, è possibile creare una funzione personalizzata (UDF) che consenta una concatenazione flessibile di più risultati. Questo approccio è compatibile con tutte le versioni di Excel e può essere adattato in base a separatori personalizzati o condizioni specifiche.
1. Tenere premuti i tasti ALT + F11 per aprire la finestra di Microsoft Visual Basic for Applications.
2. Fare clic su Inserisci > Modulo, quindi incollare il codice seguente nella finestra del modulo.
Codice VBA: Cercare con CERCA.VERT e concatenare più valori corrispondenti in una cella
Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
Dim Cell As Range
Dim Result As String
Result = ""
For Each Cell In LookupRange
If Cell.Value = LookupValue Then
Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
End If
Next Cell
If Result <> "" Then
Result = Left(Result, Len(Result) - Len(Delimiter))
End If
ConcatenateMatches = Result
End Function
3. Salva e chiudi l'editor VBA. Torna al foglio di lavoro e utilizza questa UDF inserendo la formula: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) in una cella vuota dove desideri visualizzare il risultato. Trascina il quadratino di riempimento verso il basso per copiare la formula nelle altre celle, se necessario. Tutti i valori corrispondenti, basati su un determinato valore di ricerca, verranno restituiti e concatenati in un’unica cella, separati da virgola e spazio. Guarda lo screenshot:
- D2: Il valore da cercare all’interno del set di dati (ValoreRicerca).
- A2:A16: L’intervallo in cui la funzione cerca il valore da trovare (IntervalloRicerca).
- B2:B16: L’intervallo contenente i valori da concatenare quando il valore cercato corrisponde (IntervalloRestituito).
Cercare con CERCA.VERT e concatenare più valori corrispondenti con codice VBA
Per scenari che richiedono un uso ripetuto o per chi preferisce evitare l’impiego di funzioni personalizzate nelle celle del foglio di lavoro, è possibile ricorrere a una macro VBA pronta all’uso per concatenare direttamente i risultati. Questo approccio si rivela particolarmente efficace negli ambienti condivisi, dove non tutti gli utenti dispongono della stessa versione di Excel o degli stessi componenti aggiuntivi.
1. Fare clic su Strumenti per sviluppatori > Visual Basic per aprire l’editor VBA.
2. Nella finestra VBA, fai clic su Inserisci > Modulo, quindi incolla il seguente codice nel modulo:
Sub VLookupAndConcatenate()
Dim ws As Worksheet
Dim dataRange As Range, lookupRange As Range, resultRange As Range
Dim dict As Object
Dim i As Long, lastRow As Long
Dim lookupValue As Variant, result As String
Dim delimiter As String
delimiter = ", "
Set dict = CreateObject("Scripting.Dictionary")
Set ws = ActiveSheet
On Error Resume Next
Set dataRange = Application.InputBox( _
Prompt:="Please select the data range (contains lookup column and result column)", _
Title:="Select Data Range", _
Type:=8)
On Error GoTo 0
If dataRange Is Nothing Then Exit Sub
On Error Resume Next
Set lookupRange = Application.InputBox( _
Prompt:="Please select the lookup range (single column)", _
Title:="Select Lookup Range", _
Type:=8)
On Error GoTo 0
If lookupRange Is Nothing Then Exit Sub
On Error Resume Next
Set resultRange = Application.InputBox( _
Prompt:="Please select the starting cell for results output", _
Title:="Select Output Location", _
Type:=8)
On Error GoTo 0
If resultRange Is Nothing Then Exit Sub
resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
For i = 1 To dataRange.Rows.Count
lookupValue = dataRange.Cells(i, 1).Value
If Not dict.Exists(lookupValue) Then
dict.Add lookupValue, dataRange.Cells(i, 2).Value
Else
dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
End If
Next i
For i = 1 To lookupRange.Rows.Count
lookupValue = lookupRange.Cells(i, 1).Value
If dict.Exists(lookupValue) Then
resultRange.Cells(i, 1).Value = dict(lookupValue)
Else
resultRange.Cells(i, 1).Value = "Not Found"
End If
Next i
MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub
3. Fai clic sul pulsante 
per eseguire la macro. Ti verrà chiesto, tramite finestre di input, di selezionare l’intervallo dati, l’intervallo di ricerca e l’intervallo dei risultati. Il risultato concatenato apparirà immediatamente nelle celle di output selezionate.
Questo approccio basato su macro è particolarmente utile quando si eseguono frequentemente ricerche di concatenazione multiple con valori diversi, poiché evita di sovraccaricare il foglio di lavoro con chiamate a funzioni personalizzate.
È possibile modificare facilmente il delimitatore nel codice, se necessario, ed espandere la macro per esportare i risultati in una cella o in un file, adattandola al proprio flusso di lavoro.
È possibile concatenare più valori corrispondenti in Excel con diversi approcci, ciascuno dotato di vantaggi specifici a seconda del contesto. Che tu opti per formule con matrici dinamiche, componenti aggiuntivi come Kutools per Excel o soluzioni basate su VBA, potenzierai la tua capacità di analizzare e visualizzare in modo efficiente i dati raggruppati. In base alle dimensioni e alla complessità del tuo set di dati, valuta attentamente quale metodo garantisca le migliori prestazioni e la massima facilità di manutenzione per te o per il tuo team. Nelle operazioni quotidiane, assicurati della coerenza dei dati, evita errori di unione e verifica sempre gli intervalli di riferimento per ottenere risultati ottimali. Se riscontri errori nei calcoli delle formule, ricontrolla che gli intervalli siano allineati ai dati e che tu stia applicando il metodo di inserimento corretto in base alla versione di Excel in uso.
Per tecniche Excel più avanzate e una vasta gamma di guide pratiche passo-passo, visita la nostra ampia libreria di tutorial.
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