Apakah Excel Solver?

2024 Pengarang: Abigail Brown | [email protected]. Diubah suai terakhir: 2023-12-17 06:56

Add-in Excel Solver melakukan pengoptimuman matematik. Ini biasanya digunakan untuk menyesuaikan model kompleks dengan data atau mencari penyelesaian berulang kepada masalah. Sebagai contoh, anda mungkin mahu menyesuaikan lengkung melalui beberapa titik data, menggunakan persamaan. Penyelesai boleh mencari pemalar dalam persamaan yang memberikan kesesuaian terbaik kepada data. Aplikasi lain ialah sukar untuk menyusun semula model untuk menjadikan output yang diperlukan sebagai subjek persamaan.

Di manakah Solver dalam Excel?

Tambahan Solver disertakan dengan Excel tetapi ia tidak selalu dimuatkan sebagai sebahagian daripada pemasangan lalai. Untuk menyemak sama ada ia dimuatkan, pilih tab DATA dan cari ikon Solver dalam bahagian Analysis.

Jika anda tidak menemui Penyelesai di bawah tab DATA maka anda perlu memuatkan tambahan:

1. Pilih tab FAIL dan kemudian pilih Pilihan.

   

2. Dalam kotak dialog Options pilih Add-Ins daripada tab di sebelah kiri.

   

3. Di bahagian bawah tetingkap, pilih Excel Add-in daripada menu lungsur Urus dan pilih Pergi…

   

4. Semak kotak semak di sebelah Tambah Penyelesai dan pilih OK.

   

5. Arahan Solver kini sepatutnya muncul pada tab DATA. Anda sudah bersedia untuk menggunakan Solver.

   

Menggunakan Solver dalam Excel

Mari kita mulakan dengan contoh mudah untuk memahami perkara yang dilakukan oleh Penyelesai. Bayangkan kita ingin tahu berapa jejari yang akan memberikan bulatan dengan keluasan 50 unit persegi. Kita tahu persamaan untuk luas bulatan (A=pi r²). Sudah tentu, kita boleh menyusun semula persamaan ini untuk memberikan jejari yang diperlukan untuk kawasan tertentu, tetapi sebagai contoh mari kita berpura-pura tidak tahu bagaimana untuk melakukannya.

Buat hamparan dengan jejari dalam B1 dan hitung luas dalam B2 menggunakan persamaan =pi()B1^2.

Kami boleh melaraskan nilai secara manual dalam B1 sehingga B2 menunjukkan nilai yang cukup hampir kepada 50. Bergantung pada ketepatan kita perlu, ini mungkin pendekatan praktikal. Walau bagaimanapun, jika kita perlu sangat tepat, ia akan mengambil masa yang lama untuk membuat pelarasan yang diperlukan. Sebenarnya, ini pada asasnya apa yang Solver lakukan. Ia membuat pelarasan pada nilai dalam sel tertentu dan menyemak nilai dalam sel sasaran:

1. Pilih DATA tab dan Solver, untuk memuatkan Parameter Penyelesai kotak dialog

2. Tetapkan Objektif sel menjadi Kawasan, B2. Ini ialah nilai yang akan disemak, melaraskan sel lain sehingga yang ini mencapai nilai yang betul.

   

3. Pilih butang untuk Nilai: dan tetapkan nilai 50. Ini ialah nilai yang harus dicapai oleh B2.

   

4. Dalam kotak bertajuk Dengan Menukar Sel Pembolehubah: masukkan sel yang mengandungi jejari, B1.

   

5. Biarkan pilihan lain sebagai pilihan lalai dan pilih Selesai. Pengoptimuman dijalankan, nilai B1 dilaraskan sehingga B2 ialah 50 dan dialog Hasil Penyelesai dipaparkan.

   

6. Pilih OK untuk mengekalkan penyelesaiannya.

   

Contoh mudah ini menunjukkan cara penyelesai berfungsi. Dalam kes ini, kita boleh mendapatkan penyelesaian dengan lebih mudah dengan cara lain. Seterusnya, kita akan melihat beberapa contoh di mana Solver memberikan penyelesaian yang sukar dicari dengan cara lain.

Memasang Model Kompleks Menggunakan Alat Tambah Penyelesai Excel

Excel mempunyai fungsi terbina dalam untuk melaksanakan regresi linear, menyesuaikan garis lurus melalui set data. Banyak fungsi bukan linear biasa boleh dilinearkan bermakna regresi linear boleh digunakan untuk menyesuaikan fungsi seperti eksponen. Untuk fungsi yang lebih kompleks, Solver boleh digunakan untuk melakukan 'pengurangan kuasa dua terkecil'. Dalam contoh ini, kami akan mempertimbangkan untuk memasangkan persamaan bentuk ax^b+cx^d kepada data yang ditunjukkan di bawah.

Ini melibatkan langkah berikut:

1. Susun set data dengan nilai x dalam lajur A dan nilai-y dalam lajur B.
2. Buat 4 nilai pekali (a, b, c dan d) di suatu tempat pada hamparan, ini boleh diberikan nilai permulaan sewenang-wenangnya.

3. Buat lajur nilai Y yang dipasang, menggunakan persamaan bentuk ax^b+cx^d yang merujuk pekali yang dicipta dalam langkah 2 dan nilai x dalam lajur A. Ambil perhatian bahawa untuk menyalin formula ke bawah lajur, rujukan kepada pekali mestilah mutlak manakala rujukan kepada nilai x mestilah relatif.

   

4. Walaupun tidak penting, anda boleh mendapatkan petunjuk visual tentang kesesuaian persamaan dengan memplot kedua-dua lajur y terhadap nilai x pada carta serakan XY tunggal. Adalah wajar untuk menggunakan penanda untuk titik data asal, kerana ini adalah nilai diskret dengan hingar dan menggunakan garis untuk persamaan yang dipasang.

   

5. Seterusnya, kami memerlukan cara untuk mengukur perbezaan antara data dan persamaan kami yang dipasang. Cara standard untuk melakukan ini ialah mengira jumlah perbezaan kuasa dua. Dalam lajur ketiga, untuk setiap baris, nilai data asal untuk Y ditolak daripada nilai persamaan yang dipasang, dan hasilnya adalah kuasa dua. Jadi, dalam D2, nilai diberikan oleh =(C2-B2)^2 Jumlah semua nilai kuasa dua ini kemudiannya dikira. Oleh kerana nilai adalah kuasa dua ia hanya boleh positif.

   

6. Anda kini bersedia untuk melakukan pengoptimuman menggunakan Solver. Terdapat empat pekali yang perlu dilaraskan (a, b, c dan d). Anda juga mempunyai satu nilai objektif untuk diminimumkan, jumlah perbezaan kuasa dua. Lancarkan penyelesai, seperti di atas, dan tetapkan parameter penyelesai untuk merujuk nilai ini, seperti yang ditunjukkan di bawah.

   

7. Nyahtanda pilihan untuk Jadikan Pembolehubah Tanpa Kekangan Bukan Negatif, ini akan memaksa semua pekali mengambil nilai positif.

   

8. Pilih Selesai dan semak keputusan. Carta akan dikemas kini memberikan petunjuk yang baik tentang kebaikan kesesuaian. Jika penyelesai tidak menghasilkan kesesuaian yang baik pada percubaan pertama anda boleh cuba menjalankannya semula. Jika kesesuaian telah bertambah baik, cuba selesaikan daripada nilai semasa. Jika tidak, anda boleh cuba meningkatkan kesesuaian secara manual sebelum menyelesaikannya.

   

9. Setelah kesesuaian yang baik diperoleh, anda boleh keluar dari penyelesai.

Menyelesaikan Model Secara Berulang

Kadangkala terdapat persamaan yang agak mudah yang memberikan output dari segi beberapa input. Walau bagaimanapun, apabila kita cuba menyongsangkan masalah itu tidak mungkin untuk mencari penyelesaian yang mudah. Sebagai contoh, kuasa yang digunakan oleh kenderaan adalah lebih kurang diberikan oleh P=av + bv^3 di mana v ialah halaju, a ialah pekali bagi rintangan bergolek dan b ialah pekali untuk seretan aerodinamik. Walaupun ini adalah persamaan yang agak mudah, ia tidak mudah untuk menyusun semula untuk memberikan persamaan halaju yang akan dicapai oleh kenderaan untuk input kuasa yang diberikan. Walau bagaimanapun, kita boleh menggunakan Solver untuk mencari halaju ini secara berulang. Sebagai contoh, cari halaju yang dicapai dengan input kuasa 740 W.

1. Sediakan hamparan mudah dengan halaju, pekali a dan b serta kuasa yang dikira daripadanya.

   

2. Lancarkan Penyelesai dan masukkan kuasa, B5, sebagai objektif. Tetapkan nilai objektif 740 dan pilih halaju, B2, sebagai sel pembolehubah untuk ditukar. Pilih solve untuk memulakan penyelesaian.

   

3. Penyelesai melaraskan nilai halaju sehingga kuasa sangat hampir kepada 740, memberikan halaju yang kami perlukan.

   

4. Menyelesaikan model dengan cara ini selalunya boleh menjadi lebih cepat dan kurang terdedah kepada ralat daripada menyongsangkan model kompleks.

Memahami pilihan berbeza yang tersedia dalam penyelesai boleh menjadi agak sukar. Jika anda menghadapi kesukaran mendapatkan penyelesaian yang munasabah maka selalunya berguna untuk menggunakan syarat sempadan pada sel yang boleh diubah. Ini adalah nilai mengehadkan yang melebihi nilai yang tidak boleh diselaraskan. Sebagai contoh, dalam contoh sebelumnya, halaju tidak boleh kurang daripada sifar dan ia juga mungkin untuk menetapkan sempadan atas. Ini akan menjadi kelajuan yang anda pasti pasti kenderaan itu tidak boleh pergi lebih laju. Jika anda dapat menetapkan had untuk sel boleh ubah boleh ubah, maka ia juga menjadikan pilihan lain yang lebih maju berfungsi dengan lebih baik, seperti multistart. Ini akan menjalankan beberapa penyelesaian yang berbeza, bermula pada nilai awal yang berbeza untuk pembolehubah.

Memilih Kaedah Penyelesaian juga boleh menjadi sukar. Simplex LP hanya sesuai untuk model linear, jika masalahnya bukan linear, ia akan gagal dengan mesej bahawa syarat ini tidak dipenuhi. Dua kaedah lain adalah sesuai untuk kaedah bukan linear. GRG Bukan linear adalah yang terpantas tetapi penyelesaiannya boleh sangat bergantung pada keadaan permulaan awal. Ia mempunyai fleksibiliti bahawa ia tidak memerlukan pembolehubah untuk menetapkan had. Penyelesai Evolusi selalunya paling boleh dipercayai tetapi ia memerlukan semua pembolehubah mempunyai kedua-dua sempadan atas dan bawah, yang mungkin sukar untuk diselesaikan terlebih dahulu.

Tambahan Excel Solver ialah alat yang sangat berkuasa yang boleh digunakan untuk banyak masalah praktikal. Untuk mengakses sepenuhnya kuasa Excel, cuba gabungkan Solver dengan makro Excel.