TOP
Elenchi a discesa collegati
Descrizione
In Excel puoi farlo rapidamente e facilmente creare un elenco a discesa , ma hai mai provato a creare un elenco a discesa dipendente? Esistono diversi modi per creare un elenco di questo tipo, quindi diamo un'occhiata a loro.
Metodo 1. Funzione INDIRECT
Questa tecnica si basa sull'applicazione della funzione INDIRECT, che può fare una cosa semplice: convertire il contenuto di qualsiasi cella specificata in un intervallo di indirizzi comprensibile da Excel. Cioè, se la cella contiene il testo " А1 ", la funzione fornirà come risultato un riferimento alla cella А1 . Se la cella contiene la parola " Auto ", la funzione genererà un riferimento all'intervallo denominato con il nome Auto eccetera.
Prendi, ad esempio, questo elenco di modelli di auto Toyota, Ford E Nissan :
Seleziona l'elenco completo dei modelli Toyota (da cell А2 e fino alla fine dell'elenco) e assegnare un nome a questo intervallo Toyota nel menu Inserisci - Nome - Assegna (Insert - Name - Define) . Quindi ripeteremo lo stesso con gli elenchi Ford E Nissan , specificando di conseguenza i nomi degli intervalli Ford E Nissan .
Quando assegni i nomi, ricorda che i nomi degli intervalli in Excel non devono contenere spazi, segni di punteggiatura e devono iniziare con una lettera. Pertanto, se ci fosse una lacuna in uno dei marchi automobilistici (ad es Ssang Yong), allora dovrebbe essere sostituito nella cella e nel nome dell'intervallo con un carattere di sottolineatura (es Ssang_Yong).
Ora creiamo il primo elenco a discesa per la scelta della marca automobilistica. Seleziona una cella vuota e apri il menu Dati: controlla (Data - Validation) , quindi dall'elenco a discesa Tipo di dati seleziona un'opzione Elenco e sul campo Fonte - evidenzia le celle con i nomi dei marchi (celle gialle nel nostro esempio). Dopo aver cliccato su ОК il primo elenco a discesa è pronto:
Creiamo ora un secondo elenco a tendina, nel quale verranno visualizzati i modelli della marca selezionata nel primo elenco. Proprio come nel caso precedente, seleziona una cella vuota e apri il menu Dati: controlla - ulteriore Elenco . Nel campo Fonte dovrai inserire la seguente formula:
=INDIRECT(F3)
Dove:
- F3 - l'indirizzo della cella con il primo elenco a discesa - sostituiscilo con il tuo.
Tutto. Dopo aver cliccato su OK il contenuto del secondo elenco verrà selezionato in base al nome dell'intervallo selezionato nel primo elenco.
Contro di questo metodo:
- Gli intervalli dinamici specificati dalle formule di tipo OFFSET non possono fungere da intervalli secondari (dipendenti). Puoi usarli per l'elenco primario (indipendente), ma l'elenco secondario deve essere definito in modo rigido, senza formule.
- I nomi degli intervalli secondari devono corrispondere agli elementi dell'elenco a discesa principale. Cioè, se contiene testo con spazi vuoti, questi dovranno essere sostituiti con sottolineature, ecc.
- È necessario creare manualmente molti intervalli denominati.
Metodo 2. Elenco corrispondenze (OFFSET e MATCH)
Questo metodo richiede un elenco ordinato di corrispondenze marca-modello del seguente tipo:
Per creare un elenco a tendina primario di marchi, puoi utilizzare il solito metodo sopra descritto, ovvero:
- Assegnare il nome dell'intervallo D1:D3 (es Marche )
- Selezionare nella scheda Dati (Data) squadra Verifica dei dati (Data validation)
- Seleziona un'opzione di controllo dall'elenco a discesa Elenco (List) e specificare come qualità Fonti (Source) = Marche oppure seleziona semplicemente le celle D1:D3 (se si trovano sullo stesso foglio dell'elenco).
Ma per un elenco dipendente di modelli, dovrai creare un intervallo denominato con la funzione OFFSET, che farà riferimento dinamicamente solo alle celle dei modelli di una determinata marca. Per questo:
- Premi Ctrl + F3 o usa il pulsante Gestore del nome (Name manager) sulla scheda Formule (Formulas) . Nelle versioni precedenti al 2003 si trattava di un comando di menu Inserisci - Nome - Assegna (Insert - Name - Define)
- Crea un nuovo intervallo denominato con qualsiasi nome (es Modelli ) e sul campo Collegamento (Reference) nella parte inferiore della finestra, inserisci manualmente la seguente formula:
=OFFSET( $A$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )
I riferimenti devono essere assoluti (con segni $ ). Dopo aver premuto Enter, i nomi dei fogli verranno aggiunti automaticamente alla formula.
La funzione OFFSET è in grado di emettere un riferimento a un intervallo della dimensione desiderata, spostato rispetto alla cella iniziale del numero specificato di righe e colonne. In una versione più comprensibile, la sintassi di questa funzione è la seguente:
=OFFSET( cella_iniziale ; spedizione_giù ; spedizione_destra ; range_size_in_rows ; range_size_in_columns )
COSÌ:
- cella_iniziale - prendiamo la prima cella della nostra lista, cioè A1 ;
- spedizione_giù - contiamo la funzione MATCH, che, in poche parole, restituisce il numero di serie della cella con la marca selezionata (G7) nell'intervallo dato (colonne E );
- spedizione_destra =1 , quindi vogliamo fare riferimento ai modelli nella colonna adiacente ( IN );
- range_size_in_rows - calcoliamo utilizzando la funzione COUNTIF, che è in grado di contare il numero di occorrenze nell'elenco (colonna A) dei valori di cui abbiamo bisogno - marche di automobili ( G7 );
- range_size_in_columns =1 , quindi abbiamo bisogno di una colonna con i modelli.
Il risultato dovrebbe essere qualcosa del genere:
Resta da aggiungere alla cella un elenco a discesa basato sulla formula creata G8 . Per questo:
- Seleziona una cella G8
- Selezionare nella scheda Dati (Data) squadra Verifica dei dati (Data validation) o nel menu Dati: controlla (Data - Validation)
- Seleziona l'opzione di controllo dall'elenco a discesa Elenco (List) e inserisci come Fonti (Source) segno è uguale al nome del nostro intervallo, ovvero = Modelli .
Articoli sull'argomento: