Bincike Bayanan Musamman tare da HASKIYA TASHE

Ayyukan HASKIYA na Excel, takaice don dubawa a kwance , zai iya taimaka maka samun takamaiman bayani a cikin manyan bayanan bayanai irin su lissafin kayayyaki na ɓangarori ko babban jerin sunayen abokan tarayya.

HLOOKUP yana aiki sosai irin aikin VLIOKUP na Excel. Bambanci kawai shine cewa VLOOKUP yayi bincike don bayanai a ginshikan yayin da HLOOKUP ke nemo bayanai a cikin layuka.

Biye matakai a cikin darussan koyawa a ƙasa ke tafiya ta hanyar yin amfani da aikin HLOOKUP don samun bayani na musamman a cikin wani shafin Excel.

Ƙarshen mataki na tutorial yana rufe saƙonnin kuskure wanda ke faruwa tare da aikin HLOOKUP.

Tutorial Topics

01 na 09

Shigar da Bayanan Tutorial

Yadda ake amfani da HLOOKUP a Excel. © Ted Faransanci

Lokacin shigar da bayanai a cikin takardar aikin Excel, akwai wasu sharuddan da suka biyo baya:

  1. A duk lokacin da ya yiwu, kar ka bar layuka marar launi ko ginshiƙai lokacin shigar da bayanai naka.

Don wannan koyawa

  1. Shigar da bayanai kamar yadda aka gani a cikin hoton da ke cikin sel D4 zuwa I5.

02 na 09

Fara aikin HLOOKUP

Yadda ake amfani da HLOOKUP a Excel. © Ted Faransanci

Kafin fara aikin HLOOKUP yakan zama kyakkyawan ra'ayin da za a ƙara rubutun zuwa takardun aiki don nuna abin da HLOOKUP ya samo bayanan. Don wannan koyo shigar da rubutun da ke biyowa a cikin kwayoyin da aka nuna. Ayyukan HLOOKUP da kuma bayanan da aka samo daga cikin asusun zai kasance a cikin kwayoyin zuwa dama na waɗannan rubutun.

  1. D1 - Sashe na Sunan
    E1 - Farashin

Kodayake yana yiwuwa a rubuta aikin HLOOKUP kawai a cikin tantanin halitta a cikin takardun aiki , mutane da yawa suna neman sauki don amfani da maganganun aikin.

Don Wannan Koyarwar

  1. Danna kan tantanin halitta E2 don sa shi tantanin halitta mai aiki . Wannan shine inda za mu fara aikin HLOOKUP.
  2. Danna kan shafukan Formulas .
  3. Zabi Binciken & Magana daga ribbon don buɗe jerin abubuwan da aka sauke aikin.
  4. Danna HLOOKUP cikin jerin don kawo akwatin maganganun aikin.

Bayanan da muka shiga cikin layuka hudu a cikin akwatin maganganu zasu haifar da muhawarar aikin HLOOKUP. Wadannan muhawara suna nuna aikin abin da bayanan da muke bayarwa kuma inda ya kamata mu bincika don gano shi.

03 na 09

Darajar Bincike

Ƙara Magana mai Mahimmanci. © Ted Faransanci

Shawarar ta farko ita ce Lookup_value . Yana nuna HLOOKUP game da abin da ke cikin database muna neman bayani. Ana duba Lookup_value a jere na farko na zaɓin da aka zaba.

Bayanin da HLOOKUP zai dawo zai kasance daga wannan shafi na asusun a matsayin Lookup_value.

Ƙaƙidar Lookup_value na iya zama nau'in rubutu, ƙimar mahimmanci (TRUE ko FALSE kawai), lamba, ko tantancewar salula zuwa darajar.

Don wannan koyawa

  1. Danna kan layin Lookup_value a cikin akwatin maganganu
  2. Danna kan tantanin halitta D2 don ƙara wannan tantanin halitta zuwa layin Lookup_value . Wannan shi ne tantanin halitta inda za mu rubuta sunan sashi game da abin da muke nemo bayani.

04 of 09

Abun Tsarin

Ƙara Magana ta Tsarin Gida. © Ted Faransanci

Shawarar Table_array shine kewayon bayanan da aikin HLOOKUP ke nema don nemo bayaninka. Lura cewa wannan jeri bazai buƙatar haɗa duk layuka ko ma jeri na farko na cibiyar yanar gizo ba .

Table_array dole ne ya ƙunshi akalla layuka guda biyu na bayanai duk da haka, tare da jeri na farko da ke dauke da Lookup_value (duba mataki na baya).

Idan ka shigar da bayanan salula don wannan hujja abu ne mai kyau don amfani da cikakkun bayanan salula. Ana nuna alamun ƙididdigar sel a Excel ta alamar dollar ( $ ). Misali zai zama $ E $ 4.

Idan ba ku yi amfani da cikakkun bayanai ba kuma kuna kwafin aikin HLOOKUP zuwa wasu kwayoyin halitta, akwai damar da za ku sami saƙonnin kuskure a cikin kwayoyin da aka kofe aikin.

Don wannan koyawa

  1. Danna kan layin Table_array a cikin akwatin maganganu.
  2. Sanya siffofin E4 zuwa I5 a cikin maƙallan rubutu don ƙara wannan kewayon zuwa layi na Table_array . Wannan shi ne kewayon bayanan da HLOOKUP zai bincika.
  3. Latsa maɓallin F4 a kan maɓallin keɓaɓɓiyar hanya don yin iyaka cikakken ($ E $ 4: $ I $ 5).

05 na 09

Lambar Shafin Yanayin

Ƙara Shawarar Maimaita Shaidar Index. © Ted Faransanci

Sha'idar lissafin layi (Row_index_num) ya nuna wane jere na Table_array yana dauke da bayanan da kake bayan.

Misali:

Don wannan koyawa

  1. Danna kan layin Row_index_num a cikin akwatin maganganu
  2. Rubuta 2 a cikin wannan layin don nuna cewa muna son HLOOKUP don dawo da bayanin daga jere na biyu na tashar tebur.

06 na 09

Binciken Range

Ƙara Binciken Bincike a Range. © Ted Faransanci

Shawarar Range_lookup tana da mahimmanci na gaske (TRUE ko FALSE kawai) wanda ya nuna ko kana son HLOOKUP su sami ainihin ko kimanin dacewa zuwa Lookup_value .

Don Wannan Koyarwar

  1. Danna kan Range_lookup line a cikin akwatin maganganu
  2. Rubuta kalma ƙarya a cikin wannan layi don nuna cewa muna son HLOOKUP ya dawo daidai daidai don bayanin da muke nema.
  3. Danna Ya yi don rufe akwatin maganganu.
  4. Idan kun bi duk matakai na wannan koyaswa ya kamata a yanzu kuna da cikakken aikin HLOOKUP a cikin cell E2.

07 na 09

Yin amfani da HLOOKUP don dawo da Bayanai

Ana dawo da bayanan tare da Ayyukan HLOOKU KASHE. © Ted Faransanci

Da zarar an gama aikin HLOOKUP ana iya amfani dashi don dawo da bayanan daga bayanan .

Don yin haka, rubuta sunan abin da kake so don dawowa cikin sel Lookup_value kuma danna maballin ENTER akan keyboard.

HLOOKUP yana amfani da lambar Lissafi na Lissafi domin sanin abin da aka nuna a cikin tantanin halitta E2.

Don Wannan Koyarwar

  1. Danna kan salula E1 a cikin shafukanka.
  2. Rubuta Bolt a cikin sel E1 kuma danna maballin ENTER akan keyboard.
  3. Yawan farashi - $ 1.54 - ya kamata a nuna a cikin cell E2.
    Gwada aikin HLOOKUP ta hanyar buga wasu sassan sassa a cikin cell E1 kuma kwatanta bayanan da aka dawo cikin cell E2 tare da farashin da aka lissafa a cikin kwayoyin E5 zuwa I5.

08 na 09

Saƙonnin kuskure na Excel HLOOKUP

Saƙonnin kuskure na Excel HLOOKUP. © Ted Faransanci

Saƙonnin kuskure masu zuwa suna hade da HLOOKUP.

# N / A kuskure:

#ref !:

Wannan ya kammala tutorial akan ƙirƙirar da yin amfani da aikin HLOOKUP a Excel 2007.

09 na 09

Misali Yin Amfani da Ayyukan HAUSA na 2007 na Excel

Shigar da wadannan bayanan cikin sel da aka nuna:

Bayanan Cell

Danna kan tantanin halitta E1 - wurin da za a nuna sakamakon.

Danna kan shafukan Formulas.

Zabi Binciken & Magana daga ribbon don buɗe jerin abubuwan da aka sauke aikin.

Danna HLOOKUP cikin jerin don kawo akwatin maganganun aikin.

A cikin akwatin maganganu, danna kan layi na _value.

Danna kan tantanin halitta D1 a cikin maƙallan rubutu. Wannan shi ne inda za mu rubuta sunan ɓangaren da muke son farashin.

A cikin akwatin maganganu, danna kan Line_array line.

Sanya siffofin E3 zuwa I4 a cikin maƙallan don shigar da kewayon cikin akwatin maganganu. Wannan shi ne kewayon bayanan da muke so HOKOKA don bincika.

A cikin akwatin maganganu, danna kan jerin Row_index_num.

Rubuta lambar 2 don nuna cewa bayanan da muke son dawowa a cikin jere na 2 na table_array.

A cikin akwatin maganganu, danna kan Range_lookup line.

Rubuta kalma ƙarya don nuna cewa muna so daidai daidai don bayanai da muke nema.

Danna Ya yi.

A cikin cell D1 na taswirar, rubuta maballin kalmar.

Darajar $ 1.54 ya kamata ya bayyana a cikin tantanin halitta E1 yana nuna farashin wani ƙulli kamar yadda aka nuna a cikin table_array.

Idan ka danna kan tantanin halitta E1, cikakken aikin = HLOOKUP (D1, E3: I4, 2, FALSE) ya bayyana a cikin wannan tsari a sama da takardun aiki.