Dinamična območja in funkcija OFFSET

V današnjem blogu sem imel namen pisati o dinamičnih grafih, to je o grafih, ki prikažejo toliko podatkov, kot jih je v tabeli, torej se prilagajajo količini podatkov.

A ob pisanju se je izkazalo, da bi bil blog prevelik, zato sem tematiko razbil na 3 sklope in danes bomo spoznali prvega – dinamična območja.

Dinamična območja

Vsi ki uporabljamo Excel uporabljamo tudi območja. Ko namreč zaukažemo Excelu naj nam pove seštevek nekih števil, mu pač podamo območje na katerem se ta števila nahajajo in Excel jih sešteje.

Kaj pa se zgodi, ko število podatkov raste? No Excel nam že dobrih 10 let pri tem intenzivno pomaga in v kolikor vrivamo vrstice v tabelo in imamo na koncu seštevke, je Excel že dovolj pameten, da sam poveča območje, saj sklepa da je to tisto, kar bi tudi mi želeli. In v večini ima prav; kakopak :)!

Kaj pa ko imamo samo tabelo s podatki na eni strani in izračune na drugi strani? Ko v takšni tabeli dodajamo podatke na konec, pa Excel ne popravlja več formul. Dobro; novejše verzije Excela vam v celico s formulo narišejo majhen trikotnik – opomnik, kjer vas opozarjajo, da bi se znalo zgoditi, da formula ni več pravilna, to pa je tudi vse – funkcija sam se ne popravi!

Če imate torej takšno tabelo (recimo da se nahaja na področju C4:D10) in imate nekje drugje izračunano povprečno mesečno prodajo (=AVERAGE(D5:D9)), bo le ta znašala 202.419,00€. Če sedaj k tabeli dopišete še mesec junij pa se vrednost povprečja ne bo več spremenila!

Štetje polnih celic

Da bi rešili takšno zagato pa torej formuli ne moremo več podati fiksnega območja, temveč moramo formuli nekako zaukazati naj prešteje koliko je polnih celic in upošteva tako veliko območje.
Število polnih celic nam pove funkcija COUNTA. In če ji zaukažemo (ne pozabite da se naša tabela nahaja na območju C4:D10!):

=COUNTA(D4:D100)

Bomo dobili vrednost 6, saj je polnih 6 celic.

Funkcija OFFSET

Torej število celic znamo prešteti, sedaj pa potrebujemo še neko drugo funkcijo, ki nam bo na podlagi velikosti nekega območja tudi vrnila podatek o tem območju. Kot (pravilno!) sklepate je to seveda funkcija OFFSET.

Funkcija sprejme 5 parametrov:

• Prvi parameter je neka začetna celica
• Drugi parameter določa koliko vrstic se želimo odmakniti od začetne celice
• Tretji parameter določa koliko kolon se želimo odmakniti od začetne celice
• Četrti parameter določa velikost območja v vrsticah
• Peti parameter določa velikost območja v kolonah

Če torej zapišemo funkcijo:

=OFFSET(A1;4;10;5;4)

S tem zaukažemo. Postavi se v celico A1 in se od nje odmakni za 4 vrstice in 10 kolon – torej dobimo celico K5 (od A do K je 10 kolon in od 1 do 5 so 4 vrstice). S tem si dobil prvi kot območja. Velikost območja pa je 5 vrstic in 4 kolone, torej K5:N9.

In kako mi to pomaga?

No če združimo funkciji COUNTA in OFFSET pa lahko dobimo dinamično območje in tako namesto

=AVERAGE(D5:D9)

raje zapišemo

=AVERAGE(OFFSET(C4;1;1;COUNTA(D4:D100)-1;1))

S tem dobimo funkcijo, ki računa povprečje za vse vnesene celice. Če vnesemo več celic je območje večje, če jih vnesemo manj je manjše.

• OPOMBA1: Kaj točno pomeni ta »dolga« funkcija? Od celice C4 (gornji kot naše table) se pomakni za eno celico desno in dol ==> nahajamo se na prodaji za januar, torej celici D5. Od te celice pa vzami velikost območja tolikšno kot je število nepraznih celic v območju D4:D100 (1 odštejemo, ker je tudi naslov polna celica!).
• OPOMBA2: Seveda bi bilo povprečje pravilno, četudi bi napisali kar fiksno območje D4:D100, a to sedaj ni važno. Zamislite si, da je funkcija pač kompleksnejša in je zares odvisna od števila polnih celic. Tukaj sem želel prikazati samo preprost primer!