Cara membuat matriks dalam Excel. Menyelesaikan sistem persamaan dalam Excel menggunakan kaedah Cramer dan matriks songsang

Mari kita mengira penentu matriks menggunakan fungsi MOPRED() atau Bahasa Inggeris. MDETERM, pengembangan baris/lajur (untuk 3 x 3) dan mengikut takrifan (sehingga tertib ke-6).

Penentu matriks (det) hanya boleh dikira untuk matriks persegi, i.e. di mana bilangan baris adalah sama dengan bilangan lajur.

Untuk mengira penentu dalam MS EXCEL ada fungsi khas MOPRED() . Argumen fungsi mesti mengandungi rujukan kepada julat sel (tatasusunan) yang mengandungi elemen matriks (lihat fail contoh).

Tatasusunan boleh ditentukan bukan sahaja sebagai julat sel, contohnya A7:B8 , tetapi juga seperti , contohnya =MOPRED((5;4:3;2)) . Menulis menggunakan tatasusunan pemalar membolehkan anda tidak menentukan elemen dalam sel berasingan, tetapi meletakkannya dalam sel bersama-sama dengan fungsi. Dalam kes ini, tatasusunan ditunjukkan baris demi baris: sebagai contoh, pertama baris pertama ialah 5;4, kemudian ia ditulis dipisahkan oleh titik bertindih baris seterusnya 3;2. Elemen dipisahkan oleh koma bertitik.

Untuk matriks tertib 2, penentu boleh dikira tanpa menggunakan fungsi MOPRED(). Sebagai contoh, untuk matriks di atas, ungkapan =A7*B8-B7*A8 akan mengembalikan hasil yang sama.

Untuk matriks tertib 3, contohnya diletakkan dalam julat A16:C18 , ungkapan menjadi lebih rumit =A16*(B17*C18-C17*B18)-B16*(A17*C18-C17*A18)+C16*(A17*B18-B17*A18)(perluasan talian).

Dalam fail contoh untuk matriks 3 x 3, penentu juga dikira melalui pengembangan lajur dan peraturan Sarrus.

Sifat penentu

Sekarang tentang beberapa sifat penentu (lihat fail contoh):

Penentu adalah sama dengan penentu matriks asal
Jika dalam matriks semua elemen sekurang-kurangnya satu daripada baris (atau lajur) adalah sifar, penentu matriks sedemikian sama dengan sifar
Jika anda menukar mana-mana dua baris (lajur), penentu matriks yang terhasil akan bertentangan dengan yang asal (iaitu, tanda akan berubah)
Jika semua elemen salah satu baris (lajur) didarab dengan nombor k yang sama, maka penentu matriks yang terhasil ialah sama dengan penentu matriks asal didarab dengan k
Jika matriks mengandungi baris (lajur) yang gabungan linear baris lain (lajur), kemudian penentu =0
det(A)=1/det(A -1), dengan A -1 - matriks A (A ialah matriks bukan tunggal segi empat sama).

Pengiraan penentu matriks mengikut takrifan (sehingga urutan 6 termasuk)

NASIHAT: Bahagian ini hanya boleh dibaca oleh pengguna MS EXCEL lanjutan. Di samping itu, bahan tersebut adalah untuk kepentingan akademik sahaja, kerana terdapat fungsi MOPRED() .

Seperti yang ditunjukkan di atas, untuk mengira matriks tertib 2 dan 3, sudah cukup formula mudah dan peraturan. Untuk mengira penentu matriks lebih banyak perintah tinggi(tanpa menggunakan fungsi MOPRED()) anda perlu mengingati definisi:

Penentu matriks segi empat sama susunan n x n ialah jumlah yang mengandungi n! istilah (=FAKTA(n) ). Setiap sebutan ialah hasil darab n unsur matriks, dan setiap hasil darab mengandungi unsur dari setiap baris dan dari setiap lajur matriks A. Pekali (-1) muncul sebelum sebutan ke-k jika unsur-unsur matriks A dalam produk disusun mengikut nombor baris, dan bilangan penyongsangan dalam pilihatur ke-k bagi set nombor lajur adalah ganjil.

di mana (α 1,α 2,...,α n) ialah pilih atur nombor dari 1 kepada n, N(α 1,α 2,...,α n) ialah nombor, penjumlahan adalah atas semua yang mungkin pilih atur perintah n.

Mari kita cuba memahami definisi sukar ini menggunakan contoh matriks 3x3.

Untuk matriks 3 x 3, mengikut definisi, bilangan sebutan ialah 3!=6, dan setiap sebutan terdiri daripada hasil darab 3 unsur matriks. Di bawah adalah kesemua 6 sebutan yang diperlukan untuk mengira penentu bagi matriks 3x3:

a21*a12*a33
a21*a32*a13
a11*a32*a23
a11*a22*a33
a31*a22*a13
a31*a12*a23

a21, a12, dsb. ialah unsur-unsur matriks. Sekarang mari kita terangkan bagaimana indeks unsur-unsur terbentuk, i.e. mengapa, sebagai contoh, adakah terdapat istilah a11*a22*a33, tetapi bukan a11*a22*a13.

Mari kita lihat formula di atas (lihat definisi). Mari kita anggap bahawa indeks kedua setiap elemen matriks (dari 1 hingga n) sepadan dengan nombor lajur matriks (walaupun ini mungkin nombor baris (ini tidak penting kerana penentu matriks dan ia adalah sama) Oleh itu, indeks kedua unsur pertama dalam produk sentiasa sama dengan 1, kedua - 2, ketiga - 3. Kemudian indeks pertama unsur sepadan dengan nombor baris dan, mengikut definisi, mestilah ditentukan daripada pilih atur nombor dari 1 hingga 3, iaitu daripada pilih atur set (1, 2, 3).

Kini jelas mengapa a11*a22*a13 bukan antara istilah, kerana mengikut definisi ( setiap produk mengandungi elemen daripada setiap baris dan setiap lajur matriks A), dan istilah kami tidak mengandungi unsur dari baris 3.

Catatan: Pilih atur n nombor dalam set (tanpa ulangan) ialah sebarang susunan set yang diberikan, berbeza antara satu sama lain hanya dalam susunan unsur mereka. Sebagai contoh, diberikan satu set 3 nombor: 1, 2, 3. Daripada nombor ini anda boleh membuat 6 pilih atur yang berbeza: (1, 2, 3), (1, 3, 2), (2, 3, 1), (2, 1, 3), (3, 1, 2), (3, 2, 1). Lihat artikel

Bilangan pilih atur bagi satu set 3 nombor =3!=6 (yang, sudah tentu, adalah sama dengan bilangan sebutan dalam ungkapan untuk mengira penentu, kerana setiap sebutan sepadan dengan pilih aturnya sendiri). Untuk matriks 3x3, semua pilih atur diberikan dalam nota di atas. Anda boleh memastikan bahawa dalam setiap istilah indeks pertama unsur adalah sama dengan nombor yang sepadan dalam pilih atur. Sebagai contoh, untuk istilah a21*a12*a33 pilih atur (2, 1, 3) telah digunakan.

NASIHAT: Untuk matriks pesanan ke-4 terdapat 4! pilih atur, i.e. 26, yang sepadan dengan 26 sebutan, setiap satunya adalah hasil darab 4 unsur matriks yang berbeza. Semua 26 pilih atur boleh didapati dalam artikel.

Memandangkan kita telah menangani terma, mari tentukan faktor di hadapan setiap istilah (ia boleh menjadi +1 atau -1). Pengganda ditentukan melalui pariti bilangan penyongsangan permutasi yang sepadan.

Catatan: Anda boleh membaca tentang penyongsangan pilih kasih (dan kesamarataan bilangan penyongsangan), contohnya, dalam artikel

Sebagai contoh, sebutan pertama sepadan dengan pilih atur (2, 1, 3), yang mempunyai 1 penyongsangan (nombor ganjil) dan, dengan itu, -1 kepada kuasa 1 adalah sama dengan -1. Sebutan kedua sepadan dengan pilih atur (2, 3, 1), yang mempunyai 2 penyongsangan (nombor genap) dan, dengan itu, -1 kepada kuasa 2 sama dengan 1, dsb.

Menambah semua istilah: (-1)*(a21*a12*a33)+(+1)*(a21*a32*a13)+(-1)*(a11*a32*a23)+(+1)*(a11 *a22*a33)+(-1)*(a31*a22*a13)+(+1)*(a31*a12*a23) kita mendapat nilai penentu.

DALAM contoh fail pada helaian 4+, Dan Dengan menukar susunan matriks menggunakan , anda boleh mengira penentu matriks sehingga termasuk urutan ke-6.

Perlu diambil kira bahawa apabila mengira matriks tertib ke-6, ungkapan itu sudah menggunakan 720 sebutan (6!). Untuk pesanan ke-7, kita perlu membuat jadual untuk pilih atur 5040 dan, dengan itu, hitung 5040 sebutan! Itu. Anda tidak boleh melakukannya tanpa menggunakan MOPRED() (baik, atau anda boleh mengira penentu secara manual menggunakan kaedah Gaussian).

Kira nilai punca sistem persamaan yang terbentuk menggunakan dua kaedah: matriks songsang dan kaedah Cramer.

Mari masukkan nilai ini ke dalam sel A2:C4 - matriks A dan sel D2:D4 - matriks B.

Menyelesaikan sistem persamaan menggunakan kaedah matriks songsang

Mari kita cari songsang matriks bagi matriks A. Untuk melakukan ini, dalam sel A9 kita masukkan formula =MOBR(A2:C4). Selepas ini, pilih julat A9:C11, bermula dari sel yang mengandungi formula. Tekan kekunci F2 dan kemudian tekan kekunci CTRL+SHIFT+ENTER. Formula akan dimasukkan sebagai formula tatasusunan. =MOBR(A2:C4).
Mari cari hasil darab matriks A-1 * b. Dalam sel F9:F11, masukkan formula: =MULTIPLE(A9:C11,D2:D4) sebagai formula tatasusunan. Kita mendapatkan dalam sel F9:F11 punca persamaan:

Menyelesaikan sistem persamaan menggunakan kaedah Cramer

Mari kita selesaikan sistem menggunakan kaedah Cramer, untuk ini kita dapati penentu matriks.
Mari kita cari penentu bagi matriks yang diperoleh dengan menggantikan satu lajur dengan lajur b.

Dalam sel B16, masukkan formula =MOPRED(D15:F17),

Dalam sel B17, masukkan formula =MOPRED(D19:F21).

Dalam sel B18, masukkan formula =MOPRED(D23:F25).

Mari cari punca persamaan, untuk ini kita masukkan dalam sel B21: =B16/$B$15, dalam sel B22 kita masukkan: = =B17/$B$15, dalam sel B23 kita masukkan: ==B18/$B$15 .

Mari kita dapatkan punca-punca persamaan:

nasi. 1 Menyediakan pilihan serantau

Anda boleh menukar tetapan komputer anda menggunakan Panel Kawalan, melalui tab Serantau dan Bahasa Pilihan dengan peralihan kepada pilihan Sesuaikan.

Nama fungsi terbina dalam Excel bergantung pada versi yang dipasang - Rusia atau Inggeris. Sebagai contoh, dalam versi bahasa Inggeris fungsi penjumlahan kelihatan seperti ini

JUMLAH(A1:A9) .

Fungsi yang sama dalam versi Rusia kelihatan seperti

JUMLAH(A1:A9).

Senarai surat-menyurat antara nama fungsi Rusia dan Inggeris diberikan dalam fail FUNCS.XLS, yang terletak pada komputer anda, biasanya dalam direktori C:\Program Files\Microsoft Office\OfficeVer\1049\, di mana OfficeVer ialah nama versi, contohnya Office 11. Anda boleh membaca lebih lanjut tentang penyetempatan Excel. Fail Excel_Functions.xls menyenaraikan nama semua fungsi dalam 16 bahasa.

Dalam tutorial ini kami menggunakan versi Bahasa Inggeris Excel 2003, di mana pemisah perpuluhan ialah noktah dan pemisah senarai ialah koma. Nama fungsi Rusia disediakan untuk rujukan.

Baris dan lajur boleh dipadamkan, ditambah, disembunyikan dan juga menukar saiznya: ketinggian atau lebar.

Sel boleh mempunyai kandungan: nombor, teks atau formula. Untuk kejelasan, sel boleh diformatkan: tukar fon, warna, bingkai, dsb.

Semua operasi pada helaian dilakukan menggunakan menu yang dibentangkan di bahagian atas tetingkap (). Menu dalam Excel 2007 berbeza dengan ketara daripada versi terdahulu. Di sana, bukannya ikon biasa, reben muncul. Kami tidak akan menjelaskan secara terperinci tentang perbezaan antara versi. Yang berminat boleh baca ni.

1.3. Menangani

Dalam Excel, setiap sel mempunyai alamat yang terdiri daripada tajuk lajur dan baris. Sebagai contoh, alamat sel pertama pada helaian ialah A1. Sel yang terletak di persimpangan lajur ketiga dan baris kelima mempunyai alamat C5. Sel semasa diserlahkan dengan bingkai tebal dan alamatnya dipaparkan dalam tetingkap Kotak Nama. (cm. ). Jika kita ingin menyalin kandungan beberapa sel (contohnya, A1) ke sel lain (contohnya, F 1), anda perlu menulis formula =alamat dalam sel kedua, contohnya =A1. Alamat (pautan) boleh menjadi mutlak, relatif atau bercampur.

Sebagai contoh, sel pertama mempunyai alamat mutlak - $A$1 , alamat relatif - A1 , dan dua alamat bercampur - $A1 dan A$1 . Perbezaan dalam kaedah pengalamatan muncul terutamanya apabila formula disalin dan dipindahkan ke tempat lain. Mari kita jelaskan ini dalam contoh mudah.

nasi. 5 Pengalamatan mutlak dan relatif

1.4. Wilayah

Matriks menduduki kawasan pada helaian. Satu contoh ditunjukkan dalam Rajah. 6, di mana matriks terletak, yang mempunyai 9 baris (dari 2 hingga 10) dan 3 lajur (dari B hingga D).

Apabila bekerja dengan matriks, ia boleh menjadi mudah untuk memberi nama kawasannya. Terdapat dua cara untuk melakukan ini. Yang paling mudah ialah memilih kawasan yang dikehendaki pada helaian, dan kemudian klik dalam tetingkap Kotak Nama (lihat), padamkan alamat di sana dan masukkan nama - contohnya Data (Gamb. 6). Cara lain ialah dengan beroperasi melalui menu: Insert-Name-Define.

Nama kawasan boleh menjadi global - tersedia untuk mana-mana helaian dalam buku kerja, atau setempat - khusus untuk satu helaian sahaja. Dalam kes kedua, nama harus ditakrifkan dalam bentuk: ListName!RangeName

1.5. Pengiraan mudah

Excel menggunakan formula untuk melakukan pengiraan. Formula bermula dengan tanda sama (=) dan boleh termasuk: rujukan, pengendali, fungsi dan pemalar.

Operator membolehkan anda menjalankan pengiraan aritmetik mudah, contoh yang ditunjukkan dalam Rajah. 7

Rajah.7 Pengiraan mudah

1.6. Fungsi

Fungsi ialah formula piawai yang melakukan pengiraan berdasarkan kuantiti yang diberikan, dipanggil argumen. Beberapa contoh fungsi ditunjukkan dalam Rajah. 8.

Rajah.8 Fungsi mudah

Fungsi terdiri daripada nama, diikuti dengan kurungan, kemudian senarai argumen yang dipisahkan koma, dan akhirnya kurungan penutup.

1.7. Beberapa Ciri Penting

Excel menyediakan pelbagai fungsi standard (terbina dalam). Kami tidak boleh mempertimbangkan kesemuanya, jadi kami hanya akan menumpukan perhatian kepada yang paling penting bagi kami.

JUMLAH/ JUMLAH

Menjumlahkan semua nombor dalam senarai argumen atau rantau.

Sintaks :

S U.M. (nombor 1[, nombor2 ] [,... ])

Contoh

Rajah 12 Fungsi JUMLAH

JUMLAH SQ / SUMMKV

Mengembalikan jumlah kuasa dua hujah.

Sintaks :

S UMSQ (nombor 1[, nombor2 ] [,... ])

Contoh

Rajah 13 Fungsi JUMLAH S.Q.

SUMPRODUCT / SUMPRODUCT

Mendarab elemen sepadan tatasusunan yang diberikan dan mengembalikan jumlah produk

Sintaks :

SUMPRODUCT (tatasusunan1, tatasusunan2, ...)

Contoh

Rajah 14 Fungsi SUMPRODUCT

PURATA / PURATA

V AR/DISP

STDEV / STANDARD DEVIATION

CORREL / CORREL

Fungsi boleh digabungkan menjadi formula kompaun, contohnya ditunjukkan dalam Rajah. 19.

Rajah 19 Contoh formula sebatian

1.8. Ralat dalam formula

Apabila memasukkan formula, serta apabila menukar helaian kemudiannya, ralat berlaku. Dalam kes ini, bukannya hasil yang dijangkakan, salah satu simbol yang ditunjukkan dalam Rajah. muncul dalam sel. 20 .

Rajah.20 Ralat dalam formula

Anda boleh menentukan asal ralat menggunakan menu Pengauditan Formula, boleh diakses melalui bahagian Alat.

19. Formula "Menarik".

Apabila bekerja dengan data dalam bentuk matriks, selalunya terdapat keperluan untuk memasukkan bukan satu, tetapi keseluruhan siri formula. Sebagai contoh, apabila melakukan transformasi SNV pada data spektrum, anda perlu mengira min dan sisihan piawai untuk setiap baris. Ia akan menjadi sangat membosankan untuk mengulang formula yang sama berulang kali, menukar hanya hujah, walaupun untuk contoh yang sangat dipotong yang ditunjukkan dalam . Dan dalam data sebenar, bilangan baris atau lajur boleh mencecah puluhan ribu. Nasib baik, ini tidak perlu, kerana anda boleh menggunakan teknik formula "membuang".

Mari kita terangkan teknik ini menggunakan contoh kami. Mari kita mulakan dengan memasukkan formula sampel. Dalam kes ini, ia adalah formula yang diletakkan dalam sel J3.

Rajah 21 Penanda isian

Formula boleh dimasukkan ke dalam sel bersebelahan menggunakan pemegang isian, iaitu segi empat sama hitam kecil di sudut kanan bawah kawasan sel yang dipilih. Apabila anda menuding tetikus di atas penanda ini, penunjuk bertukar kepada salib hitam. Anda kemudian boleh menyeret sel yang mengandungi formula ke dalam julat bersebelahan. Anda boleh menyeret secara menegak, seperti yang ditunjukkan dalam Rajah. 22, dan secara mendatar.

Rajah 22 Mencabut satu siri formula yang serupa

Formula boleh dihasilkan semula dengan cara lain. Mula-mula, salin sel yang mengandungi formula sampel. Kemudian kami menandakan julat sel yang kami perlukan untuk melanjutkan formula, dan melakukan tampalan khas dengan memilih pilihan Formula.

Rajah.23 Menyalin siri formula daripada jenis yang sama

1 .10. Grafik

Anda boleh membuat carta dalam Excel jenis yang berbeza. Tetapi bagi kami, hanya dua jenis yang menarik: gambar rajah serakan (scatter) dan graf (garisan). Contoh plot serakan ditunjukkan dalam Rajah. 24.

Rajah.24 Gambar rajah serakan

Carta jenis ini digunakan untuk membina graf skor, hubungan yang diramalkan diukur, dsb. Mereka berbeza daripada graf garis kerana kedua-dua paksi adalah sama. Dalam graf garis, paksi-x hanya untuk memaparkan pembolehubah kategori, i.e. kuantiti di mana ia bukan magnitud mereka yang penting, tetapi susunan di mana ia muncul. sebab tu graf garis sesuai untuk mewakili kebergantungan pada bilangan komponen utama, contohnya, untuk menggambarkan bagaimana nilai RMSEC dan RMSEP berubah apabila model menjadi lebih kompleks.

Kaedah grafik dalam versi 2003 dan 2007 adalah sangat berbeza. Oleh itu, kami tidak akan memikirkan perkara ini, meninggalkan topik ini untuk kajian bebas.

2. Operasi matriks

2.1. Formula tatasusunan

Banyak operasi matriks dilakukan menggunakan formula jenis khas, dipanggil formula tatasusunan. Mereka berbeza daripada formula lain kerana hasilnya bukan satu nilai (nombor), tetapi satu set nilai - tatasusunan. Apabila memasukkan formula sedemikian, pengesahan khas diperlukan - bukannya kekunci ENTER, anda mesti menekan gabungan tiga kekunci CTRL+SHIFT+ENTER.

Mari kita terangkan penggunaan formula tatasusunan dengan contoh mudah. Mari kita anggap bahawa kita perlu melakukan autoscaling (penyandaran) data yang direkodkan dalam matriks X. Untuk melakukan ini, anda perlu mengira nilai purata terlebih dahulu m j dan sisihan piawai s j untuk setiap ( j th) lajur X, dan kemudian tolak nilai daripada setiap lajur m j dan bahagikan dengan nilai s j

Transformasi sedemikian boleh dilakukan menggunakan formula biasa, seperti yang ditunjukkan dalam Rajah. 25.

Rajah 25 Formula biasa

Ingatlah untuk memasukkan tanda $ sebelum nombor baris 9 ( m) dan 11 ( s) untuk menetapkan kedudukan kuantiti yang sepadan dalam garisan.

Jika matriks X adalah besar, ia adalah lebih mudah untuk menggunakan formula tatasusunan. (Gamb. 26). Mari kita panggil kawasan yang sepadan pada helaian: X, m dan s. Mari kita tandai kawasan kosong N3:R7, dimensi yang bertepatan dengan hasil yang dijangkakan. Selepas ini, masukkan ungkapan =(X-m)/s ke dalam Formula Bar. Gabungan kekunci CTRL+SHIFT+ENTER melengkapkan input. Jika semuanya dilakukan dengan betul, formula ( =(X-m)/s) akan muncul dalam Formula Bar, disertakan dalam pendakap gigi(). Ini adalah tanda formula tatasusunan. .

2.2. Cipta dan edit formula tatasusunan

Untuk memasukkan formula tatasusunan dengan betul, anda perlu memilih kawasan pada helaian yang dimensinya bertepatan dengan hasil yang dijangkakan.

Jika anda memilih kawasan yang terlalu besar, maka semasa pengiraan sel berlebihan akan diisi dengan simbol ralat #N/A. Jika kawasan keluaran lebih kecil daripada yang diperlukan, beberapa keputusan akan hilang. Selepas memilih kawasan, tulis formula dalam Bar Formula dan tekan CTRL+SHIFT+ENTER.

Sebagai alternatif, anda boleh terlebih dahulu memasukkan formula dalam satu sel, kemudian tandakan kawasan output bermula dari sel tersebut (kanan dan bawah), kemudian pergi ke Formula Bar dan tekan CTRL+SHIFT+ENTER.

Untuk menukar formula tatasusunan, anda perlu memilih kawasan yang mengandungi hasilnya. Kemudian anda perlu pergi ke Formula Bar. Dalam kes ini, kurungan kerinting di sekeliling formula ( ) akan hilang. Selepas ini, formula boleh ditukar dan tekan CTRL+SHIFT+ENTER.

Untuk mengembangkan kawasan yang diduduki oleh formula tatasusunan, cukup untuk memilihnya kawasan baru, pergi ke Formula Bar dan tekan CTRL+SHIFT+ENTER. Tetapi untuk mengurangkan kawasan ini (contohnya, untuk menghilangkan simbol #N/A) anda perlu menghabiskan lebih banyak usaha. Mula-mula anda perlu berdiri di mana-mana sel di kawasan itu, pergi ke Formula Bar dan salin garis formula. Kemudian anda perlu memadamkan kandungan kawasan lama dan menandakan kawasan baru yang lebih kecil. Selepas itu, kembali ke Formula Bar, tampal formula dan tekan CTRL+SHIFT+ENTER.

Anda tidak boleh menukar sel individu dalam formula tatasusunan. Apabila anda cuba melakukan ini, amaran muncul.

Rajah 27 Amaran tentang operasi haram dengan formula tatasusunan

2.3. Operasi paling mudah dengan matriks

Menggunakan formula tatasusunan, matriks boleh ditambah dan didarab dengan nombor.

Rajah 28 Menambah matriks dan mendarab dengan nombor

Untuk mendarab dua matriks, gunakan fungsi tersebut MMULT .

2.4. Akses kepada bahagian matriks

Untuk mengakses dan memisahkan bahagian matriks, dua ciri standard daun.

OFFSET / OFFSET

Sintaks

OFFSET (rujukan, barisan, cols[, ketinggian kelebaran])

r eferen – ini merujuk kepada kawasan dari mana offset dikira;
r ows – bilangan baris untuk beralih (positif – bawah, negatif – atas dari awal);
cols – Sama, tetapi untuk lajur (positif - ke kanan, negatif ke kiri dari awal);
ketinggian adalah hujah pilihan. Nombor positif kembalikan rentetan pautan;
lebar ialah hujah pilihan. Bilangan positif lajur pautan yang dikembalikan.

Nota

Jika argumen ketinggian atau lebar ditinggalkan, diandaikan bahawa ketinggian atau lebar yang sama seperti dalam argumen digunakan rujukan ;

Hujah rujukan ialah rujukan kepada kawasan yang mestilah nyata, bukan tatasusunan maya, i.e. berada di suatu tempat di helaian.

Contoh

Rajah 29 Fungsi OFFSET

OFFSET

INDEX / INDEX

Mengembalikan nilai elemen dalam tatasusunan yang dipilih menggunakan indeks baris dan lajur.

Sintaks

INDEKS (rujukan[, nombor_baris] [, bilangan_lajur])

r eferen ialah tatasusunan nilai (matriks);
r ow_ bilangan– hujah pilihan. Bilangan baris dari mana nilai diambil;
kol _num– hujah pilihan. Bilangan lajur dari mana nilai diambil;

Nota

Jika row_num diabaikan, keseluruhan lajur dipilih;

Jika kol_num diabaikan, keseluruhan baris dipilih;

Jika kedua-dua argumen pilihan ditentukan, nilai yang terdapat dalam sel di persimpangan baris dan lajur yang sepadan dikembalikan;

Hujah rujukan boleh menjadi pautan ke kawasan dan tatasusunan maya.

Contoh

Rajah 30 Fungsi INDEKS

INDEKS ialah fungsi tatasusunan dan entrinya mesti dilengkapkan dengan menekan CTRL+SHIFT+ENTER.

2.5. Operasi unary

Rumus unari berikut boleh digunakan untuk matriks.

MINVERSE / MOBR

Mengembalikan matriks songsang.

Sintaks

MINVERSE (tatasusunan )

tatasusunan ialah tatasusunan nilai (matriks).

Nota

Matriks tatasusunan mestilah segi empat sama;

Jika matriks adalah tunggal, simbol ralat #VALUE! dipaparkan. .

Contoh

Rajah.3 1 Fungsi MINVERSE

MINVERSE

TRANSPOSE/TRANSP

Mengembalikan matriks transposed

Sintaks

TRANSPOSE (tatasusunan)

tatasusunan

Contoh

Rajah 3 2 Fungsi TRANSPOSE

TRANSPOSE ialah fungsi tatasusunan dan entrinya mesti dilengkapkan dengan menekan CTRL+SHIFT+ENTER.

MDETERM / MOPRED

Mengembalikan penentu sesuatu matriks.

Sintaks

MDETERM (tatasusunan )

tatasusunan ialah tatasusunan nilai (matriks).

Nota

Matriks tatasusunan hendaklah segi empat sama.

MMENENTUKAN bukan fungsi tatasusunan dan inputnya mesti dilengkapkan dengan menekan satu kekunci MASUK.

2.6. Operasi binari

Operasi binari berikut boleh digunakan pada matriks.

MMULT

Mengembalikan hasil darab dua matriks.

Sintaks

MMULT (tatasusunan1, tatasusunan2)

tatasusunan1, tatasusunan2 – matriks darab.

Nota

Bilangan lajur dalam matriks tatasusunan1 mestilah sama dengan bilangan baris dalam matriks tatasusunan2, jika tidak simbol ralat #VALUE! ;

Tatasusunan yang terhasil mestilah mengandungi tidak lebih daripada 5461 elemen (Excel 2003).

Contoh

Rajah 33 Fungsi MMULT

MMULT ialah fungsi tatasusunan dan entrinya mesti dilengkapkan dengan menekan CTRL+SHIFT+ENTER .

2.7. Regresi

y=b+m 1 x 1 +…+m J x J+e

Menganggarkan nilai vektor tindak balas yang diketahui known_y"s Untuk tetapkan nilai matriks peramal dikenali_x"s dan mengembalikan nilai y, untuk tatasusunan yang diberikan new_x"s .

Sintaks

TREND (known_y"s[,known_x"s] [,new_x"s] [,const])

known_y"s y(set penentukuran);
known_x"s – hujah pilihan. Matriks nilai peramal yang diketahui X(set penentukuran);
new_x"s – hujah pilihan. Matriks nilai peramal baharu X
const ialah hujah pilihan. Nilai Boolean yang menunjukkan sama ada parameter diperlukan b adalah sama dengan sifar. Jika const mempunyai nilai BENAR atau ditinggalkan, maka b b=0.

Nota

vektor known_y"s mesti menduduki satu lajur, maka setiap lajur matriks tatasusunan dikenali_x ditafsirkan sebagai pembolehubah yang berasingan;

Jika known_x"s diabaikan, ia diandaikan sebagai vektor nombor (1;2;3;...) dengan saiz yang sama seperti dikenali_y "s ;

Matriks nilai baru n ew_x"s mesti mempunyai bilangan lajur (pembolehubah) yang sama dengan matriks_x yang diketahui;

Jika hujah n ew_x"s diketepikan, ia diandaikan sepadan dengan tatasusunan yang dikenali_x". Hasilnya ialah vektor di mana bilangan baris adalah sama dengan bilangan baris dalam tatasusunan n ew_x"s.

Contoh

Rajah 34 Fungsi TREND

Fungsi TREND ialah fungsi tatasusunan dan entrinya mesti dilengkapkan dengan menekan CTRL+SHIFT+ENTER.

LINEST / LINEN

Melengkapkan fungsi TREND dan mengeluarkan beberapa nilai statistik berkaitan dengan regresi

y=b+m 1 x 1 +…+m J x J+e

Sintaks

LINEST (known_y"s[,known_x"s] [,new_x"s] [,const] [,stats])

known_y"s– vektor nilai tindak balas yang diketahui y(set penentukuran);
known_x"s – hujah pilihan. Matriks nilai peramal yang diketahui X(set penentukuran);
new_x"s – hujah pilihan. Matriks nilai peramal baharu X baharu (set ujian) yang mana nilai tindak balas dikira dan dipaparkan;
const ialah hujah pilihan. Nilai Boolean yang menunjukkan sama ada parameter diperlukan b adalah sama dengan sifar. Jika const adalah BENAR atau ditinggalkan, maka b dikira dengan cara biasa, sebaliknya b=0;
statistik adalah hujah pilihan. Nilai Boolean yang menentukan sama ada nilai statistik tambahan harus dikeluarkan. Jika statistik adalah PALSU atau ditinggalkan, maka hanya anggaran pekali dicetak m J, …, m 2 , m 1 dan b. Jika tidak, keseluruhan jadual dipaparkan

nasi. 35 Fungsi jadual output LINEST

m J..., m 2 , m 1 dan b– anggaran pekali regresi;

s J..., s 2 , s 1 Dan s b– ralat piawai untuk anggaran pekali regresi;

R 2 – pekali penentuan;

s y – kesalahan biasa penilaian y;

F– F-statistik;

DoF– bilangan darjah kebebasan;

SS reg – jumlah regresi kuasa dua;

SS res ialah jumlah baki kuasa dua.

Nota

Contoh

Rajah 36 Fungsi LINEST

Fungsi LINEST ialah fungsi tatasusunan dan entrinya mesti dilengkapkan dengan menekan CTRL+SHIFT+ENTER.

2.8. Ralat kritikal dalam Excel 2003

Dalam fungsi Excel 2003 TRE ND Dan LINEST dalam keadaan tertentu mereka memberikan hasil yang salah.

Ini berlaku apabila serentak:

nilai purata bagi setiap pembolehubah dalam matriks peramal X sama dengan sifar;

nilai tindak balas purata Y tidak sama dengan sifar.

2.9. Tatasusunan Maya

Apabila menganalisis data, masalah menyimpan sering timbul keputusan pertengahan, yang tidak diperlukan sendiri, tetapi hanya untuk mengira nilai lain yang berguna daripada mereka. Sebagai contoh, sisa dalam kaedah PCA selalunya tidak menarik bagi kami, tetapi diperlukan hanya untuk menentukan jumlah varians yang dijelaskan, jarak ortogon, dsb. Selain itu, saiz tatasusunan perantaraan sedemikian boleh menjadi sangat besar, dan selain itu, ia perlu dikira untuk nilai yang berbeza bagi bilangan komponen utama. Semua ini membawa kepada pengisian buku kerja jumlah yang besar keputusan pertengahan yang tidak perlu. Ini boleh dielakkan dengan menggunakan tatasusunan maya. Mari kita jelaskan intipati mereka dengan contoh mudah.

Rajah 38 Contoh penggunaan tatasusunan maya

Mari kita anggap bahawa matriks diberikan A, tetapi anda perlu mengira penentu matriks A t A. Dalam Rajah. Rajah 38 menunjukkan dua kaedah pengiraan. Yang pertama adalah melalui urutan tatasusunan perantaraan yang ditandai dengan anak panah merah. Yang kedua menggunakan satu formula, ditunjukkan dengan anak panah hijau. Kedua-dua laluan membawa kepada hasil yang sama, tetapi laluan merah mengambil banyak ruang pada helaian, dan laluan hijau secara berurutan menggunakan beberapa tatasusunan maya perantaraan. Kesemuanya, sebenarnya, bertepatan dengan tatasusunan sebenar laluan merah, tetapi tidak dipaparkan pada helaian.

Tatasusunan pertama ialah matriks transpos A t , diperolehi sebagai hasil daripada fungsi (A).

Tatasusunan maya kedua diperoleh apabila tatasusunan maya pertama didarab dengan matriks A menggunakan fungsi (TRANSPOSE(A), A) .

Dan akhirnya, fungsi itu digunakan pada tatasusunan maya kedua ini.

3. Sambungan Keupayaan Excel

3.1. Pengaturcaraan. bahasa VBA

Kadang-kadang ciri standard Excel tidak mencukupi dan anda perlu menambah rutin anda sendiri. Untuk tujuan ini, bahasa pengaturcaraan khas digunakan - Microsoft Visual Basic untuk Aplikasi (VBA). Dengan itu anda boleh mencipta makro– set arahan yang melakukan urutan tindakan tertentu, dan fungsi– program untuk pengiraan khas pada helaian. Makro ialah cara untuk mengautomasikan prosedur standard. Apabila makro telah dibuat, ia boleh digunakan untuk mengulang tindakan rutin. Anda boleh mengakses makro melalui menu Tools-Macro-Macros. Kadangkala adalah mudah untuk menetapkan makro kepada butang baharu pada bar alat atau helaian.

Fungsi yang dicipta oleh pengguna dipanggil dengan cara yang sama seperti fungsi terbina dalam standard - melalui Formula Bar.

Agar makro dan fungsi yang ditentukan pengguna tersedia untuk digunakan, anda perlu menetapkan tahap keselamatan yang sesuai melalui menu Tools-Macro-Security (Excel 2003)

Rajah.39 Memilih tahap keselamatan dalam Excel 2003

Dalam Excel 2007, menetapkan tahap keselamatan dilakukan melalui Butang Pejabat - Pilihan Excel - Pusat Amanah.

Rajah 40 Memilih tahap keselamatan dalam Excel 2007

Jika tahap Sederhana (2003) atau Lumpuhkan semua makro dengan pemberitahuan(2007), maka setiap kali anda log masuk ke Excel, sistem akan meminta kebenaran untuk menggunakan makro. Kami mengesyorkan menetapkan tahap seperti yang ditunjukkan dalam Rajah. 39 atau Rajah. 40, tetapi jangan abaikan antivirus yang boleh dipercayai untuk diperiksa fail luar Excel.

Pada mulanya Pemasangan Excel 2007, keupayaan untuk bekerja dengan VBA adalah sangat terhad. Untuk memulihkannya, anda perlu melalui rantaian Butang Pejabat–Pilihan Excel–Popular dan dayakan pilihan Tunjukkan Tab Pembangun dalam Reben.

3.2. Contoh

Mari kita lihat persoalan menggunakan alat VBA menggunakan contoh.

Apabila memodelkan kinetik bukan isoterma (DSC, TGA, dll.), fungsi eksponen kamiran diperlukan E 1 (x). A-priory,

Untuk mengira E 1 (x) anda boleh menggunakan siri tak terhingga

Pada lembaran kerja Buku kerja Excel setiap ahli siri ini boleh diletakkan dalam sel yang berasingan dan kemudian disimpulkan. Kaedah ini ditunjukkan dalam Rajah. 41.

Rajah 41 Pengiraan fungsi E 1 (x) pada helaian

Kaedah pengiraan ini tidak boleh dianggap berjaya. Pertama, pengiraan mengambil banyak ruang pada helaian. Tetapi yang paling penting, tidak jelas berapa banyak istilah dalam satu siri yang perlu disimpulkan - kadang-kadang 10 sudah cukup, dan kadang-kadang 50 tidak mencukupi.

3.3. Contoh makro

Kaedah kedua adalah berdasarkan hubungan berulang yang menghubungkan dua istilah bersebelahan dalam siri

Untuk menggunakan hubungan ini, adalah perlu untuk mengatur prosedur berulang pada helaian. Sebagai contoh, seperti yang ditunjukkan dalam Rajah. 42

Rajah 42 Pengiraan fungsi E 1 (x) secara berulang

Satu langkah lelaran ialah peralihan daripada nilai dalam kawasan J2:J4 kepada nilai dalam kawasan L2:L4. Untuk membuat lelaran seterusnya, anda perlu menyalin nilai yang diperoleh dalam kawasan L2:L4 dan tampalkannya ke dalam kawasan J2:J4. Dalam kes ini, anda hanya perlu memasukkan nilai, tanpa formula. Nilai dalam rantau H2:H4 memberi nilai asal untuk memulakan lelaran. Dengan mengulangi operasi Salin-Tampal Khas berkali-kali, anda boleh mendapatkan nilai yang diingini dalam sel L4. Walau bagaimanapun, penyalinan adalah tugas yang membosankan dan adalah baik untuk mengautomasikannya. Anda boleh menulis makro untuk ini.

Cara paling mudah untuk mula mencipta makro ialah melalui menulis arahan, dilakukan pada helaian. Untuk melakukan ini, pergi ke bahagian menu Alat–Makro–Rekod Makro Baharu. Tetingkap muncul (Gamb. 43), di mana anda boleh menentukan nama makro dan di mana ia akan ditempatkan.

Rajah 43 Rakaman makro

Selepas mengklik OK, rakaman semua tindakan yang dilakukan pada helaian bermula. Apabila semua yang perlu disimpan dalam makro, rakaman mesti dihentikan dengan arahan Alat–Makro–Berhenti Rakaman. Hasilnya boleh dilihat dengan pergi ke Editor Visual Basic .

Rajah 44 Visual Basic Editor

Dalam Rajah.

Rajah 45 Fungsi IntExp

Dalam Rajah. 45 menunjukkan kod untuk fungsi ini dan contoh memanggilnya.

Kami tidak akan bercakap tentang pengaturcaraan dalam VBA, kerana... Ini adalah soalan yang sangat besar dan kompleks. Anda boleh mempelajarinya sendiri menggunakan banyak manual yang terdapat di Internet, sebagai contoh.

VBA ialah bahasa yang agak perlahan dan tidak sesuai untuk pengiraan besar. Sebagai contoh, anda tidak sepatutnya menulis prosedur untuk penguraian PCA dalam bahasa ini - ia akan mengambil masa yang sangat lama untuk mengira pada tatasusunan yang besar. Adalah lebih tepat untuk menganggap Excel dan VBA sebagai antara muka (hujung hadapan) untuk input dan output data, yang kemudiannya dipindahkan ke perpustakaan dinamik (DLL) yang ditulis dalam bahasa pantas, seperti C++ (hujung belakang). Konsep inilah yang telah dilaksanakan dalam add-on Lebih cergas Dan Kimometrik .

Ketahui lebih lanjut tentang penggunaan fungsi tersuai untuk mengembangkan keupayaan add-in Kimometrik boleh dibaca.

3.5. Alat tambah

Program yang ditulis oleh pengguna disimpan dalam buku kerja Excel di mana ia dicipta, di dalamnya bahagian khas, dipanggil modul. Untuk menjadikan makro ini tersedia dalam buku kerja lain, anda boleh menyalinnya di sana, tetapi anda juga boleh melakukannya secara berbeza - buat alat tambah.

Superstruktur– ini istimewa Fail Excel, mengandungi beberapa modul VBA yang dikaitkan dengan semua perpustakaan yang diperlukan DLL. Alat tambah boleh disambungkan ke Excel untuk mengembangkan keupayaannya.

Standard versi Excel termasuk beberapa alat tambah, antaranya dua yang patut diberi perhatian untuk kami: Penyelesai Add-In dan Analysis Toolpak.

3.6. Memasang alat tambah

Sebelum anda boleh mula menggunakan add-in, anda mesti melengkapkan prosedurnya. pemasangan, yang terdiri daripada dua bahagian.

Pada fasa pertama, fail yang disertakan dalam pakej tambahan diletakkan pada komputer. Sesetengah pakej mempunyai program Setup.exe yang melakukan ini secara automatik. Dalam yang lain, anda perlu meletakkan fail itu sendiri. Mari kita terangkan bagaimana untuk melakukan ini. Pakej ini semestinya termasuk fail dengan sambungan XLA dan beberapa fail tambahan dengan sambungan DLL, HLP, dsb. Semua fail sokongan hendaklah diletakkan dalam direktori berikut: C:\Windows, atau C:\Windows\System atau C:\Windows\System32. Fail utama (dengan sambungan XLA) boleh, pada dasarnya, terletak di mana-mana, tetapi dua direktori lebih disukai.

Microsoft mengesyorkan meletakkan fail XLA dalam direktori C:\Documents and Settings\User\Application Data\Microsoft\AddIns, di mana Pengguna ialah nama yang anda gunakan untuk log masuk. Fail ini kemudiannya boleh dimuat turun dengan cepat semasa fasa kedua pemasangan. Walau bagaimanapun, jika buku kerja digunakan pada beberapa komputer, dengan nama Pengguna yang berbeza, maka, apabila menukar komputer, sambungan dengan fail tambahan utama hilang dan ia perlu dikemas kini.

Oleh itu, kami cadangkan meletakkan fail Chemometrics.xla ke direktori yang mempunyai nama yang sama pada komputer yang berbeza, Sebagai contoh C:\Program Files\Chemometrics. Pemasangan automatik alat tambah Chemometrics Add-In diterangkan

Fasa kedua dijalankan dari buka buku Excel. Dalam versi 2003 anda perlu melaksanakan urutan perintah Tools-Add-Ins, dan dalam versi 2007 urutannya ialah: Butang Pejabat-Pilihan Excel-Tambah-Masuk-Go. Dalam tetingkap yang muncul (lihat Rajah 45) anda perlu klik Semak imbas dan cari dalam komputer fail yang diperlukan XLA.

Rajah 45 Memasang alat tambah

Setelah alat tambah dipasang, ia boleh diaktifkan dan dinyahaktifkan dengan menandakan kotak di sebelah nama. Untuk mengalih keluar tambahan, anda perlu menyahtanda kotak di sebelah namanya dalam tetingkap Add-In, tutup Excel dan padamkan semuanya sebelum ini fail yang dipasang daripada komputer.

Kesimpulan

Kami melihat teknik asas untuk bekerja dengan matriks dalam Sistem Excel. Masih banyak perkara penting yang tertinggal di luar skop manual. Manual akan membantu mengisi sebahagian jurang ini.

Dalam bahagian soalan Bagaimana untuk mencipta matriks dalam Excel???? diberikan oleh penulis Masha Kalganova jawapan yang terbaik ialah Apa sebenarnya yang perlu dilakukan?
Dokumen Excel ialah matriks tanpa dimensi (semua dimensi), setiap elemen boleh menjadi nombor, teks atau sebarang nilai lain. Ya, sekurang-kurangnya dengan formula.
Jika anda mencetak 3 nombor berturut-turut, di bawahnya 3 lagi berturut-turut, di bawahnya 3 lagi berturut-turut, kami mendapat matriks persegi 3x3.
Apa soalannya?
Mendarab matriks dengan nombor dalam Excel
Tulis formula?
Dalam sel kiri atas matriks baharu
= sel kiri atas matriks lama * sel dengan nombor (cucuk dengan tetikus), tekan F4 untuk menjadi $x$y, dengan x,y ialah koordinat sel. Ini akan membetulkan sel semasa menyalin. Seterusnya, tekan Enter dan salin kandungan sel ke dalam 2 sel bersebelahan berturut-turut. Seterusnya, kami menyalin 3 sel ini ke dalam 3 baris seterusnya dan mendapat hasil yang sama seperti dalam gambar saya. Iaitu, mendarab matriks dengan nombor.
Alamak, mereka tidak tahu matematik, tetapi mereka melayari Internet!

DALAM program Excel Anda boleh bekerja dengan matriks seperti dengan julat. Iaitu, koleksi sel bersebelahan yang menduduki kawasan segi empat tepat.

Alamat matriks ialah sel kiri atas dan kanan bawah julat, ditunjukkan oleh titik bertindih.

Formula tatasusunan

Pembinaan matriks menggunakan Excel dalam kebanyakan kes memerlukan penggunaan formula tatasusunan. Perbezaan utama mereka ialah hasilnya bukan satu nilai, tetapi tatasusunan data (julat nombor).

Untuk menggunakan formula tatasusunan:

Pilih julat di mana hasil formula harus muncul.
Masukkan formula (seperti yang dijangkakan, bermula dengan tanda “=").
Tekan kombinasi kekunci Ctrl + Shift + Enter.

Bar formula memaparkan formula tatasusunan dalam pendakap kerinting.

Untuk menukar atau memadam formula tatasusunan, anda mesti memilih keseluruhan julat dan ikut langkah yang sesuai. Untuk membuat perubahan, gunakan gabungan yang sama (Ctrl + Shift + Enter). Sebahagian daripada tatasusunan tidak boleh diubah.

Menyelesaikan matriks dalam Excel

Dengan matriks dalam Excel, operasi seperti: transposisi, penambahan, pendaraban dengan nombor / matriks dilakukan; mencari matriks songsang dan penentunya.

Transpose

Ubah matriks - tukar baris dan lajur.

Mula-mula, mari kita tandakan julat kosong di mana kita akan menukar matriks. Matriks asal mempunyai 4 baris - julat untuk transposisi mesti mempunyai 4 lajur. 5 lajur ialah lima baris dalam kawasan kosong.

Klik OK. Setakat ini fungsi itu membuang ralat. Pilih keseluruhan julat di mana matriks perlu ditukar. Tekan butang F2 (pergi ke mod penyuntingan formula). Tekan gabungan Kekunci Ctrl+ Shift + Enter.

Kelebihan kaedah kedua: Apabila perubahan dibuat kepada matriks asal, matriks yang ditranspose secara automatik berubah.

Penambahan

Anda boleh menambah matriks dengan bilangan elemen yang sama. Bilangan baris dan lajur dalam julat pertama mestilah sama dengan bilangan baris dan lajur dalam julat kedua.

Dalam sel pertama matriks yang terhasil, anda perlu memasukkan formula bentuk: = elemen pertama matriks pertama + elemen pertama kedua: (=B2+H2). Tekan Enter dan regangkan formula untuk meliputi keseluruhan julat.

Pendaraban matriks dalam Excel

Tugas:

Untuk mendarab matriks dengan nombor, anda perlu mendarab setiap elemennya dengan nombor itu. Formula dalam Excel: =A1*$E$3 (rujukan kepada sel dengan nombor mestilah mutlak).

Mari kita darabkan matriks dengan matriks julat yang berbeza. Anda boleh mencari hasil darab matriks hanya jika bilangan lajur matriks pertama adalah sama dengan bilangan baris kedua.

Dalam matriks yang terhasil, bilangan baris adalah sama dengan bilangan baris matriks pertama, dan bilangan lajur adalah sama dengan bilangan lajur kedua.

Untuk kemudahan, kami memilih julat di mana keputusan pendaraban akan diletakkan. Jadikan sel pertama medan yang terhasil aktif. Masukkan formula: =MULTIPLE(A9:C13,E9:H11). Masukkannya sebagai formula tatasusunan.

Matriks songsang dalam Excel

Adalah masuk akal untuk mencarinya jika kita berurusan dengan matriks segi empat sama (bilangan baris dan lajur adalah sama).

Dimensi matriks songsang sepadan dengan saiz matriks asal. Fungsi Excel– MOBR.

Pilih sel pertama buat masa ini julat kosong untuk matriks songsang. Masukkan formula “=MOBR(A1:D4)” sebagai fungsi tatasusunan. Satu-satunya hujah ialah julat dengan matriks asal. Kami mendapat matriks songsang dalam Excel: