Selesaikan fungsi dalam Excel. Dayakan, kes guna dengan tangkapan skrin

"Cari penyelesaian" ialah tambahan Excel, yang membolehkan anda memilih penyelesaian terbaik untuk masalah berdasarkan sekatan yang ditentukan. Fungsi ini memungkinkan untuk menjadualkan pekerja, mengagihkan kos atau pelaburan. Mengetahui cara ciri ini berfungsi akan menjimatkan masa dan usaha anda.

Apakah itu Carian Penyelesaian

Dalam kombinasi dengan pelbagai pilihan lain dalam Excel, terdapat satu fungsi yang kurang popular, tetapi sangat diperlukan "Cari penyelesaian". Walaupun fakta bahawa mencarinya tidak mudah, mengenali dan menggunakannya membantu dalam menyelesaikan banyak masalah. Pilihan memproses data dan memberikan penyelesaian optimum daripada yang dibenarkan. Artikel tersebut menerangkan cara Carian Penyelesaian berfungsi secara langsung.

Cara menghidupkan ciri "Cari penyelesaian".

Walaupun keberkesanannya, pilihan yang dipersoalkan tidak berada di tempat yang menonjol pada bar alat atau menu konteks. Kebanyakan pengguna yang bekerja dalam Excel tidak menyedari kehadirannya. Secara lalai, fungsi ini dilumpuhkan, untuk memaparkannya, lakukan tindakan berikut:

1. Buka "Fail" dengan mengklik pada nama yang sesuai.
2. Klik pada bahagian "Tetapan".
3. Kemudian pilih subseksyen "Tambahan". Semua alat tambah program akan dipaparkan di sini, tulisan "Pengurusan" akan muncul di bawah. Di sebelah kanannya akan terdapat menu pop timbul di mana anda harus memilih "Tambah Excel". Kemudian klik "Pergi".
   

   

4. Tetingkap tambahan "Add-in" akan dipaparkan pada monitor. Tandai kotak di sebelah fungsi yang dikehendaki dan klik OK.
5. Fungsi yang dikehendaki akan muncul pada reben di sebelah kanan bahagian "Data".

Mengenai Model

Maklumat ini akan sangat berguna untuk mereka yang baru mengenali konsep "model pengoptimuman". Sebelum menggunakan "Cari penyelesaian", adalah disyorkan untuk mengkaji bahan mengenai kaedah membina model:

• pilihan yang sedang dipertimbangkan akan memungkinkan untuk mengenal pasti kaedah terbaik untuk memperuntukkan dana untuk pelaburan, memuatkan premis, membekalkan barangan atau tindakan lain di mana perlu untuk mencari penyelesaian terbaik.
• "Kaedah optimum" dalam situasi sedemikian bermakna: meningkatkan pendapatan, mengurangkan kos, meningkatkan kualiti, dsb.

Tugas pengoptimuman biasa:

• Penentuan rancangan pengeluaran, di mana keuntungan daripada penjualan barang yang dikeluarkan akan menjadi maksimum.
• Penentuan peta pengangkutan, di mana kos pengangkutan diminimumkan.
• Cari pengedaran beberapa mesin untuk pelbagai jenis kerja, supaya kos pengeluaran dikurangkan.
• Penentuan masa paling singkat untuk menyiapkan kerja.

Penting! Untuk memformalkan tugas, adalah perlu untuk mencipta model yang mencerminkan parameter utama kawasan subjek. Dalam Excel, model ialah satu set formula yang menggunakan pembolehubah. Pilihan yang dipertimbangkan mencari penunjuk sedemikian bahawa fungsi objektif lebih besar (kurang) atau sama dengan nilai yang ditentukan.

Peringkat persediaan

Sebelum meletakkan fungsi pada reben, anda perlu memahami cara pilihan itu berfungsi. Sebagai contoh, terdapat maklumat mengenai penjualan barang yang ditunjukkan dalam jadual. Tugasnya adalah untuk memberikan diskaun untuk setiap item, iaitu 4.5 juta rubel. Parameter dipaparkan di dalam sel yang dipanggil sasaran. Berdasarkannya, parameter lain dikira.

Tugas kami adalah untuk mengira diskaun yang mana jumlah untuk penjualan pelbagai produk didarabkan. 2 elemen ini dihubungkan dengan formula yang ditulis seperti ini: =D13*$G$2. Di mana dalam D13 jumlah kuantiti untuk pelaksanaan ditulis, dan $G$2 ialah alamat elemen yang dikehendaki.

Menggunakan fungsi dan menetapkannya

Apabila formula sudah sedia, anda perlu menggunakan fungsi itu sendiri secara langsung:

1. Anda perlu beralih ke bahagian "Data" dan klik "Cari penyelesaian".

2. "Pilihan" akan dibuka, di mana tetapan yang diperlukan ditetapkan. Dalam baris "Optimumkan fungsi objektif:" anda harus menentukan sel tempat jumlah diskaun dipaparkan. Anda boleh menetapkan koordinat sendiri atau memilih daripada dokumen.

3. Seterusnya, anda perlu pergi ke tetapan parameter lain. Dalam bahagian "Kepada:", adalah mungkin untuk menetapkan had maksimum dan minimum atau nombor yang tepat.

4. Kemudian medan "Mengubah nilai pembolehubah:" diisi. Di sini data sel yang dikehendaki dimasukkan, yang mengandungi nilai tertentu. Koordinat didaftarkan secara bebas atau sel yang sepadan dalam dokumen diklik.

5. Kemudian tab "Menurut sekatan:" diedit, di mana sekatan pada data yang digunakan ditetapkan. Contohnya, pecahan perpuluhan atau nombor negatif dikecualikan.

6. Selepas itu, tetingkap terbuka yang membolehkan anda menambah sekatan dalam pengiraan. Baris awal mengandungi koordinat sel atau keseluruhan julat. Mengikuti syarat tugas, data sel yang dikehendaki ditunjukkan, di mana penunjuk diskaun dipaparkan. Kemudian tanda perbandingan ditentukan. Ia ditetapkan kepada "lebih besar daripada atau sama dengan" supaya nilai akhir bukan dengan tanda tolak. "Had" yang ditetapkan dalam baris 3 ialah 0 dalam situasi ini. Anda juga boleh menetapkan had dengan "Tambah". Langkah seterusnya adalah sama.

7. Apabila langkah di atas selesai, had yang ditetapkan muncul dalam baris terbesar. Senarai itu boleh menjadi besar dan bergantung pada kerumitan pengiraan, bagaimanapun, dalam keadaan tertentu, 1 syarat adalah mencukupi.

8. Di samping itu, adalah mungkin untuk memilih tetapan lanjutan lain. Di bahagian bawah sebelah kanan terdapat pilihan "Pilihan" yang membolehkan anda melakukan ini.

9. Dalam tetapan, anda boleh menetapkan "Ketepatan had" dan "Had penyelesaian". Dalam keadaan kita, tidak perlu menggunakan pilihan ini.

10. Apabila tetapan selesai, fungsi itu sendiri bermula - klik "Cari penyelesaian".

11. Selepas program melakukan pengiraan yang diperlukan dan mengeluarkan pengiraan akhir dalam sel yang diperlukan. Kemudian tetingkap dengan keputusan dibuka, di mana keputusan disimpan / dibatalkan, atau parameter carian dikonfigurasikan mengikut yang baharu. Apabila data memenuhi keperluan, penyelesaian yang ditemui disimpan. Jika anda menandai kotak "Kembali ke kotak dialog pilihan carian penyelesaian" terlebih dahulu, tetingkap dengan tetapan fungsi akan dibuka.

12. Terdapat kemungkinan pengiraan ternyata tersilap atau terdapat keperluan untuk menukar data awal untuk mendapatkan penunjuk lain. Dalam keadaan sedemikian, anda perlu membuka semula tetingkap tetapan dan semak semula maklumat.
13. Apabila data adalah tepat, kaedah alternatif boleh digunakan. Untuk tujuan ini, anda perlu mengklik pada pilihan semasa dan pilih kaedah yang paling sesuai daripada senarai yang muncul:

• Mencari Penyelesaian Menggunakan Kecerunan Umum untuk Masalah Bukan Linear. Secara lalai, pilihan ini digunakan, tetapi ada kemungkinan untuk menggunakan yang lain.
• Mencari penyelesaian bagi masalah linear berdasarkan kaedah simpleks.
• Menggunakan carian evolusi untuk menyelesaikan tugas.

Perhatian! Apabila pilihan di atas gagal menangani tugas itu, anda harus menyemak data dalam tetapan sekali lagi, kerana ini selalunya merupakan kesilapan utama dalam tugasan tersebut.

14. Apabila diskaun yang dikehendaki diterima, ia kekal menggunakannya untuk mengira jumlah diskaun bagi setiap item. Untuk tujuan ini, elemen awal lajur "Jumlah Diskaun" diserlahkan, formula ditulis «=D2*$G$2» dan tekan “Enter”. Tanda dolar diletakkan supaya apabila formula diregangkan ke garisan bersebelahan, G2 tidak berubah.

15. Amaun diskaun untuk item awal kini akan diperolehi. Kemudian anda harus menggerakkan kursor ke atas penjuru sel, apabila ia menjadi "tambah", LMB ditekan dan formula diregangkan ke garisan yang diperlukan.
16. Selepas itu, meja akan siap sepenuhnya.

Muatkan/Simpan Pilihan Carian

Pilihan ini berguna apabila menggunakan pelbagai pilihan kekangan.

1. Dalam menu Pilihan Pencari Penyelesaian, klik Muat/Simpan.
2. Masukkan julat untuk kawasan model dan klik Simpan atau Muatkan.

Apabila menyimpan model, rujukan dimasukkan ke 1 sel lajur kosong di mana model pengoptimuman akan diletakkan. Semasa pemuatan model, rujukan dimasukkan ke seluruh julat yang mengandungi model pengoptimuman.

Penting! Untuk menyimpan tetapan terakhir dalam menu Pilihan Penyelesaian, buku kerja disimpan. Setiap helaian di dalamnya mempunyai pilihan tambah Solver sendiri. Di samping itu, adalah mungkin untuk menetapkan lebih daripada 1 tugasan untuk helaian dengan mengklik butang "Muat atau Simpan" untuk menyimpan tugasan individu.

Contoh mudah menggunakan Solver

Ia perlu memuatkan bekas dengan bekas supaya jisimnya maksimum. Tangki itu mempunyai isipadu 32 meter padu. m. Sebuah kotak yang diisi mempunyai berat 20 kg, isipadunya ialah 0,15 meter padu. m. Kotak – 80 kg dan 0,5 cu. m. Jumlah bekas dikehendaki sekurang-kurangnya 110 pcs. Data disusun seperti ini:

Pembolehubah model ditandakan dengan warna hijau. Fungsi objektif diserlahkan dengan warna merah. Sekatan: mengikut bilangan bekas terkecil (lebih daripada atau sama dengan 110) dan mengikut berat (=SUMPRODUCT(B8:C8,B6:C6) – jumlah berat tar dalam bekas.

Dengan analogi, kami menganggap jumlah volum: =SUMPRODUCT(B7:C7,B8:C8). Formula sedemikian diperlukan untuk menetapkan had pada jumlah isipadu bekas. Kemudian, melalui "Cari penyelesaian", pautan dimasukkan ke elemen dengan pembolehubah, formula dan penunjuk itu sendiri (atau pautan ke sel tertentu). Sudah tentu, bilangan bekas adalah integer (ia juga merupakan had). Kami menekan "Cari penyelesaian", akibatnya kami dapati sejumlah bekas apabila jumlah jisim adalah maksimum dan semua sekatan diambil kira.

Cari penyelesaian gagal untuk mencari penyelesaian

Pemberitahuan sedemikian muncul apabila fungsi berkenaan tidak menemui gabungan skor pembolehubah yang memenuhi setiap kekangan. Apabila menggunakan kaedah Simplex, kemungkinan besar tiada penyelesaian.

Apabila kaedah untuk menyelesaikan masalah tak linear digunakan, dalam semua kes bermula dari penunjuk awal pembolehubah, ini menunjukkan bahawa penyelesaian yang mungkin adalah jauh daripada parameter tersebut. Jika anda menjalankan fungsi dengan penunjuk awal pembolehubah yang lain, maka mungkin terdapat penyelesaian.

Sebagai contoh, apabila menggunakan kaedah bukan linear, elemen jadual dengan pembolehubah tidak diisi, dan fungsi tidak menemui penyelesaian. Ini tidak bermakna tiada penyelesaian. Kini, dengan mengambil kira keputusan penilaian tertentu, data lain dimasukkan ke dalam elemen dengan pembolehubah yang hampir dengan yang diterima.

Dalam apa jua keadaan, anda harus meneliti model pada mulanya untuk ketiadaan konflik kekangan. Selalunya, ini saling berkaitan dengan pemilihan nisbah yang tidak betul atau penunjuk pengehad.

Dalam contoh di atas, penunjuk isipadu maksimum ialah 16 meter padu. m bukannya 32, kerana sekatan sedemikian bercanggah dengan penunjuk untuk bilangan minimum tempat duduk, kerana ia akan sepadan dengan bilangan 16,5 meter padu. m.

Kesimpulan

Berdasarkan ini, pilihan "Cari penyelesaian" dalam Excel akan membantu dalam menyelesaikan masalah tertentu yang agak sukar atau mustahil untuk diselesaikan dengan cara biasa. Kesukaran dalam menggunakan kaedah ini ialah pada mulanya pilihan ini disembunyikan, itulah sebabnya kebanyakan pengguna tidak menyedari kehadirannya. Di samping itu, fungsi ini agak sukar untuk dipelajari dan digunakan, tetapi dengan penyelidikan yang betul, ia akan membawa manfaat yang besar dan memudahkan pengiraan.