1/15
ST.IiE
Analiza danych przy użyciu Solvera
Informatyka ekonomiczna — laboratorium
Spis treści
DO JAKICH ZADAŃ WYKORZYSTAMY SOLVERA?
PRZYKŁADY WYKORZYSTANIA NARZĘDZIA SOLVER
1 Aktywacja polecenia Solver
Do narzędzia Solver można uzyskać dostęp za pomocą polecenia Dane/Analiza/Solver. Jeżeli nie można go znaleźć,
trzeba zainstalować dodatek Solver. Jest to prosta operacja składająca się z następujących kroków:
1. Wybrać polecenie Przycisk pakietu Office/Opcje programu Excel.
2. W oknie dialogowym Opcje programu Excel uaktywnić kartę Dodatki.
3. W dolnej części okna z listy rozwijanej Zarządzaj wybrać pozycję Dodatki programu Excel i kliknąć przycisk
Przejdź. Excel wyświetli okno dialogowe Dodatki.
4. W oknie tym obok opcji Dodatek Solver umieścić symbol zaznaczenia i kliknąć przycisk OK.
Po wykonaniu tych kroków dodatek Solver będzie ładowany każdorazowo podczas uruchamiania Excela.
2 Do jakich zadań wykorzystamy Solvera?
Zagadnienia programowania liniowego dotyczą modelowania i optymalizacji wielu problemów decyzyjnych, na
przykład:
• optymalna wielkość produkcji przy podanych ograniczeniach zasobów,
• zagadnienia transportowe, gdzie minimalizujemy koszt przewozu przesyłek,
• problem mieszkanki (diety), gdzie określamy konieczną ilość posiadanych składników tak, aby przy najniższym
koszcie dostarczyć wymaganych ilości czynników,
• problem rozdziału robót, w którym określamy, jak rozdzielić zadania miedzy pracowników o różnej wydajności
tak, aby łączny czas ich pracy był najmniejszy.
Na potrzeby zajęć omówione zostaną tylko niektóre aspekty tej metody. Do ich rozwiązywania posłuży wbudowane
w arkusz kalkulacyjny Excel specjalne narzędzie — Solver.
Programowanie liniowe opiera się w głównej mierze na tworzeniu modeli rzeczywistości. Głównym elementem
modelu jest funkcja celu, dla której wartość ma podlegać pewnemu kryterium opłacalności (minimalizacji lub
maksymalizacji).
Model zawiera zmienne decyzyjne:
2/15
ST.IiE
współczynniki funkcji celu:
oraz pewne warunki ograniczające dopuszczalne wartości zmiennych decyzyjnych i współczynników funkcji celu.
Rozwiązanie większości problemów polega na znalezieniu takich wartości zmiennych
, aby funkcja celu
wyrażona określonym wzorem:
osiągnęła maksimum lub minimum.
Działanie narzędzia Solver zostanie zaprezentowane na poniższych przykładach.
3 Prosty przykład Solvera
Zaczniemy od prostego przykładu demonstrującego użycie Solvera, a później przejdziemy do przykładów bardziej
skomplikowanych, prezentujących szersze zastosowanie tego narzędzia.
Na rysunku 1 widzimy arkusz, który służy do obliczania zysku osiągniętego ze sprzedaży trzech produktów (skoroszyt
trzy_produkty.xlsx
). Kolumna B zawiera liczbę sztuk każdego produktu, kolumna C — zysk ze sprzedaży jednej
sztuki, a w kolumnie D widzimy zysk sumaryczny dla wszystkich sprzedanych sztuk danego produktu. W komórce D6
zszumowany jest zysk ze sprzedaży wszystkich produktów.
Rysunek 1 Za pomocą Solvera określa się, ile sztuk każdego z produktów należy sprzedać, aby osiągnąć największy zysk
Już na pierwszy rzut oka widać, że największy zysk przynosi produkt C. Wydaje się, że najlepszym rozwiązaniem
będzie produkowanie wyłącznie tego produktu i że nie ma potrzeby, by korzystać z Solvera. Jednak w większości
przypadków firma będzie musiała wziąć pod uwagę różne dodatkowe ograniczenia, takie jak:
• łączna wydajność produkcyjna firmy to 300 sztuk produktów dziennie;
• firma musi zrealizować zamówienie na 50 sztuk produktu A;
• firma spodziewa się w najbliższym czasie zamówienia na 40 sztuk produktu B;
• zapotrzebowanie na produkt C na rynku jest niewielkie, dlatego firma planuje wyprodukowanie najwyżej 40
sztuk tego produktu.
Powyższe cztery ograniczenia znacznie utrudniają udzielenie odpowiedzi na pytanie, jak osiągnąć największy zysk.
Jest to zadanie w sam raz dla Solvera.
3/15
ST.IiE
Zanim przejdzie się do dalszych szczegółów, należy zapoznać się z procedurą korzystania z Solvera. Oto kroki, które
należy wykonywać:
1. Skonstruować arkusz, wpisując do niego wartości i formuły.
2. Wybrać polecenie Dane/Analiza/Solver, aby otworzyć okno dialogowe Solver — Parametry.
3. Określić komórkę wynikową (inaczej komórkę celu).
4. Określić zakres zawierający komórki zmieniane.
5. Zdefiniować warunki ograniczające.
6. Jeżeli jest to konieczne, ustawić odpowiednie opcje Solvera.
7. Wydać Solverowi polecenie rozwiązania problemu.
Aby rozpocząć pracę Solvera i zrealizować omawiany przykład, należy wybrać polecenie Dane/Analiza/Solver. Pojawi
się okno dialogowe, pokazane na rysunku 2.
Rysunek 2 Okno dialogowe Solver — parametry
W tym przykładzie komórką celu jest D6 — oblicza ona całkowity zysk ze sprzedaży trzech produktów.
1. W oknie dialogowym Solver — Parametry znajduje się pole o nazwie Komórka celu. Należy wprowadzić do
niego komórkę D6 (lub przesunąć wskaźnik myszy na tę komórkę).
2. Ponieważ chce się uzyskać maksymalną wartość tej komórki, należy zaznaczyć opcję Maks.
3. Należy zdefiniować komórki zmieniane (znajdują się one na obszarze B3:B5). Następny etap polega na
zdefiniowaniu warunków ograniczających zadanie. Dodajemy je do listy pojedynczo.
4. Aby dodać warunek, należy kliknąć przycisk Dodaj. Pokaże się okno dialogowe Dodaj warunek ograniczający,
przedstawione na rysunku 3. Składa się ono z trzech części: adresu komórki, operatora i wartości warunku
ograniczającego.
Rysunek 3 Okno dialogowe Dodaj warunek ograniczający
5. Nasz pierwszy warunek polega na tym, że łączna wydajność produkcyjna firmy ma wynosić 300 egzemplarzy
produktów dziennie. W polu Adres komórki należy wpisać B6, a następnie wybrać z listy operator „=" i jako
wartość wpisać 300.
4/15
ST.IiE
6. Czynności te należy powtórzyć w celu zdefiniowania pozostałych warunków. Należy skorzystać z tabeli 1, która
prezentuje wszystkie warunki ograniczające tego zadania.
Tabela 1 Warunki ograniczające zadanie
Warunek ograniczający
Wyrażenie
Łączna produkcja 300 sztuk
B6=300
Co najmniej 50 sztuk produktu A
B3>=50
Najwyżej 40 sztuk produktu B
B4>=40
Nie więcej niż 40 sztuk produktu C
B5<=40
7. Po zdefiniowaniu wszystkich warunków należy kliknąć przycisk OK, aby powrócić do okna dialogowego Solver
— Parametry, które teraz wyświetla podane przez użytkownika ograniczenia. W tym momencie Solver ma już
wszystkie dane potrzebne do rozwiązania problemu (rysunek 4).
Rysunek 4 Okno Solver — Parametry po zdefiniowaniu warunków
8. Należy kliknąć przycisk Rozwiąż, aby Solver rozpoczął pracę nad zadaniem. Wynik jego obliczeń pokaże okno
dialogowe przedstawione na rysunku 5.
Rysunek 5 Solver wyświetla rozwiązanie zadania w oknie dialogowym Solver — Wyniki
Na tym etapie pracy z Solverem można:
• zamienić pierwotną wartość komórki zmienianej na wartość rozwiązania lub
• przywrócić pierwotną wartość komórki zmienianej,
• utworzyć jeden z trzech możliwych raportów: wyników, wrażliwości i granic,
• zapisać rozwiązanie w formie scenariusza, dzięki czemu będzie można z niego korzystać za pomocą
5/15
ST.IiE
Jeżeli postanowi się utworzyć raporty (wyników, wrażliwości lub granic), Excel umieści każdy z nich w osobnym
arkuszu i nada im odpowiednie nazwy. Na rysunku 6 widać raport wyników. Warto zwrócić uwagę na część
zawierającą warunki ograniczające: dwa z nich mają status wiążący. Oznacza to, że warunki te zostały spełnione bez
żadnego zapasu zmian wartości.
Rysunek 6 Jeden z trzech raportów generowanych przez Solvera
W arkuszu
Arkusz1
można teraz zobaczyć efekt działania Solvera, czyli dobrane liczby sztuk produktów tak, by przy
spełnieniu podanych warunków zysk był jak największy (rysunek 7). Efekt był możliwy dlatego, że komórki zmieniane
(B3:B5) mają poprzez formuły wpływ na komórkę celu (D6).
Rysunek 7 Wielkość produkcji po zastosowaniu Solvera
Na tym prostym przykładzie zapoznano się z działaniem Solvera. Zadanie było łatwe i być może jego samodzielne
rozwiązanie nie zabrałoby więcej czasu. Nie zawsze jednak będzie to takie proste.
OSTRZEŻENIE! Jeśli zaznaczy się opcję zastępującą zmienione oryginalne komórki, nie będzie
możliwe odtworzenie pierwotnych wartości za pomocą polecenia Cofnij.
Microsoft Excel 12.0 Raport wyników
Arkusz: [trzy_produkty.xlsx]Arkusz1
Raport utworzony: 2010-12-08 12:39:08
Komórka celu (Maks)
Komórka
Nazwa
Wartość początkowa Wartość końcowa
$D$6
Suma Zysk
5 300 zł
5 310 zł
Komórki decyzyjne
Komórka
Nazwa
Wartość początkowa Wartość końcowa
$B$3
Produkt A Sztuki
100
50
$B$4
Produkt B Sztuki
100
210
$B$5
Produkt C Sztuki
100
40
Warunki ograniczające
Komórka
Nazwa
Wartość komórki
formuła
Status
Luz
$B$6
Suma Sztuki
300
$B$6=300
Niewiążące
0
$B$3
Produkt A Sztuki
50
$B$3>=50
Wiążące
-
$B$4
Produkt B Sztuki
210
$B$4>=40
Niewiążące 170
$B$5
Produkt C Sztuki
40
$B$5<=40
Wiążące
0
Sztuki
Zysk na sztukę
Zysk
Produkt A
50
13 zł
650 zł
Produkt B
210
18 zł 3 780 zł
Produkt C
40
22 zł
880 zł
Suma
300
5 310 zł
6/15
ST.IiE
4 Więcej o Solverze
Zanim przejdzie się do bardziej złożonych przykładów, należy zapoznać się z oknem dialogowym Solver — Opcje.
Okno umożliwia kontrolę różnych aspektów procesu znajdowania wyników zadania, a także zapisywanie i
odczytywanie parametrów modelowych w ramach arkusza.
Często zdarza się tak, że Solver nie może znaleźć rozwiązania, podczas gdy użytkownik jest przekonany o tym, że ono
istnieje. Z reguły wystarczy wtedy zmienić jedną lub kilka opcji Solvera i uruchomić go ponownie. W tym celu należy
wybrać przycisk Opcje w oknie dialogowym Solver — Parametry. Rysunek 8 przedstawia okno dialogowe Solver —
Opcje, które pojawi się po wybraniu tego przycisku.
Rysunek 8 Okno dialogowe Solver — Opcje umożliwia kontrolę różnych aspektów pracy Solvera
Oto lista dostępnych opcji Solvera:
• Maksymalny czas — tu można określić w sekundach maksymalny czas, jaki Solver ma poświęcić na rozwiązanie
jednego zadania. Jeżeli ten czas zostanie przekroczony, Solver wyświetli informację, a użytkownik będzie mógł
odpowiednio powiększyć parametr.
• Liczba iteracji — należy wprowadzić maksymalną liczbę obliczeń pośrednich, jaką Solver może wykonać.
• Dokładność — tutaj określa się precyzję, z jaką formuły wynikowe i formuły warunków ograniczających mają
spełniać te warunki. Im mniejsza dokładność, tym szybciej Solver znajdzie rozwiązanie.
• Tolerancja — tu wyznacza się maksymalny procent dozwolonych błędów dla rozwiązań mających postać liczb
całkowitych.
• Zbieżność — tutaj wpisuje się liczbę o wartości z przedziału
, która określi dozwoloną liczbę zmian, zanim
Solver zakończy pracę. To ustawienie dotyczy tylko zadań o charakterze nieliniowym.
• Przyjmij model liniowy — należy zaznaczyć tę opcję, jeżeli chce się przyspieszyć proces szukania rozwiązania.
Jednak należy pamiętać, że działa ona tylko wtedy, gdy wszystkie zależności w modelu mają charakter liniowy.
Opcja ta nie jest dostępna, gdy zmieniane komórki są mnożone lub dzielone, a także wtedy, gdy zadanie zawiera
zależności wykładnicze.
• Przyjmij nieujemne — gdy zaznaczy się tę opcję, Solver przyjmie jako dolne ograniczenie tych wszystkich
komórek zmienianych, w których to ograniczenie nie zostało zdefiniowane.
• Automatyczne skalowanie — opcja ta jest przydatna wtedy, gdy wielkości wartości komórek w zadaniu różnią
się znacząco — na przykład przy wyznaczaniu maksymalnego stosunku procentowego dużych wartości.
• Pokaż wyniki iteracji — opcję tę należy zaznaczyć, jeśli chce się, aby Solver po każdej iteracji przerywał pracę
i pokazywał jej wyniki.
7/15
ST.IiE
• Grupy opcji Estymaty, Pochodne i Szukanie — dzięki nim można kontrolować niektóre techniczne aspekty
rozwiązania. Zazwyczaj nie ma potrzeby zmieniania tych ustawień.
• Załaduj model — ten przycisk wywołuje okno dialogowe Załaduj model. Można w nim określić adres arkusza
zawierającego zestaw parametrów Solvera, które chce się załadować.
• Zapisz model — ten przycisk wywołuje okno dialogowe Zapisz model. Tutaj określa się adres arkusza, w którym
zapisze się parametry modelu.
Potrzeba zapisania modelu pojawia się wtedy, gdy korzysta się z kilku zestawów parametrów Solvera w jednym
arkuszu. Excel automatycznie zapisuje pierwszy model Solvera razem z arkuszem (za pomocą ukrytych nazw). Jeżeli
zapisze się dodatkowe modele, informacje zostaną zachowane w postaci formuł odpowiadających tym ustawieniom
(ostatnia komórka zapisanego obszaru to formuła tablicowa, która zawiera ustawienia opcji).
5 Przykłady wykorzystania narzędzia Solver
5.1 Rozwiązywanie równoważnych równań liniowych
Przykład pokazuje, jak rozwiązać zestaw trzech równań liniowych z 3 zmiennymi. Oto przykładowy zestaw takich
równań (skoroszyt
rownania_liniowe.xlsx
):
Solver udzieli odpowiedzi na następujące pytanie: „Jakie wartości , i będą stanowić rozwiązanie wszystkich
trzech równań?"
Rysunek 9 przedstawia skoroszyt
rownania_liniowe.xlsx
przygotowany do rozwiązania problemu. W skoroszycie
znajdują się trzy nazwane komórki, które sprawiają, że formuły są czytelniejsze. Oto one:
• x: C11
• y: C12
• z: C13
Rysunek 9 Solver spróbuje rozwiązać zestaw równań liniowych
8/15
ST.IiE
Początkowo w trzech nazwanych komórkach wstawiono wartość 1 (oczywiście nie jest to rozwiązanie).
Trzy równania są reprezentowane przez następujące formuły z zakresu B6:B8:
• B6:
=(4*x) + (y) – (2*z)
,
• B7:
=(2*x) – (3*y) + (3*z)
,
• B8:
=-(6*x) – (2*y) +(z)
.
Formuły używają wartości zawartych w nazwanych komórkach x, y, z. Zakres C6:C8 przechowuje „pożądany” wynik
dla tych trzech formuł.
Solver zmodyfikuje wartości komórek x, y, z (czyli komórki zakresu C11:C13) uwzględniając następujące ograniczenia:
• B6 = C6,
• B7 = C7,
• B8 = C8.
UWAGA! Rozpatrywany problem nie ma funkcji (komórki) celu, ponieważ nie próbuje
zmaksymalizować lub zminimalizować. Jednakże okno dialogowe Solver — Parametry
proponuje zdefiniowane formuły w polu Komórka celu. A zatem wystarczy określić odwołanie
do dowolnej komórki posiadającej formułę (np. B6).
Rysunek 10 przedstawia rozwiązanie. Wartości komórek: x = 0,75; y = -2,0 i z = 0,5 stanowią rozwiązanie wszystkich
trzech równań.
Rysunek 10 Solver rozwiązał równania równoważne
UWAGA! Trzeba wiedzieć, że układ równań liniowych może posiadać jedno rozwiązanie,
nieskończoną liczbę rozwiązań lub może nie mieć rozwiązania.
5.2 Optymalizacja programu produkcji betoniarni
5.2.1 Dwa rodzaje pustaków
Betoniarnia produkuje dwa rodzaje pustaków: zwykły i z gryzem marmurowym. Każdego dnia do dyspozycji ma 800
pustych form metalowych. Sprzedaż każdego zwykłego pustaka przynosi zysk 2,20 zł, pustaka z gryzem
9/15
ST.IiE
marmurowym 2,25 zł. Aby wyprodukować jeden zwykły pustak, potrzeba 0,5 kg cementu, z gryzem marmurowym
0,4 kg cementu. Cementownia jest w stanie dostarczyć do betoniarni 50 ton cementu dziennie. Dodatkowo do
wyprodukowania pustaka z gryzem marmurowym potrzeba 0,2 kg tego gryzu. Kamieniołom jest w stanie dziennie
dostarczyć go do betoniarni w ilości 50 kg. Należy tak ustawić produkcję, aby osiągnąć maksymalny zysk dzienny.
Na podstawie treści problemu wyodrębniono poszczególne elementy modelu:
• Zmienne decyzyjne:
•
— liczba zwykłych pustaków,
•
— liczba pustaków z gryzem marmurowym.
• Współczynniki funkcji celu:
• zysk na wyprodukowanym pustaku
• 2,20 dla ,
• 2,25 dla
.
• Funkcja celu przyjmuje więc postać:
Dodatkowo wyodrębniono
• Warunki ograniczające:
•
ze względu na liczbę form metalowych,
•
ze względu na ilość cementu,
•
ze względu na ilość gryzu marmurowego.
oraz oczywiście:
•
i
•
.
Dwa ostatnie warunki są bardzo ważne z tego powodu, że funkcja Solver „poszłaby na łatwiznę''
i maksymalizowałaby zysk, przyjmując wartości ujemne dla zmiennych decyzyjnych, a nam przecież nie o to chodzi.
Przeniesienie wyżej przedstawionych elementów do arkusza Excela (skoroszyt
betoniarnia.xlsx
, arkusz
2 pustaki
) pozwoli sprawnie wykorzystać narzędzie Solver do rozwiązania problemu. Widok arkusza przedstawia
rysunek 11.
Rysunek 11 Widok arkusza Excel z przygotowanymi danymi do użycia narzędzia Solver
Dane w kolumnach Wartość, Zużycie cementu oraz Zużycie gryzu są wyliczane. W dolnym wierszu arkusza są one
zsumowane (D4:G4). W kolumnę Ilość zawierającą zmienne decyzyjne
,
wpisano tymczasowo wartości .
Funkcja celu
Zysk jednostkowy Ilość Wartość Zużycie cementu Zużycie gryzu
Pustaki zwykłe
2,20 zł
1
2,20 zł
0,5
0
Pustaki z gryzem
2,25 zł
1
2,25 zł
0,4
0,2
Razem:
2
4,45 zł
0,9
0,2
Ograniczenia
Ilość form metalowych
800
Dysponowany cement
50000
Dysponowany gryz
50
10/15
ST.IiE
Funkcja celu, zapisana jako suma wartości zysku dla obu asortymentów, znajduje się w komórce D4. Poniżej głównej
tabeli umieszczono tabelkę z ograniczeniami. Kolorami zaznaczono odniesienia ograniczeń do wyliczonych wartości
w tabeli głównej. I tak np. Ilość form metalowych (komórka B6) ogranicza sumę liczby możliwych do
wyprodukowania pustaków (C4). Tak przygotowane dane poddano obróbce za pomocą narzędzia Solver.
Zaznaczono komórkę D4 zawierającą funkcję celu, którą chcemy zmaksymalizować, i uruchomiono narzędzie Solver.
Parametry niezbędne do rozwiązania niniejszego przykładu muszą zostać wprowadzone tak, jak pokazano na rys.12.
Rysunek 12 Okno parametrów Solvera dla przykładu betoniarni z 2 rodzajami pustaków
W oknie Solver uruchom parametry obliczenia przyciskiem Rozwiąż. W kolejnym oknie, Solver — Wyniki, należy
wybrać opcję Przechowaj rozwiązanie i kliknąć przycisk OK. Wyniki przedstawiono na rysunku 13. Były one oczywiście
intuicyjnie oczekiwane. Z uwagi na prostotę proponujemy wykonać samodzielnie ćwiczenie, nieznacznie modyfikując
dane z tego przykładu.
Rysunek 13 Widok wynikowy arkusza z 2 pustakami po przeliczeniu danych narzędziem Solver
5.2.2 Trzy rodzaje pustaków
Załóżmy, że betoniarnia zwiększyła asortyment produkcji pustaków o typ „pustaki wzmocnione” (arkusz
3 pustaki
),
których produkcja przynosi zysk jednostkowy w wysokości 2,30 zł przy jednostkowym zużyciu cementu w ilości 1,2 kg
oraz gryzu marmurowego w ilości 0,2 kg. Załóżmy także, że w cementowni nastąpiła awaria, a betoniarnia dysponuje
zapasem tylko 400 kg cementu. Pozostałe dane jak w przykładzie 5.2.1. Należy ustalić taki asortyment produkcji przy
istniejących ograniczeniach, aby zysk był jak najwyższy.
Rozwiązanie tego ćwiczenia nie jest już tak oczywiste jak w powyższym przykładzie. Zastosowanie narzędzia
wspomagającego obliczenia jest w tym przypadku zdecydowanie zalecane. Zastosujmy arkusz
3 pustaki
z danymi
wyjściowymi, by obliczyć maksymalną wartość dla komórki D5 za pomocą narzędzia Solver.
Funkcja celu
Zysk jednostkowy Ilość Wartość
Zużycie cementu Zużycie gryzu
Pustaki zwykłe
2,20 zł 550 1 210,00 zł
275
0
Pustaki z gryzem
2,25 zł 250 562,50 zł
100
50
Razem: 800 1 772,50 zł
375
50
Ograniczenia
Ilość form metalowych
800
Dysponowany cement
50000
Dysponowany gryz
50
11/15
ST.IiE
Procedura wprowadzenia parametrów obliczeniowych do narzędzia Solver jest identyczna jak w przykładzie 1. Należy
uwzględnić nieujemność liczby sztuk pustaków wzmocnionych. Wyniki ćwiczenia powinny przybrać taką postać, jak
pokazano na rysunku 14.
Rysunek 14 Widok wynikowy arkusza z 3 pustakami po przeliczeniu danych narzędziem Solver
Jak widać, otrzymano wyniki z dokładnością do ułamkowych części pustaka. Wnioski i ostateczną interpretację
wyników pozostawiamy Czytelnikowi.
5.3 Minimalizacja kosztów wysyłki
Ten przykład (skoroszyt
koszty_przesylki.xlsx
) polega na znalezieniu alternatywnych możliwości przesyłania
towarów przy jednoczesnym zachowaniu minimalnego poziomu kosztów przesyłki (rys. 15). Firma ma hurtownie
w Warszawie, Krakowie i Poznaniu. Sklepy detaliczne znajdują się w różnych miastach Polski. Z każdego z nich
napływają zamówienia, które firma przesyła do jednej z hurtowni. Firma chce zrealizować wszystkie zamówienia,
wykorzystując dostępny stan magazynów w hurtowniach i utrzymując koszty przesyłek na najniższym poziomie.
Omawiany skoroszyt jest dość skomplikowany, dlatego najpierw poznamy poszczególne jego elementy:
• Tabela kosztów wysyłki — ta tabela (zakres C2:F8) prezentuje wysokość kosztów przesyłki z każdej hurtowni do
sklepów. Na przykład koszt przesyłki z Poznania do Koszalina wynosi 58 zł.
• Zamówienia poszczególnych sklepów — te informacje pojawiają się w komórkach znajdujących się na obszarze
C12:C17. Na przykład Koszalin potrzebuje 150 sztuk towaru, Katowice 225 sztuk itd. Komórka C18 zawiera sumę
zamówień wszystkich sklepów.
• Sztuki do wysłania — w obszarze D12:F17 znajdują się komórki, na których Solver dokonuje odpowiednich
modyfikacji (dla wszystkich wartością początkową jest liczba 25). Kolumna G zawiera formuły podsumowujące
liczbę egzemplarzy produktów, jakie należy wysłać do każdego ze sklepów.
• Stan magazynów w hurtowni — wiersz 20. pokazuje stany magazynów każdej hurtowni, a wiersz 21. zawiera
formuły, które odejmują od nich liczbę wysłanych towarów (wiersz 18.).
• Koszty wysyłki — wiersz 24. zawiera formuły, które wyliczają koszty przesyłki. W komórce D24 znajduje się
formuła (skopiowana do dwóch komórek znajdujących się po jej prawej stronie), która ma następującą postać:
=SUMA.ILOCZYNÓW(D3:D8.D12:D17)
Funkcja celu
Zysk jednostkowy Ilość Wartość
Zużycie cementu Zużycie gryzu
Pustaki zwykłe
2,20 zł 550 1 210,00 zł
275
0
Pustaki wzmocnione
2,30 zł 31,3
71,88 zł
37,5
6,25
Pustaki z gryzem
2,25 zł 219 492,19 zł
87,5
43,75
Razem: 800 1 774,06 zł
400
50
Ograniczenia
Ilość form metalowych
800
Dysponowany cement
400
Dysponowany gryz
50
12/15
ST.IiE
Rysunek 15 Arkusz wyznaczający najtańszy sposób wysyłania towaró z hurtowni do poszczególnych sklepów firmy
Komórka G24 podaje wynik końcowy, czyli sumę kosztów przesłania towarów do wszystkich sklepów.
Solver wstawia wartości w obszarze D12:F17 tak, aby koszty przesyłki były jak najniższe, dbając jednocześnie
o zrealizowanie wszystkich zamówień na nowe towary. Inaczej mówiąc, minimalizuje wartość komórki C24 poprzez
odpowiednie modyfikacje wartości komórek D12:F17, które są poddane następującym ograniczeniom:
•
Liczba egzemplarzy produktów zamówionych przez każdy ze sklepów musi być równa liczbie wysłanych sztuk
produktów (czyli wszystkie zamówienia muszą być zrealizowane). Warunek ten wyrażają następujące równania:
C12=G12
C14=G14
C16=G16
C13=G13
C15-G15
C17=G17
•
Wartości komórek zmienianych nie mogą być liczbami ujemnymi, ponieważ nie można wysłać ujemnej liczby
przesyłek. Warunek ten określają następujące nierówności:
D12 >= 0
E12 >= 0
F12 >=0
D13 >= 0
E13 >= 0
F13 >=0
D14 >= 0
E14 >= 0
F14 >=0
D15 >= 0
E15 >= 0
F15 >=0
D16 >= 0
E16 >= 0
F16 >=0
D17 >= 0
E17 >= 0
F17 >=0
•
Liczba egzemplarzy produktów, które pozostały w magazynie, nie może być ujemna, ponieważ hurtownia nie
może wysłać więcej towarów, niż ma w magazynach. Ten warunek przedstawiają następujące nierówności:
D21 >= 0
E21>= 0
F21 >= 0
UWAGA! Zanim Solver rozpocznie obliczenia, można spróbować samemu zminimalizować
koszty przesyłki, wprowadzając ręcznie wartości w komórkach D12:F17. Należy przy tym
upewnić się, że wartości spełniają wszystkie warunki ograniczające. Nie jest to łatwe zadanie,
prawda? Teraz już wiadomo, jak szerokie są możliwości Solvera.
13/15
ST.IiE
Zdefiniowanie problemu wcale nie jest proste, trzeba przecież wpisać 27 warunków ograniczających. Jeżeli podano
już wszystkie potrzebne informacje, należy kliknąć przycisk Rozwiąż, aby Solver rozpoczął pracę nad zadaniem.
Rysunek 16 przedstawia rozwiązanie, które podaje Solver po dokonaniu obliczeń.
Całkowity koszt przesyłek przy spełnieniu wszystkich warunków wyniósł 55 515 zł. Warto zauważyć, że do Gdańska
towar wysyłany jest z dwóch hurtowni: z Łodzi i z Warszawy.
Rysunek 16 Rozwiązanie uzyskane przez Solvera
5.4 Optymalizacja portfela inwestycyjnego
W tym przykładzie Czytelnik dowie się, jak wykorzystać narzędzie Solver do wyznaczenia maksymalnej wartości
zwrotu portfela inwestycyjnego. Na portfel składa się kilka inwestycji, które przynoszą różny zysk. Zadaniem będzie
zdefiniowanie różnorodnych warunków ograniczających, w tym na przykład zmniejszających ryzyko inwestycji. Bez
tych ograniczeń rozwiązanie byłoby dziecinnie proste — wystarczy zainwestować wszystkie pieniądze
w przedsięwzięcie przynoszące największy zysk.
Nasz przykład dotyczy spółdzielczej kasy pożyczkowej (skoroszyt
inwestycje.xlsx
). Jest to instytucja finansowa,
która inwestuje pieniądze swoich członków w różnego rodzaju przedsięwzięcia, a także korzysta z nich w celu
udzielenia pożyczki innym członkom. Część zysków z tych inwestycji spółdzielnia zwraca swoim członkom w postaci
dywidend lub odsetek od depozytów. Ta hipotetyczna spółdzielnia musi ściśle przestrzegać przepisów dotyczących
inwestowania, a ponadto jest ograniczona warunkami określonymi przez zarząd. Właśnie te przepisy i ograniczenia
stanowią warunki naszego zadania. Na rysunku 17 widać skoroszyt. na którym został oparty ten przykład.
Twoja inwestycja to 5 milionów zł. Trzeba spełnić wszystkie poniższe warunki:
• Kwota zainwestowana w kredyty przeznaczone na zakup nowych samochodów musi być przynajmniej trzy razy
większa od kwoty przeznaczonej na kredyty na zakup używanych samochodów (ponieważ druga operacja jest
obarczona większym ryzykiem). Warunek ten wyrazi się za pomocą nierówności:
C5>=C6*3
T a b e la ko s ztó w wys yłki
P o zna ń
Łó d ź
W a rs z a wa
Koszalin
58 zł
47 zł
108 zł
Katowice
87 zł
46 zł
100 zł
Białystok
121 zł
30 zł
57 zł
Gdańsk
149 zł
66 zł
83 zł
Szczecin
62 zł
115 zł
164 zł
Kielce
128 zł
28 zł
38 zł
T o wa ry d o wys ła nia z...
Do wysłania
S k le p
Za m ó wie n ie
P o zna ń
Łó d ź
W a rs z a wa
Koszalin
150
150
0
0
150
Katowice
225
0
225
0
225
Białystok
100
0
100
0
100
Gdańsk
250
0
25
225
250
Szczecin
120
120
0
0
120
Kielce
150
0
0
150
150
Razem
995
270
350
375
995
Stan magazynowy
Początkowy stan magazynu w hurtowni:
400
350
500
Zostało:
130
0
125
Koszty wysyłki:
16 140 zł
15 000 zł
24 375 zł
55 515 zł Razem
14/15
ST.IiE
• Kredyty samochodowe powinny stanowić co najmniej 15 procent portfela:
D14>=0,15
• Kredyty nieubezpieczone mogą stanowić nie więcej niż 25 procent portfela:
E8<= 0,25
• Co najmniej 10 procent portfela powinieneś przeznaczyć na lokaty bankowe. Warunek ten wyrazi się za pomocą
następującej nierówności:
E9>=0,10
• Kwota całkowita inwestycji to 5 000 000 zł.
• Żadna inwestycja nie powinna przynosić strat, czyli zysk nie może być ujemny. Aby spełnić ten warunek, trzeba
dodać pięć kolejnych warunków, określających, że żadna komórka zmienna nie może przyjmować wartości
ujemnej.
Rysunek 17 Arkusz, na podstawie którego Solver obliczy maksymalny zysk inwestycji spółdzielczej kasy pożyczkowej
Komórki zmieniane znajdują się w obszarze C5:C9. Celem jest osiągnięcie maksymalnej wartości zysku całkowitego
w komórce D12. W komórkach zmienianych wpisano wartość początkową
1000000
. Solver uruchomiony przy tych
parametrach znajdzie rozwiązanie, które pokazano na rysunku 18 — zysk całkowity wyniósł 8,59 procent.
Rysunek 18 Wynik optymalizacji portfela inwestycyjnego
Wielkość portfela:
5 000 000,00
Inwestycja
Procent zysku
Kwota inwestycji
Zysk
Procent
portfela
Kredyty na nowy samochód
6,90%
1 872 541
129 205
37,45%
Kredyty na używany samochód
8,25%
299 590
24 716
5,99%
Kredyty mieszkaniowe
8,90%
947 541
84 331
18,95%
Kredyty nieubezpieczone
13,00%
1 250 000
162 500
25,00%
Lokaty bankowe
4,60%
630 328
28 995
12,61%
RAZEM
5 000 000
429 748
100,00%
Zysk całkowity:
8,59%
Kredyty samochodowe
43,44%
15/15
ST.IiE
Zysk w wysokości 8,59 procent nie jest jednak optymalnym rozwiązaniem. Przy włączonej opcji automatycznego
skalowania Solver uzyska rozwiązanie, w którym zysk całkowity będzie wynosił 9,25 procent.
Jak widać, Solver nie zawsze znajduje optymalne rozwiązanie za pierwszym razem, mimo że w oknie dialogowym
wyświetla informację: Solver znalazł rozwiązanie. Wszystkie warunki i ograniczenia optymalizacji są spełnione.
Dlatego zanim zacznie się wykorzystywać Solvera do rozwiązywania poważniejszych zadań, należy zapoznać się
dobrze z wszystkimi możliwościami, jakie to narzędzie oferuje. Na początku należy wprowadzać różne wartości
komórek wejściowych i zmieniać ustawienia w Opcjach, aby lepiej poznać ich działanie.
6 Literatura
1. Waćkowski K., Chmielewski J.M.: Wspomaganie zarządzania projektami informatycznymi. Poradnik dla
menedżerów. Helion, Gliwice, 2007, ss. 231–238.
2. Walkenbach J.: Excel 2007 PL. Biblia. Helion, Gliwice, 2007, ss. 702–715.
3. Office.com: Definiowanie i rozwiązywanie problemów za pomocą dodatku
Solver.
http://office.microsoft.com/pl-pl/excel-help/HP010072691.aspx
5. Zawadzka L.: Metody ilościowe w organizacji i zarządzaniu. Część I. Wydawnictwo Politechniki Gdańskiej, Gdańsk
1996, ss. 51–109.
Document Outline
- Aktywacja polecenia Solver
- Do jakich zadań wykorzystamy Solvera?
- Prosty przykład Solvera
- Więcej o Solverze
- Przykłady wykorzystania narzędzia Solver
- Literatura
Wyszukiwarka
Podobne podstrony:
więcej podobnych podstron