Excel VLOOKUP oktatóanyag kezdőknek
Charlotte Miller
⚡ Okos összefoglaló
Az Excel FKERES függvény oktatóanyaga bemutatja, hogyan keres a függőleges keresési függvény egy táblázat első oszlopában, és hogyan adja vissza a megfelelő értéket egy másik oszlopból. Ez az útmutató a szintaxist, a pontos és közelítő egyezéseket, a kereszttáblázatos kereséseket, a gyakori hibákat és a modern XKERES alternatívát ismerteti.
Mi az a VLOOKUP?
A FKERES (a V a Vertical rövidítése) egy beépített Excel függvény, amely kapcsolatot létesít a táblázat oszlopai között. Lehetővé teszi egy érték kikeresését az egyik oszlopban, és a megfelelő érték visszaadását ugyanazon sor egy másik oszlopából.
FKERES szintaxis és argumentumok
A FKERES függvény alkalmazása előtt érdemes megérteni a képlet szerkezetét. A függvény négy argumentumot fogad el, és minden Excel verzióban konzisztens mintát követ.
- keresési_érték — a keresett érték (cellahivatkozás vagy literál).
- tábla_tömb — a keresési oszlopot és a visszatérési oszlopot tartalmazó cellatartomány.
- oszlopszám — a table_array tömb oszlopszáma, amelyből az értéket vissza kell adni (az 1 a bal szélső).
- range_lookup — FALSE pontos egyezés esetén, TRUE (vagy elhagyva) hozzávetőleges egyezés esetén rendezett adatokon.
Fontos: A keresési értéknek a table_array bal szélső oszlopában kell lennie, és a FKERES függvény csak balról jobbra keres.
A VLOOKUP használata
Ha egy nagyméretű táblázatban kell konkrét információt keresnie, vagy ugyanazt az értéket kell ismételten lekérdeznie, a FKERES függvény jelentős időt takarít meg a manuális szűréshez képest.
Vegyük figyelembe a Céges fizetési táblázat a pénzügyi csapat tartja karban. Egy ismert információval – egy indexszel – kezdünk, és az FKERES függvénnyel kérjük le az ismeretlen értéket.
Például már ismeri az alkalmazott nevét:
És meg szeretné nézni az alkalmazotti fizetést:
Excel táblázat a fenti esethez:
Az ismeretlen alkalmazotti fizetés megkereséséhez írjuk be az alkalmazottat Code ami már elérhető.
A FKERES alkalmazásával az adott alkalmazotthoz tartozó fizetési érték Code automatikusan megjelenik.
A VLOOKUP funkció használata Excelben
Kövesse ezt a lépésenkénti útmutatót a FKERES függvény alkalmazásához az Excelben:
1. lépés) Navigáljon a célcellához
Kattintson arra a cellára, ahol a kiválasztott alkalmazott fizetését meg szeretné jeleníteni – ebben a példában a H3 cellára.
2. lépés) Írja be a FKERES függvényt: =FKERES()
Írd be a függvényt a cellába. Kezdd egyenlőségjellel (ami jelzi az Excelnek, hogy egy képlet következik), majd írd be a FKERES kulcsszót: =VLOOKUP().
A zárójelek tartalmazzák az argumentumokat (azokat az adatokat, amelyekre a függvénynek szüksége van).
A FKERES függvénynek négy argumentumot kell megadnia:
3. lépés) Első argumentum – a keresési érték
Az első argumentum a keresendő érték cellahivatkozása. Ebben az esetben az Alkalmazott Code a keresési érték, tehát az első argumentum a H2 – az a cella, amelynek tartalmával az Excelnek meg kell egyeznie.
4. lépés) Második argumentum — a tábla tömbje
Ez a keresendő értékek blokkjára utal, amelyet az Excelben a következő néven ismerünk: táblázat tömb vagy keresőtábla. Példánkban a keresőtábla a következőképpen fut: B2-től E25-ig.
JEGYZET: A keresési oszlopnak a táblázat tömbjének bal szélső oszlopának kell lennie.
5. lépés) Harmadik argumentum — oszlop_index_szám
Ez megmondja a FKERES függvénynek, hogy a táblázat melyik oszlopában található a visszatérési érték. Az alkalmazotti fizetés a negyedik oszlopban található, tehát az oszlopindex 4.
6. lépés) Negyedik érv – pontos vagy hozzávetőleges egyezés
Az utolsó argumentum a tartománykeresési jelző. Ez szabályozza, hogy a FKERES függvény pontos vagy hozzávetőleges egyezést ad-e vissza. Itt pontos egyezést szeretnénk (FALSE).
- HAMIS – pontos egyezés.
- TRUE — hozzávetőleges egyezés.
7. lépés) Nyomja meg az Enter billentyűt
Nyomja meg az Enter billentyűt a képlet befejezéséhez. Először hibát fog látni, mert nincs alkalmazott. Code még nem került be a H2-be.
Miután megadta az érvényes alkalmazottat Code A H2 cellában a megfelelő alkalmazotti fizetést adja vissza.
Röviden, a képlet azt jelzi az Excelnek, hogy az ismert értékek az adatok bal szélső oszlopában helyezkednek el (Alkalmazott Code). A FKERES függvény ezután átvizsgálja a táblázatot, és visszaadja a megfelelő sor negyedik oszlopának értékét – az alkalmazott fizetését.
Ez a példa a pontos egyezéseket tárgyalta (a FALSE kulcsszó). A következő szakasz a hozzávetőleges egyezéseket ismerteti.
VLOOKUP hozzávetőleges egyezésekhez (igazi kulcsszó utolsó paraméterként)
Vegyünk egy olyan forgatókönyvet, amelyben egy táblázat kiszámítja a kedvezményeket azoknak a vásárlóknak, akik nem pontosan több tíz vagy több száz tételt vásárolnak.
Amint az alább látható, egy vállalat 1 és 10 000 közötti mennyiségekre alkalmaz kedvezményeket:
Egy vásárló ritkán vásárol pontosan 100 vagy 1,000 egységet. A közelítő egyezési mód lehetővé teszi, hogy a FKERES függvény a legközelebbi alacsonyabb értéket találja meg a pontos szám megadása helyett. Lépések:
Step 1) Kattintson arra a cellára, ahová a FKERES függvény kerülni fog – cellahivatkozás I2.
Step 2) Írja be az =FKERES() képletet a cellába, és adja hozzá a zárójelben lévő argumentumokat.
3. lépés) 1. érv: Adja meg annak a cellahivatkozásnak a címét, amelynek az értékét össze kell vetni a keresőtáblázattal.
4. lépés) 2. érv: Válassza ki a keresőtáblát – itt a Mennyiség és a Kedvezmény oszlopokat.
5. lépés) 3. érv: Adja meg a keresőtábla oszlopindexét, amelyből a megfelelő értéket vissza szeretné adni.
6. lépés) 4. érv: Az utolsó argumentumot erre állítsa be TRUE hozzávetőleges egyezések esetén.
Step 7) Nyomja meg az Enter billentyűt. A képlet most már érvényes lesz a cellára. Bármely mennyiség beírásakor az Excel a hozzávetőleges egyezés alapján adja vissza a kedvezménysávot.
JEGYZET: Ha a negyedik argumentumot üresen hagyja, az Excel alapértelmezés szerint IGAZ (közelítő egyezés) értéket vesz fel. Közelítő egyezések esetén a keresési oszlopot növekvő sorrendbe kell rendezni.
Vlookup funkció 2 különböző lap között alkalmazva ugyanabban a munkafüzetben
Most vegyünk egy két munkalapból álló munkafüzetet. Az 1. munkalapon az Alkalmazott szerepel. Code, Név és beosztás; A 2. lapon fel van sorolva az alkalmazott Code és az alkalmazotti fizetés.
1. LAP:
2. LAP:
A cél az 1. munkalapon található összes adat összesítése, az alábbiak szerint:
A FKERES funkcióval összesíthetők az adatok, így az alkalmazottak CodeA , a Név és a Fizetés együtt jelenik meg egy munkalapon.
A 2. lappal kezdünk, mert az két argumentumot tartalmaz – itt található az Alkalmazotti fizetés oszlop, és itt van a oszlopindex 2.
Meg akarjuk találni az egyes alkalmazottaknak megfelelő fizetést. Code.
Az adatok az A2-től a B25-ig terjednek – ez a táblázattömbünk.
Step 1) Váltson az 1. munkalapra, és adja meg a megjelenített címsorokat.
Step 2) Kattintson az Alkalmazotti fizetés melletti cellára – F3 cella –, ahová a FKERES képlet kerülni fog.
Írja be a FKERES függvényt: =FKERES().
3. lépés) 1. érv: Nyomja meg az F2 billentyűt – az Alkalmazott mezőt tartalmazó cellát Code hogy egyezzen a keresőtáblázatban.
4. lépés) 2. érv: A keresőtábla a másik munkalapon található, ezért a munkalap nevével kell rá hivatkozni: 2. munkalap!A2:B25.
5. lépés) 3. érv: Adja meg a keresőtáblázat oszlopindexét, amely a visszatérési értéket tartalmazza.
6. lépés) 4. érv: Pontos egyezéshez használjuk a FALSE értéket, mert pontosan azt a fizetést szeretnénk, amelyik megegyezik az egyes alkalmazottakkal. Code.
Step 7) Nyomja meg az Enter billentyűt. Amikor beír egy alkalmazottat Code, a cella a 2. munkalapról kinyert megfelelő fizetést adja vissza.
Gyakori FKERES hibák és javítások
Még a tapasztalt felhasználók is belefutnak a FKERES függvény hibáiba. A leggyakoribbak és gyors javításaik:
- # N / A — A FKERES függvény nem találja a keresett értéket. Ellenőrizze, hogy nincsenek-e felesleges szóközök, nem egyező adattípusok (szövegként tárolt számok), vagy hogy az érték valóban létezik-e a tábla_tömb első oszlopában.
- #REF! — A col_index_num nagyobb, mint a table_array oszlopainak száma. Csökkentse az oszlopindexet, vagy bővítse a tartományt.
- #ÉRTÉK! — az oszlop_index_száma kisebb, mint 1, vagy egy argumentum érvénytelen. Ellenőrizze a képlet szintaxisát.
- Hibás eredményt adott vissza — a negyedik argumentum IGAZ vagy nincs megadva, de a keresési oszlop rendezetlen. Váltson HAMIS értékre, vagy rendezze az oszlopot növekvő sorrendben.
- Zárolt referenciák — képlet másolásakor abszolút hivatkozásokat kell használni (például $B$2:$E$25), hogy a table_array ne sodródjon el.
FKERES vs. XKERES: Melyiket érdemes használni?
Microsoft bevezette az XLOOKUP-ot Microsoft A 365 és az Excel 2021 a FKERES függvény modern helyettesítőjeként működik. Számos FKERES korlátozást eltávolít, és mostantól az ajánlott választás a támogatott verziókban.
| Jellemző | VLOOKUP | XLOOKUP |
|---|---|---|
| Keresési irány | Csak balról jobbra | Bármely irány (balra, jobbra, fel, le) |
| Alapértelmezett egyezési típus | Hozzávetőleges (IGAZ) | Pontos |
| Ha nem található kezelés | Visszaadja a #N/A értéket | Beépített if_not_found argumentum |
| Oszlopindex | Fixen kódolt szám | Return oszloptartományra való hivatkozás |
| Elérhetőség: | Minden Excel-verzió | Microsoft 365, Excel 2021, Webes Excel |
Mikor érdemes a FKERES függvényt választani: a munkafüzetnek az Excel 2019-es vagy korábbi verziójában kell futnia, vagy régi képleteket kell fenntartania. Mikor érdemes az XLOOKUP-ot választani? új munkafüzeteket készít a modern Excelben, és balra keresést, letisztultabb hibakezelést és alapértelmezés szerint pontos egyezést szeretne. Tudjon meg többet a keresési függvényekről a következőben: Excel-oktatóanyagok sorozat.
Összegzés
A fenti három forgatókönyv elmagyarázza, hogyan működik a FKERES függvény pontos egyezések, közelítő egyezések és kereszthivatkozások esetén. Gyakoroljon saját adathalmazain a gördülékenyebb használat érdekében. A FKERES függvény továbbra is fontos funkció a következőkben: MS-Excel az adatok hatékony kezeléséhez, és az XLOOKUP kiterjeszti ezt az eszközkészletet a modern Excelben.
