Mukati
- VLOOKUP basa muExcel - tsananguro yakajairika uye syntax
- VLOOKUP Mienzaniso
- Chaiyo kana fungidziro yemutambo muVLOOKUP basa
- VLOOKUP muExcel - unofanirwa kurangarira izvi!
Nhasi tiri kutanga nhevedzano yezvinyorwa zvinotsanangura chimwe chezvinhu zvinobatsira zveExcel - VPR (VLOOKUP). Iri basa, panguva imwe chete, nderimwe rezvakaoma uye zvisinganzwisisike.
Muchidzidzo ichi pa VPR Ini ndichaedza kuisa izvo zvekutanga zviri nyore sezvinobvira kuitira kuti maitiro ekudzidza ave akajeka sezvinobvira kune vasina ruzivo rwevashandisi. Pamusoro pezvo, isu tichadzidza akati wandei mienzaniso neExcel mafomula ayo anozoratidza anowanzo shandiswa makesi ebasa racho VPR.
VLOOKUP basa muExcel - tsananguro yakajairika uye syntax
Saka chii VPR? Zvakanaka, chekutanga pane zvese, ibasa reExcel. Chii chaanoita? Inotarisa kukosha kwaunotsanangura uye inodzorera kukosha kunowirirana kubva kune imwe column. Nehunyanzvi kutaura, VPR anotarisa kukosha muchikamu chekutanga chechikamu chakapihwa uye anodzosera mhinduro kubva kune imwe mbiru mumutsara mumwe chete.
Muchishandiso chinowanzoitwa, basa racho VPR inotsvaga dhatabhesi kune yakapihwa yakasarudzika identifier uye inobvisa imwe ruzivo ine chekuita nayo kubva mudura re database.
Vara rekutanga muzita rekuita VPR (VLOOKUP) zvinoreva Вyakatwasuka (Vvertical). Narwo unogona kusiyanisa VPR kubva GPR (HLOOKUP), iyo inotsvaga kukosha mumutsara wepamusoro werudzi − Гhorizontal (Hnehorizontally).
basa VPR inowanikwa muExcel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, uye Excel 2000.
Syntax yeVLOOKUP basa
basa VPR (VLOOKUP) ine syntax inotevera:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Sezvauri kuona, basa VPR muMicrosoft Excel ine 4 sarudzo (kana nharo). Zvitatu zvekutanga zvinosungirwa, zvekupedzisira ndezvekusarudza.
- lookup_value (lookup_value) - Kukosha kwekutsvaga. Izvi zvinogona kuva kukosha (nhamba, zuva, zvinyorwa) kana sero referensi (ine kukosha kwekutarisa), kana kukosha kwakadzorerwa nerimwe basa reExcel. Semuenzaniso, iyi fomula ichatsvaga kukosha 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Kana kukosha kwekutarisa kuri kushoma pane diki kukosha muchikamu chekutanga chechikamu chiri kutariswa, basa racho VPR achataura kukanganisa #AT (#N/A).
- tafura_array (tafura) - maviri kana anopfuura makoramu e data. Rangarira, basa VPR gara uchitsvaga kukosha muchikamu chekutanga chechikamu chakapihwa mukupokana tafura_array (tafura). Iyo inoonekwa renji inogona kuve neakasiyana data, senge zvinyorwa, misi, nhamba, booleans. Basa racho harinzwisisike, zvichireva kuti mavara epamusoro neepasi anoonekwa akafanana. Saka formula yedu ichatsvaga kukosha 40 mumacells kubva A2 to A15, nekuti A ndiyo koramu yekutanga yerudzi A2:B15 yakapihwa mukupokana tafura_array (tafura):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (column_number) inhamba yekoramu muchikamu chakapihwa panobva padzoserwa kukosha mumutsara wawanikwa. Koramu yekuruboshwe muchikamu chakapihwa ndeiyi 1, chikamu chechipiri ndicho 2, chikamu chechitatu ndicho 3 zvichingoenda zvakadaro. Iye zvino unogona kuverenga iyo formula yese:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Formula kutsvaga kukosha 40 munharaunda A2:A15 uye inodzosa kukosha kunoenderana kubva pakoramu B (nekuti B ndiyo yechipiri koramu muchikamu A2:B15).
Kana kukosha kwenharo col_index_num (column_nhamba) ishoma pane 1ipapo VPR achataura kukanganisa #VALUE! (#VALUE!). Uye kana ichipfuura nhamba yemakoramu muchikamu tafura_array (tafura), basa rinodzosa kukanganisa #REF! (#LINK!).
- range_lookup (range_lookup) - inosarudza zvekutsvaga:
- machisi chaiwo, nharo dzinofanira kuenzana VENHEMA (NHEMA);
- approximate match, nharo yakaenzana TRUE CODE (CHOKWADI) kana zvisina kutaurwa zvachose.
Iyi parameter ndeyekusarudza, asi yakakosha zvikuru. Gare gare mune ino tutorial pa VPR Ini ndichakuratidza mimwe mienzaniso inotsanangura maitiro ekunyora mafomula ekutsvaga chaiwo uye akaenzana machisi.
VLOOKUP Mienzaniso
Ndinovimba basa VPR zvinyatsojeka kwamuri. Zvino ngatitarisei mamwe ekushandisa kesi VPR mumafomula ane data chaiyo.
Maitiro ekushandisa VLOOKUP kutsvaga mune imwe Excel sheet
Mukuita, mafomula ane basa VPR haawanzo shandiswa kutsvaga data papepa rebasa rimwechete. Kazhinji pane kwete, iwe unenge uchitarisa kumusoro uye kudzoreredza hunhu hunoenderana kubva kune rimwe pepa.
Kuti kushandisa VPR, tsvaga mune imwe Microsoft Excel sheet, Iwe unofanirwa mukupokana tafura_array (tafura) tsanangura zita reshizha rine chiratidzo chekudanidzira chinoteverwa nemhando dzemasero. Semuenzaniso, iyo inotevera formula inoratidza kuti chiyero A2:B15 iri pabepa rine zita Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Ehe, zita reshizha harifanirwe kunyorwa nemaoko. Ingotanga kutaipa fomula, uye kana zvasvika pakupokana tafura_array (tafura), chinja kune pepa raunoda uye sarudza mutsara waunoda wemasero negonzo.
Formula inoratidzwa pamufananidzo pazasi inotarisa "Chigadzirwa 1" mukoramu A (ndiyo 1st column yemhando A2:B9) papepa rebasa. Prices.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Ndokumbirawo muyeuke kuti kana uchitsvaga kukosha kwemavara, unofanira kuvharira mumakotesheni mamaki (“”), sezvinowanzoitwa muExcel mafomula.
Zvenharo tafura_array (tafura) zvinodikanwa kugara uchishandisa mareferenzi akakwana (nechiratidzo che$). Muchiitiko ichi, kutsvaga kwekutsvaga kucharamba kusina kuchinja paunenge uchikopa fomu kune mamwe masero.
Tsvaga mune rimwe bhuku rebasa neVLOOKUP
Kushanda VPR yakashanda pakati pemabhuku maviri eExcel, unofanirwa kutsanangura zita rebhuku rebasa mumabhuraketi akaenzana pamberi pezita repepa.
Semuenzaniso, pazasi pane formula inotarisa kukosha 40 pabepa Sheet2 mubhuku Numbers.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Heino nzira iri nyore yekugadzira fomula muExcel nayo VPRinobatanidza nerimwe bhuku rebasa:
- Vhura mabhuku ese ari maviri. Izvi hazvidiwi, asi zviri nyore kugadzira fomula nenzira iyi. Iwe hausi kuda kuisa zita rebhuku rekushandisa nemaoko, unodaro? Mukuwedzera, ichakudzivirira kubva kune tsaona typos.
- Tanga kutaipa basa VPRuye kana zvasvika pakupopotedzana tafura_array (tafura), shandura kune rimwe bhuku rebasa uye sarudza inodiwa yekutsvaga tsvaga mairi.
Mufananidzo uri pazasi unoratidza fomula ine yekutsvaga yakaiswa kune renji mubhuku rebasa PriceList.xlsx pabepa Prices.
basa VPR ichashanda kunyange kana iwe ukavhara bhuku rebasa rakatsvaga uye nzira yakazara yefaira rebhuku rebasa rinowanikwa mubhari yefomula, sezvinoratidzwa pasi apa:
Kana zita rebhuku rebasa kana pepa riine nzvimbo, saka rinofanira kuvharirwa mune apostrophes:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Maitiro ekushandisa iro rine mazita renji kana tafura mune mafomula ane VLOOKUP
Kana iwe ukaronga kushandisa iyo yakafanana yekutsvaga renji mumabasa akawanda VPR, unogona kugadzira renji ine mazita uye woisa zita rayo mufomera senharo tafura_array (tafura).
Kuti ugadzire rondedzero yezita, ingosarudza maseru uye isa zita rakakodzera mumunda zita First, kuruboshwe rweformula bar.
Iye zvino unogona kunyora pasi nzira inotevera yekutsvaga mutengo wechigadzirwa Product 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Mazita mazhinji emhando anoshandira iro rese Excel bhuku rebasa, saka hapana chikonzero chekutsanangura zita reshizha renharo tafura_array (tafura), kunyangwe iyo fomula uye huwandu hwekutsvaga huri pamapepa ekushanda akasiyana. Kana ivo vari mumabhuku ekushanda akasiyana, saka pamberi pezita remhando iwe unofanirwa kutsanangura zita rebhuku rebasa, semuenzaniso, seizvi:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Saka iyo fomula inotaridzika zvakanyanya kujeka, unobvumirana? Zvakare, kushandisa mazita ane mazita inzira yakanaka kune absolute mareferensi nekuti iro rine zita renji harichinji kana iwe uchikopa fomura kune mamwe maseru. Izvi zvinoreva kuti iwe unogona kuve nechokwadi chekuti rondedzero yekutsvaga mune fomula inogara yakaringana.
Kana iwe ukashandura huwandu hwemasero kuita yakazara-yakazara Excel spreadsheet uchishandisa rairo tafura (Tafura) tab Kuisa (Pinza), zvino paunosarudza renji negonzo, Microsoft Excel inozongowedzera mazita emakoramu (kana zita retafura kana ukasarudza tafura yese) kune fomula.
Iyo yakapedzwa fomula ichaita seizvi:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
Kana kuti pamwe seizvi:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Paunenge uchishandisa mazita ane mazita, zvinongedzo zvinonongedza kune mamwe maseru zvisinei kuti unokopa basa kupi VPR mukati mebhuku rebasa.
Kushandisa Wildcards muVLOOKUP Formulas
Sezvakaita mamwe mabasa mazhinji, VPR Unogona kushandisa anotevera wildcard mavara:
- Mucherechedzo wemubvunzo (?) - inotsiva chero munhu mumwe chete.
- Asterisk (*) - inotsiva chero kutevedzana kwemavara.
Kushandisa Wildcards muMabasa VPR inogona kubatsira mune dzakawanda zviitiko, semuenzaniso:
- Paunenge usinganyatso rangarira chinyorwa chaunoda kuwana.
- Paunenge uchida kutsvaga rimwe izwi riri chikamu chemukati mesero. Ziva izvozvo VPR inotsvaga nezviri mukati mesero rose, sekunge sarudzo inogoneswa Matanidza zvese zviri mukati mesero (Sero yese) mune yakajairwa Excel yekutsvaga.
- Kana sero rine dzimwe nzvimbo pakutanga kana kumagumo kwezviri mukati. Mumamiriro ezvinhu akadaro, unogona kukwenya huropi hwako kwenguva yakareba, uchiedza kufunga kuti nei formula isingashande.
Muenzaniso wekutanga: Kutsvaga mavara anotanga kana kupera nemamwe mavara
Ngatiti iwe unoda kutsvaga mutengi chaiwo mudhatabhesi rinoratidzwa pazasi. Hauchayeuki zita rake rekupedzisira, asi unoziva kuti rinotanga na“ack”. Heino fomula ichaita basa nemazvo:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Zvino zvawava nechokwadi chekuti wawana zita chairo, unogona kushandisa fomula imwechete kutsvaga mari yakabhadharwa nemutengi uyu. Kuti uite izvi, ingoshandura nharo yechitatu yebasa racho VPR kune nhamba yekoramu yaunoda. Kwatiri, iyi ndiyo column C (3rd muhuwandu):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Heino mimwe mienzaniso ine wildcards:
~ Tsvaga zita rinopera na "murume":
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Tsvaga zita rinotanga na "ad" richipera na "mwanakomana":
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Isu tinowana zita rekutanga mune rondedzero, rine mavara mashanu:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Kushanda VPR nema wildcards akashanda nemazvo, senharo yechina yaunofanirwa kushandisa nguva dzose VENHEMA (NHEMA). Kana huwandu hwekutsvaga huine huwandu hunopfuura humwe hunoenderana nemashoko ekutsvaga ane wildcards, ipapo kukosha kwekutanga kwakawanikwa kuchadzorerwa.
Muenzaniso 2: Sanganisa makadhi emusango uye maseru mareferenzi muVLOOKUP mafomula
Zvino ngatitarisei muenzaniso wakaoma zvishoma wekuti ungatsvaga sei uchishandisa basa racho VPR nekukosha musero. Fungidzira kuti koramu A irondedzero yemakiyi erezinesi, uye koramu B irondedzero yemazita ane rezinesi. Mukuwedzera, iwe une chikamu (anoverengeka mavara) eimwe mhando yerezinesi kiyi muchitokisi C1, uye iwe unoda kuwana zita remuridzi.
Izvi zvinogona kuitwa uchishandisa inotevera formula:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Iyi fomula inotarisa kukosha kubva musero C1 muchikamu chakapihwa uye inodzosera kukosha kunoenderana kubva mukoramu B. Ziva kuti munharo yekutanga, tinoshandisa ampersand (&) hunhu pamberi uye mushure mesero referensi kubatanidza tambo yemavara.
Sezvauri kuona mumufananidzo pazasi, basa VPR anodzoka "Jeremy Hill" nekuti kiyi yake yerezinesi ine kutevedzana kwemavara kubva muchitokisi C1.
Cherechedza kuti nharo tafura_array (tafura) muscreenshot iri pamusoro ine zita retafura (Table7) pachinzvimbo chekutsanangura huwandu hwemasero. Izvi ndizvo zvatakaita mumuenzaniso wapfuura.
Chaiyo kana fungidziro yemutambo muVLOOKUP basa
Uye chekupedzisira, ngatitarisei zvakanyanya kupokana kwekupedzisira kunotsanangurwa nezvebasa racho VPR - range_lookup (interval_view). Sezvakataurwa pakutanga kwechidzidzo, nharo iyi inokosha zvikuru. Iwe unogona kuwana mhedzisiro yakasiyana zvachose mune imwecheteyo fomula ine kukosha kwayo TRUE CODE (CHOKWADI) kana VENHEMA (NHEMA).
Chekutanga, ngationei kuti Microsoft Excel inorevei nechaiyo uye yekufungidzira machisi.
- Kana nharo range_lookup (range_lookup) yakaenzana ne VENHEMA ( NHEMA), fomula yacho inotsvaga mucheno chaiwo, kureva kukosha kwakafanana sekupihwa mukupokana lookup_value (kutarisa_kukosha). Kana muchikamu chekutanga chechikamu tanokwanisa_array (tafura) inosangana nemhando mbiri kana kupfuura dzinoenderana nekupokana lookup_value (search_value), ipapo yekutanga ichasarudzwa. Kana pasina machisi awanikwa, basa racho rinoshuma kukanganisa #AT (#N/A). Semuenzaniso, inotevera fomula inoshuma kukanganisa #AT (#N/A) kana pasina kukosha muchikamu A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Kana nharo range_lookup (range_lookup) yakaenzana ne TRUE CODE (CHOKWADI), fomula yacho inotarisa kuenzanisa. Kunyanya, kutanga basa VPR inotarisa chaiyo inofananidzwa, uye kana pasina yawanikwa, sarudza yakaenzana. Kuda kufanana ndiko kukosha kukuru kusingapfuure kukosha kwataurwa munharo. lookup_value (kutarisa_kukosha).
Kana nharo range_lookup (range_lookup) yakaenzana ne TRUE CODE (CHOKWADI) kana zvisina kutaurwa, ipapo kukosha kuri muchikamu chekutanga cherenji kunofanirwa kurongwa mukukwira kurongeka, kureva, kubva padiki kusvika pakukura. Zvikasadaro, basa VPR inogona kudzosera mhinduro isiriyo.
Kuti unzwisise zviri nani kukosha kwesarudzo TRUE CODE (CHOKWADI) kana VENHEMA (KUNYEPA), ngatitarisei mamwe mafomula ane basa racho VPR uye tarisa mhedzisiro.
Muenzaniso 1: Kutsvaga Match chaiwo neVLOOKUP
Sezvaunorangarira, kutsvaga mutsara chaiwo, nharo yechina yebasa VPR zvinofanira kukosha VENHEMA (NHEMA).
Ngatidzokerei kutafura kubva pamuenzaniso wekutanga chaiwo uye tione kuti ndeipi mhuka inogona kufamba nekumhanya 50 mamaira paawa. Ini ndinotenda kuti iyi fomula haizokonzerese iwe chero matambudziko:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Ziva kuti tsvakiridzo yedu (column A) ine maitiro maviri 50 – mumasero A5 и A6. Formula inodzosa kukosha kubva muchitokisi B5. Sei? Nekuti kana uchitsvaga chaiyo match, basa racho VPR inoshandisa kukosha kwekutanga kunowanikwa kunoenderana neiri kutsvagwa.
Muenzaniso 2: Kushandisa VLOOKUP Kutsvaga Inoenderana Mechi
Paunoshandisa basa VPR kutsvaga kuenzanisa, kureva kana nharo range_lookup (range_lookup) yakaenzana ne TRUE CODE (CHOKWADI) kana kuti chasiiwa, chinhu chekutanga chaunofanirwa kuita kuronga huwandu nekoramu yekutanga mukukwira kurongeka.
Izvi zvakakosha zvikuru nekuti basa racho VPR inodzosa kukosha kukuru kunotevera mushure meiyo yakapihwa, uyezve kutsvaga kunomira. Kana ukaregeredza kuronga kwakaringana, unozopedzisira wava nemhedzisiro isinganzwisisike kana meseji yekukanganisa. #AT (#N/A).
Iye zvino unogona kushandisa imwe yeanotevera mafomula:
=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)
Sezvauri kuona, ndinoda kuona kuti ndeipi yemhuka ine spidhi yepedyo 69 mamaira paawa. Uye heino mhedzisiro basa rakadzokera kwandiri VPR:
Sezvauri kuona, formula yakadzosa mhedzisiro Antelope (Antelope), ane kukurumidza 61 mamaira paawa, kunyangwe rondedzero inosanganisirawo dindingwe (Cheetah) inomhanya nekumhanya 70 mamaira paawa, uye makumi manomwe ari pedyo ne70 pane 69, handizvo here? Nei izvi zviri kuitika? Nokuti basa VPR kana uchitsvaga fungidziro yekuenzanisa, inodzosa kukosha kukuru kune iyo iri kutsvagwa.
Ndinovimba iyi mienzaniso inoburitsa chiedza chekushanda nebasa VPR muExcel, uye hauchamutarisi semutorwa. Iye zvino hazvikuvadzi kudzokorora muchidimbu pfungwa dzinokosha dzezvinyorwa zvatakadzidza kuitira kuti tigadzirise zviri nani mundangariro.
VLOOKUP muExcel - unofanirwa kurangarira izvi!
- basa VPR Excel haigone kutarisa kuruboshwe. Inogara ichitsvaga kukosha muchikamu chekuruboshwe chechikamu chakapihwa nenharo tafura_array (tafura).
- Mukushanda VPR hunhu hwese husinganzwisisike, kureva mavara madiki nemahombe akaenzana.
- Kana kukosha kwauri kutsvaga kuri kushoma pane hushoma kukosha muchikamu chekutanga chechikamu chiri kutariswa, basa racho VPR achataura kukanganisa #AT (#N/A).
- Kana 3rd nharo col_index_num (column_nhamba) ishoma pane 1basa VPR achataura kukanganisa #VALUE! (#VALUE!). Kana yakakura kudarika nhamba yemakoramu muchikamu tafura_array (tafura), basa rinoshuma kukanganisa #REF! (#LINK!).
- Shandisa absolute cell reference mukupokana tafura_array (tafura) kuitira kuti iyo chaiyo yekutsvaga renji ichengetedzwe kana uchikopa fomula. Edza kushandisa mazita ane mazita kana matafura muExcel seimwe nzira.
- Paunenge uchitsvaga mutambo, rangarira kuti koramu yekutanga muchikamu chauri kutsvaga inofanira kurongwa muhurongwa hwekukwira.
- Pakupedzisira, rangarira kukosha kwenharo yechina. Shandisa tsika TRUE CODE (CHOKWADI) kana VENHEMA (KUNYEPA) nemaune uye uchabvisa misoro yakawanda.
Muzvinyorwa zvinotevera zvebasa redu tutorial VPR muExcel, tichadzidza mimwe mienzaniso yepamusoro, sekuita masvomhu akasiyana siyana tichishandisa VPR, kutora kukosha kubva kumakoramu akawanda, nezvimwe. Ndinokutendai nekuverenga iyi tutori uye ndinovimba kukuonai zvakare svondo rinouya!