Creare menu a tendina completamento automatico Excel

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.

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

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:

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

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?

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.

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.

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.

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 un elenco a discesa su Microsoft Excel 365 con origine variabile

Assumiamo di voler creare un menu a tendina con completamento automatico su Excel che sia diverso per ogni riga. In questo caso dobbiamo variare leggermente quanto fatto in precedenza lavorando sulla dimensione di riga, per esempio:

Lavoriamo su singola riga - quindi per il valore in B3, consideriamo:

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

Trascinando in basso la formula, possiamo avere un'origine dinamica per tutte le celle successive. MATR.TRASPOSTA non fa altro che trasferire su colonne diverse i risultati di FILTRO, che sarebbero invece su diverse righe.

L'importante è ricordarsi di modificare l'origine della lista, bloccando la colonna F ma non la riga 3:

Copiando e incollando verso il basso il nostro menu a tendina, questo diventerà F4, F5 e così via. Potremo quindi avere un'origine dinamica per diverse celle, come in questo esempio:

Non è proprio un metodo elegantissimo, ma potremmo anche nascondere le celle dove troviamo i singoli risultati per restituire comunque un'origine dinamica. Purtroppo non possiamo inserire direttamente la formula intera nella convalida dati perchè ci darebbe un errore, quindi dobbiamo fare questo passaggio intermedio.

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

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.