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!
Step #1: definire la origine del menu a tendina
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.
Step #2: impostare il foglio del 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.
Step #3: creare colonna di supporto per menu a completamento automatico su Excel
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.
Step #4: creare lista finale dinamica per menu a tendina
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.
Step #5: collegamento menu completamento automatico con foglio Excel
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.
Conclusione e download file per creare menu completamento automatico Excel
Abbiamo terminato! Nel caso volessi scaricare il file, puoi cliccare sul pulsante seguente.