Ezek a példák megtanítják, hogyan lehet több szempontot Vlookup-ra felvenni, adott példányt vagy az összes mérkőzést visszaadni, dinamikus Vlookup-ot végrehajtani több lap és még sok más.
A sorozat második része segít kiaknázni az Excel VLOOKUP erejét. A példák azt sugallják, hogy tudja, hogyan működik ez a funkció. Ha nem, akkor ésszerű kezdeni a VLOOKUP alapvető használatával az Excelben.
Mielőtt továbblépne, hadd emlékeztessem röviden a szintaxist:
Most, hogy mindenki ugyanazon az oldalon van, nézzük meg közelebbről a fejlett VLOOKUP képleteket:
- Hogyan lehet több feltételt Vlookup-ban felkeresni az Excelben
- Vlookup és return n-edik egyezés
- Vlookup több érték
- Vlookup sor- és oszlopértékek alapján
- Beágyazott Vlookup formula
- VLOOKUP és INDIRECT adatok több lapból történő lekéréséhez
Hogyan lehet több szempontot Vlookupba állítani
Az Excel VLOOKUP funkció valóban hasznos, ha adatbázisban keresnek egy bizonyos értékhez. Ugyanakkor hiányzik belőle egy fontos funkció – szintaxisa csak egy keresési értéket tesz lehetővé. De mi van akkor, ha több feltétellel akar keresni? Van néhány különböző megoldás, amelyek közül választhat.
1. képlet. VLOOKUP tw alapján o értékek
Tegyük fel, hogy rendelkezik rendelések listájával, és 2 kritérium, vevő neve és termék alapján szeretné megtalálni a mennyiséget. Bonyolult tényező, hogy minden ügyfél több terméket rendelt, az alábbi táblázat szerint:
A szokásos VLOOKUP képlet nem működik ebben helyzet, mert az első megtalált egyezést adja vissza az Ön által megadott egyetlen keresési érték alapján.
Ennek leküzdéséhez hozzáadhat egy segítő oszlopot, és összefűzheti ott két keresési oszlop (Ügyfél és Termék) értékeit. Fontos, hogy a segítő oszlop legyen a bal oldali oszlop a táblázat tömbben, mert ott az Excel VLOOKUP mindig a keresési értéket keresi.
Tehát adjon hozzá egy oszlopot a táblázat bal oldalán, és másolja át az alábbi képletet az oszlopban. Ez feltölti a segítő oszlopot a B és C oszlop értékeivel (a szóköz a jobb olvashatóság érdekében ketté van összefűzve):
=B2&" "&C2
Ezután használjon szabványos VLOOKUP képletet, és helyezze mindkét kritériumot a lookup_value argumentumba, szóközzel elválasztva:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Vagy adja meg a feltételeket külön cellákba (esetünkben G1 és G2), és összefűzze ezeket a cellákat:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Amint vissza akarunk adni egy értéket a D oszlopból, amely a táblázat tömbben a negyedik, akkor a 4-et használjuk a col_index_num értékre. A range_lookup argumentum FALSE-ra van állítva, hogy Vlookup pontos egyezést adjon meg. Az alábbi képernyőkép az eredményt mutatja:
Abban az esetben, ha a keresési táblázat egy másik munkalapon van, akkor a lap nevét vegye fel a VLOOKUP képletbe. Például:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternatív megoldásként hozzon létre egy elnevezett tartományt a keresőtáblához (mondjuk, Rendelések) a képlet megkönnyítése érdekében- olvasható:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
További információkért lásd: Hogyan lehet Vlookupot készíteni egy másik lapról az Excelben.
2. képlet. Excel VLOOKUP több feltétellel
Elméletileg a Vlookup fenti megközelítését két kritériumnál is többet használhatja. H azonban van néhány kifogás. Először is, a keresési érték 255 karakterre korlátozódik, másrészt a munkalap kialakítása nem feltétlenül engedélyezi a segédoszlop hozzáadását.
Szerencsére a Microsoft Excel gyakran többféle módon is biztosítja ugyanezt. A Vlookup több kritériumának megadásához használhat INDEX MATCH kombinációt vagy az XLOOKUP funkciót, amelyet nemrégiben mutattak be az Office 365-ben.
Például 3 különböző érték alapján (dátum, ügyfélnév és termék) kereshet, használja a következő képletek egyikét:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Hol:
A képletek részletes magyarázatát lásd:
- XLOOKUP több kritériummal
- INDEX MATCH képlet több kritériummal
Hogyan használjuk a VLOOKUP-ot a 2., a 3. vagy az n-edik találat megszerzéséhez
Mint már tudjátok, az Excel VLOOKUP csak egy megfelelő értéket tud lekérni, pontosabban az első megtalált találatot adja vissza. De mi van akkor, ha a keresési tömbben több egyezés található, és meg akarja szerezni a 2. vagy a 3. példányt? A feladat meglehetősen bonyolultnak hangzik, de a megoldás létezik!
1. képlet. Vlookup N. Példány
Tegyük fel, hogy az egyik oszlopban vannak ügyfélnevek, a másikban megvásárolt termékek, és Ön az adott vásárló által megvásárolt 2. vagy 3. terméket keresi.
A legegyszerűbb módszer egy segítő oszlop hozzáadása a táblázat bal oldalán, ahogy az első példában tettük. De ezúttal olyan ügyfélnevekkel és előfordulási számokkal fogjuk feltölteni, mint a “John Doe1”, “John Doe2” stb.
Az előfordulás megszerzéséhez használja a COUNTIF függvényt vegyes tartomány hivatkozással (a az első referencia abszolút, a második pedig relatív, mint a $ B $ 2: B2). Mivel a relatív referencia annak a cellának a helye alapján változik, ahová a képletet átmásoltuk, a 3. sorban $ B $ 2: B3, a 4. sorban – $ B $ 2: B4 és így tovább.
Az ügyfélnévvel (B2) összefűzve a képlet a következő formában jelenik meg:
=B2&COUNTIF($B$2:B2, B2)
A fenti képlet A2-re megy, és majd annyi cellába másolja le, ahányszor szükséges.
Ezt követően írja be a cél nevét és az előfordulás számát külön cellákba (F1 és F2), és az alábbi képlet segítségével Vlookup egy adott eseményt:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
2. képlet. Vlookup 2. előfordulás
Ha a keresési érték 2. példányát keresi, akkor a segítő oszlop nélkül is megteheti. Ehelyett dinamikusan hozza létre a tábla tömböt az INDIRECT függvény és a MATCH használatával:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Hol:
- E1 a keresési érték
- A2: A11 a keresési tartomány
- B11 a keresőtábla utolsó (jobbra lent) cellája
Felhívjuk figyelmét, hogy a fenti képlet egy olyan konkrét esetre íródott, amikor a keresőtábla adatcellái a 2. sorban kezdődnek. Ha a táblája valahol a lap közepén használja ezt az univerzális képletet, ahol A1 az oszlopfejlécet tartalmazó keresőtábla bal felső cellája:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Hogyan működik ez a képlet
Itt található a képlet legfontosabb része, amely dinamikus vlookup tartományt hoz létre:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
A pontos egyezésre konfigurált MATCH függvény (0 az utolsó argumentumban) összehasonlítja a cél nevét (E1) a névlistával (A2: A11), és visszaadja az első megtalált egyezés helyzetét, amely 3 én nem ur eset. Ezt a számot fogják használni a vlookup tartomány kezdősorának koordinátájaként, ezért hozzáadunk 2-t (+1 az első példány kizárásához és +1 az 1. sor kizárásához az oszlopfejlécekkel). Alternatív megoldásként használhatja az 1 + ROW (A1) billentyűt a szükséges kiigazítás automatikus kiszámításához a fejlécsor (esetünkben A1) helyzete alapján.
Ennek eredményeként a következő szöveges karakterláncot kapjuk: amelyet az INDIRECT konvertál tartomány referenciaként:
INDIRECT("A"&5&":B11") -> A5:B11
Ez a tartomány a VLOOKUP table_array argumentumához megy, amely arra kényszeríti, hogy kezdjen keresni a sorban 5, kihagyva a keresési érték első példányát:
VLOOKUP(E1, A5:B11, 2, FALSE)
Hogyan lehet Vlookup-ot készíteni és több értéket visszaadni az Excelben
Az Excel VLOOKUP funkció csak egy mérkőzést eredményez. Van-e mód a Vlookup több példányára? Igen, van, bár nem könnyű. Ehhez több függvény együttes használatára van szükség, például az INDEX, a SMALL és a ROW egy tömbképlet.
Az alábbiakban megtalálhatja például az F2 keresési érték összes előfordulását a B2: B16 keresési tartományban és a return több egyezés a C oszlopból:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
A képlet kétféle módon adható meg a munkalapon:
- Írja be a képletet az első cellába, nyomja meg a Ctrl + Shift + Enter billentyűkombinációt, majd húzza le néhány további cellára.
- Jelöljön ki több szomszédos cellát egyetlen oszlopban (F1: F11 in az alábbi képernyőképet), írja be a képletet, és nyomja meg a Ctrl + Shift + Enter billentyűkombinációt annak kitöltéséhez.
Akárhogy is, a képletet beíró cellák számának egyenlőnek vagy nagyobbnak kell lennie, mint a lehetséges egyezések maximális száma.
A képletlogika részletes magyarázatához és további példákért lásd: Hogyan lehet több értéket feltölteni az Excel programban .
Hogyan lehet Vlookup-ot használni sorokban és oszlopokban (kétirányú keresés)
A kétirányú keresés (más néven mátrixkeresés vagy 2-dimenziós keresés) divatos szó arra, hogy egy adott sor és oszlop metszéspontjában keressen egy értéket.Kétféle módon lehet kétdimenziós keresést végezni az Excelben, de mivel ennek az oktatóanyagnak a középpontjában a VLOOKUP függvény áll, természetesen használni fogjuk.
Ebben a példában a következőt vesszük: lenti táblázat havi eladásokkal, és dolgozzon ki egy VLOOKUP képletet az adott cikk eladási adatainak lekérdezéséhez az adott hónapban.
Az elem neveivel A2: A9, a hónap nevével a B1: F1, a cél elem az I1-ben és az I2 célhónapjában a képlet a következő:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Hogyan működik ez a képlet
A képlet magja a standard VLOOKUP függvény, amely pontos egyezést keres az I1 keresési értékével. De mivel nem tudjuk amelyben pontosan egy oszlopban szerepelnek az adott hónap eladásai, nem tudjuk közvetlenül megadni az oszlop számát a col_index_num argumentumnak. Az oszlop megtalálásához a következő MATCH függvényt használjuk:
MATCH(I2, A1:F1, 0)
Angolra lefordítva a képlet így szól: nézz utána az I2 értéket A1: F1-ben, és adja vissza relatív helyzetét a tömbben. A 3. argumentum 0-ának megadásával utasítja a MATCH-t, hogy keresse meg a keresési értékkel pontosan megegyező értéket (ez olyan, mintha a FALSE-t használnánk a VLOOKUP range_lookup argumentumához).
Mivel Mar a 4. oszlopban van a lookup tömbben a MATCH függvény visszaadja a 4 értéket, amely közvetlenül a VLOOKUP col_index_num argumentumához megy:
VLOOKUP(I1, A2:F9, 4, FALSE)
Kérjük, figyeljen oda bár a hónapnevek a B oszlopban kezdődnek, az A1: I1-et használjuk a keresési tömbhöz. Ez azért történik, hogy a MATCH által visszaadott szám megfeleljen az oszlop pozíciójának a VLOOKUP tábla_array-ben.
Ha többet szeretne megtudni a mátrixkeresés végrehajtásáról az Excelben, olvassa el az INDEX MATCH MATCH és más képletek a kétdimenziós megkereséshez című részt.
Hogyan lehet több Vlookup programot végezni az Excelben (beágyazott Vlookup)
Néha előfordulhat, hogy a főtáblának és a keresőtáblának nincs egyetlen közös oszlopa, ami megakadályozza, hogy két tábla között végezzen Vlookup-ot. Van azonban egy másik táblázat, amely nem tartalmazza a keresett információt, de van egy közös oszlopa a fő táblával és egy másik közös oszlop a keresési táblázattal.
Az alábbi képen a helyzet látható:
A cél az árak másolása a fő táblába a cikkazonosítók alapján. A probléma az, hogy az árakat tartalmazó táblázatban nincsenek elemazonosítók, vagyis két Vlookup-ot kell elvégeznünk egy képletben.
A kényelem érdekében először hozzunk létre néhány megnevezett tartományt :
- Az 1. keresőtábla neve Termékek (D3: E3).
- A 2. keresési táblázat neve Árak (G3: H3).
A táblák lehetnek ugyanazokban vagy különböző munkalapokban.
És most elvégezzük az úgynevezett kettős Vlookup-ot, más néven beágyazott Vlookup-ot.
Először készítsen VLOOKUP-ot. képlet a termék nevének megkereséséhez az 1. keresési táblázatban (Termékek néven) az elemazonosító (A3) alapján:
=VLOOKUP(A3, Products, 2, FALSE)
Ezután tegye a fenti képletet egy másik VLOOKUP függvény lookup_value argumentumába, hogy a beágyazott VLOOKUP által visszaküldött terméknév alapján az árakat lehívja a 2. keresési táblából (név szerint Árak):
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Az alábbi képernyőképen beágyazott Vlookup képletünk látható:
A Vlookup többszörösének megtekintése lapok dinamikusan
Előfordulhat, hogy ugyanazon formátumú adatok több munkalapra vannak felosztva. A cél az, hogy adatokat gyűjtsön egy adott lapról, az adott cellában lévő kulcsértéktől függően.
Ezt egy példából könnyebben meg lehet érteni. Mondjuk, van néhány regionális értékesítési jelentésed ugyanabban a formátumban, és arra törekszel, hogy megkapd egy adott termék értékesítési adatait bizonyos régiókban:
Az előző példához hasonlóan néhány név megadásával kezdjük:
- A2 tartomány: A CA lap B5 tartományának neve CA_Sales.
- A2 tartomány : Az FL lapon a B5 neve FL_Sales.
- A2: A KS lap B5 tartományának neve KS_Sales.
Mint láthatja, az összes megnevezett tartománynak közös alkatrész (értékesítés) és egyedi alkatrészek (CA, FL, KS). Kérjük, ne felejtse el megnevezni tartományait hasonló módon, mint ami elengedhetetlen a felépítendő képlethez.
1. képlet. INDIRECT VLOOKUP az adatok dinamikus lekérdezéséhez különböző lapokról
Ha az a feladata, hogy adatokat gyűjtsön több lapról, akkor a VLOOKUP INDIRECT képlet a legjobb megoldás – kompakt és könnyen érthető.
Ebben a példában az összefoglaló táblázatot így szervezzük:
- Adja meg az érdeklődésre számot tartó termékeket az A2 és az A3 mezőben. Ezek a keresési értékeink.
- Írja be a megnevezett tartományok egyedi részeit B1, C1 és D1 értékekbe.
És most összefűzzük az egyedi részt (B1) tartalmazó cellát a közös részvel (“_Sales”), és az így kapott karakterláncot az INDIRECT-hez adjuk:
INDIRECT(B$1&"_Sales")
Az INDIRECT függvény átalakítja a karaktersorozatot az Excel számára érthető névvé, és beleteszi a VLOOKUP table_array argumentumába:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
A fenti képlet a B2-re megy, majd lefelé és jobbra másolja.
Kérjük, ügyeljen arra, hogy a keresési értékben ($ A2) bezártuk az oszlop koordinátáját abszolút cellahivatkozással, hogy az oszlop rögzített maradjon, amikor a képletet jobbra másoljuk. A B $ 1 referenciában lezártuk a sort, mert azt akarjuk, hogy az oszlop koordináta a megfelelő névrész megváltoztatásához és az INDIRECT szolgáltatáshoz, attól függően, hogy mely oszlopba másolják a képletet:
Ha a főtáblája rendezett másképp, a keresési értékek egy sorban és a tartománynevek egyedi részei egy colban mn, akkor zárolnia kell a sor koordinátáját a keresési értékben (B $ 1) és az oszlop koordinátáját a névrészekben ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
2. képlet. VLOOKUP és beágyazott IF-k több lap megkeresésére
Abban a helyzetben, amikor éppen Két vagy három keresési lap, egy meglehetősen egyszerű, beágyazott IF-függvényekkel ellátott VLOOKUP képlet segítségével kiválaszthatja a megfelelő lapot az adott cellában lévő kulcsérték alapján:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Ahol $ A2 a keresési érték (elem neve), és B $ 1 a kulcsérték (állapot):
Ebben az esetben nem feltétlenül kell megadnia a neveket, és külső hivatkozásokkal hivatkozhat egy másik lapra vagy munkafüzetre.
További képlet-példákkal kapcsolatban lásd: Az Excel több lapjának áttekintése .
Így használható a VLOOKUP az Excelben. Köszönöm, hogy elolvastad, és remélem, hogy a következő héten találkozunk a blogunkon!
Gyakorlati munkafüzet letöltéshez
Haladó VLOOKUP képletpéldák (.xlsx fájl)
- Excel VLOOKUP oktatóanyag kezdőknek
- Hogyan lehet több mérkőzést Vlookupba állítani az Excelben
- Hogyan használjuk a VLOOKUP & SUM vagy SUMIF függvényeket Excel
- INDEX & MATCH függvények az Excelben – jobb alternatíva a VLOOKUP-hoz
- 4 módja a kis- és nagybetűk különbségtételének az Excelben
- Az Excel VLOOKUP nem működik – N / A, NAME és VALUE hibák