Cara mengatasi persamaan dengan Excel. Solusi Persamaan Excel

Salah satu masalah pembelajaran komputer yang paling mendesak adalah masalah seleksi dan penggunaan program pelatihan bijaksana secara pedagogis.

Saat mempelajari topik individu dan menyelesaikan beberapa masalah dalam pelajaran matematika di sekolah menengah, perhitungan rumit sebagai, misalnya, ketika memecahkan persamaan dengan membagi segmen menjadi dua atau metode pendekatan berturut-turut, makhluk matematika dikalahkan, mereka lakukan Tidak memberikan keindahan, rasionalitas metode solusi yang digunakan.

Dalam artikel ini, saya mempresentasikan tugas-tugas yang solusinya menggunakan MS Excel memungkinkan Anda untuk mendapatkan keputusan visual dan dapat diakses untuk memahami keputusan, menunjukkan logikanya, rasionalitasnya. Sepanjang jalan, siswa mendapatkan keterampilan berkelanjutan untuk bekerja dengan program ini.

Menemukan akar persamaan menggunakan pemilihan parameter

Contoh 1.

Biarkan diketahui bahwa di staf rumah sakit terdiri dari 6 senibol, 8 perawat, 10 dokter, 3 kepala departemen, kepala dokter, kepala farmasi, kepala ekonomi dan kepala rumah sakit. Total dana gaji bulanan adalah 1000.000 unit konvensional. Penting untuk menentukan apa yang harus dilakukan oleh karyawan rumah sakit.

Solusi untuk tugas seperti itu dapat dicari dengan metode pemadaman. Namun, yang terbaik, dibutuhkan banyak waktu. Anda dapat menawarkan cara lain untuk menyelesaikannya. Di Excel, itu diimplementasikan sebagai pencarian untuk nilai parameter rumus yang memenuhi nilai spesifiknya.

Bangun model menyelesaikan tugas ini. Sebagai dasar, ambil gaji perawat, dan gaji yang tersisa akan menghitung, berdasarkan: dalam berkali-kali atau begitu banyak. Berbicara dengan bahasa matematika, setiap gaji adalah fungsi linear dari gaji sanitasi: A I * C + di I, di mana dengan - gaji keperawatan; Dan saya dan di I - koefisien bahwa untuk setiap posisi ditentukan sebagai berikut:

perawat mendapat perawat 1,5 kali lebih banyak (A 2 \u003d 1,5; dalam 2 \u003d 0);
dokter 3 kali lebih banyak perawat (a 3 \u003d 3; pada 3 \u003d 0);
kepala Departemen - dengan 30 tahun. lebih dari seorang dokter (a 4 \u003d 3; b 4 \u003d 30);
kepala Farmasi - 2 kali lebih banyak perawat (5 \u003d 2; dalam 5 \u003d 0);
kepala pertanian - pada 40 tahun. lebih banyak perawat (a 6 \u003d 1,5; dalam 6 \u003d 40);
kepala rumah sakit - pada 20 tahun. Lebih banyak dokter kepala (A 8 \u003d 4; pada 8 \u003d 20);
kepala Dokter adalah 4 kali lebih banyak perawat (A 7 \u003d 4; pada 7 \u003d 0);

Mengetahui jumlah orang di setiap posting, model kami dapat ditulis sebagai persamaan: n 1 * (A 1 * C + B 1) + n 2 * (A 2 * C + B 2) + ... + N 8 * (A 8 * C + B 8) \u003d 1000000, di mana n 1 adalah jumlah tanda, N 2 adalah jumlah perawat, dll.

Dalam persamaan ini, kita tahu 1 ... a 8, b 1 ... b 8 dan n 1 ... n 8, dan dengan tidak diketahui. Analisis persamaan menunjukkan bahwa tugas menghitung upah dikurangi menjadi pemecahan persamaan linear sehubungan dengan C. Misalkan gaji perawat 150,00 Y.E.

Masukkan data sumber ke dalam lembar kerja spreadsheet, seperti yang ditunjukkan di bawah ini.


Gaji madu. Pekerja
Posisi	Gaji	Jumlah karyawan	Total gaji.
Sanitary.
Perawat

Kepala Departemen
Kepala Farmasi.

Kepala dokter
Kepala RSUD
*Dana umum sama*

Di kolom D, hitung upah untuk setiap posting. Misalnya, untuk sel D4, formula perhitungan memiliki formulir \u003d B4 * $ D $ 3 + C4.

Di kolom f, hitung gaji semua karyawan dari posisi ini. Misalnya, untuk sel F3, formula perhitungan memiliki formulir \u003d D3 * E3.

Di sel F11, total dana upah rumah sakit. Lembar kerja spreadsheet akan terlihat seperti ditunjukkan di bawah ini.


Gaji madu. Pekerja
Posisi	Gaji	Jumlah karyawan	Total gaji.
Sanitary.
Perawat

Kepala Departemen
Kepala Farmasi.

Kepala dokter
Kepala RSUD
*Dana umum sama*

Untuk menentukan gaji keperawatan sehingga dana penyelesaian sama dengan yang ditentukan:

Aktifkan perintah Pemilihan parameterdi tab Data / Data / Analisis / Analisis "Apa, jika";
Di bidang "Set in the Cell", jendela muncul untuk memasukkan referensi ke sel F11 yang berisi rumus;
Di bidang "Nilai", tekan hasil yang diinginkan dari 1000000;
Di bidang "Changing Cell Value", masukkan tautan ke Variable D3 Cell dan klik tombol BAIK.

Analisis tugas menunjukkan bahwa persamaan linear dapat diselesaikan dengan menggunakan Excel. Tentu saja, persamaan seperti itu dapat memecahkan sekolah apa pun. Namun, karena contoh sederhana ini, menjadi jelas bahwa pencarian untuk nilai parameter formula yang memuaskannya dengan nilai tertentu tidak lain adalah larutan persamaan numerik. Dengan kata lain, menggunakan Excel, persamaan apa pun dengan satu variabel dapat diselesaikan.

Tugas untuk Siswa:

Membuat beberapa opsi untuk staf menggunakan fungsi Pemilihan parameterdan mengatur bentuk tabel :

Ubah jumlah karyawan di berbagai posisi ;
Ambil gaji perawat dalam kondisi baru;
Buat tabel beberapa opsi untuk jadwal staf.

Pertimbangkan contoh lain untuk menemukan akar persamaan menggunakan pemilihan parameter. Saat memecahkan persamaan ini, metode perkiraan berurutan juga digunakan. Siswa di kelas dengan studi mendalam tentang matematika terbiasa dengan metode ini. Oleh karena itu, contoh ini tersedia untuk siswa lain, saya mengusulkan teori singkat tentang metode ini.

Biarkan persamaan direkam dalam formulir x \u003d f (x) diberikan. Pilih beberapa perkiraan awal x 1 dan ganti bukan x dalam f (x). Nilai yang diperoleh x 2 \u003d F (x 1) dari fungsi ini dianggap sebagai perkiraan kedua. Selanjutnya, perkiraan ketiga dengan Formula X 3 \u003d F (x 2) ditemukan dan sebagainya. Dengan demikian, kami memperoleh urutan x 1, x 2, x 3, ..., x n, ... angka memiliki batas α. Kemudian, jika fungsi f (x) terus menerus, dari kesetaraan x n + 1 \u003d f (x n) kita mendapatkan α \u003d f (α). Ini berarti bahwa α adalah solusi dari persamaan x \u003d f (x).

Contoh 2.

Mari kita berikan polinomial dari tingkat ketiga:

x 3 -0.01x 2 -0.7044x + 0.139104 \u003d 0.

Karena kami mencari akar polinomial tingkat ketiga, maka tidak ada lebih dari tiga akar nyata. Untuk menemukan akarnya, mereka awalnya dilokalisasi, yaitu, untuk menemukan interval yang ada. Interval-interval lokasi akar ini dapat berfungsi sebagai interval, di ujungnya fungsi memiliki tanda yang berlawanan. Untuk menemukan interval, di ujungnya fungsi mengubah tanda, perlu untuk membangun jadwal atau menonjol. Kami akan membuat tabel nilai fungsi pada interval [-1; 1] dengan kenaikan 0,2. Untuk ini yang Anda butuhkan:

Perkenalkan nilai -1 dalam sel A2, dan dalam sel A3, nilainya -0,8.
Pilih kisaran A2: A3, letakkan pointer mouse pada penanda pengisian kisaran ini dan rentangkan pada kisaran A4: A12, argumennya protagated.
Di sel B2 masukkan formula: \u003d A2 ^ 3-0.01 * A2 ^ 2-0.7044 * A2 + 0,139104
Pilih sel B2. Tempatkan pointer mouse pada penanda pengisian sel ini dan rentangkan pada kisaran B3: B12. Fungsinya juga telah ditunda.

Nilai argumen H.	Nilai fungsi u

Itu dapat dilihat dari tabel bahwa polinoma mengubah tanda pada interval [-1; -0,8], dan, oleh karena itu, pada masing-masing interval ini ada akarnya. Karena polinomial tingkat ketiga tidak memiliki lebih dari tiga akar, mereka semua terlokalisasi.

Sebelum menemukan akarnya dengan memilih parameter, Anda harus melakukan beberapa pekerjaan persiapan:

Atur keakuratan dengan root. Root menggunakan pemilihan parameter adalah metode pendekatan berurutan. Untuk ini dalam pengaturan panel akses cepat / perintah lain , Dan pada tab Formula. kotak dialog Parameter Exel. Set B. Parameter perhitungan kesalahan relatif dan membatasi Iterasi masing-masing 0,00001 dan 1000.
Ambil sel di lembar kerja, misalnya C2, di bawah root yang diinginkan. Sel ini akan memainkan peran ganda. Sebelum penggunaan pemilihan parameter di dalamnya, itu adalah pendekatan awal terhadap akar persamaan, dan setelah digunakan - nilai perkiraan dari root ditemukan.
Root menggunakan pemilihan parameter kami menemukan metode perkiraan berurutan. Oleh karena itu, dalam sel C2, Anda harus memasukkan nilai yang mendekati root yang diinginkan. Dalam kasus kami, segmen pertama lokalisasi root adalah [-1; -0.8]. Akibatnya, untuk pendekatan awal ke root, masuk akal untuk mengambil titik rata-rata segmen ini -0,9.
Ambil sel, misalnya D2, di bawah fungsi yang dilakukan oleh pencarian root dilakukan, dan alih-alih fungsi yang tidak diketahui, fitur ini harus menunjukkan tautan ke sel, dialokasikan di bawah root yang diinginkan. Jadi, dalam sel D2, masukkan rumus: \u003d c2 ^ 3-0,01 * c2 ^ 2-0.7044 * C2 + 0,139104

Demikian pula, Anda harus pergi dengan dua akar lain yang diinginkan:

Ambil sel C8 di bawah akar kedua, masukkan ke dalamnya perkiraan awal 0,3, dan dalam sel D8 Masukkan rumus berikut: \u003d c8 ^ 3-0,01 * c8 ^ 2-0.7044 * C8 + 0,139104
Bagi sel C10 di bawah akar kedua, masukkan ke dalamnya perkiraan awal 0,7, dan dalam sel D10 memperkenalkan rumus berikut: \u003d C10 ^ 3-0,7044 * C10 + 0,139104

Hasil tindakan ditunjukkan dalam tabel.

MAKNA H.	V. Nilai	Perkiraan awal sebelum menerapkan metode	Fungsi makna

Sekarang Anda dapat pindah untuk menemukan akar pertama dari persamaan:

Pilih perintah Pemilihan parameter. Layar menampilkan kotak dialog. Pemilihan parameter.

Di bidang Instal di sel.masukkan tautan ke sel D2. Bidang ini memberikan referensi ke sel di mana rumus diperkenalkan, yang menghitung nilai bagian kiri persamaan. Untuk menemukan root menggunakan parameter pilih, persamaan harus diserahkan dalam formulir ini sehingga bagian kanannya tidak mengandung variabel.
Di bidang Nilai Masukkan 0. Ini menunjukkan nilai dari sisi kanan persamaan.
Di bidang Mengubah nilai sel Masukkan C2. Bidang ini memberikan tautan ke sel yang dialokasikan di bawah variabel.
tekan tombolnya BAIK..

Jendela menampilkan jendela Hasil pemilihan parameter Dengan hasil tim Pemilihan parameter. Selain itu, produk yang dipertimbangkan menempatkan nilai perkiraan akar ke dalam sel C2. Dalam hal ini, itu -0.920. Demikian pula, ada dua akar yang tersisa dalam sel C8 dan C10. Mereka sama dengan 0,210 dan 0,721.

MAKNA H.	V. Nilai	Akar persamaan	Fungsi makna

Tugas untuk Siswa:

Temukan semua akar persamaan

1. x 3 -2.92x 2 + 1.4355x + 0,791136 \u003d 0

2. x 3 -2.56x 2 -1,3251x + 4,395006 \u003d 0

3. x 3 + 2.84x 2-5.6064x-14,766336 \u003d 0

Menemukan akar persamaan dengan membagi segmen menjadi dua

Metode metode singkat. Biarkan fungsi kontinu f (x) memiliki nilai dari tanda-tanda yang berbeda di ujung segmen, yaitu, f (a) f (b)<0.Тогда уравнение F(x)=0 имеет корень внутри этого отрезка. Отрезок отрезком локализации корня. Пусть c=(a+b)/2 – середина отрезка . Если F(a)F(c)<=0, то корень находится на отрезке , который берем за новый отрезок локализации корня. Если F(a)F(c)>0, kemudian untuk segmen baru dari lokasi root yang kita ambil. Ini adalah bahwa segmen baru dari lokalisasi akar dua kali lebih sedikit. Proses Divisi Segmen untuk lokalisasi root dilanjutkan hingga panjangnya menjadi kurang ε, keakuratan akar. Dalam hal ini, titik segmen lokalisasi berbeda dari akar tidak lebih dari ε / 2.

Temukan akar persamaan x 2 -2 \u003d 0 dengan akurasi 0,001 dengan membagi segmen menjadi dua. Untuk segmen awal lokalisasi akar dipilih. Untuk mengimplementasikan metode ini, masukkan formul atau nilai di bawah tabel di bawah ini dalam tabel:

Sel	Rumus atau nilai




	\u003d (A3 ^ 2-2) * (C3 ^ 2-2)

	If (B3-A3<$B$1;""Корень найден и равен "" & текст (C3;""0,000""); "" "")
	Jika (D3.<=0; A3;C3)
	Jika (D3.<=0; C3; B3)

	\u003d (A4 ^ 2-2) * (C4 ^ 2-2)

	If (B4-A4<$B$1; ""Корень найден и равен "" & текст(C4; ""0,000""); "" "")

Sekarang tetap hanya untuk memilih rentang A4: F4, letakkan pointer mouse pada marker untuk mengisinya dan mencelupkannya sampai pesan muncul di kolom F yang ditemukan root. Dalam hal ini, pesan akan muncul di sel F14, dan nilai root akurat hingga 0,001 sama dengan 1.415.

Jumlah langkah dapat didefinisikan terlebih dahulu dan menyalin rumus dalam kisaran jumlah baris yang diperlukan. Jumlah langkah sebelum menemukan root ditentukan oleh formula: +1 (1), di mana [x] memiliki seluruh bagian dari angka X, T - akurasi yang ditentukan.

Kesimpulannya, saya perhatikan bahwa contoh yang dipertimbangkan digunakan:

Pengoperasian concontine dari string yang menggabungkan beberapa baris menjadi satu (ditunjukkan oleh simbol ampersan &). Saat menggabungkan dua baris, string kedua ditambahkan langsung ke ujung baris pertama.
Fungsi lembar kerja dari kategori fungsi untuk bekerja dengan teks teks (teks). Fitur ini mengubah nilai pada teks dalam format numerik yang diberikan.

Tugas untuk Siswa:

Hitung root cosx \u003d x persamaan pada segmen dengan akurasi 0,001. Jumlah langkah untuk menentukan root untuk menghitung menggunakan rumus (1).

Menggunakan MS EXEL secara signifikan memperluas rentang tugas yang dapat digunakan dalam pembelajaran. Hal ini disebabkan oleh kemungkinan mentransmisikan operasi padat karya ke komputer, misalnya, dalam memecahkan persamaan dengan metode iterasi dan membagi segmen menjadi dua.

Literatur:

Informatika di sekolah / ed. Makarova N. V. - St. Petersburg: Peter Kom, 1999.
Simbol L.v. Memecahkan tugas bisnis di Microsoft Office - M.: ZAO "Publishing BINOM", 2001.
Shokolovich v.f. Pelatihan Teknologi Informasi. Informatika dan pendidikan. 1998. - №2.
Ignekova G.S. Aspek metodis pelatihan guru ilmu komputer. Informatika dan pendidikan. 1998. - №3.

Micrisoft Office Excel 2007 adalah program khusus Windows khusus yang memungkinkan Anda membuat tabel yang berbeda dengan data yang dimasukkan. Selain itu, program ini memungkinkan Anda untuk menyelesaikan persamaan.

Buka Excel 2007. Untuk solusi paling sederhana dari persamaan, gunakan fitur solusi solusi. Benar, dalam banyak paket kantor standar, superstruktur ini tidak diinstal. Untuk menginstal, membuka pengaturan Excel Office, yang terletak di sudut kanan bawah kotak dialog bawah pop-up. Di menu klik yang terbuka dalam urutan berikut: "Add-in" - "Pencarian Solusi" - "Pergi".

Setelah transisi, centang kotak di sebelah item "Solution Search" dan klik OK.

Kemudian Excel akan mengkonfigurasi program.

Kemudian, untuk memecahkan persamaan, masukkan di bidang daun. Biarkan persamaan Anda dengan dua variabel: F (x1, x2) \u003d 3 × 1 + 2 × 2 - maks, dalam kasus pembatasan tertentu:

X1 - x2 ≥ -2
3 × 1 - 2 × 2 ≤ 6
2 × 1 + 3 × 2 ≥ 2
X2 ≤ 3.
X1 ≥ 0.
X2 ≤ 0.

Masukkan variabel X1 dan X2 di kolom A dan X2. Kemudian sorot bidang di mana nilai-nilai variabel berada. Kemudian di kolom dan masukkan fungsi f (x1, x2) \u003d. Dan di sebelah kanannya untuk menyoroti sel yang sama dengan nilai fungsi ini akan berada di sebelah kanan.

Kemudian di bidang merah, masukkan 3 × 1 + 2 × 2 persamaan itu sendiri. Perhatikan bahwa X1 adalah sel B1, dan X2 - sel B2.

Sekarang masukkan semua batasan di bidang.

Kemudian pergi ke bagian "Pencarian Keputusan" (folder data). Temukan bidang "Instal Sel Target" di mana Anda perlu meletakkan sel merah. Berlawanan "\u003d" Kami menulis nilai maksimum.
Di bidang "Cell Changing Cell", tambahkan sel biru - X1, X2.

Jika Anda telah memasukkan semua batasan, periksa dengan benar, lalu klik tombol "Jalankan". Dalam hal semua data dimasukkan dengan benar, program harus menghitung tidak diketahui. Dalam kasus kami x1 \u003d 4, h2 \u003d 3 dan f (x1, x2) \u003d 18. Persamaan diputuskan.

Kemampuan untuk menyelesaikan sistem persamaan seringkali dapat memperoleh manfaat tidak hanya dalam studi, tetapi juga dalam praktiknya. Pada saat yang sama, tidak setiap pengguna PC tahu bahwa pengasingan memiliki varian solusi persamaan linear sendiri. Mari cari tahu bagaimana menggunakan toolkit dari prosesor tabel ini untuk melakukan tugas ini dengan berbagai cara.

Metode 1: Metode Matrix

Cara paling umum untuk menyelesaikan sistem Alat Persamaan Linear Excel adalah penggunaan metode matriks. Ini terdiri dalam membangun matriks koefisien ekspresi, dan kemudian dalam membuat matriks kembali. Mari kita coba menggunakan metode ini untuk menyelesaikan sistem persamaan berikut:

14x1.+2x2.+8x4.=218
7x1.-3x2.+5x3.+12x4.=213
5x1.+x2.-2x3.+4x4.=83
6x1.+2x2.+x3.-3x4.=21

Isi angka matriks yang merupakan koefisien persamaan. Angka-angka ini harus ditempatkan secara berurutan dalam rangka, dengan mempertimbangkan lokasi setiap root yang sesuai dengannya. Jika salah satu akarnya tidak ada dalam beberapa ekspresi, maka dalam hal ini koefisien dianggap nol. Jika koefisien tidak ditetapkan dalam persamaan, tetapi root yang sesuai tersedia, diyakini bahwa koefisien sama 1 . Lihat tabel yang dihasilkan sebagai vektor SEBUAH..

Rekam nilai secara terpisah setelah tanda "sama". Kami menunjukkan dengan nama umum mereka seperti vektor Dgn B..

Sekarang, untuk menemukan akar persamaan, pertama-tama, kita perlu menemukan matriks terbalik yang ada. Untungnya, Excel memiliki operator khusus, yang dirancang untuk menyelesaikan tugas ini. Disebut O. Kuningan. Ini memiliki sintaks yang cukup sederhana:
Kuningan (array)

Argumen "Himpunan" - Ini, pada kenyataannya, alamat tabel sumber.

Jadi, kami mengalokasikan area sel-sel kosong pada lembar, yang berukuran sama dengan kisaran matriks asli. Klik pada tombol "Masukkan fungsi"terletak di dekat garis formula.

Berlari Fungsi Master.. Pergi ke kategori. "Matematis". Dalam daftar daftar yang diperkenalkan "Kuningan". Setelah ditemukan, kami menyorotnya dan mengklik tombol Baik.

Kuningan. Ini hanya satu bidang dalam hal jumlah argumen - "Himpunan". Di sini Anda perlu menentukan alamat tabel kami. Untuk keperluan ini, atur kursor di bidang ini. Kemudian pinkan tombol kiri mouse dan sorot area pada lembar di mana matriks berada. Seperti yang Anda lihat, data pada koordinat penempatan dimasukkan secara otomatis di bidang jendela. Setelah tugas ini selesai, akan paling jelas untuk mengklik tombol BaikTapi jangan terburu-buru. Faktanya adalah bahwa mengklik tombol ini setara dengan aplikasi perintah MEMASUKKAN. Tetapi ketika bekerja dengan array setelah menyelesaikan formula input, Anda tidak boleh mengklik tombol MEMASUKKAN, dan buat satu set kombinasi tombol Ctrl + Shift + Enter. Lakukan operasi ini.

Jadi, setelah itu, program ini membuat perhitungan dan pada output di area yang dipilih sebelumnya, kami memiliki matriks, terbalik ini.

Sekarang kita perlu mengalikan matriks terbalik pada matriks Dgn B.yang terdiri dari satu kolom nilai yang diatur setelah tanda "sama" Dalam ekspresi. Untuk multiplikasi tabel di Excel ada juga fungsi terpisah yang disebut Mumset.. Operator ini memiliki sintaks berikut:
Ibu (array1; array2)

Kami menyoroti kisaran, dalam kasus kami yang terdiri dari empat sel. Selanjutnya meluncurkan Master of Functions.Dengan menekan ikon "Masukkan fungsi".

Dalam kategori "Matematis"Dimulai Fungsi Master., Mengalokasikan nama "Mumznom" dan klik tombol Baik.

Jendela argumen fungsi diaktifkan. Mumset.. Di bidang "MASSIVE1" Kami memperkenalkan koordinat dari matriks terbalik kami. Untuk ini, sebagai yang terakhir kalinya, kami mengatur kursor di lapangan dan dengan tombol kiri mouse, kami menyorot kursor tabel yang sesuai. Tindakan serupa dilakukan untuk membuat koordinat di lapangan "MASSIVE2", hanya waktu ini mengalokasikan nilai kolom Dgn B.. Setelah tindakan di atas telah diadakan, jangan terburu-buru pada tombol lagi Baik atau kunci MEMASUKKAN, dan ketik kombinasi tombol Ctrl + Shift + Enter.

Setelah tindakan ini dalam sel yang dipilih sebelumnya, akar persamaan akan ditampilkan: X1., X2., X3. dan X4.. Mereka akan berlokasi konsisten. Dengan demikian, kita dapat mengatakan bahwa kita memecahkan sistem ini. Untuk memverifikasi kebenaran solusi, cukup untuk mengganti data ke sistem ekspresi asli alih-alih akar yang sesuai. Jika kesetaraan dihormati, ini berarti bahwa sistem persamaan yang diwakili dipecahkan dengan benar.

Metode 2: Pemilihan parameter

Metode kedua yang diketahui untuk memecahkan sistem persamaan di Excel adalah penerapan metode pemilihan parameter. Esensi dari metode ini adalah untuk menemukan dari yang sebaliknya. Artinya, berdasarkan hasilnya, kami menghasilkan argumen yang tidak diketahui. Mari kita gunakan persamaan persegi misalnya

Hasil ini juga dapat diperiksa dengan mengganti nilai ini dalam ekspresi terpecahkan alih-alih nilainya x..

Metode 3: Metode Cramer

Sekarang mari kita coba untuk menyelesaikan sistem persamaan oleh cramer. Misalnya, ambil sistem yang sama yang digunakan di Metode 1.:

14x1.+2x2.+8x4.=218
7x1.-3x2.+5x3.+12x4.=213
5x1.+x2.-2x3.+4x4.=83
6x1.+2x2.+x3.-3x4.=21

Seperti yang pertama, kami membuat matriks SEBUAH. dari koefisien persamaan dan tabel Dgn B. dari nilai-nilai yang berdiri setelah tanda "sama".

Selanjutnya, kami membuat empat meja lagi. Masing-masing dari mereka adalah salinan matriks SEBUAH., Hanya salinan ini secara bergantian satu kolom yang diganti dengan tabel. Dgn B.. Tabel pertama adalah kolom pertama, tabel kedua - yang kedua, dll.

Sekarang kita perlu menghitung faktor-faktor penentu untuk semua tabel ini. Sistem persamaan hanya akan memiliki solusi jika semua faktor penentu akan memiliki nilai selain nol. Untuk menghitung nilai ini di Excel, ada fungsi terpisah - Mopred.. Sintaks dari operator ini adalah sebagai berikut:
Mopred (array)

Dengan demikian, seperti fungsi Kuningan, satu-satunya argumen direferensikan ke tabel sedang diproses.

Jadi, kami menyorot sel di mana penentu matriks pertama akan menjadi output. Kemudian klik tombol ramah pada metode sebelumnya "Masukkan fungsi".

Sebuah jendela diaktifkan Fungsi Master.. Pergi ke kategori. "Matematis" dan di antara daftar operator mengalokasikan di sana "Mopred". Setelah itu kita klik tombol Baik.

Jendela argumen fungsi dimulai Mopred.. Seperti yang Anda lihat, hanya memiliki satu bidang - "Himpunan". Di bidang ini, masukkan alamat matriks yang dikonversi pertama. Untuk melakukan ini, atur kursor di lapangan, lalu pilih kisaran matriks. Setelah itu kita klik tombol Baik. Fungsi ini menampilkan hasilnya menjadi satu sel, bukan array, sehingga tidak perlu menggunakan perhitungan untuk menekan kombinasi tombol Ctrl + Shift + Enter.

Fungsi ini menghitung hasilnya dan menampilkannya ke dalam sel yang dipilih sebelumnya. Seperti yang kita lihat, dalam kasus kita penentu sama -740 , Yaitu, itu tidak sama dengan nol, yang cocok untuk kita.

Demikian pula, kami membuat perhitungan determinan untuk tiga tabel lainnya.

Pada tahap akhir, dihitung oleh penentu matriks primer. Prosedur ini terjadi pada algoritma yang sama. Seperti yang kita lihat, penentu tabel primer juga berbeda dari nol, yang berarti bahwa matriks dianggap nondegenerate, yaitu, sistem persamaan memiliki solusi.

Sekarang saatnya menemukan akar persamaan. Root persamaan akan sama dengan rasio penentu matriks yang dikonversi sesuai dengan penentu tabel primer. Dengan demikian, membagi secara bergantian keempat faktor penentu matriks yang dikonversi ke angka -148 Yang merupakan penentu dari tabel asli, kita akan mendapatkan empat akar. Seperti yang kita lihat, mereka sama dengan nilai-nilai 5 , 14 , 8 dan 15 . Dengan demikian, mereka persis bertepatan dengan akar yang kami temukan menggunakan matriks terbalik metode 1.yang mengkonfirmasi kebenaran larutan sistem persamaan.

Metode 4: Metode Gauss

Memecahkan sistem persamaan juga dapat diterapkan dengan metode GAUSS. Misalnya, kami mengambil sistem persamaan tiga yang lebih sederhana dari tiga yang tidak diketahui:

14x1.+2x2.+8x3.=110
7x1.-3x2.+5x3.=32
5x1.+x2.-2x3.=17

Lagi secara konsisten menulis koefisien di tabel SEBUAH., dan anggota gratis yang terletak setelah tanda "sama" - Di meja Dgn B.. Tetapi kali ini menyatukan kedua tabel, karena akan diperlukan untuk bekerja di masa depan. Kondisi penting adalah pada sel pertama matriks SEBUAH. Nilainya berbeda dari nol. Dalam kasus yang berlawanan, garis harus disusun ulang.

Salin string pertama dari dua matriks yang terhubung ke dalam baris di bawah ini (untuk kejelasan Anda dapat melewati satu baris). Di sel pertama, yang terletak di baris bahkan lebih rendah dari yang sebelumnya, kami memperkenalkan rumus berikut:
B8: E8- $ B $ 7: $ E $ 7 * (B8 / $ B $ 7)

Jika Anda telah menempatkan matriks dengan cara yang berbeda, maka alamat sel-sel rumus Anda akan memiliki nilai lain, tetapi Anda dapat menghitungnya dengan membandingkan dengan formula dan gambar yang diberikan di sini.

Setelah formula dimasukkan, sorot seluruh rentang sel dan klik kombinasi tombol Ctrl + Shift + Enter. Formula pemecah akan diterapkan ke baris dan akan diisi dengan nilai. Dengan demikian, kami membuat pengurangan dari baris kedua dari yang pertama dikalikan dengan rasio koefisien pertama dari dua ekspresi pertama sistem.

Setelah itu salin string yang dihasilkan dan masukkan ke dalam garis di bawah ini.

Pilih dua baris pertama setelah garis yang hilang. Klik pada tombol "Salinan"yang terletak di pita di tab "Utama".

Kami melewatkan string setelah entri terakhir pada lembar. Pilih sel pertama di baris berikutnya. Klik tombol kanan mouse. Dalam menu konteks yang dibuka, Anda akan membawa kursor ke item "Sisipan Khusus". Dalam daftar yang disebutkan di atas, pilih posisi "Nilai".

Ke baris berikutnya kami memperkenalkan rumus array. Itu memungkinkan untuk mengurangi dari baris ketiga dari grup data sebelumnya dari baris kedua dikalikan dengan rasio koefisien kedua dari string ketiga dan kedua. Dalam kasus kami, rumus akan memiliki bentuk berikut:
B13: E13- $ B $ 12: $ E $ 12 * (C13 / $ C $ 12)

Setelah memasukkan formula, pilih seluruh rentang dan oleskan kombinasi tombol Ctrl + Shift + Enter.

Sekarang Anda harus melakukan menjalankan mundur sesuai dengan metode Gauss. Kami melewati tiga baris dari entri terakhir. Di baris keempat kami memperkenalkan rumus array:
Dengan demikian, kami membagi string terbaru yang dihitung oleh kami pada koefisien ketiga. Setelah mereka mencetak rumus, kami menyorot seluruh baris dan klik tombol keyboard Ctrl + Shift + Enter.

Kami naik ke tali dan masukkan formula array berikut:
\u003d (B16: E16-B21: E21 * D16) / C16

Kami mengklik kombinasi tombol yang biasa untuk menerapkan rumus array.

Naikkan baris lain di atas. Dalam memperkenalkan rumus array dari bentuk berikut:
\u003d (B15: E15-B20: E20 * C15-B21: E21 * D15) / B15

Lagi mengalokasikan seluruh string dan gunakan pintasan keyboard Ctrl + Shift + Enter.

Sekarang kita melihat angka-angka yang ternyata di kolom terakhir dari blok terakhir dari garis yang dihitung oleh kita sebelumnya. Ini adalah angka-angka ini ( 4 , 7 dan 5 ) Akan menjadi akar dari sistem persamaan ini. Periksa mungkin, mengganti mereka alih-alih nilai. X1., X2. dan X3. Dalam ekspresi.

Seperti yang kita lihat, di Excele, sistem persamaan dapat diselesaikan dengan sejumlah cara, yang masing-masing memiliki kelebihan dan kekurangannya sendiri. Tetapi semua metode ini dapat dibagi menjadi dua kelompok besar: matriks dan menggunakan alat pemilihan parameter. Dalam beberapa kasus, metode matriks tidak selalu cocok untuk menyelesaikan masalah. Secara khusus, ketika penentu matriks adalah nol. Dalam kasus yang tersisa, pengguna itu sendiri menunggu untuk memutuskan opsi mana yang dianggap lebih nyaman bagi dirinya sendiri.

Add-In "Pencarian Solusi" Excel adalah alat analitik yang memungkinkan kami untuk dengan cepat dan mudah menentukan kapan dan hasil apa yang kami dapatkan dalam kondisi tertentu. Pilihan untuk menemukan solusi untuk solusi jauh lebih tinggi daripada dapat memberikan "pemilihan parameter" di Excel.

Perbedaan utama antara pencarian solusi dan pemilihan parameter:

Pemilihan beberapa parameter di Excel.
Kondisi overlay untuk membatasi perubahan sel yang mengandung nilai variabel.
Kemampuan untuk menggunakan dalam kasus di mana dapat ada banyak solusi satu tugas.

Contoh dan tugas untuk menemukan solusi di Excel

Pertimbangkan kemungkinan analitis superstruktur. Misalnya, Anda perlu mengakumulasi $ 14.000 selama 10 tahun. Untuk usia 10, Anda ingin menunda $ 1000 ke rekening deposit pada $ 1000 pada akun depositanya sebesar 5% per tahun. Di bawah ini pada gambar yang dibangun tabel di Excel, yang terlihat jelas dengan sisa akumulasi dana untuk setiap tahun. Seperti dapat dilihat dalam kondisi tersebut dari akun deposito dan kontribusi akumulasi, tujuannya tidak akan tercapai bahkan setelah 10 tahun. Saat menyelesaikan tugas ini, Anda dapat pergi dalam dua cara:

Temukan bank yang menawarkan tingkat bunga yang lebih tinggi pada deposito.
Tingkatkan ukuran kontribusi akumulatif tahunan ke rekening bank.

Kita dapat mengubah nilai variabel dalam sel B1 dan B2 untuk memilih kondisi yang diperlukan untuk mengumpulkan jumlah uang yang diperlukan.

Mendukung "Pencarian Solusi" - memungkinkan kita untuk secara bersamaan menggunakan 2 dari opsi ini untuk dengan cepat mensimulasikan kondisi yang paling optimal untuk mencapai tujuan. Untuk ini:

Seperti yang Anda lihat program sedikit meningkat suku bunga dan jumlah kontribusi tahunan.

Pembatasan parameter saat mencari solusi

Misalkan Anda pergi ke bank dengan meja ini, tetapi bank menolak untuk menaikkan suku bunga Anda. Dalam kasus seperti itu, kita perlu tahu berapa banyak kita harus meningkatkan jumlah investasi tahunan. Kita harus menetapkan batas pada sel dengan satu nilai variabel. Tetapi sebelum memulai, mengubah nilai dalam sel-sel variabel ke awal: dalam B1 sebesar 5%, dan dalam B2 pada -1000 $. Dan sekarang kami melakukan hal berikut.

Bagian penting dari tugas-tugas yang dipecahkan menggunakan spreadsheet menunjukkan bahwa untuk mendeteksi hasil yang diinginkan, pengguna sudah memiliki setidaknya beberapa sumber data. Namun, Exsel 2010 memiliki alat yang diperlukan dengan mana Anda dapat menyelesaikan tugas ini sebaliknya - untuk memilih data yang diperlukan untuk mendapatkan hasil yang diperlukan.

"Pencarian Solusi" dan merupakan salah satu alat ini yang nyaman untuk "Tugas Pengoptimalan". Dan jika sebelumnya Anda belum harus menggunakannya, sekarang saatnya untuk memperbaikinya.

Jadi - mulailah dengan pemasangan superstruktur ini (karena itu tidak akan muncul secara independen). Untungnya, sekarang dimungkinkan untuk melakukannya cukup dan dengan cepat - buka menu "layanan", dan sudah di dalamnya "superstruktur"

Ini akan tetap hanya di kolom "Manajemen", tentukan "Excel Add-in", dan setelah menekan tombol "Pergi".

Setelah tindakan sederhana ini, tombol aktivasi aktivasi "solusi" akan ditampilkan dalam data. Seperti yang ditunjukkan pada gambar

Mari kita lihat bagaimana solusi di Excel 2010 digunakan dengan benar, pada beberapa contoh sederhana.

Contoh yang pertama .

Misalkan Anda mengambil pos kepala departemen produksi besar dan Anda perlu mendistribusikan penghargaan dengan benar kepada karyawan. Misalkan jumlah total penghargaan adalah 100.000 rubel, dan perlu bahwa premi sebanding dengan gaji.

Yaitu, sekarang kita perlu memilih koefisien proporsionalitas yang tepat untuk menentukan ukuran penghargaan relatif terhadap gaji.

Pertama-tama, perlu untuk dengan cepat menyusun (jika belum) tabel di mana formula sumber dan data akan disimpan, sesuai dengan yang akan dimungkinkan untuk mendapatkan hasil yang diinginkan. Bagi kami, hasil ini adalah jumlah total hadiah. Dan sekarang perhatian adalah target C8 sel harus dengan bantuan formula yang terkait dengan sel variabel yang diinginkan di bawah alamat E2. Ini penting. Dalam contoh, kami mengaitkan mereka menggunakan rumus perantara, yang bertanggung jawab untuk menghitung penghargaan ke setiap karyawan (C2: C7).

Sekarang Anda dapat mengaktifkan "Pencarian Keputusan". Sebuah jendela baru terbuka di mana kita perlu menentukan parameter yang diperlukan.

Dibawah " 1 »Ini ditunjukkan oleh sel target kami. Itu hanya bisa satu.

« 2 "- Ini adalah kemungkinan opsi optimasi. Anda dapat memilih nilai "maksimum", "minimum" atau "spesifik" yang mungkin. Dan jika Anda perlu secara khusus, itu harus ditentukan dalam grafik yang sesuai.

« 3 »- Sel yang dapat diubah bisa agak (seluruh rentang atau alamat yang ditentukan secara terpisah). Lagi pula, Excel akan bekerja dengan mereka, memutar opsi sehingga nilai yang ditentukan dalam sel target.

« 4 "- Jika Anda perlu mengatur batasan, Anda harus menggunakan tombol" Tambah ", tetapi kami akan melihat sedikit nanti.

« 5 "- Tombol transisi ke komputasi interaktif berdasarkan program yang kami tentukan.

Tapi sekarang mari kita kembali ke kemampuan untuk mengubah tugas kita, menggunakan tombol Add. Tahap ini agak bertanggung jawab (tidak kurang dari konstruksi formula), karena itu adalah pembatasan yang memungkinkan untuk mendapatkan hasil keluaran yang benar. Semuanya dilakukan secepat mungkin di sini, sehingga Anda dapat meminta mereka tidak hanya untuk seluruh jangkauan segera, tetapi juga untuk sel-sel tertentu.

Untuk melakukan ini, Anda dapat menggunakan sejumlah tanda-tanda tertentu (dan pengguna familiar 2010) "\u003d", "\u003e \u003d", "<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

Tetapi dalam contoh kami, pembatasan hanya bisa satu - koefisien positif. Anda dapat mengaturnya, tentu saja, dalam beberapa cara - baik menggunakan "Tambah" (apa yang disebut "secara eksplisit menentukan batas"), atau cukup perhatikan fungsi akting "membuat variabel tanpa batasan non-negatif." Ini dapat dilakukan dalam add-in "Pencarian Solusi" dengan mengklik tombol "Parameter".

Ngomong-ngomong, setelah mengkonfirmasi parameter dan menjalankan program (tombol "Run"), Anda dapat melihat hasilnya di tabel. Kemudian program akan menunjukkan jendela hasil pencarian.

Jika hasil yang diperlihatkan sepenuhnya cocok untuk Anda, maka itu hanya akan dibiarkan mengkonfirmasi lagi (tombol "OK") yang memperbaiki hasil di tabel Anda. Jika sesuatu dalam perhitungan tidak cocok untuk Anda, maka Anda perlu membatalkan hasilnya (tombol "Batal"), kembali ke keadaan sebelumnya dari tabel kami dan perbaiki kesalahan.

Solusi tugas yang benar dari contoh harus menghasilkan ini

Ini sangat penting - untuk mendapatkan hasil yang benar, bahkan dengan perubahan sedikit pun dalam data sumber, perlu untuk memulai kembali "pencarian solusi".

Untuk lebih rinci tentang bagaimana program ini valid, mari kita lihat contoh lain.

Misalkan Anda adalah pemilik perusahaan furnitur besar dan perlu untuk membangun produksi sedemikian rupa untuk mendapatkan keuntungan setinggi mungkin. Anda hanya memproduksi rak buku, sementara hanya dua model - "A" dan "B", produksi yang terbatas secara eksklusif dengan keberadaan (atau tidak adanya) dewan berkualitas tinggi, serta waktu mesin (permesinan pada mesin).

Model "A" membutuhkan 3 m 3 papan, dan model "B" adalah 1 m 3 lebih (yaitu - 4). Dari pemasok Anda, Anda menerima maksimal 1.700 m 3 papan. Pada saat yang sama, model "A" dibuat dalam 12 menit pengoperasian mesin, dan "B" - dalam 30 menit. Hanya seminggu, mesin dapat bekerja tidak lebih dari 160 jam.

Pertanyaannya adalah - berapa banyak produk (dan model mana) yang harus menghasilkan perusahaan selama seminggu untuk mendapatkan keuntungan setinggi mungkin jika rak "A" memberi 60 laba rubel, dan "dalam" - 120?

Karena urutan tindakan diketahui, kami mulai membuat tabel dengan data dan formula yang Anda butuhkan. Lokasi sel, seperti sebelumnya, Anda dapat mengatur atas kebijaksanaan Anda. Atau gunakan

Dengan cara yang nyaman, kami meluncurkan "Pencarian Solusi" kami, masukkan data yang memproduksi.

Jadi, pertimbangkan apa yang kita miliki. Sel target F7 mengandung formula yang akan menghitung laba. Parameter optimisasi diatur ke maksimum. Di antara sel yang berubah, kami memiliki "F3: G3". Batasan - Semua nilai yang terdeteksi harus bilangan bulat, non-negatif, jumlah total waktu mesin bekas tidak melebihi 160 (Sel D9 kami), jumlah bahan baku tidak melebihi 1700 (sel D8).

Tentu saja, dalam hal ini, dimungkinkan untuk tidak menentukan alamat sel, tetapi secara langsung untuk mendaftarkan nilai digital yang diperlukan, tetapi jika Anda menggunakan alamat, perubahan dalam batasan dapat dilakukan di tabel, yang akan membantu mengandalkan untung dari perusahaan ini di masa depan, ketika mengubah data sumber.

Kami mengaktifkan program, dan itu menyiapkan solusinya.

Namun, ini bukan satu-satunya solusi dan Anda mungkin melompati hasil lain. Ini dapat terjadi bahkan jika semua data diindikasikan benar dan tidak ada kesalahan dalam rumus.

Iya. Itu bisa terjadi bahkan jika kami mengatakan program untuk mencari seluruh jumlah. Dan jika tiba-tiba terjadi, perlu untuk hanya melakukan konfigurasi tambahan dari "Cari Solusi". Buka jendela "Cari untuk Solusi" dan masukkan "Parameter".

Parameter teratas kami bertanggung jawab atas akurasi. Apa yang kurang, semakin tinggi keakuratannya dan dalam kasus kami itu secara signifikan meningkatkan peluang untuk mendapatkan bilangan bulat. Parameter kedua ("abaikan pembatasan integer") dan memberikan jawaban atas pertanyaan tentang bagaimana kita bisa mendapatkan jawaban sedemikian rupa sehingga integer yang diindikasikan dalam kueri. "Pencarian Solusi" hanya mengabaikan pembatasan ini karena fakta bahwa ia diberitahu oleh pengaturan yang diperluas.

Jadi sangat penuh perhatian di masa depan.

Ketiga dan mungkin contoh terakhir. Mari kita coba meminimalkan biaya perusahaan transportasi menggunakan pencarian solusi di Excel 2010.

Jadi, perusahaan konstruksi memberikan pesanan untuk pengangkutan pasir, yang diambil dari 3 pemasok (tambang). Itu harus dikirim ke 5 konsumen yang berbeda (yaitu situs konstruksi). Biaya pengiriman sudah termasuk dalam biaya objek, sehingga tugas kami adalah untuk memastikan pengiriman kargo ke lokasi konstruksi dengan biaya minimal.

Kami memiliki - pasokan pasir dalam karir, kebutuhan untuk situs konstruksi di pasir, biaya transportasi "pemasok-konsumen".

Penting untuk menemukan skema untuk pengiriman optimal (di mana dan dari mana), di mana total biaya transportasi akan minimal.

Sel-sel abu-abu dari tabel kami berisi rumus jumlah pada kolom dan string, dan sel target adalah rumus untuk penghitungan biaya pengiriman. Jalankan "Pencarian Solusi" kami dan buat pengaturan yang diperlukan

Setelah itu, lanjutkan untuk menemukan solusi tugas ini

Namun, kami tidak akan lupa bahwa tugas transportasi yang cukup sering dapat diperumit oleh beberapa pembatas tambahan. Misalkan komplikasi muncul di jalan dan sekarang dari karir 2 secara teknis tidak mungkin untuk mengirimkan kargo ke situs konstruksi 3. Untuk memperhitungkan ini, Anda hanya perlu menambahkan batas tambahan "$ D $ 13 \u003d 0". Dan jika Anda memulai program sekarang, maka hasilnya akan berbeda

Akhirnya, tetap mengatakan hanya tentang memilih metode keputusan. Dan jika tugasnya sangat sulit, kemungkinan besar untuk mendapatkan hasil yang diperlukan, Anda kemungkinan besar akan memilih metode solusi yang diperlukan.