«

»

máj 04

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!


4 comments

Skip to comment form

  1. Filitheyo

    Szuper kis formula, nagyon tetszik :) A legnagyobb trükk a tervadatok összegzésében van, hogy a befejezett hónapok számához beírt számmal még műveletet is lehet végezni, és azt megadni az indexnek. Holnap a gyakorlatban is kipróbálom:)

  2. Tímár Erika

    Szia,
    Tegnap együtt voltunk a BI tréningen. Tetszik az oldalad, látszik, hogy sok munka van benne! :-)
    Erre a dinamiius összegzésre és a SUM-OFFSET beágyazást szoktam használni. Ha egy legördülőben a hónapok nevei vannak és nem szám, akkor a következő is lehet megoldás : =SUM(OFFSET(B5;0;0;1;MATCH($F$2;$B$4:$M$4;0)))
    A te megoldásodat mentem. :-)
    Köszi, Erika

  3. Kris
    Kris

    Szia Erika!
    Köszönöm a visszajelzést, nagyon örülök, hogy tetszik az oldal. :-)

    A te megoldásod is teljesen jó OFFSET formulával. Az OFFSET azonban volatile formula, ami bizonyos esetekben nagyon lelassíthatja a kalkulációt a munkafüzetben, ezért én nem szoktam javasolni a használatát.
    S ha már így felmerült ez a téma, felveszem a megírandó cikkek listájára, hogy a volatilitás fogalmáról is írjak. :-)
    Üdv:
    Kris

  4. Árpád

    Üdv!
    Nagyon érdekes megoldás – éppen csak haladó szinten vagyok Excelben – még gondolkodom, hogy hová tudnám használni, de projektmenedzsmentben egy-kettő meg lesz a helye.
    A blog nagyon jó, gyakran jövök nézelődni a posztok között.
    Az Excelhez tartozó új funkciókkal is fogsz majd foglalkozni, mint pl. a PowewQuery, PowerPivot?

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

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöljük.

Az alábbi HTML kódok használhatóak: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

css.php