Tugas transportasi di Excel. Menemukan metode transportasi terbaik dari penjual ke pembeli

Excel adalah program yang sangat fungsional. Ini dapat digunakan untuk memecahkan lapisan besar masalah yang harus dihadapi seseorang dalam bisnis. Salah satu yang paling umum adalah transportasi. Bayangkan bahwa kita perlu memahami metode transportasi mana dari produsen ke pembeli akhir yang paling optimal dari segi waktu, uang, dan sumber daya lainnya. Masalah ini cukup populer, apa pun industrinya. Oleh karena itu, mari kita lihat lebih dekat bagaimana menerapkannya menggunakan Excel.

Deskripsi tugas transportasi

Jadi, kami memiliki dua rekanan yang terus berinteraksi satu sama lain. Dalam kasus kami, ini adalah pembeli dan penjual. Kita perlu mencari cara untuk mengangkut barang sedemikian rupa sehingga biayanya minimal. Untuk melakukan ini, Anda perlu menyajikan semua data dalam bentuk skema atau matriks. Di Excel, kami menggunakan opsi terakhir. Secara umum, ada dua jenis tugas transportasi:

1. Tertutup. Dalam hal ini, penawaran dan permintaan seimbang.
2. Membuka. Tidak ada kesetaraan antara penawaran dan permintaan di sini. Untuk mendapatkan solusi dari masalah ini, Anda harus terlebih dahulu membawanya ke tipe pertama, yaitu menyamakan penawaran dan permintaan. Untuk melakukan ini, Anda perlu memperkenalkan indikator tambahan – kehadiran pembeli atau penjual bersyarat. Selain itu, Anda perlu membuat perubahan tertentu pada tabel biaya.

Cara mengaktifkan fitur Temukan Solusi di Excel

Untuk mengatasi masalah transportasi di Excel, ada fungsi khusus yang disebut "Cari solusi". Ini tidak diaktifkan secara default, jadi Anda perlu melakukan langkah-langkah berikut:

1. Buka menu "File", yang terletak di sudut kiri atas jendela program.
2. Setelah itu, klik tombol dengan parameter.
3. Selanjutnya, kami menemukan subbagian "Pengaturan" dan pergi ke menu manajemen add-on. Ini adalah program kecil yang berjalan dalam lingkungan Microsoft Excel. Kami melihat bahwa pada awalnya kami mengklik menu "Add-in", dan kemudian di bagian kanan bawah kami mengatur item "Excel Add-in" dan mengklik tombol "Go". Semua tindakan yang diperlukan disorot dengan persegi panjang merah dan panah.
4. Selanjutnya, aktifkan add-in "Cari solusi", setelah itu kami mengkonfirmasi tindakan kami dengan menekan tombol OK. Berdasarkan deskripsi setting, kita dapat melihat bahwa setting dirancang untuk menganalisis data yang kompleks, seperti ilmiah dan keuangan.
5. Setelah itu, buka tab "Data", di mana kita melihat tombol baru, yang disebut sama dengan add-in. Itu dapat ditemukan di grup alat Analisis.

Tetap hanya mengklik tombol ini, dan kami melanjutkan ke solusi masalah transportasi. Tetapi sebelum itu, kita harus berbicara lebih banyak tentang alat Solver di Excel. Ini adalah add-on Excel khusus yang memungkinkan untuk menemukan solusi tercepat untuk suatu masalah. Fitur karakteristik adalah pertimbangan batasan yang ditetapkan pengguna pada tahap persiapan. Secara sederhana, ini adalah subrutin yang memungkinkan untuk menentukan cara terbaik untuk mencapai tugas tertentu. Tugas-tugas tersebut dapat mencakup hal-hal berikut:

1. Investasi, memuat gudang atau aktivitas serupa lainnya. Termasuk pengiriman barang.
2. Jalan terbaik. Ini termasuk tujuan seperti mencapai keuntungan maksimum dengan biaya minimum, bagaimana mencapai kualitas terbaik dengan sumber daya yang tersedia, dan sebagainya.

Selain tugas transportasi, add-on ini juga digunakan untuk tujuan berikut:

1. Pengembangan rencana produksi. Artinya, berapa banyak unit produk yang perlu diproduksi untuk mencapai pendapatan maksimum.
2. Temukan distribusi tenaga kerja untuk berbagai jenis pekerjaan sehingga total biaya untuk memproduksi suatu produk atau jasa adalah yang terkecil.
3. Tetapkan waktu minimum yang diperlukan untuk menyelesaikan semua pekerjaan.

Seperti yang Anda lihat, tugasnya sangat berbeda. Aturan universal untuk menerapkan add-in ini adalah bahwa sebelum menyelesaikan masalah, perlu untuk membuat model yang sesuai dengan karakteristik utama dari masalah yang diajukan. Model adalah kumpulan fungsi yang menggunakan variabel sebagai argumennya. Artinya, nilai-nilai yang bisa berubah.

Penting untuk dicatat bahwa optimasi serangkaian nilai dilakukan secara eksklusif pada satu indikator, yang disebut fungsi tujuan.

Add-in Solver menghitung nilai yang berbeda dari variabel yang diteruskan ke fungsi tujuan sedemikian rupa sehingga maksimum, minimum, atau sama dengan nilai tertentu (ini tepatnya pembatasan). Ada fungsi lain yang agak mirip dalam prinsip operasinya, dan yang sering dikacaukan dengan "Mencari solusi". Ini disebut "Pilihan Opsi". Tetapi jika Anda menggali lebih dalam, perbedaan di antara mereka sangat besar:

1. Fungsi Goal Seek tidak bekerja dengan lebih dari satu variabel.
2. Itu tidak memberikan kemampuan untuk menetapkan batasan pada variabel.
3. Ia hanya dapat menentukan kesetaraan fungsi tujuan dengan nilai tertentu, tetapi tidak memungkinkan untuk menemukan maksimum dan minimum. Oleh karena itu, tidak cocok untuk tugas kita.
4. Mampu menghitung secara efisien hanya jika model tipe linier. Jika modelnya non-linier, maka ia menemukan nilai yang paling dekat dengan nilai aslinya.

Tugas transportasi jauh lebih rumit dalam strukturnya, sehingga add-on "Pemilihan parameter" tidak cukup untuk ini. Mari kita lihat lebih dekat bagaimana menerapkan fungsi "Cari Solusi" dalam praktik menggunakan contoh masalah transportasi.

Contoh pemecahan masalah transportasi di Excel

Untuk mendemonstrasikan dengan jelas bagaimana memecahkan masalah transportasi dalam praktik di Excel, mari kita berikan sebuah contoh.

Tugas kondisi

Misalkan kita memiliki 6 penjual dan 7 pembeli. Permintaan dan penawaran di antara mereka didistribusikan masing-masing dengan cara berikut: 36, 51, 32, 44, 35 dan 38 unit adalah penjual dan 33, 48, 30, 36, 33, 24 dan 32 unit adalah pembeli. Jika Anda menjumlahkan semua nilai ini, Anda akan menemukan bahwa penawaran dan permintaan seimbang. Oleh karena itu, masalah ini adalah tipe tertutup, yang diselesaikan dengan sangat sederhana.

Selain itu, kami memiliki informasi tentang berapa banyak yang harus Anda keluarkan untuk transportasi dari titik A ke titik B (mereka disorot dalam sel kuning dalam contoh).

Solusi – algoritma langkah demi langkah

Sekarang, setelah kita membiasakan diri dengan tabel dengan data awal, kita dapat menggunakan algoritma berikut untuk menyelesaikan masalah ini:

1. Pertama, kita buat tabel yang terdiri dari 6 baris dan 7 kolom.
2. Setelah itu, kami pergi ke sel mana pun yang tidak berisi nilai apa pun dan pada saat yang sama terletak di luar tabel yang baru dibuat dan menyisipkan fungsinya. Untuk melakukan ini, klik tombol fx, yang terletak di sebelah kiri baris entri fungsi.
3. Kami memiliki jendela di mana kami perlu memilih kategori "Matematika". Fungsi apa yang kami minati? Yang disorot dalam tangkapan layar ini. Fungsi SUMPRODUK mengalikan rentang atau larik di antara mereka sendiri dan menjumlahkannya. Hanya apa yang kita butuhkan. Setelah itu, tekan tombol OK.
4. Selanjutnya, sebuah jendela akan muncul di layar di mana Anda perlu menentukan parameter fungsi. Mereka adalah sebagai berikut:
   1. Array 1. Ini adalah argumen pertama di mana kita menulis rentang yang disorot dengan warna kuning. Anda dapat mengatur parameter fungsi baik menggunakan keyboard atau dengan memilih area yang sesuai dengan tombol kiri mouse.
   2. Array 2. Ini adalah argumen kedua, yang merupakan tabel yang baru dibuat. Tindakan dilakukan dengan cara yang sama.

Konfirmasikan tindakan Anda dengan menekan tombol OK.

1. Setelah itu, kita melakukan klik kiri mouse pada sel yang berfungsi sebagai kiri atas pada tabel yang baru dibuat. Sekarang klik tombol fungsi insert lagi.
2. Kami memilih kategori yang sama seperti pada kasus sebelumnya. Tapi kali ini kami tertarik dengan fungsinya JUMLAH.
3. Sekarang sampai pada tahap pengisian argumen. Sebagai argumen pertama, kita menulis baris paling atas dari tabel yang kita buat di awal. Dengan cara yang sama seperti sebelumnya, ini dapat dilakukan dengan memilih sel-sel ini pada lembar, atau secara manual. Kami mengkonfirmasi tindakan kami dengan menekan tombol OK.
4. Kita akan melihat hasilnya di sel dengan fungsi. Dalam hal ini, itu nol. Selanjutnya, pindahkan kursor ke sudut kanan bawah, setelah itu akan muncul penanda pelengkapan otomatis. Itu terlihat seperti boneka hitam kecil. Jika muncul, tahan tombol kiri mouse dan pindahkan kursor ke sel terakhir di tabel kita.
5. Ini memberi kita kesempatan untuk mentransfer rumus ke semua sel lain dan mendapatkan hasil yang benar tanpa harus melakukan perhitungan tambahan.
6. Langkah selanjutnya adalah memilih sel kiri atas dan menempelkan fungsinya JUMLAH ke dalam dirinya. Setelah itu, kami memasukkan argumen dan menggunakan penanda pelengkapan otomatis untuk mengisi semua sel yang tersisa.
7. Setelah itu, kami melanjutkan langsung ke pemecahan masalah. Untuk melakukan ini, kami akan menggunakan add-on yang kami sertakan sebelumnya. Buka tab "Data", dan di sana kami menemukan alat "Cari solusi". Kami mengklik tombol ini.
8. Sekarang sebuah jendela telah muncul di depan mata kita, di mana Anda dapat mengonfigurasi parameter add-on kami. Mari kita lihat masing-masing opsi ini:
   1. Optimalkan fungsi tujuan. Di sini kita perlu memilih sel yang berisi fungsi SUMPRODUK. Kami melihat bahwa opsi ini memungkinkan untuk memilih fungsi yang solusi akan dicari.
   2. Sebelum. Di sini kami mengatur opsi "Minimum".
   3. Dengan mengubah sel-sel variabel. Di sini kami menunjukkan rentang yang sesuai dengan tabel yang kami buat di awal (dengan pengecualian untuk meringkas baris dan kolom).
   4. Tunduk pada pembatasan. Di sini kita perlu menambahkan batasan dengan mengklik tombol Tambah.
   5. Kami ingat batasan seperti apa yang perlu kami buat – jumlah nilai permintaan pembeli dan penawaran penjual harus sama.
9. Tugas pembatasan dilakukan sebagai berikut:
   1. Tautan ke sel. Di sini kita memasukkan rentang tabel untuk perhitungan.
   2. Ketentuan. Ini adalah operasi matematika di mana rentang yang ditentukan dalam bidang input pertama diperiksa.
   3. Nilai kondisi atau kendala. Di sini kita memasukkan kolom yang sesuai di tabel sumber.
   4. Setelah semua langkah selesai, klik tombol OK, dengan demikian mengonfirmasi tindakan kami.

Kami melakukan operasi yang sama persis untuk baris teratas, dengan menetapkan kondisi berikut: mereka harus sama.

Langkah selanjutnya adalah mengatur kondisi. Kita perlu menetapkan kriteria berikut untuk jumlah sel dalam tabel – lebih besar dari atau sama dengan nol, bilangan bulat. Akibatnya, kami memiliki daftar kondisi di mana masalah diselesaikan. Di sini Anda perlu memastikan bahwa kotak centang di sebelah opsi "Buat variabel tanpa batas non-negatif" dicentang. Juga, dalam situasi kami, diperlukan metode untuk memecahkan masalah yang dipilih - "Mencari solusi untuk masalah nonlinier metode OPG". Sekarang kita dapat dengan aman mengatakan bahwa pengaturan sudah selesai. Karena itu, tinggal melakukan perhitungan saja. Untuk melakukan ini, klik tombol "Temukan solusi".

Setelah itu, semua data akan dihitung secara otomatis, dan kemudian Excel akan menampilkan jendela dengan hasilnya. Penting untuk memeriksa ulang pengoperasian komputer, karena kesalahan mungkin terjadi jika kondisi sebelumnya tidak diatur dengan benar. Jika semuanya sudah benar, maka klik tombol "OK" dan lihat tabel yang sudah jadi.

Jika ternyata tugas kita menjadi tipe terbuka, maka ini buruk, karena Anda perlu mengedit tabel sumber agar tugas berubah menjadi tertutup. Namun, ketika ini dilakukan, algoritma yang tersisa akan sama.

Kesimpulan

Seperti yang Anda lihat, Excel juga dapat digunakan untuk perhitungan yang sangat kompleks, yang sekilas tidak tersedia untuk program komputer sederhana yang diinstal di hampir semua orang. Namun, itu. Hari ini kita telah membahas penggunaan tingkat lanjut. Topik ini tidak begitu sederhana, tetapi seperti yang mereka katakan, jalan akan dikuasai oleh yang berjalan. Hal utama adalah mengikuti rencana tindakan, dan secara akurat melakukan semua tindakan yang ditunjukkan di atas. Maka tidak akan ada kesalahan, dan program akan secara mandiri melakukan semua perhitungan yang diperlukan. Tidak perlu memikirkan fungsi mana yang akan digunakan dan sebagainya.