Obecný vzorec
=$A$1:INDEX($A:$A,lastrow)
souhrn
Jedním ze způsobů, jak vytvořit dynamický pojmenovaný rozsah v aplikaci Excel, je použití funkce INDEX. V zobrazeném příkladu je pojmenovaná oblast „data“ definována následujícím vzorcem:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
, která se pohybuje v rozmezí $ A $ 2: $ A $ 10.
Poznámka: tento vzorec má definovat pojmenovaný rozsah, který lze použít v jiných vzorcích.
Vysvětlení
Tato stránka ukazuje příklad dynamického pojmenovaného rozsahu vytvořeného pomocí funkce INDEX společně s funkcí COUNTA. Dynamické pojmenované rozsahy se automaticky rozšiřují a smršťují, když jsou data přidávána nebo odebírána. Jsou alternativou k použití tabulky aplikace Excel, která také mění velikost při přidávání nebo odebírání dat.
Funkce INDEX vrací hodnotu na dané pozici v rozsahu nebo poli. INDEX můžete použít k načtení jednotlivých hodnot nebo celých řádků a sloupců v rozsahu. Díky čemuž je INDEX zvláště užitečný pro dynamické pojmenované rozsahy, je to, že ve skutečnosti vrací referenci. To znamená, že můžete použít INDEX k vytvoření smíšeného odkazu, jako je $ A $ 1: A100.
V zobrazeném příkladu je pojmenovaná oblast „data“ definována následujícím vzorcem:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
, která se pohybuje v rozmezí $ A $ 2: $ A $ 10.
Jak tyto vzorce fungují
Nejprve si všimněte, že tento vzorec je složen ze dvou částí, které sedí po obou stranách operátoru rozsahu (:). Vlevo máme počáteční referenci rozsahu, pevně kódovanou jako:
$A$2
Vpravo je koncová reference rozsahu, vytvořená pomocí INDEXu takto:
INDEX($A:$A,COUNTA($A:$A))
Zde vložíme INDEX do celého sloupce A pro pole a poté pomocí funkce COUNTA zjistíme „poslední řádek“ v rozsahu. COUNTA zde funguje dobře, protože ve sloupci A je 10 hodnot, včetně řádku záhlaví. COUNTA proto vrací 10, což jde přímo do INDEXU jako číslo řádku. INDEX poté vrátí odkaz na $ A $ 10, poslední použitý řádek v rozsahu:
INDEX($A:$A,10) // resolves to $A$10
Konečným výsledkem vzorce je tedy tento rozsah:
$A$2:$A$10
Dvourozměrný rozsah
Výše uvedený příklad funguje pro jednorozměrný rozsah. Chcete-li vytvořit dvourozměrný dynamický rozsah, kde je také počet sloupců dynamický, můžete použít stejný přístup, rozšířený takto:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Stejně jako dříve se COUNTA používá k určení „lastrow“ a my znovu použijeme COUNTA k získání „lastcolumn“. Ty jsou dodávány do indexu jako row_num, respektive column_num.
Pro pole však dodáváme celý list, zadaný jako všech 1048576 řádků, což umožňuje INDEXU vrátit referenci v 2D prostoru.
Poznámka: Excel 2003 podporuje pouze 65535 řádků.
Určení posledního řádku
Existuje několik způsobů, jak určit poslední řádek (poslední relativní polohu) v sadě dat, v závislosti na struktuře a obsahu dat v listu:
- Poslední řádek se smíšenými daty s mezerami
- Poslední řádek se smíšenými daty bez mezer
- Poslední řádek v textových datech
- Poslední řádek v číselných datech
