Excel Kuwana Purogiramu neMatambudziko mazhinji

Nekushandisa mazano eArcel inogona kuumba fomu yekutsvaga inoshandisa nzira dzakawanda kuwana ruzivo mune d database kana tafura ye data.

Nzira yekushandisa inosanganisira kunyanya basa reMATCH mukati me INDEX basa.

Izvi zvidzidzo zvinosanganisira muenzaniso wedanho nechimwe chekusika fomu yekutsvaga iyo inoshandisa nzira dzakawanda dzekutsvaga mutengi we titanium Widget mune imwe shanduro yekudhinda.

Kutevera matanho mumusoro wekudzidziswa kwemuzasi kunokufambisa kuburikidza nekusika uye kushandisa shanduko inoonekwa mumufananidzo uri pamusoro.

01 ye 09

Kupinda Tutorial Data

Nhanho yekutanga mune zvidzidzo ndeyekuisa deta muExpcel worksheet .

Mukutevera kutevera matanho ari mudzidzisi pinda data inoratidzwa mumufananidzo pamusoro pemasero anotevera.

• Ita huwandu hwemashoko makuru mumasero D1 kusvika kuF2
• Pinda muganhu wechipiri mumasero D5 kusvika F11

Mitsara 3 ne4 inosara isina chinhu kuitira kuti igadzire mazano akaumbwa panguva ino.

Dzidzo haisanganisi kuumbwa kunoonekwa mumufananidzo, asi izvi hazvingabatsiri maitiro ekutevedza kwekushandisa.

Mashoko pamusoro pekugadzirisa mafungiro akafanana neaya akaonekwa pamusoro apa anowanika mune iyi Basic Excel Kuburitsa Tutori.

02 of 09

Kutanga INDEX Basa

Iyo INDEX basa ndeimwe yevashomanana mu Excel ine mafomu akawanda. Basa racho rine Form Form uye Ref Reference Form .

Iyo Fomu yeArray inodzorera iyo chaiyo data kubva kune database kana tafura ye data, nepo Refomu Yokufambisa inokupa sero renzvimbo kana nzvimbo ye data iri patafura.

Muchidzidzo ichi tichashandisa Fomu yeArray sezvo tichida kuziva zita remutengesi weti titaniti rakasarudzwa pane kuti sero rinotaura kune uyu mutengesi mune database yedu.

Fomu imwe neimwe ine mhando yakasiyana yepikisano inofanira kusarudzwa asati yatanga basa.

Tutorial Steps

1. Dzvanya pachitokisi F3 kuti uite sero rinoshanda . Apa ndipo patinopinda nenyuchi basa.
2. Dzvanya paMafomu yebhu yebhoni yebhoni .
3. Sarudza Kuona uye Kutarisa kubva kubhoni kuti uzarure basa rinoderera pasi.
4. Dzvanya pa INDEX muurongwa kuti uuye neChirongwa cheArguments dialog box .
5. Sarudza mhando, row_num, col_num sarudzo mubhokisi rebhokisi.
6. Dzvanya OK kuti uzarure INDEX basa dialog box.

03 of 09

Kupinda INDEX Function Array Argument

Nharo yekutanga inodiwa ndiyo Array argument. Iyi nharo inotsanangura huwandu hwemasero kuti hutsvakwe data yakada.

Nokuda kweikudzidziswa iyi nharo ichava shanduro yedu yekushandiswa .

Tutorial Steps

1. Muchi INDEX basa dialog box , tora paAraire line.
2. Dzorerai masero D6 kusvika F11 mubasa rekushandisa kuti uise bhora mubhokisi rebhokisi.

04 of 09

Kutanga Nested MATCH Function

Kana kuvhara chimwe chinhu mukati memumwe hazvibviri kuzarura chikamu chechipiri kana chakasvibiswa chebhokisi rebasa kuti uise mhinduro dzinodikanwa.

Ichinhu chakasara chinoshanda chinofanira kunge chakanyorwa seimwe yemashoko ekutanga basa.

Muchidzidzo ichi, chakasvibiswa MATCH basa uye nharo dzaro dzichapinda mumutsara wechipiri we INDEX basa dialog box - Row_num line.

Zvakakosha kucherechedza kuti, paunopinda maitiro nemaune, nharo dzebasa dzakaparadzana kubva kune mumwe nemumwe " comma ", " .

Kupinda muMATCH Function's Lookup_value Argument

Nhanho yekutanga yekupinda munzvimbo yakasvibiswa MATCH basa ndeyokupinza mhirizhonga ye Lookup_value .

The Lookup_value ichava nzvimbo kana sero renhoroondo yezwi rekutsvaga ratinofanira kufanana naro mu database.

Kazhinji Lookup_value inogamuchira imwe chete yekutsvaga nzira kana izwi. Kuti titsvake mitemo yakawanda, tinofanira kuderedza Lookup_value .

Izvi zvinoitwa nekubvumirana kana kubatanidza maviri kana kupfuura mamwe masero ezvinyorwa pamwe chete achishandisa chiratidzo che ampersand " & ".

Tutorial Steps

1. Muchi INDEX basa dialog box, tora paRow_num line.
2. Tora iyo basa basa zita rinoteverwa nebhokisi rakashama rakasvibira " ( "
3. Dzvanya pane sero D3 kuti uise iyo seferensi iri mubhokisi rebhokisi.
4. Tora an ampersand " & " mushure memusero reD3 kuti uwedzere kechipiri sekare.
5. Dzvanyira pane cell E3 kuti uise iyi yechipiri seferensi mubhokisi rebhokisi.
6. Tora comma "," mushure memutero wechiratidzo E3 kukwanise kupinda kweMATCH function's Lookup_value argument.
7. Siya iyo INDEX basa dialog box inoshandiswa kuitira danho rinotevera mumudzidzi.

Muchikamu chekupedzisira chekudzidzisa maLookup_values ​​achapinda mumasero D3 uye E3 emushandi webasa.

05 of 09

Kuwedzera Lookup_array nokuda kweMATCH Function

Ichi chinyorwa chinowedzera kuwedzera mhinduro yeLookup_array yeNATED yakasikwa basa.

Iko Lookup_array ndiyo mhando yemasero ayo MATCH basa ichatsvaga kuwana mhinduro ye Lookup_value yakawedzerwa muchigamba chekutanga chekudzidzisa.

Sezvo takaziva tsvaga miviri yekutsvaga muLookup_array argument tinofanira kuita zvakafanana kune Lookup_array . Iyo MATCH inoshanda chete inotsvaga imwe imwechete yezwi rimwe nerimwe rinotsanangurwa.

Kuti tiise zvinyorwa zvakawanda tinoshandisa zvakare ampersand " & " kuti tibvumirane nematombo pamwe chete.

Tutorial Steps

Aya matanho anofanira kupinda mushure mokunge komma inopinda mudanho rekutanga pane Row_num line mu INDEX basa dialog box .

1. Dzvanya paRow_num line mushure mokunge comma iise iyo insertion point pamagumo ekupinda ikozvino.
2. Simbisai masero D6 kusvika kuD11 mubasa rekushanda kuti muite mararamiro. Iyi ndiyo yekutanga iyo basa iri kutsvaga.
3. Tora an ampersand " & " mushure memasare ezviratidzo D6: D11 nokuti tinoda basa kuti titsvake mabhii maviri.
4. Simbisai masero E6 kusvika kuE11 mubasa rekushanda kuti muise mararamiro. Ichi ndicho chechipiri chebasa basa rekutsvaga.
5. Tora comma "," mushure memutsetse wekare E3 kukwanise kupinda kweMATCH function's Lookup_array argument.
6. Siya iyo INDEX basa dialog box inoshandiswa kuitira danho rinotevera mumudzidzi.

06 of 09

Kuwedzera Mhando yeMatch uye Kuzadza Basa reMATCH

Nharo yechitatu uye yokupedzisira yeMATCH basa ndiyo Match_type argument.

Iyi nhaurirano inoudza Excel nzira yekufananidza neLookup_value nemitemo muLookup_array. Zvisarudzo ndezvi: 1, 0, kana -1.

Iyi nharo inofanirwa. Kana ikasiyiwa basa racho rinoshandisa kukosha kwekutengesa kwe 1.

• kana Match_type = 1 kana kuti yakasiyiwa: MATCH inowana kukosha kukuru kudarika kana kuenzana neLookup_value. Dhora reLookup_array rinofanira kuenzaniswa mukukwira.
• kana iyo match_type = 0: MATCH inowana kukosha kokutanga kwakanyatsoenzana neLookup_value. Dhora reLookup_array rinogona kugadziriswa chero ripi zvaro.
• kana Match_type = -1: MATCH inowana kukosha kuduku kudarika kana kuenzana neLookup_value. Dhora reLookup_array rinofanira kuenzanisirwa richidzika.

Tutorial Steps

Aya matanho anofanira kupinda mushure mokunge komma inopinda mudanho rekutanga pane Row_num line mu INDEX basa dialog box .

1. Kutevedzera komma paRow_num line, tora zero " 0 " sezvo tichida basa rakasvibiswa kuti tidzorere maitiro akafanana nemashoko atinopinda mumasero D3 neE3.
2. Tora chivharo chakavhara " ) " kupedzisa MATCH basa.
3. Siya iyo INDEX basa dialog box inoshandiswa kuitira danho rinotevera mumudzidzi.

07 of 09

Dzokera kushanda ye INDEX

Iye zvino kuti basa reMATCH raitwa tichaenda kumutsara wechitatu wezaruriro rebhokisi rebhokisi uye ugoita nharo yekupedzisira ye INDEX basa.

Iyi nharo yechitatu uye yekupedzisira ndiyo Column_num argument iyo inotaurira Excel nhamba yekorodhe munharaunda D6 kusvika F11 apo ichawana ruzivo rwatinoda kudzoka nemabasa. Muchiitiko ichi, mutengesi weti titanium widgets .

Tutorial Steps

1. Dzvanya paColumn_numurongo mubhokisi rebhokisi.
2. Pinda nhamba nhatu " 3 " (hapana zvinyorwa) pamutsara uyu sezvo isu tichitsvaga dhesi mukamuri yechitatu yezera D6 kusvika F11.
3. Usambofa OK kana kuvhara INDEX basa dialog box. Inofanira kuramba yakasununguka kuitira danho rinotevera mumudzidzi-kuumba mazano akafanana .

08 of 09

Kugadzira Array Formula

Tisati tavhara bhokisi rebhokisi tinoda kushandura basa redu rakasvibirira muurongwa hwakafanana .

Nzira yakarongeka ndiyo inobvumira iyo kutsvaga mazwi akawanda mutafura yedata. Muchidzidzo ichi tiri kutarisa kuenzanisa mazwi maviri: Zvikwata kubva pakurongwa 1 uye titanium kubva munoronga 2.

Kugadzira urongwa hwekushandisa muExcel kunoitwa nekudhinda CTRL , SHIFT , uye ENTER maikoni pabhodhi panguva imwechete.

Mhedzisiro yekufambisa makiyi aya pamwe chete ndeyekukomberedza basa nekugadzirisa mabhesi: {} kuratidza kuti ikozvino maitiro akasiyana.

Tutorial Steps

1. Nebhokisi rebhokisi rebhokisi rave richizarurirwa kubva pane danho rekare remudzidzisi uyu, shandisa uye ubatisise CTRL uye SHIFT makii pabhobhoyo uye chengeta nekusunungura ENTER key.
2. Kana yakaitwa zvakanaka, bhokisi rebhokisi richavhara uye nharo ye # N / A ichaonekwa muchitokisi F3 - sero yatakapinda mubasa.
3. Iko # N / A kukanganisa kunooneka muchitokisi F3 nokuti masero D3 neE3 haana chinhu. D3 uye E3 ndiyo masero atakaudza basa kuti tiwane Lookup_values ​​muchikamu chechipiri chekudzidzisa. Kana imwe nguva dhechi ichiwedzerwa kune aya maviri masero, iko kukanganisa kuchagadziriswa nemashoko kubva kune database .

09 of 09

Kuwedzera Mitsva Yokutsvaga

Nhanho yekupedzisira mune zvidzidzo ndeyekuwedzera mazwi ekutsvaga kune pepa rekushanda kwedu.

Sezvakataurwa munhanho yapfuura, isu tiri kutarisa kuti tifanane nemashoko ekushandurwa kubva munoramende 1 neTitanium kubva munoronga 2.

Kana, uye chete kana, shanduro yedu inowana mutsara wemashoko maviri mumakoroni akakodzera mu database, ichadzorera kukosha kubva muhomwe yechitatu.

Tutorial Steps

1. Dzvanya pane cell D3.
2. Torai Widgets uye torai Enter Enter pane keyboard.
3. Dzvanya pane cell E3.
4. Tora Titanium uye pinda paImpikicha pa keyboard.
5. Zita romutengesi zita rokuti Widgets Inc. rinofanira kuonekwa muchitokisi F3 - nzvimbo yebasa sezvo iri chete mutengesi akarongwa anotengesa Titanium Widget.
6. Paunenge uchinyora pane cell F3 basa rakakwana
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   inowanikwa mubhairi yepamusoro pane pepa rekushanda .

Cherechedza: Mumuenzaniso wedu kwaiva nemugovera mumwechete weti titaniti zvakasarudzwa. Kana pakanga pane vapeji vanopfuura imwe chete, mutengesi yakanyorwa pakutanga mudhesikiti inodzorerwa nemabasa.