Predstavte si, že máme nejaké údaje o predaji pre spoločnosť:
A že by sme chceli nájsť celkové údaje za daný rok. Rozbaľovací zoznam môžeme pridať takto:
Aby sme mohli špecifikovať aktuálny mesiac. Preto teraz chceme vypracovať doterajší rok v marci. Najjednoduchším formátom by bolo mať vzorce, ktoré by sa rozšírili v celom rozsahu:
A potom by sme každý mesiac menili vzorce.
Excel však umožňuje iný prístup. Mohli by sme nastaviť dynamický rozsah, ktorého veľkosť sa líši v mesiaci, v ktorom sa nachádzame. Keď v rozbaľovacom zozname zmeníme mesiac, zmení sa aj veľkosť rozsahu.
Takže za mesiac marec je rozsah dlhý 3 stĺpce a za mesiac jún to bude 6 mesiacov.
Veľkosť rozsahu sa riadi mesiacom. Jedným zo spôsobov, ako to formulovať, je použiť funkciu Mesiac:
= Mesiac (c8)
Kde c8 je adresa bunky nášho rozbaľovacieho zoznamu. Preferovanou metódou je však použiť funkciu MATCH na určenie polohy aktuálnych mesiacov vo všetkých mesiacoch v našej správe:
ZÁPAS (c8, $ c $ 3: $ j $ 3,0)
Kde:
• c8 je adresa bunky aktuálneho mesiaca
• C3: J3 je adresa všetkých našich mesiacov
• 0 znamená zaistenie presnej zhody
Teraz môžeme určiť veľkosť nášho dynamického rozsahu pomocou funkcie OFFSET, ktorá má 5 argumentov:
= OFFSET (referencia, riadky, stĺpce, výška, šírka)
Kde:
• Referencia je horný ľavý roh nášho dynamického rozsahu - bunka C5 - prvá bunka, ktorú chceme sčítať
• Riadky - počet riadkov nadol od našej základnej bunky - to je 0
• Cols - počet cols oproti nášmu základnému hovoru - toto je 0
• Šírka nášho dynamického rozsahu - čo sú v tomto prípade 3. Keďže si však želáme, aby sa rozsah líšil podľa mesiaca, vložíme sem naše vzorce MATCH
• Toto je výška nášho dynamického rozsahu, ktorá je 1
Naše vzorce OFFSET sú teda:
= OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1)
Nakoniec musíme Excelu povedať, aby to SÚČET poskytol úplné vzorce ako:
= SÚČET (OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1))
Máme:
Ak teraz zmeníme mesiac v rozbaľovacom zozname, správny údaj k dátumu k dátumu preteká:
Keďže ide o automatickú aktualizáciu, tento prístup má nasledujúce výhody:
• Nie je potrebné meniť vzorce každý mesiac
• Pretože existuje menej zmien vzorcov, menší priestor pre chyby
• Tabuľku môže použiť niekto, kto má obmedzené znalosti programu Excel - môže len zmeniť rozbaľovaciu ponuku a neobťažovať sa vzorcami.
