Thola idatha ecacile nge-Excel HLOOKUP

Umsebenzi we-HLOOKUP we-Excel, okufinyeleleka kokungena okuzungezile , kungakusiza ukuthola ulwazi oluthile kumatafula amakhulu wedatha njengoluhlu lwama-list of izingxenye noma uhlu olubanzi lokuthintana nobulungu.

I-HLOOKUP isebenza kakhulu umsebenzi we-VLOOKUP we-Excel. Umehluko kuphela ukuthi i-VLOOKUP ifuna idatha kumakholomu ngenkathi i-HLOOKUP ifuna idatha emigqeni.

Ukulandela izinyathelo ezihlokweni zezifundo ngezansi ukuhamba usebenzisa umsebenzi we-HLOOKUP ukuthola ulwazi oluqondile ku-database ye-Excel.

Isinyathelo sokugcina sesifundo sihlanganisa imiyalezo yephutha ejwayelekile ngokusebenza kwe-HLOOKUP.

Izihloko zezifundo

• Ukufaka idatha yokufundisa
• Ukuqala ukusebenza kwe-HLOOKUP
• Inani lokuLookup
• Ithebula le-Table
• Inombolo Yenkomba Yenkambo
• I-Range Lookup
• Ukusebenzisa i-HLOOKUP ukuthola idatha
• Imilayezo ephutha evamile ye-Excel HLOOKUP

01 ka 09

Ukufaka idatha yokufundisa

Uma ufaka idatha ku-sheet ye-Excel, kukhona neminye imithetho ejwayelekile okumele ilandelwe:

1. Uma kunokwenzeka, ungashiyi imigqa noma amakholomu angenalutho uma ufaka idatha yakho.

• Ukushiya imigqa namakholomu angenalutho kumatafula wedatha kungenza kube nzima ukusebenzisa imisebenzi eminingi ye-Excel - kuhlanganise ne-HLOOKUP.

• Faka idatha yakho ngemigqa.
• Uma ubeka ishidi lakho lokusebenzela, ubhale amagama achaza idatha emgqeni wokuqala wetafula futhi, ngaphansi kwalokho, idatha ngokwayo.
• Uma kunezinchungechunge ezingaphezu kweyodwa yedatha, zifake ohlwini olulodwa emva komunye emigqeni isihloko salunye uchungechunge lwedatha esitokisini sokuqala ngakwesobunxele.

Kulesi sifundo

1. Faka idatha njengoba iboniswe esithombeni ngenhla kumaseli D4 kuya ku-I5.

• Umugqa wokuqala wedatha (umugqa 4) uqukethe amagama engxenye. Umugqa wesibili (umugqa 5) intengo ngayinye ngayinye.

02 ka 09

Ukuqala ukusebenza kwe-HLOOKUP

Ngaphambi kokuqala umsebenzi we-HLOOKUP ngokuvamile umqondo omuhle ukwengeza izihloko ekhasini lokusebenzela ukuze ubonise ukuthi iyiphi idatha ebuyiswa ngu-HLOOKUP. Kule tutorial faka izihloko ezilandelayo kumaseli abonisiwe. Umsebenzi we-HLOOKUP nedatha ithola kusuka ku-database izotholakala emasethini ngakwesokudla salezi zihloko.

1. D1 - Ingxenye Yegama
   E1 - Intengo

Nakuba kungenzeka nje ukuthayipha umsebenzi we-HLOOKUP esitokisini kushidi lokusebenzela , abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lengxoxo lomsebenzi.

Kule Nfundo

1. Chofoza kumakhalekhukhwini E2 ukuze wenze iseli esebenzayo . Yilapho sizoqala khona umsebenzi we-HLOOKUP.
2. Chofoza kuthebhu yefomula .
3. Khetha Ukulanda & Izikhombisi ezivela kubhebhoni ukuvula uhlu lwehla lokusebenza.
4. Chofoza ku- HLOOKUP kuhlu ukuze ulethe ibhokisi lengxoxo lomsebenzi.

Idatha esiyifaka emigqeni emine engenalutho ebhokisini lengxoxo izokwenza izingxabano zomsebenzi we-HLOOKUP. Lezi zimpikiswano zitshela umsebenzi ukuthi yiluphi ulwazi esikulo futhi kufanele lufune ukuluthola.

03 ka 09

Inani lokuLookup

Ingxabano yokuqala yi- Lookup_value . Itshela i-HLOOKUP ngenye into ku-database esifuna ulwazi. I-Lookup_value itholakala emgqeni wokuqala webanga elikhethiwe.

Ukwaziswa ukuthi i-HLOOKUP izobuya kuyohlala njalo kusuka kukholomu efanayo yedatha njengeLookup_value.

I-Lookup_value ingaba yochungechunge lombhalo, inani elilinganiselwe (TRUE noma FALSE kuphela), inombolo, noma ireferensi yeseli kunani.

Kulesi sifundo

1. Chofoza kumugqa we- Lookup_value ebhokisini lengxoxo
2. Chofoza kuseli D2 ukuze ungeze le nkomba yeselula kumlayini we- Lookup_value . Lona iseli lapho sizokuthayipha igama legama mayelana nokuthi sifuna ulwazi.

04 ka 09

Ithebula le-Table

I-argument ye-Table_array ibanga lemininingwane ukuthi umsebenzi weHLOOKUP uphenya ukuthola ulwazi lwakho. Qaphela ukuthi lolu hlu aludingi ukufaka yonke imigqa noma ngisho nomugqa wokuqala wedatha.

I-Table_array kumele iqukathe okungenani imigqa emibili yedatha nakuba, ngomugqa wokuqala oqukethe i-Lookup_value (bheka isinyathelo sangaphambilini).

Uma ufaka izingqikithi zamangqamuzana kule mpikiswano kuwumqondo omuhle ukusebenzisa izikhombisi zezinhlamvu ngokuphelele. Izinkomba ezingenalutho zamangqamuzana zikhonjiswe ku-Excel nge-dollar sign ( $ ). Isibonelo sibe ngu $ E $ 4.

Uma ungasebenzisi izikhombisi eziphelele futhi ukopisha umsebenzi we-HLOOKUP kwamanye amaseli, kunethuba uzothola imilayezo yephutha emafetheni lapho umsebenzi ukopishwa khona.

Kulesi sifundo

1. Chofoza ku- Table_array line ebhokisini lengxoxo.
2. Gqamisa amaseli e-E4 ku-I5 kuspredishithi ukuze ungeze lolu hlu kulayini we- Table_array . Lona ububanzi bemininingwane uHLOOKUP izosesha.
3. Cindezela ukhiye we- F4 ekhibhodi ukuze wenze uhla oluphelele ($ E $ 4: $ I $ 5).

05 ka 09

Inombolo Yenkomba Yenkambo

Ingxabano yenombolo yenkomba yomugqa (Row_index_num) ibonisa ukuthi yiliphi umugqa weThebula_array liqukethe idatha oyilandelayo.

Ngokwesibonelo:

• uma ufaka 1 ohlwini lwenombolo yomugqa, i-HLOOKUP ibuyisela inani kusukela kukholomu yokuqala ku-table_array;
• uma inombolo yerejista ye-row i-2, ibuyisela inani kusukela emgqeni wesibili ku-table_array.

Kulesi sifundo

1. Chofoza ku- Row_index_num line ebhokisini lengxoxo
2. Thayipha i- 2 kulolu gqa ukuze ukhombise ukuthi sifuna i-HLOOKUP ukubuyisa ulwazi kusuka emgqeni wesibili wethebula lethebula.

06 ka 09

I-Range Lookup

I-argument ye-Range_lookup iyinani elilinganiselwe (TRUE noma FALSE kuphela) elibonisa ukuthi ufuna HLOOKUP ukuthola umlinganiso oqondile noma olinganayo ku- Lookup_value .

• Uma i-TRUE noma uma le mpikiswano ingavaliwe, i-HLOOKUP izosebenzisa umdlalo olinganiselwe uma ingawutholi umdlalo oqondile ne-Lookup_value. Uma umdlalo oqondile ungatholakali, i-HLOOKUP ibuyisela inani elilandelayo elikhulu elingaphansi kwe-Lookup_value.
• Uma i-FALSE, i-HLOOKUP izosebenzisa kuphela umdlalo oqondile ku-Lookup_value. Uma kunamagugu amabili noma ngaphezulu kukholomu yokuqala yeThebula_array efanisa i-Lookup_value, inani lokuqala elitholiwe lisetshenzisiwe. Uma umdlalo oqondile ungatholakali, iphutha le- # N / A libuyiselwe.

Kule Nfundo

1. Chofoza kumugqa we- Range_lookup ebhokisini lengxoxo
2. Thayipha igama Amanga kulolu gqa ukuze ukhombise ukuthi sifuna i-HLOOKUP ukubuyisa umdlalo oqondile we-data esiyifunayo.
3. Chofoza OK ukuvala ibhokisi lengxoxo.
4. Uma ulandele zonke izinyathelo zalesi sifundo kufanele manje ube nomsebenzi ophelele we-HLOOKUP kuseli le-E2.

07 ka 09

Ukusebenzisa i-HLOOKUP ukuthola idatha

Uma umsebenzi weHLOOKUP usuqedile ungasetshenziselwa ukubuyisa ulwazi kusuka ku- database .

Ukwenza kanjalo, thayipha igama lento ofuna ukuyifaka esitokisini seLookup_value bese ucindezela inkinobho ENTER ekhibhodi.

I-HLOOKUP isebenzisa Inombolo Yenkomba Yezintambo ukuthola ukuthi iyiphi idatha yedatha okufanele iboniswe kuseli E2.

Kule Nfundo

1. Chofoza kuseli E1 kuspredishithi sakho.
2. Thayipha i- Bolt ibe yiseli E1 bese ucindezela inkinobho ENTER ekhibhodi.
3. Inani lentengo - $ 1.54 - kufanele iboniswe kuseli le-E2.
   Hlola umsebenzi we-HLOOKUP ngokuqhubeka nokuthayipha amanye amagama engxenyeni e-E1 bese uqhathanisa nedatha ebuyele esitokisini se-E2 ngamanani afakwe kumaseli E5 kuya ku-I5.

08 ka 09

Imilayezo yephutha HLOOKUP ye-Excel

Imilayezo yephutha elandelayo ihlotshaniswa ne-HLOOKUP.

Iphutha le-# N / A:

• Lephutha iboniswa uma inani lokubheka lingatholakali kukholomu yokuqala yethebula lethebula.
• Izophinde iboniswe uma ububanzi be- array argument array engalungile. Uma le mpikiswano ihlanganisa imigqa engenalutho ngaphezu kwetafula lethebula.

#REF !:

• Lephutha iboniswa uma ingxabano yenombolo yenkomba yomugqa ingaphezulu kunomboro wemigqa etafuleni. Esithombeni ngenhla, i- #REF! iphutha livela ngoba inombolo yochungechunge lwemigqa isethwe ku- 3 ngenkathi kunemigqa emibili kuphela ku-Table_array.

Lokhu kuqedela ukufundisa ngokudala nokusebenzisa umsebenzi we-HLOOKUP ku-Excel 2007.

09 ka 09

Isibonelo Ukusebenzisa umsebenzi we-HLOOKUP we-Excel 2007

Faka idatha elandelayo kumaseli abonisiwe:

Idatha yeselula

• D3 - Ingxenye
• E3 - Ukubeka
• F3 - Bolt
• I-G3 - I-Cog
• H3 - Gear
• I3 - Washer
• D4 - Inani
• E4 - $ 17.34
• F4 - $ 1.54
• I-G4 - $ 20.21
• H4 - $ 23.56
• I4 - $ 1.43

Chofoza kuseli E1 - indawo lapho imiphumela izoboniswa khona.

Chofoza kuthebhu yefomula.

Khetha Ukulanda & Izikhombisi ezivela kubhebhoni ukuvula uhlu lwehla lokusebenza.

Chofoza ku-HLOOKUP kuhlu ukuze ulethe ibhokisi lengxoxo lomsebenzi.

Ebhokisini lengxoxo, chofoza kulayini we-Lookup _value.

Chofoza kuseli D1 kuspredishithi. Yilapho sizobe siyiphayipha igama lenye into esiyifisa ukuyithenga.

Ebhokisini lebhokisi, chofoza kulayini lwe-Table_array.

Gqamisa amaseli e-E3 ku-I4 kuspredishithi sokufaka ibanga ebhokisini lengxoxo. Lona ububanzi bedatha esifuna uHLOOKUP ukucinga.

Ebhokisini lengxoxo, chofoza ku-Row_index_num line.

Thayipha inombolo 2 ukukhombisa ukuthi idatha esiyifunayo ibuyiselwe kulayini 2 wetafula_array.

Ebhokisini lengxoxo, chofoza kumugqa we-Range_lookup.

Thayipha igama lamanga ukukhombisa ukuthi sifuna ukufanelana ngqo kwedatha yethu eceliwe.

Chofoza OK.

Ngeselula D1 yesipredishithi, thayipha igama le-bolt.

Inani elibiza i-$ 1.54 kufanele livele esitokisini se-E1 sibonisa intengo ye-bolt njengoba kuboniswe etafuleni_array.

Uma uchofoza ku-cell E1, umsebenzi ophelele = HLOOKUP (D1, E3: I4, 2, FALSE) uvela kubha yefomula ngaphezulu kwephepha lokusebenzela.