Fumana iLwazi kwiiDafa zeDatha ne-Excel's LOOKUP Function

01 ngo 01

I-Excel PHEKA UKUPHATHA KWEENKQUBO ZOMSEBENZI kwiFom ye-Array

Umsebenzi we-Excel LOOKUP uneefom ezimbini: i- Vector Form kunye neFomu ye - Array .

Ifomu yohlobo lwe-LOOKUP umsebenzi lifana nezinye i-Excel lookup imisebenzi efana neVLOOKUP kunye ne- HLOOKUP ukuze isetyenziswe ukufumana okanye ukubheka ixabiso elithile elisetafile yedatha.

Indlela eyahlukileyo ngayo kukuba:

1. Nge-VLOOKUP kunye ne-HLOOKUP, unokukhetha ukuba yeyiphi ikholomu okanye umqolo ukubuyisela ixabiso lwedatha ukususela, ngoxa i-LOOKUP ihlala ibuyisela ixabiso ukusuka kumgca wokugqibela okanye ikholomu kwindawo.
2. Xa uzama ukufumana umdlalo wexabiso elithethiweyo - eyaziwa ngokuba yiLowokup_value - VLOOKUP isesha kuphela ikholomu yokuqala yedatha kunye neHLOOKUP kuphela umgca wokuqala, ngelixa umsebenzi we LOOKUP ufuna ukukhangela umqolo wokuqala okanye ikholomu ngokuxhomekeka kwimilo yoluhlu .

UKUQALA Umsebenzi kunye ne-Array Shape

Ubume be-array-nokuba ingaba libala (inani elilinganayo lamakholomu kunye nemigca) okanye ixande (inombolo engalinganiyo yamakholomu kunye nemigqa) - ithintela apho umsebenzi LOOKUP uhlola idatha:

• Ukuba uluhlu lukrelekrele okanye ukuba luxande olude (ubude kunokuba bubanzi), UKUBHALWA kuthatha ukuba idatha ihlelwe kwiikholamu kwaye ngoko uphando lomdlalo kwiLookup_value kwikholamu yokuqala yoluhlu.
• Ukuba uluhlu olubanzi luxande (ububanzi kunokuba bude), LOOKUP ithatha ukuba idatha ihlelwe ngemigqa kwaye ngoko isesha umdlalo kwi-Lookup_value kumqolo wokuqala wohlu.

I-LOOKUP Umsebenzi we-Syntax kunye neArguments - Ifomu ye-Array

I- syntax yeFom yeFom ye-LOOKUP umsebenzi yile:

= LOOKUP (Lookup_value, Array)

I-Lookup_value (efunekayo) - ixabiso elisebenzayo kulo msebenzi. I-Lookup_value ingaba yinani, isicatshulwa, isixabiso esinengqiqo, okanye igama okanye iselferensi yeseli ebhekisela kwixabiso.

Uluhlu (olufunekayo) - iiseli eziluhlu olwenziwa ngumsebenzi ukufumana iLookup_value. Idatha ingaba yombhalo, amanani, okanye amanani angqiqo.

Amanqaku:

• Ukuze umsebenzi we-LOOKUP usebenze ngokuchanekileyo, i- argument Array kufuneka ihlelwe ngokunyuka komyalelo (A ukuya kwi-Z okanye encinci ukuya kunkulu kumanani)
• Ukuba umsebenzi awukwazi ukufumana umdlalo ochanekileyo weLookup_value, ukhetha ixabiso elikhulu kwi-Array engaphantsi okanye elinganayo kwixabiso leLookup_value
• Ukuba iLookup_value ilahlekile okanye incinci ngaphezu kwayo yonke imilinganiselo kwi-Array, umsebenzi we-LOOKUP uza kubuya i-# N / A iphutha

Umzekelo Ukusebenzisa ifomu yohlobo lwe-LOOKUP Function

Njengoko kuboniswe kumfanekiso ongentla, lo mzekelo uya kusebenzisa ifomu ye-Array yo-LOOKUP ukufumana ixabiso leWhachamacallit kwisahlu yolwazi.

Ubume boluhlu luxande olude . Ngenxa yoko, umsebenzi uya kubuyisa ixabiso elisekhompyutheni yokugqibela yoluhlu lwempahla.

Ukuhlela iDatha

Njengoko kuboniswe kumanqaku apha ngasentla, idatha kuluhlu kufuneka ihlelwe ngokunyuka komyalelo ukuze umsebenzi we-LOOKUP usebenze ngokufanelekileyo.

Xa ukhetha idatha kwi-Excel kuyimfuneko ukuba uqale ukhethe iikholomu kunye nemigca yedatha ekuhlelwe ngayo. Ngokuqhelekileyo oku kubandakanya izihloko zekholomu.

1. Phakamisa iiseli ze-A4 ukuya kwi-C10 kwiphepha lokusebenzela
2. Cofa kwiTebhu yeData yemenyu yebhondi
3. Cofa kwi- Sort khetho phakathi kwebhebhoni ukuvula ibhokisi yencoko yencoko
4. Ngaphantsi kweKholam ehamba kwibhokisi yencoko khetha ukhetha iCandelo kwizinto ezikhethiweyo zokukhetha uluhlu
5. Ukuba kuyimfuneko, phantsi kohlobo olukhethiweyo khetha iimilinganiselo ukusuka kwiindlela ezikhethiweyo zokukhetha
6. Ukuba kuyimfuneko, phantsi kwesihloko se- Order khetha i- A ukuya kwiZ ukusuka kwindlela yokukhetha phantsi
7. Cofa u-OK ukuhlela idatha kunye nokuvala ibhokisi yencoko
8. Umyalelo weedatha kufuneka uqhathanise neyabonwa kumfanekiso ongentla

UKUHLOLA Umsebenzi Umzekelo

Nangona kunokwenzeka ukuba uthayiphe nje umsebenzi we-LOOKUP

= LOOKUP (A2, A5: C10)

kwisitoreji somsebenzi, abaninzi abantu bakuthola kulula ukusebenzisa ibhokisi yencoko yencoko yomsebenzi.

Ibhokisi yencoko yenza ukuba ufake ingxabano nganye kumgca ohlukile ngaphandle kokuxhalabela nge-syntax yomsebenzi - njengabazali kunye nabahluli be-comma phakathi kweengxabano.

Amanyathelo angezantsi ngeenkcukacha indlela umsebenzi WOKUKHOKHELA ungeniswe ngayo kwiseli B2 usebenzisa ibhokisi yencoko.

1. Cofa kwiseli B2 kwiphepha lokusebenzela ukuze wenze iseli esebenzayo ;
2. Cofa kwiThebhu yefomula;
3. Khetha uLookup kunye neKhonkco kwi-Ribbon ukuvula uluhlu lwehla lokuhla;
4. Cofa kwi- LOOKUP kwoluhlu ukukhupha ibhokisi yencoko yencoko yeengxoxo ;
5. Cofa kwi- lookup_value, ukhetho olukhethiweyo kwoluhlu;
6. Cofa Kulungile ukukhupha ibhokisi yencoko yebhokisi yeeNtsebenzo;
7. Kwibhokisi yencoko, nqakraza kumgca weLookup_value ;
8. Cofa kwifowuni A2 kwiphepha lokusebenzela ukuze ufake ifowuni yeefayile kwibhokisi yencoko;
9. Cofa kumgca we- Array kwibhokisi yencoko
10. Gqamisa iiseli ezi-A5 ukuya ku-C10 kwiphepha lokusebenzela ukufaka eli bakala kwibhokisi yencoko-le ncwadana iqulethe yonke idatha efuna ukutshekishwa ngumsebenzi
11. Cofa u-OK ukugqiba umsebenzi kwaye uvale ibhokisi yencoko
12. Iphutha le-# N / A livela kwiseli ye-E2 kuba asikafaneli ukuthayipha igama lesahlulo kwi-cell D2

Ukungena kwexabiso lokuLookup

1. Cofa kwiseli A2, thayipha iWhachamacallit kwaye cinezela ukungena kwekhibhodi;
2. Ixabiso le -23.56 ye-$ limele livele kwiseli yeB2 njengoko eli lixabiso leWhachamacallit elisecaleni lokugqibela letafile yedatha;
3. Hlola umsebenzi ngokuthayipha amanye amagama enxalenye kwiseli ye-A2. Ixabiso le nxalenye nganye kuloluhlu liya kubonakala kwiseli B2;
4. Xa ucofa kwi-cell E2 umsebenzi opheleleyo = LOOKUP (A2, A5: C10) ibonakala kwifom yefomula ngaphezu kwephepha lokusebenzela.