Fonksyon VLOOKUP nan Excel - Gid pou debutan: sentaks ak egzanp

Jodi a nou kòmanse yon seri atik ki dekri youn nan karakteristik ki pi itil nan Excel - VPR (VCHACHUP). Fonksyon sa a, an menm tan an, se youn nan pi konplèks ak pi piti konprann.

Nan leson patikilye sa a sou VPR Mwen pral eseye mete de baz yo kòm tou senpleman posib yo nan lòd yo fè pwosesis aprantisaj la klè ke posib pou itilizatè ki pa gen eksperyans. Anplis de sa, nou pral etidye plizyè egzanp ak fòmil Excel ki pral demontre ka itilizasyon ki pi komen pou fonksyon an VPR.

  • Deskripsyon jeneral ak sentaks
  • Egzanp VLOOKUP
    • Ki jan yo itilize VLOOKUP pou fè rechèch nan yon lòt fèy Excel
    • Chèche nan yon lòt liv travay ak VLOOKUP
    • Ki jan yo sèvi ak yon seri non oswa yon tab nan fòmil ak VLOOKUP
    • Sèvi ak Wildcards nan fòmil VLOOKUP
  • Korespondans egzak oswa apwoksimatif nan fonksyon VLOOKUP
  • VLOOKUP nan Excel - ou bezwen sonje sa a!

Fonksyon VLOOKUP nan Excel - deskripsyon jeneral ak sentaks

Se konsa, ki sa li ye VPR? Oke, anvan tout bagay, li se yon fonksyon Excel. Kisa li fè? Li gade valè ou presize epi li retounen valè ki koresponn lan nan lòt kolòn nan. Teknikman pale, VPR gade valè nan premye kolòn nan seri yo bay la epi li retounen rezilta a soti nan yon lòt kolòn nan menm ranje a.

Nan aplikasyon ki pi komen, fonksyon an VPR rechèch baz done a pou yon idantifyan inik bay epi ekstrè kèk enfòmasyon ki gen rapò ak li nan baz done a.

Premye lèt nan non fonksyon an VPR (VLOOKUP) vle di Вvètikal (Vvètikal). Pa li ou ka distenge VPR soti nan GPR (HLOOKUP), ki chèche yon valè nan ranje anlè yon seri - Гorizontal (Horizontal).

fonksyon VPR disponib nan Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, ak Excel 2000.

Sentaks nan fonksyon VLOOKUP

fonksyon VPR (VLOOKUP) gen sentaks sa a:

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

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Kòm ou ka wè, yon fonksyon VPR nan Microsoft Excel gen 4 opsyon (oswa agiman). Twa premye yo obligatwa, dènye a se opsyonèl.

  • valè_chèch (lookup_value) - Valè pou chèche. Sa a kapab yon valè (nimewo, dat, tèks) oswa yon referans selil (ki gen valè rechèch la), oswa yon valè retounen pa kèk lòt fonksyon Excel. Pou egzanp, fòmil sa a pral chèche valè a 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

Si valè rechèch la pi piti pase valè ki pi piti a nan premye kolòn nan seri a, fonksyon an VPR pral rapòte yon erè #AT (#N/A).

  • table_array (tablo) - de oswa plis kolòn done. Sonje byen, fonksyon an VPR toujou chèche valè nan premye kolòn nan seri yo bay nan agiman an table_array (tab). Ranje a ka wè divès kalite done, tankou tèks, dat, nimewo, boolean. Fonksyon an pa sansib, sa vle di karaktè majiskil ak miniskil yo konsidere menm bagay la. Se konsa, fòmil nou an ap chèche pou valè a 40 nan selil ki soti nan A2 yo A15, paske A se premye kolòn seri A2:B15 yo bay nan agiman an table_array (tab):

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • kol_index_num (column_number) se nimewo kolòn nan seri yo bay la ki pral retounen valè nan ranje yo jwenn. Kolòn ki pi goch la nan seri yo bay la se 1, dezyèm kolòn nan se 2, twazyèm kolòn nan se 3 ak sou sa. Koulye a, ou ka li tout fòmil la:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    Fòmil kap chèche valè 40 nan seri a A2: A15 epi li retounen valè ki koresponn lan nan kolòn B (paske B se dezyèm kolòn nan seri A2:B15).

Si valè agiman an kol_index_num (column_number) mwens pase 1lè sa a, VPR pral rapòte yon erè #VALUE! (#VALÈ!). Men, si li se pi plis pase kantite kolòn nan seri a table_array (tablo), fonksyon an ap retounen yon erè #REF! (#LINK!).

  • range_lookup (range_lookup) - detèmine sa pou chèche pou:
    • matche egzak, agiman dwe egal FO (FO);
    • matche ak apwoksimatif, agiman egal VRE KÒD (VRÈ) oswa pa espesifye ditou.

    Paramèt sa a se opsyonèl, men trè enpòtan. Pita nan leson patikilye sa a sou VPR Mwen pral montre w kèk egzanp ki eksplike kijan pou ekri fòmil pou jwenn alimèt egzak ak apwoksimatif.

Egzanp VLOOKUP

Mwen espere fonksyon an VPR vin yon ti kras pi klè pou ou. Koulye a, kite a gade nan kèk ka itilize VPR nan fòmil ak done reyèl.

Ki jan yo itilize VLOOKUP pou fè rechèch nan yon lòt fèy Excel

Nan pratik, fòmil ak yon fonksyon VPR yo raman itilize pou chèche done sou menm fichye travay la. Pi souvan pase pa, ou pral gade anlèvman epi rekipere valè korespondan soti nan yon lòt fèy.

Yo nan lòd yo sèvi ak VPR, rechèch nan yon lòt fèy Microsoft Excel, Ou dwe nan agiman an table_array (tablo) presize non fèy la ak yon mak esklamasyon ki te swiv pa yon seri selil. Pou egzanp, fòmil sa a montre ke seri a A2: B15 se sou yon fèy ki rele Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Natirèlman, non fèy la pa oblije antre manyèlman. Jis kòmanse tape fòmil la, ak lè li rive agiman an table_array (tablo), chanje nan fèy la vle epi chwazi seri a vle nan selil ak sourit la.

Fòmil yo montre nan ekran ki anba a ap chèche tèks "Pwodwi 1" nan kolòn A (se 1ye kolòn seri A2:B9) sou yon fèy travay. Pri.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Tanpri sonje ke lè w ap chèche yon valè tèks, ou dwe mete l nan guillemets (""), jan sa konn fè nan fòmil Excel yo.

Pou agiman table_array (tablo) li se dezirab toujou sèvi ak referans absoli (ak siy $). Nan ka sa a, seri rechèch la ap rete san okenn chanjman lè w kopye fòmil la nan lòt selil yo.

Chèche nan yon lòt liv travay ak VLOOKUP

Pou fonksyone VPR te travay ant de liv travay Excel, ou bezwen presize non liv travay la nan parantèz kare anvan non fèy la.

Pou egzanp, anba a se yon fòmil ki gade pou valè a 40 sou fèy la Sheet2 nan liv la Numbers.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Isit la se fason ki pi fasil yo kreye yon fòmil nan Excel ak VPRki gen lyen ak yon lòt liv travay:

  1. Louvri tou de liv yo. Sa a pa obligatwa, men li pi fasil pou kreye yon fòmil konsa. Ou pa vle antre non liv travay la manyèlman, pa vre? Anplis de sa, li pral pwoteje ou kont erè tipopo aksidan.
  2. Kòmanse tape yon fonksyon VPRak lè li rive agiman an table_array (tablo), chanje nan yon lòt liv travay epi chwazi seri rechèch ki nesesè nan li.

Ekran ki anba a montre fòmil la ak rechèch la mete nan yon seri nan liv travay la PriceList.xlsx sou fèy la Pri.

fonksyon VPR pral travay menm lè ou fèmen liv travay la rechèch la ak chemen an konplè nan fichye liv travay la parèt nan ba fòmil la, jan yo montre anba a:

Si non liv travay la oswa fèy la gen espas, lè sa a li dwe fèmen nan apòt:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Ki jan yo sèvi ak yon seri non oswa yon tab nan fòmil ak VLOOKUP

Si w gen plan pou itilize menm seri rechèch la nan plizyè fonksyon VPR, ou ka kreye yon seri non epi antre non li nan fòmil la kòm yon agiman table_array (tab).

Pou kreye yon seri non, tou senpleman chwazi selil yo epi antre yon non apwopriye nan jaden an Prénoms, sou bò gòch ba fòmil la.

Koulye a, ou ka ekri fòmil sa a pou jwenn pri a nan yon pwodwi Pwodwi 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Pifò non seri travay pou tout liv Excel la, kidonk pa gen okenn nesesite pou presize non fèy pou agiman an. table_array (tablo), menm si fòmil la ak seri rechèch la sou fèy travay diferan. Si yo nan liv travay diferan, Lè sa a, anvan non an nan seri a ou bezwen presize non an nan liv travay la, pou egzanp, tankou sa a:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Se konsa, fòmil la sanble pi klè, dakò? Epitou, lè l sèvi avèk ranje non se yon bon altènatif nan referans absoli paske ranje yo nonmen pa chanje lè w kopye fòmil la nan lòt selil yo. Sa vle di ke ou ka asire w ke seri rechèch la nan fòmil la ap toujou rete kòrèk.

Si ou konvèti yon seri selil nan yon fèy kalkil Excel konplè lè l sèvi avèk lòd la Tablo (Tablo) tab ensèsyon (Insert), Lè sa a, lè ou chwazi yon seri ak sourit la, Microsoft Excel pral otomatikman ajoute non kolòn yo (oswa non tab la si ou chwazi tab la tout antye) nan fòmil la.

Fòmil la fini pral gade yon bagay tankou sa a:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Oswa petèt menm jan sa a:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Lè w ap itilize chenn nonmen, lyen yo pral montre menm selil yo kèlkeswa kote w kopye fonksyon an VPR nan liv travay la.

Sèvi ak Wildcards nan fòmil VLOOKUP

Menm jan ak anpil lòt fonksyon, VPR Ou ka itilize karaktè joker sa yo:

  • Mak kesyon (?) - ranplase nenpòt karaktè sèl.
  • Asteris (*) – ranplase nenpòt sekans karaktè.

Sèvi ak Wildcards nan Fonksyon yo VPR ka itil nan anpil ka, pou egzanp:

  • Lè ou pa sonje egzakteman tèks la ou bezwen jwenn.
  • Lè ou vle jwenn kèk mo ki se yon pati nan kontni an nan yon selil. Konnen sa VPR rechèch pa sa ki nan selil la kòm yon antye, kòm si opsyon a pèmèt Koresponn ak kontni selil tout antye (Tout selil) nan rechèch estanda Excel la.
  • Lè yon selil gen espas siplemantè nan kòmansman oswa nan fen kontni an. Nan yon sitiyasyon konsa, ou ka etajè sèvo ou pou yon tan long, ap eseye konnen poukisa fòmil la pa travay.

Egzanp 1: Chèche tèks ki kòmanse oswa ki fini ak sèten karaktè

Ann di ou vle fè rechèch pou yon kliyan espesifik nan baz done a montre anba a. Ou pa sonje non li, men ou konnen ke li kòmanse ak "ack". Isit la se yon fòmil ki pral fè travay la jis byen:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Kounye a ke ou sèten ou te jwenn non ki kòrèk la, ou ka itilize menm fòmil la pou jwenn kantite lajan kliyan sa a peye. Pou fè sa, jis chanje twazyèm agiman fonksyon an VPR nan nimewo kolòn ou vle a. Nan ka nou an, sa a se kolòn C (3yèm nan seri a):

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

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Men kèk egzanp plis ak joker:

~ Jwenn yon non ki fini an "moun":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Jwenn yon non ki kòmanse ak "anons" epi ki fini ak "pitit gason":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Nou jwenn premye non an nan lis la, ki gen 5 karaktè:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Pou fonksyone VPR ak wildcards te travay kòrèkteman, kòm katriyèm agiman ou ta dwe toujou itilize FO (FO). Si seri rechèch la gen plis pase yon valè ki koresponn ak tèm rechèch yo ak wildcards, yo pral retounen premye valè yo jwenn.

Egzanp 2: Konbine joker ak referans selil nan fòmil VLOOKUP

Koulye a, ann gade nan yon egzanp yon ti kras pi konplèks sou fason pou fè rechèch lè l sèvi avèk fonksyon an VPR pa valè nan yon selil. Imajine ke kolòn A se yon lis kle lisans, ak kolòn B se yon lis non ki posede yon lisans. Anplis de sa, ou gen yon pati (plizyè karaktè) nan kèk kalite kle lisans nan selil C1, epi ou vle jwenn non an nan mèt kay la.

Sa a ka fè lè l sèvi avèk fòmil sa a:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Fòmil sa a gade valè ki soti nan selil C1 nan seri yo bay la epi li retounen valè ki koresponn lan nan kolòn B. Remake byen ke nan premye agiman an, nou itilize yon karaktè ampersand (&) anvan ak apre referans selil la konekte chèn tèks la.

Kòm ou ka wè nan figi ki anba a, fonksyon an VPR retounen "Jeremy Hill" paske kle lisans li a gen sekans karaktè ki soti nan selil C1.

Remake byen ke agiman an table_array (tablo) nan ekran ki anwo a gen non tab la (Table7) olye pou yo espesifye yon seri selil. Sa a se sa nou te fè nan egzanp anvan an.

Korespondans egzak oswa apwoksimatif nan fonksyon VLOOKUP

Epi finalman, ann pran yon gade pi pre nan dènye agiman an ki espesifye pou fonksyon an VPR - range_lookup (interval_view). Kòm mansyone nan kòmansman leson an, agiman sa a trè enpòtan. Ou ka jwenn rezilta konplètman diferan nan menm fòmil la ak valè li yo VRE KÒD (VRÈ) oswa FO (FO).

Premyèman, ann chèche konnen ki sa Microsoft Excel vle di pa alimèt egzak ak apwoksimatif.

  • Si agiman an range_lookup (range_lookup) egal a FO (FALSE), fòmil la ap chèche yon match egzak, sa vle di egzakteman menm valè ke yo bay nan agiman an valè_chèch (lookup_value). Si nan premye kolòn nan seri table_array (tablo) rankontre de oswa plis valè ki matche ak agiman an valè_chèch (search_value), Lè sa a, yo pral chwazi premye a. Si yo pa jwenn alimèt, fonksyon an pral rapòte yon erè #AT (#N/A). Pou egzanp, fòmil sa a pral rapòte yon erè #AT (#N/A) si pa gen okenn valè nan seri A2:A15 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • Si agiman an range_lookup (range_lookup) egal a VRE KÒD (VRÈ), fòmil la ap chèche yon match apwoksimatif. Plis jisteman, premye fonksyon an VPR chèche yon match egzak, epi si yo pa jwenn okenn, chwazi yon apwoksimatif. Yon match apwoksimatif se pi gwo valè ki pa depase valè ki espesifye nan agiman an. valè_chèch (lookup_value).

Si agiman an range_lookup (range_lookup) egal a VRE KÒD (VRÈ) oswa pa espesifye, Lè sa a, valè yo nan premye kolòn nan seri a ta dwe klase nan lòd monte, se sa ki, soti nan pi piti a pi gwo. Sinon, fonksyon an VPR ka retounen yon rezilta inègza.

Pou pi byen konprann enpòtans chwa VRE KÒD (VERITE) oswa FO (FALSE), ann gade kèk fòmil plis ak fonksyon an VPR epi gade rezilta yo.

Egzanp 1: Jwenn yon Korespondans egzak ak VLOOKUP

Kòm ou sonje, pou chèche yon match egzak, katriyèm agiman fonksyon an VPR ta dwe gen pwoblèm FO (FO).

Ann tounen sou tab la depi premye egzanp lan epi chèche konnen ki bèt ki ka deplase ak yon vitès 50 mil pa èdtan. Mwen kwè ke fòmil sa a pa pral lakòz ou nenpòt difikilte:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Remake byen ke seri rechèch nou an (kolòn A) gen de valè 50 - nan selil yo A5 и A6. Fòmil retounen valè nan selil la B5. Poukisa? Paske lè w ap chèche yon match egzak, fonksyon an VPR sèvi ak premye valè yo jwenn ki matche ak youn yo ap chèche a.

Egzanp 2: Sèvi ak VLOOKUP pou Jwenn yon Korespondans Apwoksimatif

Lè w sèvi ak fonksyon an VPR pou chèche yon match apwoksimatif, sa vle di lè agiman an range_lookup (range_lookup) egal a VRE KÒD (VRÈ) oswa omisyon, premye bagay ou bezwen fè se klase seri a pa premye kolòn nan lòd monte.

Sa a trè enpòtan paske fonksyon an VPR retounen pi gwo valè apre sa yo bay la, epi rechèch la sispann. Si ou neglije klasman kòrèk la, ou pral fini ak rezilta trè etranj oswa yon mesaj erè. #AT (#N/A).

Koulye a, ou ka itilize youn nan fòmil sa yo:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Kòm ou ka wè, mwen vle chèche konnen kilès nan bèt yo ki gen vitès ki pi pre 69 mil pa èdtan. Ak isit la se rezilta fonksyon an tounen vin jwenn mwen VPR:

Kòm ou ka wè, fòmil la te retounen yon rezilta Antelope (Antilope), ki gen vitès 61 mil pa èdtan, byenke lis la gen ladan tou cheetah (Cheetah) ki kouri nan vitès 70 mil pa èdtan, ak 70 pi pre 69 pase 61, pa vre? Poukisa sa ap pase? Paske fonksyon an VPR lè w ap chèche yon match apwoksimatif, li retounen pi gwo valè ki pa pi gran pase sa y ap chèche a.

Mwen espere ke egzanp sa yo bay kèk limyè sou travay ak fonksyon an VPR nan Excel, epi ou pa gade l ankò kòm yon etranje. Koulye a, li pa fè mal pou yon ti tan repete pwen kle yo nan materyèl la nou te etidye yo nan lòd yo pi byen ranje li nan memwa.

VLOOKUP nan Excel - ou bezwen sonje sa a!

  1. fonksyon VPR Excel pa ka gade agoch. Li toujou gade pou valè a nan kolòn ki pi goch la nan seri a bay nan agiman an table_array (tab).
  2. Nan fonksyon VPR tout valè yo pa sansib, sa vle di lèt ti ak gwo yo ekivalan.
  3. Si valè w ap chèche a pi piti pase valè minimòm nan premye kolòn nan seri a, fonksyon an. VPR pral rapòte yon erè #AT (#N/A).
  4. Si 3e agiman kol_index_num (column_number) mwens pase 1fonksyon VPR pral rapòte yon erè #VALUE! (#VALÈ!). Si li pi gran pase kantite kolòn nan seri a table_array (tablo), fonksyon an pral rapòte yon erè #REF! (#LINK!).
  5. Sèvi ak referans selil absoli nan agiman table_array (tablo) pou ranje rechèch kòrèk la konsève lè w ap kopye fòmil la. Eseye itilize ranje yo rele oswa tab nan Excel kòm yon altènatif.
  6. Lè w ap fè yon rechèch matche ak apwoksimatif, sonje premye kolòn nan seri w ap chèche a dwe klase nan lòd monte.
  7. Finalman, sonje enpòtans katriyèm agiman an. Sèvi ak valè VRE KÒD (VERITE) oswa FO (FO) fè espre epi ou pral debarase m de anpil tèt fè mal.

Nan atik sa yo nan leson patikilye fonksyon nou an VPR nan Excel, nou pral aprann egzanp ki pi avanse, tankou fè plizyè kalkil lè l sèvi avèk VPR, ekstrè valè nan plizyè kolòn, ak plis ankò. Mèsi paske w li leson patikilye sa a e mwen espere wè w ankò semèn pwochèn!

Kite yon Reply