Contents
Rana ta 10 na gudun marathon 30 Excel yana aiki a cikin kwanaki 30 za mu sadaukar da karatun aikin HLOOKUP (GPR). Wannan yanayin yayi kama da KYAUTA (VLOOKUP), kawai yana aiki tare da abubuwan lissafin kwance.
Aiki mara sa'a HLOOKUP (GLOW) baya shahara kamar 'yar uwarta, tunda a mafi yawan lokuta ana jera bayanan da ke cikin tebur a tsaye. Ka tuna karo na ƙarshe da kake son neman kirtani? Me game da dawo da ƙimar daga ginshiƙi ɗaya, amma yana cikin ɗaya daga cikin layuka a ƙasa?
Duk da haka, bari mu ba da fasali HLOOKUP (GPR) lokacin da ya cancanci ɗaukaka kuma ku dubi bayanan game da wannan fasalin, da kuma misalai na amfani da shi. Ka tuna, idan kuna da ra'ayoyi masu ban sha'awa ko misalai, da fatan za a raba su a cikin sharhi.
Aiki 10: HLOOKUP
aiki HLOOKUP (HLOOKUP) yana duba ƙimar a jere na farko na tebur kuma ya dawo da wata ƙima daga shafi ɗaya a cikin tebur.
Ta yaya zan iya amfani da aikin HLOOKUP (HLOOKUP)?
Tun da aikin HLOOKUP (HLOOKUP) zai iya nemo madaidaicin ƙima ko ƙima a cikin kirtani, sannan zai iya:
- Nemo jimlar tallace-tallace don yankin da aka zaɓa.
- Nemo mai nuna alama wanda ya dace da ranar da aka zaɓa.
HLOOKUP Syntax
aiki HLOOKUP (HLOOKUP) yana da ma'ana mai zuwa:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
- dubawa (lookup_value): ƙimar da za a samo. Zai iya zama ƙima ko bayanin tantanin halitta.
- katakarar (tebur): teburin duba. Zai iya zama kewayo ko kewayon suna mai ɗauke da layi biyu ko fiye.
- jere_index_num (line_number): Kirtani mai ɗauke da ƙimar da aikin zai dawo dashi. Saita da lambar jere a cikin tebur.
- range_lokup (range_lookup): Yi amfani da KARYA ko 0 don nemo madaidaicin wasa; don bincike mai ƙimanci, GASKIYA (GASKIYA) ko 1. A cikin yanayi na ƙarshe, igiyar da aikin ke nema dole ne a jera shi cikin tsari mai hawa.
Tarko HLOOKUP (GPR)
Kamar KYAUTA (VLOOKUP), aiki HLOOKUP (HLOOKUP) na iya zama a hankali, musamman lokacin nemo ainihin madaidaicin igiyar rubutu a cikin teburin da ba a daidaita ba. A duk lokacin da zai yiwu, yi amfani da kimanin bincike a cikin tebur da aka jera ta hanyar layi na farko a cikin tsari mai hawa. Kuna iya fara amfani da aikin MATCH (MORE EXPOSED) ko COUNTIF (COUNTIF) don tabbatar da ƙimar da kuke nema ta wanzu a jere na farko.
Sauran siffofi kamar INDEX (INDEX) da MATCH Hakanan ana iya amfani da (MATCH) don dawo da ƙima daga tebur kuma sun fi dacewa. Za mu dube su daga baya a tseren marathon mu mu ga yadda ƙarfi da sassauƙa za su iya zama.
Misali 1: Nemo ƙimar tallace-tallace don yankin da aka zaɓa
Bari in sake tunatar da ku cewa aikin HLOOKUP (HLOOKUP) kawai yana neman ƙimar a saman jere na tebur. A cikin wannan misali, za mu sami jimlar tallace-tallace don yankin da aka zaɓa. Yana da mahimmanci a gare mu mu sami ƙimar daidai, don haka muna amfani da saitunan masu zuwa:
- An shigar da sunan yankin a cikin tantanin halitta B7.
- Teburin neman yanki yana da layuka biyu kuma ya zarce kewayon C2:F3.
- Jimlar tallace-tallace suna cikin layi na 2 na teburin mu.
- An saita hujja ta ƙarshe zuwa KARYA don nemo madaidaicin wasa lokacin nema.
Tsarin tsari a cikin cell C7 shine:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
Idan ba a sami sunan yankin a jere na farko na tebur ba, sakamakon aikin HLOOKUP (GPR) zai yi #AT (#N/A).
Misali 2: Nemo ma'auni don zaɓaɓɓen kwanan wata
Yawancin lokaci lokacin amfani da aikin HLOOKUP (HLOOKUP) yana buƙatar daidaiton wasa, amma wani lokacin kusan wasa ya fi dacewa. Misali, idan alamomi sun canza a farkon kowane kwata, kuma ana amfani da kwanakin farko na waɗannan kwata-kwata azaman taken shafi (duba hoton da ke ƙasa). A wannan yanayin, amfani da aikin HLOOKUP (HLOOKUP) da kusan wasa, zaku sami alamar da ta dace da kwanan wata da aka bayar. A cikin wannan misali:
- An rubuta kwanan wata a cikin cell C5.
- Tebur mai nuna alama yana da layuka biyu kuma yana cikin kewayon C2:F3.
- An jera tebur ɗin neman ta jeren kwanan wata a cikin tsari mai hawa.
- An rubuta alamun a cikin layi na 2 na teburin mu.
- An saita gardamar ƙarshe na aikin zuwa GASKIYA don nemo kusan daidai.
Tsarin tsari a cikin cell D5 shine:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
Idan ba a sami kwanan wata a jere na farko na tebur ba, aikin HLOOKUP (HLOOKUP) zai sami mafi girman ƙimar mafi kusa wanda bai wuce hujja ba dubawa (lokup_darajar). A cikin wannan misali, ƙimar da ake so shine Maris 15. Ba a cikin layin kwanan wata ba, don haka tsarin zai ɗauki ƙimar 1 Janairu kuma dawo 0,25.
