Da quando i menu a discesa Convalida dati sono stati aggiunti a Excel nel 1997, le persone hanno cercato di trovare un modo per modificare il secondo menu a discesa in base alla selezione nel primo menu a discesa.
Ad esempio, se scegli Frutta in A2, il menu a discesa in A4 offrirà Apple, Banana, Cherry. Ma se scegli Erbe da A2, l'elenco in A4 offrirebbe anice, basilico, cannella. Ci sono state molte soluzioni nel corso degli anni. L'ho coperto almeno due volte nel Podcast:
- Il metodo classico utilizzava molti intervalli denominati come mostrato nell'episodio 383.
- Un altro metodo ha utilizzato le formule OFFSET nell'episodio 1606.
Con il rilascio delle nuove formule di array dinamici in anteprima pubblica, la nuova funzione FILTER ci offrirà un altro modo per eseguire la convalida dipendente.
Di 'che questo è il tuo database di prodotti:
Usa una formula di =SORT(UNIQUE(B4:B23))in D4 per ottenere un elenco univoco delle classificazioni. Questo è un nuovo tipo di formula. Una formula in D4 restituisce molte risposte che si riverseranno in molte celle. Per fare riferimento alla gamma Spiller, dovresti usare al =D4#posto di =D4.
Seleziona una cella per contenere il menu Convalida dati. Scegli Alt + DL per aprire la convalida dei dati. Cambia Consenti in "Elenco". Specificare =D4#come origine dell'elenco. Nota che l'hashtag (#) è lo Spiller - significa che ti riferisci all'intera gamma Spiller.
Il piano è che qualcuno scelga una classificazione dal primo menu a discesa. Quindi, una formula di =FILTER(A4:A23,B4:B23=H3,"Choose Class First")in E4 restituirà tutti i prodotti in quella categoria. Notare che l'uso di "Scegli prima classe" come terzo argomento opzionale. Ciò impedirà un #VALORE! errore dalla visualizzazione.
Potrebbe esserci un numero diverso di elementi nell'elenco a seconda della categoria selezionata. L'impostazione della convalida dei dati che punta a =E4#si espanderà o si contrarrà con la lunghezza dell'elenco.
Guarda un video
Trascrizione del video
Learn Excel From, Podcast Episode 2248: Dependent Validation Using Arrays.
Beh ciao. Questo è stato affrontato due volte in precedenza sul podcast, come eseguire la convalida dipendente e quale convalida dipendente è che puoi scegliere, prima, una categoria e poi, in risposta, il secondo menu a discesa cambierà in solo il elementi di quella categoria e, prima, questo era complicato, e con i nuovi array dinamici che sono stati annunciati a settembre del 2018 … e questi sono in fase di lancio, quindi devi avere Office 365. In questo momento 10 ottobre, ho sentito che fanno parte di circa il 50% degli addetti ai lavori di Office, quindi li stanno implementando molto lentamente. Probabilmente sarà fino alla prima metà del 2019 prima che tu li riceva, ma ci consentirà di eseguire la convalida dipendente in modo molto più semplice.
Quindi, ho due formule qui. La prima formula è l'UNICA di tutte le classificazioni e l'ho inviata nel comando SORT. Quindi, questo mi dà 1 formula che restituisce 5 risultati e che vive in D4. Quindi, qui, dove voglio scegliere la convalida dei dati, io (DL - 1:09) … la SORGENTE sarà = D4 #. Quel # - lo abbiamo chiamato spiller - assicurati che restituisca tutti i risultati di D4. Quindi, se aggiungo una nuova categoria qui e questa cresce, D4 # raccoglierà quella quantità extra, va bene? (= ORDINA (UNICO (B4: B23)))
Quindi, quella prima convalida è abbastanza semplice, ma ora che sappiamo che abbiamo scelto CITRUS - questo sarà più difficile - voglio filtrare l'elenco nella colonna A dove l'elemento nella colonna B è uguale all'elemento scelto , Tutto a posto? Quindi, prima dobbiamo lasciare che scelgano qualcosa e poi, una volta che so che è AGRUMI, poi dammi LIME, ARANCIO e MANDARINO, sceglieranno qualcos'altro. BACCA. Controllalo. Le riviste scientifiche dicono che una banana è una bacca. Non sono d'accordo con quello. Non mi sembra una bacca ma non incolparmi. Sto solo, sai, usando Internet. BANANA, ELDERBERRY e LAMPONE.
Ora, sai, il problema con questo è che qualcuno verrà inizialmente qui senza aver scelto nulla, e, quindi in quel caso, abbiamo SCEGLI LA CLASSE PRIMA che è il terzo argomento che dice se non viene trovato nulla, va bene? Quindi, sai, in questo modo, se partiamo da questo scenario, la scelta sarà SCEGLIERE LA CLASSE PRIMA. L'idea è che scelgano CLASSE, VEGETALE, questo aggiornamento, e poi quegli elementi provengono da quella lista. La CONVALIDA DEI DATI qui, ovviamente, beh, questo è un altro spiller, = E4 # per farlo funzionare, va bene? Quindi, questo è fantastico. (= FILTER (A4: A23, B4: B23 = H3, "Choose Class First"))
Dai un'occhiata al mio libro Excel Dynamic Arrays. Questo è … sarà gratuito fino alla fine del 2018. Controlla il link nella descrizione di YouTube, come puoi scaricarlo, per questo stesso esempio più altri 29 esempi su come utilizzare questi elementi.
Bene, concludi per oggi. Gli array dinamici ci offrono un altro modo per eseguire la convalida dipendente. Se non sei su Office 365 e non li hai ancora, sentiti libero di tornare, suppongo, al video 1606 che mostra il vecchio modo per farlo.
Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Scarica il file Excel
Per scaricare il file excel: dipendente-validazione-utilizzando-arrays.xlsx
Per saperne di più sugli array dinamici, controlla gli array dinamici di Excel direttamente al punto.
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Non eliminare mai un file Excel senza prima eseguirne il backup."
Mike Alexander
