Úvod do dynamických rozsahů
Funkce VLOOKUP se často používá k vyhledání informací uložených v tabulkách v aplikaci Excel. Pokud například máme seznam jmen a věku lidí:
A pak můžeme v blízké buňce použít funkci VLOOKUP k určení Paulova věku:
Zatím je to celkem standardní. Co se ale stane, když do seznamu potřebujeme přidat další jména? Zjevnou myšlenkou by bylo upravit rozsah ve VLOOKUP. Ve skutečně složitém modelu však může existovat několik odkazů na VLOOKUP. To znamená, že bychom museli změnit každou referenci - za předpokladu, že bychom věděli, kde jsou.
Excel však poskytuje alternativní způsob - nazývaný DYNAMICKÝ rozsah. Toto je rozsah, který automaticky rozšiřuje aktualizace. To je perfektní, pokud se vaše seznamy neustále rozšiřují (např. Údaje o tržbách z měsíce na měsíc).
Abychom mohli nastavit dynamický rozsah, musíme mít název rozsahu - proto budeme volat náš AGE_DATA. Přístup k nastavení dynamických rozsahů se mezi Excelem 2007 a dřívějšími verzemi Excelu liší:
V aplikaci Excel 2007 klikněte ve vzorcích na „Definovat jméno“:
V dřívějších verzích aplikace Excel klikněte na „Vložit“ a poté na Jména.
Do vyskakovacího pole zadejte název našeho dynamického rozsahu - což je „VĚKOVÁ DATA“:
Do pole označeného „Odkazuje na“ musíme zadat rozsah našich údajů. Toho bude dosaženo funkcí OFFSET. To má 5 argumentů:
= OFFSET (reference, řádky, sloupce, výška, šířka)
- Reference je adresa NEJLEPŠÍHO rohu našeho sortimentu - v tomto případě buňky B5
- Řádky je počet řádků z NEJLEPŠÍCH LEVÝCH, kterými chceme, aby byl tento rozsah - což bude v tomto případě 0
- Cols je počet řádků z TOP LEFT, které chceme, aby byl tento rozsah - což bude v tomto případě 0
- Výška rozsahu - viz níže
- Šířka rozsahu - to jsou 2, v našem rozsahu máme DVA sloupce (jméno osoby a její věk)
Nyní se výška rozsahu bude muset lišit v závislosti na počtu záznamů v naší tabulce (což je aktuálně 7).
Samozřejmě chceme způsob počítání řádků v naší tabulce, který se automaticky aktualizuje - takže jedním ze způsobů, jak toho dosáhnout, je použít funkci COUNTA. Tím se pouze spočítá počet neprázdných buněk v rozsahu. Protože jsou naše jména ve sloupci B, počet záznamů v našich datech je COUNTA (B: B).
Všimněte si, že pokud byste to vložili do buňky, získali byste hodnotu 8 - protože obsahuje názvy záhlaví. Že je to však nepodstatné.
Do pole „Odkazuje na“ tedy uvedeme:
= OFFSET ($ B $ 5,0,0, counta (B: B), 2)
A klikněte na tlačítko OK. Náš dynamický rozsah je nyní vytvořen.
Nyní se vraťte k vzorcům VLOOKUP a nahraďte rozsah $ B: 4: $ C11 názvem našeho nového dynamického rozsahu AGE_DATA, takže máme:
Zatím se nic nezměnilo. Pokud však do naší tabulky přidáme několik dalších jmen:
A v cele, kde jsme měli Paula, ho nahraďte novým jménem, jako je Pedro (to na původním seznamu nebylo):
A vidíme, že Excel automaticky vrátil věk Pedra - i když jsme nezměnili vzorce VLOOKUP. Místo toho se rozsah dynamického rozsahu zvýšil o další názvy.
Dynamické rozsahy jsou velmi užitečné, když máme rostoucí objemy dat - zvláště když jsou vyžadovány tabulky VLOOKUP a PIVOT.
