Excel csodafegyver: FKeres / VLookup
2010 október 6Ez a függvény egy vízválasztó az Excel tudásában.
Állásinterjúk felvételi tesztjénél a körlevélhez hasonlóan ez az egyik sűrűn ellenőrzött/ellenőrzendő ismeret.
Hozzáteszem: Nem véletlenül!
Szükséges, mert valós problémát old meg, amit vagy rengeteg manuális munkával, vagy csak ennél bonyolultabb
függvénykombinációval lehetne kiváltani.
Vízválasztó, mert ez egy relatív bonyolult függvény.
Most nézzük az egyik felhasználási módját, egy késöbbi cikkben pedig kitérek a másik verzióra is.
A példában pontszámok alapján osztályzatokat kerestetünk ki, de ugyanígy lehetne bónuszokat, vagy kedvezményeket is kerestetni.
Az FKeres a függőlegesen keres rövidítése. A 2007-es excelben a “Keresés” függvénycsoportban, a 2003-as excelben a “Mátrix” kategóriában található.
A függvényt az ábrán a C2-es cellába szúrjuk be, mert ide kell megtalálnunk “Most Márió” pontszámához tartozó osztályzatot.
A függvény paraméterei:
Keresési érték: “Most Márió” pontszámát tartalmazó cella, mert ez alapján kerestetünk meg valamit.
Tábla: Ponthatár-Érdemjegy táblázat, melynek első oszlopából a függvény kikeresi a pontszámot és visszaadja a pontszámhoz tartozó értéket. Ezt a hivatkozást rögzítjük, hogy a függvény másolásakor is a megfelelő helyről keressen.
Oszlopszám: A ponthatár-érdemjegy tábla hányadik oszlopából szeretnénk az eredményt kiolvastatni.
A Tartományban_keres-t most üresen hagyjuk.
Miután Kész-et nyomtunk a függvényszerkesztőben, a függvényünket a többi személyhez is lemásoljuk, így mindenkihez megjelenik az osztályzata.
Ha a ponthatár táblázat ponthatárain módosítunk, akkor mindenhol átszámítódnak az osztályzatok is.
Elhelyeztem még egy képet a facebook oldalon a témában. Nézd meg azt is! http://www.facebook.com/hatekonysag
Hamarosan egy nagyon jó hírrel jelentkezem, majd azt követően visszatérünk az FKeres másik felhasználási területére.
18 hozzászólás
Naponta használom
sőt oktatom 
Váraljay Gabriel 2010 október, 6 - 14:02:37
Hát igen, ez kötelező tudás, sok esetben nélkülözhetetlen is.
Aki nem ismeri dolgozhat sokat feleslegesen…
De, személy szerint szívesen látnék példát az OFSZET függvényre is.
Takács Gábor 2010 október, 6 - 14:15:27
Ez egy jó függvény, sokat használom.
Üdv: B. Edit
Baumgartner Edit 2010 október, 6 - 14:23:18
Képt apró adalék az FKERES-hez, lehet hogy másnak is volt ilyen problémája. Főleg akkor fordulnak elő ha másoktól kapott excel táblákban akarok FKERES-sel dolgozni:
- Beirom a képletet és eredmény helyett magát a képletet irja ki.
Megoldás: én ilyenkor egy másik üres munkafüzet egy cellájának a formátumát másolom rá a képletre. F2 majd ENTER és már jó is.
- Van hogy látszólag teljesen egyforma a két összekeresendő oszlop formátuma, látok is az összetertozó értékekre példát de az istennek sem találja meg az FKERES semmit pedig már mindent megpróbáltam, közös formátumra hoztam az oszlopokat stb.
Megoldás: Ráállok mindkét oszlopra egyenként.
Adatok – Szövegből oszlopok – fixed – column data format: text.
Unána már megtalálja az FKERES az összetartozó értékeket.
KVH 2010 október, 6 - 17:48:06
Napi szinten használom. Számomra nélkülözhetetlen. Mint ahogy szerintem sok mindenkinek, akik még nem is tudnak róla. Az egyik legjobb, leghasznosabb függvény!
Németh Tibor 2010 október, 7 - 08:31:56
Fontos apróság:
Előfordulhat, hogy a keresett érték éppen valamelyik ponthatár-érték, és bár egész számként jelenik meg, de valójában felfelé kerekített tört (pl. egy számítás eredménye), akkor a kisebb érték párját rendeli hozzá a képlet.
(Megoldás: Eszközök/Beállítások/Számolás -Munkafüzet bejelölni: A mutatott pontosság szerint – Figyelmeztet, hogy az adatok véglegesen elveszítik pontosságukat – elfogadhatjuk, de csak akkor, ha máshol nincs szükségünk ezekre a pontosabb adatokra.
Ez esetben megváltoztathatjuk a ponthatár listát pl 50 helyett 49,6 vagy akár 49,99999 értéket választhatunk, a szükséges pontosság, szándékaink, és a feladat elméleti megfontolása alapján)
Bogár Zsuzsanna 2010 október, 7 - 18:14:00
Ja, alap.
De egy kicsit butácska is. Ugyanis, a találat előtti utolsó rekordot adja eredményül, mindegy, hogy mekkora hibával.
Ha mondjuk van egy lista, amiben keresgélsz, s mondjuk ez a két rekord szerepel benne:
– 80.2
– 81.7
A keresett értéked pedig: 81.6, akkor bizony a 80.2 lesz a válasz, holott nyílvánvaló, hogy a helyes eredmény a 81.7.
Árva Ferenc 2011 március, 16 - 16:07:42
Valójában át kellett írni a függvényt ahhoz, hogy jó legyen:)
Árva Ferenc 2011 március, 16 - 16:09:51
Kaptam egy több ezer nevet tartalmazó listát, amit több másikból egyesítettek.
Feladat: szűrjem ki a többször is előforduló neveket.
De ne törölje, mert lehet többféle emailcímet adott meg, és azok is kellenek.
Ezt melyik függvénnyel tudom megtenni? Nem színezni akarom, hanem válogassa ki egymás alá az azonosakat, amit törlök, ha minden adata egyezik. 2007-es Excelt használok, de nem régóta, és csak azt találtam meg, hogy a teljesen azonosakat törli. függvényben nem vagyok túl jó.
Kérek szépen segítséget lépésről lépésre.
Köszönöm előre is!
Bohn Andrea 2011 április, 1 - 18:28:02
Szia, Andrea!
1. Mentsd el az eredetit, és egy másolati példányban dolgozz.
2. Ha nincs az eredeti táblázatnak fejléce, készíts. (jelöld ki az első sort; szúrj be egy sort, írd be a cellákba, hogy az oszlop milyen adatot tartalmaz, vezetéknév, utónév, település, stb)
3. Takarítsd ki az egyesített cellákat, ha vannak ilyenek. (egyenként ráállsz az egyesített cellára, és megnyomod a cellák egyesítése gombot, ha túl sok van, kitalálunk valami mást)
4. Jelöld ki az egész táblázatot.
5. Adatok menüpont. Sorbarendezés Válaszd ki a Név (vagy Vezetéknév) oszlopot, hogy aszerint rendezze sorba.
(Lehet második szempont az utónév, de csak akkor szükséges, ha külön oszlopban van) (Ha több azonos név is szerepel a listában, lehet következő szempont az irányítószám vagy település, vagy még az utcanév is.)
6. Figyeld meg, hogy figyelembe vette e, hogy van rovatfej – jelöld be, ha nem.
7. OK gomb megnyomására elrendezi a sorokat.
Kapsz egy táblázatot. amiben az azonos nevűek egymás alatt vannak.
8.Az A oszlop elé illessz be egy oszlopot Jelölő felirattal.(vagy ha úgy kényelmesebb, az utolsó adatoszlop utánit használd)
9. A jelölő oszlop megfelelő cellájába tegyél egy 1-est vagy egy betűt minden sorban, amire szükség van.
10. Jelöld ki az első sort (a rovatcímeket).
11. Adatok menüpont Szűrés Autoszűrő
A rovatcímek mindegyikében megjelenik egy kis nyilacskát tartalmazó négyzet.
12. A jelölő rovatcím cellájában kattints a kis nyílra. Válaszd ki az 1-est (vagy amit beírtál), kattints rá.
Az egész táblázatból most már csak a jelöltek látszanak.
13. Válaszd a Látszó cellák kijelölése ikont vagy parancsot. (Ha nincs az eszköztáradban az ikon, az Eszközök menüpont Testreszabás, Parancsok (fül) Szerkesztés (baloldalt) Látható cellák (jobboldalt, az ikonok közt, ha legörgeted)kattints rá a bal egésgommbal, és nyomva tartva húzd az eszköztárra, ahol legyen)
14. Másold le a táblázatot.
Illeszd be egy új munkalapra, vagy munkafüzetbe.
Jó munkát!
Szeretettel:
Zsuzsanna
ű
Zsiann 2011 április, 6 - 15:20:53
Szia Andrea!
Én a pivot (kimutatás) alkalmazását javasolnám. Egy több ezres listánál egyesével kijelölgetni nem épp a legjobb megoldás!
Egy fkeres-sel aztán visszateheted az eredeti lista mellé ezeket a darabszámokat, majd sorbarendezés és szűrés (nem egyenlő 1) és kész. Így majd pl 2 sorban lesz egymás alatt Kis Béla, ahol az fkeres a Kis Béla név mellé 2-est hozott.
A nevek oszlopból csinálj kimutatást, úgy hogy az “oszlopokhoz” tedd be a neveket és az értékhez úgyszintén. (ha a vezeték és keresztnevek külön oszlopban van, akkor javaslom, hogy Összefűz függvénnyel csinálj egy teljes név oszlopot!) Mezőbeállításnál válaszd a “Darab”-ot. Ahol 1-es hoz fel a név mellé, abból csak egy rekordod van, de amelyik névnél nem 1-est hoz fel, abból értelemszerűen több van
Enikő 2011 április, 7 - 09:02:11
Kedves Andrea,
Itt találsz a problémádhoz egy egyszerű megoldást:
http://www.hatekonysag.hu/blog/hasznos-excel-fuggveny-darabteli-countif.htm
Till Zoltán 2011 április, 7 - 11:15:08
Egy kérdésem lenne, amire nem igazán tudok rájönni.
Az addig oké, hogy FKERES segítségével kikeresem, hogy pl. 2010.12.31-től vagy 2011.03.12-től mennyibe került egy adott termék, de arra nem találok megoldást, hogy mit tegyek akkor ha a táblázatomban több termék is található, és mindig másik termék árára vagyok kíváncsi egy adott dátum alapján.
Tudnátok nekem ebben segíteni? Köszönöm előre is!
Kati 2011 május, 4 - 10:00:20
Sziasztok!
Az lenne a kérdésem hogy egy készlet táblázatból kell kinyernem adott cikk adott raktárhoz tartozó készletét. Hogy tudom ezt megtenni. 1 db Fkeres fv. kevés mert két változó van cikk és raktár. Az adatokat egy másik táblába kell átvezetnem és további számításokat végezni. Pivotból próbálkoztam és a kimutatásadatot.vesz fv-el, de ez nem jó mert ha valamelyik raktárban nincs az adott cikkből akkor #HIV! ír és további számítást már nem tudok vele végezni. Így néz ki a tábla
cikkszám készlet raktár
169 112 SZOLNOKI REGIONÁLIS RAKTÁR
169 28 GYÖNGYÖSI REGIONÁLIS RAKTÁR
169 84 DEBRECENI REGIONÁLIS RAKTÁR
169 28 SZEGEDI REGIONÁLIS RAKTÁR
169 28 MISKOLCI REGIONÁLIS RAKTÁR
169 56 VESZPRÉMI REGIONÁLIS RAKTÁR
169 28 SALGÓTARJÁNI REGIONÁLIS RAKTÁR
169 56 VESZPRÉMI REGIONÁLIS RAKTÁR
349 24 SZEGEDI REGIONÁLIS RAKTÁR
653 27 GYÖNGYÖSI REGIONÁLIS RAKTÁR
653 13 SZEGEDI REGIONÁLIS RAKTÁR
654 27 GYÖNGYÖSI REGIONÁLIS RAKTÁR
654 13 SZEGEDI REGIONÁLIS RAKTÁR
2154 10 MISKOLCI REGIONÁLIS RAKTÁR
2154 12 SZEGEDI REGIONÁLIS RAKTÁR
2154 12 GYÖNGYÖSI REGIONÁLIS RAKTÁR
2154 12 SZOLNOKI REGIONÁLIS RAKTÁR
2154 6 NYÍREGYHÁZI REGIONÁLIS RAKTÁR
2154 6 BÉKÉSCSABAI REGIONÁLIS RAKTÁR
2154 12 SALGÓTARJÁNI REGIONÁLIS RAKTÁR
2169 3 SZEGEDI REGIONÁLIS RAKTÁR
2169 3 GYÖNGYÖSI REGIONÁLIS RAKTÁR
2169 1 BÉKÉSCSABAI REGIONÁLIS RAKTÁR
2169 3 SZOLNOKI REGIONÁLIS RAKTÁR
2169 3 SALGÓTARJÁNI REGIONÁLIS RAKTÁR
előre is köszi a segítséget!
Zoli 2011 augusztus, 13 - 13:47:49
Lehet, hogy buta kérdés, de az adott tömböm egy sorszám fv-nyel rendezett helyezési rangsor. Mivel van benne holtverseny, az FKERES az első értéket, amit talál hozza duplán. Hogy lehet ezt kiküszöbölni?
Köszönöm!!!
Nóri 2011 november, 6 - 21:56:45
Jó ez az FKERES valóban, két komoly baja van:
- vagy pontos értéket keres, vagy sorrendbe kell lennie az adatoknak
- csak az első oszlopban tud keresni
Bizonyos problémákhoz jobb megoldást jelenthetnek mondjuk az adatbázis függvények (pl. AB.MEZŐ), amik viszont szintén nem problémamentesek.
cousin333 2011 december, 15 - 12:46:05
Sziasztok!
Szintén FKERES függvénnyel kapcsolatban ütköztem akadályba.
Az office.microsoft.com-on azt olvastam, hogy “Az FKERES függvényt ezzel szemben akkor érdemes használni, amikor az összehasonlítandó értékek a tömb jobb vagy bal oldalán levő oszlopban helyezkednek el és egy adott oszlopbeli értéket szeretnénk megtudni.”
Nekem csak akkor működik ez a függvény, ha a bal oldali oszlopban vannak az összehasonlítandó értékek.
Pedig most a jobb oldali oszlopban lévő értékeket szeretném összehasonlítani, és egy másik tőle balra lévő oszlopból nyert adattal továbbdolgozni, de nem sikerül megfelelő megoldást találnom.
Segítenétek.
paga 2012 január, 16 - 16:55:16
Hát ezt elírtam.
Természetesen nem úgy gondoltam a végét, hogy “Segítenétek.” kijelentve, hanem kérdésnek szántam, így:
“Segítenétek?”
És természetesen köszönöm.
(Kicsit kapkodtam. A gimis fiamnak szeretnék segíteni.)
paga 2012 január, 16 - 19:42:39