Odkedy boli do programu Excel v roku 1997 pridané rozbaľovacie ponuky Overenie údajov, ľudia sa snažili nájsť spôsob, ako zmeniť druhú rozbaľovaciu ponuku na základe výberu v prvej rozbaľovacej ponuke.
Napríklad ak vyberiete Ovocie v A2, rozbaľovacia ponuka v A4 ponúkne Apple, Banana, Cherry. Ak si ale vyberiete Bylinky z A2, zoznam v A4 by ponúkol Aníz, Bazalku, Škoricu. V priebehu rokov bolo veľa riešení. V Podcaste som to prebral minimálne dvakrát:
- Klasická metóda používala veľa pomenovaných rozsahov, ako je uvedené v epizóde 383.
- Ďalšia metóda používala v epizóde 1606 vzorce OFFSET.
S vydaním nových vzorcov Dynamic Array vo verejnej ukážke nám nová funkcia FILTER poskytne ďalší spôsob, ako vykonať závislé overenie.
Povedzme, že toto je vaša databáza produktov:
Pomocou vzorca =SORT(UNIQUE(B4:B23))v D4 získate jedinečný zoznam klasifikácií. Toto je úplne nový typ receptúry. Jeden vzorec v D4 vráti veľa odpovedí, ktoré sa vylejú do mnohých buniek. Pre odkaz na Spiller Range by ste použili =D4#namiesto =D4.
Vyberte bunku, v ktorej sa nachádza ponuka Overenie údajov. Výberom kombinácie klávesov Alt + DL otvorte overenie údajov. Zmeňte Povoliť na „Zoznam“. Zadajte =D4#ako zdroj zoznamu. Všimnite si, že Hashtag (#) je Spiller - to znamená, že máte na mysli celý Spiller Range.
Plán je taký, že niekto si vyberie klasifikáciu z prvej rozbaľovacej ponuky. Potom vzorec =FILTER(A4:A23,B4:B23=H3,"Choose Class First")v E4 vráti všetky produkty v tejto kategórii. Všimnite si, že použitie možnosti „Vybrať triedu ako prvú“ ako voliteľného tretieho argumentu. Takto zabránite #HODNOTE! sa nezobrazila chyba.
V závislosti od vybranej kategórie môže byť v zozname rôzny počet položiek. Nastavenie overenia údajov, ktoré ukazuje na, =E4#sa rozšíri alebo zmenší s dĺžkou zoznamu.
Pozeraj video
Prepis videa
Naučte sa Excel od, Podcast Episode 2248: Závislé overenie pomocou polí.
No hej. To sa v podcastu riešilo už dvakrát, ako vykonať závislú validáciu a čo je to závislá validácia, musíte si najprv zvoliť kategóriu a potom v reakcii na to sa druhá rozbaľovacia ponuka zmení iba na položky z tejto kategórie a predtým to bolo komplikované a s novými dynamickými poľami, ktoré boli oznámené v septembri 2018 … a tieto sa zavádzajú, takže musíte mať Office 365. Práve teraz, 10. októbra, som počul že sú asi na 50% insiderov Office, takže ich rozširujú veľmi pomaly. Pravdepodobne to bude v prvej polovici roku 2019, kým ich získate, ale umožní nám to oveľa jednoduchšie vykonať závislú validáciu.
Mám tu teda dva vzorce. Prvý vzorec je JEDINEČNÝ všetkých klasifikácií a poslal som ho do príkazu SORT. To mi dáva 1 vzorec, ktorý vráti 5 výsledkov, a ktorý žije v D4. Takže tu, kde chcem zvoliť overenie údajov, budem (DL - 1:09) … ZDROJ bude = D4 #. To # - my sme tomu hovorili spiller - uistite sa, že vráti všetky výsledky z D4. Takže, ak by som sem pridal novú kategóriu a táto by narástla, D4 # vyzdvihne túto sumu navyše, dobre? (= Triedenie (JEDINEČNÉ (B4: B23))))
Takže prvá validácia je dosť jednoduchá, ale teraz, keď vieme, že sme si vybrali CITRUS - bude to zložitejšie - chcem filtrovať zoznam v stĺpci A, kde sa položka v stĺpci B rovná vybranej položke , dobre? Najprv teda musíme nechať, aby si vybrali niečo, a potom, keď viem, že je to CITRUS, potom mi dajte VÁPNU, ORANŽU a TANGERÍNU, vybrali by si niečo iné. BERRY. Pozri na toto. Vedecké časopisy tvrdia, že banán je bobule. S tym nesuhlasim. Necíti sa mi ako bobuľa, ale neobviňuj ma. Ja len, ako viete, používam internet. BANANA, STRADA a MALINA.
Teraz viete, že s tým bude mať niekto problém, že sem pôvodne príde, ale nič si nevyberie, takže v takom prípade máme PRVÝ VÝBER TRIEDY, čo je tretí argument, ktorý hovorí, že ak sa nič nenájde, dobre? Takže, viete, týmto spôsobom, ak začneme v tomto scenári, bude voľba PRVÁ VYBERTE SI TRIEDU. Myšlienka je, že si vyberú TRIEDU, ZELENINU, túto aktualizáciu a potom tieto položky pochádzajú z tohto zoznamu. VALIDÁCIA ÚDAJOV tu, samozrejme, to je ďalší problém, = E4 #, aby to fungovalo, dobre? Takže je to v pohode. (= FILTER (A4: A23, B4: B23 = H3, „Najprv vyberte triedu“)))
Pozrite sa na moju knihu Excel Dynamic Arrays. Toto je … do konca roka 2018 to bude zadarmo. Pre tento príklad plus odkaz na ďalších 29 príkladov použitia týchto položiek nájdete odkaz dole v popise YouTube, ako si ho môžete stiahnuť.
No zabalte sa na dnes. Dynamické polia nám poskytujú ďalší spôsob, ako vykonať závislú validáciu. Ak nepoužívate Office 365 a ešte ich nemáte, pokojne sa vráťte k videu 1606, ktoré ukazuje starý spôsob, ako to urobiť.
Chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: dependent-validation-using-arrays.xlsx
Ak sa chcete dozvedieť viac informácií o dynamických poliach, vyskúšajte Dynamické polia Excel priamo k bodu.
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Nikdy neodstraňujte súbor programu Excel bez toho, aby ste ho najskôr zálohovali.“
Mike Alexander
