Splošna formula
=$A$1:INDEX($A:$A,lastrow)
Povzetek
Eden od načinov za ustvarjanje dinamičnega imenovanega obsega v Excelu je uporaba funkcije INDEX. V prikazanem primeru je imenovani obseg "podatki" opredeljen z naslednjo formulo:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
ki se razreši na obseg $ A $ 2: $ A $ 10.
Opomba: ta formula naj bi opredelila imenovani obseg, ki se lahko uporablja v drugih formulah.
Pojasnilo
Ta stran prikazuje primer dinamičnega imenovanega obsega, ustvarjenega s funkcijo INDEX skupaj s funkcijo COUNTA. Dinamični imenovani obsegi se samodejno razširijo in skrčijo, ko se podatki dodajo ali odstranijo. So alternativa uporabi tabele Excel, ki se prav tako spremeni, ko se podatki dodajajo ali odstranjujejo.
Funkcija INDEX vrne vrednost na določenem položaju v obsegu ali matriki. INDEX lahko uporabite za pridobivanje posameznih vrednosti ali celotnih vrstic in stolpcev v obsegu. INDEX je še posebej uporaben za dinamične imenovane obsege, saj dejansko vrne referenco. To pomeni, da lahko uporabite INDEX za izdelavo mešane reference, kot je $ A $ 1: A100.
V prikazanem primeru je imenovani obseg "podatki" opredeljen z naslednjo formulo:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
ki se razreši na obseg $ A $ 2: $ A $ 10.
Kako te formule delujejo
Najprej upoštevajte, da je ta formula sestavljena iz dveh delov, ki se nahajata na obeh straneh operatorja obsega (:). Na levi strani imamo začetno referenco za obseg, trdo kodirano kot:
$A$2
Na desni je končna referenca za obseg, ustvarjen z INDEX, kot je ta:
INDEX($A:$A,COUNTA($A:$A))
Tu podajamo INDEX ves stolpec A za matriko, nato pa s funkcijo COUNTA ugotovimo "zadnjo vrstico" v obsegu. COUNTA tukaj dobro deluje, ker je v stolpcu A 10 vrednosti, vključno z vrstico glave. COUNTA torej vrne 10, ki gre neposredno v INDEX kot številka vrstice. Nato INDEX vrne sklic na 10 USD, zadnjo uporabljeno vrstico v obsegu:
INDEX($A:$A,10) // resolves to $A$10
Končni rezultat formule je torej ta razpon:
$A$2:$A$10
Dvodimenzionalno območje
Zgornji primer deluje za enodimenzionalni obseg. Če želite ustvariti dvodimenzionalni dinamični razpon, kjer je tudi število stolpcev dinamično, lahko uporabite enak pristop, razširjen takole:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Kot prej se za ugotavljanje "lastrow" uporablja COUNTA, za "zadnji stolpec" pa ponovno uporabimo COUNTA. Ti se indeksirajo kot številka_ vrstice oziroma številka_ stolpca.
Za matriko pa dobimo celoten delovni list, vnesen kot vseh 1048576 vrstic, kar omogoča, da INDEX vrne referenco v 2D prostoru.
Opomba: Excel 2003 podpira le 65535 vrstic.
Določitev zadnje vrstice
Odvisno od strukture in vsebine podatkov na delovnem listu obstaja več načinov za določitev zadnje vrstice (zadnjega relativnega položaja) v nizu podatkov:
- Zadnja vrstica v mešanih podatkih s praznimi mesti
- Zadnja vrstica v mešanih podatkih brez praznih prostorov
- Zadnja vrstica v besedilnih podatkih
- Zadnja vrstica v številskih podatkih
