Cara Menggunakan Pencari Matlamat dan Penyelesai Excel untuk Menyelesaikan Pembolehubah Tidak Diketahui

Excel sangat berkemampuan apabila anda mempunyai semua data yang anda perlukan untuk pengiraan anda.

Tetapi bukankah lebih baik jika boleh selesaikan pemboleh ubah yang tidak diketahui ?

Dengan Goal Seek dan tambahan Solver, ia dapat. Dan kami akan menunjukkan caranya. Baca terus untuk panduan lengkap mengenai cara menyelesaikan satu sel dengan Goal Seek atau persamaan yang lebih rumit dengan Solver.

Cara Menggunakan Goal Seek di Excel

Pencarian Matlamat sudah dibina ke dalam Excel. Ia berada di bawah Data tab, di Analisis Apa-Sekiranya menu:

Untuk contoh ini, kami akan menggunakan sekumpulan nombor yang sangat mudah. Kami mempunyai nombor penjualan bernilai tiga perempat dan matlamat tahunan. Kita boleh menggunakan Goal Seek untuk mengetahui bilangan yang perlu ada dalam Q4 untuk mencapai tujuan.

Seperti yang anda lihat, jumlah jualan semasa adalah 114,706 unit. Sekiranya kita ingin menjual 250,000 pada akhir tahun, berapa banyak yang perlu kita jual pada Q4? Tujuan Matlamat Excel akan memberitahu kami.

Inilah cara menggunakan Goal Seek, langkah demi langkah:

Klik Data> Analisis What-If> Pencapaian Matlamat . Anda akan melihat tetingkap ini:
Masukkan bahagian 'sama' dari persamaan anda di Tetapkan Sel bidang. Ini adalah nombor yang akan dioptimumkan oleh Excel. Dalam kes kami, jumlah penjualan kami yang terdapat di sel B5.
Taipkan nilai matlamat anda ke dalam Untuk menghargai bidang. Kami mencari sejumlah 250,000 unit yang terjual, jadi kami akan meletakkan '250,000' dalam bidang ini.
Beritahu Excel pemboleh ubah mana yang harus diselesaikan dalam Dengan menukar sel bidang. Kami ingin melihat bagaimana jualan kami di Q4. Oleh itu, kami akan memberitahu Excel untuk menyelesaikan sel D2. Ia akan kelihatan seperti ini apabila sudah siap:
Pukul okey untuk menyelesaikan matlamat anda. Bila nampak bagus, tekan saja okey . Excel akan memberitahu anda bila Goal Seek menemui jalan penyelesaian.
Klik okey sekali lagi dan anda akan melihat nilai yang menyelesaikan persamaan anda dalam sel yang anda pilih Dengan menukar sel .

Dalam kes kami, penyelesaiannya adalah 135,294 unit. Sudah tentu, kita dapat menjumpainya dengan mengurangkan jumlah berjalan dari matlamat tahunan. Tetapi Goal Seek juga boleh digunakan pada sel yang sudah mempunyai data di dalamnya . Dan itu lebih berguna.

Perhatikan bahawa Excel menimpa data kami sebelumnya. Adalah idea yang baik untuk jalankan Goal Seek pada salinan data anda . Sebaiknya buat catatan pada data yang disalin anda bahawa ia dihasilkan menggunakan Goal Seek. Anda tidak mahu mengelirukannya untuk data terkini dan tepat.

adakah haram memuat turun video youtube

Jadi Tujuan Pencari adalah ciri Excel yang berguna , tetapi tidak begitu mengagumkan. Mari lihat alat yang jauh lebih menarik: add-in Solver.

Apa yang Dilakukan oleh Penyelesai Excel?

Pendek kata, Penyelesai seperti versi multivariat Goal Seek . Ia memerlukan satu pemboleh ubah matlamat dan menyesuaikan sebilangan pemboleh ubah lain sehingga mendapat jawapan yang anda mahukan.

Ia dapat menyelesaikan nilai maksimum nombor, nilai minimum nombor, atau nombor tepat.

Dan ia berfungsi dalam batasan, jadi jika satu pemboleh ubah tidak dapat diubah, atau hanya dapat berubah dalam julat yang ditentukan, Penyelesai akan mempertimbangkannya.

Ini adalah kaedah yang baik untuk menyelesaikan beberapa pemboleh ubah yang tidak diketahui di Excel. Tetapi mencari dan menggunakannya tidak mudah.

Mari kita lihat pemuatan Solver add-in, kemudian cari cara menggunakan Solver di Excel 2016.

Cara Memuatkan Solusi Tambahan

Secara lalai Excel tidak mempunyai Penyelesai. Ini adalah tambahan jadi, seperti ciri Excel lain yang kuat, anda harus memuatkannya terlebih dahulu. Nasib baik, ia sudah ada di komputer anda.

Kepala ke Fail> Pilihan> Tambah . Kemudian klik pada Pergi sebelah Urus: Tambah Excel .

Sekiranya dropdown ini mengatakan sesuatu selain 'Excel Add-Ins', anda perlu mengubahnya:

Di tetingkap yang dihasilkan, anda akan melihat beberapa pilihan. Pastikan kotak di sebelah Tambahan Penyelesai diperiksa, dan tekan okey .

Anda sekarang akan melihat Penyelesai butang di Analisis kumpulan dari Data tab:

Sekiranya anda sudah menggunakan Papan Alat Analisis Data , anda akan melihat butang Analisis Data. Sekiranya tidak, Solver akan muncul dengan sendirinya.

Sekarang setelah memuatkan add-in, mari kita lihat cara menggunakannya.

Cara Menggunakan Penyelesai di Excel

Terdapat tiga bahagian untuk setiap tindakan Penyelesai: objektif, sel pemboleh ubah, dan kekangan. Kami akan melalui setiap langkah.

Klik Data> Penyelesai . Anda akan melihat tetingkap Penyelesaian Parameter di bawah. (Sekiranya anda tidak melihat butang pemecah, lihat bahagian sebelumnya tentang cara memuatkan tambahan Penyelesai.)
Tetapkan objektif sel anda dan beritahu Excel tujuan anda. Objektifnya berada di bahagian atas tetingkap Penyelesai, dan ia mempunyai dua bahagian: sel objektif dan pilihan memaksimumkan, meminimumkan, atau nilai tertentu. Sekiranya anda memilih Maks , Excel akan menyesuaikan pemboleh ubah anda untuk mendapatkan bilangan sebanyak mungkin dalam sel objektif anda. Min adalah sebaliknya: Penyelesai akan meminimumkan bilangan objektif. Nilai membolehkan anda menentukan nombor tertentu untuk dicari oleh Solver.
Pilih sel pemboleh ubah yang boleh diubah Excel. Sel pemboleh ubah ditetapkan dengan Dengan Menukar Sel Berubah bidang. Klik anak panah di sebelah medan, kemudian klik dan seret untuk memilih sel yang boleh digunakan oleh Penyelesai. Perhatikan bahawa ini semua sel yang boleh berbeza-beza. Sekiranya anda tidak mahu sel berubah, jangan pilih sel itu.
Tetapkan kekangan pada pemboleh ubah berbilang atau individu. Akhirnya, kita sampai pada kekangan. Di sinilah Solver sangat berkuasa. Daripada mengubah mana-mana sel pemboleh ubah ke nombor yang diinginkan, anda boleh menentukan batasan yang harus dipenuhi. Untuk perincian, lihat bahagian cara menetapkan kekangan di bawah.
Setelah semua maklumat ini ada, tekan Selesaikan untuk mendapatkan jawapan anda. Excel akan mengemas kini data anda untuk memasukkan pemboleh ubah baru (inilah sebabnya kami mengesyorkan anda membuat salinan data anda terlebih dahulu).

Anda juga boleh menghasilkan laporan, yang akan kita lihat secara ringkas dalam contoh Penyelesaian kami di bawah.

Cara Menetapkan Kekangan dalam Penyelesai

Anda mungkin memberitahu Excel bahawa satu pemboleh ubah harus lebih besar 200. Apabila mencuba nilai pemboleh ubah yang berbeza, Excel tidak akan berada di bawah 201 dengan pemboleh ubah tertentu.

Untuk menambah kekangan, klik Tambah butang di sebelah senarai kekangan. Anda akan mendapat tetingkap baru. Pilih sel (atau sel) yang akan dikekang di Rujukan Sel bidang, kemudian pilih pengendali.

Berikut adalah pengendali yang ada:

<= (kurang daripada atau sama dengan)
= (sama dengan)
=> (lebih besar daripada atau sama dengan)
int (mestilah bilangan bulat)
saya (mestilah 1 atau 0)
Semua Berbeza

Semua Berbeza agak mengelirukan. Ini menentukan bahawa setiap sel dalam julat yang anda pilih Rujukan Sel mestilah nombor yang berbeza. Tetapi ia juga menentukan bahawa mereka mesti antara 1 dan jumlah sel. Oleh itu, jika anda mempunyai tiga sel, anda akan memperoleh nombor 1, 2 dan 3 (tetapi tidak semestinya mengikut urutan itu)

Akhirnya, tambahkan nilai untuk kekangan.

Penting untuk diingat bahawa anda boleh pilih beberapa sel untuk Rujukan Sel. Sekiranya anda mahu enam pemboleh ubah mempunyai nilai lebih dari 10, misalnya, anda boleh memilih semuanya dan memberitahu Penyelesai bahawa ia mesti lebih besar daripada atau sama dengan 11. Anda tidak perlu menambahkan kekangan untuk setiap sel.

Anda juga boleh menggunakan kotak centang di tetingkap Penyelesai utama untuk memastikan bahawa semua nilai yang tidak anda tetapkan batasannya tidak negatif. Sekiranya anda mahu pemboleh ubah anda menjadi negatif, hapus centang kotak ini.

Contoh Penyelesai

Untuk melihat bagaimana semua ini berfungsi, kami akan menggunakan add-in Solver untuk membuat pengiraan cepat. Inilah data yang kami mulakan:

Di dalamnya, kita mempunyai lima pekerjaan yang berbeza, masing-masing membayar kadar yang berbeza. Kami juga mempunyai jumlah jam pekerja teoritis bekerja di setiap pekerjaan tersebut dalam seminggu tertentu. Kita boleh menggunakan add-in Solver untuk mengetahui cara memaksimumkan jumlah pembayaran sambil mengekalkan pemboleh ubah tertentu dalam beberapa batasan.

Inilah kekangan yang akan kami gunakan:

Tiada pekerjaan boleh turun di bawah empat jam.
Kerja 2 mesti melebihi lapan jam .
Kerja 5 mesti kurang dari sebelas jam .
Jumlah jam kerja mestilah sama dengan 40 .

Adalah berguna untuk menuliskan kekangan anda seperti ini sebelum menggunakan Solver.

Inilah cara kami menetapkannya di Solver:

Pertama, perhatikan bahawa Saya telah membuat salinan jadual jadi kami tidak menimpa yang asal, yang mengandungi waktu kerja kami sekarang.

Dan kedua, lihat bahawa nilai-nilai dalam kekangan lebih besar daripada dan kurang daripada itu satu lebih tinggi atau lebih rendah daripada apa yang saya nyatakan di atas. Itu kerana tidak ada pilihan yang lebih besar atau kurang daripada. Terdapat hanya lebih besar daripada atau sama dengan dan kurang daripada atau sama dengan.

Mari memukul Selesaikan dan lihat apa yang berlaku.

Penyelesai menemui jalan penyelesaian! Seperti yang anda lihat di sebelah kiri tetingkap di atas, pendapatan kami meningkat sebanyak $ 130. Dan semua kekangan telah dipenuhi.

windows 7 anda memerlukan kebenaran untuk melakukan tindakan ini

Untuk mengekalkan nilai baru, pastikan Simpan Penyelesaian Penyelesaian diperiksa dan dipukul okey .

Sekiranya anda mahukan lebih banyak maklumat, anda boleh memilih laporan dari sebelah kanan tetingkap. Pilih semua laporan yang anda mahukan, beritahu Excel sama ada anda mahu digariskan (saya mengesyorkannya), dan tekan okey .

Laporan dihasilkan pada helaian baru di buku kerja anda dan memberi anda maklumat mengenai proses yang dilalui oleh add-in Solver untuk mendapatkan jawapan anda.

Dalam kes kami, laporannya tidak begitu menarik, dan tidak ada banyak maklumat menarik di sana. Tetapi jika anda menjalankan persamaan Penyelesai yang lebih rumit, anda mungkin menemui beberapa maklumat pelaporan yang berguna dalam lembaran kerja baru ini. Klik sahaja + butang di sebelah mana-mana laporan untuk mendapatkan lebih banyak maklumat:

Pilihan Lanjutan Penyelesai

Sekiranya anda tidak tahu banyak tentang statistik, anda boleh mengabaikan pilihan lanjutan Solver dan jalankan seperti sedia kala. Tetapi jika anda menjalankan pengiraan yang besar dan rumit, anda mungkin ingin memeriksanya.

Yang paling jelas adalah kaedah penyelesaian:

Anda boleh memilih antara GRG Nonlinear, Simplex LP, dan Evolutionary. Excel memberikan penjelasan ringkas mengenai kapan anda harus menggunakannya. Penjelasan yang lebih baik memerlukan pengetahuan mengenai statistik dan regresi.

Untuk menyesuaikan tetapan tambahan, tekan sahaja Pilihan butang. Anda boleh memberitahu Excel mengenai optimum bilangan bulat, menetapkan had masa pengiraan (berguna untuk set data besar), dan menyesuaikan bagaimana kaedah penyelesaian GRG dan Evolutionary membuat pengiraan mereka.

Sekali lagi, jika anda tidak tahu apa maksudnya, jangan risau. Sekiranya anda ingin mengetahui lebih lanjut mengenai kaedah penyelesaian yang akan digunakan, Engineer Excel mempunyai artikel baik yang memberikannya untuk anda . Sekiranya anda mahukan ketepatan maksimum, Evolutionary mungkin merupakan kaedah yang baik. Ketahuilah bahawa ia akan memakan masa yang lama.

Pencari Matlamat dan Penyelesai: Mengambil Excel ke Tahap Seterusnya

Sekarang setelah anda selesa dengan asas-asas penyelesaian untuk pemboleh ubah yang tidak diketahui di Excel, dunia pengiraan spreadsheet sepenuhnya terbuka untuk anda.

Goal Seek dapat membantu anda menjimatkan masa dengan membuat beberapa pengiraan lebih cepat, dan Solver menambahkan sejumlah besar tenaga Kebolehan mengira Excel .

Cuma masalah selesa dengan mereka. Semakin banyak anda menggunakannya, semakin berguna.

Adakah anda menggunakan Goal Seek atau Solver dalam hamparan anda? Apa petua lain yang dapat anda berikan untuk mendapatkan jawapan terbaik dari mereka? Kongsi pendapat anda dalam komen di bawah!

Berkongsi Berkongsi Tweet E-mel 5 Petua untuk Meningkatkan Mesin Linux VirtualBox Anda

Bosan dengan prestasi buruk yang ditawarkan oleh mesin maya? Inilah yang harus anda lakukan untuk meningkatkan prestasi VirtualBox anda.

Baca Seterusnya Topik-topik yang berkaitan

Produktiviti
Hamparan
Microsoft Excel
Petua Microsoft Office

Mengenai Pengarang Kemudian Albright(506 Artikel Diterbitkan)

Dann adalah strategi kandungan dan perunding pemasaran yang membantu syarikat menghasilkan permintaan dan petunjuk. Dia juga menulis blog mengenai strategi dan pemasaran konten di dannalbright.com.

Lagi Dari Dann Albright

Langgan buletin kami

Sertailah buletin kami untuk mendapatkan petua, ulasan, ebook percuma, dan tawaran eksklusif!

Klik di sini untuk melanggan