Note: The other languages of the website are Google-translated. Back to English
Vpiši se  \/ 
x
or
x
Registracija  \/ 
x

or

Funkcija VLOOKUP z nekaj osnovnimi in naprednimi primeri v Excelu

V Excelu je funkcija VLOOKUP zmogljiva funkcija za večino uporabnikov Excela, ki se uporablja za iskanje vrednosti v skrajnem levem delu obsega podatkov in vrnitev ustrezne vrednosti v isti vrstici iz stolpca, ki ste ga določili, kot je prikazano spodaj na sliki zaslona. . Ta vadnica govori o uporabi funkcije VLOOKUP z nekaterimi osnovnimi in naprednimi primeri v Excelu.

Kazalo:

1. Predstavitev funkcije VLOOKUP - Sintaksa in argumenti

2. Osnovni primeri VLOOKUP-a

3. Primeri naprednega VLOOKUP-a

4. Ustrezne vrednosti VLOOKUP ohranijo oblikovanje celic

5. Prenesite vzorčne datoteke VLOOKUP


Predstavitev funkcije VLOOKUP - Sintaksa in argumenti

Sintaksa funkcije VLOOKUP:

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

Argumenti:

Lookup_value: Vrednost, ki jo želite iskati. Biti mora v prvem stolpcu obsega table_array.

Tabela_ matrika: Podatkovno območje ali tabela, v kateri najdeta stolpec iskalne vrednosti in stolpec z vrednostjo rezultata.

Col_index_num: Število stolpcev, iz katerih bo vrnjena ujemajoča se vrednost. Začne se z 1 iz skrajnega levega stolpca v tabeli.

Range_lookup: Logična vrednost, ki določa, ali bo ta funkcija VLOOKUP vrnila natančno ali približno ujemanje.

  • Približno ujemanje - 1 / TRUE: Če natančnega ujemanja ni mogoče najti, formula išče najbližje ujemanje - največjo vrednost, ki je manjša od iskalne vrednosti. V tem primeru morate stolpec za iskanje razvrstiti po naraščajočem vrstnem redu.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Natančna tekma - 0 / FALSE: Uporablja se za iskanje vrednosti, ki je popolnoma enaka iskalni vrednosti. Če natančnega ujemanja ne najdemo, bo vrnjena vrednost napake # N / A.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

Opombe:

  • 1. Funkcija Vlookup išče vrednost samo od leve proti desni.
  • 2. Če je na podlagi vrednosti iskanja več ujemajočih se vrednosti, bo s funkcijo Vlookup vrnjeno le prvo ujemanje.
  • 3. Vrne vrednost napake # N / A, če vrednosti iskanja ni mogoče najti.

Osnovni primeri VLOOKUP

1 Naredite natančno ujemanje Vlookup in približno ujemanje Vlookup

Naredite natančno ujemanje Vlookup v Excelu

Če iščete natančno ujemanje s funkcijo Vlookup, morate v zadnjem argumentu uporabiti FALSE.

Če želite na primer dobiti ustrezne matematične ocene na podlagi določenih številk ID, naredite naslednje:

1. Spodnjo formulo uporabite v prazno celico, kjer želite dobiti rezultat:

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

2. Nato povlecite ročico za polnjenje navzdol do celic, v katere želite izpolniti to formulo, in dobili boste rezultate, kot jih potrebujete. Oglejte si posnetek zaslona:

Opombe:

  • 1. V zgornji formuli: F2 je vrednost, ki ji želite vrniti njeno ujemajočo se vrednost, A2: D7 je polje tabele, število 3 je številka stolpca, iz katere je vrnjena ujemajoča se vrednost, in FALSE se nanaša na natančno ujemanje.
  • 2. Če vaše vrednosti meril ni mogoče najti v obsegu podatkov, bo prikazana vrednost napake # N / A.

Naredite približno ujemanje Vlookup v Excelu

Približno ujemanje je uporabno za iskanje vrednosti med obsegi podatkov. Če natančnega ujemanja ne najdemo, bo približni Vlookup vrnil največjo vrednost, ki je manjša od iskalne vrednosti.

Če imate na primer naslednje podatke o obsegu, določenih naročil ni v stolpcu Naročila. Kako najti najbližji popust v stolpcu B?

1. Vnesite naslednjo formulo v celico, kamor želite dati rezultat:

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

2. Nato povlecite ročico za polnjenje navzdol do celic, da uporabite to formulo, in dobili boste približna ujemanja na podlagi danih vrednosti, glejte posnetek zaslona:

Opombe:

  • 1. V zgornji formuli: D2 je vrednost, za katero želite vrniti njene relativne podatke, A2: B9 je obseg podatkov, število 2 označuje številko stolpca, v katero je vrnjena ujemajoča se vrednost, in TRUE se nanaša na približno ujemanje.
  • 2. Približno ujemanje bo vrnilo največjo vrednost, ki je manjša od vaše določene vrednosti iskanja.
  • 3. Če želite s funkcijo Vlookup dobiti približno vrednost ujemanja, morate razvrstiti skrajni levi stolpec obsega podatkov v naraščajočem vrstnem redu, sicer bo vrnil napačen rezultat.

2. V Excelu naredite Vlookup z razliko med velikimi in malimi črkami

Funkcija Vlookup privzeto izvaja iskanje, ki ne razlikuje med velikimi in malimi črkami, kar pomeni, da male in velike črke obravnava kot enake. Včasih boste morda morali v Excelu poiskati med velikimi in malimi črkami, funkcije Index, Match in Exact ali funkcije Lookup and Exact vam lahko naredijo uslugo.

Na primer, imam naslednje obseg podatkov, kateri stolpec ID vsebuje besedilni niz z velikimi ali malimi črkami, zdaj želim vrniti ustrezen matematični rezultat dane številke ID.

Formula 1: Uporaba funkcij EXACT, INDEX, MATCH

1. Prosimo, vnesite ali kopirajte spodnjo formulo matrike v prazno celico, kjer želite dobiti rezultat:

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

2. Nato pritisnite Ctrl + Shift + Enter tipke hkrati, da dobite prvi rezultat, nato pa izberite celico formule, povlecite ročico za polnjenje navzdol do celic, ki jih želite izpolniti s to formulo, nato boste dobili pravilne rezultate, ki jih potrebujete. Oglejte si posnetek zaslona:

Opombe:

  • 1. V zgornji formuli: A2: A10 je stolpec, ki vsebuje posebne vrednosti, ki jih želite poiskati, F2 je iskalna vrednost, C2: C10 je stolpec, iz katerega bo vrnjen rezultat.
  • 2. Če je bilo najdenih več zadetkov, bo ta formula vedno vrnila prvo ujemanje.

Formula 2: Uporaba funkcij Iskanje in Natančno

1. Prosimo, uporabite spodnjo formulo v prazno celico, kjer želite dobiti rezultat:

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

2. Nato povlecite ročico za polnjenje navzdol do celic, v katere želite kopirati to formulo, in dobili boste ujemajoče se vrednosti z velikimi in malimi črkami, kot je prikazano spodaj:

Opombe:

  • 1. V zgornji formuli: A2: A10 je stolpec, ki vsebuje posebne vrednosti, ki jih želite poiskati, F2 je iskalna vrednost, C2: C10 je stolpec, iz katerega bo vrnjen rezultat.
  • 2. Če je bilo najdenih več zadetkov, bo ta formula vedno vrnila zadnje ujemanje.

3. Vrednosti iskanja od desne proti levi v Excelu

Funkcija Vlookup vedno poišče vrednost v skrajnem levem stolpcu obsega podatkov in vrne ustrezno vrednost iz stolpca na desno. Če želite narediti povratni Vlookup, kar pomeni, da v desnem stolpcu poiščete določeno vrednost in vrnete njeno ustrezno vrednost v levem stolpcu, kot je prikazano spodaj:

Kliknite podrobnosti o tej nalogi po korakih ...


4. Poiščite drugo, nto ali zadnjo ujemajočo se vrednost v Excelu

Če je med uporabo funkcije Vlookup najdenih več ujemajočih se vrednosti, se vrne samo prvi ujemajoči se zapis. V tem poglavju bom govoril o tem, kako s funkcijo Vlookup pridobiti drugo, nto ali zadnjo ujemajočo se vrednost.

Poiščite in vrnite drugo ali nto ujemajočo se vrednost

Recimo, da imate seznam imen v stolpcu A, tečaj usposabljanja, ki so ga kupili v stolpcu B, in zdaj iščete 2. ali n. Tečaj, ki ga je kupila določena stranka. Oglejte si posnetek zaslona:

1. Če želite dobiti drugo ali nto ujemajočo se vrednost na podlagi danih meril, v prazno celico uporabite naslednjo formulo matrike:

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

2. Nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat, nato pa izberite celico formule, povlecite ročico za polnjenje navzdol do celic, ki jih želite izpolniti s to formulo, in vse druge ujemajoče se vrednosti na podlagi danih imen so prikazane hkrati, glejte posnetek zaslona:

Opomba:

  • V tej formuli: A2: A14 je obseg z vsemi vrednostmi za iskanje, B2: B14 je obseg ujemajočih se vrednosti, iz katerih se želite vrniti, E2 je iskalna vrednost in zadnja številka 2 označuje drugo ujemajočo se vrednost, ki jo želite dobiti, če želite vrniti tretjo ujemajočo se vrednost, jo morate samo spremeniti na 3, kot jo potrebujete.

Poiščite in vrnite zadnjo ujemajočo se vrednost

Če želite vlookup in vrniti zadnjo ujemajočo se vrednost, kot je prikazano na spodnji sliki zaslona, ​​to Iskanje in vrnitev zadnje ujemajoče se vrednosti Vadnica vam lahko pomaga podrobneje pridobiti zadnjo ujemajočo se vrednost.


5. Vlookup ujemajoče se vrednosti med dvema danima vrednostma ali datumoma

Včasih boste morda želeli poiskati vrednosti med dvema vrednostma ali datumoma in vrniti ustrezne rezultate, kot je prikazano na spodnji sliki zaslona, ​​v tem primeru lahko uporabite funkcijo LOOKUP in razvrščeno tabelo.

Vlookup ujemajoče se vrednosti med dvema danima vrednostma ali datumoma s formulo

1. Najprej mora biti izvirna tabela razvrščen obseg podatkov. Nato kopirajte ali vnesite naslednjo formulo v prazno celico:

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

2. Nato povlecite ročico za polnjenje, da izpolnite to formulo v druge celice, ki jih potrebujete, in zdaj boste dobili vse ujemajoče se zapise glede na dano vrednost, glejte posnetek zaslona:

Opombe:

  • 1. V zgornji formuli: A2: A6 je obseg manjših vrednosti in B2: B6 je obseg večjih števil v vašem obsegu podatkov, E2 je dana vrednost, za katero želite dobiti ustrezno vrednost, C2: C6 so podatki stolpca, iz katerih želite izvleči.
  • 2. To formulo lahko uporabite tudi za pridobivanje ujemajočih se vrednosti med dvema datumoma, kot je prikazano spodaj:

Vlookup ujemajoče se vrednosti med dvema danima vrednostma ali datumoma s koristno funkcijo

Če vas boleča zgornja formula, bom tukaj predstavil enostavno orodje - Kutools za Excel, Z njegovim POGLED med dvema vrednostma lahko vrnete ustrezen element na podlagi določene vrednosti ali datuma med dvema vrednostma ali datumoma, ne da bi si zapomnili katero koli formulo.   Kliknite za prenos Kutools za Excel zdaj!


6. Uporaba nadomestnih znakov za delna ujemanja v funkciji Vlookup

V Excelu lahko nadomestne znake uporabljate v funkciji Vlookup, ki omogoča delno ujemanje vrednosti iskanja. Na primer, lahko uporabite Vlookup za vrnitev ujemajoče se vrednosti iz tabele, ki temelji na delu iskalne vrednosti.

Recimo, da imam vrsto podatkov, kot je prikazano na spodnji sliki zaslona, ​​zdaj želim izvleči rezultat na podlagi imena (ne polnega imena). Kako bi lahko to nalogo rešil v Excelu?

1. Običajna funkcija Vlookup ne deluje pravilno, besedilo ali sklic na celico morate združiti z nadomestnim znakom, kopirajte ali vnesite naslednjo formulo v prazno celico:

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

2. Nato povlecite ročico za polnjenje, da izpolnite to formulo v druge celice, ki jih potrebujete, in vsi ujemajoči se rezultati so vrnjeni, kot je prikazano spodaj na sliki zaslona:

Opombe:

  • 1. V zgornji formuli: E2 & "*" je iskalna vrednost, vrednost v E2 in * nadomestni znak ("*" označuje katerega koli znaka ali katerega koli znaka), A2: C11 je obseg iskanja, število 3 stolpec, ki vsebuje vrednost, ki jo je treba vrniti.
  • 2. Vlookup pri uporabi nadomestnih znakov morate za zadnji argument v funkciji Vlookup nastaviti način natančnega ujemanja z FALSE ali 0.

Nasvet:

1. Poiščite in vrnite ujemajoče se vrednosti, ki se končajo z določeno vrednostjo, uporabite to formulo: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Če želite iskati in vrniti ujemajočo se vrednost na podlagi dela besedilnega niza, ne glede na to, ali je določeno besedilo spredaj, zadaj ali sredi besedilnega niza, morate samo združiti dva znaka * okoli sklica na celico ali besedila. Prosimo, upoštevajte to formulo: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Vlookup vrednosti iz drugega delovnega lista

Običajno boste morda morali delati z več kot enim delovnim listom, funkcijo Vlookup pa lahko uporabite za iskanje podatkov z drugega lista, enako kot na enem delovnem listu.

Tako imate na primer dva delovna lista, kot je prikazano na spodnji sliki zaslona. Če želite poiskati in vrniti ustrezne podatke iz navedenega delovnega lista, naredite naslednje:

1. Prosimo, vnesite ali kopirajte spodnjo formulo v prazno celico, kjer želite dobiti ujemajoče se elemente:

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

2. Nato povlecite ročico za polnjenje navzdol do celic, za katere želite uporabiti to formulo, in dobili boste ustrezne rezultate, kot jih potrebujete, glejte posnetek zaslona:

Opomba: V zgornji formuli:

  • A2 predstavlja vrednost iskanja;
  • Podatkovni list je ime delovnega lista, iz katerega želite iskati podatke, (Če ime lista vsebuje presledke ali ločila, morate okoli imena lista priložiti enojne narekovaje, sicer lahko neposredno uporabite ime lista, na primer = VLOOKUP (A2, Podatkovni list! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 je obseg podatkov v podatkovnem listu, kjer iščemo podatke;
  • število 3 je številka stolpca, ki vsebuje usklajene podatke, iz katerih se želite vrniti.

8. Vlookup vrednosti iz drugega delovnega zvezka

V tem razdelku bomo govorili o iskanju in vrnili ujemajoče se vrednosti iz drugega delovnega zvezka s pomočjo funkcije Vlookup.

Na primer, prvi delovni zvezek vsebuje sezname izdelkov in stroškov, zdaj pa želite iz drugega delovnega zvezka izvleči ustrezne stroške na podlagi postavke izdelka, kot je prikazano spodaj.

1. Če želite pridobiti relativne stroške iz drugega delovnega zvezka, najprej odprite oba delovna zvezka, ki ju želite uporabiti, nato pa uporabite naslednjo formulo v celico, kamor želite dati rezultat:

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

2. Nato povlecite in kopirajte to formulo v druge celice, ki jih potrebujete, glejte posnetek zaslona:

Opombe:

  • 1. V zgornji formuli:
    B2 predstavlja vrednost iskanja;
    [Seznam izdelkov.xlsx] List1 je ime delovnega zvezka in delovnega lista, iz katerega želite iskati podatke, (sklic na delovni zvezek je priložen v oglatih oklepajih, celoten delovni zvezek + list pa v enojne narekovaje);
    A2: B6 je obseg podatkov na delovnem listu drugega delovnega zvezka, kjer iščemo podatke;
    število 2 je številka stolpca, ki vsebuje usklajene podatke, iz katerih se želite vrniti.
  • 2. Če je iskalni delovni zvezek zaprt, bo celotna pot datoteke za iskalni delovni zvezek prikazana v formuli, kot je prikazano na sliki spodaj:

9. Poiščite in vrnite prazno ali določeno besedilo namesto vrednosti napake 0 ali # N / A

Ko uporabite funkcijo vlookup za vrnitev ustrezne vrednosti, če je vaša ujemajoča se celica prazna, bo vrnila 0, in če vaše ujemajoče se vrednosti ne najdete, boste dobili vrednost napake # N / A, kot je prikazano na sliki spodaj. Namesto prikaza vrednosti 0 ali # N / A s prazno celico ali drugo vrednostjo, ki vam je všeč, to Vlookup vrne prazno ali določeno vrednost namesto 0 ali N / A vadnica vam lahko korak za korakom naredi uslugo.


Primeri naprednega VLOOKUP-a

1. Dvosmerno iskanje s funkcijo Vlookup (Vlookup v vrstici in stolpcu)

Včasih boste morda morali opraviti dvodimenzionalno iskanje, kar pomeni Vlookup hkrati v vrstici in stolpcu. Recimo, če imate naslednji obseg podatkov in zdaj boste morda morali dobiti vrednost za določen izdelek v določenem četrtletju. Ta razdelek bo predstavil nekaj formul za reševanje tega dela v Excelu.

Formula 1: Uporaba funkcij VLOOKUP in MATCH

V Excelu lahko za dvosmerno iskanje uporabite kombinacijo funkcij VLOOKUP in MATCH, prosimo, uporabite naslednjo formulo v prazno celico in pritisnite Vnesite ključ, da dobite rezultat.

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

Opomba: V zgornji formuli:

  • H1: iskalna vrednost v stolpcu, na podlagi katere želite dobiti ustrezno vrednost;
  • A2: E6: obseg podatkov, vključno z glavami vrstic;
  • H2: iskalna vrednost v vrstici, na podlagi katere želite dobiti ustrezno vrednost;
  • A1: E1: celice glav stolpcev.

Formula 2: Uporaba funkcij INDEX in MATCH

Tu je še ena formula, ki vam lahko pomaga pri izvedbi dvodimenzionalnega iskanja, uporabite spodnjo formulo in pritisnite Vnesite tipko, da dobite rezultat, ki ga potrebujete.

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

Opomba: V zgornji formuli:

  • B2: E6: obseg podatkov, iz katerega se vrne ujemajoči se element;
  • H1: iskalna vrednost v stolpcu, na podlagi katere želite dobiti ustrezno vrednost;
  • A2: A6: glave vrstic vsebujejo izdelek, ki ga želite iskati.
  • H2: iskalna vrednost v vrstici, na podlagi katere želite dobiti ustrezno vrednost;
  • B1: E1: glave stolpcev vsebujejo četrtletje, ki ga želite iskati.

2. Vlookup ujemajoča se vrednost na podlagi dveh ali več meril

Preprosto poiščete ujemajočo se vrednost na podlagi enega merila, kaj pa lahko storite, če imate dva ali več kriterijev? Funkcije LOOKUP ali MATCH in INDEX v Excelu vam lahko pomagajo hitro in enostavno rešiti to nalogo.

Na primer, imam spodnjo tabelo s podatki, da vam vrnem ujeto ceno glede na določen izdelek in velikost, vam lahko pomagajo naslednje formule.

Formula 1: Uporaba funkcije LOOKUP

Prosimo, uporabite spodnjo formulo v celico, kjer želite dobiti rezultat, in nato pritisnite tipko Enter, glejte posnetek zaslona:

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

Opombe:

  • 1. V zgornji formuli:
    A2: A12 = G1: pomeni iskanje meril G1 v območju A2: A12;
    B2: B12 = G2: pomeni iskanje meril G2 v območju B2: B12;
    D2: D12: obseg, za katerega želite vrniti ustrezno vrednost.
  • 2. Če imate več kot dva merila, morate v formulo združiti druga merila, na primer: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Formula 2: Uporaba funkcij INDEXT IN MATCH

Kombinacijo funkcije indeksa in ujemanja lahko uporabite tudi za vrnitev usklajene vrednosti na podlagi več meril. Prosimo, kopirajte ali vnesite naslednjo formulo:

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

Nato pritisnite tipki Ctrl + Shift + Enter, da dobite relativno vrednost, kot jo potrebujete. Oglejte si posnetek zaslona:

Opombe:

  • 1. V zgornji formuli:
    A2: A12 = G1: pomeni iskanje meril G1 v območju A2: A12;
    B2: B12 = G2: pomeni iskanje meril G2 v območju B2: B12;
    D2: D12: obseg, za katerega želite vrniti ustrezno vrednost.
  • 2. Če imate več kot dva merila, morate nova merila združiti v formulo, na primer: =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 za vrnitev več ujemajočih se vrednosti z enim ali več pogoji

V Excelu funkcija Vlookup išče vrednost in vrne prvo ujemajočo se vrednost le, če je najdenih več ustreznih vrednosti. Včasih boste morda želeli vrniti vse ustrezne vrednosti zapored, v stolpec ali v eno celico. Ta razdelek govori o tem, kako vrniti več ujemajočih se vrednosti z enim ali več pogoji v delovnem zvezku.

Vodoravno poiščite vse ujemajoče se vrednosti na podlagi enega ali več pogojev

Vodoravno poiščite vse ujemajoče se vrednosti glede na en pogoj:

Če želite Vlookup in vodoravno vrniti vse ujemajoče se vrednosti na podlagi določene vrednosti, je generična formula:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Opombe: m je številka vrstice prve celice v območju vrnitve minus 1.
      n je številka stolpca prve celice formule minus 1.

1. Prosimo, uporabite spodnjo formulo v prazno celico, nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvo ujemajočo se vrednost, glejte posnetek zaslona:

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

2. Nato izberite prvo celico formule in povlecite ročico za polnjenje v desne celice, dokler se ne prikaže prazna celica in so vsi ustrezni elementi ekstrahirani, glejte sliko zaslona:

Nasvet:

Če so na vrnjenem seznamu podvojene ujemajoče se vrednosti, če želite prezreti dvojnike, uporabite te formule in pritisnite Vnesite da dobite prvi rezultat: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Nadaljujte s to formulo: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") v celico poleg prvega rezultata in nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite drugi rezultat, nato povlecite to formulo v desne celice, da dobite vse druge ujemajoče se vrednosti, dokler se ne prikaže prazna celica, glejte posnetek zaslona:


Vodoravno poiščite vse ujemajoče se vrednosti na podlagi dveh ali več pogojev:

Če želite Vlookup in vodoravno vrniti vse ujemajoče se vrednosti na podlagi natančnejših vrednosti, je splošna formula:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Opombe: m je številka vrstice prve celice v območju vrnitve minus 1.
      n je številka stolpca prve celice formule minus 1.

1. V prazno celico, kjer želite izpisati rezultat, uporabite naslednjo formulo:

=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. Nato izberite celico formule in povlecite ročico za polnjenje v desne celice, dokler se ne prikaže prazna celica, in vrnjene bodo vse ujemajoče se vrednosti na podlagi določenih meril, glejte sliko zaslona:

Opombe: Za več kriterijev morate le združiti lookup_value in lookup_range v formulo, na primer: =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)),"").


Poiščite vse ujemajoče se vrednosti glede na enega ali več pogojev navpično

Poglej navpično vse ustrezne vrednosti glede na en pogoj:

Če želite Vlookup vrniti vse ujemajoče se vrednosti, ki temeljijo na določeni vrednosti navpično, je generična formula:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Opombe: m je številka vrstice prve celice v območju vrnitve minus 1.
      n je številka vrstice prve celice formule minus 1.

1. Kopirajte ali vnesite naslednjo formulo v celico, kjer želite dobiti rezultat, in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvo ujemajočo se vrednost, glejte posnetek zaslona:

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

2. Nato izberite prvo celico formule in povlecite ročico za polnjenje navzdol v druge celice, dokler se ne prikaže prazna celica in so vsi ustrezni elementi navedeni v stolpcu, glejte sliko zaslona:

Nasvet:

Če želite prezreti dvojnike v vrnjenih ujemajočih se vrednostih, uporabite te formule: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Nato pritisnite Ctrl + Shift + Enter in nato povlecite to celico formule navzdol v druge celice, dokler se ne prikaže prazna celica, in dobili boste rezultat, kot ga potrebujete:


Navpično poiščite vse ujemajoče se vrednosti na podlagi dveh ali več pogojev:

Če želite Vlookup in vrniti vse ujemajoče se vrednosti, ki temeljijo na natančneje določenih vrednostih, je splošna formula:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Opombe: m je številka vrstice prve celice v območju vrnitve minus 1.
      n je številka vrstice prve celice formule minus 1.

1. Kopirajte spodnjo formulo v prazno celico in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi ujemajoči se element.

=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. Nato povlecite celico formule navzdol v druge celice, dokler se ne prikaže prazna celica, glejte posnetek zaslona:

Opombe: Za več kriterijev morate le združiti lookup_value in lookup_range v formulo, na primer: =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)),"").


V eno celico poiščite vse ujemajoče se vrednosti na podlagi dveh ali več pogojev

Če želite Vlookup in vrniti več ujemajočih se vrednosti v eno celico z določenim ločilom, vam lahko nova funkcija TEXTJOIN pomaga hitro in enostavno rešiti to nalogo.

V eno celico poiščite vse ujemajoče se vrednosti na podlagi enega pogoja:

Prosimo, uporabite spodnjo preprosto formulo v prazno celico in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite rezultat:

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

Nasvet:

Če želite prezreti dvojnike v vrnjenih ujemajočih se vrednostih, uporabite te formule: =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, ""))


V eno celico poiščite vse ujemajoče se vrednosti na podlagi dveh ali več pogojev:

Za obravnavo več pogojev pri vračanju vseh ujemajočih se vrednosti v eno celico uporabite spodnjo formulo in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite rezultat:

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

Opombe:

1. Funkcija TEXTJOIN je na voljo samo v Excelu 2019 in Office 365.

2. Če uporabljate Excel 2016 in starejše različice, uporabite uporabniško določeno funkcijo spodnjih člankov:


4. Vlookup za vrnitev celotne ali celotne vrstice ujemajoče se celice

V tem poglavju bom govoril o tem, kako pridobiti celotno vrstico ujemajoče se vrednosti s funkcijo Vlookup.

1. Kopirajte ali vnesite spodnjo formulo v prazno celico, kjer želite izpisati rezultat, in pritisnite Vnesite tipko, da dobite prvo vrednost.

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

2. Nato povlecite celico formule na desno stran, dokler se ne prikažejo podatki celotne vrstice, glejte posnetek zaslona:

Opombe: V zgornji formuli, F2 je iskalna vrednost, na podlagi katere želite vrniti celotno vrstico, A1: D12 je obseg podatkov, ki ga želite uporabiti, A1 označuje številko prvega stolpca v vašem obsegu podatkov.

Nasvet:

Če na podlagi ujemajoče se vrednosti najdemo več vrstic, če želite vrniti vse ustrezne vrstice, uporabite to formulo: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat, nato povlecite ročico za polnjenje desno do celic, glejte posnetek zaslona:

In nato povlecite ročico za polnjenje navzdol po celicah, da dobite vse ujemajoče se vrstice, kot je prikazano spodaj:


5. Naredite več funkcij Vlookup (ugnezdeni Vlookup) v Excelu

Včasih boste morda želeli iskati vrednosti v več tabelah, če katera od tabel vsebuje dano iskalno vrednost, kot je prikazano na spodnjem posnetku zaslona, ​​lahko v tem primeru združite eno ali več funkcij Vlookup skupaj s funkcijo IFERROR, da izvedete več iskanj.

Splošna formula za ugnezdene funkcije Vlookup je:

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

Opomba:

  • lookup_value: vrednost, ki jo iščete;
  • Tabela1, Tabela2, Tabela3, ...: tabele, v katerih obstajajo iskalna in povratna vrednost;
  • col: številka stolpca v tabeli, iz katere želite vrniti ujemajočo se vrednost.
  • 0: Uporablja se za natančno ujemanje.

1. V prazno celico, v katero želite dati rezultat, uporabite naslednjo formulo:

=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. Nato povlecite ročico za polnjenje navzdol do celic, za katere želite uporabiti to formulo, in vse ujemajoče se vrednosti so bile vrnjene, kot je prikazano spodaj na sliki zaslona:

Opombe:

  • 1. V zgornji formuli: J3 je vrednost, ki jo iščete; A3: B7, D3: E7, G3: H7 so obsegi tabel, v katerih obstajajo iskalna in povratna vrednost; Število 2 je številka stolpca v obsegu, iz katerega se vrne ujemajoča se vrednost.
  • 2. Če vrednosti iskanja ni mogoče najti, se prikaže vrednost napake. Če želite napako nadomestiti z berljivim besedilom, uporabite to formulo: =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, da preverite, ali obstaja vrednost na podlagi podatkov seznama v drugem stolpcu

Funkcija Vlookup vam lahko pomaga tudi pri preverjanju, ali obstajajo vrednosti na podlagi drugega seznama, na primer, če želite poiskati imena v stolpcu C in vrniti Da ali Ne, če je ime v stolpcu A najdeno ali ne, kot je prikazano spodaj na sliki zaslona prikazano.

1. V prazno celico uporabite naslednjo formulo:

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

2. Nato povlecite ročico za polnjenje navzdol do celic, v katere želite izpolniti to formulo, in dobili boste rezultat, kot ga potrebujete, glejte posnetek zaslona:

Opombe: V zgornji formuli, C2 je vrednost iskanja, ki jo želite preveriti; A2: A10 je seznam obsega, od koder bodo najdene vrednosti iskanja; število 1 je številka stolpca, od koder želite pridobiti vrednost v svojem obsegu.


7. Poiščite in seštejte vse ujemajoče se vrednosti v vrsticah ali stolpcih

Če delate s številskimi podatki, boste včasih pri pridobivanju ujetih vrednosti iz tabele morda morali številke sešteti v več stolpcev ali vrstic. Ta razdelek bo predstavil nekaj formul za dokončanje tega dela v Excelu.

Poiščite in seštejte vse ujemajoče se vrednosti v vrstici ali več vrsticah

Recimo, da imate seznam izdelkov s prodajo za več mesecev, kot je prikazano spodaj na sliki zaslona, ​​zdaj pa morate vsa naročila v vseh mesecih sešteti glede na dane izdelke.

Poiščite in seštejte prve ujemajoče se vrednosti v vrstici:

1. Kopirajte ali vnesite naslednjo formulo v prazno celico in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat.

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

2. Nato povlecite ročico za polnjenje navzdol, da kopirate to formulo v druge celice, ki jih potrebujete, in vse vrednosti v vrstici prve ujemajoče se vrednosti so seštete, glejte posnetek zaslona:

Opombe: V zgornji formuli: H2 je celica, ki vsebuje vrednost, ki jo iščete; A2: F9 je obseg podatkov (brez glav stolpcev), ki vključuje iskalno vrednost in usklajene vrednosti; Število 2,3,4,5,6 {} so številke stolpcev, ki se uporabljajo za izračun skupnega obsega.


Poiščite in seštejte vse ujemajoče se vrednosti v več vrsticah:

Zgornja formula lahko sešteje le vrednosti v vrsti za prvo ujemajočo se vrednost. Če želite vsa ujemanja strniti v več vrstic, uporabite naslednjo formulo in povlecite ročico za polnjenje navzdol do celic, v katere želite uporabiti to formulo, in dobili boste želeni rezultat, ki ga potrebujete, glejte posnetek zaslona:

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

Opombe: V zgornji formuli: H2 je iskalna vrednost, ki jo iščete; A2: A9 je glava vrstic, ki vsebujejo iskalno vrednost; B2: F9 obseg podatkov številskih vrednosti, ki jih želite sešteti.


Poiščite in seštejte vse ujemajoče se vrednosti v stolpcu ali več stolpcih

Poiščite in seštejte prve ujemajoče se vrednosti v stolpcu:

Če želite sešteti skupno vrednost za določene mesece, kot je prikazano na spodnjem posnetku zaslona.

Uporabite spodnjo formulo v prazno celico in nato povlecite ročico za polnjenje navzdol, da kopirate to formulo v druge celice. Zdaj so bile prve ujemajoče se vrednosti, ki temeljijo na določenem mesecu v stolpcu, strnjene, glejte sliko zaslona:

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

Opombe: V zgornji formuli: H2 je iskalna vrednost, ki jo iščete; B1: F1 so glave stolpcev, ki vsebujejo iskalno vrednost; B2: F9 obseg podatkov številskih vrednosti, ki jih želite sešteti.


Poiščite in seštejte vse ujemajoče se vrednosti v več stolpcih:

Če želite Vlookup in sešteti vse ujemajoče se vrednosti v več stolpcih, uporabite naslednjo formulo:

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

Opombe: V zgornji formuli: H2 je iskalna vrednost, ki jo iščete; B1: F1 so glave stolpcev, ki vsebujejo iskalno vrednost; B2: F9 obseg podatkov številskih vrednosti, ki jih želite sešteti.


Poiščite in seštejte prve ali vse ujemajoče se vrednosti z zmogljivo funkcijo

Morda si zgornjih formul težko zapomnite, v tem primeru vam priporočam priročno funkcijo - Iskanje in vsota of Kutools za Excel, s to funkcijo lahko rezultat dosežete čim enostavneje.    Kliknite za prenos Kutools za Excel zdaj!


Poiščite in seštejte vse ujemajoče se vrednosti v vrsticah in stolpcih

Če želite vrednosti strniti, ko se morate na primer ujemati tako v stolpcu kot v vrstici, če želite na primer dobiti skupno vrednost izdelka Pulover v mesecu marcu, kot je prikazano spodaj.

Prosimo, uporabite naslednjo formulo v celico in pritisnite tipko Enter, da dobite rezultat, glejte posnetek zaslona:

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

Opombe: V zgornji formuli: B2: F9 je obseg podatkov številskih vrednosti, ki jih želite sešteti; B1: F1 is glave stolpcev vsebujejo iskalno vrednost, na podlagi katere želite sešteti; I2 je vrednost iskanja v iskanih glavah stolpcev; A2: A9 ali glave vrstic vsebujejo iskalno vrednost, na podlagi katere želite sešteti; H2 je iskalna vrednost v iskanih glavah vrstic.


8. Vlookup za združitev dveh tabel na podlagi enega ali več stolpcev s ključi

Pri vsakodnevnem delu boste pri analizi podatkov morda morali zbrati vse potrebne informacije v eno tabelo na podlagi enega ali več ključnih stolpcev. Za rešitev tega opravila vam lahko storitev Vlookup tudi stori uslugo.

Vlookup za združitev dveh tabel na podlagi enega stolpca ključev

Na primer, imate dve tabeli, prva tabela vsebuje podatke o izdelkih in imenih, druga tabela pa izdelke in naročila. Zdaj želite združiti ti dve tabeli tako, da v eno tabelo uvrstite skupni stolpec izdelkov.

Formula 1: Uporaba funkcije VLOOKUP

Če želite združiti obe tabeli v eno na podlagi ključnega stolpca, uporabite naslednjo formulo v prazno celico, kjer želite dobiti rezultat, in nato povlecite ročico za polnjenje navzdol do celic, za katere želite uporabiti to formulo, pridobite združeno tabelo s stolpcem vrstnega reda, ki se pridruži podatkom prve tabele na podlagi podatkov ključnih stolpcev.

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

Opombe: V zgornji formuli, A2 je vrednost, ki jo iščete, E2: F8 je tabela za iskanje, številka 2 je številka stolpca v tabeli, iz katere je treba pridobiti vrednost.

Formula 2: Uporaba funkcij INDEX in MATCH

Če vaši skupni podatki na desni strani in vrnjeni podatki v levem stolpcu v drugi tabeli, za združitev stolpca vrstnega reda, funkcija Vlookup ne more opraviti dela. Če želite iskati od desne proti levi, lahko s funkcijama INDEX in MATCH nadomestite funkcijo Vlookup.

Prosimo, kopirajte ali vnesite spodnjo formulo v prazno celico, nato kopirajte formulo navzdol v stolpec in stolpec vrstnega reda je pridružen prvi tabeli, glejte posnetek zaslona:

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

Opombe: V zgornji formuli, A2 je iskalna vrednost, ki jo iščete, E2: E8 je obseg podatkov, ki jih želite vrniti, F2: F8 je obseg iskanja, ki vsebuje iskalno vrednost.


Vlookup za združitev dveh tabel na podlagi več stolpcev ključev

Če imata dve tabeli, ki ju želite združiti, več stolpcev s ključi, vam lahko za združitev tabel na podlagi teh pogostih stolpcev pomagata funkciji INDEX in MATCH.

Splošna formula za združitev dveh tabel, ki temelji na več stolpcih ključev, je:

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

1. Prosimo, uporabite spodnjo formulo v prazno celico, kamor želite dati rezultat, in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvo ujemajočo se vrednost, glejte posnetek zaslona:

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

Opombe: V zgornji formuli je prikazano, kaj sklice na celice predstavljajo spodaj:

2Nato izberite prvo celico formule in povlecite ročico za polnjenje, da kopirate to formulo v druge celice, kot potrebujete:

nasveti: V Excelu 2016 in novejših različicah lahko uporabite tudi Power Query funkcija za združitev dveh ali več tabel v eno na podlagi ključnih stolpcev. Prosimo, kliknite za podrobnosti korak za korakom.

9. Vlookup ujemajoče se vrednosti na več delovnih listih

Ste že kdaj poskusili preveriti vrednosti Vlookupa na več delovnih listih? Recimo, da imam naslednje tri delovne liste z obsegom podatkov, zdaj pa želim dobiti del ustreznih vrednosti na podlagi meril iz teh treh delovnih listov, da dobim rezultat, kot je prikazano na spodnji sliki zaslona. V tem primeru je Vrednosti iskanja v več delovnih listih vadnica vam lahko korak za korakom naredi uslugo.


Ustrezne vrednosti VLOOKUP ohranijo oblikovanje celic

1. Vlookup za oblikovanje celic (barva celice, barva pisave) skupaj z vrednostjo iskanja

Kot vsi vemo, nam lahko običajna funkcija Vlookup pomaga vrniti ujemajočo se vrednost iz drugega obsega podatkov, včasih pa boste morda želeli vrniti ustrezno vrednost skupaj z oblikovanjem celice, kot so barva polnila, barva pisave, slog pisave kot je prikazano na sliki spodaj. Ta razdelek govori o tem, kako pridobiti oblikovanje celice z vrnjeno vrednostjo v Excelu.

Za iskanje in vrnitev ustrezne vrednosti skupaj s formatiranjem celic sledite naslednjim korakom:

1. Na delovnem listu so podatki, ki jih želite izbrisati, z desno miškino tipko kliknite jeziček lista in izberite Ogled kode iz kontekstnega menija. Oglejte si posnetek zaslona:

2. V odprti Microsoft Visual Basic za aplikacije okno, kopirajte spodnjo kodo VBA v okno Code.

Koda VBA 1: Vlookup, da dobite oblikovanje celic skupaj z vrednostjo iskanja

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. Še vedno v Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduliin nato v okno modula kopirajte spodnjo kodo VBA 2.

Koda VBA 2: Vlookup, da dobite oblikovanje celic skupaj z vrednostjo iskanja

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. Ko vstavite zgornje kode, kliknite Orodja > Reference v Microsoft Visual Basic za aplikacije okno. Nato preverite Microsoft Script Runtime potrditveno polje v Reference - VBAProject pogovorno okno. Oglejte si posnetke zaslona:

5. Nato kliknite OK če želite zapreti pogovorno okno in nato shraniti in zapreti okno s kodo, se zdaj vrnite na delovni list in uporabite to formulo: =LookupKeepFormat(E2,$A$1:$C$10,3) v prazno celico, kjer želite izpisati rezultat, in pritisnite tipko Enter. Oglejte si posnetek zaslona:

Opombe: V zgornji formuli, E2 je vrednost, ki jo boste poiskali, A1: C10 je obseg tabele in število 3 je številka stolpca tabele, za katero želite vrniti ujemajočo se vrednost.

6. Nato izberite prvo celico z rezultati in povlecite ročico za polnjenje navzdol, da dobite vse rezultate skupaj z njihovim oblikovanjem. Oglejte si posnetek zaslona.


2. Oblika datuma naj ostane iz vrnjene vrednosti Vlookup

Običajno se pri uporabi funkcije Vloook za iskanje in vrnitev vrednosti ujemajočega se formata datuma prikaže neka oblika števil, kot je prikazano spodaj. Če želite vrniti obliko zapisa datuma iz vrnjenega rezultata, morate funkciji TEXT priložiti funkcijo Vlookup.

Prosimo, uporabite spodnjo formulo v prazno celico in nato povlecite ročico za polnjenje, da kopirate to formulo v druge celice, in vsi ujemajoči se datumi so vrnjeni, kot je prikazano spodaj na sliki zaslona:

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

Opombe: V zgornji formuli, E2 je vrednost videza, A2: C9 je obseg iskanja, število 3 je številka stolpca, za katero želite vrniti vrednost, mm / dd / llll je oblika datuma, ki jo želite obdržati.


3. Iskanje in vrnitev ujemajoče se vrednosti s komentarjem celice

Ste že kdaj poskusili Vlookup vrniti ne le ujemajoče se podatke celice, temveč tudi komentar celice v Excelu, kot je prikazano na sliki spodaj? Če želite rešiti to nalogo, vam spodnja funkcija, ki jo določi uporabnik, lahko naredi uslugo.

1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduli, nato v okno modula kopirajte in prilepite naslednjo kodo.

Koda VBA: Vlookup in vrnitev ujemajoče se vrednosti s komentarjem celice:

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. Nato shranite in zaprite okno s kodo, vnesite to formulo: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) v prazno celico, da poiščete rezultat, in nato povlecite ročico za polnjenje, da kopirate to formulo v druge celice, zdaj pa se ujemajoče se vrednosti in komentarji vrnejo hkrati, glejte posnetek zaslona:

Opombe: V zgornji formuli, D2 je vrednost iskanja, ki ji želite vrniti ustrezno vrednost, A2: B9 je podatkovna tabela, ki jo želite uporabiti, številka 2 je številka stolpca, ki vsebuje ujemajočo se vrednost, ki jo želite vrniti.


4. Ukvarjajte se z besedilom in realnimi številkami v Vlookupu

Na primer, imam vrsto podatkov, ID številka v izvirni tabeli je v obliki številke, v iskalni celici, ki je shranjena kot besedilo, se pri uporabi običajne funkcije Vlookup prikaže rezultat napake # N / A, kot je prikazano spodaj na sliki zaslona prikazano. Kako bi lahko v tem primeru dobili pravilne podatke, če imata izbirna številka in izvirna številka v tabeli različno obliko zapisa podatkov?

Če želite obravnavati besedilo in realne številke v funkciji Vlookup, uporabite naslednjo formulo v prazno celico in nato povlecite ročico za polnjenje navzdol, da kopirate to formulo, in dobili boste pravilne rezultate, kot je prikazano na spodnji sliki zaslona:

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

Opombe:

  • 1. V zgornji formuli: D2 je vrednost iskanja, ki ji želite vrniti ustrezno vrednost, A2: B8 je podatkovna tabela, ki jo želite uporabiti, številka 2 je številka stolpca, ki vsebuje ujemajočo se vrednost, ki jo želite vrniti.
  • 2. Ta formula dobro deluje tudi, če niste prepričani, kje imate številke in kje besedilo.

Prenesite vzorčne datoteke VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
zavihek kte 201905
  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50% in vsak dan zmanjša na stotine klikov z miško!
dno pisarniške mize
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.