Sesha Idatha nge-Excel LOOKUP Function

Sebenzisa umsebenzi we-LOOKUP we-Excel - ifomu le-vector - ukuthola i-value eyodwa kusuka kumugqa owodwa noma uhla lwekholomu yedatha. Funda ukuthi yiliphi isiqondiso ngesinyathelo ngesinyathelo.

01 ngo-04

Thola Idatha kumakholomu noma imigqa nge-Excel's LOOKUP Function

Umsebenzi we-LOOKUP we-Excel unamafomu amabili:

• Ifomu le-Array
• i- Vector Form ;

Yehluke kanjani ukuthi:

• Ifomu le-Array libukeka emgqeni wokuqala noma ikholomu ye- array - ibhokisi ledatha equkethe imigqa eminingi namakholomu - ngenani elibekiwe, bese libuyisa inani elivela endaweni efanayo kumugqa wokugcina noma ikholomu ye-array.
• I-Vector Form ihlola i-vector - umugqa owodwa noma ikholomu yedatha - ngenani elibekiwe bese ubuyela inani kusukela endaweni efanayo kumugqa wesibili noma ikholomu.

02 ka 04

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

I- syntax yomsebenzi ibhekisela kokuhlelwa komsebenzi futhi ihlanganisa igama lomsebenzi, amabakaki, nokuphikisana .

I-syntax ye- Vector Form yohlelo LOOKUP yilezi:

= LOOKUP (Lookup_value, Lookup_vector, [Result_vector])

I-Lookup_value (edingekayo) - inani elifundwa ngumsebenzi ku-vector yokuqala. I-Lookup_value ingaba inombolo, umbhalo, inani elilinganiselwe, noma igama noma ireferensi yeseli elibhekisela kunani.

I-Lookup_vector (edingekayo) - ibanga eliqukethe umugqa owodwa noma ikholomu ukuthi umsebenzi usesha ukuthola i- Lookup_value . Idatha ingaba umbhalo, izinombolo, noma izindinganiso ezinengqondo.

Umphumela_ukusebenza (okukhethwa kukho) - ibanga eliqukethe umugqa owodwa noma ikholomu eyodwa. Le mpikiswano kufanele ibe nobukhulu obufanayo no- Lookup_vector .

Amanothi:

• Ukuze umsebenzi we-LOOKUP usebenze kahle, i- Lookup_vector kumele ihlungwe ngokunyuka kwe-oda (A kuya ku-Z noma encane kunazo zonke kunenani elikhulu kunezinombolo )
• Uma ngabe impikiswano ye- Result_vector ishiyiwe, umsebenzi ubuyisela impikiswano ye-Lookup_value uma ikhona ku- Lookup_vector
• Uma ngabe umsebenzi awukwazi ukuthola umdlalo oqondile we-Lookup_value, ukhetha inani elikhulu kunawo wonke ku- Lookup_vector elingaphansi noma elilinganayo lexabiso ku- Lookup_value;
• Uma i- Lookup_value encane kunazo zonke izindinganiso ku- Lookup_vector , umsebenzi we-LOOKUP uzobuyisa iphutha le- # N / A.

03 ka 04

Hlola Isibonelo Somsebenzi

Njengoba kuboniswe esithombeni ngenhla, lesi sibonelo sizosebenzisa ifomu le- Vector le-LOOKUP ngomsebenzi ukuze uthole intengo ye Gear ohlwini lokusebenzisa usebenzisa ifomula elandelayo:

= LOOKUP (D2, D5: D10, E5: E10)

Ukuze kube lula ukufaka izingxabano zomsebenzi, ibhokisi lebhokisi lebhokisi le-LOOKUP lisetshenziswe kulezi zinyathelo ezilandelayo.

1. Chofoza kumakhalekhukhwini E2 kushidi lokusebenzela ukuze wenze iseli elisebenzayo ;
2. Chofoza kuthebhu yefomula yemenyu ye- Ribbon ;
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, lookup_vector, okukhethwa kukho_kukhetha ohlwini ohlwini;
6. Chofoza u-KULUNGILE ukuletha ibhokisi lengxoxo ye- Function Arguments ;
7. Ebhokisini lebhokisi, chofoza kulayini Lookup_value ;
8. Chofoza kuseli D2 kushidi lokufaka ukungena kulolo lwazi lweselenki ebhokisini lebhokisi - kulolu selu sizobe sothayipha igama lesigaba esiliseshayo
9. Chofoza kumzila we- Lookup_vector ebhokisini lengxoxo;
10. Gqamisa amaseli D5 kuya ku-D10 ekhasini lokusebenzela ukuze ufake leli hlu ebhokisini lebhokisi - lolu hlu luqukethe amagama okuyingxenye;
11. Chofoza kumphumela we- Result_vector ebhokisini lengxoxo;
12. Gqamisa amaseli e-E5 kuya ku-E10 kushidi lokusebenzela ukuze ufake lolu hlu ebhokisini lebhokisi - lolu hlu luqukethe amanani ohlwini lwazo izingxenye;
13. Chofoza OK ukuze uqedele umsebenzi bese uvala ibhokisi lengxoxo;
14. Iphutha le- # N / A livela eselini le-E2 ngoba asikafaneli ukuthayipha igama lenxenye kuseli D2

04 ka 04

Ukufaka i-Value Lookup

Chofoza kuseli D2, thayipha i- Gear bese ucindezela ukhiye we- Enter ekhibhodi

1. Inani elilinganiselwa ku-$ 20.21 kufanele livele esitokisini se-E2 ngoba lokhu kuyinani legiya elisekholomu yesibili yethebula lwedatha;
2. Hlola umsebenzi ngokuthayipha amanye amagama okuyingxenye kwiseli D2. Inani lentengo ngayinye ohlwini luzovela esitokisini E2;
3. Uma uchofoza ku-cell E2, umsebenzi ophelele
   = LOOKUP (D2, D5: D10, E5: E10) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela.