Excel VKERES függvény magyarázat példákkal

VKERES függvény magyarázat példákkal

Az Excelben a keresés mindig is fontos feladat volt. Az egyik gyakori keresőfüggvény a VKERES függvény (angolul HLOOKUP függvény) megkeres egy értéket a tábla első sorában, majd a megtalált elem oszlopából a megadott sorban elhelyezkedő értéket adja eredményül. Lehet, most így még nem világos, de a példán keresztül biztos megérted.

VKERES függvény és FKERES függvény

Az Excel VKERES függvényhez hasonlóan működik az FKERES is, csak az első oszlopban kereshetünk vele. A függvény megkeres egy értéket a tábla első oszlopában, majd a megtalált elem sorából a megadott oszlopban elhelyezkedő értéket adja eredményül. Erről bővebben olvashatsz a Mire jó az FKERES függvény? bejegyzésünkben.

Most nézzük a VKERES függvényt részletesen.

Excel VKERES függvény felépítése

A tábla vagy értéktömb felső sorában megkeres egy értéket, és a megtalált elem oszlopából a megadott sorban elhelyezkedő értéket adja eredményül.

A függvény: VKERES(keresési_érték; tábla; sor_szám; [tartományban_keres] Az újabb verziókban már más elnevezéssel láthatod, de ugyanezeket jelöli.

A függvény: VKERES(keresési_érték; táblatömb; sorindex; [tartományi_keresés]

Argumentumai:

Keresési_érték: Az az érték, amelyet a függvény a tábla első sorában keres. Lehet érték, hivatkozás vagy szöveg. Pl: 354 vagy C4 vagy „alma”
Tábla: A szöveget, számokat vagy logikai értékeket tartalmazó tábla, amelyben a keresést végre kell hajtani. Lehet tartományra való hivatkozás vagy tartománynév is.
Sor_szám: A táblázat azon sorának a táblán belüli száma, amelyből az eredményt meg kívánjuk kapni. A tábla első értéksora az 1-es számú sor. Számmal kell megadni mindig.
Tartományban_keres: Logikai érték: HAMIS esetén pontos egyezés szükséges (ez a 0-nak felel meg, beírhatjuk azt is). Ha IGAZ vagy elhagyjuk az első oszlopban lévő legjobb közelítést adja meg, növekvő rendezés esetén (Ez az 1-nek felel meg).
Megadhatjuk, hogy a keresési adattal pontos egyezési találatot akarunk, vagy sem. Ha pontos egyezést szeretnénk, a 0-t írjuk be. Amennyiben megközelítőleg is megfelel a találat (pl.: intervallum esetén) akkor 1-et használnunk, vagy üresen hagyjuk az argumentumot. Viszont ilyenkor tábla első sorában szereplő értékeknek növekvő sorrendben kell állniuk.

Négy argumentumot kell megadni, illetve csak hármat kötelező, de javaslom, hogy mindig add meg mind a négyet, mert sok hibától óvhatod így meg magad.

VKERES függvény beírása

A függvényt behívhatod úgy, hogy elkezded a cellába beírni egy = után, és kiválasztod a felkínált listából a bal egérgombbal duplán kattintva rajta. Ha nem ismered az argumentumait, akkor ezek után kattints a szerkesztőlécen az fx gombra, vagy használd a Ctrl + A billentyűkombinációt, ha már beírtad a függvényt és kinyitottad a zárójelet.

De rögtön a cellán belül is szerkesztheted. Ekkor az alábbi felépítéssel kell megírni.

=VKERES (keresési_érték;tábla;sor_szám;[tartományban_keres])

VKERES függvény példával- tartományban keres: HAMIS esetén

A minta feladatot letöltheted innen: Excel VKERES függvény feladatok

A feladatban egy csomagelosztó központ dolgozói leszünk. Az alábbi képen megrendelők nevei láthatóak, a hozzátartozó rendelési kóddal. A rendelési kód alapján kell meghatározni, hogy melyik cég végzi a szállítást. (Látható még a szállítási idő és a sebesség, de ezek a következő feladatban kellenek.)

A rendelés kód és a hozzátartozó futárszolgálat egy kis táblázatban van rögzítve a G3:M4 cellákban.

Nézzük, hogy ezek alapján, hogy írjuk meg a függvényt!

VKERES függvény megoldás- tartományban keres: HAMIS esetén

A C3 cellában állva hívd meg az Excel VKERES függvényt (angolul HLOOKUP függvény) és töltsd ki az argumentumokat.

Négy argumentuma van a függvénynek, nézzük ezeket végig.

Keresési érték: az érték, amit keresek a (a kis táblázatban). Vagyis az adott rendelés kód (G), ami a B3 cella tartalma.

Tábla: amiben keresek, ez a kis tábla, ami narancssárga, a G3:M4 cellák.

Sor_szám: az előzőben megadott táblázatból az a sor, amiből a választ akarom megkapni. A kis táblázat első sorában keresek, a második sorban vannak a futárszolgálatok, így abból akarom megkapni a választ. 2-t kell ide írni.

Tartományban_keres: hamis, vagy 0, mert pontos egyezés kell. Konkrétan az a Rendelés kód kell, ami a B2 cellában van (G).

Nagyon fontos, hogy itt figyelj rá, hogy ne hagyd el a HAMIS értéket. Sok esetben úgy is működik, és ez becsapós lehet. Viszont, ha egy helyen éppen nem, akkor már probléma van, jön a hibaüzenet(#HIÁNYZIK). Azt javaslom, mindig tölts ki, a Tartományban_keres értékét.

Az ablakban már látható is a függvény eredménye.

Rögtön a cellában megírva a függvényt a megoldás így fest. =VKERES(B3;$G$3:$M$4;2;0)

Ahhoz, hogy másolható legyen lefelé a függvény, a kis táblázatot le kell rögzíteni a dollárjel segítségével. Ha ezt elmulasztod, #HIÁNYZIK hibaüzenetet kapsz. Amikor a táblát adod meg, a kijelölés után kattints az F4 billentyűre, ez rögzíti a táblát a dollárjelekkel.

Ennek használatáról a Hogyan használjam a dollárjeleket a képletekben? bejegyzésünkben olvashatsz bővebben.

Végeredmény

Excel VKERES függvény példával- tartományban keres: IGAZ esetén

Az előző feladatot folytatva most a szállítási napok alapján kell meghatározni a futárok sebességét. A szállítási idő és a sebesség összefüggése a G6:K8 cellákban található, zöld kis táblázat. Az értékek intervallumban találhatóak. 1-3 nap az nagyon gyors szállítás, 4-5 nap gyors, 6-7 nap átlagos, 8 és e felett lassú.

Nézzük, hogy ezek alapján, hogy írjuk meg most a függvényt!

Most az E3 cellában dolgozunk.

A függvény argumentumait töltsük ki a feladatnak megfelelően.

Keresési érték: az érték, amit meg kell találni a kis zöld táblázatban. Konkrétan az D3 cella tartalma (2 2 nap a kiszállítás).

Tábla: amiben keresek, a zöld kis tábla, G6:K8 cellák

Sor_szám: az előzőben megadott táblázatból az a sor, amiből a választ akarom megkapni. A kis táblázat első sorában keresek, a második sorban szintén értékek vannak (de csak segítségnek), a harmadik sorban van a Sebesség, ebből akarom az eredményt megkapni. Így 3-as számot kell írni.

A VKERES függvény mindig csak az első sorban keres, más sorokat nem néz. Így a második sort akár el is hagyhattam volna a tábla megírásakor. A napok számának meghatározása intervallummal látható, de ez csak nekünk segítség.

Tehát a felsorolásban (első sor) lévő legjobb közelítést adja meg. Más szóval, ha nem talál pontos egyezést, akkor a legközelebbi (legnagyobb) olyan értéket veszi, amely kisebb, mint a keresési_érték.

Konkrétan ennél a feladatnál: megvizsgálja, hogy a keresett érték (2) nagyobb-e mint 1 (H6 cella tartalma), ha igen, megy tovább jobbra. 2 nagyobb, mint 4 (I6 cella tartalma), nem nagyobb, így visszalép és eredményül a H6 cella tartalmát adja majd.

Tartományban_keres: IGAZ vagy 1, mert nem kell pontos egyezés. A zöld kis táblázat intervallumokat tartalmaz: 1-3 nap, 4-5 nap, 6-7 nap, 8-bármeddig nap. Tehát itt nem biztos, hogy megtaláljuk a konkrét értéket, ezért kell 1-t írni. Így a függvény az intervallumban keresi meg a Szállítási időt (napot), ami az F2 cellában van és a hozzátartozó értéket adja vissza. Úgy, ahogy pár sorral feljebb olvashattad.

Ezt az IGAZ feliratot el is hagyhatjuk, akkor is megtalálja a közelítő egyezést a program. Ekkor azonban három argumentum fog csak a függvényben szerepelni.

Mivel ismét másolni kell a függvényt, már előre gondolkozva tedd ki a dollárjeleket.

A cellába írva a függvényt:

=VKERES(B3;$G$3:$M$4;2;0)

Végeredmény

További VKERES függvény feladat, megoldással

A táblázatban autók adatai találhatóak, a hiányzó adatokat kell kitölteni (fogyasztás és felhasználás) a kis kék táblázat segítségével. Minden helyre a megfelelő kategória alapján kell megjeleníteni az értéket.