Matrix mashandiro muExcel

A matrix seti yemasero ari padivi peumwe neumwe uye ayo pamwechete anoumba rectangle. Hapana hunyanzvi hwakakosha hunodiwa kuita zviito zvakasiyana nematrix, zvakangofanana neaya anoshandiswa kana uchishanda neiyo classic renji zvakakwana.

Imwe neimwe matrix ine kero yayo, iyo yakanyorwa nenzira imwechete seyero. Chikamu chekutanga ndiyo sero yekutanga yemhando (iri mukona yepamusoro kuruboshwe), uye chikamu chechipiri ndicho chekupedzisira sero, iri mukona yezasi yekona. 

Array mafomula

Muhuwandu hwakawanda hwemabasa, kana uchishanda nearrays (uye matrices akadaro), mafomula emhando inoenderana anoshandiswa. Musiyano wavo wekutanga kubva kune wakajairwa ndewekuti iyo yekupedzisira inoburitsa kukosha kumwe chete. Kuti ushandise fomula yakarongwa, unofanirwa kuita zvinhu zvishoma:

1. Sarudza seti yemaseru uko ukoshi hucharatidzwa. 
2. Yakananga sumo yefomula. 
3. Kudzvanya kiyi kutevedzana Ctrl + Shift + Enter.

Mushure mekuita matanho akareruka aya, array formula inoratidzwa mumunda wekuisa. Inogona kusiyaniswa kubva kune yakajairika curly braces.

Kugadzirisa, kudzima array formula, iwe unofanirwa kusarudza iyo inodiwa renji uye ita zvaunoda. Kugadzirisa matrix, unofanirwa kushandisa musanganiswa wakafanana kuti uugadzire. Muchiitiko ichi, hazvibviri kugadzirisa chinhu chimwe chete chechikwata.

Chii chingaitwa nematrices

Kazhinji, kune nhamba huru yezviito zvinogona kushandiswa kune matrices. Ngatitarisei mumwe nomumwe wavo zvakadzama.

Shandura

Vanhu vazhinji havanzwisise zvinoreva izwi iri. Fungidzira kuti unoda kuchinjanisa mitsetse nemakoramu. Chiito ichi chinonzi transposition. 

Usati waita izvi, zvakakosha kuti usarudze nzvimbo yakaparadzana ine nhamba yakafanana yemitsara senhamba yemakoramu mumatrikisi epakutanga uye nhamba yakafanana yemakoroni. Kuti unzwisise zviri nani kuti izvi zvinoshanda sei, tarisa iyi skrini.

Kune nzira dzakawanda dzekuti transpose. 

Nzira yekutanga ndiyo inotevera. Kutanga iwe unofanirwa kusarudza matrix, uye wozoikopa. Tevere, mutsara wemaseru unosarudzwa panofanira kuiswa mutsara wakachinjirwa. Tevere, iyo Paste Special hwindo rinovhura.

Pane mabasa akawanda ipapo, asi tinofanira kuwana "Transpose" redhiyo bhatani. Mushure mekupedza chiito ichi, unofanirwa kuchisimbisa nekudzvanya bhatani re OK.

Pane imwe nzira yekufambisa matrix. Kutanga iwe unofanirwa kusarudza sero iri mukona yekumusoro kuruboshwe yemhando yakagoverwa kune transposed matrix. Zvadaro, bhokisi rebhokisi rine mabasa rinovhura, apo pane basa TRANSP. Ona muenzaniso uri pasi apa kuti uwane mamwe mashoko ekuita izvi. Iyo mhando inoenderana neyekutanga matrix inoshandiswa seyebasa parameter.

Mushure mekudzvanya OK, zvinotanga kuratidza kuti wakanganisa. Hapana chinotyisa mune izvi. Izvi zvinodaro nekuti iro basa ratakaisa haritsanangurwe seyakarongwa fomula. Naizvozvo, tinofanira kuita zvinotevera:

1. Sarudza seti yemasero akachengeterwa iyo transposed matrix.
2. Dzvanya F2 kiyi.
3. Dzvanya makiyi anopisa Ctrl + Shift + Enter.

Kubatsira kukuru kweiyo nzira iri mukugona kweiyo transposed matrix kugadzirisa nekukurumidza ruzivo rwurimo, nekukurumidza kana data rapinda mune yekutanga. Nokudaro, zvinokurudzirwa kushandisa nzira iyi.

Kuwedzera

Kushanda uku kunogoneka chete maererano neaya mitsara, nhamba yezvikamu zvayo zvakafanana. Zvichitaurwa zviri nyore, imwe neimwe yematrices ayo mushandisi achashanda nawo anofanirwa kunge aine zviyero zvakafanana. Uye isu tinopa skrini yekujekesa.

Mune matrix iyo inofanirwa kubuda, iwe unofanirwa kusarudza yekutanga sero uye isa fomula yakadaro.

=Chinhu chekutanga chekutanga matrix + Chekutanga chinhu chechipiri matrix 

Tevere, isu tinosimbisa yekupinda fomula neEnter kiyi uye toshandisa auto-yakazara (skweya iri pakona yekurudyi) kukopa ese kukosha uXNUMXbuXNUMXbinto itsva matrix.

Kuwedzera

Ngatitii tine tafura yakadaro inofanira kuwanzwa ne12.

Muverengi akangwara anogona kunzwisisa zviri nyore kuti nzira yacho yakafanana neyakapfuura. Kureva kuti, rimwe nerimwe masero e matrix 1 anofanira kuwanzwa negumi nembiri kuitira kuti mumatrix ekupedzisira sero rimwe nerimwe rive neukoshi hwakawedzerwa neiyi coefficient.

Muchiitiko ichi, zvakakosha kutsanangura zvachose cell references.

Nekuda kweizvozvo, fomula yakadaro inozobuda.

=A1*$E$3

Uyezve, maitiro acho akafanana neapfuura. Iwe unofanirwa kutambanudza kukosha uku kune nhamba inodiwa yemasero. 

Ngatifungei kuti zvakakosha kuwanza matrices pakati pavo. Asi pane chimiro chimwe chete chekuti izvi zvinogoneka. Izvo zvinodikanwa kuti nhamba yemakoramu nemitsara muzvikamu zviviri zvionekwe zvakafanana. Ndiko kuti, ingani makoramu, mitsetse yakawanda.

Kuita kuti zvive nyore, takasarudza mutsara une matrix inoguma. Iwe unofanirwa kufambisa chitubu kuchitokisi chiri kumusoro kwekona yekuruboshwe uye woisa inotevera fomula =MUMNOH(A9:C13;E9:H11). Usakanganwa kudzvanya Ctrl + Shift + Enter.

inverse matrix

Kana rudzi rwedu rune chimiro cheskweya (kureva, nhamba yemasero akatwasuka uye akatwasuka akafanana), zvino zvinozokwanisika kuwana inverse matrix, kana zvichidikanwa. Kukosha kwayo kuchafanana nepakutanga. Nokuda kweizvi, basa rinoshandiswa MOBR.

Kutanga, iwe unofanirwa kusarudza yekutanga sero yematrix, iyo inverse ichaiswa. Heino formula =INV(A1:A4). Nharo inotsanangura huwandu hwatinoda kugadzira inverse matrix. Zvinongosara chete kudzvanya Ctrl + Shift + Enter, uye wapedza.

Kutsvaga Determinant yeMatrix

Chiyereso inhamba iri square matrix. Kutsvaga chirevo che matrix, pane basa - MOPRED.

Kutanga, cursor inoiswa mune chero sero. Tevere, tinopinda =MOPRED(A1:D4)

Mienzaniso mishoma

Kuti tijekese, ngatitarisei mimwe mienzaniso yemashandiro anogona kuitwa nematrices muExcel.

Kuwanza uye kupatsanura

Iyo 1 nzira

Ngatitii tine matrix A iyo masero matatu akareba uye maseru mana akafara. Panewo nhamba k, iyo yakanyorwa mune imwe sero. Mushure mekuita mashandiro ekuwanza matrix nenhamba, huwandu hwehunhu huchaonekwa, huine zviyero zvakafanana, asi chikamu chimwe nechimwe chayo chinopetwa ne k.

Rudzi rweB3: E5 ndiyo yekutanga matrix inozowedzerwa nenhamba k, iyo zvakare inowanikwa muchitokisi H4. Iyo matrix inoguma ichave iri mumhando yeK3: N5. Matrix ekutanga achanzi A, uye anozoguma - B. Iyo yekupedzisira inoumbwa nekuwanza matrix A nenhamba k. 

Tevere, pinda =B3*$H$4 Sero K3, uko B3 iri chinhu A11 yematrix A.

Usakanganwa kuti sero H4, apo nhamba k inoratidzwa, inofanira kuiswa mufomura uchishandisa chirevo chakakwana. Zvikasadaro, kukosha kunoshanduka kana rondedzero ichikopwa, uye iyo inokonzeresa matrix ichakundikana.

Tevere, iyo autofill marker (iyo yakafanana sikweya mukona yezasi yekurudyi) inoshandiswa kukopa kukosha kunowanikwa muchitokisi K3 kune mamwe maseru ari muchikamu ichi.

Saka isu takakwanisa kuwanza matrix A neimwe nhamba uye kuwana yakabuda matrix B.

Kupatsanura kunoitwa nenzira yakafanana. Iwe unongoda kuisa iyo division formula. Muchiitiko chedu, izvi =B3/$H$4.

Iyo 2 nzira

Saka, musiyano mukuru weiyi nzira ndewekuti mhedzisiro ndeye data rakawanda, saka iwe unofanirwa kushandisa array formula kuzadza seti yese yemasero.

Izvo zvinodiwa kuti usarudze mhedzisiro inobuda, isa chiratidzo chakaenzana (=), sarudza seti yemasero ane zviyero zvinoenderana neyekutanga matrix, tinya pane nyeredzi. Zvadaro, sarudza sero rine nhamba k. Zvakanaka, kuti usimbise zviito zvako, unofanirwa kudzvanya iri pamusoro apa kiyi musanganiswa. Hooray, huwandu hwese huri kuzadza.

Kupatsanura kunoitwa nenzira yakafanana, chete chiratidzo * chinofanira kutsiviwa ne /.

Kuwedzera nekubvisa

Ngatitsanangurirei mimwe mienzaniso inoshanda yekushandisa nzira dzekuwedzera nekubvisa mukuita.

Iyo 1 nzira

Usakanganwa kuti zvinokwanisika kuwedzera iwo chete matrices ane saizi yakafanana. Muchikamu chinoguma, masero ose anozadzwa nehuwandu hunova huwandu hwemasero akafanana mumatrices ekutanga.

Tomboti tine matrices maviri ari 3×4 muhukuru. Kuti uverenge huwandu, iwe unofanirwa kuisa inotevera fomula musero N3:

=B3+H3

Pano, chinhu chega chega ndiyo sero rekutanga rematrices atiri kuwedzera. Izvo zvakakosha kuti zvinongedzo zvine hukama, nekuti kana ukashandisa absolute links, iyo data chaiyo haizoonekwe.

Kupfuurirazve, zvakafanana nekuwanza, tichishandisa iyo autocomplete marker, isu tinoparadzira fomula kune ese maseru ematrix inoguma.

Kubvisa kunoitwa nenzira yakafanana, kunze kwekuti chiratidzo chokubvisa (-) chinoshandiswa pane chikwangwani chekuwedzera.

Iyo 2 nzira

Zvakafanana nenzira yekuwedzera nekubvisa matrices maviri, nzira iyi inosanganisira kushandiswa kwefomura yakarongwa. Naizvozvo, semhedzisiro yayo, seti yemakoshero uXNUMXbuXNUMX ichapihwa nekukasira. Naizvozvo, haugone kugadzirisa kana kudzima chero zvinhu.

Kutanga iwe unofanirwa kusarudza huwandu hwakapatsanurwa kune inoguma matrix, wobva wadzvanya pa "="". Ipapo iwe unofanirwa kutsanangura yekutanga parameter yeformula mumhando yerudzi rwematrix A, tinya pane + chiratidzo uye nyora yechipiri parameter muchimiro chemhando inoenderana nematrix B. Isu tinosimbisa zviito zvedu nekutsikirira musanganiswa. Ctrl + Shift + Enter. Zvese, zvino iyo yese mhedzisiro matrix yakazadzwa nehunhu.

Matrix transposition muenzaniso

Ngatitii isu tinoda kugadzira matrix AT kubva kune matrix A, yatinayo pakutanga nekutamisa. Iyo yekupedzisira ine, nechekare netsika, zviyero zve 3 × 4. Nokuda kweizvi tichashandisa basa =TRANSP().

Isu tinosarudza huwandu hwemasero ematrix AT.

Kuti uite izvi, enda kune "Formulas" tab, uko sarudza iyo "Insert basa" sarudzo, ipapo tsvaga "References uye arrays" chikamu uye tsvaga basa. TRANSP. Mushure meizvozvo, zviito zvako zvinosimbiswa nebhatani re OK.

Zvadaro, enda ku "Function Arguments" hwindo, apo huwandu hweB3: E5 hunopindirwa, hunodzokorora matrix A. Zvadaro, unofanira kudzvanya Shift + Ctrl, uye wobva wadzvanya "OK".

Zvakakosha. Iwe haufanirwe kuve neusimbe kudzvanya makiyi aya anopisa, nekuti kana zvisina kudaro chete kukosha kwesero rekutanga remhando yeAT matrix ichaverengerwa.

Nekuda kweizvozvo, tinowana tafura yakachinjika yakadai inoshandura hunhu hwayo mushure meiyo yekutanga.

Inverse Matrix Search

Ngatitii tine matrix A, ane saizi ye3 × 3 masero. Isu tinoziva kuti kuwana inverse matrix, isu tinofanirwa kushandisa basa racho =MOBR().

Iye zvino tinotsanangura maitiro ekuita izvi mukuita. Kutanga iwe unofanirwa kusarudza iyo huwandu G3: I5 (iyo inverse matrix ichave iripo). Iwe unofanirwa kuwana chinhu che "Insert Function" pane "Formulas" tab.

Iyo "Insert function" dialog ichavhura, kwaunoda kusarudza "Math" chikamu. Uye pachave nebasa mune rondedzero MOBR. Kana tasarudza, tinoda kudzvanya kiyi OK. Zvadaro, bhokisi re "Function Arguments" rinoonekwa, umo tinonyora mutsara B3: D5, iyo inowirirana nematrix A. Zvimwe zviito zvakafanana nekushandura. Iwe unofanirwa kudzvanya kiyi musanganiswa Shift + Ctrl uye tinya OK.

mhedziso

Isu takaongorora mimwe mienzaniso yekuti ungashanda sei nematrices muExcel, uye zvakare akatsanangura dzidziso. Zvinoitika kuti izvi hazvisi kutyisa sezvazvingaite senge pakuona kwekutanga, handizvo here? Zvinongonzwika zvisinganzwisisike, asi kutaura zvazviri, mushandisi wepakati anofanirwa kubata nematrices mazuva ese. Dzinogona kushandiswa kune chero tafura iyo pane zvishoma zvishoma zve data. Uye zvino iwe unoziva kuti ungarerutsa sei hupenyu hwako mukushanda navo.