Tugas pengangkutan dalam Excel. Mencari kaedah pengangkutan terbaik daripada penjual kepada pembeli

Excel ialah program yang sangat berfungsi. Ia boleh digunakan untuk menyelesaikan lapisan besar masalah yang perlu dihadapi dalam perniagaan. Salah satu yang paling biasa ialah pengangkutan. Bayangkan bahawa kita perlu memahami kaedah pengangkutan dari pengilang kepada pembeli akhir yang paling optimum dari segi masa, wang dan sumber lain. Masalah ini agak popular, tidak kira dalam industri apa pun perniagaan itu. Oleh itu, mari kita lihat dengan lebih dekat cara melaksanakannya menggunakan Excel.

Penerangan tentang tugas pengangkutan

Jadi, kami mempunyai dua rakan niaga yang sentiasa berinteraksi antara satu sama lain. Dalam kes kami, ini adalah pembeli dan penjual. Kita perlu memikirkan cara mengangkut barang dengan cara yang kosnya minimum. Untuk melakukan ini, anda perlu membentangkan semua data dalam bentuk skema atau matriks. Dalam Excel, kami menggunakan pilihan terakhir. Secara umum, terdapat dua jenis tugas pengangkutan:

1. tertutup. Dalam kes ini, penawaran dan permintaan adalah seimbang.
2. Buka. Tidak ada persamaan antara penawaran dan permintaan di sini. Untuk mendapatkan penyelesaian kepada masalah ini, anda mesti membawanya kepada jenis pertama, menyamakan penawaran dan permintaan. Untuk melakukan ini, anda perlu memperkenalkan penunjuk tambahan - kehadiran pembeli atau penjual bersyarat. Di samping itu, anda perlu membuat perubahan tertentu pada jadual kos.

Bagaimana untuk mendayakan ciri Cari Penyelesaian dalam Excel

Untuk menyelesaikan masalah pengangkutan dalam Excel, terdapat fungsi khas yang dipanggil "Cari penyelesaian". Ia tidak didayakan secara lalai, jadi anda perlu melakukan langkah berikut:

1. Buka menu "Fail", yang terletak di sudut kiri atas tetingkap program.
2. Selepas itu, klik pada butang dengan parameter.
3. Seterusnya, kami mencari subseksyen "Tetapan" dan pergi ke menu pengurusan alat tambah. Ini adalah program kecil yang dijalankan dalam persekitaran Microsoft Excel. Kami melihat bahawa pada mulanya kami mengklik pada menu "Add-in", dan kemudian di bahagian bawah sebelah kanan kami menetapkan item "Excel Add-in" dan mengklik pada butang "Go". Semua tindakan yang diperlukan diserlahkan dengan segi empat tepat merah dan anak panah.
4. Seterusnya, hidupkan tambahan "Cari penyelesaian", selepas itu kami mengesahkan tindakan kami dengan menekan butang OK. Berdasarkan huraian tetapan, kita dapat melihat bahawa ia direka untuk menganalisis data yang kompleks, seperti saintifik dan kewangan.
5. Selepas itu, pergi ke tab "Data", di mana kita melihat butang baharu, yang dipanggil sama dengan tambahan. Ia boleh didapati dalam kumpulan alat Analisis.

Ia kekal hanya untuk mengklik pada butang ini, dan kami meneruskan kepada penyelesaian masalah pengangkutan. Tetapi sebelum itu, kita harus bercakap lebih sedikit tentang alat Penyelesai dalam Excel. Ini ialah alat tambah Excel khas yang membolehkan anda mencari penyelesaian terpantas kepada masalah. Ciri ciri ialah pertimbangan sekatan yang ditetapkan pengguna pada peringkat penyediaan. Secara ringkas, ini adalah subrutin yang membolehkan anda menentukan cara terbaik untuk mencapai tugas tertentu. Tugas sedemikian mungkin termasuk yang berikut:

1. Melabur, memuatkan gudang atau sebarang aktiviti lain yang serupa. Termasuk penghantaran barang.
2. Cara yang paling baik. Ini termasuk objektif seperti mencapai keuntungan maksimum pada kos minimum, bagaimana untuk mencapai kualiti terbaik dengan sumber yang ada, dan sebagainya.

Selain tugas pengangkutan, alat tambah ini juga digunakan untuk tujuan berikut:

1. Pembangunan rancangan pengeluaran. Iaitu, berapa unit sesuatu produk yang perlu dihasilkan untuk mencapai pendapatan maksimum.
2. Cari agihan buruh untuk jenis kerja yang berbeza supaya jumlah kos pengeluaran produk atau perkhidmatan adalah yang paling kecil.
3. Tetapkan masa minimum yang diperlukan untuk menyelesaikan semua kerja.

Seperti yang anda lihat, tugasnya sangat berbeza. Peraturan universal untuk menggunakan tambahan ini ialah sebelum menyelesaikan masalah, adalah perlu untuk mencipta model yang sepadan dengan ciri utama masalah yang dikemukakan. Model ialah koleksi fungsi yang menggunakan pembolehubah sebagai hujahnya. Iaitu, nilai yang boleh berubah.

Adalah penting untuk ambil perhatian bahawa pengoptimuman satu set nilai dijalankan secara eksklusif pada satu penunjuk, yang dipanggil fungsi objektif.

Tambahan Solver menghitung nilai berbeza pembolehubah yang dihantar ke fungsi objektif sedemikian rupa sehingga ia adalah maksimum, minimum atau sama dengan nilai tertentu (ini tepatnya sekatan). Terdapat satu lagi fungsi yang agak serupa dalam prinsip operasinya, dan yang sering dikelirukan dengan "Cari penyelesaian". Ia dipanggil "Pilihan Pilihan". Tetapi jika anda menggali lebih dalam, perbezaan di antara mereka sangat besar:

1. Fungsi Goal Seek tidak berfungsi dengan lebih daripada satu pembolehubah.
2. Ia tidak menyediakan keupayaan untuk menetapkan had pada pembolehubah.
3. Ia dapat menentukan hanya kesamaan fungsi objektif kepada nilai tertentu, tetapi tidak memungkinkan untuk mencari maksimum dan minimum. Oleh itu, ia tidak sesuai untuk tugas kita.
4. Mampu mengira dengan cekap hanya jika model jenis linear. Jika model bukan linear, maka ia mencari nilai yang paling hampir dengan nilai asal.

Tugas pengangkutan jauh lebih rumit dalam strukturnya, jadi alat tambah "Pemilihan parameter" tidak mencukupi untuk ini. Mari kita lihat lebih dekat cara melaksanakan fungsi "Cari Penyelesaian" dalam amalan menggunakan contoh masalah pengangkutan.

Contoh penyelesaian masalah pengangkutan dalam Excel

Untuk menunjukkan dengan jelas cara menyelesaikan masalah pengangkutan dalam amalan dalam Excel, mari berikan contoh.

Tugas syarat

Katakan kita mempunyai 6 penjual dan 7 pembeli. Permintaan dan penawaran antara mereka diagihkan masing-masing mengikut 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 merumuskan semua nilai ini, anda akan mendapati bahawa penawaran dan permintaan adalah seimbang. Oleh itu, masalah ini adalah jenis tertutup, yang diselesaikan dengan sangat mudah.

Di samping itu, kami mempunyai maklumat tentang amaun yang anda perlu belanjakan untuk pengangkutan dari titik A ke titik B (ia diserlahkan dalam sel kuning dalam contoh).

Penyelesaian - algoritma langkah demi langkah

Sekarang, selepas kita membiasakan diri dengan jadual dengan data awal, kita boleh menggunakan algoritma berikut untuk menyelesaikan masalah ini:

1. Pertama, kami membuat jadual yang terdiri daripada 6 baris dan 7 lajur.
2. Selepas itu, kami pergi ke mana-mana sel yang tidak mengandungi sebarang nilai dan pada masa yang sama terletak di luar jadual yang baru dibuat dan masukkan fungsi tersebut. Untuk melakukan ini, klik pada butang fx, yang terletak di sebelah kiri baris kemasukan fungsi.
3. Kami mempunyai tetingkap di mana kita perlu memilih kategori "Matematik". Fungsi apa yang kita minat? Yang diserlahkan dalam tangkapan skrin ini. Fungsi SUMPRODUCT mendarab julat atau tatasusunan antara mereka sendiri dan menjumlahkannya. Hanya apa yang kita perlukan. Selepas itu, tekan kekunci OK.
4. Seterusnya, tetingkap akan muncul pada skrin di mana anda perlu menentukan parameter fungsi. Mereka adalah berikut:
   1. Tatasusunan 1. Ini ialah hujah pertama di mana kita menulis julat yang diserlahkan dalam warna kuning. Anda boleh menetapkan parameter fungsi sama ada menggunakan papan kekunci atau dengan memilih kawasan yang sesuai dengan butang tetikus kiri.
   2. Array 2. Ini adalah hujah kedua, iaitu jadual yang baru dibuat. Tindakan dilakukan dengan cara yang sama.

Sahkan tindakan anda dengan menekan butang OK.

1. Selepas itu, kami membuat klik kiri tetikus pada sel yang berfungsi sebagai kiri atas dalam jadual yang baru dibuat. Sekarang klik butang sisip fungsi sekali lagi.
2. Kami memilih kategori yang sama seperti dalam kes sebelumnya. Tetapi kali ini kami berminat dengan fungsinya SUM.
3. Kini tibalah peringkat pengisian hujah. Sebagai hujah pertama, kami menulis baris atas jadual yang kami buat pada mulanya. Dengan cara yang sama seperti sebelum ini, ini boleh dilakukan dengan memilih sel ini pada helaian, atau secara manual. Kami mengesahkan tindakan kami dengan menekan butang OK.
4. Kami akan melihat keputusan dalam sel dengan fungsi. Dalam kes ini, ia adalah sifar. Seterusnya, gerakkan kursor ke penjuru kanan sebelah bawah, selepas itu penanda autolengkap akan muncul. Ia kelihatan seperti mewah hitam kecil. Jika ia muncul, tahan butang kiri tetikus dan gerakkan kursor ke sel terakhir dalam jadual kami.
5. Ini memberi kami peluang untuk memindahkan formula ke semua sel lain dan mendapatkan hasil yang betul tanpa perlu melakukan pengiraan tambahan.
6. Langkah seterusnya ialah memilih sel kiri atas dan tampal fungsi SUM ke dalam dia. Selepas itu, kami memasukkan argumen dan menggunakan penanda autolengkap untuk mengisi semua sel yang tinggal.
7. Selepas itu, kami teruskan untuk menyelesaikan masalah. Untuk melakukan ini, kami akan menggunakan alat tambah yang kami sertakan sebelum ini. Pergi ke tab "Data", dan di sana kita temui alat "Cari penyelesaian". Kami klik pada butang ini.
8. Kini tetingkap telah muncul di hadapan mata kami, yang melaluinya anda boleh mengkonfigurasi parameter alat tambah kami. Mari kita lihat setiap pilihan ini:
   1. Optimumkan fungsi objektif. Di sini kita perlu memilih sel yang mengandungi fungsi SUMPRODUCT. Kami melihat bahawa pilihan ini memungkinkan untuk memilih fungsi yang mana penyelesaian akan dicari.
   2. Sebelum ini. Di sini kami menetapkan pilihan "Minimum".
   3. Dengan menukar sel pembolehubah. Di sini kami menunjukkan julat yang sepadan dengan jadual yang kami buat pada awalnya (dengan pengecualian baris dan lajur ringkasan).
   4. Tertakluk kepada sekatan. Di sini kita perlu menambah kekangan dengan mengklik butang Tambah.
   5. Kami ingat jenis kekangan yang perlu kami wujudkan – jumlah nilai permintaan pembeli dan tawaran penjual mestilah sama.
9. Tugas sekatan dijalankan seperti berikut:
   1. Pautan ke sel. Di sini kita masukkan julat jadual untuk pengiraan.
   2. Syarat. Ini ialah operasi matematik yang julat yang ditentukan dalam medan input pertama diperiksa.
   3. Nilai syarat atau kekangan. Di sini kita masukkan lajur yang sesuai dalam jadual sumber.
   4. Selepas semua langkah selesai, klik butang OK, dengan itu mengesahkan tindakan kami.

Kami melakukan operasi yang betul-betul sama untuk baris atas, menetapkan keadaan berikut: mereka mesti sama.

Langkah seterusnya ialah menetapkan syarat. Kita perlu menetapkan kriteria berikut untuk jumlah sel dalam jadual – lebih besar daripada atau sama dengan sifar, integer. Akibatnya, kami mempunyai senarai keadaan sedemikian di mana masalah itu diselesaikan. Di sini anda perlu memastikan bahawa kotak semak di sebelah pilihan "Buat pembolehubah tanpa had bukan negatif" ditandakan. Juga, dalam keadaan kami, kaedah untuk menyelesaikan masalah itu perlu dipilih - "Mencari penyelesaian kepada masalah bukan linear kaedah OPG". Sekarang kita boleh mengatakan dengan selamat bahawa tetapan telah selesai. Oleh itu, ia kekal hanya untuk melakukan pengiraan. Untuk melakukan ini, klik pada butang "Cari penyelesaian".

Selepas itu, semua data akan dikira secara automatik, dan kemudian Excel akan menunjukkan tetingkap dengan hasilnya. Ia adalah perlu untuk menyemak semula operasi komputer, kerana ralat mungkin berlaku jika keadaan sebelum ini tidak ditetapkan dengan betul. Jika semuanya betul, kemudian klik butang "OK" dan lihat jadual yang telah siap.

Jika ternyata tugas kami telah menjadi jenis terbuka, maka ini buruk, kerana anda perlu mengedit jadual sumber supaya tugas itu berubah menjadi tertutup. Walau bagaimanapun, apabila ini dilakukan, algoritma yang selebihnya akan menjadi sama.

Kesimpulan

Seperti yang anda lihat, Excel juga boleh digunakan untuk pengiraan yang sangat kompleks, yang pada pandangan pertama tidak tersedia untuk program komputer mudah yang dipasang pada hampir semua orang. Walau bagaimanapun, ia adalah. Hari ini kita telah pun meliputi tahap penggunaan lanjutan. Topik ini tidak begitu mudah, tetapi seperti yang mereka katakan, jalan itu akan dikuasai oleh yang berjalan. Perkara utama adalah mengikuti pelan tindakan, dan melaksanakan semua tindakan yang ditunjukkan di atas dengan tepat. Kemudian tidak akan ada ralat, dan program secara bebas akan melaksanakan semua pengiraan yang diperlukan. Tidak perlu memikirkan fungsi mana yang hendak digunakan dan sebagainya.