✅ Crea un elenco a discesa di Excel con suggerimenti di ricerca

Sommario

Elenco a discesa ricercabile in Excel

Usiamo tutti Google come parte della nostra routine quotidiana. Una delle sue caratteristiche è il suggerimento di ricerca, in cui Google agisce in modo intelligente e ci fornisce un elenco di suggerimenti durante la digitazione.

In questo tutorial imparerai come creare un elenco a discesa ricercabile in Excel, ovvero un elenco a discesa che mostrerà gli elementi corrispondenti durante la digitazione.

Di seguito è riportato un video di questo tutorial (nel caso in cui si preferisca guardare un video piuttosto che leggere il testo).

Elenco a discesa ricercabile in Excel

Ai fini di questo tutorial, sto utilizzando i dati dei primi 20 paesi per PIL.

L'intento è quello di creare un elenco a discesa Excel con un meccanismo di suggerimento di ricerca, in modo tale che mostri un menu a discesa con le opzioni di corrispondenza mentre digito nella barra di ricerca.

Qualcosa come mostrato di seguito:

Per seguire, scarica il file di esempio da qui

La creazione dell'elenco a discesa ricercabile in Excel sarebbe un processo in tre parti:

Configurazione della casella di ricerca.
Impostazione dei dati.
Scrivere un breve codice VBA per farlo funzionare.

Passaggio 1 - Configurazione della casella di ricerca

In questo primo passaggio, utilizzerò una casella combinata e la configurerò in modo che quando la digiti, il testo si riflette anche in una cella in tempo reale.

Ecco i passaggi per farlo:

Vai alla scheda Sviluppatore -> Inserisci -> Controlli ActiveX -> Casella combinata (controllo ActiveX).
- È possibile che tu non trovi la scheda sviluppatore nella barra multifunzione. Per impostazione predefinita, è nascosto e deve essere abilitato. Fai clic qui per sapere come ottenere la scheda sviluppatore nella barra multifunzione in Excel.
Sposta il cursore nell'area del foglio di lavoro e fai clic ovunque. Inserirà una casella combinata.
Fare clic con il pulsante destro del mouse sulla casella combinata e selezionare Proprietà.
Nella finestra di dialogo delle proprietà, apportare le seguenti modifiche:
- Selezione automatica della parola: falso
- Cella collegata: B3
- ListFillRange: DropDownList (creeremo un intervallo denominato con questo nome nel passaggio 2)
- Iscrizione partita: 2 - fmMatchEntryNessuno

(La cella B3 è collegata alla casella combinata, il che significa che tutto ciò che si digita nella casella combinata viene inserito in B3)

Vai alla scheda Sviluppatore e fai clic su Modalità progettazione. Ciò ti consentirà di inserire il testo nella casella combinata. Inoltre, poiché la cella B3 è collegata alla casella combinata, qualsiasi testo immesso nella casella combinata si rifletterà anche in B3 in tempo reale.

Passaggio 2 - Impostazione dei dati

Ora che la casella di ricerca è pronta, dobbiamo mettere a posto i dati. L'idea è che non appena digiti qualcosa nella casella di ricerca, mostra solo quegli elementi che contengono quel testo.

Per fare questo, useremo

Tre colonne di supporto.
Un intervallo denominato dinamico.

Colonna di supporto 1

Inserisci la seguente formula nella cella F3 e trascinala per l'intera colonna (F3: F22)

=--VAL.NUMERO(SEERRORE(RICERCA($B$3,E3,1),""))

Questa formula restituisce 1 quando il testo nella casella combinata è presente nel nome del paese a sinistra. Ad esempio, se si digita UNI, solo i valori per United States e United Kingdom sono 1 e tutti i valori rimanenti sono 0.

Colonna di supporto 2

Inserisci la seguente formula nella cella G3 e trascinala per l'intera colonna (G3: G22)

=SE(F3=1,CONTA.SE($F$3:F3,1),"")

Questa formula restituisce 1 per la prima occorrenza in cui il testo della casella combinata corrisponde al nome del paese, 2 per la seconda occorrenza, 3 per la terza e così via. Ad esempio, se si digita UNI, la cella G3 visualizzerà 1 come corrisponde a Stati Uniti e G9 visualizzerà 2 come corrisponde a Regno Unito. Il resto delle celle sarà vuoto.

Colonna di supporto 3

Inserisci la seguente formula nella cella H3 e trascinala per l'intera colonna (H3: H22)

=IFERROR(INDICE($E$3:$E$22,CONFRONTA(RIGHE($G$3:G3),$G$3:$G$22,0)),"")

Questa formula impila insieme tutti i nomi corrispondenti senza celle vuote tra di loro. Ad esempio, se si digita UNI, questa colonna mostrerebbe 2 e 9 insieme e il resto tutte le celle sarebbero vuote.

Creazione dell'intervallo con nome dinamico

Ora che le colonne di supporto sono a posto, dobbiamo creare l'intervallo denominato dinamico. Questo intervallo denominato farà riferimento solo a quei valori che corrispondono al testo immesso nella casella combinata. Useremo questo intervallo denominato dinamico per mostrare i valori nella casella a discesa.

Nota: Nel passaggio 1 abbiamo inserito DropDownList nell'opzione ListFillRange. Ora creeremo l'intervallo denominato con lo stesso nome.

Ecco i passaggi per crearlo:

Vai su Formule -> Gestione nomi.
Nella finestra di dialogo del gestore dei nomi, fai clic su Nuovo. Si aprirà una finestra di dialogo Nuovo nome.
Nel campo Nome inserisci DropDownList
Nel campo Riferito a, inserisci la formula: =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

Passaggio 3: mettere in funzione il codice VBA

Siamo quasi li.

La parte finale è scrivere un breve codice VBA. Questo codice rende dinamico il menu a discesa in modo tale da mostrare gli elementi/nomi corrispondenti mentre si digita nella casella di ricerca.

Per aggiungere questo codice alla cartella di lavoro:

Fare clic con il pulsante destro del mouse sulla scheda Foglio di lavoro e selezionare Visualizza codice.

Nella finestra VBA, copia e incolla il seguente codice:

Sub ComboBox1_Change privato () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Fine sottotitolo

Questo è tutto!!

Sei pronto con la tua barra di ricerca di tipo Google che mostra gli elementi corrispondenti mentre li digiti.

Per un aspetto migliore, puoi coprire la cella B3 con la casella combinata e nascondere tutte le colonne di supporto. Ora puoi metterti in mostra un po 'con questo fantastico trucco di Excel.

Per seguire, scarica il file da qui

Cosa ne pensi? Saresti in grado di utilizzare questo elenco a discesa dei suggerimenti di ricerca nel tuo lavoro? Fatemi sapere i vostri pensieri lasciando un commento.

Se ti è piaciuto questo tutorial, sono sicuro che ti piacerebbero anche i seguenti tutorial di Excel: