Tsvaga Nyaya Dzakananga neExcel HLOOKUP

Basa reHLOOKUP reEexcel, rakapfupika kuti rive rakatarisana , rinogona kukubatsira kuwana ruzivo rwakananga mumatafura makuru e data akadai seongorororwe urongwa hwezvikamu kana boka guru rekutsvaga nhengo.

HLOOKUP inoshanda zvakawanda zvakafanana neEexcel yeVLOOKUP. Kusiyana chete kuripo ndeyekuti VLOOKUP inotsvaga dhidhi muhurukuro apo HLOOKUP inotsvaga deta mumitsara.

Kutevera matanho mumusoro wekudzidzisa pasi apa famba iwe uchishandisa basa reHLOOKUP kuti uwane ruzivo rwakananga mune imwe nhoroondo ye Excel.

Nhanho yekupedzisira yemudzidzisi inoshandisa mhosho yemashoko anowanzoitika neHLOOKUP.

Zvidzidzo Misoro

• Kupinda Tutorial Data
• Kutanga basa reHLOOKUP
• Kukosha kweLookup
• The Table Array
• Row Index Index
• The Range Lookup
• Kushandisa HLOOKUP ku Retrieve Data
• Mhinduro Dzakakanganiswa dzeEexcel HLOOKUP

01 ye 09

Kupinda Tutorial Data

Paunopinda deta iri muExpcel worksheet, pane mitemo yakawanda yekutevera:

1. Pose pazvinenge zvichibvira, usasiya mitsetse isina kana michina kana uchinge waisa data yako.

• Kusiya mitsetse isina ruvara uye mazamu mumatafura epa data zvinogona kuita kuti zvive nyore kushandisa mabasa akawanda eExpcel - kusanganisira HLOOKUP.

• Isa data yako mumitsara.
• Paunenge uchiisa pepa rako remabhuku, nyora mazita anotsanangura data mumutsara wokutanga wetafura uye, pasi apa, iyo data pachayo.
• Kana pane nhepfenyuro yakawanda yedheta, nyora imwe pashure peimwe mumitsara ine musoro wezvikamu zvakasiyana-siyana zvemukati mumusero wekutanga kuruboshwe.

Nokuda kweikudzidzisa iyi

1. Pinda data sezvinoonekwa mumufananidzo uri pamusoro apa mumasero D4 kusvika I5.

• Mutsara wekutanga we data (rutsva 4) rine mazita mazita. Mutsara wechipiri (mutsara we5) mutengo we chikamu chimwe nechimwe.

02 of 09

Kutanga basa reHLOOKUP

Usati watanga basa reHLOOKUP kanowanzova pfungwa yakanaka yekuwedzera misoro ku pepa rekushandisa kuratidza kuti data iri kuwanikwa nei neHLOOKUP. Nokuti iyi dzidzo inopinda misoro inotevera mumasero anoratidzwa. Basa reHLOOKUP uye data rainowana kubva kune database rinowanikwa mumasero kusvika kurudyi rwemashoko aya.

1. D1 - Chikamu Zita
   E1 - Mutengo

Kunyange zvazvo zvichibvira kungonyora basa reHLOOKUP muchikamu mune rimwe pepaji , vanhu vazhinji vanoona zviri nyore kushandisa bhokisi rebasa rebasa racho.

Pamusoro peTetorial

1. Dzvanya pachitokisi E2 kuti uite sero rinoshanda . Apa ndipo patinozotanga basa reHLOOKUP.
2. Dzvanya paMafomu eTom.
3. Sarudza Kuona & Tsvaga kubva kubhoni kuti uzarure basa rinoderera pasi.
4. Dzvanya paHLOOKUP mune urongwa kuti ubudise bhokisi rebasa rebasa.

Dhenda yatinopinda mumitsara mina isina mubhokisi rebhokisi ichaita nharo dzebasa reHLOOKUP. Iyi nharo inoudza basa iro ruzivo rwedu rwuri shure uye kuti rwunofanira kutsvaga kuti rwuwane.

03 of 09

Kukosha kweLookup

Nharo yekutanga ndiyo Lookup_value . Inotaurira HLOOKUP nezvechinhu chipi chiri mu database yatinenge tichitsvaga ruzivo. Iko Lookup_value iri mumutsara wekutanga wemasarudzo akasarudzwa.

Itaurwa iyo HLOOKUP ichadzoka inogara ichibva kune imwe senyere ye database seLookup_value.

Iko Lookup_value inogona kunge yakagadzirwa nemutsara, kukosha kwechokwadi (TRUE kana FALSE chete), nhamba, kana sechitsiko chekero kune kukosha.

Nokuda kweikudzidzisa iyi

1. Dzvanya paLowokup_value mubhokisi rebhokisi
2. Dzvanya pachitokisi D2 kuti uwedzere kurefera iyi yesero kuRookup_value line. Iyi ndiyo sero yatinonyora iyo chikamu zita rekuti tiri kutsvaga ruzivo.

04 of 09

The Table Array

Itafura_array mhirizhonga ibasa re data iyo basa reHLOOKUP rinotsvaga kuwana ruzivo rwako. Cherechedza kuti uwandu huno haufaniri kuisa mitsetse yose kana kunyange musara wokutanga we database .

Itafura_array inofanira kunge ine mitsetse miviri ye data asi, nemutsara wokutanga une Lookup_value (ona danho rekare).

Kana iwe ukapinda muchitokisi zvinyorwa zvekukakavadzana iyi pfungwa yakanaka yekushandisa zvizere zvikamu zvinyorwa. Absolute cell references rinotsanangurwa muExcel nechiratidzo chemadhora ( $ ). Muenzaniso waizova $ E $ 4.

Kana iwe usingashandisi zvachose zvinyorwa uye iwe ukanyora basa reHLOOKUP kune mamwe masero, kune mukana unowana mhosho yemashoko mumasero ayo basa racho rakadhindwa.

Nokuda kweikudzidzisa iyi

1. Dzvanya paTable_array line mubhokisi rebhokisi.
2. Simbisai masero E4 kusvika I5 mumapepaneteti kuti uwedzere urwu kuTable_array line. Iyi ndiyo mhando ye data iyo HLOOKUP ichatsvaga.
3. Dhinda f4 key pane keyboard kuti uite mhando yakakwana ($ E $ 4: $ I $ 5).

05 of 09

Row Index Index

Rino_index_num) Rino_index_num) inoratidza mutsara wei weTable_array ine data yaunotevera.

Semuyenzaniso:

• kana iwe ukaisa 1 mukati mutsara yenhamba yenhamba, HLOOKUP inodzorera kukosha kubva pakirasi yekutanga mutafura_array;
• kana iyo nhamba yekero yenhamba iri 2, inodzorera kukosha kubva kumutsara wechipiri mutafura_array.

Nokuda kweikudzidzisa iyi

1. Dzvanya paRow_index_num line mubhokisi rebhokisi
2. Tora a 2 mumutsara uyu kuratidzira kuti tinoda HLOOKUP kudzorera mashoko kubva kumutsara wechipiri we tafura yakazara.

06 of 09

The Range Lookup

Nharo yeRange_lookup inokosha (TRUE kana FALSE chete) inoratidza kana iwe uchida HLOOKUP kuwana mhuka chaiyo kana kuti yakatarisana neLookup_value .

• Kana CHOKWADI kana kuti nharo iyi isingabvumirwi, HLOOKUP inoshandisa mutsanana unofanirwa kana isingagoni kuwana mutsara chaiyo neLookup_value. Kana mutsara chaiwo usina kuwanika, HLOOKUP inodzorera inotevera hukuru hunokosha huri pasi peLookup_value.
• Kana FALSE, HLOOKUP inongoshandisa mamiriro chaiyo neLookup_value. Kana pane zviyero zviviri kana kupfuura muhombe yekutanga yeTable_array inofanana neLookup_value, kukosha kokutanga kunowanikwa kunoshandiswa. Kana mutsara chaiwo usina kuwanikwa, # N / A kukanganisa kunodzorerwa.

Pamusoro peTetorial

1. Dzvanya paRange_lookup mutsara mubhokisi rebhokisi
2. Tora shoko rokuti Manyepo munharaunda ino kuratidzira kuti tinoda HLOOKUP kudzorera mamiriro chaiwo kwe data yatinenge tichitsvaka.
3. Dzvanya OK kuti uvhare bhokisi rebhokisi.
4. Kana wakatevera matanho ose edzidziso iyi iwe unofanira kunge uine basa rakakwana HLOOKUP muchitokisi E2.

07 of 09

Kushandisa HLOOKUP ku Retrieve Data

Kamwe kana basa reHLOOKUP rave rapera rinogona kushandiswa kutora mashoko kubva kune database .

Kuti uite kudaro, tanidza zita rechimwe chinhu chaunoda kutora muloji yeLookup_value uye tumira ENTER mutsara pabhobhoyo.

HLOOKUP inoshandisa Row Index Index kuti ione chimwe chinhu che data chinofanira kuratidzwa muchitokisi E2.

Pamusoro peTetorial

1. Dzvanya pane cell E1 mune pepa rako repasiteteti.
2. Tora Bolt muchitsetse E1 uye shandisa ENTER key pane keyboard.
3. Mutengo webheni - $ 1.54 - inofanira kuiswa muchitokisi E2.
   Edzai basa reHLOOKUP rinowedzera nekunyora zvimwe zvikamu mazita muchitokisi E1 uye kuenzanisa data yakadzorerwa muchitokisi E2 nemitengo yakanyorwa mumasero E5 kusvika I5.

08 of 09

Excel HLOOKUP Error Messages

Izvo zvikanganiso zvinyorwa zvinotevera zvinosanganiswa neHLOOKUP.

# N / A kukanganisa:

• Iko kukanganisa kunoratidzwa kana kukosha kwekutsvaga kusingawanikwi mukorori yekutanga yefurafura yakagadzirwa.
• Ichazonyatsoratidzwa kana mararamiro etafura yepafura yakazara yakarurama. Kana nharo iyi inosanganisira mitsetse isina chinhu pamusoro pefurafura yakawanda.

#REF !:

• Iko kukanganisa kunoratidzwa kana mutsara wenhamba yemutsara wekutsvaga yakakura kudarika nhamba yemitsara iri patafura. Mumufananidzo uri pamusoro, #REF! kukanganisa kunoitika nokuti nhamba ye index yemiganhu yakagadzirirwa kusvika 3 apo kune mitsetse miviri chete muTable_array.

Izvi zvinopedza tutori pakuumba nekushandisa basa reHLOOKUP muExcel 2007.

09 of 09

Muenzaniso Uchishandisa Basa reHLOOKUP yeExcel 2007

Isa data inotevera inesei inoratidzwa:

Cell Data

• D3 - Chikamu
• E3 - Kubereka
• F3 - Bolt
• G3 - Cog
• H3 - Gear
• I3 - Washer
• D4 - Mutengo
• E4 - $ 17.34
• F4 - $ 1.54
• G4 - $ 20.21
• H4 - $ 23.56
• I4 - $ 1.43

Dzvanya pane cell E1 - nzvimbo iyo migumisiro icharatidzwa.

Dzvanya paMafomu eTom.

Sarudza Kuona & Tsvaga kubva kubhoni kuti uzarure basa rinoderera pasi.

Dzvanya paHLOOKUP mune urongwa kuti ubudise bhokisi rebasa rebasa.

Mubhokisi rebhokisi, tora paLowokup _value line.

Dzvanya pane sero D1 mune pepanheti. Apa ndipo patinonyora zita rechikamu chatinoda kutenga.

Mubhokisi rebhokisi, tora paTear_array line.

Simbisai masero E3 kusvika I4 mune pepadheetti kupinda mubhokisi mubhokisi rebhokisi. Iyi ndiyo mhando ye data yatinoda HLOOKUP kutsvaga.

Mubhokisi rebhokisi, tora paRow_index_num line.

Tora nhamba 2 kuratidzira kuti data yatinoda kudzoka iri mumutsara 2 wetafura_array.

Mubhokisi rebhokisi, tora paRange_lookup line.

Tora shoko rokuti Rhema kuratidzira kuti tinoda kunyatsoenderana nemashoko edu akavakirwa.

Dzvanya OK.

Muchikamu cheD1 chepadheseti, chengetedza shoko rokuti bolt.

Iko inosvika $ 1.54 inofanira kuonekwa muchitokisi E1 ichiratidza mutengo wehuta sezvinoratidzwa mutafura_array.

Kana iwe ukanyora pachitokisi E1, basa rakakwana = HLOOKUP (D1, E3: I4, 2, FALSE) rinowanikwa mubhadha yepamusoro pane pepa rekushanda.