Odkar so bili spustni meniji za preverjanje veljavnosti podatkov dodani Excelu leta 1997, ljudje poskušajo najti način, da se drugi spustni meni spremeni na podlagi izbire v prvem spustnem meniju.
Če na primer izberete Sadje v A2, bo spustni meni A4 ponudil jabolka, banane in češnje. Če pa izberete Zelišča med A2, bo seznam v A4 ponudil janež, baziliko in cimet. V preteklih letih je bilo veliko rešitev. V Podcastu sem ga pokrival vsaj dvakrat:
- Klasična metoda je uporabila veliko imenovanih obsegov, kot je prikazano v epizodi 383.
- Druga metoda je uporabila formule OFFSET v epizodi 1606.
Z izdajo novih formul dinamičnega polja v Javnem predogledu nam bo nova funkcija FILTER omogočila še en način za odvisno preverjanje veljavnosti.
Recimo, da je to vaša baza podatkov o izdelkih:
Uporabite formulo =SORT(UNIQUE(B4:B23))iz D4, da dobite edinstven seznam klasifikacij. To je povsem nova vrsta formule. Ena formula v D4 vrne veliko odgovorov, ki se bodo razlili v številne celice. Če se želite sklicevati na območje Spiller, bi uporabili =D4#namesto =D4.
Izberite celico, v kateri bo meni za preverjanje veljavnosti podatkov. Izberite Alt + DL, da odprete preverjanje podatkov. Spremenite Dovoli na "Seznam". Navedite =D4#kot vir seznama. Upoštevajte, da je Hashtag (#) Spiller - to pomeni, da se sklicujete na celotno paleto Spillerjev.
V načrtu je, da bo nekdo izbral klasifikacijo v prvem spustnem meniju. Nato bo formula =FILTER(A4:A23,B4:B23=H3,"Choose Class First")iz E4 vrnila vse izdelke v tej kategoriji. Upoštevajte, da kot neobvezni tretji argument uporabite "Izberi razred najprej". To bo preprečilo #VALUE! napaka pri pojavu.
Na seznamu je lahko različno število elementov, odvisno od izbrane kategorije. Nastavitev preverjanja podatkov, ki kaže na, se =E4#bo razširila ali skrčila z dolžino seznama.
Oglejte si video
Video zapis
Learn Excel From, Podcast Episode 2248: Odvisno preverjanje veljavnosti s pomočjo nizov.
No, hej. To je bilo že dvakrat obravnavano v podcastu, kako narediti odvisno validacijo in kaj odvisno validacijo je, da najprej izberete kategorijo, nato pa se kot odziv na to drugi spustni meni spremeni v samo elemente iz te kategorije, prej pa je bilo to zapleteno in z novimi dinamičnimi nizi, ki so bili objavljeni septembra 2018 … in ti se uvajajo, zato morate imeti Office 365. Zdaj 10. oktobra, slišal sem da so na približno 50% insajderjev urada, zato jih uvajajo zelo počasi. Verjetno bo pretekla prva polovica leta 2019, preden jih dobite, vendar nam bo omogočil, da bomo veliko lažje opravili odvisno preverjanje.
Torej, tukaj imam dve formuli. Prva formula je ENOTNA za vse klasifikacije in to sem poslal v ukaz SORT. Torej, to mi daje 1 formulo, ki vrne 5 rezultatov in živi v D4. Torej, tukaj, kjer želim izbrati preverjanje veljavnosti podatkov, bom (DL - 1:09)… VIR bo = D4 #. Ta # - imenovali smo jo razlitje - poskrbite, da vrne vse rezultate iz D4. Torej, če bi tukaj dodal novo kategorijo in ta narašča, bo D4 # pobral ta dodaten znesek, v redu? (= UREDI (ENOTNO (B4: B23)))
Torej, prvo potrjevanje je dokaj preprosto, toda zdaj, ko vemo, da smo izbrali CITRUS - to bo težje - želim filtrirati seznam v stolpcu A, kjer je postavka v stolpcu B enaka izbrani postavki , vredu? Torej, najprej jim moramo dovoliti, da izberejo nekaj, potem pa, ko enkrat vem, da gre za CITRUS, potem mi dajo LIME, ORANŽO in TANGERINO, bi izbrali nekaj drugega. BERRY. Poglej to. V znanstvenih revijah piše, da je banana jagodičje. Ne strinjam se s tem. Se mi ne zdi jagodičja, ampak ne krivite me. Veste, samo uporabljam internet. BANANA, BREZ in MALINA.
Zdaj veste, težava s tem je, da bo nekdo sprva prišel sem, ne da bi karkoli izbral, in v tem primeru imamo IZBRANI RAZRED PRVI, kar je tisti tretji argument, ki pravi, če se nič ne najde, prav? Torej, veste, na ta način, če začnemo s tem scenarijem, bo prva izbira IZBRI RAZRED. Ideja je, da izberejo RAZRED, RASTLINSKO, te posodobitve, nato pa ti elementi prihajajo s tega seznama. POTRDITEV PODATKOV tukaj, seveda, no, to je še en prelivec, = E4 #, da to deluje, v redu? Torej, to je v redu. (= FILTER (A4: A23, B4: B23 = H3, "Najprej izberi razred"))
Oglejte si mojo knjigo Excel Dynamic Arrays. To je … do konca leta 2018 bo brezplačno. Preverite povezavo tam spodaj v opisu YouTube, kako jo lahko prenesete, prav za ta primer in še 29 drugih primerov uporabe teh elementov.
No, zaključite za danes. Dinamična polja nam dajo še en način za odvisno preverjanje veljavnosti. Če še niste v storitvi Office 365 in jih še nimate, se predvidoma vrnite na video 1606, ki prikazuje stari način za to.
Želim se vam zahvaliti, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko Excel
Če želite prenesti datoteko excel: depend-validation-using-arrays.xlsx
Če želite izvedeti več o dinamičnih nizih, si oglejte Excelove dinamične nivoje naravnost do točke.
Excel Misel dneva
Za nasvet glede Excela sem prosil prijatelje Excel Excel. Današnja misel o premišljevanju:
"Nikoli ne izbrišite Excelove datoteke, ne da bi jo prej varnostno kopirali."
Mike Alexander
