Ayyukan VLOOKUP a cikin Excel - Jagorar Mafari: Haɗin kai da Misalai

Contents

Ayyukan VLOOKUP a cikin Excel - cikakken bayanin da haɗin gwiwa
- Haɗin aikin VLOOKUP
Misalai na VLOOKUP
Daidai ko madaidaicin wasa a cikin aikin VLOOKUP
- Misali 1: Neman Daidaitaccen Matches tare da VLOOKUP
- Misali 2: Amfani da VLOOKUP don Nemo Kimanta Matches
VLOOKUP a cikin Excel - kuna buƙatar tunawa da wannan!

A yau muna fara jerin labaran da ke bayyana ɗayan mafi fa'ida daga cikin fasalulluka na Excel - VPR (VLOOKUP). Wannan aikin, a lokaci guda, yana ɗaya daga cikin mafi rikitarwa kuma mafi ƙarancin fahimta.

A cikin wannan koyawa akan VPR Zan yi ƙoƙarin tsara abubuwan yau da kullun a sauƙaƙe don in bayyana tsarin koyo a sarari yadda zai yiwu ga masu amfani da ba su da masaniya. Bugu da ƙari, za mu yi nazarin misalai da yawa tare da ƙididdiga na Excel waɗanda za su nuna mafi yawan lokuta masu amfani don aikin VPR.

Ayyukan VLOOKUP a cikin Excel - cikakken bayanin da haɗin gwiwa

To menene VPR? To, da farko, aikin Excel ne. Me take yi? Yana duba ƙimar da kuka ƙididdigewa kuma yana dawo da ƙimar daidai daga ɗayan ginshiƙi. Ta hanyar fasaha, VPR yana duba ƙima a cikin ginshiƙin farko na kewayon da aka bayar kuma ya dawo da sakamakon daga wani ginshiƙi a jere ɗaya.

A cikin aikace-aikacen gama gari, aikin VPR yana bincika rumbun adana bayanai don wani abin ganowa na musamman da aka ba shi kuma ya fitar da wasu bayanai masu alaƙa da su daga ma'ajin bayanai.

Harafi na farko a cikin sunan aiki VPR (VLOOKUP) yana nufin Вa tsaye (Va tsaye). Da shi za ku iya rarrabewa VPR daga GPR (HLOOKUP), wanda ke neman ƙima a saman jere na kewayo - Гa kwance (Ha kwance).

aiki VPR Akwai a cikin Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, da Excel 2000.

Haɗin aikin VLOOKUP

aiki VPR (VLOOKUP) yana da ma'ana mai zuwa:

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

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

Kamar yadda kake gani, aiki VPR a cikin Microsoft Excel yana da zaɓuɓɓuka 4 (ko muhawara). Uku na farko wajibi ne, na ƙarshe na zaɓi ne.

dubawa (lookup_value) - ƙimar da za a nema. Wannan na iya zama ƙima (lamba, kwanan wata, rubutu) ko ma'anar tantanin halitta (wanda ke ɗauke da ƙimar dubawa), ko ƙimar da wani aikin Excel ya dawo dashi. Misali, wannan dabarar za ta nemi darajar 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Idan ƙimar binciken ta kasance ƙasa da ƙaramin ƙima a cikin ginshiƙi na farko na kewayon da ake duba sama, aikin VPR zai ba da rahoton kuskure #AT (#N/A).

katakarar (tebur) - guda biyu ko fiye na bayanai. Ka tuna, aikin VPR koyaushe yana neman ƙima a cikin ginshiƙi na farko na kewayon da aka bayar a cikin gardama katakarar (tebur). Kewayon da ake iya gani zai iya ƙunsar bayanai daban-daban, kamar rubutu, kwanan wata, lambobi, booleans. Aikin ba shi da hankali, ma'ana babba da ƙananan haruffa ana ɗaukarsu iri ɗaya ne. Don haka tsarinmu zai nemi darajar 40 a cikin sel daga A2 to A15, saboda A shine shafi na farko na kewayon A2:B15 da aka bayar a cikin gardama katakarar (tebur):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (column_number) shine adadin ginshiƙi a cikin kewayon da aka bayar inda za a dawo da ƙimar da ke cikin layin da aka samo. Ƙungiya mafi hagu a cikin kewayon da aka bayar shine 1, shafi na biyu shine 2, shafi na uku shine 3 da sauransu. Yanzu za ku iya karanta dukan dabara:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Formula neman ƙima 40 a cikin kewayon A2: a15 kuma yana mayar da madaidaicin ƙimar daga shafi na B (saboda B shine shafi na biyu a cikin kewayon A2:B15).

Idan darajar hujja col_index_num (lambar_lambar) ƙasa da 1sa'an nan VPR zai ba da rahoton kuskure #DARAJAR! (#DARAJAR!). Kuma idan ya fi adadin ginshiƙai a cikin kewayon katakarar (tebur), aikin zai dawo da kuskure #REF! (#LINK!).

range_lokup (range_lookup) - yana ƙayyade abin da za a nema:
- daidai wasa, dole ne hujja ta zama daidai KARYA (KARYA);
- m daidaito, jayayya daidai KODA GASKIYA (GASKIYA) ko ba a fayyace komai ba.
Wannan siga na zaɓi ne, amma yana da mahimmanci. Daga baya a cikin wannan koyawa akan VPR Zan nuna muku wasu misalan da ke bayanin yadda ake rubuta dabaru don nemo madaidaicin daidai da matches.

Misalai na VLOOKUP

Ina fatan aikin VPR zama dan kara bayyana a gare ku. Yanzu bari mu dubi wasu lokuta masu amfani VPR a cikin dabara tare da ainihin bayanai.

Yadda ake amfani da VLOOKUP don bincika a cikin wani takardar Excel

A aikace, ƙididdiga tare da aiki VPR ba kasafai ake amfani da su don nemo bayanai akan takardar aiki ɗaya ba. Mafi sau da yawa fiye da haka, za ku duba sama da dawo da daidaitattun dabi'u daga wani takardar.

Domin amfani da VPR, bincika a cikin wani takardar Microsoft Excel, Dole ne a cikin gardama katakarar (tebur) saka sunan takardar tare da alamar tsawa tare da kewayon sel. Misali, dabarar da ke gaba tana nuna cewa kewayon A2: b15 yana kan takardar mai suna Takardar2.

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

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

Tabbas, ba sai an shigar da sunan takardar da hannu ba. Kawai fara buga dabarar, kuma idan ya zo ga hujja katakarar (tebur), canza zuwa takardar da ake so kuma zaɓi kewayon sel da ake so tare da linzamin kwamfuta.

Dabarar da aka nuna a hoton da ke ƙasa tana neman rubutun “samfurin 1” a shafi na A (shine na 1st na kewayon A2:B9) akan takardar aiki prices.

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

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

Da fatan za a tuna cewa lokacin neman ƙimar rubutu, dole ne ku haɗa shi cikin alamomin ambato (""), kamar yadda aka saba yi a cikin ƙirar Excel.

Domin jayayya katakarar (tebur) yana da kyawawa a koyaushe a yi amfani da cikakkun bayanai (tare da alamar $). A wannan yanayin, kewayon bincike ba zai canza ba lokacin yin kwafin dabarar zuwa wasu sel.

Bincika a cikin wani littafin aiki tare da VLOOKUP

Don aiki VPR yayi aiki tsakanin littattafan aikin Excel guda biyu, kuna buƙatar tantance sunan littafin aiki a cikin maƙallan murabba'i kafin sunan takardar.

Misali, a ƙasa akwai dabarar da ke neman ƙimar 40 a kan takardar Takardar2 a cikin littafin Lambobi.xlsx:

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

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

Anan ita ce hanya mafi sauƙi don ƙirƙirar dabara a cikin Excel da VPRwanda ke danganta zuwa wani littafin aiki:

Bude littattafan biyu. Ba a buƙatar wannan, amma yana da sauƙi don ƙirƙirar dabara ta wannan hanya. Ba kwa son shigar da sunan littafin aiki da hannu, kuna? Bugu da kari, zai kare ka daga buga buga kuskure.
Fara buga aiki VPRkuma idan aka zo ga jayayya katakarar (tebur), canza zuwa wani littafin aiki kuma zaɓi kewayon bincike da ake buƙata a ciki.

Hoton hoton da ke ƙasa yana nuna dabara tare da saitin bincike zuwa kewayo a cikin littafin aiki PriceList.xlsx a kan takardar prices.

aiki VPR zai yi aiki ko da lokacin da kuka rufe littafin aikin da aka nema kuma cikakken hanyar zuwa fayil ɗin littafin aiki ya bayyana a cikin mashaya dabara, kamar yadda aka nuna a ƙasa:

Idan sunan littafin aikin ko takardar ya ƙunshi sarari, to dole ne a haɗa shi a cikin apostrophes:

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

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

Yadda ake amfani da kewayon mai suna ko tebur a cikin dabara tare da VLOOKUP

Idan kuna shirin amfani da kewayon bincike iri ɗaya a ayyuka da yawa VPR, za ka iya ƙirƙirar kewayon mai suna kuma shigar da sunansa a cikin dabarar azaman hujja katakarar (tebur).

Don ƙirƙirar kewayon mai suna, kawai zaɓi sel kuma shigar da sunan da ya dace a cikin filin Sunan rana, zuwa hagu na mashayin dabara.

Yanzu zaku iya rubuta wannan dabarar don nemo farashin samfur Samfurin 1:

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

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

Yawancin sunaye suna aiki don dukan littafin aikin Excel, don haka babu buƙatar saka sunan takardar don hujja katakarar (tebur), ko da dabara da kewayon bincike suna kan takaddun aiki daban-daban. Idan sun kasance a cikin littattafan aiki daban-daban, to kafin sunan kewayon kuna buƙatar tantance sunan littafin aikin, alal misali, kamar haka:

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

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

Don haka tsarin ya yi kama da bayyananne, yarda? Hakanan, yin amfani da jeri mai suna shine kyakkyawan madadin nassoshi cikakke saboda kewayon mai suna baya canzawa lokacin da kuka kwafi dabarar zuwa wasu sel. Wannan yana nufin cewa za ku iya tabbata cewa kewayon bincike a cikin dabara zai kasance daidai koyaushe.

Idan kun canza kewayon sel zuwa cikakken maƙunsar Excel ta amfani da umarnin Table (Table) tab sa (Saka), sannan lokacin da kuka zaɓi kewayon tare da linzamin kwamfuta, Microsoft Excel za ta ƙara sunaye ta atomatik (ko sunan tebur idan kun zaɓi tebur gaba ɗaya) zuwa dabarar.

Ƙarshen dabarar za ta yi kama da wani abu kamar haka:

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

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

Ko watakila ma kamar haka:

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

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

Lokacin amfani da jeri mai suna, hanyoyin haɗin zasu nuna sel iri ɗaya duk inda kuka kwafi aikin VPR cikin littafin aiki.

Amfani da Katunan Tsara a cikin Tsarin VLOOKUP

Kamar sauran ayyuka da yawa, VPR Kuna iya amfani da haruffan jakunkuna masu zuwa:

Alamar tambaya (?) - tana maye gurbin kowane hali guda.
Alamar alama (*) - tana maye gurbin kowane jerin haruffa.

Amfani da Wildcards a Ayyuka VPR na iya zama da amfani a lokuta da yawa, misali:

Lokacin da ba ku tuna ainihin rubutun da kuke buƙatar nemo ba.
Lokacin da kake son nemo wasu kalmomi waɗanda ke cikin abubuwan da ke cikin tantanin halitta. Sanin haka VPR bincika abubuwan da ke cikin tantanin halitta gaba ɗaya, kamar an kunna zaɓin Daidaita duka abun cikin tantanin halitta (Dukkan cell) a cikin daidaitaccen bincike na Excel.
Lokacin da tantanin halitta ya ƙunshi ƙarin sarari a farkon ko ƙarshen abun ciki. A cikin irin wannan yanayi, zaku iya ɗaukar kwakwalwar ku na dogon lokaci, kuna ƙoƙarin gano dalilin da yasa tsarin ba ya aiki.

Misali 1: Neman rubutu wanda ya fara ko ya ƙare da wasu haruffa

Bari mu ce kuna son nemo takamaiman abokin ciniki a cikin bayanan da aka nuna a ƙasa. Ba ku tuna sunansa na ƙarshe, amma kun san cewa yana farawa da “ack”. Ga dabarar da za ta yi aikin daidai:

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

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

Yanzu da kun tabbata kun sami sunan daidai, zaku iya amfani da wannan dabara don nemo adadin da wannan abokin ciniki ya biya. Don yin wannan, kawai canza hujja na uku na aikin VPR zuwa lambar shafi da ake so. A cikin yanayinmu, wannan shafi ne C (na uku a cikin kewayon):

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

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

Ga wasu ƙarin misalan tare da jakunkuna:

~ Nemo suna mai ƙarewa cikin "mutum":

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

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

~ Nemo suna wanda ya fara da "ad" kuma ya ƙare da "ɗa":

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

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

~ Mun sami sunan farko a cikin jerin, wanda ya ƙunshi haruffa 5:

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

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

Don aiki VPR tare da ƙaƙƙarfan ƙaƙƙarfan aiki daidai, azaman hujja ta huɗu yakamata ku yi amfani da su koyaushe KARYA (KARYA). Idan kewayon binciken ya ƙunshi ƙima fiye da ɗaya waɗanda suka yi daidai da sharuɗɗan nema tare da kati, to za a dawo da ƙimar farko da aka samu.

Misali 2: Haɗa katunan daji da nassoshin tantanin halitta a cikin tsarin VLOOKUP

Yanzu bari mu kalli wani misali mai rikitarwa na yadda ake nema ta amfani da aikin VPR ta darajar a cikin tantanin halitta. Ka yi tunanin cewa shafi A jerin maɓallan lasisi ne, kuma shafi B jerin sunayen da ke da lasisi. Bugu da ƙari, kuna da ɓangaren (haruffa da yawa) na wasu nau'ikan maɓallin lasisi a cikin cell C1, kuma kuna son nemo sunan mai shi.

Ana iya yin wannan ta amfani da dabara mai zuwa:

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

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

Wannan dabarar tana duba ƙimar daga cell C1 a cikin kewayon da aka ba kuma ya dawo da ƙimar daidai daga shafi na B. Lura cewa a cikin gardama ta farko, muna amfani da ampersand (&) harafin kafin da bayan bayanan tantanin halitta don haɗa kirtan rubutu.

Kamar yadda kake gani a cikin hoton da ke ƙasa, aikin VPR ya dawo "Jeremy Hill" saboda maɓallin lasisinsa ya ƙunshi jerin haruffa daga cell C1.

Lura cewa hujjar katakarar (tebur) a cikin hoton da ke sama ya ƙunshi sunan tebur (Table7) maimakon fayyace kewayon sel. Wannan shi ne abin da muka yi a misalin da ya gabata.

Daidai ko madaidaicin wasa a cikin aikin VLOOKUP

Kuma a ƙarshe, bari mu dubi hujja ta ƙarshe da aka ƙayyade don aikin VPR - range_lokup (tazara_view). Kamar yadda aka ambata a farkon darasin, wannan hujja tana da matukar muhimmanci. Kuna iya samun sakamako daban-daban a cikin tsari iri ɗaya tare da ƙimar sa KODA GASKIYA (GASKIYA) ko KARYA (KARYA).

Da farko, bari mu gano abin da Microsoft Excel ke nufi ta daidai da matches masu ƙima.

Idan hujja range_lokup (range_lookup) daidai yake da KARYA (KARYA), dabarar tana neman daidai daidai gwargwado, watau daidai da darajar da aka bayar a cikin gardama dubawa (lokup_darajar). Idan a cikin ginshiƙin farko na kewayon tiya_array (tebur) ya ci karo da ƙima biyu ko fiye waɗanda suka dace da hujjar dubawa (search_value), sannan za a zabi na farko. Idan ba a sami ashana ba, aikin zai ba da rahoton kuskure #AT (#N/A). Misali, dabara mai zuwa za ta ba da rahoton kuskure #AT (#N/A) idan babu ƙima a cikin kewayon A2: A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Idan hujja range_lokup (range_lookup) daidai yake da KODA GASKIYA (GASKIYA), dabarar tana neman kusancin wasa. Fiye da daidai, fara aikin VPR yana neman madaidaicin wasa, kuma idan ba'a samu ba, zaɓi kusan ɗaya. Ƙimar daidaitawa ita ce mafi girman ƙimar da ba ta wuce ƙimar da aka ƙayyade a cikin gardama ba. dubawa (lokup_darajar).

Idan hujja range_lokup (range_lookup) daidai yake da KODA GASKIYA (GASKIYA) ko ba a ƙayyade ba, to, ƙimar da ke cikin ginshiƙi na farko na kewayon yakamata a jera su cikin tsari mai hawa, wato, daga ƙarami zuwa babba. In ba haka ba, aikin VPR na iya dawo da sakamakon kuskure.

Don ƙarin fahimtar mahimmancin zaɓi KODA GASKIYA (GASKIYA) ko KARYA (KARYA), bari mu kalli wasu ƙarin dabaru tare da aikin VPR kuma duba sakamakon.

Misali 1: Neman Daidaitaccen Matches tare da VLOOKUP

Kamar yadda kuke tunawa, don bincika ainihin wasa, hujja ta huɗu na aikin VPR kamata komai KARYA (KARYA).

Bari mu koma teburin daga misalin farko kuma mu gano wacce dabba za ta iya motsawa cikin sauri 50 mil awa daya. Na yi imani cewa wannan dabarar ba za ta haifar muku da matsala ba:

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

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

Lura cewa kewayon binciken mu (shafi A) ya ƙunshi ƙima biyu 50 - a cikin sel A5 и A6. Formula tana dawo da ƙima daga tantanin halitta B5. Me yasa? Domin lokacin neman ainihin wasa, aikin VPR yana amfani da ƙimar farko da aka samo wanda yayi daidai da wanda ake nema.

Misali 2: Amfani da VLOOKUP don Nemo Kimanta Matches

Lokacin da kake amfani da aikin VPR don nemo kusan ashana, watau lokacin da gardama range_lokup (range_lookup) daidai yake da KODA GASKIYA (GASKIYA) ko kuma an tsallake, abu na farko da kuke buƙatar yi shine tsara kewayon ta shafi na farko a cikin tsari mai hawa.

Wannan yana da mahimmanci saboda aikin VPR yana dawo da ƙima mafi girma na gaba bayan wanda aka bayar, sannan binciken ya tsaya. Idan kun yi sakaci daidai rarrabuwa, zaku ƙare da sakamako mai ban mamaki ko saƙon kuskure. #AT (#N/A).

Yanzu zaku iya amfani da ɗaya daga cikin waɗannan hanyoyin:

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

Kamar yadda kake gani, ina so in gano wane daga cikin dabbobin yake da mafi kusancin gudu 69 mil awa daya. Kuma ga sakamakon aikin ya dawo gare ni VPR:

Kamar yadda kuke gani, dabarar ta dawo da sakamako Kwaro (Antelope), wanda gudunsa 61 mil a kowace awa, kodayake jerin sun haɗa da rabbi (Cheetah) mai gudu da sauri 70 mil a kowace awa, kuma 70 yana kusa da 69 fiye da 61, ko ba haka ba? Me yasa hakan ke faruwa? Domin aikin VPR lokacin neman kusan wasa, yana mayar da mafi girman kimar da ba ta fi wadda ake nema ba.

Ina fatan waɗannan misalan sun ba da haske kan aiki tare da aikin VPR a Excel, kuma ka daina kallonta a matsayin baƙo. Yanzu ba zai yi zafi ba a taƙaice maimaita mahimman abubuwan abubuwan da muka yi nazari don gyara shi a cikin ƙwaƙwalwar ajiya.

VLOOKUP a cikin Excel - kuna buƙatar tunawa da wannan!

aiki VPR Excel ba zai iya duba hagu ba. Koyaushe yana neman ƙima a cikin ginshiƙin hagu na kewayon da aka bayar ta gardama katakarar (tebur).
A cikin aiki VPR duk dabi'u ba su da hankali, watau ƙanana da manyan haruffa daidai suke.
Idan ƙimar da kuke nema ta yi ƙasa da mafi ƙarancin ƙima a cikin ginshiƙi na farko na kewayon da ake duba sama, aikin VPR zai ba da rahoton kuskure #AT (#N/A).
Idan hujja ta 3 col_index_num (lambar_lambar) ƙasa da 1aiki VPR zai ba da rahoton kuskure #DARAJAR! (#DARAJAR!). Idan ya fi yawan ginshiƙai a cikin kewayon katakarar (tebur), aikin zai ba da rahoton kuskure #REF! (#LINK!).
Yi amfani da cikakkun bayanan tantanin halitta a cikin gardama katakarar (tebur) domin a kiyaye madaidaicin kewayon bincike lokacin yin kwafin dabarar. Gwada amfani da jeri mai suna ko tebur a cikin Excel azaman madadin.
Lokacin yin kusan binciken wasa, tuna cewa ginshiƙi na farko a cikin kewayon da kuke nema dole ne a jera su cikin tsari mai hawa.
A ƙarshe, ku tuna mahimmancin hujja na huɗu. Yi amfani da dabi'u KODA GASKIYA (GASKIYA) ko KARYA (KARYA) da gangan kuma zaka rabu da yawan ciwon kai.

A cikin labarai masu zuwa na koyawa aikin mu VPR a cikin Excel, za mu koyi ƙarin misalan ci-gaba, kamar yin lissafin daban-daban ta amfani da su VPR, Cire ƙima daga ginshiƙai masu yawa, da ƙari. Na gode da karanta wannan koyawa kuma ina fatan in sake ganin ku mako mai zuwa!

Ayyukan VLOOKUP a cikin Excel - Jagorar Mafari: Daidaitawa da Misalai