Excel formula: Dinamički imenovani raspon s OFFSET -

Generička formula

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

Sažetak

Jedan od načina za stvaranje dinamičkog imenovanog raspona s formulom je upotreba funkcije OFFSET zajedno s funkcijom COUNTA. Dinamički rasponi poznati su i kao prošireni rasponi - oni se automatski proširuju i skupljaju kako bi se prilagodili novim ili izbrisanim podacima.

Napomena: OFFSET je hlapljiva funkcija, što znači da se preračunava svakom promjenom radnog lista. S modernim strojem i manjim skupom podataka to ne bi trebalo stvarati problem, ali možda ćete primijetiti sporije performanse na velikim skupovima podataka. U tom slučaju razmislite o tome da umjesto toga napravite dinamički imenovani raspon s funkcijom INDEX.

U prikazanom primjeru formula koja se koristi za dinamički raspon je:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Obrazloženje

Ova formula koristi funkciju OFFSET za generiranje raspona koji se širi i skuplja podešavanjem visine i širine na temelju broja nepraznih ćelija.

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

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

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

Za širinu koristimo funkciju COUNTA za brojanje praznih vrijednosti u rasponu B5: Z5. To pretpostavlja da nema zaglavnih ćelija i zaglavlja izvan Z5. COUNTA se vraća 6.

U ovom trenutku formula izgleda ovako:

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

S tim informacijama, OFFSET vraća referencu na B5: G10, što odgovara rasponu visine 6 redaka sa 6 stupaca.

Napomena: Raspone koji se koriste za visinu i širinu treba prilagoditi tako da odgovaraju rasporedu radnog lista.

Varijacija s punim referencama stupaca / redaka

Također možete koristiti cjelokupne reference stupaca i redaka za visinu i širinu na sljedeći način:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Imajte na umu da se visina prilagođ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 zbog velike veličine punih stupaca i redaka - mora se voditi računa da se spriječe pogrešne vrijednosti izvan raspona, jer mogu lako odbaciti brojanje.

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: