Tabulky Excelu často obsahují rozevírací seznam buněk, aby se zjednodušilo nebo standardizovalo zadávání dat. Tyto rozevírací seznamy jsou vytvořeny pomocí funkce ověření dat k určení seznamu přípustných položek.
Chcete-li nastavit jednoduchý rozevírací seznam, vyberte buňku, do které budou data zadána, a poté klikněte na Ověřování dat(na kartě Data), vyberte možnost Ověření dat, zvolte Seznam(pod položkou Povolit :) a poté zadejte položky seznamu (oddělené čárkami) do Zdroj: pole (viz obrázek 1).
V tomto typu základního rozevíracího seznamu je uveden seznam povolených položek. v rámci samotné validace dat; proto, aby uživatel provedl změny v seznamu, musí uživatel otevřít a upravit ověření dat. To může být obtížné pro nezkušené uživatele nebo v případech, kdy je seznam možností dlouhý.
Další možností je umístit seznam do pojmenovaný rozsah v tabulce a poté zadat název tohoto rozsahu (s předsazením se stejným znaménkem) v poli Zdroj: ověření dat (viz obrázek 2).
Tato druhá metoda usnadňuje úpravy možností v seznamu, ale přidávání nebo odebírání položek může být problematické. Protože pojmenovaný rozsah (FruitChoices, v našem příkladu) odkazuje na pevný rozsah buněk ($ H $ 3: $ H $ 10, jak je ukázáno), pokud se do buněk H11 nebo níže přidají další možnosti, nebudou se v rozevírací nabídce zobrazovat (protože tyto buňky nejsou součástí řady FruitChoices).
Podobně, pokud jsou například položky Hrušky a Jahody vymazány, nebudou se již v rozevíracím seznamu zobrazovat, ale místo toho bude obsahovat dvě „Prázdné“ volby, protože rozevírací nabídka stále odkazuje na celou řadu FruitChoices, včetně prázdných buněk H9 a H10.
Z těchto důvodů, když se jako zdroj seznamu pro rozevírací seznam používá normální pojmenovaný rozsah, pojmenovaná oblast musí být sám upraven tak, aby zahrnoval více nebo méně buněk, pokud jsou položky přidány nebo odstraněny ze seznamu.
Řešením tohoto problému je použití dynamickénázev rozsahu jako zdroj pro rozbalovací nabídky. Název dynamického rozsahu je název, který se automaticky rozšiřuje (nebo zkracuje) tak, aby přesně odpovídal velikosti bloku dat při přidávání nebo odebírání položek. Chcete-li to provést, použijte k definování pojmenovaného rozsahu spíše formulanež pevný rozsah adres buněk.
Jak nastavit dynamický Rozsah v Excelu
Normální (statický) název rozsahu odkazuje na určený rozsah buněk ($ H $ 3: $ H $ 10 v našem příkladu viz níže):
Ale dynamický rozsah je definován pomocí vzorce (viz níže, převzato ze samostatné tabulky, která používá názvy dynamických rozsahů):
Než začneme, nezapomeňte si stáhnout naše Příklad souboru Excel (makra řazení byla zakázána).
Pojďme si tento vzorec podrobně prohlédnout. Možnosti pro ovoce jsou v bloku buněk přímo pod nadpisem (OVOCE). Tomuto záhlaví je také přiřazeno jméno: FruitsHeading
Celý vzorec používaný k definování dynamického rozsahu pro Výběr ovoce je:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
OvoceHeadingoznačuje záhlaví, které je jeden řádek nad prvním záznamem v seznamu. Číslo 20 (použité ve vzorci dvakrát) je maximální velikost (počet řádků) seznamu (toto lze upravit podle potřeby).
Všimněte si, že v tomto příkladu je pouze 8 položek v seznamu, ale pod nimi jsou také prázdné buňky, do kterých lze přidat další položky. Číslo 20 se vztahuje na celý blok, ve kterém lze provádět zápisy, nikoli na skutečný počet záznamů.
Nyní rozdělme vzorec na kousky (barevné kódování každého kusu), abychom pochopili, jak to funguje. :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
„Nejvnitřnějším“ kusem je OFFSET (FruitsHeading, 1,0,20,1). Toto odkazuje na blok 20 buněk (pod buňkou FruitsHeading), kde lze zadat volby. Tato funkce OFFSET v podstatě říká: Začněte v buňce FruitsHeading, přejděte dolů o 1 řádek a přes 0 sloupců, poté vyberte oblast, která je 20 řádků dlouhá a 1 sloupec široká. To nám dává blok 20 řádků, do kterého se zadávají možnosti ovoce.
Další část vzorce je funkce ISBLANK:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Zde byla funkce OFFSET (vysvětleno výše) nahrazena „výše“ (pro usnadnění čtení). Funkce ISBLANK však pracuje s 20 řádkovým rozsahem buněk, které definuje funkce OFFSET.
ISBLANK pak vytvoří sadu 20 hodnot TRUE a FALSE, což naznačuje, zda každá z jednotlivých buněk v 20- rozsah řádků, na který odkazuje funkce OFFSET, je prázdný (prázdný) nebo ne. V tomto příkladu bude prvních 8 hodnot v sadě FALSE, protože prvních 8 buněk není prázdné a posledních 12 hodnot bude TRUE.
Další část vzorce je funkce INDEX:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Opět platí, že „výše“ se týká výše popsaných funkcí ISBLANK a OFFSET. Funkce INDEX vrací matici obsahující 20 hodnot TRUE / FALSE vytvořených funkcí ISBLANK.
INDEXse obvykle používá k výběru určité hodnoty (nebo rozsahu hodnot) z blok dat zadáním určitého řádku a sloupce (v rámci tohoto bloku). Ale nastavení vstupů řádků a sloupců na nulu (jak se to dělá zde) způsobí, že INDEX vrátí matici obsahující celý blok dat.
Další část vzorce je funkce MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
Funkce MATCHvrací pozici první hodnoty TRUE v rámci pole vráceného funkcí INDEX. Protože prvních 8 položek v seznamu není prázdné, prvních 8 hodnot v poli bude NEPRAVDA a devátá hodnota bude PRAVDA (protože řádek 9 thv rozsahu je prázdný).
Funkce MATCH tedy vrátí hodnotu 9. V tomto případě však opravdu chceme vědět, kolik záznamů je v seznamu, takže vzorec odečte 1 od hodnoty MATCH (což udává pozici posledního záznamu). Takže nakonec MATCH (TRUE, výše, 0) -1 vrací hodnotu 8
Další část vzorce je funkce IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
Funkce IFERROR vrací alternativní hodnotu, pokud první zadaná hodnota způsobí chybu. Tato funkce je zahrnuta, protože pokud je celý blok buněk (všech 20 řádků) vyplněn položkami, funkce MATCH vrátí chybu.
Je to proto, že povíme funkci MATCH, aby hledala první hodnota TRUE (v poli hodnot z funkce ISBLANK), ale pokud NONE buněk jsou prázdné, bude celé pole vyplněno hodnotami FALSE. Pokud MATCH nemůže najít cílovou hodnotu (TRUE) v poli, které prohledává, vrací chybu.
Takže pokud je celý seznam plný (a proto MATCH vrací chybu), funkce IFERROR bude místo toho vraťte hodnotu 20 (s vědomím, že v seznamu musí být 20 položek).
Nakonec OFFSET (FruitsHeading, 1,0, výše, 1)vrátí rozsah, který vlastně hledáme: Začněte v buňce FruitsHeading, přejděte dolů o 1 řádek a přes 0 sloupců, poté vyberte oblast, která má však mnoho řádků, pokud jsou v seznamu položky (a 1 široký sloupec). Celý vzorec tedy vrátí rozsah, který obsahuje pouze skutečné položky (až do první prázdné buňky).
Pomocí tohoto vzorce můžete definovat rozsah, který je zdrojem rozevíracího seznamu, takže můžete volně upravovat seznam (přidávání nebo odebírání položek, pokud zbývající položky začínají v horní buňce a sousedí) a rozevírací nabídka bude vždy odrážet aktuální seznam (viz obrázek 6).
Zde použité vzorový soubor (dynamické seznamy) jsou zahrnuty a lze je stáhnout z tohoto webu. Makra však nefungují, protože WordPress nemá rád knihy Excel s makry v nich.
Jako alternativu k určení počtu řádků v bloku seznamu lze bloku seznamu přiřadit jeho vlastní název rozsahu, který pak lze použít v upraveném vzorci. V příkladovém souboru tuto metodu používá druhý seznam (jména). Zde je celému bloku seznamu (pod nadpisem „NAMES“, 40 řádků v příkladu souboru) přiřazen název rozsahu NameBlock. Alternativní vzorec pro definování NamesList je pak:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
, kde NamesBlocknahrazuje OFFSET (FruitsHeading, 1,0,20,1) a ROWS (NamesBlock)nahrazuje 20 (počet řádků) v předchozím vzorci.
Takže pro rozevírací seznamy, které lze snadno upravovat (včetně jiných uživatelů, kteří mohou být nezkušení), zkuste použít názvy dynamických rozsahů! Ačkoli byl tento článek zaměřen na rozevírací seznamy, názvy dynamických rozsahů lze použít kdekoli, kde je potřeba odkazovat na rozsah nebo seznam, který se může lišit velikostí. Užijte si to!