Vaše data aplikace Excel se často mění, takže je užitečné vytvořit dynamicky definovaný rozsah, který se automaticky rozšiřuje a smršťuje podle velikosti rozsahu dat. Podívejme se jak.
Při použití dynamicky definovaného rozsahu nebudete muset při změně dat ručně upravovat rozsahy vzorců, grafů a kontingenčních tabulek. To se stane automaticky.
K vytvoření dynamických rozsahů se používají dva vzorce: OFFSET a INDEX. Tento článek se zaměří na použití funkce INDEX, protože je to efektivnější přístup. OFFSET je nestálá funkce a může zpomalit velké tabulky.
Table of Contents
Vytvořte dynamický definovaný rozsah v aplikaci Excel
Pro náš první příklad máme níže uvedený seznam dat s jedním sloupcem.
Potřebujeme, aby to bylo dynamické, takže pokud se přidá nebo odebere více zemí, rozsah se automaticky aktualizuje.
V tomto příkladu se chceme vyhnout buňce záhlaví. Jako takový chceme rozsah $A$2:$A$6, ale dynamický. To provedete kliknutím na Vzorce > Definovat název.
Do pole „Název“ zadejte „země“ a do pole „Odkazuje“ zadejte níže uvedený vzorec.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Zadání této rovnice do buňky tabulky a její zkopírování do pole Nový název je někdy rychlejší a jednodušší.
Jak tohle funguje?
První část vzorce určuje počáteční buňku rozsahu (v našem případě A2) a poté následuje operátor rozsahu (:).
=$A$2:
Použití operátoru rozsahu vynutí funkci INDEX vrátit rozsah namísto hodnoty buňky. Funkce INDEX se pak používá s funkcí COUNTA. COUNTA počítá počet neprázdných buněk ve sloupci A (v našem případě šest).
INDEX($A:$A,COUNTA($A:$A))
Tento vzorec žádá funkci INDEX, aby vrátila rozsah poslední neprázdné buňky ve sloupci A ($A$6).
Konečný výsledek je $A$2:$A$6 a díky funkci COUNTA je dynamický, protože najde poslední řádek. Tento název definovaný „zeměmi“ nyní můžete použít v pravidle ověření dat, vzorci, grafu nebo kdekoli, kde potřebujeme odkazovat na názvy všech zemí.
Vytvořte dvoucestný dynamický definovaný rozsah
První příklad byl dynamický pouze na výšku. S mírnou úpravou a další funkcí COUNTA však můžete vytvořit rozsah, který je dynamický jak na výšku, tak na šířku.
V tomto příkladu budeme používat data uvedená níže.
Tentokrát vytvoříme dynamicky definovaný rozsah, který zahrnuje hlavičky. Klikněte na Vzorce > Definovat název.
Do pole „Název“ zadejte „prodej“ a do pole „Odkazuje“ zadejte níže uvedený vzorec.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Tento vzorec používá $A$1 jako počáteční buňku. Funkce INDEX pak používá rozsah celého listu ($1:$1048576) k nahlédnutí a návratu.
Jedna z funkcí COUNTA se používá k počítání neprázdných řádků a další se používá pro neprázdné sloupce, díky čemuž je dynamická v obou směrech. Ačkoli tento vzorec začal od A1, mohli jste zadat libovolnou počáteční buňku.
Nyní můžete tento definovaný název (prodeje) použít ve vzorci nebo jako datovou řadu grafu, aby byly dynamické.