Note: The other languages of the website are Google-translated. Back to English
Belépek  \/ 
x
or
x
Regisztráció  \/ 
x

or

VLOOKUP függvény néhány alapvető és haladó példával az Excelben

Az Excelben a VLOOKUP függvény az Excel legtöbb felhasználójának hatékony funkciója, amelyet arra használnak, hogy az adattartomány bal szélén található értéket keresse meg, és egy megfelelő értéket adjon vissza ugyanabban a sorban az Ön által megadott oszlopból, mint az alábbi képernyőkép . Ez az oktatóanyag a VLOOKUP függvény használatáról szól az Excel néhány alapvető és haladó példájával.

Tartalomjegyzék:

1. A VLOOKUP funkció bemutatása - Szintaxis és érvek

2. Alapvető VLOOKUP példák

3. Haladó VLOOKUP példák

4. A VLOOKUP egyeztetett értékek megtartják a cellák formázását

5. Töltse le a VLOOKUP mintafájlokat


A VLOOKUP függvény bemutatása - Szintaxis és érvek

A VLOOKUP függvény szintaxisa:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

érvek:

Keresési_érték: A keresni kívánt érték. Ennek a table_array tartomány első oszlopában kell lennie.

Táblázat_tömb: Az az adattartomány vagy táblázat, ahol a keresési érték oszlop és az eredményérték oszlop található.

Oszlop_index_szám: Az oszlop száma, ahonnan az egyező érték visszatér. A táblázattömb bal szélső oszlopának 1-jével kezdődik.

Tartomány_keresés: Logikai érték, amely meghatározza, hogy ez a VLOOKUP függvény pontos vagy hozzávetőleges egyezést ad-e vissza.

  • Hozzávetőleges mérkőzés - 1 / IGAZ: Ha nem található pontos egyezés, a képlet a legközelebbi egyezést keresi - a legnagyobb értéket, amely kisebb, mint a keresési érték. Ebben az esetben a keresési oszlopot növekvő sorrendben kell rendezni.
    = VLOOKUP (keresési_érték, tábla_rajz, oszlop_index, IGAZ)
    = VLOOKUP (keresési_érték, tábla_rajz, col_index, 1)
  • Pontos egyezés - 0 / HAMIS: Ez a keresési értékkel pontosan megegyező érték keresésére szolgál. Ha nem található pontos egyezés, a # N / A hibaértéket adja vissza.
    = VLOOKUP (keresési_érték, tábla_rajz, oszlop_index, HAMIS)
    = VLOOKUP (keresési_érték, tábla_rajz, col_index, 0)

Megjegyzések:

  • 1. A Vlookup függvény csak balról jobbra keresi az értéket.
  • 2. Ha a keresési érték alapján több egyező érték van, akkor a Vlookup függvény használatával csak az első egyezést adja vissza.
  • 3. Ha a keresési érték nem található, a # N / A hibaértéket adja vissza.

Alapvető VLOOKUP példák

1. Végezzen pontos egyezést a Vlookup és a hozzávetőleges egyezéssel

Végezzen pontos egyezést a Vlookup programmal az Excelben

Normál esetben, ha pontos egyezést keres a Vlookup funkcióval, akkor csak a FALSE-t kell használnia az utolsó argumentumban.

Például a megfelelő matematikai pontszámok megszerzéséhez a konkrét azonosító számok alapján tegye a következőket:

1. Alkalmazza az alábbi képletet egy üres cellába, ahol meg szeretné szerezni az eredményt:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

2. Ezután húzza lefelé a kitöltő fogantyút azokhoz a cellákhoz, amelyekbe kitölteni szeretné ezt a képletet, és a kívánt eredményt megkapja. Lásd a képernyőképet:

Megjegyzések:

  • 1. A fenti képletben F2 az az érték, amellyel vissza szeretné adni az egyező értéket, A2: D7 a tábla tömb, a szám 3 az az oszlopszám, amelyből az egyező értéket visszaadja, és a HAMIS a pontos egyezésre utal.
  • 2. Ha a kritériuma nem található az adattartományban, # N / A hibaérték jelenik meg.

Végezzen hozzávetőleges egyezést a Vlookup programmal az Excelben

A hozzávetőleges egyezés hasznos az adattartományok közötti értékek kereséséhez. Ha a pontos egyezés nem található, a hozzávetőleges Vlookup a legnagyobb értéket adja vissza, amely kisebb, mint a keresési érték.

Például, ha a következő tartományi adatokkal rendelkezik, a megadott megrendelések nincsenek a Rendelések oszlopban, hogyan lehet a legközelebbi Kedvezményt elérni a B oszlopban?

1. Írja be a következő képletet egy cellába, ahová be szeretné helyezni az eredményt:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

2. Ezután húzza le a kitöltő fogantyút a cellákra a képlet alkalmazásához, és megkapja a hozzávetőleges egyezéseket a megadott értékek alapján, lásd a képernyőképet:

Megjegyzések:

  • 1. A fenti képletben D2 az az érték, amellyel vissza szeretné adni a relatív információit, A2: B9 az adattartomány, a szám 2 azt az oszlopszámot jelzi, hogy az egyező érték visszaadódik, és a TRUE a hozzávetőleges mérkőzésre utal.
  • 2. A hozzávetőleges egyezés a legnagyobb értéket adja vissza, amely kisebb, mint az Ön konkrét keresési értéke.
  • 3. Ahhoz, hogy a Vlookup függvény segítségével hozzávetőleges egyezési értéket kapjon, az adattartomány bal szélső oszlopát növekvő sorrendben kell rendezni, különben rossz eredményt ad vissza.

2. Kis- és nagybetűk megkülönböztetése a Vlookup programból az Excel programban

Alapértelmezés szerint a Vlookup függvény kis- és nagybetűk nélküli keresést hajt végre, ami azt jelenti, hogy a kis- és nagybetűket azonosakként kezeli. Valamikor előfordulhat, hogy kis- és nagybetűk közötti keresést kell végeznie az Excelben, az Index, a Mérkőzés és a pontos vagy a Keresés és a pontos függvények tehetnek szívességet.

Például a következő adattartománnyal rendelkezem, amely azonosító oszlop tartalmaz nagy- vagy kisbetűkkel ellátott szöveges karakterláncot, most vissza akarom adni az adott azonosítószám megfelelő matematikai pontszámát.

1. képlet: EXACT, INDEX, MATCH függvények használata

1. Kérjük, írja be vagy másolja az alábbi tömbképletet egy üres cellába, ahol meg szeretné szerezni az eredményt:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2. Ezután nyomja meg a gombot Ctrl + Shift + Enter gombokat egyidejűleg az első eredmény eléréséhez, majd válassza ki a képlet cellát, húzza lefelé a kitöltő fogantyút azokhoz a cellákhoz, amelyekbe kitölteni kívánja ezt a képletet, és ezzel megkapja a megfelelő eredményeket. Lásd a képernyőképet:

Megjegyzések:

  • 1. A fenti képletben A2: A10 az az oszlop, amely tartalmazza azokat a konkrét értékeket, amelyekben meg szeretné keresni, F2 a keresési érték, C2: C10 az az oszlop, ahonnan az eredmény visszatér.
  • 2. Ha több találatot talált, ez a képlet mindig az első mérkőzést adja vissza.

2. képlet: A Keresés és a Pontos függvények használata

1. Kérjük, alkalmazza az alábbi képletet egy üres cellába, ahol meg szeretné szerezni az eredményt:

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

2. Ezután húzza le a kitöltő fogantyút azokra a cellákra, amelyekre másolni kívánja ezt a képletet, és megkapja az egyeztetett értékeket kis- és nagybetűkkel, ahogy az alábbi képernyőképen látható:

Megjegyzések:

  • 1. A fenti képletben A2: A10 az az oszlop, amely tartalmazza azokat a konkrét értékeket, amelyekben meg szeretné keresni, F2 a keresési érték, C2: C10 az az oszlop, ahonnan az eredmény visszatér.
  • 2. Ha több találatot talált, ez a képlet mindig az utolsó mérkőzést adja vissza.

3. A Vlookup értékei jobbról balra az Excelben

A Vlookup függvény mindig megkeres egy értéket egy adattartomány bal szélső oszlopában, és a megfelelő értéket egy oszlopból jobbra adja vissza. Ha egy fordított Vlookup-ot szeretne megtenni, ami azt jelenti, hogy egy adott értéket keres a jobb oldalon, és annak megfelelő értékét adja vissza a bal oszlopban, az alábbi képernyőkép szerint:

Kattintson a részletek részletes ismertetésére erről a feladatról ...


4. Vlookup az Excel második, n-edik vagy utolsó egyező értéke

Normál esetben, ha a Vlookup funkció használatakor több egyező érték található, csak az első egyeztetett rekordot adja vissza. Ebben a részben arról fogok beszélni, hogy miként lehet megkapni a második, az n-edik vagy az utolsó megfelelő értéket a Vlookup funkcióval.

Vlookup és adja vissza a második vagy n-edik egyező értéket

Tegyük fel, hogy van névlistája az A oszlopban, az általuk vásárolt tanfolyam a B oszlopban, és most azt keresi, hogy megtalálja az adott vásárló által megvásárolt 2. vagy n. Tanfolyamot. Lásd a képernyőképet:

1. Ahhoz, hogy a megadott kritériumok alapján megkapja a második vagy az n-edik megfelelő értéket, kérjük, alkalmazza a következő tömbképletet egy üres cellába:

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

2. Ezután nyomja meg a gombot Ctrl + Shift + Enter gombok együtt az első eredmény eléréséhez, majd válassza ki a képlet cellát, húzza lefelé a kitöltő fogantyút azokhoz a cellákhoz, amelyekbe kitölteni kívánja ezt a képletet, és a megadott nevek alapján az összes második egyező érték egyszerre megjelent, lásd a képernyőképet:

Jegyzet:

  • Ebben a képletben A2: A14 tartomány a keresés összes értékével, B2: B14 a megfelelő értékek tartománya, amelyekből vissza szeretne térni, E2 a keresési érték és az utolsó szám 2 jelzi a megszerezni kívánt második egyeztetett értéket, ha vissza akarja adni a harmadik egyező értéket, akkor csak 3-ra kell változtatnia, amire szüksége van.

Vlookup és adja vissza az utolsó egyező értéket

Ha vlookup-ot szeretne adni, és visszaadja az utolsó egyező értéket, az alábbi képernyőképen látható, akkor ezt Vlookup és visszaadja az utolsó egyező értéket oktatóanyag segíthet abban, hogy az utolsó megfelelő értéket részletesen megkapja.


5. Két megadott érték vagy dátum közötti értékeket egyező Vlookup

Előfordulhat, hogy érdemes két érték vagy dátum között keresni az értékeket, és visszaadni a megfelelő eredményeket az alábbi képernyőképen látható módon, ebben az esetben használhatja a LOOKUP funkciót és a rendezett táblázatot.

Vlookup két megadott érték vagy dátum közötti értékek egyeztetése képlettel

1. Először az eredeti táblának rendezett adattartománynak kell lennie. Ezután másolja vagy írja be a következő képletet egy üres cellába:

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

2. Ezután húzza a kitöltő fogantyút, hogy kitöltse ezt a képletet más szükséges cellákra, és most megkapja az összes megfelelő rekordot az adott érték alapján, lásd a képernyőképet:

Megjegyzések:

  • 1. A fenti képletben A2: A6 a kisebb értékek tartománya és B2: B6 a nagyobb számok tartománya az adattartományban, a E2 az az adott érték, amelyet meg akarsz szerezni a megfelelő értéknek, C2: C6 az oszlopadatok, amelyekből kivonni kívánja.
  • 2. Ez a képlet két dátum közötti egyező értékek kinyerésére is használható, az alábbi képernyőképen:

Vlookup két megadott érték vagy dátum közötti értékek egyeztetése hasznos funkcióval

Ha fájdalmas a fenti képlet, itt bemutatok egy egyszerű eszközt - Kutools az Excel számára, Annak KERESÉS két érték között funkcióval, a képlet megjegyzése nélkül visszaadhatja a megfelelő elemet a két érték vagy dátum közötti konkrét érték vagy dátum alapján.   Kattintson a Kutools for Excel letöltéséhez most!


6. Helyettesítő karakterek használata részleges egyezésekhez a Vlookup funkcióban

Az Excelben a helyettesítő karakterek felhasználhatók a Vlookup függvényen belül, amely részleges egyezést hajt végre egy keresési értéken. Például a Vlookup segítségével visszaadhatja az egyező értéket egy táblából a keresési érték egy része alapján.

Tételezzük fel, hogy van egy sor adatom, amint az az alábbi képernyőképen látható. Most a keresztnév (nem a teljes név) alapján szeretném kivonni a pontszámot. Hogyan oldhatná meg ezt a feladatot az Excel programban?

1. A normál Vlookup funkció nem működik megfelelően, helyettesítő karakterekkel kell összekapcsolnia a szöveges vagy cellahivatkozást, kérjük, másolja vagy írja be az alábbi képletet egy üres cellába:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

2. Ezután húzza a kitöltő fogantyút, hogy kitöltse ezt a képletet más szükséges cellákra, és az összes egyeztetett pontszámot visszaküldtük az alábbi képernyőkép szerint:

Megjegyzések:

  • 1. A fenti képletben E2 & ”*” a keresési érték, az értéke E2 és a * helyettesítő karakter (a „*” bármelyik karaktert vagy karaktereket jelöli), A2: C11 a keresési tartomány, a szám 3 a visszatérendő értéket tartalmazó oszlop.
  • 2. Helyettesítő karakterek használata esetén a Vlookup használatakor meg kell adnia a pontos egyezési módot FALSE vagy 0 értékkel a Vlookup függvény utolsó argumentumához.

Tipp:

1. Keresse meg és adja meg az adott értékre végződő megfelelő értékeket, alkalmazza ezt a képletet: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Az egyeztetett érték megkereséséhez és visszaadásához a szöveges karakterlánc egy része alapján, függetlenül attól, hogy a megadott szöveg a szöveges karakterlánc előtt, mögött vagy közepén van-e, csak két * karaktert kell összekapcsolnia a cellahivatkozás vagy a szöveg körül. Kérjük, tegye a következőket: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Vlookup értékek egy másik munkalapról

Előfordulhat, hogy több munkalapmal kell dolgoznia, a Vlookup függvény felhasználható egy másik munkalap adatainak megkeresésére, ugyanúgy, mint egy munkalapon.

Például két munkalapja van, az alábbi képernyőképen: a megadott adatok megkereséséhez és visszaadásához tegye a következőket:

1. Kérjük, írja be vagy másolja az alábbi képletet egy üres cellába, ahová az egyező elemeket szeretné kapni:

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

2. Ezután húzza le a kitöltő fogantyút azokra a cellákra, amelyeken alkalmazni szeretné ezt a képletet, és a megfelelő eredményeket megkapja, amire szüksége van, lásd a képernyőképet:

Jegyzet: A fenti képletben:

  • A2 a keresési értéket képviseli;
  • Adatlap annak a munkalapnak a neve, amelyről adatokat akar keresni, ((Ha a lap neve szóközt vagy írásjeleket tartalmaz, akkor egy idézőjelet kell csatolnia a lap neve köré, ellenkező esetben közvetlenül használhatja a lap nevét, például = VLOOKUP (A2, Adatlap! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 az adatlap azon adattartománya, ahol adatokat keresünk;
  • a szám 3 az oszlop száma, amely tartalmazza azokat az egyeztetett adatokat, amelyekből vissza szeretne térni.

8. Vlookup értékek egy másik munkafüzetből

Ez a szakasz a keresésről és egy másik munkafüzet megfelelő értékeinek visszaadásáról fog beszélni a Vlookup függvény használatával.

Például az első munkafüzet tartalmazza a termék- és költséglistákat, most pedig ki szeretné vonni a megfelelő költségeket a második munkafüzetbe a termékelem alapján, az alábbi képernyőképen.

1. Ha meg szeretné szerezni a relatív költségeket egy másik munkafüzetből, először nyissa meg mindkét használni kívánt munkafüzetet, majd alkalmazza a következő képletet egy cellába, ahová be szeretné helyezni az eredményt:

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

2. Ezután húzza és másolja ezt a képletet más szükséges cellákba, lásd a képernyőképet:

Megjegyzések:

  • 1. A fenti képletben:
    B2 a keresési értéket képviseli;
    [Product list.xlsx] 1. lap annak a munkafüzetnek és munkalapnak a neve, amelyből adatokat szeretne keresni, (A munkafüzetre való hivatkozás szögletes zárójelben, a teljes munkafüzet + lap pedig egyetlen idézőjelben szerepel);
    A2: B6 egy másik munkafüzet munkalapjának adatköre, ahol adatokat keresünk;
    a szám 2 az oszlop száma, amely tartalmazza azokat az egyeztetett adatokat, amelyekből vissza szeretne térni.
  • 2. Ha a keresési munkafüzet zárva van, a keresési munkafüzet teljes elérési útvonala a képletben jelenik meg, az alábbi képernyőképen:

9. Vlookup és üres vagy megadott szöveget adjon vissza 0 vagy # N / A hibaérték helyett

Normál esetben, amikor a vlookup függvényt alkalmazza a megfelelő érték visszaadására, ha a megfelelő cella üres, akkor 0-t ad vissza, és ha a megfelelő értéke nem található, akkor # N / A hibát kap, mint az alábbi képernyőképen látható. Ahelyett, hogy a 0 vagy # N / A értéket üres cellával vagy más tetszőleges értékkel jelenítené meg, ez Vlookup 0 vagy N / A helyett üres vagy specifikus értéket ad vissza bemutató lépésről lépésre tehet egy szívességet.


Haladó VLOOKUP példák

1. Kétirányú keresés Vlookup funkcióval (Vlookup a sorban és az oszlopban)

Előfordulhat, hogy kétdimenziós keresésre van szükség, ami azt jelenti, hogy a Vlookup a sorban és az oszlopban egyaránt. Tegyük fel, hogy ha a következő adattartomány van, és most, akkor lehet, hogy meg kell kapnia egy adott termék értékét egy megadott negyedévben. Ez a szakasz bemutat néhány képletet ennek a feladatnak az Excel kezelésére.

1. képlet: VLOOKUP és MATCH függvények használata

Az Excelben a VLOOKUP és a MATCH függvények kombinációjával kétirányú keresést végezhet, kérjük, alkalmazza a következő képletet egy üres cellába, majd nyomja meg az belép kulcs az eredmény eléréséhez.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Jegyzet: A fenti képletben:

  • H1: az az oszlop keresési értéke, amely alapján meg akarja szerezni a megfelelő értéket;
  • A2: E6: az adattartomány a sorfejlécekkel együtt;
  • H2: a keresési érték abban a sorban, amely alapján meg szeretné kapni a megfelelő értéket;
  • A1: E1: az oszlopfejlécek cellái.

2. képlet: INDEX és MATCH függvények használata

Itt van egy másik képlet, amely segíthet a kétdimenziós megkeresésben, kérjük, alkalmazza az alábbi képletet, majd nyomja meg az gombot belép gombot a kívánt eredmény eléréséhez.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Jegyzet: A fenti képletben:

  • B2: E6: az adattartomány, ahonnan az egyező elemet vissza kell adni;
  • H1: az az oszlop keresési értéke, amely alapján meg akarja szerezni a megfelelő értéket;
  • A2: A6: a sorfejlécek tartalmazzák a keresni kívánt terméket.
  • H2: a keresési érték abban a sorban, amely alapján meg szeretné kapni a megfelelő értéket;
  • B1: E1: az oszlopfejlécek a keresni kívánt negyedet tartalmazzák.

2. Két vagy több feltétel alapján egyező Vlookup érték

Könnyen megkeresheti az egyező értéket egy kritérium alapján, de ha két vagy több kritériuma van, mit tehet? Az Excel LOOKUP vagy MATCH és INDEX funkciói segítségével gyorsan és egyszerűen megoldhatja ezt a feladatot.

Például az alábbi adattáblázat áll rendelkezésemre, hogy az adott termék és méret alapján kiegyenlített ár visszatéréséhez a következő képletek segíthetnek.

1. képlet: A LOOKUP funkció használata

Kérjük, alkalmazza az alábbi képletet egy cellába, ahol meg szeretné szerezni az eredményt, majd nyomja meg az Enter billentyűt, lásd a képernyőképet:

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Megjegyzések:

  • 1. A fenti képletben:
    A2: A12 = G1: azt jelenti, hogy megkeressük a G1 kritériumait az A2: A12 tartományban;
    B2: B12 = G2: azt jelenti, hogy megkeressük a G2 kritériumait a B2: B12 tartományban;
    D2: D12: az a tartomány, amellyel vissza szeretné adni a megfelelő értéket.
  • 2. Ha kettőnél több kritériuma van, csak be kell illesztenie a többi kritériumot a képletbe, például: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

2. képlet: Az INDEXT és a MATCH függvények használata

Az Index és az Egyezés funkció kombinációja szintén felhasználható az egyező érték több szempont alapján történő visszaadására. Kérjük, másolja vagy írja be a következő képletet:

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Ezután nyomja meg a Ctrl + Shift + Enter billentyűket, hogy megkapja a relatív értéket, amire szüksége van. Lásd a képernyőképet:

Megjegyzések:

  • 1. A fenti képletben:
    A2: A12 = G1: azt jelenti, hogy megkeressük a G1 kritériumait az A2: A12 tartományban;
    B2: B12 = G2: azt jelenti, hogy megkeressük a G2 kritériumait a B2: B12 tartományban;
    D2: D12: az a tartomány, amellyel vissza szeretné adni a megfelelő értéket.
  • 2. Ha kettőnél több kritériuma van, csak be kell illesztenie az új feltételeket a képletbe, például: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup több egyező érték visszaadásához egy vagy több feltétellel

Az Excelben a Vlookup függvény keres egy értéket, és csak akkor adja vissza az első megfelelő értéket, ha több megfelelő érték található. Előfordulhat, hogy vissza szeretné adni az összes megfelelő értéket egy sorban, egy oszlopban vagy egy cellában. Ez a szakasz arról fog beszélni, hogyan lehet a több egyező értéket egy vagy több feltétellel visszaadni egy munkafüzetben.

Vlookup minden egyező értéket egy vagy több feltétel alapján vízszintesen

Vlookup az összes megfelelő értéket egy feltétel alapján vízszintesen:

A Vlookup programba, és az összes megfelelő értéket egy adott érték alapján vízszintesen adja vissza, az általános képlet a következő:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Megjegyzések: m a visszatérési tartomány első cellájának sorszáma mínusz 1.
      n az első képletcella oszlopszáma mínusz 1.

1. Kérjük, alkalmazza az alábbi képletet egy üres cellába, majd nyomja meg az gombot Ctrl + Shift + Enter billentyűk együtt kapják meg az első egyező értéket, lásd a képernyőképet:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Ezután válassza ki az első képlet cellát, és húzza a kitöltő fogantyút jobb cellákba, amíg meg nem jelenik az üres cella, és az összes megfelelő elem kibontásra kerül, lásd a képernyőképet:

Tipp:

Ha a visszaküldött listában vannak duplikált egyező értékek, az ismétlések figyelmen kívül hagyásához használja ezt a képletet, majd nyomja meg az belép az első eredmény elérése érdekében: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Adja meg ezt a képletet: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") az első eredmény melletti cellába, majd nyomja meg a gombot Ctrl + Shift + Enter billentyűk együtt a második eredmény eléréséhez, majd húzza ezt a képletet a jobb cellákba, hogy az összes többi egyező értéket megkapja, amíg üres cella meg nem jelenik, lásd a képernyőképet:


Vlookup összes megfelelő érték két vagy több feltétel alapján vízszintesen:

A Vlookup programba, és az összes megfelelő értéket vízszintesebben, specifikusabb értékek alapján adja vissza, az általános képlet a következő:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Megjegyzések: m a visszatérési tartomány első cellájának sorszáma mínusz 1.
      n az első képletcella oszlopszáma mínusz 1.

1. Alkalmazza a következő képletet egy üres cellába, ahová ki szeretné adni az eredményt:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Ezután válassza ki a képlet cellát, és húzza a kitöltő fogantyút a jobb cellákba, amíg az üres cella meg nem jelenik, és a megadott kritériumok alapján az összes megfelelő értéket visszaadja, lásd a képernyőképet:

Megjegyzések: További feltételek megadásához egyszerűen be kell csatlakoznia a keresési_érték és a keresési_tartomány közé a képletbe, például: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Vlookup minden egyező érték függőlegesen egy vagy több feltétel alapján

Vlookup az összes megfelelő érték függőlegesen egy feltétel alapján:

A Vlookup-hoz és az összes egyező érték függőleges visszaadásához egy adott érték alapján az általános képlet a következő:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Megjegyzések: m a visszatérési tartomány első cellájának sorszáma mínusz 1.
      n az első képletcella sorszáma mínusz 1.

1. Másolja vagy írja be a következő képletet egy cellába, ahol meg szeretné szerezni az eredményt, majd nyomja meg az gombot Ctrl + Shift + Enter billentyűk együtt kapják meg az első egyező értéket, lásd a képernyőképet:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Ezután válassza ki az első képlet cellát, és húzza lefelé a kitöltő fogantyút más cellákra, amíg meg nem jelenik az üres cella, és az összes megfelelő elem felkerül egy oszlopba, lásd a képernyőképet:

Tipp:

Ha figyelmen kívül akarja hagyni a visszaadott egyező értékek duplikátumait, használja a következő képleteket: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Ezután nyomja meg a gombot Ctrl + Shift + Enter billentyűk együtt kapják meg az első egyező értéket, majd húzzák le ezt a képlet cellát más cellákra, amíg meg nem jelenik az üres cella, és a kívánt eredményt kapja:


Vlookup az összes egyező érték függőlegesen két vagy több feltétel alapján:

A Vlookup programba, és az összes megfelelő értéket függőlegesebben, konkrétabb értékek alapján adja vissza, az általános képlet a következő:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Megjegyzések: m a visszatérési tartomány első cellájának sorszáma mínusz 1.
      n az első képletcella sorszáma mínusz 1.

1. Másolja az alábbi képletet egy üres cellába, majd nyomja meg az gombot Ctrl + Shift + Enter billentyűk együtt kapják meg az első egyező elemet.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Ezután húzza le a képlet cellát más cellákra, amíg meg nem jelenik az üres cella, lásd a képernyőképet:

Megjegyzések: További feltételek megadásához egyszerűen be kell csatlakoznia a keresési_érték és a keresési_tartomány közé a képletbe, például: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup két vagy több feltétel alapján minden egyező értéket egyetlen cellába

Ha szeretné a Vlookup programot létrehozni, és több egyeztetett értéket visszaadni egyetlen cellába a megadott elválasztóval, a TEXTJOIN új funkciója segíthet a feladat gyors és egyszerű megoldásában.

Vlookup egy feltételen alapuló összes egyező értéket egyetlen cellába:

Kérjük, alkalmazza az alábbi egyszerű képletet egy üres cellába, majd nyomja meg az gombot Ctrl + Shift + Enter gombok együtt az eredmény eléréséhez:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Tipp:

Ha figyelmen kívül akarja hagyni a visszaadott egyező értékek duplikátumait, használja a következő képleteket: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Vlookup két vagy több feltétel alapján az összes megfelelő érték egyetlen cellába:

Ha több feltételt szeretne kezelni, amikor az összes egyező értéket egyetlen cellába adja vissza, kérjük, alkalmazza az alábbi képletet, majd nyomja meg az gombot Ctrl + Shift + Enter gombok együtt az eredmény eléréséhez:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Megjegyzések:

1. A TEXTJOIN funkció csak az Excel 2019 és az Office 365 rendszerekben érhető el.

2. Ha az Excel 2016 és korábbi verziókat használja, kérjük, használja az alábbi cikkek Felhasználó által definiált funkcióját:


4. Vlookup az egyeztetett cellák egészének vagy egész sorának visszaadásához

Ebben a részben arról fogok beszélni, hogy a Vlookup függvény segítségével hogyan lehet lekérni az egyező érték teljes sorát.

1. Kérjük, másolja vagy írja be az alábbi képletet egy üres cellába, ahová ki szeretné adni az eredményt, majd nyomja meg a gombot belép gombot az első érték megszerzéséhez.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

2. Ezután húzza a képlet cellát jobbra, amíg a teljes sor adatai meg nem jelennek, lásd a képernyőképet:

Megjegyzések: A fenti képletben F2 az a keresési érték, amely alapján az egész sort vissza szeretné adni, A1: D12 a használni kívánt adattartomány, A1 jelzi az adattartomány első oszlopának számát.

Tipp:

Ha az egyező érték alapján több sor található, az összes megfelelő sor visszaadásához használja ezt a képletet: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), majd nyomja meg a gombot Ctrl + Shift + Enter gombokat együtt az első eredmény eléréséhez, majd húzza a kitöltő fogantyút közvetlenül a cellákhoz, lásd a képernyőképet:

Ezután húzza lefelé a kitöltő fogantyút a cellákon, hogy megkapja az összes megfelelő sort az alábbi képernyőképen:


5. Végezzen több Vlookup funkciót (beágyazott Vlookup) az Excelben

Előfordulhat, hogy érdemes több táblában is megnéznie az értékeket, ha bármelyik táblázat tartalmazza a megadott keresési értéket, ahogy az alábbi képernyőképen látható, ebben az esetben egy vagy több Vlookup függvényt kombinálhat az IFERROR funkcióval többszörös keresés végrehajtásához.

A beágyazott Vlookup függvény általános képlete:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Jegyzet:

  • keresési_érték: az érték, amelyet keres;
  • Table1, Table2, Table3, ...: azok a táblázatok, amelyekben megtalálható a keresési és a visszatérési érték;
  • col: annak a táblázatnak az oszlopszáma, amelyből vissza szeretné adni az egyező értéket.
  • 0: Ezt pontos egyezéshez használják.

1. Kérjük, alkalmazza a következő képletet egy üres cellába, ahová az eredményt fel kívánja tenni:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Ezután húzza lefelé a kitöltő fogantyút azokra a cellákra, amelyeken alkalmazni szeretné ezt a képletet, és az összes egyező értéket visszaküldtük, ahogy az alábbi képernyőkép látható:

Megjegyzések:

  • 1. A fenti képletben J3 az az érték, amelyet keres; A3: B7, D3: E7, G3: H7 azok a táblázattartományok, amelyekben a keresési és a visszatérési érték létezik; A szám 2 a tartomány oszlopszáma, ahonnan az egyező értéket adja vissza.
  • 2. Ha a keresési érték nem található, akkor hibaérték jelenik meg, a hiba helyettesítéséhez olvasható szöveggel használja ezt a képletet: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup annak ellenőrzésére, hogy létezik-e érték egy másik oszlopban szereplő listadatok alapján

A Vlookup funkció segíthet abban is, hogy ellenőrizze, léteznek-e értékek egy másik lista alapján, például, ha meg szeretné keresni a neveket a C oszlopban, és csak az Igen vagy Nem választ adja meg, ha a név megtalálható vagy nem az A oszlopban, mint az alábbi képernyőkép Látható.

1. Kérjük, alkalmazza a következő képletet egy üres cellába:

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

2. Ezután húzza lefelé a kitöltő fogantyút azokhoz a cellákhoz, amelyekbe kitölteni szeretné ezt a képletet, és az eredményt megkapja, amire szüksége van.

Megjegyzések: A fenti képletben C2 az ellenőrizni kívánt keresési érték; A2: A10 annak a tartománynak a listája, ahonnan a keresési értékek megtalálhatók; a szám 1 az az oszlop száma, ahonnan értéket akar beolvasni a tartományban.


7. Vlookup és összesítse az összes egyező értéket sorokban vagy oszlopokban

Ha numerikus adatokkal dolgozik, néha az egyező értékek táblázatból történő kivonásakor előfordulhat, hogy több oszlopban vagy sorban kell összegeznie a számokat. Ez a szakasz néhány képletet mutat be a munka befejezéséhez az Excelben.

Vlookup és összesítse az összes egyező értéket egy sorban vagy több sorban

Tegyük fel, hogy van egy terméklistája, amely több hónapos eladással rendelkezik, amint az az alábbi képernyőképen látható. Most minden hónapban meg kell összesítenie az összes megrendelést az adott termékek alapján.

Vlookup és összegezze a sorban az első egyező értékeket:

1. Kérjük, másolja vagy írja be az alábbi képletet egy üres cellába, majd nyomja meg az gombot Ctrl + Shift + Enter gombokat együtt az első eredmény eléréséhez.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

2. Ezután húzza lefelé a kitöltő fogantyút, hogy ezt a képletet más szükséges cellákba másolja, és az első egyező érték sorának összes értéke összeadódik, lásd a képernyőképet:

Megjegyzések: A fenti képletben: H2 a keresett értéket tartalmazó cella; A2: F9 az adattartomány (oszlopfejlécek nélkül), amely tartalmazza a keresési és az egyeztetett értékeket; A szám 2,3,4,5,6 {} a tartomány teljes számításához használt oszlopszámok.


Vlookup, és összesítse az összes egyező értéket több sorban:

A fenti képlet csak az első illesztett értéket tudja összegezni egymás után. Ha az összes mérkőzést több sorban szeretné összegezni, kérjük, használja a következő képletet, majd húzza lefelé a kitöltő fogantyút azokra a cellákra, amelyeken alkalmazni szeretné ezt a képletet, és megkapja a kívánt eredményt, lásd a képernyőképet:

=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)

Megjegyzések: A fenti képletben: H2 az a keresési érték, amelyet keres; A2: A9 a keresési értéket tartalmazó sorfejlécek; B2: F9 az összegezni kívánt numerikus értékek adattartománya.


Vlookup és összesítse az összes egyező értéket egy oszlopban vagy több oszlopban

Vlookup és összegezze az oszlop első illesztett értékeit:

Ha összegezni szeretné az adott hónapok teljes értékét az alábbi képernyőképen látható módon.

Alkalmazza az alábbi képletet egy üres cellába, majd húzza lefelé a kitöltő fogantyút, hogy ezt a képletet más cellákba másolja, most az oszlop adott hónapja alapján az első egyeztetett értékeket összegezték, lásd a képernyőképet:

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Megjegyzések: A fenti képletben: H2 az a keresési érték, amelyet keres; B1: F1 a keresési értéket tartalmazó oszlopfejlécek; B2: F9 az összegezni kívánt numerikus értékek adattartománya.


Vlookup és összesítse az összes egyező értéket több oszlopban:

A Vlookup és az összes egyező érték összesítéséhez több oszlopban a következő képletet kell használnia:

=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))

Megjegyzések: A fenti képletben: H2 az a keresési érték, amelyet keres; B1: F1 a keresési értéket tartalmazó oszlopfejlécek; B2: F9 az összegezni kívánt numerikus értékek adattartománya.


Vlookup és összegezze az első egyező vagy az összes egyeztetett értéket egy erőteljes funkcióval

Lehet, hogy a fenti képleteket nehéz megjegyeznie, ebben az esetben egy praktikus funkciót fogok ajánlani - Keresés és összeg of Kutools az Excel számára, ezzel a funkcióval a lehető legegyszerűbben érheti el az eredményt.    Kattintson a Kutools for Excel letöltéséhez most!


Vlookup és összesíti az összes egyező értéket sorokban és oszlopokban egyaránt

Ha összegezni szeretné az értékeket, amikor meg kell egyeznie az oszlopnak és a sornak is, például megkapja a pulóver termék teljes értékét a március hónapban, az alábbi képernyőképen.

Kérjük, alkalmazza a következő képletet egy cellába, majd nyomja meg az Enter billentyűt az eredmény eléréséhez, lásd a képernyőképet:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Megjegyzések: A fenti képletben: B2: F9 az összegezni kívánt numerikus értékek adattartománya; B1: F1 is az oszlopfejlécek tartalmazzák azt a keresési értéket, amely alapján összegezni kíván; I2 a keresett érték a keresett oszlopfejléceken belül; A2: A9 a sorfejlécek tartalmazzák azt a keresési értéket, amely alapján összegezni akar; H2 a keresett érték a keresett sorfejléceken belül.


8. Vlookup két tábla egyesítéséhez egy vagy több kulcsoszlop alapján

A mindennapi munkában az adatok elemzése során előfordulhat, hogy az összes szükséges információt egyetlen táblába kell gyűjteni egy vagy több kulcsoszlop alapján. Ennek a feladatnak a megoldásához a Vlookup funkció is szívességet tehet Önnek.

Vlookup két tábla egyesítéséhez egy kulcsoszlop alapján

Például két táblája van, az első táblázat tartalmazza a termékek és nevek adatait, a második táblázat pedig a termékeket és a megrendeléseket tartalmazza. Most ezt a két táblázatot egyesítenie kell a közös termékoszlop egy táblába illesztésével.

1. képlet: A VLOOKUP funkció használata

Ha két oszlopot egyesít egy kulcsoszlop alapján, kérjük, alkalmazza a következő képletet egy üres cellába, ahol meg szeretné szerezni az eredményt, majd húzza lefelé a kitöltő fogantyút azokra a cellákra, amelyeken alkalmazni szeretné ezt a képletet. kap egy összevont táblázatot, amelynek rendelési oszlopa csatlakozik az első tábla adatokhoz a kulcsoszlop adatai alapján.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Megjegyzések: A fenti képletben A2 az az érték, amit keres, E2: F8 a keresendő táblázat, a szám 2 a táblázat oszlopszáma, ahonnan beolvashatja az értéket.

2. képlet: INDEX és MATCH függvények használata

Ha a közös adatok a jobb oldalon, a visszaküldött adatok pedig a bal oldali oszlopban találhatók a második táblázatban, a rendelés oszlop egyesítéséhez a Vlookup függvény nem tudja elvégezni a feladatot. Jobbról balra történő felnézéshez az INDEX és a MATCH függvényeket használhatja a Vlookup funkció helyettesítésére.

Kérjük, másolja vagy írja be az alábbi képletet egy üres cellába, majd másolja le a képletet az oszlopba, és a sorrend oszlop csatlakozik az első táblához, lásd a képernyőképet:

=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))

Megjegyzések: A fenti képletben A2 a keresett érték, E2: E8 a visszaküldendő adatok köre, F2: F8 a keresési tartomány, amely tartalmazza a keresési értéket.


Vlookup két tábla egyesítéséhez több kulcsoszlop alapján

Ha a két összekapcsolni kívánt táblának több kulcsoszlopa van, akkor a táblák e közös oszlopok alapján történő egyesítéséhez az INDEX és a MATCH függvények segíthetnek.

Két tábla több kulcsoszlopon alapuló egyesítésének általános képlete:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Kérjük, alkalmazza az alábbi képletet egy üres cellába, ahová be szeretné helyezni az eredményt, majd nyomja meg a gombot Ctrl + Shift + Enter billentyűk együtt kapják meg az első egyező értéket, lásd a képernyőképet:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Megjegyzések: A fenti képletben mit mutatnak a cellahivatkozások, az alábbi képernyőképen:

2. Ezután válassza ki az első képlet cellát, és húzza a kitöltő fogantyút a képlet más cellákba történő másolásához, amire szüksége van:

tippek: Az Excel 2016 és újabb verzióiban a Teljesítmény lekérdezés két vagy több tábla egyesítése a kulcsoszlopok alapján. Kérjük, kattintson a részletekről lépésről lépésre.

9. Vlookup megfelelő értékek több munkalapon

Megpróbálta már a Vlookup értékeit több munkalapon is használni? Tételezzük fel, hogy a következő három munkalapot adattartománnyal rendelkezem, és most szeretném megszerezni a megfelelő értékek részét a három munkalap kritériumai alapján, hogy az eredményt az alábbi képernyőképen láthassam. Ebben az esetben a Vlookup értékek több munkalapon bemutató lépésről lépésre tehet egy szívességet.


A VLOOKUP egyeztetett értékek megőrzik a cellák formázását

1. Vlookup a cella formázásának (cellaszín, betűszín) és a keresési érték eléréséhez

Mint mindannyian tudjuk, a normál Vlookup funkció csak abban segít, hogy visszaküldjük az egyező értéket egy másik adattartományból, de néha érdemes megadnia a megfelelő értéket a cella formázásával együtt, mint például a kitöltési szín, a betűszín, a betűstílus az alábbi képernyőképen látható. Ez a szakasz arról fog beszélni, hogyan lehet elérni a cellák formázását a visszaadott értékkel az Excelben.

A következő lépésekkel keresse meg és adja vissza a megfelelő értéket a cellák formázásával együtt:

1. A munkalap tartalmazza a Vlookup által kívánt adatokat, kattintson a jobb gombbal a lap fülre, és válassza ki Kód megtekintése a helyi menüből. Lásd a képernyőképet:

2. A nyitottban Microsoft Visual Basic for Applications ablakba, kérjük, másolja a VBA kód alatt a Kód ablakba.

1. VBA-kód: Vlookup a cella formázásának és a keresési értéknek a megszerzéséhez

Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
                Range(xDic.Keys(I)).Font.FontStyle = _
                Range(xDic.Items(I)).Font.FontStyle
                Range(xDic.Keys(I)).Font.Size = _
                Range(xDic.Items(I)).Font.Size
                Range(xDic.Keys(I)).Font.Color = _
                Range(xDic.Items(I)).Font.Color
                Range(xDic.Keys(I)).Font.Name = _
                Range(xDic.Items(I)).Font.Name
                Range(xDic.Keys(I)).Font.Underline = _
                Range(xDic.Items(I)).Font.Underline
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

3. Még mindig a Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modulok, majd másolja az alábbi VBA kódot a Modul ablakba.

2. VBA-kód: Vlookup a cella formázásának és a keresési értéknek a megszerzéséhez

Public xDic As New Dictionary
Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. A fenti kódok beillesztése után kattintson a gombra Eszközök > Referenciák a Microsoft Visual Basic for Applications ablak. Ezután ellenőrizze a Microsoft Script futásidejű jelölőnégyzet a Referenciák - VBAProject párbeszédablak. Képernyőképek megtekintése:

5. Ezután kattints a gombra OK a párbeszédpanel bezárásához, majd a kódablak mentéséhez és bezárásához térjen vissza a munkalapra, majd alkalmazza ezt a képletet: =LookupKeepFormat(E2,$A$1:$C$10,3) egy üres cellába, ahol ki szeretné adni az eredményt, majd nyomja meg az Enter billentyűt. Lásd a képernyőképet:

Megjegyzések: A fenti képletben E2 az az érték, amelyet fel fog keresni, A1: C10 a táblázat tartománya és száma 3 annak a táblázatnak az oszlopszáma, amelynek vissza szeretné adni az egyező értéket.

6. Ezután válassza ki az első eredmény cellát, és húzza lefelé a kitöltő fogantyút, hogy az összes eredmény megjelenjen a formázással együtt. Lásd a képernyőképet.


2. Tartsa meg a dátumformátumot egy Vlookup által visszaadott értéktől

Normális esetben, amikor a Vloook függvény használatával keresi meg és adja vissza az egyeztetett dátumformátum értékét, valamilyen számformátum jelenik meg, az alábbi képernyőképen. Annak érdekében, hogy a dátumformátum megmaradjon a visszaküldött eredménytől, csatolja a TEXT függvényt a Vlookup függvényhez.

Kérjük, alkalmazza az alábbi képletet egy üres cellába, majd húzza a kitöltő fogantyút, hogy ezt a képletet más cellákba másolja, és az összes egyeztetett dátum vissza lett adva az alábbi képernyőképen:

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Megjegyzések: A fenti képletben E2 a megjelenési érték, A2: C9 a keresési tartomány, a szám 3 annak az oszlopnak a száma, amelynek értékét vissza szeretné adni éééé / hh / nn a megtartani kívánt dátumformátum.


3. Vlookup és egyező érték visszaadása cellakommentárral

Megpróbálta már a Vlookup programot, hogy ne csak az egyező celladatokat adja vissza, hanem a cellamegjegyzéseket is, az Excel-ben is, az alábbi képernyőkép szerint? Ennek a feladatnak a megoldásához az alábbi Felhasználó által definiált funkció tehet Önnek szívességet.

1. Tartsa lenyomva a ALT + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. Kattints betétlap > Modulok, majd másolja és illessze be a következő kódot a Modul ablakba.

VBA kód: Vlookup és egyező érték visszaadása cellamegjegyzéssel:

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

3. Ezután mentse el és zárja be a kódablakot, írja be ezt a képletet: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) egy üres cellába az eredmény megkereséséhez, majd húzza a kitöltő fogantyút, hogy ezt a képletet más cellákba másolja, most az egyeztetett értékek és a megjegyzések egyszerre kerülnek vissza, lásd a képernyőképet:

Megjegyzések: A fenti képletben D2 a keresési érték, amelynek vissza szeretné adni a megfelelő értéket, A2: B9 a használni kívánt adattábla, a szám 2 az oszlop száma, amely tartalmazza a visszaadni kívánt egyeztetett értéket.


4. Foglalkozzon a szöveggel és a valós számokkal a Vlookup programban

Például van egy adattartományom, az eredeti táblázatban szereplő azonosítószám számformátum, a szövegként tárolt kereső cellában, a normál Vlookup függvény alkalmazásakor # N / A hibaeredmény jelenik meg, az alábbi képernyőképen Látható. Ebben az esetben hogyan szerezhetné meg a helyes információt, ha a táblázatban szereplő keresési számnak és az eredeti számnak eltérő az adatformátuma?

A szöveg és a valós számok kezeléséhez a Vlookup függvényben kérjük, alkalmazza a következő képletet egy üres cellába, majd húzza lefelé a kitöltő fogantyút a képlet másolásához, és a megfelelő eredményeket kapja, ahogy az alábbi képernyőképen látható:

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Megjegyzések:

  • 1. A fenti képletben D2 a keresési érték, amelynek vissza szeretné adni a megfelelő értéket, A2: B8 a használni kívánt adattábla, a szám 2 az oszlop száma, amely tartalmazza a visszaadni kívánt egyeztetett értéket.
  • 2. Ez a képlet akkor is jól működik, ha nem biztos abban, hogy hol vannak számai és hol van szövege.

Töltse le a VLOOKUP mintafájlokat

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
  • Cellák / sorok / oszlopok egyesítése és az adatok megőrzése; Osztott cellák tartalma; Kombinálja az ismétlődő sorokat és az Összeg / Átlagot... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
  • Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
  • Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
  • Kedvenc és gyorsan beszúrható képletek, Tartományok, diagramok és képek; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
  • Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
  • Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
  • Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
  • Pivot tábla csoportosítás hét száma, a hét napja és egyebek ... Mutassa a Feloldott, Zárt cellákat különböző színekkel; Jelölje ki azokat a cellákat, amelyeknek képlete / neve van...
kte lap 201905
  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
officetab alja
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.