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 INDIREK 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.

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.

Excel INDIREKT függvény

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:

Indirekt függvény

A különböző havi eredménykimutatások munkalapjai az adott hónap nevével vannak elnevezve:

indirekt függvény

És az eredmény pedig a következő kell, hogy legyen:

Indirekt függvény

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:

Indirekt függvény

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.

Indirekt függvény

A munkalapjaink a következő neveket viselik:

Indirekt függvény

És a kívánt eredmény:

Indirekt függvé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:

Indirekt függvény

Í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! 🙂