Contents [show]
Perumusan masalah
Katakan syarikat tempat anda bekerja mempunyai tiga gudang, dari mana barang pergi ke lima kedai anda yang tersebar di seluruh Moscow.
Setiap kedai mampu menjual kuantiti barang tertentu yang kami ketahui. Setiap gudang mempunyai kapasiti terhad. Tugasnya adalah untuk memilih secara rasional dari gudang yang mana kedai untuk menghantar barangan untuk meminimumkan jumlah kos pengangkutan.
Sebelum memulakan pengoptimuman, adalah perlu untuk menyusun jadual ringkas pada helaian Excel - model matematik kami yang menerangkan keadaan:
Difahamkan bahawa:
- Meja kuning muda (C4:G6) menerangkan kos penghantaran satu item dari setiap gudang ke setiap kedai.
- Sel ungu (C15:G14) menerangkan kuantiti barang yang diperlukan untuk setiap kedai untuk dijual.
- Sel merah (J10:J13) memaparkan kapasiti setiap gudang - jumlah maksimum barang yang boleh disimpan oleh gudang.
- Sel kuning (C13:G13) dan biru (H10:H13) ialah jumlah baris dan lajur untuk sel hijau, masing-masing.
- Jumlah kos penghantaran (J18) dikira sebagai jumlah produk bilangan barangan dan kos penghantaran yang sepadan – untuk pengiraan, fungsi ini digunakan di sini SUMPRODUCT (SUMPRODUCT).
Oleh itu, tugas kami dikurangkan kepada pemilihan nilai optimum sel hijau. Dan supaya jumlah keseluruhan baris (sel biru) tidak melebihi kapasiti gudang (sel merah), dan pada masa yang sama setiap kedai menerima kuantiti barang yang perlu dijual (jumlah untuk setiap kedai dalam sel kuning hendaklah sedekat mungkin dengan keperluan – sel ungu).
Penyelesaian
Dalam matematik, masalah seperti memilih pengagihan sumber yang optimum telah dirumus dan diterangkan untuk masa yang lama. Dan, sudah tentu, cara untuk menyelesaikannya telah lama dibangunkan bukan dengan penghitungan tumpul (yang sangat panjang), tetapi dalam bilangan lelaran yang sangat kecil. Excel menyediakan pengguna dengan fungsi sedemikian menggunakan tambahan. Penyelesaian Carian (Penyelesai) daripada tab Tarikh (Tarikh):
Jika pada tab Tarikh Excel anda tidak mempunyai arahan sedemikian – tidak mengapa – ini bermakna add-in itu belum disambungkan lagi. Untuk mengaktifkannya buka File, Kemudian pilih Parameter - Add-ons - Info (Pilihan — Tambahan — Pergi Ke). Dalam tetingkap yang terbuka, tandai kotak di sebelah baris yang kami perlukan Penyelesaian Carian (Penyelesai).
Mari jalankan add-on:
Dalam tetingkap ini, anda perlu menetapkan parameter berikut:
- Optimumkan fungsi sasaran (Tetapkan twang sel) – di sini adalah perlu untuk menunjukkan matlamat utama akhir pengoptimuman kami, iaitu kotak merah jambu dengan jumlah kos penghantaran (J18). Sel sasaran boleh diminimumkan (jika ia adalah perbelanjaan, seperti dalam kes kami), dimaksimumkan (jika ia, sebagai contoh, keuntungan) atau cuba membawanya ke nilai tertentu (contohnya, sesuai dengan anggaran yang diperuntukkan).
- Menukar Sel Boleh Ubah (By berubah sel) – di sini kami menunjukkan sel hijau (C10: G12), dengan mengubah nilai yang kami ingin capai hasil kami – kos penghantaran minimum.
- Selaras dengan sekatan (Tajuk kepada yang Kekangan) – senarai sekatan yang mesti diambil kira semasa mengoptimumkan. Untuk menambah sekatan pada senarai, klik butang Tambah (Tambah) dan masukkan syarat dalam tetingkap yang muncul. Dalam kes kami, ini akan menjadi kekangan permintaan:
dan had pada volum maksimum gudang:
Sebagai tambahan kepada batasan jelas yang berkaitan dengan faktor fizikal (kapasiti gudang dan cara pengangkutan, kekangan belanjawan dan masa, dsb.), kadangkala perlu menambah sekatan "khas untuk Excel". Jadi, sebagai contoh, Excel boleh dengan mudah mengatur untuk anda "mengoptimumkan" kos penghantaran dengan menawarkan untuk mengangkut barang dari kedai kembali ke gudang - kos akan menjadi negatif, iaitu kami akan mendapat keuntungan! 🙂
Untuk mengelakkan perkara ini daripada berlaku, sebaiknya biarkan kotak semak didayakan. Jadikan Pembolehubah Tanpa Had Bukan Negatif atau bahkan kadangkala secara eksplisit mendaftarkan detik sedemikian dalam senarai sekatan.
Selepas menetapkan semua parameter yang diperlukan, tetingkap sepatutnya kelihatan seperti ini:
Dalam senarai juntai bawah Pilih kaedah penyelesaian, anda juga perlu memilih kaedah matematik yang sesuai untuk menyelesaikan pilihan tiga pilihan:
- Kaedah simplex adalah kaedah yang mudah dan pantas untuk menyelesaikan masalah linear, iaitu masalah di mana output bergantung secara linear kepada input.
- Kaedah Kecerunan Turun Umum (OGG) – untuk masalah bukan linear, di mana terdapat kebergantungan bukan linear yang kompleks antara data input dan output (contohnya, pergantungan jualan pada kos pengiklanan).
- Pencarian evolusi untuk penyelesaian – kaedah pengoptimuman yang agak baharu berdasarkan prinsip evolusi biologi (hello Darwin). Kaedah ini berfungsi berkali-kali lebih lama daripada dua yang pertama, tetapi boleh menyelesaikan hampir semua masalah (tidak linear, diskret).
Tugas kami jelas linear: menghantar 1 keping – membelanjakan 40 rubel, menghantar 2 keping – menghabiskan 80 rubel. dan lain-lain, jadi kaedah simpleks adalah pilihan terbaik.
Sekarang setelah data untuk pengiraan dimasukkan, tekan butang Mencari penyelesaian (Selesaikan)untuk memulakan pengoptimuman. Dalam kes yang teruk dengan banyak perubahan sel dan kekangan, mencari penyelesaian boleh mengambil masa yang lama (terutama dengan kaedah evolusi), tetapi tugas kami untuk Excel tidak akan menjadi masalah – dalam beberapa saat kami akan mendapat keputusan berikut :
Perhatikan betapa menariknya volum bekalan diedarkan di antara kedai-kedai, sambil tidak melebihi kapasiti gudang kami dan memenuhi semua permintaan untuk bilangan barangan yang diperlukan untuk setiap kedai.
Jika penyelesaian yang ditemui sesuai dengan kami, maka kami boleh menyimpannya, atau kembalikan kepada nilai asal dan cuba lagi dengan parameter lain. Anda juga boleh menyimpan gabungan parameter yang dipilih sebagai senario. Atas permintaan pengguna, Excel boleh membina tiga jenis Laporan mengenai masalah yang diselesaikan pada helaian berasingan: laporan mengenai keputusan, laporan mengenai kestabilan matematik penyelesaian dan laporan mengenai had (sekatan) penyelesaian, bagaimanapun, dalam kebanyakan kes, ia hanya menarik minat pakar .
Walau bagaimanapun, terdapat situasi di mana Excel tidak dapat mencari penyelesaian yang sesuai. Adalah mungkin untuk mensimulasikan kes sedemikian jika kita menunjukkan dalam contoh kita keperluan kedai dalam jumlah yang lebih besar daripada jumlah kapasiti gudang. Kemudian, apabila melakukan pengoptimuman, Excel akan cuba mendekati penyelesaian yang mungkin, dan kemudian memaparkan mesej bahawa penyelesaian itu tidak dapat ditemui. Namun begitu, walaupun dalam kes ini, kami mempunyai banyak maklumat berguna – khususnya, kami dapat melihat "pautan lemah" proses perniagaan kami dan memahami bidang untuk penambahbaikan.
Contoh yang dipertimbangkan, sudah tentu, adalah agak mudah, tetapi mudah skala untuk menyelesaikan masalah yang lebih kompleks. Sebagai contoh:
- Pengoptimuman pengagihan sumber kewangan mengikut butiran perbelanjaan dalam rancangan perniagaan atau belanjawan projek. Sekatan, dalam kes ini, ialah jumlah pembiayaan dan masa projek, dan matlamat pengoptimuman adalah untuk memaksimumkan keuntungan dan meminimumkan kos projek.
- Pengoptimuman penjadualan pekerja untuk meminimumkan dana gaji perusahaan. Sekatan, dalam kes ini, akan menjadi kehendak setiap pekerja mengikut jadual pekerjaan dan keperluan jadual kakitangan.
- Pengoptimuman pelaburan pelaburan – keperluan untuk mengagihkan dana dengan betul antara beberapa bank, sekuriti atau saham perusahaan, sekali lagi, untuk memaksimumkan keuntungan atau (jika lebih penting) meminimumkan risiko.
Walau apa pun, add-on Penyelesaian Carian (Penyelesai) ialah alat Excel yang sangat berkuasa dan cantik serta patut diberi perhatian anda, kerana ia boleh membantu dalam banyak situasi sukar yang perlu anda hadapi dalam perniagaan moden.