Come cercare e concatenare più valori corrispondenti in Excel?

Quando si utilizza la funzione CERCA.VERT in Excel, questa restituirà generalmente solo il primo valore corrispondente che trova per un determinato criterio di ricerca. Tuttavia, ci sono molte situazioni comuni in cui potrebbe essere necessario recuperare e combinare tutti i valori corrispondenti associati a una particolare chiave, come elencare tutti gli studenti di una classe o tutti i prodotti associati a una certa categoria. Poiché la funzione CERCA.VERT standard è limitata in questo senso, ci si può chiedere come sia possibile ottenere la capacità di cercare e concatenare più risultati corrispondenti in una singola cella. Di seguito, esploreremo diversi metodi pratici ed efficienti per svolgere questo compito, adatti a diverse versioni di Excel e preferenze degli utenti.

Cerca e concatena più valori corrispondenti in Excel

• Utilizzando le funzioni TESTO.UNISCI e FILTRO (Excel365/2021)
• Utilizzando Kutools per Excel
• Utilizzando una Funzione Definita dall'Utente
• Utilizzando il codice VBA per Concatenazioni Multiple

Cerca verticale e concatena più valori corrispondenti con le funzioni TESTO.UNISCI e FILTRO

Se stai utilizzando Excel 365 o Excel 2021, la combinazione delle funzioni TESTO.UNISCI e FILTRO offre un approccio basato su formule efficiente per cercare e concatenare tutti i valori corrispondenti. Questa soluzione è particolarmente adatta per set di dati dinamici e aggiornati, poiché aggiornerà automaticamente il risultato quando cambiano i dati di origine. È meglio applicata quando la tua versione di Excel supporta la funzione FILTRO, disponibile solo nelle versioni recenti di Office.

Nella cella di destinazione, inserisci la seguente formula, quindi trascina la formula verso il basso se desideri applicarla anche ad altre righe. Tutti i valori corrispondenti trovati verranno estratti e combinati in una sola cella. Vedi screenshot:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

Cerca verticale e concatena più valori corrispondenti con Kutools per Excel

Se trovi complicati i metodi basati su formule incorporate o la tua versione di Excel non supporta funzioni avanzate come TESTO.UNISCI e FILTRO, Kutools per Excel offre una soluzione grafica user-friendly. La funzione Ricerca uno-a-molti in Kutools ti consente di cercare e concatenare più risultati corrispondenti con pochi semplici passaggi, rendendola adatta sia per principianti che per utenti esperti. Con Kutools, non c'è bisogno di scrivere formule o codici complessi ed è particolarmente utile quando si lavora con grandi dataset variabili che richiedono ricerche e aggregazioni ripetute.

Dopo aver installato Kutools per Excel, segui i passaggi seguenti:

Clicca 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 usando i seguenti passaggi:

1. Seleziona le celle di output target per i risultati concatenati e le celle contenenti i valori che desideri cercare;
2. Indica l'intervallo della tabella che contiene sia la chiave di ricerca che le colonne dei risultati;
3. Specifica quale colonna contiene le chiavi di ricerca (Colonna Chiave) e la colonna i cui valori verranno concatenati (Colonna di Restituzione);
4. Clicca sul pulsante OK per confermare le impostazioni ed elaborare i dati.
   

Risultato: Kutools ora mostrerà tutti i valori corrispondenti e concatenati nella cella di output selezionata. Vedi screenshot:

Questo metodo è altamente raccomandato per coloro che preferiscono lavorare direttamente dall'interfaccia di Excel senza formule o codici complessi. Riduce anche la probabilità di errori nella formula e migliora la produttività nel gestire attività di ricerca e concatenazione ripetitive.

Cerca verticale e concatena più valori corrispondenti con una Funzione Definita dall'Utente

Per gli utenti esperti in VBA (Visual Basic for Applications), o quelli che utilizzano versioni precedenti di Excel che mancano di supporto per array dinamici o la funzione FILTRO, è possibile creare una funzione personalizzata definita dall'utente (UDF) per ottenere una concatenazione flessibile di più risultati. Questo metodo è universalmente compatibile con tutte le versioni di Excel e può essere adattato a particolari simboli separatori o condizioni.

1. Tieni premuti i tasti ALT + F11 per aprire la finestra Microsoft Visual Basic for Applications.

2. Clicca su Inserisci > Modulo, e incolla il seguente codice nella finestra Modulo.

Codice VBA: Cerca verticale e concatena 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. Ritorna al tuo foglio di lavoro e usa questa UDF inserendo la formula: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) in una cella vuota dove desideri ottenere il risultato. Trascina la maniglia di riempimento verso il basso per copiare la formula in altre celle se necessario. Tutti i valori corrispondenti basati su un valore di ricerca specifico verranno restituiti e concatenati in una cella, separati da una virgola e uno spazio. Vedi screenshot:

Cerca verticale e concatena più valori corrispondenti con il codice VBA

Per scenari che richiedono un uso ripetuto o per coloro che desiderano evitare funzioni personalizzate nelle celle del foglio di lavoro, è possibile utilizzare una macro VBA pronta per concatenare direttamente i risultati. Questo metodo funziona bene in ambienti condivisi dove non tutti gli utenti potrebbero avere la stessa versione o componenti aggiuntivi.

1. Clicca su Strumenti di Sviluppo > Visual Basic per aprire l'editor VBA.

2. Nella finestra VBA, clicca su Inserisci > Modulo, quindi incolla questo 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. Clicca il pulsante per eseguire la macro. Le caselle di input ti chiederanno di selezionare l'intervallo di dati, l'intervallo di ricerca e l'intervallo di risultati. Il risultato concatenato viene quindi visualizzato direttamente nelle celle di output selezionate.

Questo approccio con la macro è particolarmente utile se esegui frequentemente ricerche con concatenazione multipla con valori diversi, poiché evita di intasare il foglio di lavoro con chiamate UDF.

Puoi facilmente regolare il delimitatore nel codice se necessario, ed estendere la macro per inviare i risultati a una cella o un file in base al tuo flusso di lavoro.

La concatenazione di più valori corrispondenti in Excel è possibile utilizzando vari approcci, ognuno con benefici specifici a seconda della tua situazione. Che tu scelga formule con array dinamici, add-in come Kutools per Excel o metodi basati su VBA, migliorerai la tua capacità di analizzare e visualizzare i dati raggruppati in modo efficiente. A seconda della dimensione e della complessità del tuo dataset, considera quale approccio offre prestazioni ottimali e facilità di manutenzione per te o il tuo team. Nelle operazioni quotidiane, controlla la consistenza dei dati, evita celle unite e verifica gli intervalli di riferimento per ottenere i migliori risultati. Se incontri errori nei calcoli delle formule, ricontrolla che i tuoi intervalli corrispondano ai dati e che utilizzi il metodo di immissione corretto per la tua versione di Excel.

Per tecniche avanzate di Excel e una vasta gamma di guide pratiche, visita la nostra estesa libreria di tutorial.