Contents
Wannan ƙaramin koyawa yana bayanin yadda ake yin aiki VPR (VLOOKUP) mai hankali, yana nuna wasu dabaru da yawa waɗanda Excel za su iya bincika ta hanyar da ta dace, kuma suna nuna ƙarfi da raunin kowane aiki.
Ina tsammanin kowane mai amfani da Excel ya san aikin da ke yin bincike a tsaye. Haka ne, aiki ne VPR. Duk da haka, mutane kaɗan ne suka san hakan VPR ba shi da hankali, watau ƙananan haruffa da manyan haruffa iri ɗaya ne da shi.
Ga misali mai sauri da ke nuna rashin iyawa VPR gane rajista. A ce a cikin tantanin halitta A1 ya ƙunshi darajar “lissafin” da tantanin halitta A2 - "Bill", dabara:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… zai dakatar da bincikensa akan “lissafin kudi” tunda wannan ƙimar ta zo farko a lissafin, kuma ta cire ƙimar daga tantanin halitta B1.
Daga baya a cikin wannan labarin, zan nuna muku yadda ake yi VPR harka m. Bugu da ƙari, za mu koyi ƴan ƙarin ayyuka waɗanda za su iya yin bincike mai mahimmanci a cikin Excel.
Za mu fara da mafi sauƙi - Duba (LOOKUP) da SUMPRODUCT (SUMPRODUCT), wanda, rashin alheri, yana da iyakoki da yawa. Na gaba, za mu yi la'akari da ƙayyadaddun dabarar da ta fi rikitarwa INDEX+MATCH (INDEX + MATCH), wanda ke aiki mara aibi a kowane yanayi kuma tare da kowane saitin bayanai.
Ayyukan VLOOKUP yana da hankali
Kamar yadda kuka riga kuka sani, aikin da aka saba VPR yanayin rashin hankali ne. Duk da haka, akwai hanyar da za a sanya shi a hankali. Don yin wannan, kuna buƙatar ƙara ginshiƙi na taimako zuwa teburin, kamar yadda aka nuna a cikin misali mai zuwa.
A ce a cikin wani shafi B akwai masu gano samfur (Abu) kuma kuna son cire farashin samfurin da sharhin da ya dace daga ginshiƙan. C и D. Matsalar ita ce masu ganowa sun ƙunshi ƙananan haruffa biyu da manyan haruffa. Misali, ƙimar tantanin halitta B4 (001Tvci3u) da B5 (001Tvci3U) ya bambanta kawai a yanayin halin ƙarshe, u и U bi da bi.
Kamar yadda zaku iya tunanin, tsarin bincike na yau da kullun
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
zai dawo $ 90, tun da darajar 001Tvci3u yana cikin kewayon bincike kafin 001Tvci3U. Amma ba abin da muke bukata ba ke nan, ko?
Don bincika tare da aiki VPR a cikin yanayin yanayin Excel, dole ne ku ƙara ginshiƙi mai taimako kuma ku cika sel ɗinsa tare da wannan dabarar (inda B shine ginshiƙin neman):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Wannan dabarar tana karya ƙimar da ake so zuwa haruffa daban, ta maye gurbin kowane hali da lambar sa (misali, maimakon A a 65, maimakon a code 97) sa'an nan kuma ya haɗa waɗannan lambobin zuwa lambobi na musamman.
Bayan haka, muna amfani da aiki mai sauƙi VPR don bincike mai mahimmanci:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Ayyukan da ya dace na aikin VPR rashin sanin yakamata ya dogara da abubuwa biyu:
- Dole ne ginshiƙin mai taimako ya zama ginshiƙin hagu a cikin kewayon da ake iya gani.
- Dole ne ƙimar da kuke nema ta ƙunshi lambar haruffa maimakon ainihin ƙimar.
Yadda ake amfani da aikin CODE daidai
Tsarin da aka saka a cikin sel na ginshiƙi na taimako yana ɗauka cewa duk ƙimar bincikenku suna da adadin haruffa iri ɗaya. Idan ba haka ba, to kuna buƙatar sanin mafi ƙanƙanta da manyan lambobi kuma ƙara yawan fasali IFEROR (IFERROR) haruffa nawa ne bambanci tsakanin mafi guntu da ƙimar da aka nema mafi tsayi.
Misali, idan mafi guntu ƙimar bincike shine haruffa 3 kuma mafi tsayi shine haruffa 5, yi amfani da wannan dabara:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Don aiki PSTR (MID) Kuna bayar da hujjoji masu zuwa:
- Hujja ta 1- rubutu (rubutu) shine bayanin rubutu ko tantanin halitta mai ɗauke da haruffan da za a ciro (a yanayin mu shine B2)
- Hujja ta 2- farawa_num (start_position) shine matsayi na farkon waɗannan haruffan da za a ciro. ka shiga 1 a cikin aikin farko PSTR, 2 – a cikin aiki na biyu PSTR da dai sauransu.
- Hujja ta 3- lamba_chars (lambar_halayen_halaye) - Yana ƙayyadaddun adadin haruffa don ciro daga rubutun. Tunda harafi 1 kawai muke buƙatar kowane lokaci, a cikin duk ayyuka muna rubutawa 1.
LIMITATIONS: aiki VPR ba shine mafi kyawun mafita don bincike-bincike mai ma'ana a cikin Excel ba. Na farko, ana buƙatar ƙarin ginshiƙi na taimako. Abu na biyu, dabarar tana yin kyakkyawan aiki ne kawai idan bayanan sun yi kama, ko kuma an san ainihin adadin haruffa a cikin ƙimar da aka bincika. Idan wannan ba shine batun ku ba, yana da kyau a yi amfani da ɗayan mafita waɗanda muka nuna a ƙasa.
Ayyukan LOOKUP don bincike mai mahimmanci
aiki Duba (LOOKUP) masu alaka VPR, duk da haka rubutun sa yana ba da damar yin bincike mai ma'ana ba tare da ƙara ginshiƙi na taimako ba. Don yin wannan, yi amfani da Duba hade da aikin SAURARA (GASKIYA).
Idan muka ɗauki bayanan daga misalin da ya gabata (ba tare da ginshiƙan taimako ba), to wannan dabarar za ta jimre da aikin:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Binciken tsari a cikin kewayo A2: a7 daidai daidai da ƙimar tantanin halitta F2 mai hankali kuma yana dawo da ƙimar daga shafi na B na jere ɗaya.
Kamar VPRaiki Duba yana aiki daidai da rubutu da ƙimar lambobi, kamar yadda kuke gani a hoton da ke ƙasa:
Muhimmin! Domin aikin Duba yayi aiki daidai, ƙimar da ke cikin ginshiƙi ya kamata a jera su cikin tsari mai hawa, watau daga ƙarami zuwa babba.
Bari in ɗan yi bayanin yadda aikin yake aiki SAURARA a cikin dabarar da aka nuna a sama, saboda wannan shine mahimmin batu.
aiki SAURARA yana kwatanta ƙimar rubutu guda biyu a cikin mahawara ta 1 da ta 2 kuma yana mayar da GASKIYA idan sun kasance daidai, ko KARYA idan ba haka ba. Yana da mahimmanci a gare mu cewa aikin SAURARA harka m.
Bari mu ga yadda tsarin mu ke aiki DUBI + EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- aiki SAURARA yana kwatanta ƙimar tantanin halitta F2 tare da duk abubuwa a cikin wani shafi A (A2: A7). Yana dawo da GASKIYA idan an sami ainihin wasa, in ba haka ba KARYA.
- Tun da kun ba da hujjar aikin farko Duba darajar GASKIYA, yana fitar da madaidaicin ƙimar daga ƙayyadadden ginshiƙi (a cikin yanayinmu, shafi na B) kawai idan an sami daidaitaccen wasa, mai hankali.
Ina fatan wannan bayanin ya fito fili kuma yanzu kun fahimci babban ra'ayi. Idan haka ne, to ba za ku sami matsala tare da wasu ayyuka waɗanda za mu ƙara yin nazari ba, saboda. dukkansu suna aiki akan ka'ida ɗaya.
LIMITATIONS: Dole ne a jera bayanan da ke cikin ginshiƙin nema a cikin tsari mai hawa.
SUMPRODUCT – yana nemo ƙimar rubutu, yanayin yanayi, amma yana dawo da lambobi kawai
Kamar yadda kuka riga kuka fahimta daga taken. SUMPRODUCT (SUMPRODUCT) wani aikin Excel ne wanda zai taimaka maka yin bincike mai ma'ana, amma zai dawo da ƙimar lambobi kawai. Idan wannan zaɓin bai dace da ku ba, to zaku iya ci gaba nan da nan zuwa gungumen INDEX+MATCH, wanda ke ba da mafita ga kowane hali da kowane nau'in bayanai.
Da farko, bari in yi bayani a taƙaice ma’anar wannan aikin, wannan zai taimaka muku ƙarin fahimtar dabarar da ke tafe.
aiki SUMPRODUCT yana ninka abubuwan da aka bayar kuma yana mayar da jimillar sakamakon. Maganar magana tayi kama da haka:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Tun da muna buƙatar bincike mai mahimmanci, muna amfani da aikin SAURARA (EXACT) daga misalin da ya gabata azaman ɗayan masu haɓakawa:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Kamar yadda kuka tuna, SAURARA yana kwatanta ƙimar tantanin halitta F2 tare da duk abubuwa a cikin wani shafi A. Yana dawo da GASKIYA idan an sami ainihin wasa, in ba haka ba KARYA. A cikin ayyukan lissafi, Excel yana ɗaukar GASKIYA azaman 1, da KARYA don 0Ƙari SUMPRODUCT yana ninka waɗannan lambobi kuma yana tara sakamakon.
Ba a kirga sifili domin idan aka ninka su koyaushe suna bayarwa 0. Bari mu dubi abin da ke faruwa idan daidaitaccen wasa a cikin ginshiƙi A samu ya dawo 1… Aiki SUMPRODUCT yana ninka lamba a cikin ginshiƙi B on 1 kuma ya dawo da sakamakon - daidai lamba ɗaya! Wannan saboda sakamakon sauran samfuran ba su da sifili, kuma ba sa shafar adadin da aka samu.
Abin takaici aikin SUMPRODUCT ba zai iya aiki tare da ƙimar rubutu da kwanan wata kamar yadda ba za a iya ninka su ba. A wannan yanayin, zaku karɓi saƙon kuskure #DARAJAR! (#VALUE!) kamar a cikin tantanin halitta F4 a hoton da ke kasa:
LIMITATIONS: Yana dawo da ƙimar lamba kawai.
INDEX + MATCH – bincike mai hankali ga kowane nau'in bayanai
A ƙarshe, muna kusa da tsarin bincike mara iyaka kuma mai hankali wanda ke aiki tare da kowane saitin bayanai.
Wannan misalin ya zo na ƙarshe, ba saboda an bar mafi kyau don kayan zaki ba, amma saboda ilimin da aka samu daga misalan da suka gabata zai taimake ka ka fahimci dabara mai mahimmanci da sauri da sauri. INDEX+MATCH (INDEX+MATCH).
Kamar yadda wataƙila kuka zaci, haɗin ayyuka MORE BAYYANA и INDEX ana amfani da shi a cikin Excel azaman mafi sassauƙa kuma mai ƙarfi madadin don VPR. Labarin Amfani da INDEX da MATCH maimakon VLOOKUP zai bayyana daidai yadda waɗannan ayyukan ke aiki tare.
Zan sake dawo da mahimman abubuwan:
- aiki MORE BAYYANA (MATCH) yana neman ƙima a cikin kewayon da aka bayar kuma ya dawo da matsayinsa na dangi, wato, jere da/ko lambar shafi;
- Na gaba, aikin INDEX (INDEX) yana dawo da ƙima daga ƙayyadadden shafi da/ko jere.
Don tsari INDEX+MATCH zai iya bincika harka-hankali, kawai kuna buƙatar ƙara aiki guda ɗaya gareshi. Ba shi da wuya a sake tunanin menene shi SAURARA (EXACT):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
A cikin wannan tsari SAURARA yana aiki kamar yadda yake tare da aikin Duba, kuma yana bada sakamako iri ɗaya:
Lura cewa dabarar INDEX+MATCH rufaffen takalmin gyaran kafa mai lanƙwasa tsari ne na tsararru kuma dole ne ka kammala ta latsa Ctrl + Shigar + Shigar.
Me yasa INDEX+MATCH shine mafi kyawun mafita don bincike mai ma'ana?
Babban abũbuwan amfãni daga cikin dam INDEX и MORE BAYYANA:
- Baya buƙatar ƙara ginshiƙi na taimako, sabanin VPR.
- Baya buƙatar rarrabuwar ginshiƙin bincike, sabanin Duba.
- Yana aiki tare da kowane nau'in bayanai - lambobi, rubutu da kwanan wata.
Wannan dabarar tana da kama da kamala, ko ba haka ba? A gaskiya, ba haka ba ne. Kuma shi ya sa.
A ɗauka cewa tantanin halitta a cikin ginshiƙin dawowa da ke da alaƙa da ƙimar binciken ba komai bane. Menene sakamakon dabara zai dawo? A'a? Bari mu ga abin da ainihin dabarar ke dawowa:
Kash, dabarar ta dawo sifili! Wannan bazai zama babbar matsala ba idan kuna aiki tare da tsantsar ƙimar rubutu. Duk da haka, idan tebur ya ƙunshi lambobi, ciki har da sifilin "ainihin", wannan ya zama matsala.
A haƙiƙa, duk sauran hanyoyin bincike (VLOOKUP, LOOKUP, da SUMPRODUCT) da muka tattauna a baya suna yin hakan. Amma kuna son cikakkiyar dabara, daidai?
Don yin yanayin yanayin dabara INDEX+MATCH cikakke, sanya shi cikin aiki IF (IF) wanda zai gwada tantanin halitta tare da ƙimar dawowa kuma ya dawo da komai mara kyau idan babu komai:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
A cikin wannan tsari:
- B ginshiƙi ne mai ƙimar dawowa
- 1+ lamba ce da ke juya matsayin dangi na tantanin halitta da aikin ya dawo MORE BAYYANA, zuwa ainihin adireshin tantanin halitta. Misali, a cikin aikinmu MORE BAYYANA bincike tsararru bayar A2: a7, wato, matsayin dangi na tantanin halitta A2 so 1, domin shi ne na farko a cikin tsararru. Amma ainihin matsayi na tantanin halitta A2 a cikin ginshiƙi ne 2, don haka mu ƙara 1don gyara bambanci da samun aikin GASKIYA (INDIRECT) ya dawo da ƙimar daga tantanin halitta da ake so.
Hotunan da ke ƙasa suna nuna ingantacciyar dabarar da ta dace INDEX+MATCH A cikin aiki. Yana mayar da sakamako mara komai idan tantanin da aka dawo ya zama fanko.
Na sake rubuta dabarar zuwa ginshiƙai B:Ddon dacewa da mashaya dabara akan hoton allo.
Formula ya dawo 0idan tantanin da aka dawo ya ƙunshi sifili.
Idan kuna son hanyar haɗin gwiwa INDEX и MORE BAYYANA nuna wasu saƙon lokacin da ƙimar dawowar ta zama fanko, zaku iya rubuta shi a cikin ƙididdiga ta ƙarshe ("") na dabarar, misali, kamar haka:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
