Funksioni VLOOKUP në Excel – Udhëzues për fillestarët: Sintaksa dhe shembuj

Sot po fillojmë një seri artikujsh që përshkruajnë një nga veçoritë më të dobishme të Excel - VPR (VLOOKUP). Ky funksion, në të njëjtën kohë, është një nga më komplekset dhe më pak të kuptuarit.

Në këtë tutorial mbi VPR Do të përpiqem të parashtroj bazat sa më thjesht që të jetë e mundur, në mënyrë që procesin e të mësuarit ta bëj sa më të qartë që të jetë e mundur për përdoruesit e papërvojë. Përveç kësaj, ne do të studiojmë disa shembuj me formulat Excel që do të demonstrojnë rastet më të zakonshme të përdorimit të funksionit VPR.

  • Përshkrimi i përgjithshëm dhe sintaksa
  • Shembuj VLOOKUP
    • Si të përdorni VLOOKUP për të kërkuar në një fletë tjetër Excel
    • Kërkoni në një libër tjetër pune me VLOOKUP
    • Si të përdorni një gamë ose tabelë të emërtuar në formula me VLOOKUP
    • Përdorimi i Wildcards në Formulat VLOOKUP
  • Përputhja e saktë ose e përafërt në funksionin VLOOKUP
  • VLOOKUP në Excel - duhet ta mbani mend këtë!

Funksioni VLOOKUP në Excel – përshkrim i përgjithshëm dhe sintaksë

Pra, çfarë është ajo VPR? Epo, para së gjithash, është një funksion Excel. Çfarë bën ajo? Kërkon vlerën që specifikoni dhe kthen vlerën përkatëse nga kolona tjetër. Duke folur teknikisht, VPR shikon vlerën në kolonën e parë të diapazonit të dhënë dhe kthen rezultatin nga një kolonë tjetër në të njëjtin rresht.

Në aplikacionin më të zakonshëm, funksioni VPR kërkon në bazën e të dhënave për një identifikues të caktuar unik dhe nxjerr disa informacione që lidhen me të nga baza e të dhënave.

Shkronja e parë në emrin e funksionit VPR (VLOOKUP) do të thotë Вvertikale (Vvertikale). Me të mund të dallosh VPR nga GPR (HLOOKUP), i cili kërkon një vlerë në rreshtin e sipërm të një diapazoni − Гhorizontale (Hhorizontalisht).

funksion VPR disponohet në Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP dhe Excel 2000.

Sintaksa e funksionit VLOOKUP

funksion VPR (VLOOKUP) ka sintaksën e mëposhtme:

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

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

Siç mund ta shihni, një funksion VPR në Microsoft Excel ka 4 opsione (ose argumente). Tre të parat janë të detyrueshme, e fundit është fakultative.

  • lookup_value (lookup_value) – Vlera që duhet kërkuar. Kjo mund të jetë një vlerë (numër, datë, tekst) ose një referencë qelize (që përmban vlerën e kërkimit), ose një vlerë e kthyer nga ndonjë funksion tjetër Excel. Për shembull, kjo formulë do të kërkojë vlerën 40:

    =VLOOKUP(40,A2:B15,2)

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

Nëse vlera e kërkimit është më e vogël se vlera më e vogël në kolonën e parë të diapazonit që po kërkohet, funksioni VPR do të raportojë një gabim #AT (# N/A).

  • table_array (tabela) - dy ose më shumë kolona të dhënash. Mbani mend, funksionin VPR gjithmonë kërkon vlerën në kolonën e parë të diapazonit të dhënë në argument table_array (tabela). Gama e shikueshme mund të përmbajë të dhëna të ndryshme, të tilla si tekst, data, numra, booleans. Funksioni është i pandjeshëm ndaj shkronjave të mëdha, që do të thotë se karakteret e mëdha dhe të vogla konsiderohen të njëjta. Pra, formula jonë do të kërkojë vlerën 40 në qelizat nga A2 në A15, sepse A është kolona e parë e diapazonit A2:B15 të dhënë në argument table_array (tabela):

    =VLOOKUP(40,A2:B15,2)

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

  • col_index_num (numri_kolona) është numri i kolonës në diapazonin e dhënë nga i cili do të kthehet vlera në rreshtin e gjetur. Kolona më e majtë në diapazonin e dhënë është 1, kolona e dytë është 2, kolona e tretë është 3 e kështu me radhë. Tani mund të lexoni të gjithë formulën:

    =VLOOKUP(40,A2:B15,2)

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

    Formula në kërkim të vlerës 40 në varg A2: A15 dhe kthen vlerën përkatëse nga kolona B (sepse B është kolona e dytë në rangun A2:B15).

Nëse vlera e argumentit col_index_num (numri_kolona) më pak se 1pastaj VPR do të raportojë një gabim #VALUE! (#VLERË!). Dhe nëse është më shumë se numri i kolonave në interval table_array (tabela), funksioni do të kthejë një gabim # REF! (#LINK!).

  • kërkimi_rangut (range_lookup) – përcakton se çfarë të kërkoni:
    • përputhje e saktë, argumenti duhet të jetë i barabartë I RREMË (I RREMË);
    • përputhje e përafërt, argumenti është i barabartë KODI I VËRTETË (E VËRTETË) ose nuk specifikohet fare.

    Ky parametër është opsional, por shumë i rëndësishëm. Më vonë në këtë tutorial në VPR Do t'ju tregoj disa shembuj që shpjegojnë se si të shkruani formula për gjetjen e përputhjeve të sakta dhe të përafërta.

Shembuj VLOOKUP

Shpresoj funksionin VPR bëhuni pak më i qartë për ju. Tani le të shohim disa raste përdorimi VPR në formula me të dhëna reale.

Si të përdorni VLOOKUP për të kërkuar në një fletë tjetër Excel

Në praktikë, formula me një funksion VPR përdoren rrallë për të kërkuar të dhëna në të njëjtën fletë pune. Më shpesh sesa jo, do të kërkoni dhe do të merrni vlerat përkatëse nga një fletë tjetër.

Në mënyrë që të përdorni VPR, kërkoni në një fletë tjetër të Microsoft Excel, Ju duhet në argument table_array (tabela) specifikoni emrin e fletës me një pikëçuditëse të ndjekur nga një sërë qelizash. Për shembull, formula e mëposhtme tregon se diapazoni A2: B15 është në një fletë me emrin Sheet2.

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

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

Natyrisht, emri i fletës nuk duhet të futet manualisht. Thjesht filloni të shkruani formulën dhe kur bëhet fjalë për argumentin table_array (tabela), kaloni në fletën e dëshiruar dhe zgjidhni gamën e dëshiruar të qelizave me miun.

Formula e treguar në pamjen e mëposhtme të ekranit kërkon tekstin "Produkti 1" në kolonën A (është kolona e parë e gamës A1:B2) në një fletë pune Çmimet.

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

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

Ju lutemi mbani mend se kur kërkoni për një vlerë teksti, duhet ta vendosni atë në thonjëza (“”), siç bëhet zakonisht në formulat Excel.

Për argument table_array (tabela) është e dëshirueshme që gjithmonë të përdoren referenca absolute (me shenjën $). Në këtë rast, diapazoni i kërkimit do të mbetet i pandryshuar kur kopjoni formulën në qeliza të tjera.

Kërkoni në një libër tjetër pune me VLOOKUP

Për të funksionuar VPR punuar ndërmjet dy librave të punës Excel, duhet të specifikoni emrin e librit të punës në kllapa katrore përpara emrit të fletës.

Për shembull, më poshtë është një formulë që kërkon vlerën 40 në fletë Sheet2 në libër Numrat.xlsx:

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

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

Këtu është mënyra më e lehtë për të krijuar një formulë në Excel me VPRe cila lidhet me një libër tjetër pune:

  1. Hapni të dy librat. Kjo nuk kërkohet, por është më e lehtë të krijosh një formulë në këtë mënyrë. Nuk dëshironi të futni manualisht emrin e librit të punës, apo jo? Përveç kësaj, do t'ju mbrojë nga gabimet e rastësishme të shkrimit.
  2. Filloni të shkruani një funksion VPRdhe kur është fjala për argumentin table_array (tabela), kaloni në një libër tjetër pune dhe zgjidhni gamën e kërkuar të kërkimit në të.

Pamja e ekranit më poshtë tregon formulën me kërkimin e vendosur në një interval në librin e punës Lista e Çmimeve.xlsx në fletë Çmimet.

funksion VPR do të funksionojë edhe kur mbyllni librin e kërkuar të punës dhe shtegu i plotë i skedarit të librit të punës shfaqet në shiritin e formulave, siç tregohet më poshtë:

Nëse emri i librit ose fletës së punës përmban hapësira, atëherë ai duhet të mbyllet me apostrofa:

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

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

Si të përdorni një gamë ose tabelë të emërtuar në formula me VLOOKUP

Nëse planifikoni të përdorni të njëjtin gamë kërkimi në funksione të shumta VPR, mund të krijoni një varg të emërtuar dhe të futni emrin e tij në formulë si argument table_array (tabela).

Për të krijuar një varg me emër, thjesht zgjidhni qelizat dhe vendosni një emër të përshtatshëm në fushë Emri, në të majtë të shiritit të formulës.

Tani mund të shkruani formulën e mëposhtme për të gjetur çmimin e një produkti Produkti 1:

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

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

Shumica e emrave të diapazonit funksionojnë për të gjithë librin e punës të Excel, kështu që nuk ka nevojë të specifikoni emrin e fletës për argumentin table_array (tabela), edhe nëse formula dhe diapazoni i kërkimit janë në fletë pune të ndryshme. Nëse ato janë në libra të ndryshëm pune, atëherë para emrit të diapazonit duhet të specifikoni emrin e librit të punës, për shembull, si kjo:

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

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

Pra formula duket shumë më e qartë, dakord? Gjithashtu, përdorimi i vargjeve të emërtuara është një alternativë e mirë për referencat absolute sepse diapazoni i emërtuar nuk ndryshon kur kopjoni formulën në qeliza të tjera. Kjo do të thotë që mund të jeni i sigurt se diapazoni i kërkimit në formulë do të mbetet gjithmonë i saktë.

Nëse konvertoni një sërë qelizash në një fletëllogaritëse të plotë Excel duke përdorur komandën Tryezë (Tabela) skeda futje (Fut), më pas kur zgjidhni një gamë me miun, Microsoft Excel do të shtojë automatikisht emrat e kolonave (ose emrin e tabelës nëse zgjidhni të gjithë tabelën) në formulë.

Formula e përfunduar do të duket diçka si kjo:

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

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

Ose ndoshta edhe si kjo:

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

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

Kur përdorni vargjet e emërtuara, lidhjet do të tregojnë të njëjtat qeliza, pavarësisht se ku e kopjoni funksionin VPR brenda fletores së punës.

Përdorimi i Wildcards në Formulat VLOOKUP

Ashtu si me shumë funksione të tjera, VPR Ju mund të përdorni karakteret e mëposhtme të karaktereve:

  • Pikëpyetje (?) - zëvendëson çdo karakter të vetëm.
  • Ylli (*) – zëvendëson çdo sekuencë karakteresh.

Përdorimi i Wildcards në Funksionet VPR mund të jetë i dobishëm në shumë raste, për shembull:

  • Kur nuk e mbani mend saktësisht tekstin që duhet të gjeni.
  • Kur doni të gjeni ndonjë fjalë që është pjesë e përmbajtjes së një qelize. Dije se VPR kërkon sipas përmbajtjes së qelizës në tërësi, sikur opsioni të jetë i aktivizuar Përputhni të gjithë përmbajtjen e qelizës (E gjithë qeliza) në kërkimin standard të Excel.
  • Kur një qelizë përmban hapësira shtesë në fillim ose në fund të përmbajtjes. Në një situatë të tillë, ju mund të grumbulloni trurin tuaj për një kohë të gjatë, duke u përpjekur të kuptoni pse formula nuk funksionon.

Shembulli 1: Kërkimi i tekstit që fillon ose mbaron me karaktere të caktuara

Le të themi se dëshironi të kërkoni për një klient specifik në bazën e të dhënave të paraqitur më poshtë. Mbiemri i tij nuk ju kujtohet, por e dini që fillon me “ack”. Këtu është një formulë që do ta bëjë punën mirë:

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

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

Tani që jeni të sigurt se keni gjetur emrin e saktë, mund të përdorni të njëjtën formulë për të gjetur shumën e paguar nga ky klient. Për ta bërë këtë, thjesht ndryshoni argumentin e tretë të funksionit VPR në numrin e dëshiruar të kolonës. Në rastin tonë, kjo është kolona C (e treta në rangun):

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

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

Këtu janë disa shembuj të tjerë me karaktere të egra:

~ Gjeni një emër që mbaron me "burrë":

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

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

~ Gjeni një emër që fillon me "ad" dhe mbaron me "bir":

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

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

~ Ne gjejmë emrin e parë në listë, i përbërë nga 5 karaktere:

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

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

Për të funksionuar VPR me wildcards ka punuar si duhet, si argumenti i katërt që duhet të përdorni gjithmonë I RREMË (I RREMË). Nëse diapazoni i kërkimit përmban më shumë se një vlerë që përputhet me termat e kërkimit me shkronja të ngurta, atëherë vlera e parë e gjetur do të kthehet.

Shembulli 2: Kombinoni shkronjat e egra dhe referencat e qelizave në formulat VLOOKUP

Tani le të shohim një shembull pak më kompleks se si të kërkoni duke përdorur funksionin VPR sipas vlerës në një qelizë. Imagjinoni që kolona A është një listë e çelësave të licencës dhe kolona B është një listë emrash që zotërojnë një licencë. Përveç kësaj, ju keni një pjesë (disa karaktere) të një lloj çelësi licence në qelizën C1 dhe dëshironi të gjeni emrin e pronarit.

Kjo mund të bëhet duke përdorur formulën e mëposhtme:

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

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

Kjo formulë kërkon vlerën nga qeliza C1 në intervalin e dhënë dhe kthen vlerën përkatëse nga kolona B. Vini re se në argumentin e parë, ne përdorim një karakter ampersand (&) përpara dhe pas referencës së qelizës për të lidhur vargun e tekstit.

Siç mund ta shihni në figurën më poshtë, funksioni VPR kthen "Jeremy Hill" sepse çelësi i tij i licencës përmban sekuencën e karaktereve nga qeliza C1.

Vini re se argumenti table_array (tabela) në pamjen e mësipërme përmban emrin e tabelës (Tabela 7) në vend që të specifikojë një varg qelizash. Kjo është ajo që bëmë në shembullin e mëparshëm.

Përputhja e saktë ose e përafërt në funksionin VLOOKUP

Dhe së fundi, le të hedhim një vështrim më të afërt në argumentin e fundit që është specifikuar për funksionin VPR - kërkimi_rangut (pamja_intervali). Siç u përmend në fillim të mësimit, ky argument është shumë i rëndësishëm. Ju mund të merrni rezultate krejtësisht të ndryshme në të njëjtën formulë me vlerën e saj KODI I VËRTETË (E VËRTETË) ose I RREMË (I RREMË).

Së pari, le të zbulojmë se çfarë do të thotë Microsoft Excel me përputhje të saktë dhe të përafërt.

  • Nëse argumenti kërkimi_rangut (range_lookup) është e barabartë me I RREMË (FALSE), formula kërkon një përputhje të saktë, pra saktësisht të njëjtën vlerë siç është dhënë në argument lookup_value (vlera_kërkimi). Nëse në kolonën e parë të diapazonit tgrup_afte (tabela) ndeshet me dy ose më shumë vlera që përputhen me argumentin lookup_value (search_value), atëherë do të zgjidhet i pari. Nëse nuk gjenden përputhje, funksioni do të raportojë një gabim #AT (# N/A). Për shembull, formula e mëposhtme do të raportojë një gabim #AT (#N/A) nëse nuk ka vlerë në intervalin A2:A15 4:

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

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

  • Nëse argumenti kërkimi_rangut (range_lookup) është e barabartë me KODI I VËRTETË (E VËRTETË), formula kërkon një përputhje të përafërt. Më saktësisht, së pari funksioni VPR kërkon një përputhje të saktë dhe nëse nuk gjendet, zgjedh një të përafërt. Një përputhje e përafërt është vlera më e madhe që nuk e kalon vlerën e specifikuar në argument. lookup_value (vlera_kërkimi).

Nëse argumenti kërkimi_rangut (range_lookup) është e barabartë me KODI I VËRTETË (E VËRTETË) ose jo e specifikuar, atëherë vlerat në kolonën e parë të diapazonit duhet të renditen në rend rritës, domethënë nga më i vogli tek më i madhi. Përndryshe, funksioni VPR mund të kthejë një rezultat të gabuar.

Për të kuptuar më mirë rëndësinë e zgjedhjes KODI I VËRTETË (E VËRTETA) ose I RREMË (FALSE), le të shohim disa formula të tjera me funksionin VPR dhe shikoni rezultatet.

Shembulli 1: Gjetja e një përputhje të saktë me VLOOKUP

Siç e mbani mend, për të kërkuar një përputhje të saktë, argumenti i katërt i funksionit VPR duhet të ketë rëndësi I RREMË (I RREMË).

Le të kthehemi në tabelë nga shembulli i parë dhe të zbulojmë se cila kafshë mund të lëvizë me një shpejtësi 50 milje per ore. Unë besoj se kjo formulë nuk do t'ju shkaktojë ndonjë vështirësi:

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

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

Vini re se diapazoni ynë i kërkimit (kolona A) përmban dy vlera 50 – në qeliza A5 и A6. Formula kthen vlerën nga qeliza B5. Pse? Sepse kur kërkoni për një përputhje të saktë, funksioni VPR përdor vlerën e parë të gjetur që përputhet me atë që kërkohet.

Shembulli 2: Përdorimi i VLOOKUP për të gjetur një përputhje të përafërt

Kur përdorni funksionin VPR për të kërkuar një përputhje të përafërt, dmth kur argumenti kërkimi_rangut (range_lookup) është e barabartë me KODI I VËRTETË (E VËRTETË) ose e lënë jashtë, gjëja e parë që duhet të bëni është të renditni diapazonin sipas kolonës së parë në rend rritës.

Kjo është shumë e rëndësishme sepse funksioni VPR kthen vlerën tjetër më të madhe pas asaj të dhënë dhe më pas kërkimi ndalon. Nëse neglizhoni renditjen e saktë, do të përfundoni me rezultate shumë të çuditshme ose një mesazh gabimi. #AT (# N/A).

Tani mund të përdorni një nga formulat e mëposhtme:

=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)

Siç mund ta shihni, unë dua të zbuloj se cila nga kafshët ka shpejtësinë më të afërt 69 milje per ore. Dhe këtu është rezultati që funksioni m'u kthye VPR:

Siç mund ta shihni, formula ktheu një rezultat antilopë (Antilopa), shpejtësia e së cilës 61 milje në orë, megjithëse lista përfshin gjithashtu gatopard (Gapardi) që vrapon me shpejtësi 70 milje në orë, dhe 70 është më afër 69 se 61, apo jo? Pse po ndodh kjo? Sepse funksioni VPR kur kërkoni për një përputhje të përafërt, kthen vlerën më të madhe që nuk është më e madhe se ajo që kërkohet.

Shpresoj që këta shembuj të hedhin pak dritë mbi punën me funksionin VPR në Excel, dhe ju nuk e shikoni më atë si një të huaj. Tani nuk është e dëmshme të përsërisim shkurtimisht pikat kryesore të materialit që kemi studiuar për ta rregulluar më mirë atë në kujtesë.

VLOOKUP në Excel - duhet ta mbani mend këtë!

  1. funksion VPR Excel nuk mund të duket majtas. Gjithmonë kërkon vlerën në kolonën më të majtë të diapazonit të dhënë nga argumenti table_array (tabela).
  2. Në funksion VPR të gjitha vlerat janë të pandjeshme ndaj shkronjave, pra shkronjat e vogla dhe të mëdha janë ekuivalente.
  3. Nëse vlera që kërkoni është më e vogël se vlera minimale në kolonën e parë të diapazonit që po kërkoni, funksioni VPR do të raportojë një gabim #AT (# N/A).
  4. Nëse argumenti i tretë col_index_num (numri_kolona) më pak se 1funksion VPR do të raportojë një gabim #VALUE! (#VLERA!). Nëse është më i madh se numri i kolonave në interval table_array (tabela), funksioni do të raportojë një gabim # REF! (#LINK!).
  5. Përdorni referenca absolute të qelizave në argument table_array (tabela) në mënyrë që të ruhet diapazoni i saktë i kërkimit gjatë kopjimit të formulës. Provoni të përdorni vargjet ose tabelat e emëruara në Excel si një alternativë.
  6. Kur bëni një kërkim të përafërt të përputhjes, mbani mend se kolona e parë në diapazonin që kërkoni duhet të renditet në rend rritës.
  7. Së fundi, mbani mend rëndësinë e argumentit të katërt. Përdorni vlerat KODI I VËRTETË (E VËRTETA) ose I RREMË (FALSE) qëllimisht dhe do të shpëtoni nga shumë dhimbje koke.

Në artikujt vijues të tutorialit tonë të funksionit VPR në Excel, ne do të mësojmë shembuj më të avancuar, të tilla si kryerja e llogaritjeve të ndryshme duke përdorur VPR, nxjerrjen e vlerave nga kolona të shumta dhe më shumë. Faleminderit që lexuat këtë tutorial dhe shpresoj të shihemi sërish javën e ardhshme!

Lini një Përgjigju