Dinamički imenovani raspon s OFFSET -om

Jedan od načina za stvaranje dinamike imenovani raspon s formulom je koristiti funkciju OFFSET zajedno s funkcijom COUNTA. Dinamički rasponi poznati su i kao proširujući rasponi - oni se automatski proširuju i skupljaju kako bi prilagodili nove ili izbrisane podatke.

Napomena: OFFSET je promjenjiva funkcija, što znači da se ponovno izračunava sa svakom promjenom na radnom listu. S modernim strojem i manjim skupom podataka to ne bi trebalo uzrokovati problem, ali možda ćete vidjeti sporije performanse na velikim skupovima podataka. U tom slučaju razmislite o izgradnji dinamičkog imenovanog raspona s funkcijom INDEX.

U prikazanom primjeru formula za dinamički raspon je:

= OFFSET (origin,0,0, COUNTA (range), COUNTA (range))

Ova formula koristi funkciju OFFSET za generiranje raspona koji se širi i skuplja prilagođavanjem visine i širine na temelju broja ćelija koje nisu prazne.

Prvi argument u OFFSET -u predstavlja prvu ćeliju u podacima (ishodište), koja je u ovom slučaju ćelija B5. Sljedeća dva argumenta su pomaci za retke i stupce i isporučuju se kao nula.

kako ući u sljedeći redak u

Zadnja dva argumenta predstavljaju visinu i širinu. Visina i širina generiraju se u hodu pomoću COUNTA -e, što rezultirajuću referentnu referencu čini dinamičnom.

Za visinu koristimo funkciju COUNTA za brojanje praznih vrijednosti u rasponu B5: B100. To ne pretpostavlja prazne vrijednosti u podacima i vrijednosti izvan B100. COUNTA vraća 6.

Za širinu koristimo funkciju COUNTA za brojanje praznih vrijednosti u rasponu B5: Z5. Ovo ne pretpostavlja ćelije zaglavlja i zaglavlja izvan Z5. COUNTA vraća 6.

U ovom trenutku formula izgleda ovako:

 
= OFFSET (B5,0,0, COUNTA ($B:$B0), COUNTA ($B:$Z))

S tim podacima OFFSET vraća referencu na B5: G10, što odgovara rasponu od 6 redova visine do 6 stupaca.

Napomena: Raspone koji se koriste za visinu i širinu potrebno je prilagoditi tako da odgovaraju izgledu radnog lista.

Varijacija s punim referencama stupaca/redaka

Za visinu i širinu možete koristiti i pune reference stupaca i redaka:

 
= OFFSET (B5,0,0,6,6)

Imajte na umu da se visina podešava s -2 kako bi se uzele u obzir vrijednosti zaglavlja i naslova u ćelijama B4 i B2. Prednost ovog pristupa je jednostavnost raspona unutar COUNTA -e. Nedostatak dolazi od velikih stupaca i redaka velike veličine - mora se voditi računa da se spriječe pogrešne vrijednosti izvan raspona jer mogu lako odbaciti brojanje.

Određivanje zadnjeg retka

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 red u mješovitim podacima s prazninama
• Zadnji red u mješovitim podacima bez praznina
• Posljednji redak u tekstualnim podacima
• Posljednji red u numeričkim podacima