Od chvíle, kdy do aplikace Excel v roce 1997 byly přidány rozevírací nabídky Ověření dat, se lidé pokoušeli vypracovat způsob, jak změnit druhou rozevírací nabídku na základě výběru v první rozevírací nabídce.
Například pokud zvolíte Ovoce v A2, rozbalovací nabídka A4 nabídne Apple, Banana, Cherry. Pokud si ale vyberete Bylinky z A2, seznam v A4 nabídne Anise, Basil, Skořici. V průběhu let bylo mnoho řešení. V Podcastu jsem to popsal alespoň dvakrát:
- Klasická metoda používala mnoho pojmenovaných rozsahů, jak je uvedeno v epizodě 383.
- Další metoda používala vzorce OFFSET v epizodě 1606.
S vydáním nových vzorců dynamického pole ve veřejném náhledu nám nová funkce FILTER poskytne další způsob, jak provést závislé ověření.
Řekněme, že toto je vaše databáze produktů:
Pomocí vzorce =SORT(UNIQUE(B4:B23))v D4 získáte jedinečný seznam klasifikací. Jedná se o zcela nový typ receptury. Jeden vzorec v D4 vrací mnoho odpovědí, které se rozlévají do mnoha buněk. Pro odkaz na Spiller Range byste použili =D4#místo =D4.
Vyberte buňku, do které chcete uložit nabídku Ověření dat. Výběrem Alt + DL otevřete ověření dat. Změnit Povolit na "Seznam". Zadejte =D4#jako zdroj seznamu. Všimněte si, že Hashtag (#) je Spiller - to znamená, že máte na mysli celý Spiller Range.
Plán spočívá v tom, že si někdo zvolí klasifikaci z první rozbalovací nabídky. Potom vzorec =FILTER(A4:A23,B4:B23=H3,"Choose Class First")v E4 vrátí všechny produkty v této kategorii. Všimněte si, že použití „Zvolte nejprve třídu“ jako volitelného třetího argumentu. Tím se zabrání #HODNOTA! se nezobrazila chyba.
V závislosti na vybrané kategorii může být v seznamu různý počet položek. Nastavením Ověření dat směřujícím na =E4#se rozšíří nebo zkrátí délka seznamu.
Sledovat video
Přepis videa
Learn Excel from, Podcast Episode 2248: Dependent Validation using Arrays.
No, hej. To bylo v podcastu řešeno dvakrát, jak provést závislé ověřování a jaké je závislé ověřování, je vybrat si nejprve kategorii a poté v reakci na to se druhá rozevírací nabídka změní pouze na položky z této kategorie a dříve to bylo komplikované as novými dynamickými poli, která byla oznámena v září roku 2018… a ta se zavádějí, takže musíte mít Office 365. Právě teď 10. října jsem slyšel že jsou asi na 50% zasvěcených do Office, takže je vydávají velmi pomalu. Pravděpodobně to bude v první polovině roku 2019, než je získáte, ale umožní nám to provádět závislé ověřování mnohem jednodušším způsobem.
Mám zde tedy dva vzorce. První vzorec je JEDINEČNÝ ze všech klasifikací a poslal jsem to do příkazu SORT. To mi dává 1 vzorec, který vrací 5 výsledků, a který žije v D4. Takže tady, kde chci zvolit ověření dat, budu (DL - 1:09) … ZDROJ bude = D4 #. To # - my jsme tomu říkali spiller - ujistěte se, že vrátí všechny výsledky z D4. Takže pokud sem přidám novou kategorii a toto naroste, D4 # vyzvedne tu extra částku, dobře? (= TŘÍDĚNÍ (JEDINEČNÉ (B4: B23)))
Takže první ověření je poměrně jednoduché, ale teď, když víme, že jsme zvolili CITRUS - to bude obtížnější - chci filtrovat seznam ve sloupci A, kde se položka ve sloupci B rovná vybrané položce , v pořádku? Nejprve je tedy musíme nechat, aby si vybrali něco, a pak, jakmile vím, že je to CITRUS, pak mi dejte VÁPNU, ORANŽOVOU a TANGERINU, vybrali by si něco jiného. BOBULE. Koukej na tohle. Vědecké časopisy říkají, že banán je bobule. Nesouhlasím s tím. Necítí se mi jako bobule, ale neobviňuj mě. Já jen, víš, používám internet. BANANA, STŘEDA a MALINA.
Nyní, víte, potíže s tím je, že sem někdo původně přijde, aniž by si něco vybral, a tak v takovém případě máme VYBERTE SI TŘÍDU PRVNÍ, což je ten třetí argument, který říká, že pokud nic není nalezeno, dobře? Takže, víte, tímto způsobem, pokud začneme v tomto scénáři, bude volba VYBRAT TŘÍDU PRVNÍ. Myšlenka je, že si vyberou TŘÍDU, ZELENINU, tuto aktualizaci a pak tyto položky pocházejí z tohoto seznamu. Ověření údajů zde, samozřejmě, to je další spiller, = E4 #, aby to fungovalo, dobře? To je skvělé. (= FILTR (A4: A23, B4: B23 = H3, „Vyberte nejprve třídu“)))
Podívejte se na mou knihu Excel Dynamic Arrays. To je … do konce roku 2018 to bude zdarma. Podívejte se na odkaz dole v popisu YouTube, jak si jej můžete stáhnout, pro tento příklad plus dalších 29 příkladů, jak tyto položky používat.
No, zabalte se pro dnešek. Dynamická pole nám dávají další způsob, jak provádět závislé ověřování. Pokud nejste na Office 365 a ještě je nemáte, vraťte se, předpokládám, k videu 1606, které ukazuje starý způsob, jak to udělat.
Chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhněte si soubor Excel
Stažení souboru aplikace Excel: dependent-validation-using-arrays.xlsx
Chcete-li se dozvědět více o dynamických polích, podívejte se na dynamická pole Excel přímo k bodu.
Excel myšlenka dne
Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:
„Nikdy nemažte soubor aplikace Excel, aniž byste jej nejprve zálohovali.“
Mike Alexander
