Apa itu Excel Solver?

2024 Pengarang: Abigail Brown | [email protected]. Terakhir diubah: 2023-12-17 06:51

Add-in Excel Solver melakukan pengoptimalan matematis. Ini biasanya digunakan untuk menyesuaikan model kompleks dengan data atau menemukan solusi berulang untuk masalah. Misalnya, Anda mungkin ingin menyesuaikan kurva melalui beberapa titik data, menggunakan persamaan. Solver dapat menemukan konstanta dalam persamaan yang paling sesuai dengan data. Aplikasi lain adalah di mana sulit untuk mengatur ulang model untuk membuat output yang diperlukan menjadi subjek persamaan.

Di Mana Solver di Excel?

Add-in Solver disertakan dengan Excel tetapi tidak selalu dimuat sebagai bagian dari penginstalan default. Untuk memeriksa apakah sudah dimuat, pilih tab DATA dan cari ikon Solver di bagian Analysis.

Jika Anda tidak dapat menemukan Solver di bawah tab DATA maka Anda perlu memuat add-in:

1. Pilih tab FILE lalu pilih Options.

   

2. Di kotak dialog Options pilih Add-Ins dari tab di sisi kiri.

   

3. Di bagian bawah jendela, pilih Excel Add-in dari dropdown Manage dan pilih Go…

   

4. Centang kotak di sebelah Solver Add-in dan pilih OK.

   

5. Perintah Solver sekarang akan muncul di tab DATA. Anda siap menggunakan Solver.

   

Menggunakan Solver di Excel

Mari kita mulai dengan contoh sederhana untuk memahami apa yang dilakukan Solver. Bayangkan kita ingin mengetahui radius apa yang akan menghasilkan lingkaran dengan luas 50 satuan persegi. Kita mengetahui persamaan luas lingkaran (A=pi r²). Kita tentu saja dapat mengatur ulang persamaan ini untuk memberikan radius yang diperlukan untuk area tertentu, tetapi untuk contoh, mari kita berpura-pura tidak tahu bagaimana melakukannya.

Buat spreadsheet dengan jari-jari di B1 dan hitung luas di B2 menggunakan persamaan =pi()B1^2.

Kita dapat menyesuaikan nilai secara manual di B1 hingga B2 menunjukkan nilai yang cukup mendekati 50. Tergantung pada seberapa akurat kita perlu, ini mungkin pendekatan praktis. Namun, jika kita perlu sangat tepat, itu akan memakan waktu lama untuk melakukan penyesuaian yang diperlukan. Sebenarnya, inilah yang dilakukan Solver. Itu membuat penyesuaian nilai di sel tertentu, dan memeriksa nilai di sel target:

1. Pilih tab DATA dan Solver, untuk memuat Solver Parameters kotak dialog

2. Tetapkan Tujuan sel menjadi Area, B2. Ini adalah nilai yang akan diperiksa, menyesuaikan sel lain hingga sel ini mencapai nilai yang benar.

   

3. Pilih tombol untuk Nilai: dan tetapkan nilai 50. Ini adalah nilai yang harus dicapai B2.

   

4. Dalam kotak berjudul Dengan Mengubah Sel Variabel: masukkan sel yang berisi jari-jari, B1.

   

5. Biarkan opsi lain sebagaimana adanya secara default dan pilih Solve. Optimalisasi dilakukan, nilai B1 disesuaikan hingga B2 menjadi 50 dan muncul dialog Solver Hasil.

   

6. Pilih OK untuk menyimpan solusi.

   

Contoh sederhana ini menunjukkan cara kerja solver. Dalam hal ini, kita bisa lebih mudah mendapatkan solusi dengan cara lain. Selanjutnya kita akan melihat beberapa contoh di mana Solver memberikan solusi yang sulit ditemukan dengan cara lain.

Fitting Model Kompleks Menggunakan Excel Solver Add-In

Excel memiliki fungsi bawaan untuk melakukan regresi linier, menyesuaikan garis lurus melalui sekumpulan data. Banyak fungsi non-linier umum dapat dilinierkan yang berarti bahwa regresi linier dapat digunakan untuk menyesuaikan fungsi seperti eksponensial. Untuk fungsi yang lebih kompleks, Solver dapat digunakan untuk melakukan 'minimalisasi kuadrat terkecil'. Dalam contoh ini, kita akan mempertimbangkan untuk memasukkan persamaan bentuk ax^b+cx^d ke data yang ditunjukkan di bawah ini.

Ini melibatkan langkah-langkah berikut:

1. Tata dataset dengan nilai x di kolom A dan nilai y di kolom B.
2. Buat 4 nilai koefisien (a, b, c, dan d) di suatu tempat di spreadsheet, ini dapat diberikan nilai awal yang berubah-ubah.

3. Buat kolom dengan nilai Y yang pas, menggunakan persamaan bentuk ax^b+cx^d yang merujuk pada koefisien yang dibuat pada langkah 2 dan nilai x di kolom A. Perhatikan bahwa untuk menyalin rumus ke bawah kolom, referensi ke koefisien harus absolut sedangkan referensi ke nilai x harus relatif.

   

4. Meskipun tidak penting, Anda bisa mendapatkan indikasi visual tentang seberapa cocok persamaan dengan memplot kedua kolom y terhadap nilai x pada bagan sebar XY tunggal. Masuk akal untuk menggunakan penanda untuk titik data asli, karena ini adalah nilai diskrit dengan noise, dan menggunakan garis untuk persamaan yang dipasang.

   

5. Selanjutnya, kita membutuhkan cara untuk mengukur perbedaan antara data dan persamaan yang dipasang. Cara standar untuk melakukannya adalah dengan menghitung jumlah perbedaan kuadrat. Di kolom ketiga, untuk setiap baris, nilai data asli untuk Y dikurangi dari nilai persamaan yang dipasang, dan hasilnya dikuadratkan. Jadi, dalam D2, nilainya diberikan oleh =(C2-B2)^2 Jumlah semua nilai kuadrat ini kemudian dihitung. Karena nilainya dikuadratkan, mereka hanya bisa positif.

   

6. Anda sekarang siap untuk melakukan optimasi menggunakan Solver. Ada empat koefisien yang perlu disesuaikan (a, b, c dan d). Anda juga memiliki satu nilai objektif untuk diminimalkan, jumlah dari perbedaan kuadrat. Luncurkan solver, seperti di atas, dan atur parameter solver untuk mereferensikan nilai-nilai ini, seperti yang ditunjukkan di bawah ini.

   

7. Hapus centang opsi untuk Jadikan Variabel Tidak Terkendala Non-Negatif, ini akan memaksa semua koefisien untuk mengambil nilai positif.

   

8. Pilih Pecahkan dan tinjau hasilnya. Grafik akan diperbarui memberikan indikasi yang baik tentang kesesuaian. Jika pemecah tidak menghasilkan kecocokan yang baik pada upaya pertama, Anda dapat mencoba menjalankannya lagi. Jika kecocokan telah meningkat, coba selesaikan dari nilai saat ini. Jika tidak, Anda dapat mencoba meningkatkan kecocokan secara manual sebelum menyelesaikannya.

   

9. Setelah kecocokan diperoleh, Anda dapat keluar dari solver.

Memecahkan Model Secara Iteratif

Terkadang ada persamaan yang relatif sederhana yang memberikan keluaran dalam bentuk beberapa masukan. Namun, ketika kami mencoba membalikkan masalah, tidak mungkin menemukan solusi sederhana. Misalnya, daya yang dikonsumsi oleh kendaraan kira-kira diberikan oleh P=av + bv^3 di mana v adalah kecepatan, a adalah koefisien untuk tahanan gelinding dan b adalah koefisien untuk tarikan aerodinamis. Meskipun persamaan ini cukup sederhana, tidak mudah untuk mengatur ulang untuk memberikan persamaan kecepatan yang akan dicapai kendaraan untuk input daya yang diberikan. Namun, kita dapat menggunakan Solver untuk menemukan kecepatan ini secara iteratif. Misalnya, temukan kecepatan yang dicapai dengan input daya 740 W.

1. Buat spreadsheet sederhana dengan kecepatan, koefisien a dan b, dan daya yang dihitung darinya.

   

2. Luncurkan Solver dan masukkan kekuatan, B5, sebagai tujuannya. Tetapkan nilai objektif 740 dan pilih kecepatan, B2, sebagai sel variabel yang akan diubah. Pilih solve untuk memulai solusi.

   

3. solver menyesuaikan nilai kecepatan hingga daya mendekati 740, memberikan kecepatan yang kita butuhkan.

   

4. Memecahkan model dengan cara ini seringkali bisa lebih cepat dan lebih sedikit kesalahan daripada membalikkan model kompleks.

Memahami berbagai opsi yang tersedia di solver bisa sangat sulit. Jika Anda mengalami kesulitan mendapatkan solusi yang masuk akal maka seringkali berguna untuk menerapkan kondisi batas ke sel yang dapat diubah. Ini adalah nilai-nilai yang membatasi di mana mereka tidak boleh disesuaikan. Misalnya, pada contoh sebelumnya, kecepatan tidak boleh kurang dari nol dan juga dimungkinkan untuk menetapkan batas atas. Ini akan menjadi kecepatan yang Anda yakin kendaraan tidak bisa melaju lebih cepat dari itu. Jika Anda dapat menetapkan batas untuk sel variabel yang dapat diubah, maka itu juga membuat opsi lanjutan lainnya berfungsi lebih baik, seperti multistart. Ini akan menjalankan sejumlah solusi yang berbeda, mulai dari nilai awal yang berbeda untuk variabel.

Memilih Metode Penyelesaian juga bisa sulit. Simplex LP hanya cocok untuk model linier, jika masalahnya tidak linier maka akan gagal dengan pesan bahwa kondisi ini tidak terpenuhi. Dua metode lainnya keduanya cocok untuk metode non-linear. GRG Nonlinier adalah yang tercepat tetapi solusinya bisa sangat bergantung pada kondisi awal awal. Itu memang memiliki fleksibilitas yang tidak memerlukan variabel untuk menetapkan batas. Pemecah Evolusi sering yang paling dapat diandalkan tetapi mengharuskan semua variabel memiliki batas atas dan bawah, yang mungkin sulit untuk dikerjakan sebelumnya.

Add-in Excel Solver adalah alat yang sangat kuat yang dapat diterapkan pada banyak masalah praktis. Untuk mengakses sepenuhnya kecanggihan Excel, coba gabungkan Solver dengan makro Excel.