Excel: elenco a discesa con completamento automatico

Menù a discesa popolato tramite completamento automatico

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 05 Febbraio 2023
Categoria: Microsoft Office Excel Elenchi a discesa

In questo tutorial di Microsoft Excel vedremo come, digitando del testo in un elenco a discesa (o come altri lo chiamano, menu a tendina), sia possibile ottenere un completamento automatico delle voci in esso presenti, scrivendo semplicemente l’iniziale di una determinata voce.
Potrebbe interessarti anche come creare elenchi a discesa concatenati in Excel, ma ora concentriamo l'attenzione su come si crea un elenco a discesa con completamento automatico in Excel.

Sommario Excel: elenco a discesa con completamento automatico

• Introduzione al menu a tendina con completamenteo automatico
• Funzioni SCARTO, CONFRONTA e CONTA.SE
• Convalida dati
• Conclusione

Se ad esempio dal riferimento relativo C2 comincio a scrivere An, compare un elenco a discesa con tutto ciò che inizia con An nell’intervallo A2:A12, mentre se scrivo A esce tutto ciò che inizia con A. La ricerca non è case-sensitive, quindi è indifferente scrivere maiuscolo o minuscolo. Se invece digito un’iniziale non presente in quell’intervallo, non accade nulla in quando l’elenco a discesa in C2 non si popola.

Introduzione al menu a tendina con completamenteo automatico

Per realizzare questo utile trucco, sfrutterò la convalida dei dati di Excel presente nella scheda Dati, icona Convalida dati; una volta aperta la finestra, dalla voce di elenco Elenco, nella casella di testo Origine, scriverò una formula che andrà creata da zero, dato che non esiste nelle funzioni di Excel già disponibili (per saperne di più leggi la convalida dati in Excel).

Userò come base la funzione della libreria Ricerca e riferimento (la stessa del famoso CERCA.VERT per capirci) chiamata SCARTO, che presenta 5 argomenti e che ha il compito di restituire un riferimento a un intervallo costituito da un numero specificato di righe e colonne da un riferimento dato.

Funzioni SCARTO, CONFRONTA e CONTA.SE

La sintassi della funzione SCARTO è la seguente:
=SCARTO(Rif;Righe;Colonne;Altezza;Largh)

L’argomento:

• Rif è obbligatorio e rappresenta il riferimento da cui si vuole iniziare lo scostamento, un riferimento a una cella oppure a un intervallo di celle adiacenti
• Righe è obbligatorio e rappresenta il numero di righe, in alto o in basso, da usare come riferimento per la cella superiore sinistra del risultato
• Colonne è obbligatorio e rappresenta il numero di colonne, in alto o in basso, da usare come riferimento per la cella superiore sinistra del risultato
• Altezza è facoltativo e rappresenta l’altezza del risultato espressa in numero di righe; se omessa equivarrà all’altezza in Rif
• Largh è facoltativo e rappresenta la larghezza espressa in numero di colonne; se omessa equivarrà alla larghezza in Rif

Occorreranno anche le funzioni CONFRONTA (che fa parte della libreria Ricerca e riferimento) e CONTA.SE (che fa parte della libreria Statistica)

La funzione CONFRONTA (in altri contesti usata per esempio assieme a INDICE per ovviare al fatto che il CERCA.VERT non ricerca valori verso sinistra) restituisce la posizione relativa di un elemento di matrice che corrisponde a un valore specificato in un ordine specificato.
La sintassi della funzione CONFRONTA è la seguente:
=CONFRONTA(Valore;Matrice;Corrisp)
L’argomento

• Valore è obbligatorio e rappresenta il valore usato per cercare il valore voluto nella matrice, un numero, un testo o un valore logico oppure un riferimento a essi
• Matrice è obbligatorio e rappresenta un intervallo contiguo di celle che contengono i possibili valori da cercare, una matrice di valori o un riferimento a una matrice
• Corrisp è facoltativo e rappresenta un numero (-1, 0 oppure 1) che indica il valore da restituire

La funzione CONTA.SE, che fa parte delle funzioni di conteggio di Excel, conta il numero di celle in un intervallo che corrispondono al criterio dato.
La sintassi della funzione CONTA.SE è la seguente:
=CONTA.SE(Intervallo;Criterio)
L’argomento

• Intervallo è obbligatorio e rappresenta l’intervallo di celle di cui contare le celle non vuote
• Criterio è obbligatorio e rappresenta la condizione, in forma di numero, espressione o testo, che definisce le celle da contare.

Convalida dati

La convalida dei dati consente di limitare il tipo di dati o i valori immessi dagli utenti in una cella. Viene usata comunemente per creare un elenco a discesa.
Nella scheda Impostazioni, tramite l'opzione Consenti è possibile scegliere:

• Numero intero: limita la cella ad accettare solo numeri interi
• Decimale: limita la cella ad accettare solo numeri decimali
• Elenco: consente di selezionare dati dall'elenco a discesa
• Data: accetta solo l'immissione di date nelle celle
• Ora: limita la cella ad accettare solo valori temporali
• Lunghezza testo: limita la lunghezza del testo
• Personalizzato: consente di immettere una formula personalizzata

Nell'esempio del tutorial, dopo aver selezionato la cella dove inserire il Cognome da cercare, ad esempio C2, la funzione da introdurre nella Convalida dati, dalla voce di elenco Elenco, all’interno della casella di testo Origine sarà:
=SCARTO(A2;CONFRONTA(C2&"*";A2:A12;0)-1;;CONTA.SE(A2:A12;C2&"*"))

• A2 è il primo argomento ed è la cella iniziale dell’intervallo dei Cognomi
• CONFRONTA(C2&"*";A2:A12;0)-1 è il secondo argomento e serve per sapere di quante righe voglio ci sia il riferimento spostato, ad esempio se nella cella C2 mettessi la B, per cercare cognomi che iniziano con questa lettera, l’argomento dovrebbe calcolare il riferimento dell’intervallo dove inizia la lettera B. Ma dato che CONFRONTA non trova esattamente la lettera B nell’intervallo, ma cognomi che iniziano per B, ho bisogno che alla cella C2 sia concatenata la stringa del carattere jolly *, in modo da dire alla formula che sto cercando tutte le stringhe che iniziano per B, a prescindere dalla lunghezza, nella matrice A2:A12. Il terzo argomento, 0, fornisce la corrispondenza esatta (si può anche mettere FALSO); il -1 serve perché l’intervallo parte dalla riga 2 e non 1.
• ; è il terzo argomento e chiede quante colonne ci sono come riferimento spostato. Dato che non c’è n’è nessuna, metto semplicemente un ;
• CONTA.SE(A2:A12;C2&"*") è il quarto argomento e chiede quanto alto (nel senso di quante righe) è l’intervallo riguardante la lettera B (questo perché ho deciso di fare l’esempio mettendo B in C2); dunque vanno contate usando anche qui il carattere jolly
• Il quinto argomento chiede la larghezza, ma dato che è facoltativo e non occorre in questo esercizio, lo ometto

Per completare le impostazioni è necessario raggiungere la scheda Messaggio di errore, presente sempre in Convalida dati, e deselezionare la casella di controllo Mostra messaggio di errore quando i dati immessi non sono validi.

Conclusione

Abbiamo visto un'ottima soluzione per popolare dinamicamente un elenco a discesa digitando semplicemente l'iniziale o le iniziali delle voci senza ricorrerre al VBA o a tabelle esterne, ma condensando il tutto in una funzione SCARTO che nidifica in essa anche CONFRONTA e CONTA.SE, una soluzione certamente preferibile. Spero ti sia servito leggere Excel: elenco a discesa con completamento automatico, ma potrebbe interessarti un elenco a discesa tra file diversi!

Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri: