Egy korábbi posztban szó volt a KARAKTER függvényről, ahol említettem, hogy egy jól használható esete, amikor az INDIREKT függvénnyel szeretnénk dolgozni. De mire is való az Excel INDIREKT függvény?
Ezzel a két függvénnyel szövegdarabból tudunk hivatkozást formálni. Ez olyan esetekben hasznos számunkra, amikor nem a „hagyományos” módon szeretnénk, hogy a függvényeink mozogjanak, ahogy újabb és újabb cellákba másoljuk őket, hanem egy részét nekünk kell megadni, mint változót.
Mielőtt rátérünk egy konkrét példára, nézzük meg, hogy az INDIREKT (angolul: INDIRECT) függvényt hogyan is kell megírni.
A fájlt innen letöltheted: INDIREKT függvény feladat
Excel INDIREKT függvény
A feladatot és levezetését, megoldását Gere-Nagy Dorottya oktatónk készítette, én csak bemutatom videós formában.
Két argumentuma van, amiből az első az a szövegdarab, amit hivatkozássá szeretnénk átalakítani. A második pedig megadja, hogy az általunk jól ismert „A1”-es megnevezéssel kezelje a cellákat (IGAZ vagy elhagyható), vagy az S1O1 hivatkozási típust használja (HAMIS).
Tehát a C6-os cellára való hivatkozás így alakítható át:
=C6 -> =INDIREKT(„C6”)
Természetesen nem ilyen egyszerű esetekben használjuk, de így jól látszik, hogy összesen annyi a dolgunk, hogy a „normál” hivatkozást szövegesen megadjuk a függvény első argumentumaként.
INDIREKT függvény példa: hónapválasztás
De nézzünk is egy példát! Tegyük fel, hogy a cégünk különböző munkalapokon gyűjti az adott havi eredménykimutatás pontos adatait, és nekünk készíteni kell egy összefoglaló munkalapot, ahol a hónap kiválasztását követően, csak az adott hónaphoz tartozó adatok jelennek meg.
Példaként a Január havi eredménykimutatás:
A különböző havi eredménykimutatások munkalapjai az adott hónap nevével vannak elnevezve:
És az eredmény pedig a következő kell, hogy legyen:
A sárga cellában a legördülő listából választhatjuk ki a megfelelő hónapot.
Erre a problémára természetesen nem az egyetlen megoldás az INDIREKT függvény használata, mert akár egy többszörösen egybeágyazott HA függvény is megoldást jelenthet, de sok választási lehetőség esetén a megírása eléggé hosszadalmas.
Nézzük meg, hogyan is néznek ki ezek a cellahivatkozások JAN esetében:
Mivel mind a három hónap struktúrája teljesen megegyezik, ezért ahhoz, hogy a kiválasztott hónap függvényében kapjunk vissza eredményeket csak a hivatkozás elejét kell változónak tekintenünk, és a többi része állandó lehet.
Így tehát az első cella hivatkozását a következőképpen tudjuk az INDIREKT függvénnyel megfogalmazni:
(a legördülő listánk a C11-es cellában van)
=INDIREKT($C$11 & “!B3”)
Így tehát a C11 visszaadja a kiválasztott hónap nevét, és utána már csak szövegként meg kellett adnunk a hivatkozás maradék részét, azaz a !B3-at.
Ami nagyon fontos, hogy a legördülő lista elemei pontosan egyezzenek meg a munkalapok nevével, mert csak így tud működni a függvény. Egyébként egy ismeretlen munkalapra hivatkozna.
A többi cella esetében pedig csak a B3-at kell a megfelelő cellahivatkozásra módosítani, és máris működik a riportunk.
INDIREKT függvény példa: oszlopok mozgatása
A következő példában már kicsit összetettebb a helyzet. Különböző Termelő cégek első hat hónapi eredménykimutatásunk van meg, és a Termelőt választva szeretnénk visszakapni az eredményeket.
A munkalapjaink a következő neveket viselik:
És a kívánt eredmény:
Ebben az esetben is ugyanúgy használható az INDIREKT függvény, de mivel nem szeretünk sokat gépelni, ezért szeretnénk elkerülni azt, hogy a különböző hónapok oszlopainak hivatkozását magunknak kelljen megtenni.
Ezért nézzük meg, hogy hogyan tudjuk a hivatkozás minden darabját változóvá tenni!
Az első cellában lévő képlet a következő:
=Termelő1!B3
A hivatkozás különböző darabjai:
• Termelő1
• !
• B
• 3
Ebből a Termelő1, azaz a munkalap neve egészen egyszerűen hivatkozható a legördülő listából, ami ebben az esetben a C24-es cellában van.
A B, azaz az oszlop megnevezéshez lesz szükségünk a KARAKTER függvényre. A függvény 65 és 90 között az angol ABC betűit adja vissza, így a B-t a KARAKTER(66) eredményezi. A 3 pedig az adott érték sorának a száma.
Tehát ezt a hivatkozást a következőképpen tudjuk leírni:
=INDIREKT($C$24 & “!” & KARAKTER(66) & “3”)
Ahhoz, hogy teljesen változóvá tegyük, felveszünk egy segéd sort és oszlopot, hogy a KARAKTER függvény argumentumát és a sor számát is automatikusan tudja a függvény hivatkozni:
Így tehát az első cellában szereplő képlet:
=INDIREKT($C$24 & “!” & KARAKTER(C$26) & $B28)
Ez a függvény pedig nyugodtan másolható a tábla többi cellájába is, hiszen a megfelelő rögzítésekkel ($) nem mozdulnak el a hivatkozott segédcellák sem.
A fentiekben tehát láthattál egy példát a KARAKTER függvény használatára. A fenti példa alternatív megoldása, ha a segédsorba a 66, 67 stb. helyett a megfelelő oszlopneveket viszed be (B, C, D stb.) és ezeket fűzöd össze a KARAKTER függvény nélkül. Több megoldás is létezik tehát, ahogy a legtöbb esetben. A számok előnye, hogy lehet jobbra húzni és sorozatként kitölti, a betűket ellenben nem. Az előnyök és hátrányok tudatában tehát válaszd azt, amelyik szimpatikusabb.
Gyakorolj és Te is belejössz a lehetőségek felfedezésébe!
Remélem, hogy hasznodra válik ez a függvény is! Ha tetszett, kérlek, oszd meg a bejegyzést másokkal is!
ISMERD MEG INGYEN AZ EXCEL KIMUTATÁS FUNKCIÓJÁT
Annyira fontos funkció az Excelben a kimutatások ismerete, hogy szeretném, ha Te is megismerhetnéd és használnád a munkád során, így most ajándékként megkapod tőlem! Hét rövid videóból Te magad is végigmehetsz a lépéseken, hogy jobban megértsd működését. Vágj bele most és használd bátran a munkádban! 🙂
