Zadanie transportowe w programie Microsoft Excel
Zadaniem transportowym jest poszukiwanie optymalnego wariantu transportu tego samego rodzaju towarów od dostawcy do konsumenta. Jego podstawą jest model szeroko stosowany w różnych dziedzinach matematyki i ekonomii. W programie Microsoft Excel istnieją narzędzia, które znacznie ułatwiają rozwiązanie zadania transportowego. Dowiemy się, jak z nich korzystać w praktyce.
Spis treści
Ogólny opis zadania transportowego
Głównym celem zadania transportowego jest znalezienie optymalnego planu transportu od dostawcy do klienta przy minimalnych kosztach. Warunki takiego problemu zapisywane są w postaci obwodu lub macierzy. W programie Excel używany jest typ macierzy.
Jeśli całkowita ilość towarów w magazynach dostawcy jest równa wartości popytu, zadanie transportowe nazywa się zamknięte. Jeśli te wskaźniki nie są równe, takie zadanie transportowe nazywa się otwarte. Aby go rozwiązać, warunki należy zredukować do typu zamkniętego. Aby to zrobić, dodaj fikcyjnego sprzedawcę lub fikcyjnego kupca, którego zapasy lub potrzeby są równe różnicy między podażą a popytem w rzeczywistej sytuacji. W takim przypadku do tabeli kosztów dodawana jest dodatkowa kolumna lub wiersz z wartościami zerowymi.
Narzędzia do rozwiązywania problemu transportu w programie Excel
Aby rozwiązać problem transportu w programie Excel, używana jest funkcja "Znajdź rozwiązania" . Problem polega na tym, że domyślnie jest wyłączony. Aby włączyć to narzędzie, musisz wykonać określone czynności.
- Przechodzimy do zakładki "Plik" .
- Klikamy na podsekcję "Parametry" .
- W nowym oknie przejdź do napisu "Dodatki" .
- W bloku "Zarządzanie" , który znajduje się na dole otwartego okna, z rozwijanej listy, zatrzymujemy zaznaczenie pozycji "Dodatki Excel" . Kliknij przycisk "Przejdź ..." .
- Otworzy się okno aktywacji dodatków. Zaznacz pole "Znajdowanie rozwiązania" . Kliknij przycisk "OK" .
- Z powodu tych działań, w zakładce "Dane" w bloku ustawień "Analiza" na wstążce pojawia się przycisk "Znajdź rozwiązania" . Będzie nam potrzebny w poszukiwaniu rozwiązania problemu transportu.
Lekcja: Znajdź rozwiązanie w Excelu
Przykład rozwiązania zadania transportowego w programie Excel
Teraz spójrzmy na konkretny przykład rozwiązania problemu transportu.
Warunki zadania
Mamy 5 dostawców i 6 kupujących. Wielkość produkcji tych dostawców wynosi 48, 65, 51, 61, 53 sztuk. Potrzeba kupujących: 43, 47, 42, 46, 41, 59 sztuk. W związku z tym całkowita podaż jest równa wartości popytu, czyli mamy do czynienia z zamkniętym zadaniem transportowym.
Ponadto, pod warunkiem, podana jest matryca kosztów transportu z jednego punktu do drugiego, co pokazano na ilustracji poniżej na zielono.
Rozwiązanie problemu
Przed nami jest zadanie w wyżej wymienionych warunkach, aby zmniejszyć koszty transportu do minimum.
- Aby rozwiązać problem, tworzymy tabelę z dokładnie taką samą liczbą komórek, jak wyżej opisana macierz kosztów.
- Wybierz dowolną pustą komórkę na arkuszu. Kliknij ikonę "Wstaw funkcję" , znajdującą się po lewej stronie linii formuły.
- Otworzy się "Kreator funkcji". Na liście, którą sugeruje, powinniśmy znaleźć funkcję SUMPRODUCT . Wybierz go i kliknij przycisk "OK" .
- Otwiera okno wprowadzania dla funkcji SUMPROSE . Jako pierwszy argument wprowadzamy zakres komórek w macierzy kosztów. Aby to zrobić, wystarczy podświetlić dane komórki kursorem. Drugi argument to zakres komórek w tabeli, która została przygotowana do obliczeń. Następnie kliknij przycisk "OK" .
- Kliknij komórkę znajdującą się po lewej stronie lewej górnej komórki tabeli w celu obliczeń. Jak również po raz ostatni wywołujemy kreatora funkcji, otwieramy w nim argumenty funkcji SUMA . Kliknięcie pola pierwszego argumentu powoduje zaznaczenie całego górnego wiersza komórek tabeli do obliczeń. Po wprowadzeniu współrzędnych w odpowiednim polu kliknij przycisk "OK" .
- Stajemy się prawym dolnym rogiem komórki z funkcją SUMA . Pojawi się uchwyt do wypełniania. Kliknij lewym przyciskiem myszy i przeciągnij uchwyt wypełniania w dół do końca tabeli, aby obliczyć. Więc skopiowaliśmy formułę.
- Klikamy komórkę umieszczoną powyżej górnej lewej komórki tabeli w celu obliczeń. Podobnie jak w poprzednim czasie nazywamy funkcję SUMA , ale tym razem jako argument używamy pierwszej kolumny tabeli do obliczeń. Kliknij przycisk "OK" .
- Skopiuj token wypełnienia do formuły dla całego wiersza.
- Przejdź do zakładki "Dane" . Tam, w przyborniku "Analiza", kliknij przycisk "Znajdź rozwiązanie" .
- Wyświetlane są opcje wyszukiwania rozwiązania. W polu "Optymalizacja funkcji docelowej" podaj komórkę zawierającą funkcję SUMPRODUCT . W bloku "Do" ustaw wartość "Minimum" . W polu "Zmiana komórek zmiennych" określamy cały zakres tabeli do obliczeń. W bloku ustawień "Zgodnie z ograniczeniami" kliknij przycisk "Dodaj" , aby dodać kilka ważnych ograniczeń.
- Otworzy się okno do dodawania ograniczeń. Przede wszystkim musimy dodać warunek, że suma danych w wierszach tabeli dla obliczeń powinna być równa sumie danych w wierszach tabeli z warunkiem. W polu "Cell reference" określ zakres sumy w wierszach tabeli obliczeń. Następnie ustaw znak równości (=). W polu "Limit" określ zakres sum w wierszach tabeli z warunkiem. Następnie kliknij przycisk "OK" .
- Podobnie dodajemy warunek, że kolumny dwóch tabel powinny być sobie równe. Dodajemy ograniczenie, że suma zakresu wszystkich komórek w tabeli do obliczenia musi być większa lub równa 0, a także warunek, że musi to być liczba całkowita. Ogólny widok ograniczeń powinien być taki, jak pokazano na poniższym obrazku. Upewnij się, że w pobliżu pozycji "Dokonuj zmiennych bez ograniczeń nieujemnych" było zaznaczenie, a metodą rozwiązania było "Wyszukaj rozwiązanie problemów nieliniowych metodą OPG" . Po określeniu wszystkich ustawień, kliknij przycisk "Znajdź rozwiązanie" .
- Następnie odbywa się obliczenie. Dane są wyprowadzane do komórek tabeli w celu obliczenia. Zostanie wyświetlone okno wyników wyszukiwania rozwiązania. Jeśli wyniki są zadowalające, kliknij przycisk "OK" .
Jak widać, rozwiązanie zadania transportowego w Excelu jest zredukowane do poprawnego tworzenia danych wejściowych. Obliczenia wykonuje program, a nie użytkownik.