Konten
Rumusan masalah
Misalkan perusahaan tempat Anda bekerja memiliki tiga gudang, dari mana barang dikirim ke lima toko Anda yang tersebar di seluruh Moskow.
Setiap toko dapat menjual sejumlah barang yang kita ketahui. Masing-masing gudang memiliki kapasitas yang terbatas. Tugasnya adalah memilih secara rasional dari gudang mana toko mana untuk mengirimkan barang untuk meminimalkan total biaya transportasi.
Sebelum memulai pengoptimalan, Anda perlu menyusun tabel sederhana pada lembar Excel – model matematika kami yang menjelaskan situasinya:
Dipahami bahwa:
- Tabel kuning muda (C4:G6) menjelaskan biaya pengiriman satu item dari setiap gudang ke setiap toko.
- Sel ungu (C15:G14) menggambarkan jumlah barang yang dibutuhkan setiap toko untuk dijual.
- Sel merah (J10:J13) menampilkan kapasitas setiap gudang – jumlah maksimum barang yang dapat ditampung oleh gudang.
- Sel kuning (C13:G13) dan biru (H10:H13) masing-masing adalah jumlah baris dan kolom untuk sel hijau.
- Total biaya pengiriman (J18) dihitung sebagai jumlah produk dari jumlah barang dan biaya pengiriman yang sesuai – untuk perhitungan, fungsi digunakan di sini SUMPRODUK (SUMPRODUK).
Dengan demikian, tugas kita direduksi menjadi pemilihan nilai optimal sel hijau. Dan agar jumlah total untuk jalur (sel biru) tidak melebihi kapasitas gudang (sel merah), dan pada saat yang sama setiap toko menerima jumlah barang yang perlu dijual (jumlah untuk setiap toko di sel kuning harus sedekat mungkin dengan persyaratan – sel ungu).
Solusi
Dalam matematika, masalah pemilihan distribusi sumber daya yang optimal telah dirumuskan dan dijelaskan untuk waktu yang lama. Dan, tentu saja, cara untuk menyelesaikannya telah lama dikembangkan bukan dengan enumerasi tumpul (yang sangat panjang), tetapi dalam jumlah iterasi yang sangat kecil. Excel memberi pengguna fungsionalitas seperti itu menggunakan add-in. Solusi Pencarian (Pemecah) dari tab Data (Tanggal):
Jika di tab Data Excel Anda tidak memiliki perintah seperti itu – tidak apa-apa – artinya add-in belum terhubung. Untuk mengaktifkannya buka File, Lalu pilih parameter - Pengaya - Tentang Kami (Opsi — Add-In — Pergi Ke). Di jendela yang terbuka, centang kotak di sebelah baris yang kita butuhkan Solusi Pencarian (Pemecah).
Mari kita jalankan add-on:
Di jendela ini, Anda perlu mengatur parameter berikut:
- Optimalkan fungsi target (Setel keuang sel) – di sini perlu untuk menunjukkan tujuan utama akhir dari optimasi kami, yaitu kotak merah muda dengan total biaya pengiriman (J18). Sel target dapat diminimalkan (jika itu adalah pengeluaran, seperti dalam kasus kami), dimaksimalkan (jika, misalnya, untung) atau mencoba membawanya ke nilai tertentu (misalnya, sesuai dengan anggaran yang dialokasikan).
- Mengubah Sel Variabel (By mengubah sel) – di sini kami menunjukkan sel hijau (C10: G12), dengan memvariasikan nilai yang ingin kami capai hasil kami – biaya pengiriman minimum.
- Konsisten dengan batasan (Subjek untuk itu Kendala) – daftar batasan yang harus diperhitungkan saat mengoptimalkan. Untuk menambahkan batasan ke daftar, klik tombol Add (Menambahkan) dan masukkan kondisi di jendela yang muncul. Dalam kasus kami, ini akan menjadi kendala permintaan:
dan batas volume maksimum gudang:
Selain keterbatasan yang jelas terkait dengan faktor fisik (kapasitas gudang dan sarana transportasi, keterbatasan anggaran dan waktu, dll), terkadang perlu ditambahkan batasan “khusus untuk Excel”. Jadi, misalnya, Excel dapat dengan mudah mengatur agar Anda "mengoptimalkan" biaya pengiriman dengan menawarkan untuk mengangkut barang dari toko kembali ke gudang – biayanya akan menjadi negatif, yaitu kita akan mendapat untung!
Untuk mencegah hal ini terjadi, yang terbaik adalah membiarkan kotak centang diaktifkan. Jadikan Variabel Tidak Terbatas Non-Negatif atau bahkan terkadang secara eksplisit mendaftarkan momen seperti itu dalam daftar batasan.
Setelah mengatur semua parameter yang diperlukan, jendela akan terlihat seperti ini:
Dalam daftar drop-down Pilih metode penyelesaian, Anda juga perlu memilih metode matematika yang sesuai untuk menyelesaikan pilihan dari tiga opsi:
- Metode simpleks adalah metode sederhana dan cepat untuk menyelesaikan masalah linier, yaitu masalah di mana output bergantung secara linier pada input.
- Metode Gradien Turunan Umum (OGG) – untuk masalah non-linier, di mana terdapat ketergantungan non-linier yang kompleks antara data input dan output (misalnya, ketergantungan penjualan pada biaya iklan).
- Pencarian evolusioner untuk solusi – metode optimasi yang relatif baru berdasarkan prinsip-prinsip evolusi biologis (halo Darwin). Metode ini bekerja berkali-kali lebih lama daripada dua yang pertama, tetapi dapat menyelesaikan hampir semua masalah (nonlinier, diskrit).
Tugas kami jelas linier: mengirim 1 potong - menghabiskan 40 rubel, mengirim 2 potong - menghabiskan 80 rubel. dll, jadi metode simpleks adalah pilihan terbaik.
Sekarang setelah data untuk perhitungan dimasukkan, tekan tombol Mencari solusi (Menyelesaikan)untuk memulai pengoptimalan. Dalam kasus yang parah dengan banyak sel yang berubah dan kendala, menemukan solusi dapat memakan waktu lama (terutama dengan metode evolusi), tetapi tugas kita untuk Excel tidak akan menjadi masalah – dalam beberapa saat kita akan mendapatkan hasil berikut :
Perhatikan betapa menariknya volume pasokan didistribusikan di antara toko-toko, sementara tidak melebihi kapasitas gudang kami dan memenuhi semua permintaan jumlah barang yang dibutuhkan untuk setiap toko.
Jika solusi yang ditemukan cocok untuk kita, maka kita dapat menyimpannya, atau memutar kembali ke nilai aslinya dan coba lagi dengan parameter lain. Anda juga dapat menyimpan kombinasi parameter yang dipilih sebagai Contoh. Atas permintaan pengguna, Excel dapat membangun tiga jenis: laporan tentang masalah yang diselesaikan pada lembar terpisah: laporan hasil, laporan stabilitas matematis solusi dan laporan batas (pembatasan) solusi, namun, dalam banyak kasus, mereka hanya menarik bagi spesialis .
Namun, ada situasi di mana Excel tidak dapat menemukan solusi yang sesuai. Dimungkinkan untuk mensimulasikan kasus seperti itu jika kami menunjukkan dalam contoh kami persyaratan toko dalam jumlah yang lebih besar dari total kapasitas gudang. Kemudian, saat melakukan pengoptimalan, Excel akan mencoba sedekat mungkin dengan solusi, lalu menampilkan pesan bahwa solusi tidak dapat ditemukan. Namun demikian, bahkan dalam kasus ini, kami memiliki banyak informasi berguna – khususnya, kami dapat melihat “tautan lemah” dari proses bisnis kami dan memahami area yang perlu ditingkatkan.
Contoh yang dipertimbangkan, tentu saja, relatif sederhana, tetapi mudah disesuaikan untuk memecahkan masalah yang jauh lebih kompleks. Sebagai contoh:
- Optimalisasi distribusi sumber daya keuangan dengan item pengeluaran dalam rencana bisnis atau anggaran proyek. Pembatasan, dalam hal ini, akan menjadi jumlah pembiayaan dan waktu proyek, dan tujuan optimasi adalah untuk memaksimalkan keuntungan dan meminimalkan biaya proyek.
- Optimalisasi penjadwalan karyawan untuk meminimalkan dana upah perusahaan. Pembatasan, dalam hal ini, akan menjadi keinginan setiap karyawan sesuai dengan jadwal kerja dan persyaratan tabel kepegawaian.
- Optimalisasi investasi investasi – kebutuhan untuk mendistribusikan dana dengan benar antara beberapa bank, sekuritas atau saham perusahaan untuk, sekali lagi, untuk memaksimalkan keuntungan atau (jika lebih penting) meminimalkan risiko.
Bagaimanapun, add-on Solusi Pencarian (pemecah) adalah alat Excel yang sangat kuat dan indah dan patut Anda perhatikan, karena dapat membantu dalam banyak situasi sulit yang harus Anda hadapi dalam bisnis modern.