Představte si, že máme nějaké údaje o prodeji pro společnost:
A že si přejeme zjistit celkové údaje za daný rok. Můžeme přidat rozevírací seznam takto:
Abychom mohli určit aktuální měsíc. Proto nyní chceme zpracovat dosavadní rok v březnu. Nejjednodušším formátem by bylo mít vzorce, které by se rozšířily v celém rozsahu:
A pak bychom jen měnili vzorce každý měsíc.
Excel však umožňuje jiný přístup. Mohli bychom nastavit dynamický rozsah, jehož velikost se měnila v měsíci, ve kterém se nacházíme. Jak měníme měsíc v rozevíracím seznamu, pak se velikost rozsahu mění.
Takže pro měsíc březen je rozsah dlouhý 3 sloupce a pro měsíc červen to bude 6 měsíců.
Velikost rozsahu se řídí měsícem. Jedním ze způsobů, jak to formulovat, je použít funkci Měsíc:
= Měsíc (c8)
Kde c8 je adresa buňky našeho rozevíracího seznamu. Upřednostňovanou metodou je však použít funkci MATCH k určení polohy aktuálních měsíců ve všech měsících v naší zprávě:
MATCH (c8, $ c $ 3: $ j $ 3,0)
Kde:
• c8 je adresa buňky aktuálního měsíce
• C3: J3 je adresa všech našich měsíců
• 0 má zajistit přesnou shodu
Nyní můžeme určit velikost našeho dynamického rozsahu funkcí OFFSET, která má 5 argumentů:
= OFFSET (reference, řádky, sloupce, výška, šířka)
Kde:
• Reference je levý horní roh našeho dynamického rozsahu - buňka C5 - první buňka, kterou chceme sčítat
• Řádky - počet řádků dolů od naší základní buňky - to je 0
• Cols - počet cols napříč naším základním hovorem - to je 0
• Šířka našeho dynamického rozsahu - což jsou v tomto případě 3. Protože si však přejeme, aby se rozsah lišil podle měsíce, vložíme sem naše vzorce MATCH
• Toto je výška našeho dynamického rozsahu, která je 1
Naše vzorce OFFSET jsou tedy:
= OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1)
Nakonec musíme aplikaci Excel sdělit, aby poskytla úplné vzorce jako:
= SUM (OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1))
My máme:
Pokud nyní změníme měsíc v rozevíracím seznamu, protéká správný údaj k aktuálnímu roku:
Jelikož se jedná o automatickou aktualizaci, má tento přístup následující výhody:
• Není nutné měnit vzorce každý měsíc
• Protože dochází k menším změnám vzorců, menší prostor pro chyby
• Tabulku může použít někdo, kdo má omezené znalosti Excelu - může pouze změnit rozevírací seznam a neobtěžovat ho vzorce
