In questa guida scopriremo come creare un menu a tendina a completamento automatico con Microsoft Excel. Il nostro obiettivo è creare un menu a tendina, o convalida dati, che ci restituisca una lista di opzioni già profilata quando inseriamo alcuni caratteri nella cella di convalida.
Ad esempio, vogliamo selezionare i giocatori NBA di una lista solo se il loro nome contiene la sillaba "de", come nell'immagine seguente.
Se stai cercando come creare un menu a tendina statico, puoi dare un'occhiata a questo articolo.
Creare un elenco a discesa su Microsoft Excel
Abbiamo numerosi passaggi per arrivare a questo risultato. Nel caso volessi avere già la soluzione, puoi scaricare questo file:
Se invece vuoi capire meglio sul funzionamento del file, continua a leggere!
Nel foglio DB, abbiamo impostato due colonne:
- Colonna B: nome completo del giocatore, in B2 James Harden
- Colonna C: nome della squadra di appartenenza, in C2 Houston Rockets
Ci sono 50 giocatori NBA nell'elenco, quindi abbiamo già una buona base per il nostro menu a tendina.
In questo caso, abbiamo inserito il valore "Devin Booker" nella cella B3 di un altro foglio, chiamato "MENU_AUTOMATICO". Sarà la cella B3 a ospitare il nostro menu a tendina dinamico, cui è anche collegato il nome della squadra con un semplice INDICE CONFRONTA.
In D2 inseriamo la formula completa per trovare la squadra di appartenenza.
Come abbiamo detto, questa soluzione non ci soddisfa perchè è troppo standard. Per il momento, abbiamo solo inserito il nome cognome completo e non esiste un elenco dinamico come quello che stiamo per creare.
Ora inseriamo la sillaba "de" che sarà il nostro riferimento per l'articolo. Possiamo anche non inserire alcun convalida dati, per il momento.
Purtroppo non c'è un modo automatico per creare il menu a tendina dinamico. In questo caso, dobbiamo creare una colonna di supporto per tutti i giocatori se vogliamo far funzionare la formula. In questo caso, vogliamo che appaia VERO se quanto contenuto nella cella B3 del foglio MENU_AUTOMATICO, ovvero "de", sia anche contenuto nel nome del giocatore.
Nel primo caso, James Harden, abbiamo un risultato positivo. Nel secondo, Bradley Beal, un risultato negativo che viene indicato con FALSO.
=VAL.NUMERO(
VAL.NUMERO verifica se il risultato successivo sia VERO oppure FALSO. Può risultare fuorviante perchè non stiamo utilizzando numeri ma operando su testo, tuttavia è una delle funzioni più utilizzate per questi calcoli.
RICERCA(MENU_AUTOMATICO!$B$3;B2))
RICERCA verifica che il contenuto di B2 del foglio MENU AUTOMATICO sia contenuto nella cella B2, in questo caso James Harden. Stiamo quindi effettuando il calcolo necessario per distinguere i valori che ci servono e quelli che invece non fanno parte della selezione.
La formula viene trascinata per tutti gli elementi della lista, in questo caso fino a D51.
Ora, non ci resta che restituire i valori pari a VERO su un'altra colonna. In questo caso abbiamo utilizzato la colonna E dello stesso foglio.
Per scoprire come restituire valori duplicati da un intervallo, posso consigliarti di dare un'occhiata a questo articolo. La combinazione che si rende necessaria è INDICE + AGGREGA, in modo da avere un risultato dinamico e efficiente.
=SE.ERRORE(INDICE(B:B;
Sappiamo che vogliamo come risultato i nomi dei giocatori sulla colonna B, quindi lo indichiamo nella funzione INDICE.
AGGREGA(15;6;RIF.RIGA($B$2:$B$51)-RIF.RIGA($A$1)/(D:D=VERO());
Questa è la parte più complicata. AGGREGA 15;6 si rende necessario per spiegare che stiamo cercando i valori più piccoli (15) e senza errori (6). Più piccoli significa che la lista sarà restituita dall'alto verso il basso.
La parte con i due RIF.RIGA crea invece una matrice di numeri 1 a 50 e, sul tuo esempio, dovresti aggiornare il valore 51 con l'ultima riga della lista che utilizzerai. Puoi verificarlo cliccando F9 sull'intera sequenza, la differenza con $A$1 finale è utilizzata perchè il primo valore è sulla seconda riga in B2.
Infine, la parte con VERO() è importante perchè delimita i parametri che ci interessano. Quindi la formula ci sta dicendo di restituire i valori della colonna B solo se nella colonna D c'è il valore VERO. Attenzione a non inserire ="VERO" come condizione, poichè sarebbe errata. Ci aiuta comunque la funzione VERO che rende possibile questo calcolo.
RIF.RIGA(A1)));"")
Questo RIF.RIGA ci spiega invece che il primo valore sarà quello più in alto in assoluto e scorrendo la formula avremo RIF.RIGA(A2), RIF.RIGA(A3) etc. Quindi partiremo dal valore in assoluto più in alto e scorreremo verso il basso senza dover modificare la formula numerose volte.
La parte con "" finale è solo per evidenziare con vuoto nel caso ci fosse un errore, quindi è collegata con il SE.ERRORE iniziale.
A questo punto, abbiamo una lista di giocatori in ordine seguita da alcune celle vuote fino alla cella E51.
Ora non ci resta che collegare la cella B3 del foglio MENU_AUTOMATICO con la nostra nuova lista. Utilizzeremo una strategia per evitare la visualizzazione dei valori vuoti all'interno. Clicchiamo su B3 -> Dati -> Convalida Dati.
Vediamo quindi quale combinazione di funzioni inserire per ottenere la nostra lista dinamica:
=SCARTO(DB!$E$2;0;0;
La funzione SCARTO inizia da una cella prestabilita e, a seconda di alcune condizioni, si muove sul foglio per restituire ulteriori valori. In questo caso, stiamo segnalando che la partenza sarà E2 sul foglio DB, ovvero la posizione del primo risultato della nostra lista. In questo caso, James Harden.
I due 0 indicano che non intendiamo muoverci nè per righe nè per colonne. Ci mancano però altezza e larghezza:
CONTA.SE(DB!$D$2:$D$51;VERO()));1)
Altezza significa quanti valori vogliamo restituire della nostra lista e questo si evidenzia utilizzando le funzioni CONTA.SE e VERO. Contiamo tutti i valori che contengono VERO, quindi tutti i valori che contengano la sillaba "de". L'1 finale indica solo il fatto che siamo sulla stessa colonna.
Ricordiamoci, in questo caso, di evitare la visualizzazione dei messaggi di errore andando su Dati -> Convalida Dati -> Messaggi di errore ed eliminando la spunta, come visualizzato nell'immagine seguente.
Il risultato finale dovrebbe essere un elenco di nomi già segmentati per la sillaba "de", come nell'immagine seguente.
Abbiamo terminato! Nel caso volessi scaricare il file, puoi cliccare sul pulsante seguente.
Mi chiamo Marco Filocamo e mi occupo di consulenza / formazione su Microsoft Excel. Realizzo corsi di formazione Excel dedicati per aziende, supporto professionisti 1:1 a distanza con call mirate e collaboro con aziende offrendo servizi di consulenza quali creazione di business plan, dashboard di vendita e non solo.
L’articolo ti è piaciuto e vorresti approfondire la tua conoscenza di Excel? Contattami!
eccezionale esempio . Sto facendo un file del fantacalcio e volevo fare una cosa simile, ho diviso i giocatori in liste in base al ruolo e vorrei che quando ne acquistassi uno e lo aggiungo alla mia lista, nella cella affianco comparisse la squadra di appartenenza che è impostatata al fianco della lista giocatori
Ciao Umberto,
grazie mille!
In effetti sembra perfetto come esempio per quello che ti serve, in bocca al lupo per il fantacalcio allora 🙂
A presto
Marco
Ciao Marco
Ho provato il tuo file, l’articolo lo trovo molto interessante, ma dal tuo file in convalida dati vedo un errore.
Quale riferimento non corretto manca per far funzionare il completamento automatico?
Grazie per un tuo riscontro
Cordiali saluti
Andrea
Ciao Andrea,
grazie del commento e per la segnalazione! Quale versione di Excel utilizzi?
Se copi le formule dovrebbe funzionare ma ho allegato un nuovo file, puoi verificare se con questo funziona correttamente?
A presto
Marco
Ciao, ho provato come da indicazioni e sono riuscito a creare un menù a tendina. Però, i campi da considerare sono oltre 10k e quindi non è proprio comodo quando devi cercare nominativi che iniziano con le ultime lettere dell’alfabeto. Mi chiedevo quindi se fosse possibile creare un menù a tendina interattivo dove tuoi puoi cominciare a digitare alcune lettere ed Excel ti suggerisce alcuni risultati.
Grazie mille,
Daniele
Ciao Daniele,
il file che puoi scaricare dall’articolo fa proprio questo! Hai provato a inserire i tuoi valori nel file per adattarlo alle tue esigenze e vedere se funziona?
A presto,
Marco
Ti ringrazio infinitamente, questo articolo ed il file di esempio mi hanno permesso di trovare soluzione ad una cosa che dovevo risolvere.
Segnalo solo una cosa: ho applicato le formule in un file in cui le righe del DB erano più di 8000 e risultava spaventosamente lento nei calcoli.
Ho cercato in rete fino a trovare soluzione in una discussione su Excelvba.it
Le due ricerche su intera colonna nella formula INDICE + AGGREGA rallentano molto.
Ho verificato, mettendo $D$2:$D$10000 invece di D:D (e analogo per la B:B) va come una scheggia.
Ciao Stefano,
ti ringrazio molto per i complimenti e per lo spunto, in effetti non l’ho specificato nell’articolo perchè se l’utente si scorda di modificare nello stesso modo B:B e D:D (B1:B100 e D1:D100, per esempio) purtroppo salta tutto. Sicuramente se si riduce l’intervallo di riferimento, il file si velocizza notevolmente!
A presto e buona giornata,
Marco
Ciao Marco, è fantastico e può risolvere anche il mio problema. Purtroppo però non fa quello che speravo: ovvero, se io digito le prime lettere di quello che cerco, non mi compare istantaneamente il menù a tendina come quando (ad esempio) cerchi una funzione (hai presente?); al contrario, devi comunque cliccare sulla freccina per aprire il menù a tendina (che certamente riduce ai pochi elementi cercati), ma non è fico come quello davvero dinamico (cioè il vero completamento automatico!).
Ciao Luigi,
grazie per il tuo riscontro prima di tutto!
Diciamo che è una scelta voluta (nell’articolo) perchè quanto dici si può realizzare ma richiede una minima conoscenza / consapevolezza di VBA e ho evitato di andare troppo sul tecnico – puoi fare riferimento a questo articolo (se te la cavi con l’inglese), il risparmio è veramente minimo anche se sicuramente è l’opzione più gradevole in assoluto. Personalmente credo sia un po’ un rischio, soprattutto se sono file condivisi con terzi o in un ambiente business dove non sempre VBA è accettato / utilizzato.
A presto e buona giornata,
Marco