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
- Shigar da Bayanan Tutorial
- Fara aikin HLOOKUP
- Darajar Bincike
- Abun Tsarin
- Lambar Shafin Yanayin
- Binciken Range
- Yin amfani da HLOOKUP don dawo da Bayanai
- Saƙonnin kuskuren Common don Excel HLOOKUP
01 na 09
Shigar da Bayanan Tutorial
Lokacin shigar da bayanai a cikin takardar aikin Excel, akwai wasu sharuddan da suka biyo baya:
- A duk lokacin da ya yiwu, kar ka bar layuka marar launi ko ginshiƙai lokacin shigar da bayanai naka.
- Cire layin jeri da ginshiƙai a cikin bayanan bayanai zai iya sa wuyar amfani da ayyukan Excel - ciki har da HLOOKUP.
- Shigar da bayananku a layuka.
- A lokacin da aka shimfiɗa takardar aikinku, da jerin sunayen da ke kwatanta bayanai a jere na farko na teburin kuma, zuwa ƙarƙashin wannan, bayanan ɗin kanta.
- Idan akwai jerin bayanai fiye da ɗaya, lissafa su daya bayan ɗayan a layuka tare da take don kowane jerin bayanai a cikin wayar farko a hagu.
Don wannan koyawa
- Shigar da bayanai kamar yadda aka gani a cikin hoton da ke cikin sel D4 zuwa I5.
- Lissafin farko na bayanai (jere 4) yana ƙunshe da sunaye. Hanya na biyu (jere 5) Farashin kowane bangare.
02 na 09
Fara aikin HLOOKUP
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.
- 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
- Danna kan tantanin halitta E2 don sa shi tantanin halitta mai aiki . Wannan shine inda za mu fara aikin HLOOKUP.
- 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.
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
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
- Danna kan layin Lookup_value a cikin akwatin maganganu
- 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
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
- Danna kan layin Table_array a cikin akwatin maganganu.
- 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.
- 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
Sha'idar lissafin layi (Row_index_num) ya nuna wane jere na Table_array yana dauke da bayanan da kake bayan.
Misali:
- idan ka shigar da 1 a cikin jeri na layi, HLOOKUP ya dawo darajar daga shafi na farko a table_array;
- idan lambar nuni jeri na 2, ya dawo darajar daga jere na biyu a table_array.
Don wannan koyawa
- Danna kan layin Row_index_num a cikin akwatin maganganu
- 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
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 .
- Idan TRUE ko kuma idan an cire wannan hujja, HLOOKUP zai yi amfani da kimanin dacewa idan baza ta iya samun matsala daidai da Lookup_value ba. Idan ba'a samo daidai ba, HLOOKUP ya sake dawowa mafi girma mafi girma wanda yake kasa da Lookup_value.
- Idan FALSE, HLOOKUP zai yi amfani da daidai daidai da Lookup_value. Idan akwai dabi'u biyu ko fiye a cikin shafin farko na Table_array wanda yayi daidai da Lookup_value, ana amfani da darajar farko. Idan ba a samo daidai ba, ana mayar da kuskuren N / A.
Don Wannan Koyarwar
- Danna kan Range_lookup line a cikin akwatin maganganu
- Rubuta kalma ƙarya a cikin wannan layi don nuna cewa muna son HLOOKUP ya dawo daidai daidai don bayanin da muke nema.
- Danna Ya yi don rufe akwatin maganganu.
- 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
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
- Danna kan salula E1 a cikin shafukanka.
- Rubuta Bolt a cikin sel E1 kuma danna maballin ENTER akan keyboard.
- 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 masu zuwa suna hade da HLOOKUP.
# N / A kuskure:
- An nuna wannan kuskure idan ba a samo darajar binciken ba a cikin sashin farko na tashar tebur.
- Har ila yau za'a nuna idan filin don tabbacin jigon launi na da kuskure. Idan wannan jigidar ta ƙunshi layuka maras tabbas a sama da tsararren tashar.
#ref !:
- Wannan kuskure yana nunawa idan layin jigilar lambobi na sama ya fi yawan layuka a cikin tebur. A cikin hoto a sama, da #REF! kuskure yana faruwa ne saboda an saita lambar index a cikin 3 yayin da akwai layuka biyu a cikin Table_array.
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
- D3 - Sashe
- E3 - Gaya
- F3 - Bolt
- G3 - Cog
- H3 - Gear
- I3 - Washer
- D4 - Farashin
- E4 - $ 17.34
- F4 - $ 1.54
- G4 - $ 20.21
- H4 - $ 23.56
- I4 - $ 1.43
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.