Thola Ulwazi kumathebula wedatha Ngomsebenzi wokubuka we-Excel

01 ka 01

I-Excel PHEKA UKUFUNDA KWEZOMSEBENZI kuFomu lokuHlanganisa

Umsebenzi we-Excel LOOKUP unamafomu amabili: i- Vector Form ne- Form Array .

Ifomu elihle lohlelo LOOKUP lifana neminye imisebenzi ye-Excel lookup efana ne-VLOOKUP ne- HLOOKUP yokuthi ingasetshenziselwa ukuthola noma ukubheka izindinganiso ezithile ezisetafuleni lwedatha.

Ihluke kanjani ukuthi:

1. Nge-VLOOKUP ne-HLOOKUP, ungakhetha ukuthi iyiphi ikholomu noma umugqa ukubuyisela inani lwedatha kusuka, ngenkathi u-LOOKUP njalo ebuyisela inani kusukela kumugqa wokugcina noma ikholomu kuhlu .
2. Uma uzama ukuthola ukufaniswa kwexabiso elibekiwe - owaziwa ngokuthi i- Lookup_value - VLOOKUP isesha kuphela ikholomu yokuqala yedatha kanye ne-HLOOKUP kuphela umugqa wokuqala, ngenkathi umsebenzi we-LOOKUP uzosesha noma umugqa wokuqala noma ikholomu kuye ngokuthi umumo wesigaba .

Hlola umsebenzi kanye ne-Array Shape

Ukuma kwesigaba - kungakhathaliseki ukuthi sikwele (inani elilinganayo lamakholomu nemigqa) noma umugqa (inombolo engalingani yamakholomu nemigqa) - kuthinta lapho umsebenzi we-LOOKUP useshela idatha:

• Uma i-array ibhekene nesimo esimweni noma uma ingxande ende (ubude kunokuba ibubanzi), LOOKUP inquma ukuthi idatha ihlelwe ngamakholomu ngakho-ke usesho lomdlalo ku-Lookup_value kukholomu yokuqala ye-array.
• Uma i-array ingxande ebanzi (evulekile kuneyinde), u-LOOKUP uthatha ukuthi idatha ihlelwe ngemigqa futhi ngaleyo ndlela isesha umdlalo ku-Lookup_value emgqeni wokuqala wohlu.

I-Syntax ye-Function and Arguments - I-Form Form

I- syntax yohlobo lwe-Array lohlelo LOOKUP yilezi:

= LOOKUP (Lookup_value, Array)

I-Lookup_value (edingekayo) - inani elisebenzisayo umsebenzi. I-Lookup_value ingaba inombolo, umbhalo, inani elilinganiselwe, noma igama noma ireferensi yeseli elibhekisela kunani.

I-array (edingekayo) - amaseli wezinzwa umsebenzi osesho ukuthola i-Lookup_value. Idatha ingaba umbhalo, izinombolo, noma izindinganiso ezinengqondo.

Amanothi:

• Ukuze umsebenzi we-LOOKUP usebenze kahle, i-array argument kufanele ihlelwe ngendlela ekhuphukayo (A kuya ku-Z noma encane kunazo zonke kunenani elikhulu kunezinombolo)
• Uma ngabe umsebenzi awukwazi ukuthola umdlalo oqondile we-Lookup_value, ukhetha inani elikhulu kunazo zonke kwi-Array engaphansi noma elingana nenani elifanayo ku-Lookup_value
• Uma i-Lookup_value ingekho noma encane kunazo zonke izindinganiso ku-Array, umsebenzi we-LOOKUP uzobuyisela iphutha le- # N / A

Isibonelo Ukusebenzisa Ifomu Yomsebenzi we-LOOKUP Umsebenzi

Njengoba kuboniswe esithombeni ngenhla, lesi sibonelo sizosebenzisa ifomu le-LOOKUP ukuthola intengo ye- Whachamacallit kuhlu lwama-inventory.

Ukuma kwendatshana kungumgqa ode . Ngenxa yalokho, umsebenzi uzobuyisela inani elitholakala kukholomu yokugcina ohlwini lohlu.

Ukuhlunga Idatha

Njengoba kuboniswe kumanothi angenhla, idatha ku-array kufanele ihlungwe ngokunyuka kwe-oda ukuze umsebenzi we-LOOKUP usebenze kahle.

Uma uhlela idatha ku-Excel kuyadingeka ukuba uqale ukhethe amakholomu nemigqa yedatha ezohlungwa. Ngokuvamile lokhu kuhlanganisa izihloko zekholomu.

1. Gqamisa amaseli A4 kuya ku-C10 ekhasini lokusebenzela
2. Chofoza kuthebhu yedatha yedatha yemenyu
3. Chofoza ku- Uhlobo lokukhetha phakathi kwebhebhoni ukuze uvule ibhokisi lengxoxo yohlobo
4. Ngaphansi kwekholomu ebhokisini lebhokisi khetha ukukhetha ngeNgxenye kusuka kokukhethwa kukho ohlwini lokudonsa phansi
5. Uma kunesidingo, ngaphansi koHlelo ekuqondeni ukhethe Amanani kusukela kokukhethwa kukho ohlwini lokudonsa phansi
6. Uma kunesidingo, ngaphansi kwesihloko se- oda khetha i- A ukuya kuZ kusukela kokukhethwa kukho ohlwini lwezinhla
7. Chofoza OK ukuze uhlele idatha bese uvala ibhokisi lengxoxo
8. Ukuhlelwa kwedatha kufanele kufane manje okubonwe esithombeni ngenhla

Hlola Isibonelo Somsebenzi

Nakuba kungenzeka ukuthi uthayiphe nje umsebenzi we-LOOKUP

= LOOKUP (A2, A5: C10)

esitokisini somsebenzi, abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lengxoxo lomsebenzi.

Ibhokisi lebhokisi liyakuvumela ukuba ufake izingxabano ngalunye emgqeni ohlukile ngaphandle kokukhathazeka mayelana ne-syntax yomsebenzi - njengabazali kanye nabahlukanisi be-comma phakathi kwama-arguments.

Izinyathelo ezingezansi imininingwane yokuthi umsebenzi wokubuka ungene kanjani esitokisini B2 usebenzisa ibhokisi lengxoxo.

1. Chofoza kuseli B2 kushidi lokusebenzela ukuze wenze iseli elisebenzayo ;
2. Chofoza kuthebhu yefomula ;
3. Khetha ukuLondoloza kanye neNkhombandlela kusuka kubhebhoni ukuvula uhlu lwehla lokusebenza;
4. Chofoza ku- LOOKUP ohlwini ukuze ukhiphe i- Arguments ibhokisi lebhokisi lengxoxo;
5. Chofoza ku- lookup_value, ukhetho oluhle ohlwini;
6. Chofoza u-KULUNGILE ukuletha ibhokisi lengxoxo ye- Function Arguments ;
7. Ebhokisini lebhokisi, chofoza kulayini Lookup_value ;
8. Chofoza ku-cell A2 kusikripthi sokusebenzela ukuze ufake leyo reference reference ebhokisini lengxoxo;
9. Chofoza kulayini we- Array ebhokisini lengxoxo
10. Gqamisa amaseli A5 kuya ku-C10 kusikripthi sokusebenzela ukuze ufake lolu hlu ebhokisini lebhokisi - lolu hlu luqukethe yonke idatha ezoseshwa ngumsebenzi
11. Chofoza OK ukuqedela umsebenzi bese uvala ibhokisi lengxoxo
12. Iphutha le- # N / A livela eselini le-E2 ngoba asikafaneli ukuthayipha igama legama esitokisini D2

Ukufaka i-Value Lookup

1. Chofoza kumakhalekhukhwini A2, thayipha u- Whachamacallit bese ucindezela ukhiye we- Enter ekhibhodi;
2. Inani elilinganiselwa ku-$ 23.56 kufanele livele esitokisini B2 ngoba lokhu kuyintengo ye-Whachamacallit etholakala kukholomu yokugcina yedatha yedatha;
3. Hlola umsebenzi ngokuthayipha amanye amagama okuyingxenye esitokisini A2. Inani lentengo ngayinye kuhlu luzovela esitokisini B2;
4. Uma uchofoza kumakhalekhukhwini E2 umsebenzi ophelele = LOOKUP (A2, A5: C10) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela.