Thola Idatha Kuhlu nge-INDEX Umsebenzi

01 ngo-02

I-Excel Umsebenzi we-INDEX - Ifomu le-Array

I-Excel Umsebenzi we-INDEX Uhlolojikelele

Ngokuvamile, umsebenzi we-INDEX ungasetshenziselwa ukuthola nokubuyisela inani elithile noma ukuthola ireferensi yeseli endaweni yaleso senani ekhasini lokusebenzela.

Kunezinhlobo ezimbili zomsebenzi we-INDEX otholakala ku-Excel: Ifomu le- Array kanye neFomu Lokubhekisela.

Umehluko omkhulu phakathi kwezinhlobo ezimbili zomsebenzi uwuku :

• ifomu le-Array libuyisela inani lemininingwane elisezingeni lokuhlangana komugqa othile kanye nekholomu yedatha;
• Ifomu leReferensi libuyisela ireferensi yeseli yephuzu lezinhlangothi zekholomu ethile kanye nomugqa.

I-Excel Umsebenzi we-INDEX - Ifomu le-Array

I- array ngokuvamile ibhekwa njengeqembu lamaseli aseduze kukhasi lokusebenzela. Emfanekisweni ngenhla, i-array yayiyoba ibhokisi lamaseli ukusuka ku-A2 kuya ku-C4.

Kulesi sibonelo, ifomu elihle lomsebenzi we-INDEX osekuceleni kwe-C2 kubuyisa inani lemininingwane - I-Widget - etholakala endaweni ye-intersection elayini 3 nekholomu 2.

Umsebenzi we-INDEX (I-Array Form) I-Syntax ne-Arguments

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

I-syntax yomsebenzi we-INDEX yile:

= INDEX (I-Array, i-Row_num, i-Column_num)

I-Array - Izikhombisi zeseli zezinhlobo zamaseli okufanele zifunwe ngumsebenzi wolwazi olufunayo

I-row_num (okukhethwa kukho) - Inombolo yomugqa emgqeni okuzobuyisela ngayo inani. Uma ngabe le mpikiswano ishiyiwe, ikholamu_nombuzo iyadingeka.

Ikholomu_noma (okukhethwa kukho) - Inomboro yekholomu ohlwini ofuna ukubuyisela ngayo inani. Uma le mpikiswano ishiyiwe, i-Row_num iyadingeka.

• Kuzo zombili ama- Row_num kanye nekholomu_izingqinamba , noma izinombolo zamakholomu kanye nekholomu noma izinkomba zeseli endaweni yolu lwazi ekhasini lokusebenzela lingangena .

Umsebenzi we-INDEX (I-Array Form) Isibonelo

Njengoba kukhonjisiwe, isibonelo esithombeni esingenhla sisebenzisa ifomu le-INDEX ukuze libuyisele i-widget yesikhathi ohlwini lwama-inventory.

Ulwazi olungezansi luhlanganisa izinyathelo ezisetshenziselwa ukungena umsebenzi we-INDEX esitokisini B8 sekhasi lokusebenzela.

Izinyathelo zisebenzisa izinkomba zocingo ze- Row_num kanye ne- Column_num arguments, kunokungena kulezi zinombolo ngokuqondile.

Ukufaka umsebenzi we-INDEX

Izinketho zokufaka umsebenzi kanye neziphakamiso zalo zifaka:

1. Ukuthayipha umsebenzi ophelele: = INDEX (A2: C4, B6, B7) ibe esitokisini B8
2. Ukukhetha umsebenzi nemibono yayo usebenzisa ibhokisi lebhokisi lengxoxo ye- INDEX

Nakuba kungenzeka nje ukuthayipha umsebenzi ophelele ngesandla, abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lengxoxo ukufaka izingxabano zomsebenzi.

Izinyathelo ezingezansi zisebenzisa ibhokisi lengxoxo ukuze ufake izingqinamba zomsebenzi.

Ukuvula ibhokisi lokuxoxa

Njengoba kunezinhlobo ezimbili zomsebenzi - ngayinye eneziphakamiso zazo - ifomu ngalinye lidinga ibhokisi lengxoxo elihlukile.

Ngenxa yalokho, kunesinyathelo esengeziwe ekuvuleni ibhokisi lebhokisi lebhizinisi le-INDEX ungahambisani neminye imisebenzi eminingi ye-Excel. Lesi sinyathelo sithinta ukukhetha ifomu le- Array noma i- Reference form isethi yezimpikiswano.

Ngezansi izinyathelo ezisetshenziselwa ukungena umsebenzi we-INDEX nezimpikiswano zibe esitokisini B8 usebenzisa ibhokisi lengxoxo lomsebenzi.

1. Chofoza kuseli B8 kushidi lokusebenzela - yilapho umsebenzi uzobe khona
2. Chofoza kuthebhu yefomula yemenyu ye- ribbon
3. Khetha Ukubheka kanye neNkhombandlela kusuka kubhebhoni ukuvula uhlu lwehla lokusebenza
4. Chofoza ku- INDEX ohlwini ukuze ulethe ibhokisi lebhokisi le- Arguments - elikuvumela ukuthi ukhethe phakathi kwamafomu we- Array ne- Reference
5. Chofoza ku- array, row_num, ikholomu_num inketho
6. Chofoza ku-Kulungile ukuvula umsebenzi we-INDEX - Ibhokisi lengxoxo yefomu ye-Array

Ukufaka izimpendulo zomsebenzi

1. Ebhokisini lengxoxo, chofoza kulayini we- Array
2. Gqamisa amaseli A2 kuya ku-C4 ekhasini lokusebenzela ukuze ufake ibanga ebhokisini lengxoxo
3. Chofoza ku- Row_num line ebhokisini lengxoxo
4. Chofoza kuseli B6 ukufaka lelo referensi yeseli ebhokisini lengxoxo
5. Chofoza ku- Column_numugqa ebhokisini lengxoxo
6. Chofoza kuseli B7 ukufaka lelo referensi yeseli ebhokisini lengxoxo
7. Chofoza OK ukuqedela umsebenzi bese uvala ibhokisi lengxoxo
8. Igama elithi Gizmo livela esitokisini B8 ngoba liyigama esitokisini eliphakathi komugqa wesithathu kanye nekholomu yesibili yezinqolobane zezingxenye
9. Uma uchofoza ku-cell B8 umsebenzi ophelele = INDEX (A2: C4, B6, B7) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela

I-Index ye-Function Error Values

Amanani wephutha elivamile elihambisana nomsebenzi we-INDEX - Ifomu le-array yilo:

#VALUE! - Ivela uma ngabe i- Row_num , i- Column_numbuzo engezona ayizinombolo .

#REF! - Ivela uma:

• I- Row_num ingxabano inkulu kunezinombolo zemigqa ebangeni elikhethiwe;
• I- Col_num argument mkhulu kunani lamakholomu ebangeni elikhethiwe.

Ibhokisi lezingxoxo ze-Dialog

Izinzuzo zokusebenzisa ibhokisi lengxoxo ukufaka idatha yezingxabano zomsebenzi zihlanganisa:

1. Ibhokisi lebhokisi liyakhathalela i-syntax yomsebenzi - okwenza kube lula ukufaka izingxabano zomsebenzi ngesikhathi esisodwa ngaphandle kokufaka uphawu olulinganayo, amabakaki, noma amakhomishana enza njengabahlukanisi phakathi kwezimpikiswano.
2. Izinkomba zefoni, ezifana ne-B6 noma i-B7, zingangena ebhokisini lengxoxo ngokukhomba , okubandakanya ukuchofoza amaseli akhethiwe negundane kunokuba uzifake. Akugcini nje ukukhomba lula, futhi kusiza ukunciphisa amaphutha emafomula abangelwa izikhombisi zeseli ezingalungile.

02 ka 02

I-Excel Umsebenzi we-INDEX - Ifomu Lokubhekisela

I-Excel Umsebenzi we-INDEX - Ifomu Lokubhekisela

Ifomu yokubhekisela yalomsebenzi ibuyisela inani le-cell elitholakala endaweni ye-intersection yomugqa othile kanye nekholomu yedatha.

I-reference reference ingaba nezindawo eziningi ezingezona eziseduze njengoba kuboniswe esithombeni ngenhla.

Umsebenzi we-INDEX (i-Reference Form) i-Syntax ne-Arguments

I- syntax neziphakamiso zefomu ye-INDEX yokufometha yilezi:

= INDEX (Thatha, Row_num, Column_num, Area_num)

Izikhombisi- (ezidingekayo) izinkomba zezingcingo zohlu lwamaseli okufanele zifunwe ngumsebenzi wolwazi olufunayo.

• uma kunezingxenye eziningi, ezingekho eduze ezifakelwe kule mpikiswano, amareja kumele azungezwe ngesethi ehlukile yamakaki okujikeleza njengoba kuboniswe kumfomu we-INDEX: = INDEX ((A1: A5, C1: E1, C4: D5), B7 , B8) ethathwe esithombeni ngenhla

Row_num - inombolo yomugqa ohlwini ofuna ukubuyisela ngayo inani.

• okukhethwa kukho amareferensi wesigcawu esisodwa
• okudingekayo kukholomu eyodwa kanye nemigqa yochungechunge emigqa eminingi

Ikholomu_noma - inombolo yekholomu kuhlu ofuna ukubuyisela ngayo inani.

• okukhethwa kukho amakholi wekholomu oyedwa
• okudingekayo emgqeni owodwa kanye namakhodi amaningi wekholomu yekholomu

Qaphela: Kuzo zombili ama- Row_num kanye nekholomu_izingqinamba , noma izinombolo zamakholomu namakholomu noma izinkomba zeseli endaweni yolu lwazi ekhasini lokusebenzela lingangena .

Indawo_num (okuzikhethela) - uma i-argument yokubhekisela iqukethe izingxube eziningi ezingahambisani , le mpikiswano ikhetha ukuthi yiliphi uhla lwamaseli ukubuyisela idatha kusuka. Uma ushiywe, umsebenzi usebenzise ibanga lokuqala elibalwe ku-argument yokubhekisela .

• Ibanga lokuqala langena ku-Impikiswano Yokubhekisela libalwa 1;
• okwesibili ngu-2;
• okwesithathu kungu-3, ​​njalonjalo.

Umsebenzi we-INDEX (Ifomula yeFomu) Isibonelo

Isibonelo esithombeni esingenhla sisebenzisa ifomu yokufometha yesenzo se-INDEX ukubuyela ngenyanga kaJulayi kusuka endaweni engu-2 yokufutheka A1 kuya ku-E1.

Ulwazi olungezansi luhlanganisa izinyathelo ezisetshenziselwa ukungena umsebenzi we-INDEX esitokisini B10 kwiphepha lokusebenzela.

Izinyathelo zisebenzisa izinkomba zeselula ku- Row_num, Column_num, naku-Area_num izingxabano, kunokuba ufake lezi zinombolo ngokuqondile.

Ukufaka umsebenzi we-INDEX

Izinketho zokufaka umsebenzi kanye neziphakamiso zalo zifaka:

1. Ukuthayipha umsebenzi ophelele: = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) ibe esitokisini B10
2. Ukukhetha umsebenzi nemibono yayo usebenzisa ibhokisi lebhokisi lengxoxo ye- INDEX

Nakuba kungenzeka nje ukuthayipha umsebenzi ophelele ngesandla, abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lengxoxo ukufaka izingxabano zomsebenzi.

Izinyathelo ezingezansi zisebenzisa ibhokisi lengxoxo ukuze ufake izingqinamba zomsebenzi.

Ukuvula ibhokisi lokuxoxa

Njengoba kunezinhlobo ezimbili zomsebenzi - ngayinye eneziphakamiso zazo - ifomu ngalinye lidinga ibhokisi lengxoxo elihlukile.

Ngenxa yalokho, kunesinyathelo esengeziwe ekuvuleni ibhokisi lebhokisi lebhizinisi le-INDEX ungahambisani neminye imisebenzi eminingi ye-Excel. Lesi sinyathelo sithinta ukukhetha ifomu le- Array noma i- Reference form isethi yezimpikiswano.

Ngezansi izinyathelo ezisetshenziselwa ukungena umsebenzi we-INDEX nama-arguments esitokisini B10 usebenzisa ibhokisi lengxoxo lomsebenzi.

1. Chofoza kuseli B8 kushidi lokusebenzela - yilapho umsebenzi uzobe khona
2. Chofoza kuthebhu yefomula yemenyu ye- ribbon
3. Khetha Ukubheka kanye neNkhombandlela kusuka kubhebhoni ukuvula uhlu lwehla lokusebenza
4. Chofoza ku- INDEX ohlwini ukuze ulethe ibhokisi lebhokisi le- Arguments - elikuvumela ukuthi ukhethe phakathi kwamafomu we- Array ne- Reference
5. Chofoza kureferensi, umugqa_num, ikholomu_num, indawo_nokukhethwa kukho
6. Chofoza ku-Kulungile ukuvula umsebenzi we-INDEX - Ibhokisi lebhokisi lebhokisi lokufometha

Ukufaka izimpendulo zomsebenzi

1. Ebhokisini lengxoxo, chofoza kulayini lokubhekisela
2. Faka ibakaki evulekile " ( " kulolu hlu ebhokisini lengxoxo
3. Gqamisa amaseli A1 kuya ku-A5 kushidi lokusebenzela ukuze ungene ebangeni ngemuva kwe-bracket evulekile
4. Thayipha i-comma ukuze uhlukanise phakathi kwezingxenye zokuqala nezesibili
5. Qinisekisa amaseli C1 kuya ku-E1 kwiphepha lokusebenzela ukuze ungene ebangeni emva kombala
6. Thayipha i-comma yesibili ukuba uhlukanise phakathi kweyesibili neyesithathu
7. Qinisekisa amaseli C4 kuya ku-D5 ekhasini lokusebenzela ukuze ungene ebangeni emva kombala
8. Faka ibhakha lokuvala " ) " emva kobubanzi besithathu ukuqedela ukuphikisana kwe- Reference
9. Chofoza ku- Row_num line ebhokisini lengxoxo
10. Chofoza kuseli B7 ukufaka lelo referensi yeseli ebhokisini lengxoxo
11. Chofoza ku- Column_numugqa ebhokisini lengxoxo
12. Chofoza kuseli ye-B8 ukuze ufake leyo reference yocingo ebhokisini lengxoxo
13. Chofoza ku- Area_num umugqa ebhokisini lengxoxo
14. Chofoza kuseli B9 ukufaka lelo referensi yeseli ebhokisini lengxoxo
15. Chofoza OK ukuqedela umsebenzi bese uvala ibhokisi lengxoxo
16. Inyanga kaJulayi ivela esitokisini B10 kusukela inyanga esitokisini ihlukanisa umugqa wokuqala kanye nekholomu yesibili yendawo yesibili (uhla C1 kuya ku-1)
17. Uma uchofoza ku-cell B8 umsebenzi ophelele = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela

I-Index ye-Function Error Values

Amanani wephutha elivamile elihambisana nomsebenzi we-INDEX - Ifomu Lokubhekisela:

#VALUE! - Ivela uma ngabe i- Row_num , i- Column_num, noma i- Area_num izingxabano ayizona izinombolo.

#REF! - Ivela uma:

• I- Row_num ingxabano inkulu kunezinombolo zemigqa ebangeni elikhethiwe;
• I- Col_num argument mkhulu kunani lamakholomu ebangeni elikhethiwe.
• I- Area_num ingxabano inkulu kunezinombolo zezindawo ebangeni elikhethiwe.