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 pings