Come creare un elenco a discesa in Excel

L'elenco a discesa è uno strumento incredibilmente utile che può aiutare a rendere più confortevole il lavoro con le informazioni. Consente di contenere più valori in una cella contemporaneamente, con cui puoi lavorare, come con qualsiasi altro. Per selezionare quello che ti serve, fai clic sull'icona della freccia, dopodiché viene visualizzato un elenco di valori uXNUMXbuXNUMXbis. Dopo aver selezionato uno specifico, la cella viene riempita automaticamente con esso e le formule vengono ricalcolate in base ad esso.

Excel fornisce molti metodi diversi per generare un menu a discesa e, inoltre, consente di personalizzarli in modo flessibile. Analizziamo questi metodi in modo più dettagliato.

Processo di creazione dell'elenco

Per generare un menu a comparsa, fare clic sulle voci di menu lungo il percorso “Dati” – “Convalida dati”. Si aprirà una finestra di dialogo in cui è necessario trovare la scheda "Parametri" e fare clic su di essa se non è stata aperta prima. Ha molte impostazioni, ma l'elemento "Tipo di dati" è importante per noi. Di tutti i significati, "Elenco" è quello giusto.

Il numero di metodi con cui le informazioni vengono inserite nell'elenco a comparsa è piuttosto elevato.

1. Indicazione indipendente degli elementi dell'elenco separati da un punto e virgola nel campo "Sorgente" situato nella stessa scheda della stessa finestra di dialogo.
   

   

2. Indicazione preliminare dei valori. Il campo Sorgente contiene l'intervallo in cui sono disponibili le informazioni richieste.
   

   

3. Specifica di un intervallo denominato. Un metodo che ripete il precedente, ma è solo necessario nominare preliminarmente l'intervallo.
   

   

Ognuno di questi metodi produrrà il risultato desiderato. Diamo un'occhiata ai metodi per generare elenchi a discesa in situazioni di vita reale.

Sulla base dei dati della lista

Diciamo che abbiamo una tabella che descrive i tipi di frutti diversi.

Per creare un elenco in un menu a discesa basato su questo insieme di informazioni, è necessario effettuare le seguenti operazioni:

1. Seleziona la cella riservata per l'elenco futuro.
2. Trova la scheda Dati sulla barra multifunzione. Lì facciamo clic su "Verifica dati".
   

   

3. Trova la voce "Tipo di dati" e cambia il valore in "Elenco".
   

   

4. Nel campo che indica l'opzione "Sorgente", immettere l'intervallo desiderato. Si prega di notare che i riferimenti assoluti devono essere specificati in modo che durante la copia dell'elenco, le informazioni non si spostino.
   

Inoltre, esiste una funzione per generare elenchi contemporaneamente in più di una cella. Per ottenere ciò, dovresti selezionarli tutti ed eseguire gli stessi passaggi descritti in precedenza. Ancora una volta, è necessario assicurarsi che siano scritti riferimenti assoluti. Se l'indirizzo non ha il simbolo del dollaro accanto ai nomi delle colonne e delle righe, è necessario aggiungerli premendo il tasto F4 finché il segno $ non si trova accanto ai nomi delle colonne e delle righe.

Con registrazione manuale dei dati

Nella situazione precedente, l'elenco è stato scritto evidenziando l'intervallo richiesto. Questo è un metodo conveniente, ma a volte è necessario registrare manualmente i dati. Ciò consentirà di evitare la duplicazione delle informazioni nella cartella di lavoro.

Supponiamo di trovarci di fronte al compito di creare una lista contenente due possibili scelte: sì e no. Per svolgere il compito è necessario:

1. Fare clic sulla cella per l'elenco.
2. Apri "Dati" e trova la sezione "Controllo dati" a noi familiare.
   

   

3. Ancora una volta, seleziona il tipo "Elenco".
   

   

4. Qui è necessario inserire “Sì; No” come fonte. Vediamo che le informazioni vengono immesse manualmente utilizzando un punto e virgola per l'enumerazione.

Dopo aver fatto clic su OK, abbiamo il seguente risultato.

Successivamente, il programma creerà automaticamente un menu a discesa nella cella appropriata. Tutte le informazioni che l'utente ha specificato come elementi nell'elenco a comparsa. Le regole per creare un elenco in più celle sono simili alle precedenti, con l'unica eccezione che è necessario specificare le informazioni manualmente utilizzando un punto e virgola.

Creazione di un elenco a discesa utilizzando la funzione OFFSET

Oltre al metodo classico, è possibile utilizzare la funzione SMALTIMENTOper generare menu a tendina.

Apriamo il foglio.

Per utilizzare la funzione per l'elenco a discesa, è necessario effettuare le seguenti operazioni:

1. Seleziona la cella di interesse in cui desideri posizionare l'elenco futuro.
2. Aprire in sequenza la scheda "Dati" e la finestra "Convalida dati".
   

   

3. Scaletta". Questo viene fatto allo stesso modo degli esempi precedenti. Infine si utilizza la seguente formula: =SCARTO(A$2$;0;0;5). Lo inseriamo dove vengono specificate le celle che verranno utilizzate come argomento.

Quindi il programma creerà un menu con un elenco di frutta.

La sintassi per questo è:

=OFFSET(riferimento,offset_linea,offset_colonna,[altezza],[larghezza])

Vediamo che questa funzione ha 5 argomenti. Innanzitutto, viene fornito l'indirizzo della prima cella da compensare. I due argomenti successivi specificano quante righe e colonne devono essere sfalsate. Parlando di noi, l'argomento Altezza è 5 perché rappresenta l'altezza della lista. 

Elenco a tendina in Excel con sostituzione dati (+ tramite la funzione OFFSET)

Nel caso dato SMALTIMENTO consentito di creare un menu a comparsa situato in un intervallo fisso. Lo svantaggio di questo metodo è che dopo aver aggiunto l'elemento, dovrai modificare tu stesso la formula.

Per creare un elenco dinamico con supporto per l'inserimento di nuove informazioni, è necessario:

1. Seleziona la cella di interesse.
2. Espandi la scheda "Dati" e fai clic su "Convalida dati".
3. Nella finestra che si apre, seleziona nuovamente la voce “Elenco” e specifica la seguente formula come origine dati: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Fare clic su OK.

Questo contiene una funzione COUNTIF, per determinare immediatamente quante celle vengono riempite (sebbene abbia un numero molto maggiore di usi, lo scriviamo qui solo per uno scopo specifico).

Affinché la formula funzioni normalmente, è necessario tracciare se sono presenti celle vuote sul percorso della formula. Non dovrebbero esserlo.

Elenco a discesa con i dati di un altro foglio o file Excel

Il metodo classico non funziona se devi ottenere informazioni da un altro documento o anche da un foglio contenuto nello stesso file. Per questo, viene utilizzata la funzione INDIRETTI, che consente di inserire nel formato corretto un collegamento a una cella che si trova in un altro foglio o, in generale, a un file. Devi fare quanto segue:

1. Attiva la cella in cui posizioniamo l'elenco.
2. Apriamo la finestra che già conosciamo. Nello stesso punto in cui abbiamo indicato in precedenza le fonti per altri intervalli, nel formato è indicata una formula =INDIRETTO(“[Lista1.xlsx]Foglio1!$A$1:$A$9”). Naturalmente, al posto di List1 e Sheet1, puoi inserire rispettivamente i nomi del tuo libro e del foglio. 

Attenzione! Il nome del file è indicato tra parentesi quadre. In questo caso, Excel non sarà in grado di utilizzare il file attualmente chiuso come fonte di informazioni.

Va inoltre notato che il nome del file stesso ha senso solo se il documento richiesto si trova nella stessa cartella di quella in cui verrà inserito l'elenco. In caso contrario, è necessario specificare per intero l'indirizzo di questo documento.

Creazione di menu a discesa dipendenti

Un elenco dipendente è un elenco i cui contenuti sono influenzati dalla scelta dell'utente in un altro elenco. Supponiamo di avere una tabella aperta di fronte a noi che contiene tre intervalli, a ciascuno dei quali è stato assegnato un nome.

È necessario seguire questi passaggi per generare elenchi il cui risultato è influenzato dall'opzione selezionata in un altro elenco.

1. Crea il primo elenco con i nomi degli intervalli.
   

   

2. Nel punto di ingresso della sorgente, gli indicatori richiesti vengono evidenziati uno per uno.
   

   

3. Crea un 2° elenco a seconda del tipo di pianta che la persona ha scelto. In alternativa, se si specificano alberi nel primo elenco, le informazioni nel secondo elenco saranno “quercia, carpino, castagno” e oltre. È necessario annotare la formula al posto dell'input dell'origine dati =INDIRETTO(E3). E3 – cella contenente il nome dell'intervallo 1.=INDIRETTO(E3). E3 – cella con il nome della lista 1.

Ora è tutto pronto.

Come selezionare più valori da un elenco a discesa?

A volte non è possibile dare la preferenza a un solo valore, quindi è necessario selezionarne più di uno. Quindi è necessario aggiungere una macro al codice della pagina. Usando la combinazione di tasti Alt + F11 si apre l'editor di Visual Basic. E il codice è inserito lì.

Private Sub Worksheet_Change (ByVal Target come intervallo)

    On Error Resume Next

    If Not Intersect(Target, Range(«Е2:Е9»)) non è nulla e Target.Cells.Count = 1 Allora

        Application.EnableEvents = False

        Se Len (Target.Offset (0, 1)) = 0 Allora

            Obiettivo.Offset (0, 1) = Obiettivo

        altro

            Target.End (xlToRight) .Offset (0, 1) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

Affinché il contenuto delle celle venga mostrato di seguito, inseriamo il seguente codice nell'editor.

Private Sub Worksheet_Change (ByVal Target come intervallo)

    On Error Resume Next

    If Not Intersect(Target, Range(«Н2:К2»)) non è niente e Target.Cells.Count = 1 Allora

        Application.EnableEvents = False

        Se Len (Target.Offset (1, 0)) = 0 Allora

            Obiettivo.Offset (1, 0) = Obiettivo

        altro

            Target.End (xlDown) .Offset (1, 0) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

E infine, questo codice viene utilizzato per scrivere in una cella.

Private Sub Worksheet_Change (ByVal Target come intervallo)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) is Nothing e Target.Cells.Count = 1 Allora

        Application.EnableEvents = False

        newVal = Obiettivo

        Applicazione.Annulla

        oldval = bersaglio

        If Len (oldval) <> 0 And oldval <> newVal Then

            Target = Target & «,» & newVal

        altro

            Obiettivo = nuovo val

        End If

        Se Len (newVal) = 0, allora Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Gli intervalli sono modificabili.

Come creare un elenco a discesa con una ricerca?

In questo caso, è necessario inizialmente utilizzare un diverso tipo di elenco. Si apre la scheda "Sviluppatore", dopodiché è necessario fare clic o toccare (se lo schermo è touch) sull'elemento "Inserisci" - "ActiveX". Ha una casella combinata. Ti verrà chiesto di disegnare questo elenco, dopodiché verrà aggiunto al documento.

Inoltre, viene configurato tramite le proprietà, in cui viene specificato un intervallo nell'opzione ListFillRange. La cella in cui viene visualizzato il valore definito dall'utente viene configurata utilizzando l'opzione LinkedCell. Successivamente, devi solo annotare i primi caratteri, poiché il programma suggerirà automaticamente i possibili valori.

Elenco a tendina con sostituzione automatica dei dati

C'è anche una funzione che i dati vengono sostituiti automaticamente dopo che sono stati aggiunti all'intervallo. È facile farlo:

1. Crea un insieme di celle per l'elenco futuro. Nel nostro caso, questo è un insieme di colori. Lo selezioniamo.
   

   

2. Successivamente, deve essere formattato come tabella. È necessario fare clic sul pulsante con lo stesso nome e selezionare lo stile della tabella.
   

   

   

Successivamente, è necessario confermare questo intervallo premendo il pulsante "OK".

Selezioniamo la tabella risultante e le diamo un nome tramite il campo di input situato nella parte superiore della colonna A.

Ecco fatto, c'è una tabella e può essere utilizzata come base per un elenco a discesa, per il quale è necessario:

1. Seleziona la cella in cui si trova l'elenco.
2. Aprire la finestra di dialogo Convalida dati.
   

   

3. Impostiamo il tipo di dati su "Lista" e come valori diamo il nome della tabella tramite il segno =.
   

   

   

Tutto, la cella è pronta e al suo interno sono mostrati i nomi dei colori, come avevamo originariamente bisogno. Adesso puoi aggiungere nuove posizioni semplicemente scrivendole in una cella posta poco più in basso subito dopo l'ultima.

Questo è il vantaggio della tabella, che l'intervallo aumenta automaticamente quando vengono aggiunti nuovi dati. Di conseguenza, questo è il modo più conveniente per aggiungere un elenco.

Come copiare l'elenco a discesa?

Per copiare è sufficiente utilizzare la combinazione di tasti Ctrl + C e Ctrl + V. Quindi l'elenco a discesa verrà copiato insieme alla formattazione. Per rimuovere la formattazione, è necessario utilizzare un incolla speciale (nel menu di scelta rapida, questa opzione appare dopo aver copiato l'elenco), in cui è impostata l'opzione "condizioni sui valori".

Seleziona tutte le celle contenenti un elenco a discesa

Per eseguire questa attività, è necessario utilizzare la funzione "Seleziona un gruppo di celle" nel gruppo "Trova e seleziona".

Successivamente, si aprirà una finestra di dialogo, in cui è necessario selezionare le voci "Tutti" e "Questi stessi" nel menu "Convalida dati". Il primo elemento seleziona tutti gli elenchi e il secondo seleziona solo quelli simili a determinati.