Cara Menggunakan Goal Seek dan Solver Excel untuk Memecahkan Variabel Tak Dikenal

Excel adalah alat yang ampuh saat data Anda lengkap. Tapi bukankah bagus jika bisa memecahkan variabel yang tidak diketahui?

Dengan Goal Seek dan add-in Solver, itu bisa. Dan kami akan menunjukkan caranya. Baca terus untuk panduan lengkap tentang cara menyelesaikan satu sel dengan Goal Seek atau persamaan yang lebih rumit dengan Solver.

Baca Juga:

Sikap dan Perilaku Wirausaha yang Harus Diketahui

Cara Menggunakan Goal Seek di Excel

Goal Seek sudah ada di dalam Excel. Itu di bawah Data tab, di Analisis Bagaimana-Jika menu:

Untuk contoh ini, kita akan menggunakan sekumpulan angka yang sangat sederhana. Kami memiliki angka penjualan senilai tiga perempat dan tujuan tahunan. Kita dapat menggunakan Goal Seek untuk mencari tahu berapa angka yang dibutuhkan di Q4 untuk mencapai tujuan.

Seperti yang Anda lihat, total penjualan saat ini adalah 114.706 unit. Jika kita ingin menjual 250.000 pada akhir tahun, berapa banyak yang harus kita jual di Q4? Goal Seek Excel akan memberi tahu kita.

Berikut cara menggunakan Goal Seek, langkah demi langkah:

1. Klik Data > Analisis Bagaimana-Jika > Pencarian Sasaran. Jendela Goal Seek akan muncul.
2. Masukkan bagian “sama dengan” dari persamaan Anda ke dalam Atur Sel bidang. Ini adalah angka yang akan coba dioptimalkan oleh Excel. Dalam kasus kami, ini adalah total angka penjualan kami di sel A5.
3. Ketikkan nilai sasaran Anda ke dalam Untuk menilai bidang. Kami mencari total 250.000 unit yang terjual, jadi kami akan menempatkan “250.000” di bidang ini.
4. Beri tahu Excel variabel mana yang harus dipecahkan di Dengan mengubah sel bidang. Kami ingin melihat seperti apa penjualan kami di Q4. Jadi kami akan memberi tahu Excel untuk menyelesaikan sel D2. Ini akan terlihat seperti ini ketika siap digunakan:

   

5. Memukul OKE untuk memecahkan tujuan Anda. Saat terlihat bagus, pukul saja OKE. Excel akan memberi tahu Anda saat Goal Seek telah menemukan solusi.

   

6. Klik OKE lagi, dan Anda akan melihat nilai yang menyelesaikan persamaan Anda di sel yang Anda pilih Dengan mengubah sel.

   

Dalam kasus kami, solusinya adalah 135.294 unit. Tentu saja, kita bisa menemukannya dengan mengurangkan total berjalan dari tujuan tahunan. Tapi Goal Seek juga bisa digunakan di sel itu sudah memiliki data di dalamnya. Dan itu lebih bermanfaat.

Perhatikan bahwa Excel menimpa data kami sebelumnya. Ini adalah ide yang baik untuk jalankan Goal Seek pada salinan data Anda. Ini juga merupakan ide bagus untuk membuat catatan pada data yang disalin bahwa itu dibuat menggunakan Goal Seek. Anda tidak ingin mengacaukannya untuk data terkini dan akurat.

Jadi Goal Seek adalah salah satu fungsi Excel yang paling berguna, tetapi tidak terlalu mengesankan. Anda hanya dapat menggunakannya pada satu sel dalam satu waktu. Jika Anda ingin menggunakan Pencarian Sasaran Excel di beberapa sel secara bersamaan, Anda memerlukan alat yang jauh lebih canggih. Untungnya, salah satu alat tersebut hadir dengan Excel. Mari kita lihat add-in Solver.

Apa yang Dilakukan Pemecah Excel?

Singkatnya, Solver seperti a Goal Seek versi multivarian. Jika Anda bertanya-tanya bagaimana cara menggunakan Goal Seek di Excel untuk banyak sel sekaligus, ini dia. Dibutuhkan satu variabel tujuan dan menyesuaikan sejumlah variabel lain hingga mendapatkan jawaban yang Anda inginkan.

Itu dapat memecahkan nilai maksimum angka, nilai minimum angka, atau angka pasti. Dan itu berfungsi dalam batasan, jadi jika satu variabel tidak dapat diubah, atau hanya dapat bervariasi dalam rentang yang ditentukan, Solver akan memperhitungkannya.

Ini cara yang bagus untuk memecahkan beberapa variabel yang tidak diketahui di Excel. Tetapi menemukan dan menggunakannya tidaklah mudah. Mari kita lihat memuat add-in Solver, lalu lompat ke cara menggunakan Solver di versi Microsoft 365 Excel saat ini.

Cara Memuat Add-In Solver

Excel tidak memiliki Solver secara default. Ini adalah add-in, jadi Anda harus memuatnya terlebih dahulu. Untungnya, itu sudah ada di komputer Anda.

Menuju ke File > (Lainnya… >) Opsi > Add-in. Kemudian klik Pergi di sebelah Kelola: Add-in Excel. Jika dropdown ini mengatakan sesuatu selain “Excel Add-Ins”, Anda harus mengubahnya:

Di jendela yang dihasilkan, Anda akan melihat beberapa opsi. Pastikan kotak di sebelah Add-In Pemecah diperiksa, dan tekan OKE.

Sekarang Anda akan melihat Pemecah tombol di Analisis kelompok dari Data tab:

Jika Anda telah menggunakan Data Analysis Toolpak, Anda akan melihat tombol Data Analysis. Jika tidak, Solver akan muncul dengan sendirinya. Sekarang setelah Anda memuat add-in, mari kita lihat cara menggunakannya.

Cara Menggunakan Solver di Excel

Ada tiga bagian untuk setiap tindakan Solver: tujuan, sel variabel, dan kendala. Kami akan berjalan melalui setiap langkah.

1. Klik Data > Pemecah. Anda akan melihat jendela Solver Parameters di bawah. (Jika Anda tidak melihat tombol solver, lihat bagian sebelumnya tentang cara memuat add-in Solver.)

   

2. Tetapkan tujuan sel Anda dan beri tahu Excel tujuan Anda. Sasaran ada di bagian atas jendela Solver, dan memiliki dua bagian: sel sasaran dan pilihan maksimalkan, minimalkan, atau nilai tertentu.

   

   Jika Anda memilih MaksExcel akan menyesuaikan variabel Anda untuk mendapatkan angka terbesar di sel tujuan Anda. Min sebaliknya: Pemecah akan meminimalkan jumlah tujuan. Nilai dari memungkinkan Anda menentukan nomor tertentu untuk dicari oleh Solver.

3. Pilih sel variabel yang dapat diubah oleh Excel. Sel variabel diatur dengan Dengan Mengubah Sel Variabel bidang. Klik panah di samping bidang, lalu klik dan seret untuk memilih sel yang akan digunakan oleh Solver. Perhatikan bahwa ini adalah semua sel yang dapat bervariasi. Jika Anda tidak ingin sel berubah, jangan pilih.

   

4. Tetapkan batasan pada beberapa atau variabel individual. Akhirnya, kita sampai pada kendala. Di sinilah Solver sangat kuat. Alih-alih mengubah salah satu sel variabel ke angka berapa pun yang diinginkan, Anda dapat menentukan batasan yang harus dipenuhi. Untuk detailnya, lihat bagian tentang cara menetapkan batasan di bawah ini.
5. Setelah semua informasi ini ada, tekan Menyelesaikan untuk mendapatkan jawaban Anda. Excel akan memperbarui data Anda untuk menyertakan variabel baru (inilah mengapa kami menyarankan Anda membuat salinan data terlebih dahulu).

Anda juga dapat membuat laporan, yang akan kita lihat secara singkat dalam contoh Solver di bawah ini.

Cara Mengatur Batasan di Solver

Anda mungkin memberi tahu Excel bahwa satu variabel harus lebih besar dari 200. Saat mencoba nilai variabel yang berbeda, Excel tidak akan berada di bawah 201 dengan variabel tertentu itu.

Untuk menambahkan pembatas, klik Menambahkan tombol di sebelah daftar kendala. Anda akan mendapatkan jendela baru. Pilih sel (atau sel) yang akan dibatasi di Referensi Sel bidang, lalu pilih operator.

Berikut adalah operator yang tersedia:

• <= (kurang dari atau sama dengan)
• = (sama dengan)
• => (lebih dari atau sama dengan)
• int (harus bilangan bulat)
• tempat sampah (harus 1 atau 0)
• Semua berbeda

Semua berbeda sedikit membingungkan. Ini menentukan bahwa setiap sel dalam rentang yang Anda pilih Referensi Sel harus nomor yang berbeda. Tapi itu juga menentukan bahwa mereka harus antara 1 dan jumlah sel.

Jadi, jika Anda memiliki tiga sel, Anda akan mendapatkan angka 1, 2, dan 3 (tetapi tidak harus dalam urutan itu). Terakhir, tambahkan nilai kendala.

Penting untuk diingat bahwa Anda bisa pilih beberapa sel untuk Referensi Sel. Jika Anda ingin enam variabel memiliki nilai di atas 10, misalnya, Anda dapat memilih semuanya dan memberi tahu Solver bahwa variabel tersebut harus lebih besar dari atau sama dengan 11. Anda tidak perlu menambahkan batasan untuk setiap sel.

Anda juga dapat menggunakan kotak centang di jendela Solver utama untuk memastikan bahwa semua nilai yang tidak Anda tentukan batasannya adalah non-negatif. Jika Anda ingin variabel Anda menjadi negatif, hapus centang pada kotak ini.

Contoh Pemecah

Untuk melihat bagaimana semua ini bekerja, kami akan menggunakan Add-in Solver untuk membuat perhitungan cepat. Inilah data yang kami mulai:

Di dalamnya, kami memiliki lima pekerjaan berbeda, yang masing-masing membayar tarif berbeda. Kami juga memiliki jumlah jam kerja seorang pekerja teoretis di setiap pekerjaan tersebut dalam minggu tertentu.

Kita dapat menggunakan add-in Solver untuk mencari tahu cara memaksimalkan pembayaran total sekaligus mempertahankan variabel tertentu dalam batasan tertentu. Berikut batasan yang akan kita gunakan:

• Tidak ada pekerjaan bisa jatuh di bawah empat jam.
• Pekerjaan 4 harus lebih dari 12 jam.
• Pekerjaan 5 harus kurang dari sebelas jam.
• Total jam kerja harus sama dengan 40.

Akan sangat membantu jika menuliskan batasan Anda seperti ini sebelum menggunakan Solver. Inilah cara kami mengaturnya di Solver:

Pertama, perhatikan itu Saya telah membuat salinan tabeljadi kami tidak menimpa yang asli, yang berisi jam kerja kami saat ini.

Dan kedua, lihat bahwa nilai-nilai dalam batasan lebih besar dari dan lebih kecil adalah satu lebih tinggi atau lebih rendah daripada yang saya sebutkan di atas. Itu karena tidak ada pilihan lebih besar atau kurang dari. Hanya ada yang lebih besar dari atau sama dengan dan kurang dari atau sama dengan.

Ayo pukul Menyelesaikan dan lihat apa yang terjadi.

Solver menemukan solusi! Seperti yang Anda lihat di sebelah kiri jendela di atas, penghasilan kami telah meningkat sebesar $152. Dan semua kendala telah terpenuhi.

Untuk menjaga nilai-nilai baru, pastikan Pertahankan Solver Solution diperiksa dan dipukul OKE. Namun, jika Anda menginginkan informasi lebih lanjut, Anda dapat memilih laporan dari sisi kanan jendela. Pilih semua laporan yang Anda inginkan, beri tahu Excel apakah Anda menginginkannya diuraikan (saya merekomendasikannya), dan tekan OKE.

Laporan dibuat pada lembar baru di buku kerja Anda dan memberi Anda informasi tentang proses yang dilalui add-in Solver untuk mendapatkan jawaban Anda.

Dalam kasus kami, laporannya tidak terlalu menarik, dan tidak banyak informasi menarik di sana. Tapi jika Anda menjalankan persamaan Solver yang lebih rumit, Anda mungkin menemukan beberapa informasi pelaporan yang berguna di lembar kerja baru ini. Cukup klik + tombol di samping setiap laporan untuk mendapatkan informasi lebih lanjut:

Opsi Lanjutan Pemecah

Jika Anda tidak tahu banyak tentang statistik, Anda dapat mengabaikan opsi lanjutan Solver dan menjalankannya apa adanya. Namun jika Anda menjalankan kalkulasi yang besar dan rumit, Anda mungkin ingin mempelajarinya.

Yang paling jelas adalah metode penyelesaiannya:

Anda dapat memilih antara GRG Nonlinear, Simplex LP, dan Evolutionary. Excel memberikan penjelasan sederhana tentang kapan Anda harus menggunakan masing-masing. Penjelasan yang lebih baik membutuhkan pengetahuan tentang statistik dan regresi Excel.

Untuk menyesuaikan pengaturan tambahan, tekan saja Pilihan tombol. Anda dapat memberi tahu Excel tentang optimalitas bilangan bulat, mengatur batasan waktu perhitungan (berguna untuk kumpulan data besar), dan menyesuaikan bagaimana metode penyelesaian GRG dan Evolutionary membuat perhitungan mereka.

Sekali lagi, jika Anda tidak tahu apa artinya semua ini, jangan khawatir. Jika Anda ingin tahu lebih banyak tentang metode penyelesaian mana yang digunakan, Insinyur Excel memiliki artikel bagus yang menjabarkannya untuk Anda. Jika Anda menginginkan akurasi maksimum, Evolutionary mungkin merupakan cara yang baik. Sadarilah bahwa itu akan memakan waktu lama.

Goal Seek and Solver: Membawa Excel ke Tingkat Selanjutnya

Sekarang setelah Anda merasa nyaman dengan dasar-dasar penyelesaian variabel yang tidak diketahui di Excel, dunia perhitungan spreadsheet yang benar-benar baru terbuka untuk Anda. Goal Seek dapat membantu Anda menghemat waktu dengan membuat beberapa kalkulasi lebih cepat, dan Solver menambahkan kekuatan besar pada kemampuan kalkulasi Excel.

Ini hanya masalah merasa nyaman dengan mereka. Semakin banyak Anda menggunakannya, semakin berguna mereka jadinya.