Ottieni più valori di ricerca in una singola cella (con e senza ripetizione)

Possiamo cercare e restituire più valori in una cella in Excel (separati da virgole o spazi)?

Questa domanda mi è stata posta più volte da molti miei colleghi e lettori.

Excel ha alcune formule di ricerca sorprendenti, come VLOOKUP, INDEX/MATCH (e ora XLOOKUP), ma nessuna di queste offre un modo per restituire più valori corrispondenti. Tutti questi funzionano identificando la prima corrispondenza e restituisce quella.

Quindi ho eseguito un po' di codifica VBA per creare una funzione personalizzata (chiamata anche funzione definita dall'utente) in Excel.

Aggiornare: Dopo che Excel ha rilasciato array dinamici e fantastiche funzioni come UNIQUE e TEXTJOIN, ora è possibile utilizzare una formula semplice e restituisce tutti i valori corrispondenti in una cella (trattato in questo tutorial).

In questo tutorial, ti mostrerò come farlo (se stai utilizzando l'ultima versione di Excel - Microsoft 365 con tutte le nuove funzioni), nonché un modo per farlo nel caso in cui utilizzi versioni precedenti ( utilizzando VBA).

Quindi iniziamo!

Cerca e restituisci più valori in una cella (usando la formula)

Se stai utilizzando Excel 2016 o versioni precedenti, vai alla sezione successiva in cui mostro come farlo utilizzando VBA.

Con l'abbonamento a Microsoft 365, il tuo Excel ora ha funzioni e caratteristiche molto più potenti che non erano presenti nelle versioni precedenti (come XLOOKUP, Dynamic Array, funzioni UNIQUE/FILTER, ecc.)

Quindi, se stai usando Microsoft 365 (precedentemente noto come Office 365), puoi usare i metodi trattati in questa sezione per cercare e restituire più valori in una singola cella in Excel.

E come vedrai, è una formula davvero semplice.

Di seguito ho un set di dati in cui ho i nomi delle persone nella colonna A e la formazione che hanno seguito nella colonna B.

Fare clic qui per scaricare il file di esempio e seguire

Per ogni persona, voglio scoprire quale formazione ha completato. Nella colonna D, ho l'elenco dei nomi univoci (dalla colonna A) e voglio cercare ed estrarre rapidamente tutta la formazione che ogni persona ha fatto e ottenerli in un unico set (separato da una virgola).

Di seguito è la formula che farà questo:

=TEXTJOIN(", ",VERO,SE(D2=$A$2:$A$20,$B$2:$B$20,""))

Dopo aver inserito la formula nella cella E2, copiala per tutte le celle in cui desideri i risultati.

Come funziona questa formula?

Permettetemi di decostruire questa formula e spiegare ogni parte in che modo si unisce ci dà il risultato.

Il test logico nella formula IF (D2=$A$2:$A$20) verifica se la cella del nome D2 è uguale a quella dell'intervallo A2:A20.

Passa attraverso ogni cella nell'intervallo A2: A20 e controlla se il nome è lo stesso nella cella D2 o meno. se è lo stesso nome, restituisce TRUE, altrimenti restituisce FALSE.

Quindi questa parte della formula ti darà un array come mostrato di seguito:

{VERO;FALSO;FALSO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}

Poiché vogliamo solo ottenere l'addestramento per Bob (il valore nella cella D2), dobbiamo ottenere tutto l'addestramento corrispondente per le celle che restituiscono TRUE nell'array sopra.

Ciò è facilmente realizzabile specificando [valore_se_vero] parte della formula IF come l'intervallo che ha l'addestramento. Ciò assicura che se il nome nella cella D2 corrisponde al nome nell'intervallo A2:A20, la formula IF restituirà tutto l'allenamento che quella persona ha seguito.

E ovunque l'array restituisca un FALSE, abbiamo specificato il valore [value_if_false] come "" (vuoto), quindi restituisce uno spazio vuoto.

La parte IF della formula restituisce l'array come mostrato di seguito:

{“Excel”;””;””;”PowerPoint”;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””}

Dove ha i nomi della formazione che Bob ha seguito e spazi vuoti dove il nome non era Bob.

Ora, tutto ciò che dobbiamo fare è combinare questi nomi di allenamento (separati da una virgola) e restituirli in una cella.

E questo può essere fatto facilmente utilizzando la nuova formula TEXTJOIN (disponibile in Excel2021-2022 ed Excel in Microsoft 365)

La formula TEXTJOIN accetta tre argomenti:

• il delimitatore - che è ", " nel nostro esempio, poiché voglio che l'addestramento sia separato da una virgola e uno spazio
• TRUE - che dice alla formula TEXTJOIN di ignorare le celle vuote e combinare solo quelle che non sono vuote
• La formula If che restituisce il testo che deve essere combinato

Se stai usando Excel in Microsoft 365 che ha già array dinamici, puoi semplicemente inserire la formula sopra e premere invio. E se stai usando Excel2021-2022, devi inserire la formula e tenere premuti i tasti Ctrl e Maiusc, quindi premere Invio

Fare clic qui per scaricare il file di esempio e seguire

Ottieni più valori di ricerca in una singola cella (senza ripetizione)

Poiché la formula UNICA è disponibile solo per Excel in Microsoft 365, non potrai utilizzare questo metodo in Excel2021-2022

Nel caso in cui ci siano ripetizioni nel tuo set di dati, come mostrato di seguito, devi modificare un po' la formula in modo da ottenere solo un elenco di valori univoci in una singola cella.

Nel set di dati sopra, alcune persone hanno seguito l'allenamento più volte. Ad esempio, Bob e Stan hanno seguito due volte la formazione su Excel e Betty ha seguito due volte la formazione su MS Word. Ma nel nostro risultato, non vogliamo che un nome di allenamento si ripeta.

Puoi usare la formula seguente per farlo:

=TEXTJOIN(", ",VERO,UNICO(SE(D2=$A$2:$A$20,$B$2:$B$20,"")))

La formula sopra funziona allo stesso modo, con una piccola modifica. abbiamo usato la formula SE all'interno della funzione UNICO in modo che nel caso ci siano ripetizioni nel risultato della formula se, la funzione UNICO lo rimuoverebbe.

Clicca qui per scaricare il file di esempio

Cerca e restituisci più valori in una cella (usando VBA)

Se stai utilizzando Excel 2016 o versioni precedenti, non avrai accesso alla formula TEXTJOIN. Quindi il modo migliore per cercare e ottenere più valori corrispondenti in una singola cella è utilizzare una formula personalizzata che è possibile creare utilizzando VBA.

Per ottenere più valori di ricerca in una singola cella, dobbiamo creare una funzione in VBA (simile alla funzione CERCA.VERT) che controlla ogni cella in una colonna e se viene trovato il valore di ricerca, lo aggiunge al risultato.

Ecco il codice VBA che può farlo:

'Codice di Sumit Bansal (https://trumpexcel.com) Funzione SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then Risultato = Risultato & " " & LookupRange.Cells(i, ColumnNumber) & "," End If Next i SingleCellExtract = Left(Result, Len(Result) - 1) Fine funzione

Dove mettere questo codice?

1. Apri una cartella di lavoro e fai clic Alt + F11 (questo apre la finestra Editor VBA).
2. In questa finestra dell'editor VBA, a sinistra, c'è un esploratore di progetti (dove sono elencate tutte le cartelle di lavoro e i fogli di lavoro). Fare clic con il pulsante destro del mouse su qualsiasi oggetto nella cartella di lavoro in cui si desidera che questo codice funzioni e andare su Inserisci -> Modulo.
3. Nella finestra del modulo (che apparirà sulla destra), copia e incolla il codice sopra.
4. Ora sei a posto. Vai a qualsiasi cella della cartella di lavoro e digita =Estratto di cella singola e collegare gli argomenti di input richiesti (ad esempio LookupValue, LookupRange, ColumnNumber).

Come funziona questa formula?

Questa funzione funziona in modo simile alla funzione CERCA.VERT.

Richiede 3 argomenti come input:

1. Valore di ricerca - Una stringa che dobbiamo cercare in un intervallo di celle.
2. Intervallo di ricerca - Un array di celle da cui dobbiamo recuperare i dati ($B3:$C18 in questo caso).
3. NumeroColonna - È il numero di colonna della tabella/array da cui deve essere restituito il valore corrispondente (2 in questo caso).

Quando usi questa formula, controlla ogni cella nella colonna più a sinistra nell'intervallo di ricerca e quando trova una corrispondenza, si aggiunge al risultato nella cella in cui hai utilizzato la formula.

Ricordare: Salva la cartella di lavoro come cartella di lavoro con attivazione macro (.xlsm o .xls) per riutilizzare questa formula. Inoltre, questa funzione sarebbe disponibile solo in questa cartella di lavoro e non in tutte le cartelle di lavoro.

Clicca qui per scaricare il file di esempio

Scopri come automatizzare noiose attività ripetitive con VBA in Excel. Iscriversi a Corso Excel VBA

Ottieni più valori di ricerca in una singola cella (senza ripetizione)

C'è la possibilità che tu possa avere ripetizioni nei dati.

Se usi il codice usato sopra, ti darà anche ripetizioni nel risultato.

Se vuoi ottenere il risultato dove non ci sono ripetizioni, devi modificare un po' il codice.

Ecco il codice VBA che ti darà più valori di ricerca in una singola cella senza ripetizioni.

'Codice di Sumit Bansal (https://trumpexcel.com) Funzione MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells(J, 1) = Lookupvalue Then If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then GoTo Skip End If End If Next J Risultato = Risultato & " " & LookupRange.Cells(i, ColumnNumber) & "," Salta: End If Next i MultipleLookupNoRept = Left(Result, Len(Result) - 1) End Funzione

Dopo aver inserito questo codice nell'editor VB (come mostrato sopra nel tutorial), sarai in grado di utilizzare il MultipleLookupNoRept funzione.

Ecco un'istantanea del risultato che otterrai con questo MultipleLookupNoRept funzione.

Clicca qui per scaricare il file di esempio

In questo tutorial, ho spiegato come utilizzare formule e VBA in Excel per trovare e restituire più valori di ricerca in una cella in Excel.

Anche se può essere fatto facilmente con una semplice formula se stai usando Excel nell'abbonamento a Microsoft 365, se stai usando versioni precedenti e non hai accesso a funzioni come TEXTJOIN, puoi comunque farlo usando VBA creando il tuo propria funzione personalizzata.