Jak vytvořit dynamicky definovaný rozsah v Excelu
Vaše excelové tabulky se často vyvíjejí, proto je výhodné mít dynamicky definovaný rozsah, který se automaticky přizpůsobuje aktuálnímu množství dat. Podívejme se, jak toho docílit.
Použitím dynamicky definovaného rozsahu ušetříte čas s manuální úpravou rozsahů ve vzorcích, grafech či kontingenčních tabulkách při každé změně dat. Vše proběhne automaticky.
K vytvoření dynamických rozsahů se běžně používají funkce OFFSET a INDEX. V tomto článku se zaměříme na funkci INDEX, která je efektivnější. OFFSET je totiž volatilní funkce a u velkých tabulek může zpomalovat výpočty.
Vytvoření dynamického rozsahu pomocí funkce INDEX
Pro názornost si ukážeme příklad s jednosloupcovým seznamem dat, například seznamem zemí:
Chceme, aby se tento rozsah automaticky aktualizoval při přidání nebo odebrání zemí. Konkrétně chceme rozsah od A2 do poslední buňky se zemí, tedy $A$2:$A$6, ale dynamicky. Klikněte na záložku „Vzorce“ a zvolte „Definovat název“.
Do pole „Název“ zadejte například „zeme“ a do pole „Odkazuje se na“ vložte následující vzorec:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Někdy je rychlejší a jednodušší vložit tento vzorec přímo do buňky v tabulce a zkopírovat jej do pole pro nový název.
Jak to funguje?
První část vzorce, $A$2:, definuje počáteční buňku rozsahu a operátor : (dvojtečka) určuje, že jde o rozsah.
Operátor rozsahu nutí funkci INDEX, aby vracela rozsah a ne jen hodnotu buňky. Funkce INDEX je pak kombinovaná s funkcí COUNTA. COUNTA($A:$A) spočítá počet neprázdných buněk ve sloupci A, tedy v našem případě 6.
INDEX($A:$A,COUNTA($A:$A))
Tento vzorec říká funkci INDEX, aby vrátila rozsah až po poslední neprázdnou buňku ve sloupci A, což je $A$6. Výsledkem je dynamický rozsah $A$2:$A$6, kde funkce COUNTA automaticky najde poslední řádek s daty. Nyní můžete název „zeme“ používat v ověření dat, vzorcích, grafech nebo kdekoli, kde potřebujete odkazovat na seznam zemí.
Dynamický rozsah ve dvou směrech
Předchozí příklad byl dynamický pouze vertikálně. S malou úpravou a přidáním další funkce COUNTA, můžete vytvořit rozsah, který je dynamický horizontálně i vertikálně.
Podívejme se na data v následující tabulce:
V tomto případě vytvoříme dynamický rozsah, který zahrnuje i záhlaví tabulky. Opět klikněte na „Vzorce“ > „Definovat název“.
Do pole „Název“ zadejte například „prodeje“ a do pole „Odkazuje se na“ vložte tento vzorec:
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Tento vzorec začíná buňkou $A$1 jako počáteční buňkou. Funkce INDEX prohledává celý list, tedy rozsah ($1:$1048576).
První funkce COUNTA, konkrétně COUNTA($A:$A), počítá neprázdné řádky, a druhá, COUNTA($1:$1), neprázdné sloupce. Díky tomu je rozsah dynamický v obou směrech. Ačkoliv jsme začali od buňky A1, můžeme zadat libovolnou počáteční buňku.
Nyní můžeme název „prodeje“ používat ve vzorcích, jako datovou řadu grafu, a ten se tak bude automaticky rozšiřovat podle rozsahu dat.
