Excel 2010

Gyakran előforduló feladat lehet, hogy feltételtől függően egy cellába változó képletet kellene írni vagy végrehajtani. Különösen igaz lehet ez abban az esetben, amikor a felhasználó állítja be pl. egy legördülő menüben, hogy mi legyen a feltétel, és ennek függvényében próbáljuk a képleteket dinamikusan összeállítani.

A modell

Egy munkalapon adott egy azonosító (ID) oszlop, és mellé egy másik munkalapról ki szeretnénk keresni a hozzá tartozó nevet. Igen ám, de azt, hogy melyik munkalapról kell kikeresni, azt egy legördülő menüből szeretnénk választani. Ez egy klasszikus VLOOKUP (FKERES) feladat, de a keresési tábla a legördülő menü szerint változó.

A modellt úgy állíthatjuk fel, hogy a különböző országokhoz tartozó adatok, amiben keresni akarunk, külön-külön munkalapokon vannak, és a munkalapok neve, megegyezik a legördülő menüből választható nevekkel, a mi példánkban „HU” és „DE”.

A „HU” és „DE” lapokon az adatokat táblázattá (Listává) érdemes alakítani, hogy név szerint is lehessen hivatkozni a képletekben, és így a méretük sem kell, hogy egyforma legyen.

Megoldás HA (IF) függvénnyel

Legegyszerűbb megoldásként a képlet feltételes megadását választhatjuk, azaz HA a kiválasztott országkód a B11-es cellában van, akkor a C14-be a következő képletet írhatjuk:

=IF($B$11="HU";VLOOKUP(B14;HU!$A$2:$B$5;2;0);IF($B$11="DE";VLOOKUP(B14;DE!$A$2:$B$5;2;0);0))

Látható, hogy egymásba ágyazott IF függvényeket látunk, és a képlet a B11 tartalmától függően, vagy a HU vagy a DE munkalapról keres, ugyanabban a tartományban. Ezután a képletet lehúzhatjuk a többi ID-re is.

Előny

Az IF (HA) függvényt a legtöbben ismerik, tudják használni.

Hátrány

Több országkód esetén meglehetősen hosszú és összetett lehet a képlet. (91 karakter)

Névtartományok használata

Ha a magyar és a német munkalapokon lévő keresési tartományokat dinamikus névtartománnyá alakítjuk, (lásd a fenti ábrán: HU és DE), akkor a képlet jelentősen lerövidülhet. (71 karakter)

=IF($B$11="HU";VLOOKUP(B14;HU;2;0);IF($B$11="DE";VLOOKUP(B14;DE;2;0);0))

Megoldás CHOOSE (VÁLASZT) függvénnyel

A függvényről külön oktatóanyag készült: https://excel-bazis.hu/tutorial/a-valaszt-choose-fuggveny

Azért jön számításba, mert a példa szerint más-más munkalapokról kell keresnünk értékeket. Ha ezeket sorszámmal tudnánk ellátni, akkor máris elkészült egy, az előző (HA) szerkezetnél rövidebb képlet.

A sorszámot egy segédtábla, és VLOOKUP használatával állítjuk elő

Tehát a VÁLASZT függvény számára a sorszámot a C24 tartalmazza. Most csak a rövidebb, névtartományokat használó képletet írom le, ami a D27-be kerül. (53 karakter)

=CHOOSE($C$24;VLOOKUP(B27;HU;2;0);VLOOKUP(B27;DE;2;0))

Megoldás INDIRECT (INDIREKT) függvénnyel

Az INDIRECT függvényt leggyakrabban arra használjuk, hogy dinamikusan, szöveges hivatkozásokat képzünk, amiket Ő normál hivatkozásokká tud alakítani.

Nézzünk egy példát:

Egy oszlop utolsó kitöltött cellájának értéke

Itt az adja a kihívást, hogy nem tudjuk, hány sor van kitöltve az adott oszlopban. Viszont ki tudjuk számolni, pl. az „A” oszlopban:

=COUNTA(A:A)

Tehát össze kell állítanunk az „A” oszlop utolsó cellájának a címét, ami ugyebár tuti „A”-val kezdődik, és a sor része meg az előző függvény eredménye. Van nekünk egy remek összefűző jelünk, és már mehet is.

=INDIRECT("A"&COUNTA(A:A))

Visszatérve a fenti példához, az INDIRECT függvénnyel azt használjuk ki, hogy az országkód kiválasztása után ugyanolyan képletet kell használni (VLOOKUP), csak a belseje lesz más, mert egyszer a HU, máskor a DE lapon vagy névtartományban kell keresni. ha a „HU” országkódot választottuk, akkor a „HU!A2:B5” tartományban, és „DE” kód esetén pedig a „DE!A2:B5” tartományban. Névtartományok használata esetén pedig a „HU” vagy a „DE” neveket használjuk.

Próbáljuk belefűzni a képletbe dinamikusan a választott országkódot, ami a „B35”-ben van:

=VLOOKUP(B39;INDIRECT($B$35&"!A2:B5");2;0)

Letölthető munkafüzet: Valtozo-adatforras-modellek