Generička formula
=$A$1:INDEX($A:$A,lastrow)
Sažetak
Jedan od načina za stvaranje dinamičkog imenovanog raspona u Excelu je upotreba funkcije INDEX. U prikazanom primjeru, imenovani raspon "podaci" definiran je sljedećom formulom:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
koji se razlučuje u rasponu $ A $ 2: $ A $ 10.
Napomena: ova formula treba definirati imenovani raspon koji se može koristiti u drugim formulama.
Obrazloženje
Ova stranica prikazuje primjer dinamičkog imenovanog raspona stvorenog s funkcijom INDEX zajedno s funkcijom COUNTA. Dinamički imenovani rasponi automatski se šire i skupljaju kad se podaci dodaju ili uklone. Oni su alternativa korištenju Excel tablice, koja se također mijenja kad se podaci dodaju ili uklanjaju.
Funkcija INDEX vraća vrijednost na danom položaju u rasponu ili nizu. INDEX možete koristiti za dohvaćanje pojedinačnih vrijednosti ili cijelih redaka i stupaca u rasponu. Ono što INDEX čini posebno korisnim za dinamičke imenovane raspone je što zapravo vraća referencu. To znači da pomoću INDEX-a možete stvoriti mješovitu referencu poput $ A $ 1: A100.
U prikazanom primjeru, imenovani raspon "podaci" definiran je sljedećom formulom:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
koji se razlučuje u rasponu $ A $ 2: $ A $ 10.
Kako funkcioniraju ove formule
Primijetite prvo da je ova formula sastavljena iz dva dijela koji se nalaze s obje strane operatora raspona (:). S lijeve strane imamo početnu referencu za raspon, tvrdo kodiranu kao:
$A$2
S desne strane nalazi se završna referenca za raspon, stvorena s INDEX-om ovako:
INDEX($A:$A,COUNTA($A:$A))
Ovdje dovodimo INDEX u cijeli stupac A za niz, a zatim koristimo funkciju COUNTA da shvatimo "zadnji redak" u rasponu. COUNTA ovdje dobro funkcionira jer se u stupcu A nalazi 10 vrijednosti, uključujući redak zaglavlja. COUNTA stoga vraća 10, koji ide izravno u INDEX kao broj retka. Zatim INDEX vraća referencu na $ A $ 10, posljednji korišteni redak u rasponu:
INDEX($A:$A,10) // resolves to $A$10
Dakle, konačni rezultat formule je ovaj raspon:
$A$2:$A$10
Dvodimenzionalni raspon
Gornji primjer vrijedi za jednodimenzionalni raspon. Da biste stvorili dvodimenzionalni dinamički raspon u kojem je broj stupaca također dinamičan, možete koristiti isti pristup, proširen ovako:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Kao i prije, COUNTA se koristi za otkrivanje "lastrow" -a, a mi opet koristimo COUNTA za dobivanje "lastcolumna". Oni se dostavljaju u indeks kao red_num, odnosno stupac_num.
Međutim, za niz isporučujemo cijeli radni list, upisan kao svih 1048576 redaka, što omogućuje INDEX-u da vrati referencu u 2D prostoru.
Napomena: Excel 2003 podržava samo 65535 redaka.
Utvrđivanje posljednjeg reda
Postoji nekoliko načina za određivanje posljednjeg retka (posljednjeg relativnog položaja) u skupu podataka, ovisno o strukturi i sadržaju podataka na radnom listu:
- Zadnji redak u miješanim podacima s prazninama
- Zadnji redak u miješanim podacima bez praznih mjesta
- Posljednji redak u tekstualnim podacima
- Posljednji redak u numeričkim podacima
