Vai al contenuto

Creare menu a tendina completamento automatico Excel

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Finale

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.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Finale

Se stai cercando come creare un menu a tendina statico, puoi dare un'occhiata a questo articolo.

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!

Una premessa: su Excel online è già possibile avere un completamento automatico dei menu a tendina senza dover utilizzare formule particolari, ma purtroppo questa funzionalità non è ancora disponibile nella versione desktop di Excel.

Creare un elenco a discesa su Microsoft Excel 365

Vediamo prima di tutto come si potrebbe creare un menu a tendina se utilizziamo Excel Online o Excel 365, che presentano funzioni molto utili per velocizzare il processo.

Abbiamo una tabella di partenza chiamata TBL_GIOCATORI dove troviamo due colonne:

  • NOME: in questa colonna troviamo i nomi dei giocatori
  • SQUADRA: in questa colonna troviamo le squadre dei singoli giocatori
CREARE_MENU_COMPLETAMENTO_AUTOMATICO_EXCEL_365_TABELLA_PARTENZA

Quello che vogliamo fare é:

  • Inserire nella cella B3 del foglio MENU_AUTOMATICO_365 una sequenza di lettere per trovare i giocatori corrispondenti che contengono quella combinazione
  • Avere un menu a tendina dinamico nel quale i nomi dei giocatori (solo quelli che contengono la combinazione) siano in ordine alfabetico crescente

In questo caso, inseriamo la combinazione "de" per visualizzare unicamente i giocatori che presentino questa combinazione:

CREARE_MENU_COMPLETAMENTO_AUTOMATICO_EXCEL_365_DETTAGLIO_DE

Vediamo come realizzarla nello specifico, tornando però nel foglio DB_365.

CREARE_MENU_COMPLETAMENTO_AUTOMATICO_EXCEL_365_DETTAGLIO

Inseriamo nella cella E2 del foglio DB_365 la seguente formula:

=DATI.ORDINA(FILTRO(TBL_GIOCATORI[NOME];VAL.NUMERO(RICERCA(MENU_AUTOMATICO_365!B3;TBL_GIOCATORI[NOME]))))

Vediamola nel dettaglio. VAL.NUMERO e RICERCA ci restituiscono VERO / FALSO a seconda che quanto inserito nella cella B3 del foglio MENU_AUTOMATICO_365 sia parte anche di un nome di giocatore (ad esempio, se inseriamo hi, il risultato sarà FALSO per James Harden - se avessimo inserito de, il risultato, sempre per James Harden, sarebbe stato VERO).

In questo caso stiamo considerando l'intera tabella, come vediamo da TBL_GIOCATORI[NOME], utilizzando la funzione FILTRO per filtrare unicamente i casi dei giocatori dove la condizione precedente sia VERO. Se il giocatore presenta, nel suo nome, la combinazione precedente, questa sarà inserita sul foglio - viceversa, il risultato non sarà presente. DATI.ORDINA ordina i risultati alfabeticamente, è giusto per semplificare la parte di revisione e inserimento.

Come notiamo, la formula risultante è un dynamic array, come si può vedere dal contorno blu elettrico. Queste formule partono da una sola cella per poi coprirne un numero variabile, in questo caso a seconda di combinazioni di caratteri più o meno comuni potremmo avere 1 giocatore possibile oppure 25, quindi ci serve che la struttura sia flessibile.

Come facciamo a riportare questo elenco nel menu a tendina?

CREARE_MENU_COMPLETAMENTO_AUTOMATICO_EXCEL_365_MENU_FORMULA

In questo caso ci basterà cliccare sulla cella B3 del foglio MENU_AUTOMATICO_365 > andare nel menu Dati > in Strumenti Dati selezionare Convalida Dati > Elenco e poi indicare che i valori possibili sono quelli risultanti dall'array precedente, cioè =DB_365!$E$2#

Bisogna assolutamente ricordarsi di inserire il # perchè indica la presenza di un array, altrimenti la formula non funzionerà.

Vediamo ora come trovare la squadra corrispondente al giocatore selezionato.

CREARE_MENU_COMPLETAMENTO_AUTOMATICO_EXCEL_365_MENU_CERCAX

Inseriamo nella cella D3 la formula:

=CERCA.X(B3;TBL_GIOCATORI[NOME];TBL_GIOCATORI[SQUADRA];"")

In questo caso prendiamo il valore B3, cioè il giocatore, per poi ottenere la squadra di riferimento. Qualora ci fosse un errore, la formula ci restituirebbe il valore vuoto ("").

A questo punto avremmo terminato, ma personalmente preferisco inserire un nome definito anzichè un array per semplificare l'inserimento dei valori e rendere il file maggiormente comprensibile anche a terzi.

Vediamo assieme come funziona, prima di tutto selezionando il nostro array nella cella E2 del foglio DB_365.

CREARE_MENU_COMPLETAMENTO_AUTOMATICO_EXCEL_365_MENU_DEFINIRE_NOME

Dovremo cliccare su Formule > Definisci nome per definire un nome a partire dal nostro array. Questa è una procedura standard per aiutare a evidenziare alcuni valori che si ripetono sull'intero foglio, ad esempio l'IVA, la tassazione o parametri che non variano.

CREARE_MENU_COMPLETAMENTO_AUTOMATICO_EXCEL_365_DEFINIRE_NOME_ARRAY

Possiamo chiamare il nostro nome Giocatori possibili, in questo caso sarà esattamente uguale all'array precedente, cioè =DB_365!$E$2#. Se poi volessimo riportare questo nuovo nome nel foglio di inserimento, basterà modificare e inserirlo nell'elenco al posto dell'array, aiutando notevolmente persone che hanno meno dimestichezza con Excel a capire quali siano i valori possibili per l'inserimento:

CREARE_MENU_COMPLETAMENTO_AUTOMATICO_EXCEL_365_MENU_NOME

Ma come funzionerebbe, se non avessimo una delle ultime versioni di Excel o non potessimo accedere a Internet? Vediamolo insieme.

Creare un elenco a discesa su Microsoft Excel

Il processo per creare un elenco a discesa dinamico, come in questo caso, è sicuramente molto più complesso su versioni precedenti a 365. Se utilizzi Excel spesso nella tua attività lavorativa, ti consiglio caldamente di passare alla versione 365 o di convincere l'azienda ad adottare 365!

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
Microsoft_Excel_Creare_Menu_Tendina_Automatico_Inizio

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.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Esempio

In D2 inseriamo la formula completa per trovare la squadra di appartenenza.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Indice_Confronta

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.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Sillaba

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.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Vero_Ricerca

=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.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Lista_Finale

=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.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Convalida_Finale

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.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_No_Errore

Il risultato finale dovrebbe essere un elenco di nomi già segmentati per la sillaba "de", come nell'immagine seguente.

Microsoft_Excel_Creare_Menu_Tendina_Automatico_Finale

Conclusione e download file per creare menu completamento automatico Excel

Abbiamo terminato! Nel caso volessi scaricare il file, puoi cliccare sul pulsante seguente.

12 commenti su “Creare menu a tendina completamento automatico Excel”

  1. 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

  2. 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

    1. 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

  3. 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

    1. 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

  4. 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.

    1. 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

  5. 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!).

    1. 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

  6. ciao Marco,
    ti chiedo una cortesia, se invece dove scrivo il nome del giocatore ad esempio volessi che mi venga proposto a seconda nelle prima lettere inserite un completamento automatico del nome, come si fa?
    grazie

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *