Vytváření rozbalovacích seznamů z hlaviček tabulek v Excelu se zdá nemožné—ale tento trik to opraví

Vytvořili jste dokonalou tabulku v Excelu, ale v okamžiku, kdy se pokusíte použít její záhlaví v rozbalovacím seznamu, se všechno rozsype. Funkce Ověření dat v Excelu je pro tabulky proslule vybíravá, ale existuje chytré řešení. Takže přestaňte své nabídky tvrdošíjně kódovat a místo toho použijte tuto dynamickou synchronizaci.

Cíl: Nabídky, které se vyvíjejí spolu s vašimi daty

Představte si, že máte formátovanou tabulku Excel s názvem T_Inventory. V oblasti pro vyhledávání chcete mít dvě rozevírací nabídky: jednu v buňce H2 pro výběr produktu a jednu v buňce I1 pro výběr atributu (například náklady nebo dodavatele). Když jsou obě vybrány, buňka I2 by měla vrátit správný výsledek.

Bez triku, který vám chystám ukázat, byste mohli atribut v buňce I1 zadat ručně. To však okamžitě zastaví automatizaci vašeho vyhledávání, což vede k méně profesionálnímu tabulkovému souboru a procesu, který se může pokazit jedinou překlepem.

Další možností je odkazovat na pevný rozsah $A$1:$E$1 v pravidle pro ověřování dat. Problém tohoto přístupu je v tom, že pokud do tabulky přidáte nový sloupec, odkazovaný rozsah se automaticky nerozšíří.

Stručně řečeno, chcete menu, které se aktualizuje ve chvíli, kdy se váš stůl zvětší.

Proč tabulky ruší ověřování dat

Tabulky Excel používají strukturované odkazy (např. T_Inventory[#Headers]), což je skvělé, protože se automaticky přizpůsobují při změně dat. Problém je v tom, že kontrola platnosti dat očekává konkrétní rozsah buněk nebo definovaný název, ale nativně nerozumí syntaxi strukturovaného odkazu v závorkách, kterou tabulky používají.

Abyste se tomu vyhnuli, použijte pojmenovaný rozsah jako most mezi vaší tabulkou a nástrojem pro ověřování dat.

Řešení: Nastavení vašeho dynamického vyhledávání

Vytvoření tohoto vyhledávače zahrnuje tři kroky. Krok 2 je však ten, kde se odhalí jediný héro trik - chytrý most, který vaše rozbalovací nabídky udělá skutečně dynamickými a nezlomnými.

Krok 1: Vytvořte pojmenovaný rozsah pro produkty (buňka H2)

Zatímco ověřování dat nemá rádo strukturované odkazy, miluje pojmenované oblasti. Toto je nejčistší způsob, jak dostat seznam produktů do oblasti pro vyhledávání.

Nejprve vyberte všechny buňky ve sloupci Produkt.

Poté do pole pro název napište snadno zapamatovatelné jméno (například ProductList) a stiskněte Enter.

Dále vyberte buňku H2 (kam bude umístěn rozbalovací seznam produktů) a na kartě Data klikněte na „Ověření dat.“

Nakonec v poli Povolit vyberte „Seznam“, zadejte rovná se (=) následované názvem, který jste právě vytvořili, a klikněte na „OK.“

Teď, když vyberete buňku H2, zobrazí se rozevírací seznam vašich produktů.

Krok 2: Hrdinský trik pro záhlaví tabulky (buňka I1)

Ačkoli kontrola dat obvykle zamezuje používání záhlaví tabulek, můžete toto omezení obejít vytvořením dynamického pojmenovaného rozsahu. Nejprve přejděte na kartu Vzorce a klikněte na „Správce názvů“. Poté klikněte na „Nový“.

Do pole Název napište HeaderList. Poté odstraňte vše, co je v poli Odkazuje na, a s kurzorem v nyní prázdném poli vyberte všechny záhlaví ve své tabulce. Ověřte, že pole nyní obsahuje strukturovaný odkaz na záhlaví vaší tabulky (na rozdíl od přímých odkazů na buňky):

=T_Inventory[#Headers]

Pak klikněte na „OK“ a „Zavřít“ pro zavření dialogových oken.

Nyní vyberte buňku I1 (kam bude umístěn rozevírací seznam atributů) a v dialogovém okně Ověření dat vyberte v poli Povolit možnost „Seznam“, stejně jako jste to udělali v kroku 1. Tentokrát však do pole Zdroj napište rovná se (=) následované názvem, který jste právě vytvořili.

Když kliknete na „OK“, uvidíte, že rozbalovací nabídka v buňce I1 obsahuje záhlaví vaší tabulky.

Na rozdíl od jiných metod je tento odkaz zcela dynamický. Pokud přejmenujete záhlaví sloupce nebo dokonce celou tabulku, Správce názvů se automaticky aktualizuje a vaše rozevírací nabídka se nikdy nesekne.

Krok 3: Vzorec motoru (buňka I2)

S vybraným produktem v buňce H2 a vybraným atributem v buňce I1 potřebujete jen jeden další vzorec, abyste vše spojili dohromady. V tomto případě byste měli použít funkce INDEX a MATCH:

=INDEX(T_Inventory,MATCH(H2,T_Inventory[Product],0),MATCH(I1,T_Inventory[#Headers],0))

Zde je vysvětleno, jak vzorec funguje:

• INDEX(T_Inventory...): Toto je mapa vašeho celého datasetu.
• MATCH(H2...): Toto najde konkrétní řádek pro váš produkt.
• MATCH(I1...): Toto najde konkrétní sloupec pro váš atribut.

Teď si pohrát s vyhledáváním: vyberte jiný produkt a atribut a sledujte, jak se aktualizuje výsledek v buňce I2. Všimnete si však, že když vyberete „Cena“ v buňce I1, je výsledek v buňce I2 obyčejné, neformátované číslo místo měny.

Je to proto, že buňka I2 je chameleon, který mezi vašimi sloupci přepíná různé typy dat. Ale nebojte se - existuje jednoduchý způsob, jak to opravit.

Oprava problému s formátováním

Aby se výsledek v buňce I2 zobrazil jako měna po výběru možnosti „Náklady“, je potřeba zadat rychlé pravidlo podmíněného formátování.

Vyberte buňku a na kartě Domů klikněte na Podmíněné formátování > Nové pravidlo.

Klikněte na „Použít vzorec pro určení, které buňky formátovat,“ a do pole ve spodní části dialogu zadejte:

=$I$1="Cost"

Poté klikněte na „Formát“ a na kartě Číslo vyberte „Účetnictví“ nebo „Měna“ a ujistěte se, že je vybrán správný symbol. Poté klikněte dvakrát na „OK“ pro zavření obou dialogových oken.

Nyní, když buňka I1 obsahuje slovo „Náklad,“ výsledek v buňce I2 se automaticky změní na vámi vybranou měnu.

Závěrečný test: Přidejte sloupec

Skutečná síla tohoto nastavení spočívá v tom, jak zvládá změny. Do buňky F1 (první buňky napravo od vaší tabulky) napište nový název sloupce, například Doplněno, abyste vytvořili nový sloupec tabulky. Pak rozbalte rozevírací seznam v buňce I1 a uvidíte, že nový název sloupce už tam je.

Rozbalovací seznamy pro ověřování dat jsou ve skutečnosti užitečnější, než si možná myslíte. Kromě toho, že je můžete použít k vytvoření výběru záhlaví tabulky, je můžete využít k sestavení dynamických grafů, které se aktualizují podle vašeho výběru. Pokud chcete posunout svou automatizaci ještě dál, můžete vytvořit závislé rozbalovací seznamy, kde se možnosti v jednom menu mění podle toho, co jste zvolili v jiném.