V tomto článku se naučíme, jak vytvořit dynamický rozevírací seznam v aplikaci Microsoft Excel.
Jak víme, funkce ověření dat zlepšuje účinnost zadávání dat v aplikaci Excel a snižuje chyby a chyby při psaní. Slouží k omezení uživatele pro typ dat, která lze zadat do rozsahu. V případě neplatného záznamu zobrazí zprávu a umožní uživateli zadat data na základě zadané podmínky.
Ale dynamický rozevírací seznam v Excelu je pohodlnější způsob výběru dat, aniž byste museli měnit zdroj. Jinými slovy, řekněte, že budete často aktualizovat seznam, který jste si vybrali v rozevíracím seznamu. A myslíte si, že pokud provedete nějaké změny v seznamu, musíte pokaždé upravit ověření dat, abyste získali aktualizovaný rozevírací seznam.
Zde však přichází na scénu dynamický rozevírací seznam a je to nejlepší možnost výběru dat bez provádění změn v ověřování dat. Je velmi podobný normálnímu ověřování dat. Když však seznam aktualizujete, dynamický rozevírací seznam se přizpůsobí této akci, zatímco normální rozevírací seznam nikoli.
Vezměme si tedy příklad a pochopíme, jak vytváříme dynamický rozevírací seznam:-
Ve sloupci A máme seznam produktů a v buňce D9 budeme mít dynamický rozevírací seznam produktů.
Název tabulky s nepřímou funkcí
Nejprve vytvoříme tabulku; postupujte podle níže uvedených kroků:-
- Vyberte rozsah A8: A16
- Přejděte na Vložit kartu a poté klikněte na Tabulka
- Po kliknutí na možnost „Tabulka“ se otevře okno Tabulka
- Poté vyberte rozsah, pro který chceme vložit tabulku A8: A17
- Klikněte na OK
- Nyní klikneme na OK
- Můžete vidět, že tento rozsah byl převeden do tabulky a záhlaví této tabulky má také možnost rozevíracího seznamu filtrů
Poznámka: - Pokud přidáme jakýkoli produkt nebo položku na konec seznamu, tabulka se automaticky rozbalí a začlení nové produkty nebo položky.
Nyní vytvoříme dynamický rozevírací seznam v buňce D9, postupujte podle níže uvedených kroků:-
- Vyberte buňku D9
- Otevřete dialogové okno Ověření dat stisknutím klávesy ALT+D+L
- V rozevíracím seznamu Povolit vyberte Seznam
- A pak zadejte tuto funkci = NEPŘÍMÝ („Tabulka1“) na kartě zdroje
- Klikněte na OK
Poznámka: - Když klikneme na OK, v Excelu se objeví okno, které říká, že se vstupem není něco v pořádku. Důvodem je, že Excel nepřijímá žádnou samorozbalovací tabulku přímo ve Ověření dat.
Nyní přidejte nové produkty do seznamu produktů.
Na výše uvedeném obrázku vidíme, že se v rozevíracím seznamu objevuje nově přidaný produkt.
2nd Příklad:-
V tomto příkladu se naučíme, jak pojmenovat název tabulky na dálku
Název tabulky již máme, ale zde musíme definovat název této tabulky, abychom získali dynamický rozevírací seznam; postupujte podle níže uvedených kroků:-
- Vyberte buňku D10
- Přejděte na rozsah tabulky a kromě záhlaví vybereme rozsah od prvního produktu k poslednímu
- Přejděte do pole s názvem a zadejte krátký název „tablerange“, stiskněte klávesu Enter
- Po stisknutí klávesy Enter vidíme, že se v poli názvu nic nezměnilo
- Kliknutím na možnost rozevíracího seznamu zobrazíte všechny dostupné rozsahy jmen
- V rozevíracím seznamu vidíme také název, který jsme právě definovali pro tuto tabulku
- Nyní přejdeme k Ověření dat a v části „Zdroj“ zadáme „tablerange“
Poznámka:- Pokud si nepamatujete, jaký název jste danému rozsahu dali, můžete stisknout klávesu F3 a vyskočí okno, které vám navrhne všechny pojmenované rozsahy, které jsou k dispozici.
- Nyní přejděte na kartu „Zadat zprávu“ a do nadpisu napíšeme „Vybrat produkt“ a poté do těla zprávy napíšeme „Vyberte prosím svůj produkt ze seznamu“
- Nyní přejděte na kartu „Upozornění na chyby“ a tam v nadpisu napíšeme „Neplatný produkt“ a v chybové zprávě napíšeme „Zadali jste špatný produkt“
- Klikněte na OK
- Buňka D10 obsahující vstupní zprávu spolu s rozevíracím seznamem
- Když nyní přidáme jakýkoli produkt do seznamu, automaticky se zobrazí v rozevíracím seznamu
Co se ale stane, když přeskočíme jednu buňku za poslední buňkou a poté přidáme nový produkt nebo položku? Vidíte, tentokrát se rozsah tabulek nerozšířil a ve skutečnosti je nově přidaný produkt v obecném formátu. Zobrazí se tedy v rozevíracím seznamu nebo ne? Abychom to zkontrolovali, když přejdeme do buňky D10 a zkontrolujeme rozevírací seznam, můžeme vidět stejný starý rozevírací seznam bez nového produktu. Je to proto, že rozsah tabulky po poslední buňce nic nenašel, a proto rozsah nevynaložil.
3rd Příklad:-
V následujících dvou metodách se naučíme, jak můžeme pomocí funkcí OFFSET a COUNTA učinit náš rozevírací seznam dynamičtější.
Postupujte podle níže uvedených kroků:-
- Vyberte buňku D11 a stiskněte ALT + D + L
- Otevře se dialogové okno Ověření dat
- Nyní vyberte seznam v možnosti „Povolit“
- Poté do možnosti Zdroj zadejte níže uvedený vzorec:-
= OFFSET ($ A $ 9,0,0, COUNTA ($ A: $ A), 1)
Vysvětlení vzorce:- Vybrali jsme A9, což je první produkt v řadě, a poté napíšeme 0 na 2nd argument, protože nechceme přesunout řádek z počátečního bodu; pak znovu 0 ve 3rd argument, protože zde nechceme žádné změny v počtu sloupců ani od počátečního bodu. A pak jsme zadali funkci COUNTA a vybrali celý sloupec A. Tento argument zkontroluje výšku v počtu řádků a vrátí neprázdný počet. Po provedení jakýchkoli změn v rozsahu rozšíří rozsah.
A poslední argument „Šířka“ je volitelný argument. Je to šířka v počtu sloupců. Můžeme to buď přeskočit, nebo zde zatím můžeme zadat 1. Pokud přeskočíme, ve výchozím nastavení vezme v úvahu šířku vráceného rozsahu, který jsme zadali v argumentu, a poté zavřeme závorky.
- Po kliknutí na OK se nám v buňce D11 zobrazí rozevírací seznam
- Zobrazí seznam včetně prázdných a poté produktů, které jsme přidali
4th Příklad:-
V tomto příkladu použijeme funkci k definování názvu.
Chcete-li definovat název rozsahu, postupujte podle níže uvedených kroků:-
- Stiskněte CTRL + F3, zobrazí se dialogové okno Správce jmen
- Klikněte na Nový
- Definujte název rozsahu „ProdName“ a zadejte níže uvedený vzorec:-
= OFFSET ('Dynamický rozevírací seznam s DV'! $ A $ 9,0,0, COUNTA ('Dynamický rozevírací seznam s DV'! $ A: $ A))
- Klikněte na OK
- Otevřete dialogové okno Ověření dat stisknutím klávesy Alt + D + L
- V rozevíracím seznamu Povolit vyberte Seznam
- Na kartě Zdroj zadejte = ProdName
- Klikněte na OK
- Nyní, pokud do seznamu něco přidáme, objeví se v seznamu totéž
Takto tedy můžete získat dynamický seznam pro jakýkoli produkt nebo položku pomocí různých metod pomocí ověření dat. To je prozatím vše. V dalším videu této série si vysvětlíme, jak vytvořit závislý rozevírací seznam pomocí různých metod v Excelu.
Kliknutím na odkaz na video získáte rychlý přehled o jeho použití. Přihlaste se k odběru našeho nového kanálu a učte se s námi!
Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku.
Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail