01 na 03
Nemo Matakan da suka dace don Bayanan da ke cikin Excel's VLOOKUP
Yaya Ayyukan VLOOKUP ke aiki
Ayyukan VLOOKUP na Excel, wanda yake tsaye don neman dubawa , za a iya amfani dasu don bincika bayanan da aka samo a cikin tebur na bayanan ko bayanai.
VLOOKUP kullum yana dawowa guda guda na bayanai a matsayin fitarwa. Ta yaya wannan yake:
- Kuna samar da suna ko lookup_value wanda ya gaya wa VLOOKUP wanda jere ko rikodin lissafin bayanai don bincika bayanai da ake so
- Kuna samar da lambar shafi - da aka sani da col_index_num - na bayanan da kuke nema
- Ayyukan suna nema ga lookup_value a cikin sashin farko na layin bayanai
- VLOOKUP sa'an nan kuma gano wuri kuma ya sake dawo da bayanan da kake nema daga wani filin na wannan rikodin ta amfani da lambar mahadar da aka bayar
Kaddamar da Bayanan Na farko
Ko da yake ba a koyaushe ake buƙata ba, yana da mafi kyawun mafi dacewa da farko da zaɓin kewayon bayanan da VLOOKUP ke nema ta hanyar amfani da maɓallin farko na kewayon don maɓallin iri.
Idan ba'a samo bayanan ba, VLOOKUP zai iya dawo da sakamako mara kyau.
Hanyoyin Sakamakon Tasirin VLOOKUP da Magana
Haɗin aikin yana nufin layout na aikin kuma ya haɗa da sunan aikin, shafuka, da muhawara .
Hadawa don aikin VLOOKUP shine:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Sakamakon _value - (da ake bukata) darajar don bincika - kamar yawan da aka sayar a cikin hoton da ke sama
table_array - (da ake buƙatar) wannan shi ne teburin bayanan da VLOOKUP yayi nema don neman bayanin da kake bayan.
- Tabbatarwa dole ne kunshi akalla biyu ginshiƙan bayanai
- Shafin farko yana ƙunshi lookup_value
col_index_num - (da ake buƙata) lambar mahaɗin darajar da kake son samu.
- Lambar ta fara tare da shafi na search_key kamar shafi na 1
- Idan an saita col_index_num zuwa lambar da ta fi girma fiye da adadin ginshiƙai da aka zaɓa a cikin gardamar table_array a #REF! An dawo da kuskure ta hanyar aikin
range_lookup - (na zaɓi) ya nuna ko ko dai ba a kewayon kewayawa ba.
- Ana amfani da bayanan da ke cikin shafi na farko a matsayin maɓallin kama
- Ƙarin Boolean - TRUE ko FALSE ne kawai lambobin karɓa
- Idan aka cire, an saita darajar zuwa TRUE ta hanyar tsoho
- Idan an saita zuwa TRUE ko kuma an cire shi kuma kuma ba a rarraba shafi na farko ba, ba za a iya samun sakamako mara kyau ba.
- Idan an saita zuwa TRUE ko kuma an cire shi kuma ba a samo daidai ba don neman _value , matsala mafi kusa da girman ko girman ana amfani dashi azaman search_key
- Idan aka saita zuwa FALSE, VLOOKUP kawai ya yarda da ainihin wasa don neman ra'ayin _value . Idan akwai matakan daidaitattun lambobi, an mayar da lambar farko ta daidai
- Idan aka saita zuwa FALSE kuma ba a sami darajar daidai ba don search_key an samo, an mayar da kuskuren N / A ta wurin aikin
Misali: Nemi Ƙimar Dama don Abin da aka saya
Misali a cikin hoton da ke sama yana amfani da aikin VLOOKUP don samun rabon rangwame wanda ya bambanta dangane da yawan kayan da aka saya.
Misali ya nuna cewa rangwame don sayen abubuwa 19 shine 2%. Wannan shi ne saboda Littafin da aka ƙunshi jeri na dabi'u. A sakamakon haka, VLOOKUP ba zai iya samun ainihin wasa ba. Maimakon haka, dole ne a sami kimanin kimanin kusan don sake dawo da daidai rangwame.
Don samun matakan dacewa:
- raba da bayanai a cikin table_array a cikin tsari mai girma;
- saita shawara na range_lookup zuwa TRUE
A cikin misalin, ana amfani da wannan tsari da ke dauke da aikin VLOOKUP don samun rangwame don yawan kayan da aka saya.
= KASHI (C2, $ C $ 5: $ D $ 8,2, TRUE)
Ko da yake wannan ƙira za a iya buga shi cikin tantanin halitta, wani zaɓi, kamar yadda aka yi amfani da matakan da aka lissafa a ƙasa, shine don amfani da maganganun aikin don shigar da muhawara.
- Yin amfani da maganganun maganganu sau da yawa yakan sa ya fi sauƙi don shigar da muhawarar aiki daidai.
Ana buɗe akwatin maganganu na VLOOKUP
Matakai da aka yi amfani da su don shigar da aikin VLOOKUP da aka nuna a cikin hoton da ke cikin sel B2 sune:
- Danna kan tantanin halitta B2 don sa shi tantanin halitta mai aiki - wurin da aka nuna sakamakon VLOOKUP
- Danna kan shafukan Formulas .
- Zabi Binciken & Magana daga ribbon don buɗe jerin abubuwan da aka sauke aikin
- Danna kan VLOOKUP a cikin jerin don kawo akwatin maganganun aikin
02 na 03
Shigar da muhawarar aiki ta Excel ta Tashar ta Excel
Bayyanawa ga Siffofin Siffar
Maganganun aikin VLOOKUP an shigar da su a cikin layi na tsabar maganganu kamar yadda aka nuna a cikin hoto a sama.
Za'a iya yin amfani da labaran da ake amfani dashi a matsayin jayayya a cikin layin daidai, ko, kamar yadda aka yi a matakan da ke ƙasa, yana nunawa, wanda ya kunsa ya nuna hasashen da ake buƙatar da shi tare da maɓallin linzamin kwamfuta, za'a iya amfani da su don shigar da su cikin akwatin zane .
Amfanin amfani da ma'ana sun hada da:
- Yana da sauri fiye da bugawa;
- An yi kuskuren kuskuren shigar da saitunan sakonni daidai.
Yin Amfani da Maɗaukaki da Ƙarshe Cell References tare da Magana
Ba abin mamaki ba ne don amfani da takardun kofi na VLOOKUP don komawa bayanan bayanai daga wannan teburin bayanai. Don yin sauƙin yin wannan, sau da yawa VLOOKUP za a iya kwafe daga ɗayan tantanin halitta zuwa wani. Lokacin da aka kofe ayyuka zuwa wasu kwayoyin, dole ne a dauki kula don tabbatar da cewa sakamakon haɗin gizon ya haifar daidai ne da sabon wuri.
A cikin hoton da ke sama, alamomin dollar ( $ ) ke kewaye da bayanan salula don maganganun launi na nuna cewa sun kasance cikakkun bayanai na labaran , wanda ke nufin ba za su canza ba idan an kwafi aikin zuwa wani cell. Wannan yana da kyawawa a matsayin ƙananan magunguna na VLOOKUP za su yi la'akari da wannan tebur na bayanai a matsayin tushen bayanin.
Tambayar tantanin halitta da aka yi amfani da shi don lookup_value, a gefe guda , ba a kewaye da alamomi na dollar, wanda ya sa ya zama maƙillan zumunta. Siffofin bayanan salula sun canza lokacin da aka kofe don su nuna sabon wuri da suka dace da matsayi na bayanai da suka koma.
Shigar da Magana Magana
- Danna kan layi na _value a cikin akwatin maganganun VLOOKUP
- Danna maɓallin C2 a cikin takardun aiki don shigar da wannan tantanin halitta kamar ƙudurin search_key
- Danna kan shafin Table_array na akwatin maganganu
- Karkatar da sassan C5 zuwa D8 a cikin takardun aiki don shigar da wannan azaman azaman Table_array hujja - ba a haɗa da rubutun gadi ba
- Latsa maɓalli F4 a kan keyboard don canza layin zuwa cikakkun bayanai
- Danna kan hanyar Col_index_num na akwatin maganganu
- Rubuta 2 a kan wannan layin a matsayin shaida na Col_index_num , tun da farashin rangwame na samuwa a shafi na 2 na jumlar Table_array
- Danna kan Range_lookup line na maganganu
- Rubuta kalma Gaskiya kamar matsayin Range_lookup
- Latsa maɓallin shigarwa a kan keyboard don rufe akwatin maganganu kuma komawa zuwa aikin aiki
- Amsar 2% (yawan rangwame don yawan sayan da aka saya) ya kamata ya bayyana a cell D2 na takardar aiki
- Lokacin da ka danna kan tantanin D2, cikakken aikin = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) ya bayyana a cikin maɓallin tsari a sama da takardun aiki
Me yasa VLOOKUP ta dawo 2% a matsayin sakamakon
- A cikin misali, ɗakunan Kayan ba su ƙunshi daidai matsala don ƙimar search_key na 19 ba.
- Tun da aka ƙaddamar da gardamar da ba'a da ita ga TRUE, VLOOKUP zai sami kimanin dacewa zuwa darajar search_key .
- Ƙimar da ya fi kusan a girman da yake da ƙananan ƙimar na search_key na 19 shi ne 11.
- Saboda haka, VLOOKUP, yana duban rangwamen kashi a cikin jere 11, kuma, a sakamakon haka, ya sake dawo da kashi 2%.
03 na 03
Excel VLOOKUP Ba aiki: # N / A da #REF Kurakurai
Saƙonnin kuskuren VLOOKUP
Saƙonnin kuskure masu zuwa suna hade da VLOOKUP.
A # N / A ("darajar ba samuwa ba") An nuna kuskuren idan:
- Ba a samo _value ba a cikin shafi na farko na jigilar lamarin
- Tambayar Table_array ba daidai ba ce. Alal misali, jayayya na iya haɗawa ginshiƙai maras amfani a gefen hagu na kewayon
- An saita shawara na Range_lookup zuwa FALSE kuma daidai matsala don binciken bincike_key ba za'a iya samuwa a cikin sashen farko na kewayon ba
- An saita shawara na Range_lookup zuwa TRUE kuma dukkanin dabi'un da ke cikin sashin farko na kewayon ya fi girma akan search_key
A #REF! ("tunani daga cikin kewayon") An nuna kuskuren idan:
- Shawarar Col_index_num ya fi yawan ginshiƙai a Table_array