Tsvaga Dhiyabhorosi muRwendo neIndEX Basa

01 ye 02

Excel INDEX Basa - Array Form

Excel INDEX Function Overview

Nenzira yakawanda, INDEX basa rinogona kushandiswa kuwana nekudzorera kukosha kwakananga kana kuwana tsanangudzo yesero kune nzvimbo yekukosha ikoko mune pepa rekushanda.

Kune miviri miviri ye INDEX basa iripo muExcel: Form Array uye Ref Reference Form.

Kusiyana kukuru pakati pemhando mbiri dzebasa nderokuti:

• iyo fomu yeArray inodzorera kukosha kwenhamba iri panzvimbo yepakati penzira yemumwe musara uye column ye data;
• iyo Refomu inodzorera sero rinotenderera remutsara wepakati peimwe shongwe nemutsara.

Excel INDEX Basa - Array Form

Mhando yakawanda inofungidzirwa seboka remajeri ari pedo mune pepa rekushanda. Mumufananidzo wepamusoro, mhando yaizova ibwe remasero kubva A2 kusvika C4.

Mumuenzaniso uyu, maitiro akafanana e INDEX basa iri muchitokisi C2 anodzoka kukosha kwe data - Widget - inowanikwa pamativi ekutenderera emutsara 3 nekamupiro 2.

Iyo INDEX Basa (Array Form) Syntax uye Arguments

Mutsara webasa unoreva kurongeka kwebasa racho uye rinosanganisira zita rebasa, mabheji, vatengesi vemavara, uye nharo .

Mutsara we INDEX basa ndewokuti:

= INDEX (Array, Row_num, Column_num)

Zvirongwa - Zvinyorwa zveseti zvemazana emasero kuti aongorwe nebasa rehuwandu hunoda

Row_num (zvisarudzo) - Ramba rutsara mumatsetse ekudzorera kukosha. Kana nharo iyi isingabvumirwi, Column_nhamba inodiwa.

Column_num (kusarudza) - Iyo nhamba yekorofa mune imwe nzira inogona kudzorera kukosha. Kana nharo iyi isingabvumirwi, Row_num inodiwa.

• Kune zvose Row_num neColumn_num arguments, zvichida mutsara chaiyo uye nhamba dzekholomu kana kuti zvinyorwa zvesepi kunzvimbo yeruzivo iri mune pepa rekushandisa zvinogona kupinda.

INDEX Basa (Array Form) Muenzaniso

Sezvakataurwa, muenzaniso mumufananidzo uri pamusoro apa unoshandisa fomu yeArray ye INDEX basa kuti adzore izwi rokuti Widget kubva pakutsvaga urongwa.

Izwi iri pasi apa rinoshandisa matanho anoshandiswa kupinda INEX basa muchikamu B8 chebasa rekushanda.

Nhanho dzinoshandisa kushandiswa kwemasero kweRow_num neColumn_num arguments, panzvimbo pokupinda nhamba idzi zvakananga.

Kupinda INEX Function

Zvisarudzo zvekupinda mubasa uye nharo dzayo dzinosanganisira:

1. Kunyora basa rakakwana: = INDEX (A2: C4, B6, B7) muchikamu B8
2. Kusarudza basa uye nharo dzaro kushandisa INDEX basa dialog box

Kunyange zvazvo zvichibvira kungonyora basa rakakwana mumunhu, vanhu vazhinji vanoona zviri nyore kushandisa bhokisi rebhokisi kuti vapinde nharo dzebasa.

Matanho ari pasi apa anoshandisa bhokisi rebhokisi kuti uise mhinduro dzebasa.

Kuvhura Bhokisi reDhialog

Sezvo pane maitiro maviri ehutano - mumwe nomumwe ane zvigaro zvake zvepikisano - fomu imwe neimwe inoda bhokisi rebhokisi rega rega.

Semugumisiro, pane danho rinowedzera mukuzarura INDEX basa dialog box kwete nehumwe humwe mabasa eExcel. Iyi danho rinosanganisira kutora fomu yeArray kana Refomu fomu yezvakakavhiringidza.

Pasi pane matanho anoshandiswa kupinda mu INDEX basa uye nharo muchitokisi B8 uchishandisa basa rebhokisi rebhokisi.

1. Dzvanya pane cell B8 mune pepa rekushanda - iyi ndiyo iyo basa richawanikwa
2. Dzvanya paMafomu yebhu yebhoni yebhoni
3. Sarudza Kuona uye Kutarisa kubva kubhoni kuti uzarure basa rinoderera pasi
4. Dzvanya pa INDEX muurongwa kuunza Sarudzo dzeArguments dialog box - iyo inokuita kuti usarudze pakati peAra and Reference Reference maitiro
5. Dzvanya pamutsetse , mutsara_num, column_num chokusarudza
6. Dzvanya kuri OK kuti uzarure INDEX basa - Array fomu dialog box

Kupinda Maitiro Ebasa

1. Mubhokisi rebhokisi, chengetera pane Array line
2. Simbisai masero A2 kusvika kuC4 mubasa rekushanda kuti uise bhora mubhokisi rebhokisi
3. Dzvanya paRow_num line mubhokisi rebhokisi
4. Dzvanyira pane sero B6 kuti uise iyo iyo seferensi mubhokisi rebhokisi
5. Dzvanya paColumn_numurongo mubhokisi rebhokisi
6. Dzvanya pachitokisi B7 kuti uise iyo iyo seferensi mubhokisi rebhokisi
7. Dzvanya OK kuti upedze basa uye uvhare bhokisi rebhokisi
8. Izwi Gizmo rinowanikwa muchitokisi B8 sezvo iri izwi mumusero inoparadzanisa mutsara wechitatu uye wechipiri yekamuri yezvikamu zvinyorwa
9. Paunenge uchinyora pa cell B8 basa rakakwana = INDEX (A2: C4, B6, B7) inowanikwa mubhairi yepamusoro pane pepa rekushanda

Index Maitiro Maitiro Maitiro

Nzira yepamutemo yekukanganisa inobatanidza ne INDEX basa - Fomu yemazera ndeyi:

#VALUE! - Inoitika kana imwe Row_num , Column_num arguments isiri nhamba.

#REF! - Inoitika kana imwe:

• Iyo Row_num argument yakakura kudarika nhamba yemigwagwa muurongwa hwakasarudzwa;
• The Col_num argument yakakura kudarika nhamba yemakoroni mumasarudzo akasarudzwa.

Bhokisi reDhiyabhorosi Mazano

Makomborero ekushandisa ibhokisi rebhokisi kuti uise deta yemashoko ekushanda anosanganisira:

1. Bhokisi rebhokisi rinotarisira sangano rebasa - zvichiita kuti zvive nyore kupinda mhinduro dzebasa rimwe chete pane imwe nguva pasina kuisa chiratidzo chakaenzana, mabheji, kana kuti makasita anoita separarator pakati pezvakaitika.
2. Nhamba dzezviratidzo, dzakadai seB6 kana B7, dzinogona kupinda mubhokisi rebhokisi nekuratidza , izvo zvinosanganisira kuvhara masero akasarudzwa ane mouse kunze kwekuti kuisa mukati. Haisi kungoerekana ichitaura nyore, inobatsira kuderedza mapurogiramu mumarangariro anokonzerwa ne isina kukodzera sekare zvinyorwa.

02 of 02

Excel INDEX Function - Reference Form

Excel INDEX Function - Reference Form

Iyo inorondedzerwa yehutano inodzorera kukosha kwekutaurwa kwesero iri panzvimbo yepakati penzira yemutsetse wakatarwa uye koromo yedata.

Iyo inongororwa inogona kunge yakagadzirwa nematunhu akawanda asingasviki akafanana neakaratidzwa mumufananidzo uri pamusoro.

Iyo INDEX Basa (Reference Reference) Syntax uye Arguments

Izwi rechirevo uye nharo dze INDEX basa Fomu refomu ndei:

= INDEX (Refer, Row_num, Column_num, Area_num)

Dheuteronomio - (inoda) zvinyorwa zveseti zvemazana emasero kuti aongorwe nebasa remashoko anoda.

• kana zvinyorwa zvisiri izvo zvakasvibiswa zvakapindirwa pamhaka iyi, mitsara inofanira kunge yakapoteredzwa neketani yakasiyana yezvikwereti zvakapoteredza sezvinoratidzwa mu INDEX fomu: = INDEX ((A1: A5, C1: E1, C4: D5), B7 , B8) yatorwa kubva mumufananidzo uri pamusoro

Row_num - mutsetse nhamba mumutsetse kubva kwaunodzorera kukosha.

• zvingasarudzwa pamutsara mumwechete Zviratidzo
• inotarisirwa kuti isarudze imwechete uye mutsara wakawanda Zvirongwa zvese

Column_num - iyo nhamba yekorodhe mune imwe nzira inogona kudzorera kukosha.

• sarudzo yega imwe yerumende Zviratidzo zvese
• inotarisirwa kumutsara mumwechete uye yakawanda columende Referense

Cherechedza: Kwese Row_num neColumn_numongorudzo , kana mutsara chaiwo uye nhamba dzomumakamuri kana kuti zvinyorwa zvesepi kunzvimbo yeruzivo iri mubhuku rinogona kuiswa.

Nzvimbo_num (optional) - kana nhaurirano yeReferenti ine mitsara yakawanda isingasviki, iyo nhaurwa inosarudza kuti mazana emasero anodzoka sei kubva kune. Kana kusina kubviswa, basa racho rinoshandisa bhora rokutanga rakanyorwa muChirangariro cheReferensi.

• Bhuku rokutanga rakapinda muChirangariro reChirangaridzo inowanikwa 1;
• wechipiri ndeyechipiri;
• yechitatu yechitatu, nezvimwe zvakadaro.

INDEX Basa (Reference Form) Muenzaniso

Muenzaniso uri mumufananidzo uri pamusoro apa unoshandisa Fomu yeChirevo che INDEX basa kudzoka mwedzi waJuly kubva munzvimbo 2 yekutsamwa A1 kusvika E1.

Izwi iri pasi apa rinoshandisa matanho anoshandiswa kupinda INEX basa muchikamu B10 chebasa rekushanda.

Nhanho dzinoshandisa sero rekushandisa kweRow_num, Column_num, uye Area_num arguments, pane kupinda maamba aya zvakananga.

Kupinda INEX Function

Zvisarudzo zvekupinda mubasa uye nharo dzayo dzinosanganisira:

1. Kunyora basa rakakwana: = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) muchitokisi B10
2. Kusarudza basa uye nharo dzaro kushandisa INDEX basa dialog box

Kunyange zvazvo zvichibvira kungonyora basa rakakwana mumunhu, vanhu vazhinji vanoona zviri nyore kushandisa bhokisi rebhokisi kuti vapinde nharo dzebasa.

Matanho ari pasi apa anoshandisa bhokisi rebhokisi kuti uise mhinduro dzebasa.

Kuvhura Bhokisi reDhialog

Sezvo pane maitiro maviri ehutano - mumwe nomumwe ane zvigaro zvake zvepikisano - fomu imwe neimwe inoda bhokisi rebhokisi rega rega.

Semugumisiro, pane danho rinowedzera mukuzarura INDEX basa dialog box kwete nehumwe humwe mabasa eExcel. Iyi danho rinosanganisira kutora fomu yeArray kana Refomu fomu yezvakakavhiringidza.

Pasi pane matanho anoshandiswa kupinda INEX basa uye nharo muSeroti B10 uchishandisa basa rebhokisi rebhokisi.

1. Dzvanya pane cell B8 mune pepa rekushanda - iyi ndiyo iyo basa richawanikwa
2. Dzvanya paMafomu yebhu yebhoni yebhoni
3. Sarudza Kuona uye Kutarisa kubva kubhoni kuti uzarure basa rinoderera pasi
4. Dzvanya pa INDEX muurongwa kuunza Sarudzo dzeArguments dialog box - iyo inokuita kuti usarudze pakati peAra and Reference Reference maitiro
5. Dzvanya pane rinoreva, row_num, column_num, area_num option
6. Dzvanya kuri OK kuti uzarure INDEX basa - Refomu fomu dialog box

Kupinda Maitiro Ebasa

1. Mubhokisi rebhokisi, chengetera pane Referenced line
2. Pinda bhora rakazaruka " ( " pane iri mutsara mubhokisi rebhokisi
3. Simbisai masero A1 kusvika kuA5 mubasa rekushanda kuti uise mazamu mushure mekuvhara bhora
4. Tora comma kuti uve separator pakati pekutanga uye yechipiri
5. Simbisai masero C1 kusvika kuE1 mubasa rekushanda kuti uise mazamu mushure mekuda
6. Nyora yechipiri comma kuti uve separator pakati pechipiri neyeshatu
7. Simbisai masero C4 kusvika kuD5 mubasa rekushanda kuti uise mazamu mushure mokutora
8. Pinda bhizinesi rakapoteredza " ) " mushure mechikamu chechitatu chekuzadzisa Reference argument
9. Dzvanya paRow_num line mubhokisi rebhokisi
10. Dzvanya pachitokisi B7 kuti uise iyo iyo seferensi mubhokisi rebhokisi
11. Dzvanya paColumn_numurongo mubhokisi rebhokisi
12. Dzvanya pane sero B8 kuti uise iyo seferensi iri mubhokisi rebhokisi
13. Dzvanya paNzvimbo_numurongo mubhokisi rebhokisi
14. Dzvanya pane sero B9 kuti uise iyo sero rekuisa mubhokisi rebhokisi
15. Dzvanya OK kuti upedze basa uye uvhare bhokisi rebhokisi
16. Mwedzi waJuly unoonekwa muchitokisi B10 sezvo iri mwedzi iri muchitokisi ichibatanidza mutsara wokutanga uye wechipiri muromo wechipiri nzvimbo (rangara C1 kusvika 1)
17. Paunenge uchinyora pavha B8 basa rakakwana = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) inowanikwa mubhairi yepamusoro pane pepa rekushanda

Index Maitiro Maitiro Maitiro

Mhosva yepamutemo yepamusoro inobatanidza ne INDEX basa - Refomu Fomu ndeyokuti:

#VALUE! - Inosvika kana imwe Row_num , Column_num, kana Area_num nharo dzisiri nhamba.

#REF! - Inoitika kana:

• Iyo Row_num argument yakakura kudarika nhamba yemigwagwa muurongwa hwakasarudzwa;
• The Col_num argument yakakura kudarika nhamba yemakoroni mumasarudzo akasarudzwa.
• The Area_num nharo yakakura kudarika nhamba yenzvimbo dziri mumutsetse wakasarudzwa.