Imisebenzi ye-Excel yokusebenza ngezixhumanisi nama-array

Kulesi sibonelo, uzofunda konke mayelana nemisebenzi ye-Excel yokusebenza ngezixhumanisi nama-array, njenge VPR, I-GPR, OKUNINGI KUDANWE, INDEX и UKUKHETHA.

VPR

Umsebenzi VPR (I-VLOOKUP) ibheka inani elikukholomu kwesokunxele kwethebula futhi ibuyisela inani leseli kukholomu eshiwo yomugqa ofanayo.

1. Ifaka umsebenzi VPR:

   =ВПР(A2;$E$4:$G$7;3;ЛОЖЬ)

   =VLOOKUP(A2,$E$4:$G$7,3,FALSE)

   Incazelo:

   • Umsebenzi VPR ngifuna inani ID (104) kukholamu kwesokunxele esiphezulu sobubanzi $E$4:$G$7 futhi ibuyisela inani elisuka kukholomu yesithathu yomugqa ofanayo (njengoba impikiswano yesithathu yomsebenzi ingu-3).
   • I-agumenti yesine yomsebenzi ithi AMANGA (AMANGA) – lokhu kusho ukuthi kuzotholakala okufanayo ngqo, noma umlayezo wephutha uzovezwa # N / A (#N/A).
2. Hudula igundane ukuze ukopishe umsebenzi VPR kusuka esitokisini B2 phansi kwekholomu kuseli B11.Incazelo: Uma sikopisha umsebenzi VPR phansi, isixhumanisi esiphelele $E$4:$G$7 ihlala ingashintshiwe, kuyilapho ireferensi ehlobene A2 izinguquko ku- A3, A4, A5 njalo njalo.

I-GPR

Umsebenzi usebenza ngendlela efanayo. I-GPR (HLOOKUP):

OKUNINGI KUDANWE

Umsebenzi OKUNINGI KUDANWE (MATCH) ibuyisela indawo yenani eliseshiwe ebangeni elinikeziwe:

Incazelo:

• iZwi Yellow ithatha indawo yesithathu ebangeni E4:E7.
• Ukuphikisana komsebenzi wesithathu kungokuzithandela. Uma ufaka inani lale mpikiswano 0 (zero), bese umsebenzi uzobuyisela indawo yento efana ncamashi nevelu eliseshiwe (A2). Uma ukufana ngqo kungatholakali, umsebenzi uzobuyisela iphutha. # N / A (#N/A).

INDEX

Umsebenzi INDEX (INDEX) ibuyisela inani elinikeziwe ukusuka kububanzi obuyizinhlangothi ezimbili noma obunye.

Incazelo: Incazelo 92 isempambanweni yomugqa 3 kanye nekholomu 2 ebangeni E4:F7.

Incazelo: Incazelo 97 itholakala 3 indawo ebangeni E4:E7.

UKUKHETHA

Umsebenzi UKUKHETHA (KHETHA) ukhetha inani ohlwini olukunombolo yendawo enikeziwe.

Incazelo: Izwi Boat usesikhundleni 3.