De când meniurile derulante Validare date au fost adăugate la Excel în 1997, oamenii au încercat să găsească o modalitate de a schimba al doilea meniu derulant pe baza selecției din primul meniu derulant.
De exemplu, dacă alegeți Fructe în A2, meniul derulant în A4 ar oferi Apple, Banana, Cireș. Dar dacă alegeți ierburi din A2, lista în A4 ar oferi anason, busuioc, scorțișoară. Au fost multe soluții de-a lungul anilor. L-am acoperit cel puțin de două ori în Podcast:
- Metoda clasică a folosit o mulțime de game denumite așa cum se arată în episodul 383.
- O altă metodă a folosit formule OFFSET în episodul 1606.
Odată cu lansarea noilor formule Dynamic Array în Public Preview, noua funcție FILTER ne va oferi un alt mod de a face validarea dependentă.
Spuneți că aceasta este baza dvs. de date cu produse:
Utilizați o formulă de =SORT(UNIQUE(B4:B23))în D4 pentru a obține o listă unică a clasificărilor. Acesta este un tip nou de formulă. O formulă din D4 returnează multe răspunsuri care se vor vărsa în multe celule. Pentru a vă referi la gama Spiller, ați folosi =D4#în loc de =D4.
Selectați o celulă pentru a menține meniul Validare date. Alegeți Alt + DL pentru a deschide validarea datelor. Schimbați Permiteți la „Listă”. Specificați =D4#ca sursă a listei. Rețineți că Hashtag (#) este Spiller - înseamnă că vă referiți la întreaga gamă Spiller.
Planul este ca cineva să aleagă o clasificare din primul meniu derulant. Apoi, o formulă din =FILTER(A4:A23,B4:B23=H3,"Choose Class First")E4 va returna toate produsele din acea categorie. Rețineți că utilizarea „Alegeți clasa întâi” ca al treilea argument opțional. Acest lucru va preveni un #VALUE! eroare de la apariție.
Ar putea exista un număr diferit de articole în listă, în funcție de categoria selectată. Configurarea validării datelor care indică spre se =E4#va extinde sau se va contracta cu lungimea listei.
Urmăriți videoclipul
Transcriere video
Aflați Excel De la, Podcast Episodul 2248: Validare dependentă folosind matrice.
Ei bine, hei. Acest lucru a fost abordat de două ori înainte pe podcast, cum să faci validarea dependentă și ce validare dependentă este să alegi, mai întâi, o categorie și apoi, ca răspuns, la al doilea meniu derulant se va schimba doar la articole din acea categorie și, înainte, acest lucru era complicat și cu noile matrice dinamice care au fost anunțate în septembrie 2018 … și acestea se desfășoară, așa că trebuie să aveți Office 365. Chiar acum 10 octombrie, am auzit că sunt în proporție de aproximativ 50% din cei din interiorul Office, așa că le lansează foarte încet. Probabil va fi până în prima jumătate a anului 2019 înainte de a le obține, dar ne va permite să facem validarea dependentă într-un mod mult mai ușor.
Deci, am două formule aici. Prima formulă este UNICA a tuturor clasificărilor și am trimis-o în comanda SORT. Deci, asta îmi dă o formulă care returnează 5 rezultate și care trăiește în D4. Deci, aici, unde vreau să aleg validarea datelor, voi (DL - 1:09) … SURSA va fi = D4 #. Acel # - îl numim spiller - asigurați-vă că returnează toate rezultatele din D4. Deci, dacă aș adăuga o nouă categorie aici și aceasta va crește, D4 # va ridica suma suplimentară, bine? (= SORT (UNIC (B4: B23)))
Deci, prima validare este destul de simplă, dar acum că știm că am ales CITRUS - acest lucru va fi mai dificil - vreau să filtrez lista din coloana A în care elementul din coloana B este egal cu elementul ales , bine? Deci, mai întâi trebuie să-i lăsăm să aleagă ceva și apoi, odată ce știu că este CITRUS, apoi să-mi dea LIME, ORANGE și TANGERINE, ei ar alege altceva. BUCURI. Verificați acest lucru. Revistele științifice spun că o banană este o boabă. Nu sunt de acord cu asta. Nu mă simte ca o boabe pentru mine, dar nu mă învinovăți. Doar știi, folosesc internetul. BANANĂ, Bătrân și RASPBERRY.
Acum, știți, problemele cu asta vor veni cineva inițial aici fără să fi ales nimic și, deci, în acest caz, avem ALEGE CLASA ÎNTÂI, care este acel al treilea argument care spune dacă nu se găsește nimic, bine? Deci, știți, în acest fel, dacă începem în acest scenariu, alegerea va fi ALEGEȚI CLASA ÎNTÂI. Ideea este să aleagă CLASA, VEGETABLE, această actualizare și apoi acele elemente provin din acea listă. VALIDAREA DATELOR aici, desigur, ei bine, acesta este un alt spiller, = E4 # pentru a face asta să funcționeze, bine? Deci, asta e grozav. (= FILTRU (A4: A23, B4: B23 = H3, „Alege clasa mai întâi”))
Consultați cartea mea Excel Dynamic Arrays. Acesta este … va fi gratuit până la sfârșitul anului 2018. Verificați linkul de jos din descrierea YouTube, cum îl puteți descărca, chiar pentru acest exemplu plus alte 29 de exemple de utilizare a acestor articole.
Ei bine, încheiați pentru azi. Tablourile dinamice ne oferă un alt mod de a face validarea dependentă. Dacă nu sunteți pe Office 365 și nu aveți încă acestea, nu ezitați să reveniți la, presupun, videoclipul 1606 care arată vechiul mod de a face acest lucru.
Vreau să vă mulțumesc că ați trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: dependent-validation-using-arrays.xlsx
Pentru a afla mai multe despre tablourile dinamice, consultați tablourile dinamice Excel direct până la punctul.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Nu ștergeți niciodată un fișier Excel fără a face mai întâi backup.”
Mike Alexander
