VLOOKUP aðgerð í Excel – Byrjendahandbók: Setningafræði og dæmi

Efnisyfirlit

VLOOKUP aðgerð í Excel – almenn lýsing og setningafræði
- Setningafræði VLOOKUP fallsins
VLOOKUP Dæmi
Nákvæm eða áætluð samsvörun í VLOOKUP aðgerðinni
- Dæmi 1: Að finna nákvæma samsvörun með VLOOKUP
- Dæmi 2: Notkun VLOOKUP til að finna áætlaða samsvörun
VLOOKUP í Excel - þú þarft að muna þetta!

Í dag erum við að hefja röð greina sem lýsa einum af gagnlegustu eiginleikum Excel − VPR (ÚTLEIT). Þessi aðgerð er á sama tíma ein sú flóknasta og minnst skiljanlegasta.

Í þessari kennslu um VPR Ég mun reyna að leggja grunnatriðin á eins einfaldan hátt og mögulegt er til að gera námsferlið eins skýrt og mögulegt er fyrir óreynda notendur. Að auki munum við rannsaka nokkur dæmi með Excel formúlum sem sýna fram á algengustu notkunartilvikin fyrir fallið VPR.

VLOOKUP aðgerð í Excel – almenn lýsing og setningafræði

Svo hvað er það VPR? Jæja, fyrst af öllu, það er Excel aðgerð. Hvað gerir hún? Það flettir upp gildinu sem þú tilgreinir og skilar samsvarandi gildi úr hinum dálknum. Tæknilega séð, VPR flettir upp gildinu í fyrsta dálki tiltekins bils og skilar niðurstöðu úr öðrum dálki í sömu röð.

Í algengasta forritinu er aðgerðin VPR leitar í gagnagrunninum að tilteknu einkvæmu auðkenni og dregur nokkrar upplýsingar tengdar því úr gagnagrunninum.

Fyrsti stafur í heiti falls VPR (VLOOKUP) þýðir Вlóðrétt (Vlóðrétt). Með því er hægt að greina á milli VPR frá GPR (HLOOKUP), sem leitar að gildi í efstu röð sviðs − Гlárétt (Hlárétt).

virka VPR fáanlegt í Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP og Excel 2000.

Setningafræði VLOOKUP fallsins

virka VPR (VLOOKUP) hefur eftirfarandi setningafræði:

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

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

Eins og þú sérð, aðgerð VPR í Microsoft Excel hefur 4 valkosti (eða rök). Fyrstu þrír eru skyldubundnir, sá síðasti er valfrjáls.

lookup_value (lookup_value) – Gildið sem á að leita að. Þetta getur verið gildi (tala, dagsetning, texti) eða frumutilvísun (sem inniheldur uppflettingargildið), eða gildi sem einhver önnur Excel aðgerð skilar. Til dæmis mun þessi formúla leita að gildinu 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Ef uppflettingargildið er minna en minnsta gildið í fyrsta dálknum á því sviði sem verið er að fletta upp, er fallið VPR mun tilkynna villu #AT (#N/A).

table_array (tafla) – tveir eða fleiri dálkar af gögnum. Mundu, aðgerðina VPR leitar alltaf að gildinu í fyrsta dálknum á bilinu sem gefið er upp í röksemdafærslunni table_array (tafla). Sýnanlegt svið getur innihaldið ýmis gögn, svo sem texta, dagsetningar, tölur, boolean. Aðgerðin er hástafaónæmi, sem þýðir að hástafir og lágstafir eru álitnir eins. Þannig að formúlan okkar mun leita að gildinu 40 í frumum frá A2 til A15, vegna þess að A er fyrsti dálkurinn á bilinu A2:B15 sem gefið er upp í röksemdafærslunni table_array (tafla):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_numr (column_number) er númer dálksins á tilteknu bili sem gildið í röðinni sem fannst verður skilað frá. Dálkurinn lengst til vinstri á tilteknu bili er 1, seinni dálkurinn er 2, þriðji dálkurinn er 3 og svo framvegis. Nú geturðu lesið alla formúluna:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Formúla að leita að verðmæti 40 á bilinu A2: A15 og skilar samsvarandi gildi úr dálki B (vegna þess að B er annar dálkurinn á bilinu A2:B15).

Ef gildi röksemdafærslunnar col_index_numr (dálkanúmer) minna en 1Þá VPR mun tilkynna villu #VALUE! (#VERÐI!). Og ef það er meira en fjöldi dálka á bilinu table_array (tafla), mun aðgerðin skila villu #REF! (#LINK!).

range_lookup (range_lookup) – ákvarðar hvað á að leita að:
- nákvæm samsvörun, rök verða að vera jöfn RANGT (RANGT);
- áætluð samsvörun, rök eru jöfn SANN KÓÐI (TRUE) eða alls ekki tilgreint.
Þessi færibreyta er valkvæð, en mjög mikilvæg. Síðar í þessari kennslu á VPR Ég skal sýna þér nokkur dæmi sem útskýra hvernig á að skrifa formúlur til að finna nákvæma og áætlaða samsvörun.

VLOOKUP Dæmi

Ég vona að aðgerðin VPR verða þér aðeins skýrari. Nú skulum við skoða nokkur notkunartilvik VPR í formúlum með raunverulegum gögnum.

Hvernig á að nota VLOOKUP til að leita í öðru Excel blaði

Í reynd, formúlur með fall VPR eru sjaldan notuð til að leita að gögnum á sama vinnublaði. Oftar en ekki munt þú fletta upp og sækja samsvarandi gildi úr öðru blaði.

Til þess að nota VPR, leitaðu í öðru Microsoft Excel blaði, Þú verður að í rökræðunni table_array (tafla) tilgreindu nafn blaðsins með upphrópunarmerki og síðan fjölda hólfa. Til dæmis sýnir eftirfarandi formúla að bilið A2: B15 er á blaði sem heitir Sheet2.

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

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

Auðvitað þarf ekki að slá inn nafn blaðsins handvirkt. Byrjaðu bara að slá inn formúluna, og þegar kemur að rifrildinu table_array (tafla), skiptu yfir í blaðið sem þú vilt og veldu viðeigandi frumusvið með músinni.

Formúlan sem sýnd er á skjámyndinni hér að neðan leitar að textanum „Vöru 1“ í dálki A (það er 1. dálkur á bilinu A2:B9) á vinnublaði verð.

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

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

Vinsamlegast mundu að þegar þú leitar að textagildi verður þú að setja það innan gæsalappa (“”), eins og venjulega er gert í Excel formúlum.

Fyrir rök table_array (tafla) æskilegt er að nota alltaf algjörar tilvísanir (með $ tákninu). Í þessu tilviki mun leitarsviðið haldast óbreytt þegar formúlan er afrituð í aðrar frumur.

Leitaðu í annarri vinnubók með VLOOKUP

Að virka VPR unnið á milli tveggja Excel vinnubóka, þarf að tilgreina heiti vinnubókarinnar í hornklofa á undan nafni blaðsins.

Til dæmis, hér að neðan er formúla sem leitar að gildinu 40 á blaðinu Sheet2 í bókinni Numbers.xlsx:

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

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

Hér er auðveldasta leiðin til að búa til formúlu í Excel með VPRsem tengist annarri vinnubók:

Opnaðu báðar bækurnar. Þess er ekki krafist, en það er auðveldara að búa til formúlu með þessum hætti. Þú vilt ekki slá inn nafn vinnubókarinnar handvirkt, er það? Að auki mun það vernda þig gegn innsláttarvillum fyrir slysni.
Byrjaðu að slá inn fall VPRog þegar kemur að röksemdafærslunni table_array (tafla), skiptu yfir í aðra vinnubók og veldu tilskilið leitarsvið í henni.

Skjámyndin hér að neðan sýnir formúluna með leitinni stillt á svið í vinnubókinni Verðlisti.xlsx á blaðinu verð.

virka VPR mun virka jafnvel þegar þú lokar vinnubókinni sem leitað er að og öll slóðin að vinnubókarskránni birtist á formúlustikunni, eins og sýnt er hér að neðan:

Ef nafnið á vinnubókinni eða blaðinu inniheldur bil, þá verður það að vera umlukt með postulum:

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

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

Hvernig á að nota nafngreint svið eða töflu í formúlum með VLOOKUP

Ef þú ætlar að nota sama leitarsvið í mörgum aðgerðum VPR, þú getur búið til nafngreint svið og slegið nafn þess inn í formúluna sem rök table_array (tafla).

Til að búa til nafngreint svið skaltu einfaldlega velja frumurnar og slá inn viðeigandi nafn í reitinn Fornafn, vinstra megin við formúlustikuna.

Nú getur þú skrifað niður eftirfarandi formúlu til að finna verð á vöru Vara 1:

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

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

Flest sviðsheiti virka fyrir alla Excel vinnubókina, svo það er engin þörf á að tilgreina blaðheitið fyrir röksemdafærsluna table_array (tafla), jafnvel þótt formúlan og leitarsviðið sé á mismunandi vinnublöðum. Ef þau eru í mismunandi vinnubókum, þá þarftu á undan nafni sviðsins að tilgreina nafn vinnubókarinnar, til dæmis, svona:

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

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

Þannig að formúlan lítur miklu skýrari út, sammála? Að nota nafngreind svið er líka góður valkostur við alger tilvísanir vegna þess að nafngreint svið breytist ekki þegar þú afritar formúluna í aðrar frumur. Þetta þýðir að þú getur verið viss um að leitarsviðið í formúlunni haldist alltaf rétt.

Ef þú umbreytir fjölda frumna í fullbúið Excel töflureikni með því að nota skipunina Tafla (Tafla) flipi Innsetning (Setja inn), síðan þegar þú velur svið með músinni, mun Microsoft Excel sjálfkrafa bæta dálknöfnunum (eða töfluheitinu ef þú velur alla töfluna) við formúluna.

Fullunnin formúla mun líta eitthvað svona út:

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

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

Eða jafnvel svona:

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

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

Þegar nafngreind svið eru notuð munu hlekkirnir benda á sömu frumurnar, sama hvar þú afritar aðgerðina VPR innan vinnubókarinnar.

Að nota jokertákn í VLOOKUP formúlum

Eins og með margar aðrar aðgerðir, VPR Þú getur notað eftirfarandi algildisstafi:

Spurningamerki (?) – kemur í stað hvers einasta stafa.
Stjarna (*) – kemur í stað hvers konar stafaröð.

Að nota jokertákn í aðgerðum VPR getur verið gagnlegt í mörgum tilfellum, til dæmis:

Þegar þú manst ekki nákvæmlega textann sem þú þarft að finna.
Þegar þú vilt finna eitthvað orð sem er hluti af innihaldi frumu. Veit það VPR leitar eftir innihaldi frumunnar í heild, eins og valmöguleikinn sé virkur Passaðu allt innihald frumunnar (Allur reiturinn) í hefðbundinni Excel leit.
Þegar hólf inniheldur aukabil í upphafi eða lok efnisins. Í slíkum aðstæðum geturðu rekið heilann í langan tíma og reynt að komast að því hvers vegna formúlan virkar ekki.

Dæmi 1: Að leita að texta sem byrjar eða endar á ákveðnum stöfum

Segjum að þú viljir leita að tilteknum viðskiptavini í gagnagrunninum sem sýndur er hér að neðan. Þú manst ekki eftirnafnið hans, en þú veist að það byrjar á „ack“. Hér er formúla sem mun gera verkið vel:

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

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

Nú þegar þú ert viss um að þú hafir fundið rétta nafnið geturðu notað sömu formúlu til að finna upphæðina sem þessi viðskiptavinur greiddi. Til að gera þetta skaltu bara breyta þriðju röksemd fallsins VPR í viðkomandi dálknúmer. Í okkar tilviki er þetta dálkur C (þriðji á bilinu):

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

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

Hér eru nokkur fleiri dæmi með jokertáknum:

~ Finndu nafn sem endar á „maður“:

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

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

~ Finndu nafn sem byrjar á „ad“ og endar á „son“:

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

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

~ Við finnum fornafnið á listanum, sem samanstendur af 5 stöfum:

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

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

Að virka VPR með jokertáknum virkaði rétt, sem fjórða rökin sem þú ættir alltaf að nota RANGT (RANGT). Ef leitarsviðið inniheldur fleiri en eitt gildi sem samsvarar leitarskilmálum með algildisstöfum, þá verður fyrsta gildinu sem fannst skilað.

Dæmi 2: Sameina algildi og frumutilvísanir í VLOOKUP formúlur

Nú skulum við skoða aðeins flóknara dæmi um hvernig á að leita með aðgerðinni VPR eftir gildi í reit. Ímyndaðu þér að dálkur A sé listi yfir leyfislykla og dálkur B er listi yfir nöfn sem eiga leyfi. Að auki ertu með hluta (nokkra stafi) af einhvers konar leyfislykli í reit C1 og þú vilt finna nafn eigandans.

Þetta er hægt að gera með því að nota eftirfarandi formúlu:

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

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

Þessi formúla flettir upp gildinu úr reit C1 á tilteknu bili og skilar samsvarandi gildi úr dálki B. Athugaðu að í fyrstu röksemdinni notum við tákn (&) á undan og á eftir reittilvísuninni til að tengja textastrenginn.

Eins og þú sérð á myndinni hér að neðan er aðgerðin VPR skilar „Jeremy Hill“ vegna þess að leyfislykill hans inniheldur röð stafa úr reit C1.

Athugið að rökin table_array (tafla) í skjámyndinni hér að ofan inniheldur heiti töflunnar (Tafla7) í stað þess að tilgreina svið af frumum. Þetta er það sem við gerðum í fyrra dæminu.

Nákvæm eða áætluð samsvörun í VLOOKUP aðgerðinni

Og að lokum skulum við skoða nánar síðustu röksemdirnar sem eru tilgreindar fyrir fallið VPR - range_lookup (interval_view). Eins og fram kom í upphafi kennslustundar eru þessi rök mjög mikilvæg. Þú getur fengið allt aðrar niðurstöður í sömu formúlunni með gildi hennar SANN KÓÐI (SATT) eða RANGT (RANGT).

Fyrst skulum við komast að því hvað Microsoft Excel þýðir með nákvæmum og áætlaðri samsvörun.

Ef rökin range_lookup (range_lookup) er jafnt og RANGT (FALSE), formúlan leitar að nákvæmri samsvörun, þ.e. nákvæmlega sama gildi og gefið er upp í röksemdafærslunni lookup_value (uppflettingargildi). Ef í fyrsta dálki bilsins tfær_fylki (tafla) rekst á tvö eða fleiri gildi sem passa við rökin lookup_value (leitargildi), þá verður sá fyrsti valinn. Ef engin samsvörun finnst mun aðgerðin tilkynna um villu #AT (#N/A). Til dæmis mun eftirfarandi formúla tilkynna um villu #AT (#N/A) ef ekkert gildi er á bilinu A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Ef rökin range_lookup (range_lookup) er jafnt og SANN KÓÐI (TRUE), formúlan leitar að áætlaðri samsvörun. Nánar tiltekið, fyrst aðgerðin VPR leitar að nákvæmri samsvörun og ef engin finnst, velur áætlaða. Áætluð samsvörun er stærsta gildið sem fer ekki yfir gildið sem tilgreint er í frumbreytunni. lookup_value (uppflettingargildi).

Ef rökin range_lookup (range_lookup) er jafnt og SANN KÓÐI (TRUE) eða ekki tilgreint, þá ætti að flokka gildin í fyrsta dálki sviðsins í hækkandi röð, það er frá minnstu til stærstu. Annars er aðgerðin VPR getur skilað rangri niðurstöðu.

Til að skilja betur mikilvægi vals SANN KÓÐI (Sannleikur) eða RANGT (FALSE), skulum skoða fleiri formúlur með fallinu VPR og skoða niðurstöðurnar.

Dæmi 1: Að finna nákvæma samsvörun með VLOOKUP

Eins og þú manst, til að leita að nákvæmri samsvörun, fjórða rök fallsins VPR ætti að skipta máli RANGT (RANGT).

Við skulum fara aftur að töflunni frá fyrsta dæminu og komast að því hvaða dýr getur hreyft sig á hraða 50 mílur á klukkustund. Ég tel að þessi formúla muni ekki valda þér erfiðleikum:

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

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

Athugaðu að leitarsviðið okkar (dálkur A) inniheldur tvö gildi 50 - í frumum A5 и A6. Formúla skilar gildi úr reit B5. Hvers vegna? Vegna þess að þegar leitað er að nákvæmri samsvörun er aðgerðin VPR notar fyrsta gildið sem passar við það sem leitað er að.

Dæmi 2: Notkun VLOOKUP til að finna áætlaða samsvörun

Þegar þú notar aðgerðina VPR að leita að áætlaðri samsvörun, þ.e. þegar rökin range_lookup (range_lookup) er jafnt og SANN KÓÐI (TRUE) eða sleppt, það fyrsta sem þú þarft að gera er að raða bilinu eftir fyrsta dálknum í hækkandi röð.

Þetta er mjög mikilvægt vegna þess að aðgerðin VPR skilar næststærsta gildinu á eftir því sem gefið er upp og þá stöðvast leitin. Ef þú vanrækir rétta flokkun færðu mjög undarlegar niðurstöður eða villuboð. #AT (#N/A).

Nú geturðu notað eina af eftirfarandi formúlum:

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

Eins og þú sérð vil ég komast að því hvert dýranna hefur mestan hraða 69 mílur á klukkustund. Og hér er niðurstaðan sem aðgerðin skilaði mér VPR:

Eins og þú sérð skilaði formúlan niðurstöðu Antilóp (Antilópa), hvers hraði 61 mílur á klukkustund, þó listinn innifeli einnig blettatígur (Cheetah) sem hleypur á hraða 70 mílur á klukkustund, og 70 er nær 69 en 61, er það ekki? Hvers vegna er þetta að gerast? Vegna þess að aðgerðin VPR þegar leitað er að áætlaðri samsvörun, skilar stærsta gildinu sem er ekki stærra en það sem leitað er að.

Ég vona að þessi dæmi varpi einhverju ljósi á að vinna með aðgerðina VPR í Excel, og þú lítur ekki lengur á hana sem utanaðkomandi. Nú sakar ekki að endurtaka í stuttu máli lykilatriði efnisins sem við höfum rannsakað til að festa það betur í minninu.

VLOOKUP í Excel - þú þarft að muna þetta!

virka VPR Excel getur ekki litið til vinstri. Það leitar alltaf að gildinu í dálknum lengst til vinstri á bilinu sem röksemdin gefur upp table_array (tafla).
Í aðgerð VPR öll gildi eru há- og hástafir, þ.e. litlir og stórir stafir eru jafngildir.
Ef gildið sem þú ert að leita að er minna en lágmarksgildið í fyrsta dálki sviðsins sem verið er að fletta upp, er fallið VPR mun tilkynna villu #AT (#N/A).
Ef 3. rök col_index_numr (dálkanúmer) minna en 1virka VPR mun tilkynna villu #VALUE! (#VERÐI!). Ef það er meira en fjöldi dálka á bilinu table_array (tafla), mun aðgerðin tilkynna villu #REF! (#LINK!).
Notaðu algjörar frumutilvísanir í rök table_array (tafla) þannig að rétt leitarsvið varðveitist þegar formúlan er afrituð. Prófaðu að nota nafngreind svið eða töflur í Excel sem val.
Þegar þú gerir áætlaða samsvörunarleit skaltu muna að fyrsti dálkurinn á sviðinu sem þú ert að leita að verður að vera flokkaður í hækkandi röð.
Mundu að lokum mikilvægi fjórðu röksemdarinnar. Notaðu gildi SANN KÓÐI (Sannleikur) eða RANGT (RÖNT) vísvitandi og þú munt losna við marga höfuðverk.

Í eftirfarandi greinum í kennsluefninu okkar VPR í Excel munum við læra fullkomnari dæmi, eins og að framkvæma ýmsa útreikninga með því að nota VPR, draga gildi úr mörgum dálkum og fleira. Þakka þér fyrir að lesa þessa kennslu og ég vona að sjá þig aftur í næstu viku!

VLOOKUP aðgerð í Excel – Leiðbeiningar fyrir byrjendur: Setningafræði og dæmi