Zadanie transportowe w Excelu. Znalezienie najlepszej metody transportu od sprzedającego do kupującego

Excel to bardzo funkcjonalny program. Można go wykorzystać do rozwiązania ogromnej warstwy problemów, z którymi trzeba się zmierzyć w biznesie. Jednym z najczęstszych jest transport. Wyobraź sobie, że musimy zrozumieć, która metoda transportu od producenta do ostatecznego nabywcy jest najbardziej optymalna pod względem czasu, pieniędzy i innych zasobów. Ten problem jest dość popularny, bez względu na branżę, w której działa firma. Dlatego przyjrzyjmy się bliżej, jak go zaimplementować za pomocą Excela.

Opis zadania transportowego

Mamy więc dwóch kontrahentów, którzy stale ze sobą współdziałają. W naszym przypadku jest to kupujący i sprzedający. Musimy wymyślić, jak transportować towary w taki sposób, aby koszty były minimalne. W tym celu należy przedstawić wszystkie dane w formie schematu lub macierzy. W Excelu korzystamy z tej drugiej opcji. Generalnie istnieją dwa rodzaje zadań transportowych:

1. Zamknięte. W tym przypadku podaż i popyt są w równowadze.
2. Otwarty. Nie ma tu równości między podażą a popytem. Aby uzyskać rozwiązanie tego problemu, musisz najpierw sprowadzić go do pierwszego typu, wyrównując podaż i popyt. Aby to zrobić, musisz wprowadzić dodatkowy wskaźnik – obecność warunkowego kupującego lub sprzedającego. Ponadto musisz wprowadzić pewne zmiany w tabeli kosztów.

Jak włączyć funkcję Znajdź rozwiązanie w programie Excel

Aby rozwiązać problemy transportowe w Excelu, istnieje specjalna funkcja o nazwie „Wyszukaj rozwiązanie”. Nie jest domyślnie włączone, więc musisz wykonać następujące czynności:

1. Otwórz menu „Plik”, które znajduje się w lewym górnym rogu okna programu.
2. Następnie kliknij przycisk z parametrami.
3. Następnie znajdujemy podsekcję „Ustawienia” i przechodzimy do menu zarządzania dodatkami. Są to małe programy działające w środowisku Microsoft Excel. Widzimy, że najpierw kliknęliśmy menu „Dodatki”, a następnie w prawej dolnej części ustawiliśmy element „Dodatki Excela” i kliknęliśmy przycisk „Idź”. Wszystkie niezbędne czynności są wyróżnione czerwonymi prostokątami i strzałkami.
4. Następnie włączamy dodatek „Wyszukaj rozwiązanie”, po czym potwierdzamy nasze działania, naciskając przycisk OK. Na podstawie opisu ustawienia widzimy, że jest ono przeznaczone do analizowania złożonych danych, takich jak naukowe i finansowe.
5. Następnie przejdź do zakładki „Dane”, gdzie widzimy nowy przycisk, który nazywa się tak samo jak dodatek. Można go znaleźć w grupie narzędzi Analiza.

Pozostaje tylko kliknąć ten przycisk i przystępujemy do rozwiązania problemu transportowego. Ale wcześniej powinniśmy porozmawiać trochę o narzędziu Solver w programie Excel. To specjalny dodatek do Excela, który umożliwia znalezienie najszybszego rozwiązania problemu. Cechą charakterystyczną jest uwzględnienie ograniczeń, jakie stawia użytkownik na etapie przygotowania. W uproszczeniu jest to podprogram, który umożliwia określenie najlepszego sposobu realizacji określonego zadania. Takie zadania mogą obejmować:

1. Inwestowanie, ładowanie magazynu lub inna podobna działalność. W tym dostawa towarów.
2. Najlepszym sposobem. Obejmuje to takie cele, jak osiągnięcie maksymalnego zysku przy minimalnych kosztach, jak osiągnąć najlepszą jakość przy dostępnych zasobach i tak dalej.

Oprócz zadań transportowych ten dodatek służy również do następujących celów:

1. Opracowanie planu produkcji. To znaczy, ile jednostek produktu trzeba wyprodukować, aby osiągnąć maksymalny dochód.
2. Znajdź rozkład pracy dla różnych rodzajów pracy, aby całkowity koszt wytworzenia produktu lub usługi był jak najmniejszy.
3. Ustaw minimalny czas potrzebny na wykonanie całej pracy.

Jak widać zadania są bardzo różne. Uniwersalną zasadą stosowania tego dodatku jest to, że przed rozwiązaniem problemu konieczne jest stworzenie modelu, który odpowiadałby kluczowym cechom postawionego problemu. Model to zbiór funkcji, które używają zmiennych jako swoich argumentów. To znaczy wartości, które mogą się zmienić.

Należy zauważyć, że optymalizacja zbioru wartości odbywa się wyłącznie na jednym wskaźniku, który nazywa się funkcją celu.

Dodatek Solver wylicza różne wartości zmiennych, które są przekazywane do funkcji celu w taki sposób, że jest to maksimum, minimum lub równa określonej wartości (jest to dokładnie ograniczenie). Istnieje inna funkcja, która jest nieco podobna pod względem zasady działania i często jest mylona z „Szukaj rozwiązania”. Nazywa się „Wybór opcji”. Ale jeśli poszukasz głębiej, różnica między nimi jest ogromna:

1. Funkcja Goal Seek nie działa z więcej niż jedną zmienną.
2. Nie przewiduje możliwości ustawiania limitów na zmienne.
3. Jest w stanie określić tylko równość funkcji celu z określoną wartością, ale nie umożliwia znalezienia maksimum i minimum. Dlatego nie nadaje się do naszego zadania.
4. Potrafi efektywnie obliczyć tylko model liniowy. Jeśli model jest nieliniowy, znajduje wartość najbliższą oryginalnej wartości.

Zadanie transportowe jest znacznie bardziej skomplikowane w swojej strukturze, więc dodatek „Wybór parametrów” nie wystarczy do tego. Przyjrzyjmy się, jak w praktyce zaimplementować funkcję „Szukaj rozwiązania” na przykładzie problemu transportowego.

Przykład rozwiązania problemu transportowego w Excelu

Aby jasno pokazać, jak rozwiązywać problemy transportowe w praktyce w Excelu, podajmy przykład.

Zadania warunkujące

Załóżmy, że mamy 6 sprzedających i 7 kupujących. Popyt i podaż pomiędzy nimi rozkładają się odpowiednio w następujący sposób: 36, 51, 32, 44, 35 i 38 jednostek to sprzedający, a 33, 48, 30, 36, 33, 24 i 32 jednostki to kupujący. Jeśli zsumujesz wszystkie te wartości, przekonasz się, że podaż i popyt są w równowadze. Dlatego problem ten ma charakter zamknięty, który rozwiązuje się bardzo prosto.

Dodatkowo mamy informację, ile trzeba przeznaczyć na transport z punktu A do punktu B (w przykładzie są one zaznaczone żółtymi komórkami).

Rozwiązanie – algorytm krok po kroku

Teraz, po zapoznaniu się z tabelami z danymi początkowymi, możemy użyć następującego algorytmu do rozwiązania tego problemu:

1. Najpierw tworzymy tabelę składającą się z 6 wierszy i 7 kolumn.
2. Następnie przechodzimy do dowolnej komórki, która nie zawiera żadnych wartości, a jednocześnie leży poza nowo utworzoną tabelą i wstawiamy funkcję. Aby to zrobić, kliknij przycisk fx, który znajduje się po lewej stronie wiersza wprowadzania funkcji.
3. Mamy okno, w którym musimy wybrać kategorię „Matematyka”. Jaka funkcja nas interesuje? Ten wyróżniony na tym zrzucie ekranu. Funkcjonować SUMPRODUCT mnoży między sobą zakresy lub tablice i sumuje je. Właśnie tego potrzebujemy. Następnie naciśnij klawisz OK.
4. Następnie na ekranie pojawi się okno, w którym należy określić parametry funkcji. Są to:
   1. Tablica 1. Jest to pierwszy argument, w którym zapisujemy zakres podświetlony na żółto. Parametry funkcji można ustawić za pomocą klawiatury lub wybierając odpowiedni obszar lewym przyciskiem myszy.
   2. Tablica 2. To jest drugi argument, czyli nowo utworzona tabela. Akcje wykonywane są w ten sam sposób.

Potwierdź swoje działanie, naciskając przycisk OK.

1. Następnie klikamy lewym przyciskiem myszy komórkę, która służy jako lewy górny róg nowo utworzonej tabeli. Teraz ponownie kliknij przycisk funkcji wstawiania.
2. Wybieramy tę samą kategorię, co w poprzednim przypadku. Ale tym razem interesuje nas funkcja SUMA.
3. Teraz nadchodzi etap wypełniania argumentów. Jako pierwszy argument piszemy górny wiersz tabeli, którą utworzyliśmy na początku. W taki sam sposób, jak poprzednio, można to zrobić, wybierając te komórki w arkuszu lub ręcznie. Potwierdzamy nasze działania, naciskając przycisk OK.
4. Wyniki zobaczymy w komórce z funkcją. W tym przypadku zero. Następnie przesuń kursor w prawy dolny róg, po czym pojawi się znacznik autouzupełniania. Wygląda jak mały czarny plusz. Jeśli się pojawi, przytrzymaj lewy przycisk myszy i przesuń kursor do ostatniej komórki w naszej tabeli.
5. Daje nam to możliwość przeniesienia formuły do ​​wszystkich pozostałych komórek i uzyskania poprawnych wyników bez konieczności wykonywania dodatkowych obliczeń.
6. Następnym krokiem jest wybranie górnej lewej komórki i wklejenie funkcji SUMA w nią. Następnie wprowadzamy argumenty i używamy znacznika autouzupełniania, aby wypełnić wszystkie pozostałe komórki.
7. Następnie przystępujemy bezpośrednio do rozwiązania problemu. Aby to zrobić, użyjemy dodatku, który zawarliśmy wcześniej. Przechodzimy do zakładki „Dane”, a tam znajdujemy narzędzie „Wyszukaj rozwiązanie”. Klikamy na ten przycisk.
8. Teraz przed naszymi oczami pojawiło się okno, za pomocą którego można skonfigurować parametry naszego dodatku. Przyjrzyjmy się każdej z tych opcji:
   1. Zoptymalizuj funkcję celu. Tutaj musimy wybrać komórkę zawierającą funkcję SUMPRODUCT. Widzimy, że ta opcja pozwala wybrać funkcję, dla której będzie poszukiwane rozwiązanie.
   2. Zanim. Tutaj ustawiamy opcję „Minimum”.
   3. Zmieniając komórki zmiennych. Tutaj wskazujemy zakres odpowiadający tabeli, którą utworzyliśmy na samym początku (z wyjątkiem wiersza i kolumny podsumowującej).
   4. Z zastrzeżeniem ograniczeń. Tutaj musimy dodać ograniczenia, klikając przycisk Dodaj.
   5. Pamiętamy jakie ograniczenie musimy stworzyć – suma wartości żądań kupujących i ofert sprzedających musi być taka sama.
9. Zadanie ograniczeń jest realizowane w następujący sposób:
   1. Link do komórek. Tutaj wprowadzamy zakres tabeli do obliczeń.
   2. Semestry. Jest to operacja matematyczna, według której sprawdzany jest zakres określony w pierwszym polu wejściowym.
   3. Wartość warunku lub ograniczenia. Tutaj wpisujemy odpowiednią kolumnę w tabeli źródłowej.
   4. Po wykonaniu wszystkich kroków kliknij przycisk OK, potwierdzając tym samym nasze działania.

Wykonujemy dokładnie te same operacje dla górnych wierszy, ustawiając warunek: muszą być równe.

Następnym krokiem jest ustalenie warunków. Musimy ustawić następujące kryteria dla sumy komórek w tabeli – większa lub równa zero, liczba całkowita. Dzięki temu mamy taką listę warunków, w których problem jest rozwiązywany. Tutaj musisz upewnić się, że pole wyboru obok opcji „Ustaw zmienne bez limitów jako nieujemne” jest zaznaczone. Również w naszej sytuacji wymagany jest wybór metody rozwiązania problemu – „Poszukiwanie rozwiązania nieliniowych problemów metod OPG”. Teraz możemy śmiało powiedzieć, że ustawienie jest gotowe. Dlatego pozostaje tylko wykonać obliczenia. Aby to zrobić, kliknij przycisk „Znajdź rozwiązanie”.

Następnie wszystkie dane zostaną obliczone automatycznie, a następnie Excel wyświetli okno z wynikami. Jest to konieczne, aby dwukrotnie sprawdzić działanie komputera, ponieważ błędy są możliwe, jeśli warunki zostały wcześniej ustawione nieprawidłowo. Jeśli wszystko jest w porządku, kliknij przycisk „OK” i zobacz gotowy stół.

Jeśli okaże się, że nasze zadanie stało się typem otwartym, to źle, ponieważ musisz edytować tabelę źródłową, aby zadanie zmieniło się w zamknięte. Jednak gdy to nastąpi, pozostały algorytm będzie taki sam.

Wnioski

Jak widać, Excel może być również używany do bardzo złożonych obliczeń, które na pierwszy rzut oka nie są dostępne dla prostego programu komputerowego, który jest zainstalowany prawie w każdym. Jednak tak jest. Dziś omówiliśmy już zaawansowany poziom użytkowania. Ten temat nie jest taki prosty, ale jak mówią, drogę opanuje pieszy. Najważniejsze jest przestrzeganie planu działania i dokładne wykonanie wszystkich czynności wskazanych powyżej. Wtedy nie będzie błędów, a program samodzielnie wykona wszystkie niezbędne obliczenia. Nie będzie potrzeby zastanawiania się, której funkcji użyć i tak dalej.