Tabulky aplikace Excel často obsahují rozbalovací seznamy buněk, které zjednodušují a / nebo standardizují zadávání dat. Tyto rozevírací seznamy jsou vytvořeny pomocí funkce ověřování dat a určují seznam povolených položek.
Chcete-li nastavit jednoduchý rozevírací seznam, vyberte buňku, do které budou zadána data, klepněte na položku Ověření dat (na kartě Data ), vyberte možnost Ověření dat, vyberte položku Seznam (pod položkou Povolit :) a poté zadejte položky seznamu (oddělené čárkami ) v poli Zdroj : (viz obrázek 1).
V tomto typu základního rozevíracího seznamu je seznam povolených položek specifikován v rámci samotného ověření dat; proto, aby bylo možné provést 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í zdlouhavý.
Další možností je umístit seznam do pojmenované oblasti v rámci tabulky a poté zadat název oblasti (přednastavený znakem rovnosti) v poli Zdroj : ověření dat (jak je znázorněno na obrázku 2).
Tato druhá metoda usnadňuje úpravy voleb v seznamu, ale přidávání nebo odebírání položek může být problematické. Vzhledem k tomu, že pojmenovaný rozsah (FruitChoices, v našem příkladu) odkazuje na pevný rozsah buněk ($ H $ 3: $ H $ 10, jak je zobrazeno), pokud se do buněk H11 nebo níže přidají další volby, nezobrazí se v rozevíracím seznamu. (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 nadále zobrazovat v rozevíracím seznamu, ale místo toho budou rozevírací seznamy obsahovat dvě „prázdné“ volby, protože rozevírací seznam stále odkazuje na celou řadu FruitChoices, včetně prázdných buněk H9 a H10.
Z těchto důvodů při použití normálního pojmenovaného rozsahu jako zdroje seznamu pro rozevírací seznam musí být pojmenovaný rozsah upraven tak, aby obsahoval více či méně buněk, pokud jsou položky přidány nebo odstraněny ze seznamu.
Řešení tohoto problému je použití názvu dynamického rozsahu jako zdroje pro rozevírací volby. Název dynamického rozsahu je ten, který automaticky expanduje (nebo smlouvy) tak, aby přesně odpovídal velikosti bloku dat, když jsou položky přidávány nebo odebírány. Chcete-li to provést, použijete definici pojmenované oblasti spíše než pevný rozsah adres buněk.
Jak nastavit dynamický rozsah v aplikaci Excel
Normální (statický) název oblasti se vztahuje na specifikovaný rozsah buněk ($ H $ 3: $ H $ 10 v našem příkladu, viz níže):
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, ujistěte se, že jste si stáhli náš ukázkový soubor aplikace Excel (třídění maker bylo zakázáno).
Podívejme se podrobně na tento vzorec. Volby pro ovoce jsou v bloku buněk přímo pod hlavičkou ( OVOCE ). Tomuto záhlaví je také přiřazen název: OvoceHead :
Celý vzorec použitý pro definování dynamického rozsahu pro výběr Ovoce je:
= OFFSET (Ovoce, 1, 0, IFERROR (MATCH (PRAVDA, INDEX (ISBLANK (OFFSET (Ovoce), 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading odkazuje na nadpis, který je jeden řádek nad první položkou v seznamu. Číslo 20 (použité dvakrát ve vzorci) je maximální velikost (počet řádků) pro seznam (toto lze nastavit podle potřeby).
Všimněte si, že v tomto příkladu je v seznamu pouze 8 položek, ale pod nimi jsou také prázdné buňky, do kterých mohou být přidány další položky. Číslo 20 označuje celý blok, ve kterém mohou být provedeny záznamy, nikoli skutečný počet záznamů.
Nyní rozdělíme vzorec na kousky (barevné kódování každého kusu), abychom pochopili, jak to funguje:
= OFFSET (Ovoce, 1, 0, IFERROR (MATCH (PRAVDA, INDEX (ISBLANK ( OFFSET (Ovoce), 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
„Nejvnitřnější“ kus je OFFSET (Ovoce, 1, 0, 20, 1) . To odkazuje na blok 20 buněk (pod buňkou FruitsHeading), kde mohou být zadány volby. Tato funkce OFFSET v podstatě říká: Začněte v buňce FruitsHeading, jděte dolů o 1 řádek a více než 0 sloupců, pak vyberte oblast, která je dlouhá 20 řádků a 1 sloupec široký. Tak, že nám dává 20-řádek bloku, kde jsou zadány volby ovoce.
Další část vzorce je funkce ISBLANK :
= OFFSET (Ovoce, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (výše), 0, 0), 0) -1, 20), 1)
Zde byla funkce OFFSET (vysvětlena výše) nahrazena funkcí „výše“ (aby se věci lépe číst). Funkce ISBLANK však pracuje v rozsahu 20 řádků buněk, které definuje funkce OFFSET.
ISBLANK pak vytvoří sadu 20 hodnot TRUE a FALSE, které označují, zda je každá z jednotlivých buněk v rozsahu 20 řádků odkazovaná funkcí OFFSET prázdná nebo prázdná. V tomto příkladu bude prvních 8 hodnot v sadě FALSE, protože prvních 8 buněk není prázdných a posledních 12 hodnot bude TRUE.
Další část vzorce je funkce INDEX:
= OFFSET (Ovoce, 1, 0, IFERROR (MATCH (TRUE, INDEX (výše, 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í pole obsahující 20 hodnot TRUE / FALSE vytvořených funkcí ISBLANK.
INDEX se obvykle používá k výběru určité hodnoty (nebo rozsahu hodnot) z bloku dat zadáním určitého řádku a sloupce (v rámci tohoto bloku). Ale nastavení vstupů řádků a sloupců na nulu (jak je zde provedeno) způsobí, že INDEX vrátí pole obsahující celý blok dat.
Další část vzorce je funkce MATCH:
= OFFSET (Ovoce, 1, 0, IFERROR ( MATCH (TRUE, výše, 0) -1, 20), 1)
Funkce MATCH vrací pozici první hodnoty TRUE v poli, které je vráceno funkcí INDEX. Protože prvních 8 položek v seznamu není prázdné, prvních 8 hodnot v poli bude FALSE a devátá hodnota bude TRUE (protože devátý řádek v rozsahu je prázdný).
Funkce MATCH tedy vrátí hodnotu 9 . V tomto případě však opravdu chceme vědět, kolik položek je v seznamu, takže vzorec odečítá 1 od hodnoty MATCH (což dává pozici posledního záznamu). Takže nakonec MATCH (TRUE, výše, 0) -1 vrátí hodnotu 8 .
Další část vzorce je funkce IFERROR:
= OFFSET (Ovoce, 1, 0, IFERROR (výše, 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 říkáme funkci MATCH, aby hledala první hodnotu TRUE (v poli hodnot z funkce ISBLANK), ale pokud NONE buněk jsou prázdné, celé pole bude vyplněno hodnotami FALSE. Pokud MATCH nemůže najít cílovou hodnotu (TRUE) v poli, které hledá, vrátí chybu.
Pokud je tedy celý seznam plný (a proto MATCH vrátí chybu), funkce IFERROR namísto toho vrátí hodnotu 20 (s vědomím, že v seznamu musí být 20 položek).
Konečně OFFSET (FruitsHeading, 1, 0, výše uvedené, 1) vrací rozsah, který skutečně hledáte: Začněte v buňce OvoceHead, jděte dolů o 1 řádek a více než 0 sloupců, pak vyberte oblast, která je však mnoho řádků dlouhá jako v seznamu jsou položky (a 1 sloupec široký). Takže celý vzorec společně vrátí rozsah, který obsahuje pouze skutečné položky (dolů na první prázdnou buňku).
Pomocí tohoto vzorce můžete definovat rozsah, který je zdrojem pro rozevírací seznam, takže můžete seznam volně editovat (přidávání nebo odebírání položek, pokud zbývající položky začínají na horní buňce a jsou souvislé) a rozevírací seznam bude vždy odrážet aktuální (viz obrázek 6).
Ukázkový soubor (dynamické seznamy), který je zde používán, je zahrnut a je k dispozici na těchto webových stránkách. Makra však nefungují, protože WordPress nemá rád Excel knihy s makry v nich.
Jako alternativu ke specifikaci počtu řádků v bloku seznamu může být blok seznamu přiřazen jeho vlastní název rozsahu, který pak může být použit v modifikovaném vzorci. V ukázkovém souboru používá tento seznam druhý seznam (jména). Zde je celý seznam bloků (pod nadpisem „NAMES“, 40 řádků v příkladu souboru) přiřazen název rozsahu NameBlock . Alternativní vzorec pro definování seznamu jmen je pak:
= OFFSET (Jmenovky, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
kde NamesBlock nahrazuje OFFSET (FruitsHeading, 1, 0, 20, 1) a ROWS (NamesBlock) nahrazuje 20 (počet řádků) v dřívějším vzorci.
Pro rozevírací seznamy, které lze snadno upravovat (včetně jiných uživatelů, kteří mohou být nezkušení), zkuste použít dynamický název rozsahu! A všimněte si, že i když byl tento článek zaměřen na rozevírací seznamy, názvy dynamických rozsahů lze použít kdekoli, kde potřebujete odkazovat rozsah nebo seznam, který se může lišit ve velikosti. Užívat si!
