Yadda za a Samu Bayanai tare da VLOOKUP a Excel

01 na 03

Nemo Matakan da suka dace don Bayanan da ke cikin Excel's VLOOKUP

Nemi farashin farashin tare da VLOOKUP. © Ted Faransanci

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:

  1. Kuna samar da suna ko lookup_value wanda ya gaya wa VLOOKUP wanda jere ko rikodin lissafin bayanai don bincika bayanai da ake so
  2. Kuna samar da lambar shafi - da aka sani da col_index_num - na bayanan da kuke nema
  3. Ayyukan suna nema ga lookup_value a cikin sashin farko na layin bayanai
  4. 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.

col_index_num - (da ake buƙata) lambar mahaɗin darajar da kake son samu.

range_lookup - (na zaɓi) ya nuna ko ko dai ba a kewayon kewayawa ba.

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:

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.

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:

  1. Danna kan tantanin halitta B2 don sa shi tantanin halitta mai aiki - wurin da aka nuna sakamakon VLOOKUP
  2. Danna kan shafukan Formulas .
  3. Zabi Binciken & Magana daga ribbon don buɗe jerin abubuwan da aka sauke aikin
  4. Danna kan VLOOKUP a cikin jerin don kawo akwatin maganganun aikin

02 na 03

Shigar da muhawarar aiki ta Excel ta Tashar ta Excel

Shigar da Magana a cikin akwatin maganganun VLOOKUP. © Ted Faransanci

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:

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

  1. Danna kan layi na _value a cikin akwatin maganganun VLOOKUP
  2. Danna maɓallin C2 a cikin takardun aiki don shigar da wannan tantanin halitta kamar ƙudurin search_key
  3. Danna kan shafin Table_array na akwatin maganganu
  4. 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
  5. Latsa maɓalli F4 a kan keyboard don canza layin zuwa cikakkun bayanai
  6. Danna kan hanyar Col_index_num na akwatin maganganu
  7. 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
  8. Danna kan Range_lookup line na maganganu
  9. Rubuta kalma Gaskiya kamar matsayin Range_lookup
  10. Latsa maɓallin shigarwa a kan keyboard don rufe akwatin maganganu kuma komawa zuwa aikin aiki
  11. Amsar 2% (yawan rangwame don yawan sayan da aka saya) ya kamata ya bayyana a cell D2 na takardar aiki
  12. 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

03 na 03

Excel VLOOKUP Ba aiki: # N / A da #REF Kurakurai

VLOOKUP Yana dawo da #REF! Saƙon kuskure. © Ted Faransanci

Saƙonnin kuskuren VLOOKUP

Saƙonnin kuskure masu zuwa suna hade da VLOOKUP.

A # N / A ("darajar ba samuwa ba") An nuna kuskuren idan:

A #REF! ("tunani daga cikin kewayon") An nuna kuskuren idan: