Menggunakan fungsi boolean sebagai contoh. Fitur Microsoft Excel: Menemukan Solusi

Memecahkan persamaan dan sistem nonlinier "

Objektif: Mengeksplorasi kemampuan Ms Excel 2007 dalam memecahkan persamaan dan sistem nonlinier. Perolehan keterampilan dalam memecahkan persamaan nonlinier dan sistem menggunakan paket.

Latihan 1. Temukan akar dari polinomial x 3 - 0,01x 2 - 0,7044x + 0,139104 \u003d 0.

Pertama, mari selesaikan persamaan secara grafis. Diketahui bahwa solusi grafis dari persamaan f (x) \u003d 0 adalah titik potong dari grafik fungsi f (x) dengan absis, yaitu. nilai x seperti itu di mana fungsi tersebut menghilang.

Mari kita tabulasi polinomial kita pada interval dari -1 hingga 1 dengan langkah 0,2. Hasil perhitungan ditunjukkan pada Gambar., Dimana rumus dimasukkan ke dalam sel B2: \u003d A2 ^ 3 - 0.01 * A2 ^ 2 - 0.7044 * A2 + 0.139104. Grafik tersebut menunjukkan bahwa fungsi tersebut melintasi sumbu Ox tiga kali, dan karena polinomial derajat ketiga tidak lebih dari tiga akar nyata, solusi grafis untuk masalah tersebut telah ditemukan. Dengan kata lain, akarnya terlokalisasi, mis. interval tempat akar polinomial ini ditentukan: [-1, -0,8], dan.

Sekarang Anda dapat menemukan akar polinomial dengan pendekatan yang berurutan menggunakan perintah Data → Bekerja dengan data → Analisis Bagaimana-Jika → Pemilihan parameter.

Setelah memasukkan perkiraan awal dan nilai fungsi, Anda dapat beralih ke perintah Data → Bekerja dengan data → Analisis Bagaimana-Jika → Pemilihan parameter dan isi dialog sebagai berikut.

Di lapangan Diatur dalam selreferensi diberikan ke sel tempat rumus dimasukkan yang menghitung nilai ruas kiri persamaan (persamaan harus ditulis sehingga ruas kanannya tidak berisi variabel). Di lapangan Nilai kami memasukkan sisi kanan persamaan, dan di lapangan Mengubah nilai sel referensi ke sel yang dialokasikan untuk variabel diberikan. Perhatikan bahwa memasukkan referensi sel di bidang dialog Pemilihan parameter lebih mudah bukan dari keyboard, tetapi dengan mengklik sel yang sesuai.

Setelah menekan tombol OK, kotak dialog Hasil pemilihan parameter akan muncul dengan pesan tentang penyelesaian pencarian solusi yang berhasil, nilai perkiraan root akan ditempatkan di sel A14.

Temukan dua akar yang tersisa dengan cara yang sama. Hasil perhitungan akan ditempatkan di sel A15 dan A16.

Tugas 2. Memecahkan persamaan e x - (2x - 1) 2 = 0.

Mari lokalkan akar persamaan nonlinier.

Untuk melakukan ini, kami merepresentasikannya dalam bentuk f (x) \u003d g (x), mis. e x \u003d (2x - 1) 2 atau f (x) \u003d e x, g (x) \u003d (2x - 1) 2, dan selesaikan secara grafis.

Solusi grafis untuk persamaan f (x) \u003d g (x) akan menjadi titik potong dari garis f (x) dan g (x).

Mari kita buat grafik f (x) dan g (x). Untuk melakukan ini, masukkan nilai argumen ke dalam kisaran A3: A18. Di sel B3, masukkan rumus untuk menghitung nilai fungsi f (x): \u003d EXP (A3), dan di C3 untuk menghitung g (x): \u003d (2 * A3-1) ^ 2.

Hasil perhitungan dan plot f (x) dan g (x):

Grafik tersebut menunjukkan bahwa garis f (x) dan g (x) berpotongan dua kali, yaitu persamaan ini memiliki dua solusi. Salah satunya sepele dan bisa dihitung dengan tepat:

Untuk yang kedua dapat menentukan interval isolasi akar: 1.5< x < 2.

Sekarang Anda dapat menemukan akar persamaan pada suatu segmen dengan metode pendekatan berurutan.

Mari masukkan pendekatan awal ke dalam sel H17 \u003d 1.5, dan persamaan itu sendiri, dengan mengacu pada pendekatan awal, ke dalam sel I17 \u003d EXP (H17) - (2 * H17-1) ^ 2.

dan isi kotak dialog Pemilihan parameter.

Hasil pencarian solusi akan ditampilkan di sel H17.

Tugas3 . Pecahkan sistem persamaan:

Sebelum menggunakan metode yang dijelaskan di atas untuk menyelesaikan sistem persamaan, kita akan menemukan solusi grafis untuk sistem ini. Perhatikan bahwa kedua persamaan sistem diberikan secara implisit dan untuk membuat grafik yang sesuai dengan persamaan ini, persamaan yang diberikan harus diselesaikan dengan memperhatikan variabel y.

Untuk persamaan pertama dari sistem, kami memiliki:

Mari kita cari tahu ODV dari fungsi yang dihasilkan:

Persamaan kedua dari sistem ini menggambarkan lingkaran.

Fragmen lembar kerja MS Excel dengan rumus yang harus dimasukkan ke dalam sel untuk membangun garis yang dijelaskan oleh persamaan sistem. Titik potong dari garis yang digambarkan adalah solusi grafis untuk sistem persamaan nonlinier.

Tidak sulit untuk melihat bahwa sistem yang diberikan memiliki dua solusi. Oleh karena itu, prosedur untuk mencari solusi sistem harus dilakukan dua kali, setelah sebelumnya menentukan interval isolasi akar sepanjang sumbu Ox dan Oy. Dalam kasus kami, akar pertama terletak pada interval (-0,5; 0) x dan (0,5; 1) y, dan yang kedua adalah (0; 0,5) x dan (-0,5; -1) y. Kami akan melanjutkan sebagai berikut. Mari kita perkenalkan nilai awal dari variabel x dan y, rumus yang mewakili persamaan sistem dan fungsi tujuan.

Sekarang kita akan menggunakan perintah Data → Analysis → Search for solutions dua kali, mengisi kotak dialog yang muncul.

Membandingkan solusi yang diperoleh dari sistem dengan solusi grafis, kami memastikan bahwa sistem diselesaikan dengan benar.

Penugasan swadaya

Latihan 1... Temukan akar polinomial

Tugas 2... Temukan solusi untuk persamaan nonlinier.

Tugas 3... Temukan solusi untuk sistem persamaan nonlinier.

Seperti yang sudah Anda ketahui, rumus dalam Microsoft Excel memungkinkan Anda menentukan nilai fungsi dengan argumennya. Namun, situasi mungkin muncul ketika nilai fungsinya diketahui, dan argumennya perlu ditemukan (yaitu, untuk menyelesaikan persamaan). Untuk mengatasi masalah tersebut, ada fungsi khusus Pencarian Tujuan .

Pencarian parameter.

Fungsi khusus Pencarian Tujuan memungkinkan Anda untuk menentukan parameter (argumen) dari suatu fungsi jika nilainya diketahui. Saat parameter dipilih, nilai sel yang berpengaruh (parameter) diubah hingga rumus yang bergantung pada sel ini mengembalikan nilai yang ditentukan.

Mari kita pertimbangkan prosedur untuk mencari parameter menggunakan contoh sederhana: selesaikan persamaan 10 * x - 10 / x \u003d 15 ... Di sini parameter (argumen) - x ... Biarkan itu menjadi sel A3 ... Mari masuk ke dalam sel ini nomor apa pun yang ada dalam lingkup definisi fungsi (dalam contoh kita, angka ini tidak boleh sama dengan nol). Nilai ini akan digunakan sebagai nilai awal. Biarlah 3 ... Kami memperkenalkan rumusnya \u003d 10 * A3-10 / A3 , yang dengannya nilai yang diperlukan harus diperoleh, ke dalam beberapa sel, misalnya, B3 ... Sekarang Anda dapat memulai fungsi pencarian parameter dengan memilih perintah Pencarian Tujuan di menu Alat ... Masukkan parameter pencarian:

Di lapangan Setel sel masukkan referensi ke sel yang berisi rumus yang Anda inginkan.
Masukkan hasil pencarian di lapangan Untuk menilai .
Di lapangan Dengan mengubah sel masukkan referensi ke sel yang berisi nilai yang akan dicocokkan.
Klik pada kuncinya baik .

Di akhir fungsi, sebuah jendela akan muncul di layar tempat hasil pencarian akan ditampilkan. Parameter yang ditemukan akan muncul di sel yang dicadangkan untuknya. Perhatikan fakta bahwa dalam contoh kita persamaan memiliki dua solusi, dan parameter hanya dipilih satu - ini karena parameter berubah hanya sampai nilai yang diperlukan dikembalikan. Argumen pertama yang ditemukan dengan cara ini dikembalikan kepada kami sebagai hasil penelusuran. Jika kita tentukan sebagai nilai awal dalam contoh kita -3 , maka solusi kedua dari persamaan tersebut akan ditemukan: -0,5 .

Agak sulit untuk menentukan dengan benar nilai awal yang paling cocok. Lebih sering kita dapat membuat beberapa asumsi tentang parameter yang diinginkan, misalnya parameter harus bilangan bulat (kemudian kita mendapatkan solusi pertama dari persamaan kita) atau non-positif (solusi kedua).

Tugas untuk menemukan parameter dengan kondisi batas yang ditentukan akan dibantu oleh add-in khusus Microsoft Excel Solver .

Cari solusi.

Add-in Microsoft Excel Solver tidak dipasang secara otomatis dalam instalasi biasa:

Di menu Alat pilih tim Tambahan ... Jika kotak dialog Tambahan tidak mengandung perintah Solver , tekan tombolnya Jelajahi dan tentukan drive dan folder yang berisi file tambahan tersebut Solver.xla (biasanya ini adalah foldernya Library \\ Solver ) atau jalankan program instalasi Microsoft Office jika tidak dapat menemukan file tersebut.
Di kotak dialog Tambahan centang kotaknya Solver .

Prosedur untuk menemukan solusi memungkinkan Anda untuk menemukan nilai optimal dari rumus yang terdapat dalam sel yang disebut target. Prosedur ini berfungsi dengan sekelompok sel yang terkait dengan rumus di sel target. Prosedur mengubah nilai dalam sel yang mempengaruhi hingga memperoleh hasil yang optimal berdasarkan rumus pada sel target. Untuk mempersempit kumpulan nilai, batasan diterapkan, yang mungkin memiliki tautan ke sel lain yang mempengaruhi. Anda juga dapat menggunakan pencarian solusi untuk menentukan nilai sel yang berpengaruh yang sesuai dengan ujung sel target, misalnya, jumlah sesi pelatihan yang memaksimalkan kinerja akademis.

Di kotak dialog Solver sama seperti di kotak dialog Pencarian Tujuan , Anda perlu menentukan sel target, nilainya, dan sel yang harus diubah untuk mencapai tujuan. Untuk mengatasi masalah pengoptimalan, sel target harus disetel sama dengan nilai maksimum atau minimum.

Jika Anda mengklik tombol tersebut Tebak Excel akan mencoba menemukan semua sel yang memengaruhi rumus itu sendiri.

Anda dapat menambahkan kondisi batas dengan mengklik tombol tersebut Menambahkan .

Dengan mengklik tombol tersebut Pilihan , Anda dapat mengubah kondisi untuk menemukan solusi: waktu maksimum untuk menemukan solusi, jumlah iterasi, keakuratan solusi, toleransi penyimpangan dari solusi optimal, metode ekstrapolasi (linier atau kuadrat), pengoptimalan algoritma, dll.

Mari kita kembali ke contoh sebelumnya: untuk mendapatkan solusi kedua (non-positif), cukup dengan menambahkan syarat batas A3 ... Seperti dalam pemilihan parameter, sebuah jendela akan muncul di layar di mana laporan hasil pencarian untuk solusi yang diperlukan akan ditampilkan. Solusinya sendiri akan ditampilkan di sel yang dimaksudkan untuknya (di dalam sel A3 nilainya ditampilkan -0.50 ).

Add-in Microsoft Excel Solver juga memungkinkan Anda untuk memecahkan sistem persamaan atau pertidaksamaan. Mari pertimbangkan sebuah contoh sederhana: mari kita coba menyelesaikan sistem persamaan
x + y \u003d 2
x - y \u003d 0

Salah satu fitur paling menarik di Microsoft Excel adalah Finding a Solution. Pada saat yang sama, perlu dicatat bahwa alat ini tidak dapat diklasifikasikan sebagai yang paling populer di kalangan pengguna di lampiran ini... Namun sia-sia. Bagaimanapun, fungsi ini, menggunakan data awal, dengan mencari, menemukan solusi paling optimal dari semua yang tersedia. Mari cari tahu cara menggunakan fitur Temukan Solusi di Microsoft Excel.

Anda dapat mencari dalam waktu lama pada rekaman tempat Cari solusi berada, tetapi Anda masih tidak dapat menemukan alat ini. Sederhananya, untuk mengaktifkan fungsi ini, Anda harus mengaktifkannya di pengaturan program.

Untuk mengaktifkan Cari solusi di Microsoft Excel 2010 dan versi yang lebih baru, buka tab "File". Untuk versi 2007, klik tombol Microsoft Office di sudut kiri atas jendela. Di jendela yang terbuka, buka bagian "Parameter".

Di jendela parameter, klik item "Add-ons". Setelah transisi, di bagian bawah jendela, di seberang parameter "Control", pilih nilai "Excel Add-in", dan klik tombol "Go".

Jendela dengan add-on terbuka. Kami memberi tanda centang di depan nama add-on yang kami butuhkan - "Cari solusi". Klik pada tombol "OK".

Setelah itu, tombol untuk memulai fungsi Cari solusi akan muncul di pita Excel di tab Data.

Mempersiapkan meja

Sekarang kita telah mengaktifkan fungsinya, mari kita lihat cara kerjanya. Cara termudah untuk membayangkannya adalah dengan contoh spesifik. Jadi, kami memiliki tabel gaji karyawan perusahaan. Kami perlu menghitung bonus untuk setiap karyawan, yang merupakan produk dari upah yang ditunjukkan dalam kolom terpisah dengan koefisien tertentu. Pada saat yang sama, jumlah total dana yang dialokasikan untuk premi adalah 30.000 rubel. Sel tempat jumlah ini berada memiliki nama sel target, karena tujuan kami adalah memilih data yang tepat untuk nomor ini.

Koefisien yang digunakan untuk menghitung besarnya premi harus kita hitung menggunakan fungsi Search for solutions. Sel tempatnya berada disebut sel yang diinginkan.

Sel target dan sel target harus terkait satu sama lain menggunakan rumus. Dalam kasus khusus kami, rumus terletak di sel target, dan memiliki bentuk berikut: "\u003d C10 * $ G $ 3", di mana $ G $ 3 adalah alamat absolut dari sel yang dicari, dan "C10" adalah gaji total dari mana bonus dihitung karyawan perusahaan.

Meluncurkan Alat Temukan Solusi

Setelah tabel disiapkan, pada tab "Data", klik tombol "Cari solusi", yang terletak di pita di kotak alat "Analisis".

Jendela parameter terbuka, di mana Anda perlu memasukkan data. Di bidang "Optimalkan fungsi tujuan", Anda harus memasukkan alamat sel target, tempat jumlah bonus total untuk semua karyawan akan ditempatkan. Ini dapat dilakukan baik dengan mengetikkan koordinat secara manual, atau dengan mengklik tombol yang terletak di sebelah kiri bidang entri data.

Setelah itu, jendela parameter akan diminimalkan, dan Anda dapat memilih sel tabel yang diinginkan. Kemudian, Anda perlu mengklik tombol yang sama lagi di sebelah kiri formulir dengan data yang dimasukkan untuk memperluas jendela parameter lagi.

Di bawah jendela dengan alamat sel target, Anda perlu mengatur parameter nilai yang akan ada di dalamnya. Ini bisa berupa nilai maksimum, minimum, atau tertentu. Dalam kasus kami, ini akan menjadi opsi terakhir. Oleh karena itu, kita taruh saklar pada posisi “Values”, dan di kolom sebelah kiri kita tulis angka 30.000. Seingat kita, angka ini, sesuai kondisi, adalah jumlah total bonus untuk semua karyawan perusahaan.

Di bawah ini adalah bidang "Mengubah sel variabel". Di sini Anda perlu menunjukkan alamat sel yang diinginkan, di mana, seperti yang kita ingat, koefisiennya berada, dengan mengalikan gaji pokok dengan jumlah bonus akan dihitung. Alamatnya dapat ditulis dengan cara yang sama seperti yang kita lakukan untuk sel target.

Di kolom "Menurut batasan", Anda dapat menyetel batasan tertentu untuk data, misalnya, membuat nilai utuh atau non-negatif. Untuk melakukan ini, klik tombol "Tambah".

Setelah itu, jendela untuk menambahkan batasan terbuka. Di bidang "Referensi ke sel", tulis alamat sel yang terkait dengan batasan yang diterapkan. Dalam kasus kami, ini adalah sel yang diinginkan dengan koefisien. Kemudian kami meletakkan tanda yang diperlukan: "kurang dari atau sama", "lebih besar dari atau sama", "sama", "bilangan bulat", "biner", dll. Dalam kasus kami, kami akan memilih tanda lebih besar dari atau sama dengan untuk membuat koefisien menjadi angka positif. Karenanya, di bidang "Restriction", tunjukkan angka 0. Jika kita ingin mengkonfigurasi batasan lain, klik tombol "Add". Jika tidak, klik tombol "OK" untuk menyimpan batasan yang dimasukkan.

Seperti yang Anda lihat, setelah itu, batasan muncul di bidang terkait dari jendela parameter pencarian solusi. Selain itu, Anda dapat membuat variabel menjadi non-negatif dengan mencentang kotak di samping parameter yang sesuai tepat di bawah. Disarankan agar parameter yang ditetapkan di sini tidak bertentangan dengan yang Anda tentukan dalam pembatasan, jika tidak, konflik dapat timbul.

Pengaturan tambahan dapat diatur dengan mengklik tombol "Parameter".

Di sini Anda dapat mengatur ketepatan batasan dan batasan solusi. Saat data yang diperlukan sudah dimasukkan, klik tombol "OK". Namun, untuk kasus kami, tidak perlu mengubah parameter ini.

Setelah semua pengaturan ditetapkan, klik tombol "Temukan solusi".

Selanjutnya, program Excel di dalam sel melakukan penghitungan yang diperlukan. Bersamaan dengan keluaran hasil, sebuah jendela terbuka di mana Anda dapat menyimpan solusi yang ditemukan, atau memulihkan nilai asli dengan memindahkan sakelar ke posisi yang sesuai. Terlepas dari opsi yang dipilih, dengan mencentang kotak "Kembali ke kotak dialog opsi", Anda dapat kembali ke pengaturan untuk mencari solusi. Setelah kotak centang dan sakelar disetel, klik tombol "OK".

Jika, karena alasan apa pun, hasil pencarian solusi tidak memuaskan Anda, atau program memberikan kesalahan saat menghitungnya, maka, dalam kasus ini, kami mengembalikan, seperti dijelaskan di atas, ke kotak dialog parameter. Kami sedang meninjau semua data yang dimasukkan, karena mungkin ada kesalahan di suatu tempat. Jika kesalahan tidak ditemukan, pergi ke parameter "Pilih metode solusi". Di sini Anda dapat memilih salah satu dari tiga metode kalkulasi: "Mencari solusi untuk masalah nonlinier dengan metode OPG", "Mencari solusi untuk masalah linier dengan metode simpleks", dan "Pencarian evolusioner untuk solusi". Secara default, metode pertama digunakan. Kami mencoba menyelesaikan masalah dengan memilih metode lain. Jika tidak berhasil, coba lagi menggunakan metode terakhir. Algoritma tindakannya sama dengan yang dijelaskan di atas.

Seperti yang Anda lihat, fungsi Temukan solusi adalah alat yang cukup menarik yang, jika digunakan dengan benar, dapat menghemat waktu pengguna secara signifikan dalam berbagai perhitungan. Sayangnya, tidak semua pengguna mengetahui keberadaannya, apalagi dapat bekerja dengan baik dengan add-on ini. Dalam beberapa hal, alat ini menyerupai fungsinya , tetapi pada saat yang sama, ada perbedaan yang signifikan dengannya.

Jika masuk sel Excel rumus diperkenalkan yang berisi tautan ke sel yang sama (mungkin tidak secara langsung, tetapi tidak langsung - melalui rantai tautan lain), lalu mereka mengatakan bahwa ada referensi siklik (siklus). Dalam praktiknya, referensi siklik digunakan saat mengimplementasikan proses iteratif, dihitung dengan relasi pengulangan. Seperti biasa mode Excel mendeteksi siklus dan mengeluarkan pesan tentang situasi yang muncul, yang membutuhkan penghapusannya. Excel tidak dapat melakukan penghitungan karena referensi melingkar menghasilkan jumlah penghitungan yang tak terbatas. Ada dua cara keluar dari situasi ini: untuk menghilangkan referensi melingkar atau untuk memungkinkan penghitungan menggunakan rumus dengan referensi melingkar (dalam kasus terakhir, jumlah pengulangan siklus harus terbatas).

Pertimbangkan masalah mencari akar persamaan dengan metode Newton menggunakan referensi siklik. Ambil persamaan kuadrat sebagai contoh: x 2 - 5x + 6 \u003d 0, representasi grafis yang ditampilkan di. Anda dapat menemukan akar persamaan ini (dan yang lainnya) hanya dengan menggunakan satu sel Excel.

Untuk mengaktifkan komputasi siklik di menu alat / tab Opsi / Perhitungan nyalakan kotak centang Pengulangan, jika perlu, ubah jumlah pengulangan loop di lapangan Batasi jumlah iterasi dan ketepatan perhitungan di lapangan Kesalahan relatif (secara default nilainya masing-masing 100 dan 0,0001). Selain pengaturan ini, kami memilih opsi untuk menghitung: secara otomatis atau secara manual... Kapan otomatis perhitungan Excel segera memberikan hasil akhir, saat menghitung secara manual, Anda dapat mengamati hasil dari setiap iterasi.

Angka: 8. Grafik fungsi

Mari pilih sel sewenang-wenang, beri nama baru, katakan - X, dan masukkan ke dalamnya rumus berulang yang menetapkan kalkulasi dengan metode Newton:

dimana F dan F1 set, masing-masing, ekspresi untuk menghitung nilai fungsi dan turunannya. Untuk persamaan kuadrat kami, setelah memasukkan rumus, nilainya akan muncul di sel 2 sesuai dengan salah satu akar persamaan (). Dalam kasus kami, perkiraan awal tidak ditentukan, proses komputasi iteratif dimulai dengan nilai default yang disimpan dalam sel. X dan sama dengan nol. Bagaimana Anda mendapatkan root kedua? Ini biasanya dapat dilakukan dengan mengubah tebakan awal. Anda dapat memecahkan masalah menentukan pengaturan awal di setiap kasus dengan cara yang berbeda. Kami akan mendemonstrasikan satu teknik berdasarkan penggunaan fungsi IF. Untuk meningkatkan kejelasan penghitungan, nama yang berarti () telah ditetapkan ke sel.

2.2. Pemilihan parameter

Jika Anda mengetahui hasil rumus yang diinginkan, tetapi tidak mengetahui nilai yang diperlukan untuk mendapatkan hasil ini, Anda dapat menggunakan alat Pemilihan parameterdengan memilih perintah Pemilihan parameter di menu Layanan... Saat Anda memilih opsi, Excel mengubah nilai dalam satu sel tertentu hingga penghitungan dengan rumus yang merujuk ke sel tersebut menghasilkan hasil yang diinginkan.

Ambil contoh persamaan kuadrat yang sama x 2 -5x + 6 \u003d 0... Untuk mencari akar persamaan, lakukan hal berikut:

Excel menggunakan proses berulang (round-robin) untuk memilih parameter. Jumlah iterasi dan presisi ditetapkan di menu Tab Alat / Opsi / Perhitungan... Jika Excel melakukan tugas pemilihan parameter yang kompleks, Anda bisa mengklik Berhenti sebentar di kotak dialog Hasil pemilihan parameter dan hentikan penghitungan, lalu tekan tombol Langkahuntuk mengulang lagi dan melihat hasilnya. Saat memecahkan masalah dalam mode langkah demi langkah, sebuah tombol akan muncul Terus - untuk kembali ke mode pemilihan parameter normal.

Mari kembali ke contoh. Sekali lagi muncul pertanyaan: bagaimana cara mendapatkan root kedua? Seperti dalam kasus sebelumnya, perlu untuk menetapkan perkiraan awal. Itu bisa dilakukan seperti ini ():

sebuah

Angka: 11. Menemukan root kedua

Namun, semua ini dapat dilakukan dengan cara yang lebih sederhana. Untuk mencari akar kedua, cukup dengan meletakkan konstanta di sel C2 sebagai aproksimasi awal () 5 dan setelah itu mulailah prosesnya Pemilihan parameter.

2.3. Menemukan solusi

Tim Pemilihan parameter nyaman untuk memecahkan masalah dalam menemukan nilai target tertentu, bergantung pada satu parameter yang tidak diketahui. Untuk tugas yang lebih kompleks, gunakan perintah Menemukan solusi (Solver), yang diakses melalui item menu Layanan / Pencarian Solusi.

Tugas yang bisa diselesaikan dengan Mencari solusi, dalam pengaturan umum dirumuskan sebagai berikut:

Mencari:
x 1, x 2, ..., x n
seperti yang:
F (x 1, x 2, ..., x n)\u003e (Maks; Min; \u003d Nilai)
dengan batasan:
G (x 1, x 2, ..., x n)\u003e (Ј Nilai; i Nilai; \u003d Nilai)

Variabel yang dicari - sel pekerja lembar kerja Excel - disebut sel yang diatur. Fungsi objektif F (x 1, x 2, ..., x n)terkadang disebut hanya sebagai tujuan, harus ditentukan sebagai rumus dalam sel di lembar kerja. Rumus ini dapat berisi fungsi yang ditentukan pengguna dan harus bergantung pada (referensi) sel yang dapat disesuaikan. Pada saat menetapkan masalah, ditentukan apa yang harus dilakukan dengan fungsi tujuan. Anda dapat memilih salah satu opsi:

temukan fungsi tujuan maksimal F (x 1, x 2, ..., x n);
temukan minimum dari fungsi tujuan F (x 1, x 2, ..., x n);
mencapai itu fungsi tujuan F (x 1, x 2, ..., x n) memiliki nilai tetap: F (x 1, x 2, ..., x n) \u003d a.

Fungsi G (x 1, x 2, ..., x n) disebut kendala. Mereka dapat ditentukan baik dalam bentuk persamaan dan ketidaksetaraan. Pembatasan tambahan dapat diberlakukan pada sel yang diatur: nonnegativitas dan / atau bilangan bulat, kemudian solusi yang dicari dicari dalam kisaran bilangan bulat positif dan / atau bilangan bulat.

Rumusan ini mencakup masalah optimasi yang paling luas, termasuk solusi dari berbagai persamaan dan sistem persamaan, masalah pemrograman linier dan nonlinier. Tugas-tugas semacam itu biasanya lebih mudah dirumuskan daripada diselesaikan. Dan kemudian, untuk mengatasi masalah optimasi tertentu, diperlukan metode yang dirancang khusus. Solver memiliki peralatan yang ampuh untuk memecahkan masalah seperti itu: metode gradien umum, metode simpleks, metode cabang dan metode terikat.

Di atas, untuk mencari akar persamaan kuadrat, metode Newton diterapkan (bagian 1.4) menggunakan referensi siklik () dan alat Pemilihan parameter (). Mari kita lihat bagaimana cara menggunakannya Menemukan solusi menggunakan contoh persamaan kuadrat yang sama.

Setelah membuka dialog Menemukan solusi () Anda perlu melakukan hal berikut:

di lapangan Tetapkan sel target masukkan alamat sel yang berisi rumus untuk menghitung nilai fungsi yang dioptimalkan, dalam contoh kita, sel targetnya adalah C4, dan rumus di dalamnya adalah: \u003d C3 ^ 2 - 5 * C3 + 6;
untuk memaksimalkan nilai sel target, setel tombol radio nilai maksimum ke posisi 8, saklar digunakan untuk memperkecil nilai minimum, dalam kasus kami, atur sakelar ke nilai dan masukkan nilainya 0 ;
di lapangan Mengubah sel masukkan alamat sel yang akan diubah, mis. argumen dari fungsi tujuan (C3), memisahkannya dengan ";" (atau dengan mengklik dengan mouse saat tombol ditekan Сtrl pada sel yang sesuai), untuk secara otomatis mencari semua sel yang memengaruhi solusi, gunakan tombol Tebak;
di lapangan Batasan menggunakan tombol Tambahkan masukkan semua batasan yang harus dipenuhi oleh hasil pencarian: sebagai contoh, Anda tidak perlu mengatur batasan;
untuk memulai proses mencari solusi, tekan tombol Menjalankan.

Untuk menyimpan solusi yang diperoleh, gunakan sakelar Simpan solusi yang ditemukan di jendela dialog terbuka Hasil pencarian solusi... Kemudian lembar kerja akan berbentuk seperti yang ditunjukkan pada. Solusi yang dihasilkan bergantung pada pilihan pendekatan awal, yang ditentukan dalam sel C4 (argumen fungsi). Jika, sebagai perkiraan awal di sel C4, masukkan nilai yang sama dengan 1,0 , lalu gunakan Mencari solusi temukan akar kedua yang sama dengan 2,0 .

Pilihan yang mengatur pekerjaan Mencari solusidiatur di jendela Pilihan (jendela muncul jika Anda mengklik tombol Pilihan jendela Menemukan solusi), pengikut ():

Waktu maksimal - membatasi waktu yang dialokasikan untuk proses mencari solusi (defaultnya 100 detik, yang cukup untuk masalah dengan sekitar 10 batasan, jika masalah berdimensi besar, maka waktu harus ditambah).
Batasi jumlah iterasi - cara lain untuk membatasi waktu pencarian dengan mengatur jumlah iterasi maksimum. Standarnya adalah 100, dan, paling sering, jika solusi tidak diperoleh dalam 100 iterasi, maka dengan peningkatan jumlahnya (di bidang Anda dapat memasukkan waktu tidak melebihi 32767 detik) kemungkinan mendapatkan hasil kecil. Lebih baik coba ubah tebakan awal dan mulai lagi proses pencarian.
Kesalahan relatif - menentukan ketepatan sel yang cocok dengan nilai target atau perkiraan ke batas yang ditentukan (pecahan desimal dari 0 hingga 1).
Toleransi - set di% hanya untuk masalah dengan batasan integer. Menemukan solusi dalam masalah seperti itu, pertama-tama ia menemukan solusi non-bilangan bulat optimal, dan kemudian mencoba menemukan titik bilangan bulat terdekat, solusi yang akan berbeda dari solusi optimal tidak lebih dari jumlah persen yang ditunjukkan oleh parameter ini.
Konvergensi - ketika perubahan relatif dalam nilai dalam sel target untuk lima iterasi terakhir menjadi kurang dari angka (pecahan dari interval 0 hingga 1) yang ditentukan dalam parameter ini, pencarian berhenti.
Model linier - kotak centang ini harus diaktifkan ketika fungsi tujuan dan batasan adalah fungsi linier. Ini mempercepat proses menemukan solusi.
Nilai non-negatif - tanda ini dapat digunakan untuk menetapkan batasan pada variabel, yang akan memungkinkan pencarian solusi dalam rentang nilai positif tanpa menetapkan batasan khusus pada batas bawahnya.
Penskalaan otomatis - tanda ini harus diaktifkan ketika skala nilai variabel masukan dan fungsi tujuan dan batasan berbeda, mungkin berdasarkan urutan besarnya. Misalnya, variabel diatur dalam beberapa bagian, dan fungsi tujuan yang menentukan keuntungan maksimum diukur dalam miliaran rubel.
Tampilkan hasil iterasi - kotak centang ini memungkinkan Anda untuk mengaktifkan proses pencarian selangkah demi selangkah, yang menunjukkan hasil dari setiap iterasi di layar.
Estimasi - grup ini berfungsi untuk menunjukkan metode ekstrapolasi - linier atau kuadrat - digunakan untuk memperoleh estimasi awal nilai variabel dalam setiap pencarian satu dimensi. Linear berfungsi untuk menggunakan ekstrapolasi linier sepanjang vektor tangen. Kuadrat berfungsi untuk menggunakan ekstrapolasi kuadrat, yang memberikan hasil yang lebih baik saat menyelesaikan masalah nonlinier.
Perbedaan (turunan) - grup ini berfungsi untuk menunjukkan metode diferensiasi numerik, yang digunakan untuk menghitung turunan parsial dari fungsi target dan fungsi pembatas. Parameter Langsung ini digunakan di sebagian besar tugas yang tingkat perubahan batasannya relatif rendah. Parameter Pusat digunakan untuk fungsi dengan turunan terputus-putus. Metode ini membutuhkan lebih banyak perhitungan, tetapi penerapannya dapat dibenarkan jika sebuah pesan ditampilkan bahwa tidak mungkin untuk mendapatkan solusi yang lebih akurat.
Metode pencarian - berfungsi untuk memilih algoritma optimasi. Metode Newton telah dibahas sebelumnya. DI Metode gradien konjugasi lebih sedikit memori yang diminta, tetapi lebih banyak iterasi dilakukan daripada metode Newton. Metode ini sebaiknya digunakan jika tugas cukup besar dan perlu untuk menghemat memori, serta jika iterasi memberikan terlalu sedikit perbedaan dalam perkiraan yang berurutan.

saat menyimpan buku kerja Excel setelah menemukan solusi, semua nilai dimasukkan dalam kotak dialog Menemukan solusidisimpan bersama dengan data lembar kerja. Satu set nilai parameter dapat disimpan dengan setiap lembar kerja di buku kerja Mencari solusi;
jika dalam satu lembar kerja Excel perlu mempertimbangkan beberapa model pengoptimalan (misalnya, temukan maksimum dan minimum dari satu fungsi, atau nilai maksimum dari beberapa fungsi), maka akan lebih mudah untuk menyimpan model ini menggunakan tombol Pilihan / Simpan Model jendela Menemukan solusi... Rentang untuk model yang disimpan berisi informasi tentang sel target, tentang sel yang diubah, tentang setiap batasan dan semua nilai dialog Pilihan... Pemilihan model untuk memecahkan masalah pengoptimalan tertentu dilakukan dengan menggunakan tombol Parameter / Model beban dialog Menemukan solusi;
cara lain untuk menyimpan parameter pencarian adalah dengan menyimpannya sebagai skrip bernama. Untuk melakukan ini, klik tombolnya Simpan skrip kotak dialog Hasil pencarian untuk solusi.

Selain memasukkan nilai optimal ke dalam sel yang diubah Menemukan solusi memungkinkan Anda untuk menyajikan hasil dalam bentuk tiga laporan: hasil, Keberlanjutan dan Batasannya... Untuk menghasilkan satu atau beberapa laporan, Anda harus memilih namanya di jendela dialog Hasil pencarian solusi... Mari kita pertimbangkan masing-masing secara lebih rinci.

Angka: 15. Laporan keberlanjutan

) berisi informasi tentang seberapa sensitif sel target terhadap perubahan batasan dan variabel. Laporan ini memiliki dua bagian, satu untuk sel yang dapat diubah dan satu lagi untuk batasan. Kolom kanan di setiap bagian berisi informasi sensitivitas. Setiap sel dan batasan yang dapat diubah dicantumkan pada baris terpisah. Bagian untuk sel yang dapat dimodifikasi berisi nilai gradien yang dinormalisasi yang menunjukkan bagaimana seluruh sel merespons ketika nilai dalam sel yang dapat dimodifikasi yang sesuai ditingkatkan satu unit. Demikian pula, pengali Lagrange di bagian batasan menunjukkan bagaimana sel target merespons ketika nilai batasan yang sesuai ditingkatkan satu unit. Saat menggunakan batasan integer, Excel menampilkan pesan tersebut Laporan Ketahanan dan Batasan tidak berlaku untuk masalah dengan batasan integer... Jika kotak dialog Opsi pencarian solusi kotak centang dicentang Model linier, kemudian laporan keberlanjutan berisi beberapa kolom informasi tambahan.) berisi tiga tabel: yang pertama berisi informasi tentang fungsi tujuan sebelum memulai penghitungan, yang kedua berisi nilai-nilai variabel yang diinginkan yang diperoleh sebagai hasil penyelesaian masalah, dan yang ketiga berisi hasil solusi optimal untuk kendala-kendala tersebut. Laporan ini juga berisi informasi tentang parameter dari setiap batasan, seperti status dan perbedaan. Status dapat mengambil tiga status: terikat, tidak terikat, atau tidak terpenuhi. Nilai selisih adalah selisih antara nilai yang ditampilkan di sel batasan saat solusi diterima dan angka yang ditentukan di sisi kanan rumus batasan. Batasan terkait adalah kendala yang nilai selisihnya nol. Batasan tak terikat adalah batasan yang telah dipenuhi dengan nilai margin bukan nol.

Laporan batas berisi informasi tentang batas di mana nilai sel yang dimodifikasi bisa ditambah atau dikurangi tanpa melanggar batasan tugas. Untuk setiap sel yang Anda ubah, laporan ini berisi nilai optimal serta nilai terkecil yang dapat diterima sel tanpa melanggar batasan.