Office Guru

A VLOOKUP függvény csak jobbra tud keresni - tehetünk valamit ez ellen?

Bizony tehetünk, egy egyszerű függvénykombinációval megoldhatjuk ezt a problémát

2015. augusztus 20. - Office Guru

Az első posztomat mi mással is kezdhetném, mint a Magyarországon szerintem leginkább elterjedt és leginkább használt Office alkalmazással, az Excellel, amely még a legjáratosabb szakértő tudorok számára is igen komoly lehetőségeket és kihívásokat rejt magában. A mai alkalommal egy valószínűleg többek munkája/feladata során felmerült kérdést és a vonatkozó megoldást fogom lépésenként bemutatni, hátha ezzel valakinek tudok egy kicsit segíteni elmélyedni az excelben és talán kilépni a megszokott keretekből. Fontos tudni, hogy én talán még sosem használtam magyar nyelvű excelt, szóval maradnom kell az angol elnevezéseknél, elnézést érte.

A kérdés tehát így szól (aki akarja, saját maga is megpróbálhatja, lentebb jön a megoldás):

Az egyik leghasznosabb és leggyakoribb Excel-függvény a VLOOKUP, amellyel az én fő problémám az szokott lenni, hogy csak egy irányba, jobbra működik, balra nem. Meg lehet oldani, hogy ne kelljen oszlopokat másolgatni a VLOOKUP-hoz a megfelelő eredmény elérése érdekében - azaz valamilyen úton balra is működtetni a keresést bármilyen másolgatás nélkül?

Példafeladat megoldással:

Ahogy látható, ha azt szeretnénk megtudni, hogy egyes városokban mennyi volt az árbevétel, azt nagyon egyszerűen meg tudjuk tenni VLOOKUP-pal. De ha azt szeretnénk bekeresni a céltáblába, hogy hány bolt van mondjuk Sopronban, akkor azt már bajosan tudnánk ezzel a függvénnyel megtenni. Persze ez most egy egyszerű példa, szóval elég könnyű más megoldásokat is találni, de a balra is működő bekeresés illusztrálására tökéletes.

elso.jpg

A megoldáshoz két függvényt kell ismernünk és együtt használnunk, az egyik az INDEX, a másik pedig a MATCH.

Az INDEX tömbökre és egyszerű táblákra is használható, most kizárólag fókuszáljunk utóbbira, azaz az INDEX-nek azon funkciójára, amellyel egy, az általunk megadott tábla meghatározott sorának és oszlopának metszetében szereplő értéket kaphatunk vissza.

Azaz például az INDEX(A1:B4,2,2) az A1:B4 tartomány második oszlopának második sorában található értéket adja ki nekünk. Ez nagyon fontos lesz, hiszen már ebből is látszik, hogy miben fog nekünk segíteni a "baloldali VLOOKUP" elérésében.

Legalább ennyire fontos és hasznos a MATCH függvény is, amely hasonlóan működik, mint az INDEX, csak nem magát az értéket, hanem annak pozícióját adja vissza nekünk egy tartományban (kérhetünk teljes egyezést is a függvény harmadik paramétereként).

Azaz például a MATCH(Sopron,F5:F12,0) meg fogja mondani nekünk, hogy Sopron pontosan hányadik sorban van az F5:F12 tartományban.

És innen már nagyon egyszerű az út egy "baloldali VLOOKUP" létrehozásához, hiszen a két függvényt kell csak egymásba ágyaznunk:

Megoldás a fenti példára: INDEX($D$5:$D$12,MATCH(F5,$F$5:$F$12,0))

masodik.jpgAz INDEX első paramétereként megadjuk azt a tartományt, ahol azok az értékek vannak, amelyeket akarunk (jelen esetben a Boltok száma, azaz D5:D12), majd második paramétereként (a többi paraméter nem kötelezően használandó) a MATCH függvényt fogjuk beágyazni, hogy abból nyerjük ki az INDEX-hez szükséges sorszámot - itt ugrunk át a MATCH-be, ahol elsőként megadjuk megadjuk, hogy milyen értékhez keresse a boltok számát (ez nálunk most az első város neve, azaz F5), második paraméterként azt a tartományt, ahol ezek az értékek vannak (nálunk ez most a városok nevét tartalmazó oszlop, azaz F5:F12), majd kérjünk teljes egyezést a 0-val. És ha ENTER-t nyomunk majd lehúzzuk a képletünket, akkor ott is a megoldás, ahogy a képen is látszik.

Ugye, hogy nem is olyan bonyolult? Bármi kérdésetek merülne fel, nyugodtan írjatok egy kommentet/e-mailt.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr177722076

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

delkreta 2015.08.21. 18:56:01

A MATCH függvény magyar megfelelője a HOL,VAN fgv. Én ezt használom office 2007-ben

delkreta 2015.08.21. 18:57:12

bocs: "HOL,VAN" ... az eredmény egyenlő

delkreta 2015.08.21. 18:58:49

no mégegyszer: "HOL.VAN" ...pont van benne!

Office Guru 2015.08.21. 20:01:14

@delkreta: Köszi a magyar megfelelőt, ez egy jól sikerült, találó fordítás, úgy látom:)

nick066 2015.08.22. 21:20:24

De hogy ki volt az marha a Microsoft-nál, aki a függvények neveit lefordította különböző nyelvekre.... ???
...hozzá lenne egy kérdésem. Hindi nyelvű windowson perui culture locale alatt futtatok vietnami Office-t. A верtтърсене() bolgár függvénynek mi megfelelője ?

Csillagszemű Juhász 2015.08.22. 23:37:51

Ez tök jó. Hajrá, folytasd, én követni foglak

ppppalika 2015.08.23. 07:04:16

Hmmm... Ez az a szint, amit középszintű érettségin is illik tudni. Ez az, amit az utóbbi kb. 15-20 évben minden középiskolásnak tanulnia kellett - volna.

Ezzel nem azt akarom mondani, hogy a bejegyzés haszontalan, hanem valami fontosabbat.

Narvál 2015.08.23. 11:07:53

Először is sok sikert kívánok a blogodhoz.

Másodszor egy apró javítást javaslok:
INDEX($D$5:$D$12,MATCH(I5,$F$5:$F$12,0))

A MATCH függvényben az I oszlop a keresés kiindulási pontja, hiszen ahhoz az értékhez kell megkeresni egy másik tömbben lévő adatot.

Mákony Béla 2015.09.11. 19:12:02

Én ilyesmire már inkább pivot táblát használok, teljesen jelentőségét veszti, mi hol van az adattáblában, és egy analfabéta is tud dolgozni vele 2 percnyi instrukció után :)
süti beállítások módosítása