Folytatjuk 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:
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á:
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:
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:
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!
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
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:)
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
Author
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
Ü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?