Képzeljük el, hogy van néhány értékesítési adatunk egy vállalatról:
És szeretnénk megtalálni az eddigi év összesített számadatait. Ehhez hozzáadhatunk egy legördülő menüt:
Így megadhatjuk az aktuális hónapot. Ezért most az év márciusát szeretnénk kidolgozni. A legegyszerűbb formátum az lenne, ha a képletek kiterjednének a tartományra:
És akkor csak havonta változtatnánk a képleteket.
Az Excel azonban megenged egy másik megközelítést. Létrehozhatnánk egy dinamikus tartományt, amelynek mérete az adott hónapban változott. Ha a legördülő menüben megváltoztatjuk a hónapot, akkor a tartomány mérete megváltozik.
Tehát március hónapban a tartomány 3 oszlop hosszú, június hónapban pedig 6 hónap.
A tartomány méretét a hónap határozza meg. Ennek egyik módja a Hónap funkció használata:
= Hónap (c8)
Ahol c8 a legördülő menü cellacíme. Azonban az előnyben részesített módszer az, hogy a MATCH függvény segítségével határozzuk meg az aktuális hónapok helyzetét a jelentésünk minden hónapjában:
MATCH (c8, $ 3 $: $ j $ 3,0)
Ahol:
• c8 az aktuális hónap cellacíme
• C3: J3 minden hónapunk címe
• A 0 a pontos egyezés biztosítása
Most megadhatjuk a dinamikus tartomány méretét az OFFSET függvénnyel, amely 5 argumentumot tartalmaz:
= OFFSET (hivatkozás, sorok, oszlopok, magasság, szélesség)
Ahol:
• A referencia a dinamikus tartományunk bal felső sarka - a C5 cella - az első cella, amelyet összegezni szeretnénk
• Sorok - a sorok száma lefelé az alapcellánktól - ez 0
• Cols - az alaphívásunkkal szemben lévő colok száma - ez 0
• Dinamikus tartományunk szélessége - ami ebben az esetben 3. Azonban, ha azt szeretnénk, hogy a tartomány havonta változzon, itt feltesszük a MATCH képleteinket
• Ez a dinamikus tartományunk magassága, amely 1
Tehát az OFFSET képleteink a következők:
= OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1)
Végül meg kell mondanunk az Excelnek, hogy ezt összegzi, hogy a következő képleteket kapjuk:
= SUM (OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1))
Nekünk van:
Ha most megváltoztatjuk a hónapot a legördülő menüben, akkor a megfelelő évszám jelenik meg:
Mivel ez egy automatikus frissítés, ennek a megközelítésnek a következő előnyei vannak:
• Nincs szükség havi képletek módosítására
• Mivel kevesebb képletmódosítás történik, kevesebb a hibalehetőség
• A táblázatot olyan személyek is használhatják, akiknek korlátozott az Excel ismerete - csak megváltoztathatják a legördülő menüt, és nem zavarják a képletek
