Dinamikus összegzés adott hónapig – INDEX formula 2. rész

index_kFolytatjuk az INDEX formulát bemutató cikksorozatot – aki esetleg nem olvasta az első részt, és nem ismeri a formula alap használatát és szintaktikáját, az térjen rá vissza, mert ebben a cikkben az ott leírtakra támaszkodunk!

Vágjunk is mindjárt a közepébe. Az INDEX formula egy nagyon különleges tulajdonságát fogom bemutatni ma, amit egy praktikus összegző képletben fel is fogunk használni. Nem kell aggódni, mint azt már megszoktátok, az alapoktól indulunk!

A kulcs mondat, amiről a cikk szólni fog: az INDEX az Excel azon kevés formuláinak egyike, amely referenciaként is tud viselkedni. Bonyolultan hangzik? Pedig nem az! Kezdjük egy példával, és mindjárt érteni fogjátok.

Tegyük fel, hogy a táblázatodban az idei évre vonatkozó havi adatok vannak:

INDEX_05

Az utolsó befejezett hónap száma mutatja, hogy hány hónap telt már el – a fenti példában április az utolsó hónap. Eddig a hónapig tart a „tény” időszak, májustól decemberig pedig a „terv” időszak.

Kellene most egy olyan összegzést csinálni, ami a tény időszak számait adja össze, tehát januártól áprilisig. Mi sem egyszerűbb, ezt még a legkezdőbb is tudja. Egy sima SZUM (SUM) formula kell hozzá:

INDEX_06

Kész is!

De ahogy telik az idő (elmúlik a május…), megjelenik egy probléma: át kell írnunk a formulát, mert eltelt egy hónap, már májusig kellene összegezni. Meg lehetne oldani úgy ezt a feladatot, hogy ne kelljen a formulát havonta módosítani? Hogy az mindig az utolsó befejezett hónapig összegezzen? Hát persze, hogy meg lehet!

Gondoljunk csak az INDEX formulára (hamár erről szól a cikk, ugye... 🙂 ) – mit tud ez a formula megadni? Meg tudja adni a tartomány (a havi adatsor) valahányadik elemét. Vagyis meg tudja adni az utolsó befejezett hónap adatát:

INDEX_07

Beírtam a formulát a táblázat alá – az eredmény természetesen 37, a negyedik oszlop adata.

És most jön, ami még nem volt!

Ez a formula nem csak azt tudja visszaadni, hogy 37, (ami az E5 cellában található érték) hanem ennek a cellának a címét (referenciáját) is. Vagyis azt, hogy E5:

INDEX_08

Ezt persze sosem fogjuk látni: az INDEX a munkalapon mindig az eredményt (értéket) mutatja, sose a címet. De most már tudjuk, hogy van egy ilyen „álcázott” tulajdonsága, s ki tudjuk ezt használni!

Nézzük meg az első SZUM formulát:

=SZUM( B5:E5 )

és kombináljuk ezt össze azzal, hogy az INDEX az E5-öt adja vissza álcázva:

=SZUM( B5:INDEX(B5:M5; ; $F$2) )

És már kész is az adott hónapig dinamikusan összegző formulánk! Átírtam a hónapot 5-re és működik!

INDEX_09

Letölthetitek a példa fájlt, amiben felépítettem a terv időszak formuláját is, hasonló elven.

Kezdőknek itt a cikk véget is ért. Ugye, hogy nem nehéz?


Haladóknak egy kis kiegészítés: Valójában az INDEX mindig referenciát ad vissza, azonban ezt az Excel kiértékeli. Amikor az INDEX-et önmagában a cellába írtuk, akkor formulaként értékelődött ki, s visszaadta a referenciában található értéket. Ha olyan helyre írjuk az INDEX-et, ahol referenciának kell szerepelnie, (például kettőspont, mint referencia operátor után) akkor referenciaként fog viselkedni. (Ezt azonban nem lehet ellenőrizni a szerkesztőlécen nyomott F9-cel, mert az mindig kiértékeli a kijelölt formula-részt!)

 


Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!


Vélemény, hozzászólás?

Your email address will not be published.

css.php