Analiza danych przy użyciu Solvera
Informatyka ekonomiczna  laboratorium
Spis treści
1 AKTYWACJA POLECENIA SOLVER ....................................................................................................................................... 1
2 DO JAKICH ZADAC
WYKORZYSTAMY SOLVERA? ................................................................................................................ 1
3 PROSTY PRZYKA
AD SOLVERA .............................................................................................................................................. 2
4 WI
CEJ O SOLVERZE ........................................................................................................................................................... 6
5 PRZYKA
ADY WYKORZYSTANIA NARZ
DZIA SOLVER ............................................................................................................ 7
6 LITERATURA ..................................................................................................................................................................... 15
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 znalez
ć,
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
Przejdz
. 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:
1/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óz
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.
2/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ąć wskaz
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.
3/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
B6=300
A
ączna produkcja 300 sztuk
B3>=50
Co najmniej 50 sztuk produktu A
B4>=40
Najwyżej 40 sztuk produktu B
B5<=40
Nie więcej niż 40 sztuk produktu C
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ą Menedżera
scenariuszy.
4/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.
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 210
100
$B$5 Produkt C Sztuki
100 40
Warunki ograniczające
Komórka Nazwa Wartość komórki formuła Status Luz
$B$6 Suma Sztuki Niewiążące 0
300 $B$6=300
$B$3 Produkt A Sztuki Wiążące -
50 $B$3>=50
$B$4 Produkt B Sztuki Niewiążące 170
210 $B$4>=40
$B$5 Produkt C Sztuki Wiążące 0
40 $B$5<=40
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).
Sztuki Zysk na sztukę Zysk
Produkt A 13 zł 650 zł
50
Produkt B 18 zł 3 780 zł
210
Produkt C 22 zł 880 zł
40
Suma 5 310 zł
300
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.
5/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 znalez
ć 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.
6/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
7/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
8/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.
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
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 .
9/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.
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
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.
10/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.
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
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)
11/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.
12/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 A
odzi i z Warszawy.
Ta b e la ko s ztó w wys yłki
P o zna ń A
ódz
Wa rs za 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 kle p Za m ó wie nie P o zna ń A
ódz
Wa rs za 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
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
13/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.
Wielkość portfela: 5 000 000,00
Procent
Procent zysku Kwota inwestycji Zysk portfela
Inwestycja
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%
Rysunek 18 Wynik optymalizacji portfela inwestycyjnego
14/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.
4. Frontline Systems. http://www.solver.com/.
5. Zawadzka L.: Metody ilościowe w organizacji i zarządzaniu. Część I. Wydawnictwo Politechniki Gdańskiej, Gdańsk
1996, ss. 51 109.
15/15
ST.IiE