Všeobecný vzorec
=$A$1:INDEX($A:$A,lastrow)
Zhrnutie
Jedným zo spôsobov, ako vytvoriť dynamický pomenovaný rozsah v programe Excel, je použitie funkcie INDEX. V zobrazenom príklade je pomenovaný rozsah „data“ definovaný nasledujúcim vzorcom:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
, ktorá sa pohybuje v rozmedzí $ A $ 2: $ A $ 10.
Poznámka: tento vzorec má definovať pomenovaný rozsah, ktorý je možné použiť v iných vzorcoch.
Vysvetlenie
Táto stránka zobrazuje príklad dynamického pomenovaného rozsahu vytvoreného pomocou funkcie INDEX spolu s funkciou COUNTA. Dynamické pomenované rozsahy sa automaticky rozširujú a zmršťujú pri pridávaní alebo odstraňovaní údajov. Sú alternatívou k použitiu tabuľky Excel, ktorej veľkosť sa mení aj pri pridávaní alebo odstraňovaní údajov.
Funkcia INDEX vráti hodnotu na danej pozícii v rozsahu alebo poli. INDEX môžete použiť na načítanie jednotlivých hodnôt alebo celých riadkov a stĺpcov v rozsahu. Vďaka čomu je INDEX obzvlášť užitočný pre dynamické pomenované rozsahy, je to, že v skutočnosti vracia referenciu. To znamená, že z indexu INDEX môžete vytvoriť zmiešaný odkaz, ako napríklad $ A $ 1: A100.
V zobrazenom príklade je pomenovaný rozsah „data“ definovaný nasledujúcim vzorcom:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
, ktorá sa pohybuje v rozmedzí $ A $ 2: $ A $ 10.
Ako tieto vzorce fungujú
Najskôr si všimnite, že tento vzorec je zložený z dvoch častí, ktoré sú na oboch stranách operátora rozsahu (:). Vľavo máme východiskovú referenciu pre rozsah, pevne zakódovanú ako:
$A$2
Vpravo je koncová referencia rozsahu vytvorená s INDEX takto:
INDEX($A:$A,COUNTA($A:$A))
Tu vložíme INDEX do celého stĺpca A pre pole a potom pomocou funkcie COUNTA zistíme „posledný riadok“ v rozsahu. COUNTA tu funguje dobre, pretože v stĺpci A je 10 hodnôt vrátane riadku hlavičky. COUNTA preto vráti 10, ktoré prejdú priamo do INDEXU ako číslo riadku. INDEX potom vráti odkaz na $ A $ 10, posledný použitý riadok v rozsahu:
INDEX($A:$A,10) // resolves to $A$10
Konečným výsledkom vzorca je teda tento rozsah:
$A$2:$A$10
Dvojrozmerný rozsah
Vyššie uvedený príklad funguje pre jednorozmerný rozsah. Ak chcete vytvoriť dvojrozmerný dynamický rozsah, v ktorom je dynamický aj počet stĺpcov, môžete použiť rovnaký prístup rozšírený takto:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Rovnako ako predtým, COUNTA sa používa na zistenie hodnoty „lastrow“ a my opäť použijeme značku COUNTA na získanie „lastcolumn“. Tieto sa dodávajú na indexovanie ako row_num, respektíve column_num.
Pre pole však dodávame celý pracovný hárok, zadaný ako všetkých 1048 576 riadkov, čo umožňuje INDEXU vrátiť referenciu v 2D priestore.
Poznámka: Excel 2003 podporuje iba 65 535 riadkov.
Určenie posledného riadku
Existuje niekoľko spôsobov, ako určiť posledný riadok (posledná relatívna poloha) v množine údajov, v závislosti od štruktúry a obsahu údajov v hárku:
- Posledný riadok obsahuje zmiešané údaje a medzery
- Posledný riadok so zmiešanými údajmi bez medzier
- Posledný riadok v textových dátach
- Posledný riadok v číselných údajoch
