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))
Figyelem!
Ez a példa csak akkor helyes, ha az „A” oszlopbna minden sor ki van töltve!
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
Tovább... →
