Fumana Iinkcukacha kuludwe nge-INDEX Umsebenzi

01 ka 02

I-Excel I-INDEX Umsebenzi - Ifom yeFom

Fumana Iinkcukacha Kuludwe nge-INDEX Umsebenzi - Ifom ye-Array. © TedFrench

I-Excel INDEX Umsebenzi ngokubanzi

Ngokubanzi, umsebenzi we-INDEX ungasetyenziselwa ukufumana nokubuyisela ixabiso elithile okanye ufumane isalathisi yesalathisi kwindawo yolu xabiso kwiphepha lokusebenzela.

Kukho iifom ezimbini ze-INDEX umsebenzi okhoyo kwi-Excel: Ifomu ye-Array kunye neFom Reference Reference.

Umehluko omkhulu phakathi kweendlela ezimbini zomsebenzi :

I-Excel I-INDEX Umsebenzi - Ifom yeFom

Iqela ngokuqhelekileyo libhekwa njengeqela leeseli eziseduze kwiphepha lokusebenzela. Kulo mfanekiso ngasentla, uluhlu luya kuba yibloko leeseli ukusuka ku-A2 ukuya ku-C4.

Kulo mzekelo, ifom yefom ye-INDEX isebenze kwiselfowuni C2 ubuyisela ixabiso lwedatha - I-widget - ifunyenwe kwinqanaba le-intersection kwinqanaba 3 kunye nekholamu 2.

Umsebenzi we-INDEX (Ifomu ye-Array) I-Syntax kunye neArguments

I- syntax yomsebenzi ibhekisela kwisakhiwo somsebenzi kwaye ibandakanya igama lomsebenzi, iibakaki, abahluli be-comma kunye neengxabano .

I-syntax yomsebenzi we-INDEX ngu:

= INDEX (I-Array, Row_num, Column_num)

I-Array - Iingqinisiso zeeseli zoluhlu lweeseli eziza kuphandwa ngumsebenzi wolwazi olufunayo

I-Row_num (okukhethwa kukho) - Inombolo yomqolo kwi-array apho ubuyisela khona ixabiso. Ukuba le ngxabano ishiywe, ikholam_nomfuneko iyadingeka.

Ikholamu_nom (okukhethwa kukho) - Inombolo yekholomu kwinqanaba apho ubuyisela khona ixabiso. Ukuba le ngxabano ishiywe, i-Row_num iyadingeka.

Umsebenzi we-INDEX (Ifomu ye-Array) Umzekelo

Njengoko kubonisiwe, umzekelo kumfanekiso ongasentla usebenzisa ifomu ye- Array yomsebenzi we-INDEX ukubuyisela i-widget yegama kwi-uluhlu lwamaxabiso.

Ingcaciso engezantsi ihlanganisa amanyathelo asetyenziswa ukufaka umsebenzi we-INDEX kwiseli B8 kwiphepha lokusebenzela.

Amanyathelo asebenzisa iireferensi zeeseli kwiRow_num kunye neColumn_num arguments, kunokuba afake la manani ngqo.

Ukufaka umsebenzi we-INDEX

Izinketho zokungena kumsebenzi kunye neengxabano zayo ziquka:

  1. Ukuthayipha umsebenzi opheleleyo: = INDEX (A2: C4, B6, B7) kwi-cell B8
  2. Ukukhetha umsebenzi kunye neengxabano zayo usebenzisa ibhokisi yencoko yencoko ye -INDEX

Nangona kunokwenzeka ukuba uthayiphe kuphela umsebenzi opheleleyo ngesandla, abantu abaninzi bakuthola kulula ukusebenzisa ibhokisi yencoko yababini ukufaka iingxabano zomsebenzi.

Amanyathelo angezantsi sebenzisa ibhokisi yencoko yokufaka iingxoxo zomsebenzi.

Ukuvula Ibhokisi yeDialog

Ekubeni kukho iifom ezimbini zomsebenzi - ngamnye ngesiseko sazo seengxabano - ifom nganye nganye idinga ibhokisi yencoko yencoko.

Ngenxa yoko, kukho inyathelo elongezelelweyo ekuvuleni ibhokisi yencoko yebhokisi yencoko ye-INDEX ingabonakali kunye neminye imisebenzi ye-Excel. Eli nyathelo liquka ukukhetha ifom ye- Array okanye ifom yefom yeefomathi yeengxabano.

Ngalansi ngamanyathelo asetyenziswayo ukufaka umsebenzi we-INDEX kunye neengxabano kwisisele seB8 usebenzisa ibhokisi yencoko yencoko yomsebenzi.

  1. Cofa kwifayili B8 kwiphepha lokusebenzela - lo msebenzi uya kuba khona
  2. Cofa kwiTrafom ithebhu yemenyu yebhondi
  3. Khetha uLookup kunye neNkcukacha kwi-Ribbon ukuvula uluhlu lokuhla kwehla
  4. Cofa kwi- INDEX kwoluhlu ukuza kukhetha ibhokisi yencoko yeeArguments - okukuvumela ukuba ukhethe phakathi kweefom ze- Array kunye neeReferensi zomsebenzi
  5. Cofa kwi- array, row_num, column_num option
  6. Cofa kwi-OK ukuvula umsebenzi we-INDEX - Ibhokisi yencoko yencoko yefayile

Ukungena kwiMigqaliselo yeMisebenzi

  1. Kwibhokisi yencoko, nqakraza kumgca we- Array
  2. Phakamisa iiseli ezi-A2 ukuya ku-C4 kwiphepha lokusebenzela ukuze ufake ibhola kwibhokisi yencoko
  3. Cofa kwi Row_num line kwibhokisi yencoko
  4. Cofa kwifowuni B6 ukufaka loo ntetho yeselenki kwibhokisi yencoko
  5. Cofa kwiKholam_nomgca kwibhokisi yencoko
  6. Cofa kwifowuni B7 ukungena kuloo fom yefayile kwibhokisi yencoko
  7. Cofa u-OK ukugqiba umsebenzi kwaye uvale ibhokisi yencoko
  8. Igama elithi Gizmo livela kwiseli ye-B8 kuba lixesha kwiiseli edibanisa umgca wesithathu kunye nekholamu yesibini yezinto ezisetyenzisiweyo
  9. Xa ucofa kwi-cell B8 umsebenzi opheleleyo = INDEX (A2: C4, B6, B7) ibonakala kwifom yefomula ngaphezulu kwiphepha lokusebenzela

Ikhompyutheni yomsebenzi Iphutha leMilinganiselo

Iimpazamo eziqhelekileyo zokuphutha ezihambelana nomsebenzi we-INDEX - Ifom ye-Array yile:

#VALUE! - Iyenzeka ukuba nayiphi i- Row_num , i- Column_num arguments are not numbers.

#REF! - Iyenzeka ukuba:

Ibhokisi leNgcaciso

Inzuzo yokusebenzisa ibhokisi yencoko yababini ukufaka idatha yeengxabano zomsebenzi ziquka:

  1. Ibhokisi yencoko ibinakekisa i-syntax yomsebenzi - yenza kube lula ukufaka iingxabano zomsebenzi omnye ngexesha ngaphandle kokungena uphawu olulinganayo, iibakaki, okanye ii-commas ezenza njengabahluli phakathi kweengxabano.
  2. Izikhombisi zeselula, ezifana neB6 okanye iB7, zingeniswa kwibhokisi yencoko ngokubonisa , oku kuquka ukucofa kwiiseli ezikhethiweyo kunye nemouse kunokuba uzifake. Akukho kuphela ukukhomba okulula, kunceda ukunciphisa amaphutha kwiimfomula ezibangelwa izikhokelo zeseli ezingalunganga.

02 we 02

I-Excel I-INDEX Umsebenzi - Ifom yeReferensi

Fumana Iinkcukacha Kuludwe nge-INDEX Umsebenzi - Ifom yeReferensi. © TedFrench

I-Excel I-INDEX Umsebenzi - Ifom yeReferensi

Ifom yesalathiso yomsebenzi ubuyisela ixabiso lwedatha yeseli esecaleni lokungqamana lomqolo othile kunye nekholomu yedatha.

Uluhlu lweenkcukacha lunokuthi luqulethwe ngamanqanaba amaninzi angaqhelekanga njengoko kuboniswe kumfanekiso ongentla.

Umsebenzi we-INDEX (ifomu yokuFomula) i-Syntax kunye neArguments

I- syntax kunye neengxabano zefom ye-INDEX Ifom yeReferensi yile:

= INDEX (Khangela, Row_num, Column_num, Area_num)

Iimbekiselo- (ezifunekayo) iiferensi zeeseli zoluhlu lweeseli eziza kuphandwa ngumsebenzi wolwazi olufunayo.

I-Row_num - inombolo yomqolo kwi-array apho ubuyisela khona ixabiso.

Ikholamu_nom - inombolo yekholomu kwinqanaba apho ubuyisela khona ixabiso.

Qaphela: Konke zombini i- Row_num kunye neKholam_nokubhaliweyo , nokuba ngamanani omqolo kunye namakholomu okanye ukuboniswa kweeseli kwindawo yolu lwazi kwiphepha lokusebenzela lingangeniswa.

Indawo_num (ukhetho) - ukuba impikiswano yokubhekisa iqulethe amaninzi amaninzi angaqhelekanga, le ngxabano ikhetha uluphi uhlobo lweeseli ukubuyisela idatha ukusuka. Ukuba ushiyiweyo, umsebenzi usebenzise uluhlu lokuqala oludweliswe kwingxabano yokuBhekisela.

Umsebenzi we-INDEX (Ifomu yokuFomelwa) Umzekelo

Umzekelo kumfanekiso ongasentla usebenzisa ifom yokufakela yomsebenzi we-INDEX ukubuyisela inyanga ngenyanga kaJulayi ukusuka kummandla wesi-2 we-A1 ukuya ku-E1.

Ingcaciso engezantsi iquka amanyathelo asetyenziswa ukufaka umsebenzi we-INDEX kwiseli B10 kwiphepha lokusebenzela.

Amanyathelo asebenzisa iireferensi zeseli kwiRow_num, iColumn_num, kunye neNgingqi_numxholo , kunokuba ungene kule manani ngqo.

Ukufaka umsebenzi we-INDEX

Izinketho zokungena kumsebenzi kunye neengxabano zayo ziquka:

  1. Ukuthayipha umsebenzi opheleleyo: = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) kwi-cell B10
  2. Ukukhetha umsebenzi kunye neengxabano zayo usebenzisa ibhokisi yencoko yencoko ye -INDEX

Nangona kunokwenzeka ukuba uthayiphe kuphela umsebenzi opheleleyo ngesandla, abantu abaninzi bakuthola kulula ukusebenzisa ibhokisi yencoko yababini ukufaka iingxabano zomsebenzi.

Amanyathelo angezantsi sebenzisa ibhokisi yencoko yokufaka iingxoxo zomsebenzi.

Ukuvula Ibhokisi yeDialog

Ekubeni kukho iifom ezimbini zomsebenzi - ngamnye ngesiseko sazo seengxabano - ifom nganye nganye idinga ibhokisi yencoko yencoko.

Ngenxa yoko, kukho inyathelo elongezelelweyo ekuvuleni ibhokisi yencoko yebhokisi yencoko ye-INDEX ingabonakali kunye neminye imisebenzi ye-Excel. Eli nyathelo liquka ukukhetha ifom ye- Array okanye ifom yefom yeefomathi yeengxabano.

Ngezantsi zi nyathelo ezisetyenziselwa ukungena umsebenzi we-INDEX kunye neengxabano kwiseli B10 usebenzisa ibhokisi yencoko yencoko yomsebenzi.

  1. Cofa kwifayili B8 kwiphepha lokusebenzela - lo msebenzi uya kuba khona
  2. Cofa kwiTrafom ithebhu yemenyu yebhondi
  3. Khetha uLookup kunye neNkcukacha kwi-Ribbon ukuvula uluhlu lokuhla kwehla
  4. Cofa kwi- INDEX kwoluhlu ukuza kukhetha ibhokisi yencoko yeeArguments - okukuvumela ukuba ukhethe phakathi kweefom ze- Array kunye neeReferensi zomsebenzi
  5. Cofa kwi- reference, row_num, column_num, indawo_num option
  6. Cofa kwi-OK ukuvula umsebenzi we-INDEX - Ibhokisi yencoko yencoko yencoko

Ukungena kwiMigqaliselo yeMisebenzi

  1. Kwibhokisi yencoko, nqakraza kumgca wokubhekisa
  2. Faka ibakaki evulekileyo " ( " kulo mgca kwibhokisi yencoko
  3. Phakamisa iiseli ezi-A1 ukuya ku-A5 kwiphepha lokusebenzela ukuze ungene kwibanga emva kwe-bracket evulekile
  4. Thayipha i-comma ukuba wenze ukwahlula phakathi kweendawo zokuqala nezesibini
  5. Phakamisa iiseli C1 ukuya kwi-E kwiphepha lokusebenzela ukufaka uluhlu emva kohlobo lwe-comma
  6. Thayipha i-comma yesibini ukuba usebenze njengohluli phakathi kweesibini kunye neyesithathu
  7. Phakamisa iiseli zeC4 ukuya kwi-D5 kwiphepha lokusebenzela ukuze ungene kwibanga emva kohlobo lwe-comma
  8. Faka ibhondi yokuvala ejikelezayo " ) " emva kohlu lwesithathu ukugqiba impikiswano yokuFaka
  9. Cofa kwi Row_num line kwibhokisi yencoko
  10. Cofa kwifowuni B7 ukungena kuloo fom yefayile kwibhokisi yencoko
  11. Cofa kwiKholam_nomgca kwibhokisi yencoko
  12. Cofa kwifowuni B8 ukungena kweso senzo kwibhokisi yencoko
  13. Cofa kwi- Area_num line kwibhokisi yencoko
  14. Cofa kwifowuni B9 ukufaka loo ntetho yeselenki kwibhokisi yencoko
  15. Cofa u-OK ukugqiba umsebenzi kwaye uvale ibhokisi yencoko
  16. Inyanga kaJulayi ivela kwiseli ye-B10 kuba yinyanga enkundleni isetyenziselwa umqolo wokuqala kunye nekholamu yesibini yendawo yesibini (udidi C1 ukuya ku-1)
  17. Xa ucofa kwi-cell B8 umsebenzi opheleleyo = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) ibonakala kwifom yefomula ngaphezulu kwiphepha lokusebenzela

Ikhompyutheni yomsebenzi Iphutha leMilinganiselo

Iimpazamo eziqhelekileyo zokuphutha ezihambelana nomsebenzi we-INDEX - Ifomu yokuBhekiswa yile:

#VALUE! - Iyenzeka ukuba nayiphi i- Row_num , iColumn_num, okanye i- Area_num arguments are not numbers.

#REF! - Iyakwenzeka ukuba: