Imisebenzi ye-matrix ku-Excel

I-matrix iqoqo lamaseli abekwe eduze kwelinye futhi ndawonye akha unxande. Awekho amakhono akhethekile adingekayo ukwenza izenzo ezihlukahlukene nge-matrix, afanayo nalawo asetshenziswa lapho usebenza nebanga lakudala anele.

I-matrix ngayinye inekheli layo, elibhalwe ngendlela efanayo nobubanzi. Ingxenye yokuqala iseli lokuqala lobubanzi (elisekhoneni elingenhla kwesokunxele), futhi ingxenye yesibili iseli lokugcina, esekhoneni elingezansi kwesokudla. 

Amafomula afanayo

Emisebenzini eminingi eminingi, lapho usebenza ngama-arrays (nomatikuletsheni anjalo), amafomula ohlobo oluhambisanayo asetshenziswa. Umehluko wabo oyisisekelo kusukela kokujwayelekile ukuthi okukhiphayo kokugcina inani elilodwa kuphela. Ukuze usebenzise ifomula yamalungu afanayo, udinga ukwenza izinto ezimbalwa:

1. Khetha isethi yamaseli lapho amanani azovezwa khona. 
2. Ukwethulwa okuqondile kwefomula. 
3. Ukucindezela ukulandelana kokhiye Ctrl + Shift + Enter.

Ngemva kokwenza lezi zinyathelo ezilula, ifomula yamalungu afanayo iboniswa endaweni yokufaka. Ingahlukaniswa nama-curly braces ajwayelekile.

Ukuze uhlele, susa amafomula afanayo, udinga ukukhetha ububanzi obudingekayo futhi wenze okudingayo. Ukuze uhlele i-matrix, udinga ukusebenzisa inhlanganisela efanayo ukuze uyidale. Kulesi simo, akunakwenzeka ukuhlela ingxenye eyodwa yamalungu afanayo.

Yini engenziwa ngomatikuletsheni

Ngokuvamile, kunenombolo enkulu yezenzo ezingasetshenziswa kumatrices. Ake sibheke ngayinye yazo ngokuningiliziwe.

Shintsha

Abantu abaningi abayiqondi incazelo yaleli gama. Cabanga ukuthi udinga ukushintshanisa imigqa namakholomu. Lesi senzo sibizwa ngokuthi ukuguqulwa. 

Ngaphambi kokwenza lokhu, kubalulekile ukukhetha indawo ehlukile enenombolo efanayo yemigqa njengenani lamakholomu ku-matrix yokuqala kanye nenani elifanayo lamakholomu. Ukuze uthole ukuqonda okungcono kokuthi lokhu kusebenza kanjani, bheka lesi sithombe-skrini.

Kunezindlela ezimbalwa zendlela yokudlulisa. 

Indlela yokuqala yile elandelayo. Okokuqala udinga ukukhetha i-matrix, bese uyikopisha. Okulandelayo, ububanzi bamaseli bukhethwa lapho ububanzi obuguquliwe kufanele bufakwe khona. Okulandelayo, iwindi Namathisela Okukhethekile liyavuleka.

Kukhona imisebenzi eminingi lapho, kodwa sidinga ukuthola inkinobho yomsakazo "Transpose". Ngemva kokuqeda lesi senzo, udinga ukusiqinisekisa ngokucindezela inkinobho ethi KULUNGILE.

Kukhona enye indlela yokudlulisa i-matrix. Okokuqala udinga ukukhetha iseli elitholakala ekhoneni eliphezulu kwesokunxele lobubanzi obubelwe imatrix eguquliwe. Okulandelayo, ibhokisi lengxoxo elinemisebenzi liyavuleka, lapho kunomsebenzi I-TRANSP. Bheka isibonelo esingezansi ukuze uthole imininingwane eyengeziwe yokuthi ungakwenza kanjani lokhu. Ububanzi obuhambisana ne-matrix yoqobo busetshenziswa njengepharamitha yomsebenzi.

Ngemva kokuchofoza KULUNGILE, kuzoqale kubonise ukuthi wenze iphutha. Akukho okubi kulokhu. Lokhu kungenxa yokuthi umsebenzi esiwufakile awuchazwa njengefomula yamalungu afanayo. Ngakho-ke, sidinga ukwenza lokhu okulandelayo:

1. Khetha isethi yamaseli abekelwe imatrix eguquliwe.
2. Cindezela inkinobho F2.
3. Cindezela okhiye abashisayo Ctrl + Shift + Enter.

Inzuzo eyinhloko yendlela isemandleni e-matrix eguquliwe ukulungisa ngokushesha ulwazi oluqukethwe kuyo, ngokushesha nje lapho idatha ifakwe kweyokuqala. Ngakho-ke, kunconywa ukusebenzisa le ndlela.

Ukwengezwa

Lokhu kusebenza kungenzeka kuphela ngokuphathelene nalezo zigaba, inani lezinto ezifanayo. Kalula nje, umatikuletsheni ngamunye umsebenzisi azosebenza ngawo kufanele ube nobukhulu obufanayo. Futhi sinikeza isithombe-skrini ukuze sicace.

Ku-matrix okufanele ivele, udinga ukukhetha iseli lokuqala bese ufaka ifomula enjalo.

=Into yokuqala ye-matrix yokuqala + Into yokuqala ye-matrix yesibili 

Okulandelayo, siqinisekisa okufakiwe kwefomula ngokhiye u-Enter bese sisebenzisa i-auto-complete (isikwele esisekhoneni elingezansi kwesokudla) ukukopisha wonke amanani uXNUMXbuXNUMXbin ku-matrix entsha.

Ukuphindaphinda

Ake sithi sinetafula elinjalo okufanele liphindwe ngo-12.

Umfundi ohlakaniphile angaqonda kalula ukuthi indlela ifana kakhulu neyangaphambili. Okusho ukuthi, iseli ngalinye le-matrix 1 kufanele liphindwe ngo-12 ukuze ku-matrix yokugcina iseli ngalinye libe nenani eliphindwe ngale coefficient.

Kulesi simo, kubalulekile ukucacisa amareferensi amaseli aphelele.

Ngenxa yalokho, ifomula enjalo izovela.

=A1*$E$3

Ngaphezu kwalokho, inqubo iyefana neyangaphambili. Udinga ukwelula leli nani linani elidingekayo lamaseli. 

Ake sicabange ukuthi kuyadingeka ukuphindaphinda ama-matrics phakathi kwabo. Kodwa kunesimo esisodwa kuphela lapho lokhu kungenzeka khona. Kudingeka ukuthi inani lamakholomu nemigqa kububanzi obubili kufakwe isibuko ngendlela efanayo. Okusho ukuthi, zingaki amakholomu, imigqa eminingi kangaka.

Ukwenza kube lula, sikhethe ububanzi obunomphumela we-matrix. Udinga ukuhambisa ikhesa kuseli ekhoneni eliphezulu kwesokunxele bese ufaka ifomula elandelayo =MUMNOH(A9:C13;E9:H11). Ungakhohlwa ukucindezela u-Ctrl + Shift + Enter.

i-matrix ephambene

Uma ububanzi bethu bunomumo oyisikwele (okungukuthi, inani lamaseli avundlile naqondile ayafana), lapho-ke kuzokwaziwa ukuthola i-matrix ephambene, uma kunesidingo. Inani layo lizofana nelokuqala. Kulokhu, umsebenzi usetshenziswa I-MOBR.

Ukuze uqale, kufanele ukhethe iseli yokuqala ye-matrix, lapho okuphambene kuzofakwa khona. Nansi ifomula =INV(A1:A4). I-agumenti icacisa ububanzi okudingeka sidale i-matrix ephambene. Kusele kuphela ukucindezela okuthi Ctrl + Shift + Enter, futhi usuqedile.

Ukuthola I-Determinant ye-Matrix

I-determinant inombolo eyi-matrix yesikwele. Ukuze useshe isihlukanisi se-matrix, kunomsebenzi − I-MORED.

Okokuqala nje, ikhesa ifakwa kunoma iyiphi iseli. Okulandelayo, singena =MOPRED(A1:D4)

Izibonelo ezimbalwa

Ukuze kucace, ake sibheke ezinye izibonelo zemisebenzi engenziwa ngamatrices ku-Excel.

Ukuphindaphinda nokuhlukanisa

Indlela engu-1

Ake sithi sine-matrix A engamaseli amathathu ukuphakama namaseli amane ububanzi. Kukhona nenombolo k, ebhalwe kwenye iseli. Ngemva kokwenza umsebenzi wokuphindaphinda i-matrix ngenombolo, kuzovela uhla lwamanani, anobukhulu obufanayo, kodwa ingxenye yayo ngayinye iphindaphindwa ngo-k.

Ububanzi B3:E5 yi-matrix yoqobo ezophindaphindwa ngenombolo k, yona etholakala kuseli H4. I-matrix ewumphumela izoba kububanzi K3:N5. I-matrix yokuqala izobizwa ngo-A, bese kuthi ephumayo - B. Eyokugcina yakhiwe ngokuphindaphinda i-matrix A ngenombolo k. 

Okulandelayo, faka =B3*$H$4 kuseli K3, lapho i-B3 iyisici A11 se-matrix A.

Ungakhohlwa ukuthi iseli H4, lapho inombolo k ikhonjisiwe, kufanele ifakwe kwifomula kusetshenziswa ireferensi ephelele. Uma kungenjalo, inani lizoshintsha lapho amalungu afanayo ekopishwa, futhi i-matrix ewumphumela izohluleka.

Okulandelayo, umaka wokugcwalisa okuzenzakalelayo (isikwele esifanayo ekhoneni elingezansi kwesokudla) sisetshenziswa ukukopisha inani elitholwe kuseli K3 kuwo wonke amanye amaseli kulobu bubanzi.

Ngakho-ke sikwazile ukuphindaphinda i-matrix A ngenombolo ethile futhi sithole i-matrix yokuphumayo B.

Ukuhlukaniswa kwenziwa ngendlela efanayo. Udinga nje ukufaka ifomula yokuhlukanisa. Esimweni sethu, lokhu =B3/$H$4.

Indlela engu-2

Ngakho-ke, umehluko omkhulu wale ndlela ukuthi umphumela uwuxhaxha lwedatha, ngakho-ke udinga ukusebenzisa ifomula yohlelo ukuze ugcwalise isethi yonke yamaseli.

Kuyadingeka ukuthi ukhethe ibanga eliwumphumela, ufake uphawu olulinganayo (=), khetha isethi yamaseli anobukhulu obuhambisana ne-matrix yokuqala, chofoza inkanyezi. Okulandelayo, khetha iseli enenombolo k. Nokho, ukuze uqinisekise izenzo zakho, kufanele ucindezele inhlanganisela yokhiye ongenhla. Hooray, lonke uhla luyagcwala.

Ukuhlukaniswa kwenziwa ngendlela efanayo, uphawu * kuphela okufanele luthathelwe indawo ngu/.

Ukwengeza nokususa

Ake sichaze izibonelo ezithile ezisebenzayo zokusebenzisa izindlela zokuhlanganisa nokukhipha ngokusebenza.

Indlela engu-1

Ungakhohlwa ukuthi kungenzeka ukwengeza kuphela lawo matrices amasayizi awo afanayo. Kububanzi obuwumphumela, wonke amaseli agcwaliswa ngenani eliyisamba samaseli afanayo kumatrices oqobo.

Ake sithi sinomatikuletsheni ababili abanosayizi ongu-3×4. Ukuze ubale isamba, kufanele ufake ifomula elandelayo kuseli N3:

=B3+H3

Lapha, i-elementi ngayinye iyiseli lokuqala likamatikuletsheni esizowengeza. Kubalulekile ukuthi izixhumanisi zihlobene, ngoba uma usebenzisa izixhumanisi eziphelele, idatha elungile ngeke iboniswe.

Ngokuqhubekayo, ngokufanayo nasekuphindaphindeni, sisebenzisa umaka wokuqedela ngokuzenzakalela, sisakaza ifomula kuwo wonke amaseli we-matrix ewumphumela.

Ukukhipha kwenziwa ngendlela efanayo, ngaphandle kokuthi kusetshenziswe uphawu lokususa (-) kunophawu lokwengeza.

Indlela engu-2

Ngokufanayo nendlela yokwengeza nokukhipha omatikuletsheni ababili, le ndlela ihilela ukusetshenziswa kwefomula yohlu. Ngakho-ke, ngenxa yalokho, isethi yamanani uXNUMXbuXNUMX izokhishwa ngokushesha. Ngakho-ke, awukwazi ukuhlela noma ukususa noma yiziphi izici.

Okokuqala udinga ukukhetha ububanzi obuhlukaniselwe umphumela we-matrix, bese uchofoza ku-"="". Bese udinga ukucacisa ipharamitha yokuqala yefomula ngendlela yohlu lwe-matrix A, chofoza uphawu + bese ubhala ipharamitha yesibili ngendlela yobubanzi obuhambisana ne-matrix B. Siqinisekisa izenzo zethu ngokucindezela inhlanganisela Ctrl + Shift + Enter. Konke, manje yonke i-matrix ewumphumela igcwele amanani.

Isibonelo sokuguqulwa kwe-matrix

Ake sithi sidinga ukwakha i-matrix AT kusuka ku-matrix A, esinayo ekuqaleni ngokuyidlulisela. Lokhu kwakamuva sekuvele, ngokwesiko, ubukhulu be-3×4. Kulokhu sizosebenzisa umsebenzi =TRANSP().

Sikhetha ububanzi bamaseli we-matrix AT.

Ukuze wenze lokhu, yiya kuthebhu ethi “Amafomula”, lapho ukhetha khona inketho ethi “Faka umsebenzi,” thola lapho isigaba esithi “Izikhombo kanye nezinhlaka” bese uthola umsebenzi. I-TRANSP. Ngemva kwalokho, izenzo zakho ziqinisekiswa ngenkinobho ethi KULUNGILE.

Okulandelayo, hamba efasiteleni elithi "Izimpikiswano Zomsebenzi", lapho kufakwa khona ububanzi B3: E5, obuphinda i-matrix A. Okulandelayo, udinga ukucindezela u-Shift + Ctrl, bese uchofoza "OK".

Kubalulekile. Akufanele ube nobuvila ukucindezela lezi zihluthulelo ezishisayo, ngoba ngaphandle kwalokho kuzobalwa kuphela inani leseli lokuqala lobubanzi be-matrix ye-AT.

Ngenxa yalokho, sithola ithebula eliguquliwe elishintsha amanani alo ngemva kwelangempela.

I-Inverse Matrix Search

Ake sithi sine-matrix A, enosayizi wamaseli angu-3×3. Siyazi ukuthi ukuze sithole i-matrix ephambene, sidinga ukusebenzisa umsebenzi =MOBR().

Manje ake sichaze ukuthi singakwenza kanjani lokhu ngokusebenza. Okokuqala udinga ukukhetha uhla G3:I5 (i-matrix ephambene izotholakala lapho). Udinga ukuthola into ethi "Faka Umsebenzi" kuthebhu "Amafomula".

Ibhokisi elithi "Faka umsebenzi" lizovuleka, lapho udinga ukukhetha isigaba "sezibalo". Futhi kuzoba nomsebenzi ohlwini I-MOBR. Ngemva kokuyikhetha, sidinga ukucindezela ukhiye OK. Okulandelayo, ibhokisi lengxoxo elithi "Izimpikiswano Zomsebenzi" livela, lapho sibhala khona ububanzi B3: D5, obuhambisana ne-matrix A. Izenzo ezengeziwe zifana nokuguqulwa. Udinga ukucindezela inhlanganisela yokhiye Shift + Ctrl bese uchofoza OK.

iziphetho

Sihlaziye ezinye izibonelo zokuthi ungasebenza kanjani ngomatikuletsheni ku-Excel, saphinde sachaza ithiyori. Kuvela ukuthi lokhu akwethusi njengoba kungase kubonakale ekuqaleni, akunjalo? Kuzwakala kungaqondakali, kodwa empeleni, umsebenzisi ojwayelekile kufanele abhekane namatrices nsuku zonke. Angasetshenziselwa cishe noma yiliphi ithebula lapho kunenani elincane ledatha. Futhi manje uyazi ukuthi ungayenza kanjani impilo yakho ibe lula ekusebenzeni nabo.