Spis treści
Sformułowanie problemu
Załóżmy, że firma, w której pracujesz, ma trzy magazyny, z których towary trafiają do pięciu twoich sklepów rozsianych po całej Moskwie.
Każdy sklep jest w stanie sprzedać określoną ilość znanego nam towaru. Każdy z magazynów ma ograniczoną pojemność. Zadanie polega na racjonalnym wyborze, z którego magazynu do jakich sklepów dostarczyć towar, aby zminimalizować całkowite koszty transportu.
Przed rozpoczęciem optymalizacji konieczne będzie skompilowanie prostej tabeli na arkuszu Excela – naszego modelu matematycznego opisującego sytuację:
Rozumie się to jako:
- Jasnożółta tabela (C4:G6) opisuje koszt wysyłki jednego towaru z każdego magazynu do każdego sklepu.
- Fioletowe komórki (C15:G14) opisują ilość towarów potrzebną do sprzedaży w każdym sklepie.
- Czerwone komórki (J10:J13) pokazują pojemność każdego magazynu – maksymalną ilość towaru, jaką magazyn może pomieścić.
- Żółte (C13:G13) i niebieskie (H10:H13) komórki to odpowiednio sumy wierszy i kolumn dla komórek zielonych.
- Całkowity koszt wysyłki (J18) obliczany jest jako suma iloczynów ilości towarów i odpowiadających im kosztów wysyłki – do obliczeń służy tutaj funkcja SUMPRODUCT (SUMA ILOCZYN).
Tym samym nasze zadanie sprowadza się do doboru optymalnych wartości zielonych komórek. I tak, aby łączna kwota dla linii (niebieskie komórki) nie przekraczała pojemności magazynu (czerwone komórki), a jednocześnie każdy sklep otrzymał taką ilość towaru, jaką musi sprzedać (kwota dla każdego sklepu w żółte komórki powinny być jak najbardziej zbliżone do wymagań – fioletowe komórki).
Rozwiązanie
W matematyce takie problemy wyboru optymalnego rozmieszczenia zasobów były formułowane i opisywane od dawna. I oczywiście sposoby ich rozwiązania od dawna są opracowywane nie przez tępe wyliczenie (które jest bardzo długie), ale w bardzo małej liczbie iteracji. Excel udostępnia użytkownikowi taką funkcjonalność za pomocą dodatku. Rozwiązania wyszukiwania (Rozwiązuje) z zakładki Dane (Data):
Jeśli na karcie Dane Twój Excel nie ma takiego polecenia – wszystko w porządku – oznacza to, że dodatek po prostu nie jest jeszcze podłączony. Aby go aktywować, otwórz filet, A następnie wybierz opcję parametry - Dodatki - O nas (Opcje — Dodatki — Idź do). W oknie, które się otworzy, zaznacz pole obok potrzebnej linii Rozwiązania wyszukiwania (Rozwiązuje).
Uruchommy dodatek:
W tym oknie musisz ustawić następujące parametry:
- Zoptymalizuj funkcję docelową (Ustaw tpieniądze komórka) – tutaj należy wskazać ostateczny główny cel naszej optymalizacji, czyli różowe pudełko z całkowitym kosztem wysyłki (J18). Komórkę docelową można zminimalizować (jeśli są to wydatki, jak w naszym przypadku), zmaksymalizować (jeśli jest to np. zysk) lub spróbować sprowadzić ją do określonej wartości (np. zmieścić się dokładnie w przydzielonym budżecie).
- Zmiana komórek zmiennych (By wymiana pieniędzy komórki) – tutaj wskazujemy zielone komórki (C10:G12), zmieniając wartości których chcemy osiągnąć nasz wynik – minimalny koszt dostawy.
- Zgodnie z ograniczeniami (Temat
do dotychczasowy Ograniczenia) – lista ograniczeń, które należy wziąć pod uwagę podczas optymalizacji. Aby dodać ograniczenia do listy, kliknij przycisk Dodaj (Dodać) i wprowadź warunek w wyświetlonym oknie. W naszym przypadku będzie to ograniczenie popytu:
i ograniczenie maksymalnej objętości magazynów:
Oprócz oczywistych ograniczeń związanych z czynnikami fizycznymi (pojemność magazynów i środków transportu, ograniczenia budżetowe i czasowe itp.) czasami konieczne jest dodanie ograniczeń „specjalnych do Excela”. Na przykład Excel może łatwo zaaranżować dla Ciebie „optymalizację” kosztów dostawy, proponując przewóz towaru ze sklepów z powrotem do magazynu – koszty staną się ujemne, czyli zarobimy!
Aby temu zapobiec, najlepiej pozostawić to pole wyboru włączone. Uczyń nieograniczone zmienne nieujemnymi a nawet czasami wyraźnie rejestrują takie momenty na liście ograniczeń.
Po ustawieniu wszystkich niezbędnych parametrów okno powinno wyglądać tak:
Na liście rozwijanej Wybierz metodę rozwiązywania należy dodatkowo wybrać odpowiednią metodę matematyczną rozwiązywania trzech opcji:
- Metoda simpleks jest prostą i szybką metodą rozwiązywania problemów liniowych, czyli takich, w których wyjście jest liniowo zależne od wejścia.
- Ogólna obniżona metoda gradientu (OGG) – dla problemów nieliniowych, gdzie występują złożone nieliniowe zależności między danymi wejściowymi i wyjściowymi (np. zależność sprzedaży od kosztów reklamy).
- Ewolucyjne poszukiwanie rozwiązania – stosunkowo nowa metoda optymalizacji oparta na zasadach ewolucji biologicznej (cześć Darwin). Ta metoda działa wielokrotnie dłużej niż dwie pierwsze, ale może rozwiązać prawie każdy problem (nieliniowy, dyskretny).
Nasze zadanie jest wyraźnie liniowe: dostarczono 1 sztukę – wydano 40 rubli, dostarczono 2 sztuki – wydano 80 rubli. itp., więc najlepszym wyborem jest metoda simplex.
Teraz, gdy dane do obliczeń są wprowadzone, naciśnij przycisk Znaleźć rozwiązanie (Rozwiązywać)aby rozpocząć optymalizację. W ciężkich przypadkach z dużą ilością zmieniających się komórek i ograniczeń znalezienie rozwiązania może zająć dużo czasu (zwłaszcza metodą ewolucyjną), ale nasze zadanie dla Excela nie będzie problemem – za chwilę otrzymamy następujące wyniki :
Zwróć uwagę na to, jak ciekawie rozłożyły się wielkości dostaw pomiędzy sklepy, nie przekraczając jednocześnie pojemności naszych magazynów i zaspokajając wszystkie prośby o wymaganą liczbę towarów dla każdego sklepu.
Jeśli znalezione rozwiązanie nam odpowiada, możemy je zapisać, lub cofnąć się do pierwotnych wartościi spróbować ponownie z innymi parametrami. Możesz również zapisać wybraną kombinację parametrów jako Scenariusz. Na życzenie użytkownika Excel może zbudować trzy typy Raporty o rozwiązywanym problemie na osobnych arkuszach: raport z wyników, raport o matematycznej stabilności rozwiązania oraz raport o granicach (ograniczeniach) rozwiązania, jednak w większości przypadków interesują je tylko specjaliści .
Są jednak sytuacje, w których Excel nie może znaleźć odpowiedniego rozwiązania. Można zasymulować taki przypadek, jeśli w naszym przykładzie wskażemy wymagania sklepów w ilości większej niż łączna pojemność magazynów. Następnie podczas przeprowadzania optymalizacji program Excel spróbuje zbliżyć się do rozwiązania jak najbliżej, a następnie wyświetli komunikat, że nie można znaleźć rozwiązania. Niemniej jednak nawet w tym przypadku mamy wiele przydatnych informacji – w szczególności widzimy „słabe ogniwa” naszych procesów biznesowych i rozumiemy obszary wymagające poprawy.
Rozważany przykład jest oczywiście stosunkowo prosty, ale łatwo skaluje się do rozwiązywania znacznie bardziej złożonych problemów. Na przykład:
- Optymalizacja dystrybucji środków finansowych według pozycji wydatków w biznesplanie lub budżecie projektu. Ograniczeniem w tym przypadku będzie kwota finansowania i termin realizacji projektu, a celem optymalizacji jest maksymalizacja zysków i minimalizacja kosztów projektu.
- Optymalizacja harmonogramów pracowników w celu zminimalizowania funduszu płac przedsiębiorstwa. Ograniczeniami w tym przypadku będą życzenia każdego pracownika zgodnie z harmonogramem zatrudnienia i wymaganiami tabeli kadrowej.
- Optymalizacja inwestycji inwestycyjnych – konieczność prawidłowego podziału środków pomiędzy kilka banków, papierów wartościowych lub akcji przedsiębiorstw, aby znowu maksymalizować zyski lub (jeśli jest to ważniejsze) minimalizować ryzyko.
W każdym razie dodatek Rozwiązania wyszukiwania (Rozwiązujący) to bardzo potężne i piękne narzędzie Excela, warte Twojej uwagi, ponieważ może pomóc w wielu trudnych sytuacjach, z którymi musisz się zmierzyć we współczesnym biznesie.