P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
1
1. Przekształcenie zadania programowania liniowego w model zapisany
w arkuszu Excela
Przykładowe zadanie programowania liniowego
Rozwiązać następujące zadanie programowania liniowego wykorzystując Excela oraz dodatek Solver.
x3
x1 , x2 , - zmienne decyzyjne
7x1 + 9x2 + 4,6x3 max funkcja celu
przy ograniczeniach
8x1 + 7x2 + 4x3 d" 9900
2,5x1 + 2x2 + 0,9x3 d" 2800 ograniczenia funkcyjne
1x1 +10x2 + 4,1x3 = 11700
4,3x1 + 4x2 + 9x3 e" 12100
x1 e" 0, x2 e" 0, x3 e" 0 - warunki nieujemności zmiennych
Podstawową zasadą, na jakiej jest oparte przenoszenie do Excela modeli programowania liniowego i nielinio-
wego jest konieczność podjęcia przez użytkownika decyzji, które komórki w arkuszu będą rolę zmiennych. Wg
tej zależności ( komórka-zmienna ) tworzone są formuły oraz wpisy w polach dodatku Solver.
Przykładowy schemat rozmieszczenia danych w Excelu dla zadania programowania liniowego
Przyjmujemy, że zmiennym decyzyjnym odpowiadają w arkuszu następujące komórki:
x3
A2 - x1 , B2 - x2 , C2 - .
Komórki pełniące rolę zmiennych zostały wypełnione zerami. Nie jest to jednak konieczne  komórki te mogą
pozostać puste, ponieważ zostaną potraktowane przez Excela tak, jakby zawierały zera.
W arkuszu umieszczone są także parametry tzn. liczby będące współczynnikami funkcji (A4:C4) celu oraz wa-
runków ograniczających (A6:C9, E6:E9). Rozmieszczenie to może być w zasadzie dowolne, ale jego odpo-
wiedni dobór ma bardzo duże znaczenie dla wygody wprowadzania formuł.
Ponieważ współczynniki funkcji celu znajdują się w komórkach A4, B4 i C4, a zatem odpowiednikiem funkcji
celu
7x1 + 9x2 + 4,6x3
będzie formuła
=A4*A2+B4*B2+C4*C2
Zastosujemy jednak prostszą we wprowadzaniu (zwłaszcza, jeżeli użyty zostanie kreator funkcji0 równoważną
formułę
=SUMA.ILOCZYNÓW(A4:C4;A2:C2).
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
2
Jak widać, funkcja celu jest podobna do lewych stron warunków ograniczających (wszystkie są sumami ilo-
czynów liczb i zmiennych). Dzięki temu formuła reprezentująca w arkuszu funkcję celu zostanie wykorzystana
do stworzenia, przy pomocy kopiowania, formuł reprezentujących lewe strony warunków ograniczających W
tym celu formuła ta musi być wpisana w postaci
=SUMA.ILOCZYNÓW(A4:C4;A$2:C$2)
Dzięki zastosowaniu adresacji względnej ( zablokowania przy pomocy znaków $ odwołań do zakresu A2:C2)
po skopiowaniu D4 do D6:D9 otrzymamy formuły oznaczające lewe strony warunków ograniczających.
W tabeli poniżej jest podane są zależności pomiędzy formułami matematycznymi a  Excelowymi .
Informacja na temat formuł: wprowadzanej i kopiowanych
Zapis matematyczny
Formuły  dosłowne tzn. takie, które
Formuły z SUMA.ILOCZYNÓW odpowiada-
Uwagi
należałoby wpisać przy literalnym
jące formułom  dosłownym
 przełożeniu zapisu matematycznego
na składnię Excela
7x1 + 9x2 + 4,6x3
=SUMA.ILOCZYNÓW(A4:C4;A$2:C$2) Wprowadzona przez użyt-
D4
kownika
=A4*A2+B4*B2+C4*C2
8x1 + 7x2 + 4x3
=SUMA.ILOCZYNÓW(A6:C6;A$2:C$2) Otrzymana przez
D6
kopiowanie z D4
=A6*A2+B6*B2+C6*C2
2,5x1 + 2x2 + 0,9x3
=SUMA.ILOCZYNÓW(A7:C7;A$2:C$2) Otrzymana przez
D7
kopiowanie z D4
=A7*A2+B7*B2+C7*C2
1x1 + 10x2 + 4,1x3
=SUMA.ILOCZYNÓW(A8:C8;A$2:C$2) Otrzymana przez
D8
kopiowanie z D4
=A8*A2+B8*B2+C8*C2
4,3x1 + 4x2 + 9x3
=SUMA.ILOCZYNÓW(A9:C9;A$2:C$2) Otrzymana przez
D9
kopiowanie z D4
=A9*A2+B9*B2+C9*C2
Komórka
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
3
Widok arkusza po skopiowaniu D4 na D6:D11. Zarówno komórka z funkcją celu (D4) jak i komórki z lewymi
stronami warunków ograniczających (D6, D7, D8, D9, czyli zakres D6:D9) wyświetlają wartości zerowe, po-
nieważ komórki pełniące rolę zmiennych ( iksów ) zawierają wyłącznie zera.
Widok formuł w arkuszu otrzymanych po skopiowaniu.
Pełna lista zależności pomiędzy zapisem matematycznym a modelem Excelowym zapisana przy pomocy adre-
sów komórek wygląda następująco:
A2 B2 C2
x3
x1 , x2 , - zmienne decyzyjne
D4 7x1 + 9x2 + 4,6x3 max funkcja celu
przy ograniczeniach
8x1 + 7x2 + 4x3 d" 9900
D6 E6
D7 2,5x1 + 2x2 + 0,9x3 d" 2800 E7 ograniczenia funkcyjne
1x1 + 10x2 + 4,1x3 d" 11700
D8 E8
D9 4,3x1 + 4x2 + 9x3 d" 12100 E9
A2 B2 C2
x1 e" 0, x2 e" 0, x3 e" 0 warunki nieujemności zmiennych
Wszystkie niezbędne liczby oraz formuły zostały wprowadzone do arkusza. Należy teraz uruchomić dodatek
optymalizacyjny Solver (Menu Narzędzia  Solver, w Excelu 2007/2010 Dane-Solver).
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
4
Opis wprowadzania danych do Solvera - Excel w wersjach do 2007 włącznie
Główne okno Solvera (Solver-Parametry) po uruchomieniu Solvera
Pole Komórka celu w nowootwartym oknie Solvera Solver- Parametry jest zawsze ustawiona na komórkę bie-
żącą (na zrzucie ekranu G7). Z powyższego powodu najwygodniej jest ustawić wcześniej jako komórkę bieżącą
komórkę pełniącą rolę funkcji celu (tzn. zawierającą odpowiednią formułę). W przypadku niniejszego zadania
będzie to komórka D4.
Następnie należy wybrać typ optymalizacji tzn. maksymalizację funkcji celu w opcji Równa. Ponieważ Maks
jest ustawieniem domyślnym tej opcji, można ja pominąć.
W pole Komórki zmieniane należy wpisać zakres A2:C2.
Wygląd okna Solver-Parametry po wprowadzeniu w/w danych jest przedstawiony na rys. 7.
Główne okno Solvera (Solver-Parametry) z ustawionymi polami Komórka celu, Równa i Komórki zmieniane.
Warunki ograniczające wpisuje się w oddzielnym oknie Dodaj warunek ograniczający otwieranym po kliknię-
ciu Dodaj w oknie Solver-Parametry.
Uwaga! Pole Warunki ograniczające jest tylko listą  nie można w nie nic wpisywać!
Uwaga! Gdy jest otwarte okno Dodaj warunek ograniczający (lub analogiczne Zmień warunek ograniczający),
wtedy główne okno Solvera (Solver-Parametry) jest niewidoczne.
Uwaga! Można wpisywać adresy bez dolarów (znaki $ i tak zostaną przez Excela dostawione, podobnie
jak znaki = w polu Warunek ograniczający).
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
5
Nowootwarte okno Dodaj warunek ograniczający.
Wpisywanie dwóch warunków D6<=E6, D7<=E7  wspólnie .
Nierówność <= jest ustawieniem domyślnym więc nie trzeba nic zmieniać w środkowym polu i można przejść
do wpisywania prawej strony (pole Warunek ograniczający).
Ten zapis odpowiada grupie warunków D6<=E6, D7<=E7,
Po kliknięciu Dodaj otwiera się ponownie okno dodawania warunków. Należy wpisać warunek D8=E8.
Uwaga  trzeba pamiętać o wyborze równości = (domyślne ustawienie to <=).
Warunek D8=E8.
Po kliknięciu Dodaj otwiera się ponownie okno dodawania warunków. Należy wpisać warunek D9>=E9.
Uwaga  trzeba pamiętać o wyborze nierówności >= (domyślne ustawienie to <=).
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
6
Warunek D9=E9.
Po kliknięciu Dodaj otwiera się ponownie okno dodawania warunków. Należy wpisać warunki nieujemności
zmiennych A2:C2>=0  wspólnie .
Uwaga  trzeba pamiętać o wyborze nierówności >= (domyślne ustawienie to <=)
Ten zapis odpowiada grupie warunków A2>=0, B2>=0, C2>=0.
Warunki te są wpisywane jako ostatnie i zatwierdzone przez OK.
Kompletne ustawienia. Warunki ograniczające sortują się alfabetycznie, niezależnie od kolejności wpisywana,
Należy teraz kliknąć w Rozwiąż, a gdy pojawi się okno jak poniżej, kliknąć OK.
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
7
Widok po wykonaniu obliczeń Solverem.
Rozwiązanie
Optymalna (maksymalna) wartość funkcji celu to 11925,92405. Optymalne wartości zmiennych (tzn. wartości,
* * *
dla których osiągnięte zostało maksimum) to x1 = 85,1767, x2 = 766,645 oraz x3 = 963,0178
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
8
Opis wprowadzania danych do Solvera - Excel w wersjach 2010, 2013, 2016
W Excelu 2010 w porównaniu z poprzednimi wersjami został w Solverze zmieniony interfejs użytkownika.
Poniżej dla porównania umieszczono zrzuty ekranów Solvera z Excela 2007 i 2010.
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
9
Wykaz najważniejszych różnic między oknami Solvera
Opis Excel 2007 i wcześniejsze Excel 2010,2013,2016 Uwagi
Nazwa okna głównego Solver-Parametry Parametry dodatku Solver
Adres komórki zawierającej formu- Komórka celu Ustaw cel Domyślne ustawienie tego pola to
łę z funkcją celu lub ewentualnie komórka bieżąca
jedną z komórek zmienianych
Rodzaj optymalizacji: maksy- Równa Na Domyślne ustawienie to Maks
malizacja, minimalizacja, ustalona
wartość liczbowa funkcji celu
Deklaracja zakresu/zakresów ko- Komórki zmieniane Przez zmienianie komórek Brak ustawienia domyślnego
mórek pełniących rolę zmiennych zmiennych
 Odgadnięcie zakresu/zakresów Odgadnij brak Najczęściej  odgadnięcie jest błędne
komórek pełniących rolę zmien-
nych na podstawie formuły z funk-
cją celu
Lista warunków ograniczających Warunki ograniczające Podlegające ograniczeniom Jest to lista służącą tylko do wyświe-
tlania, a nie do wpisywania/edycji
Otwiera okno dodawania nowego Dodaj Dodaj W Excelu 2010 zmieniono okno do-
warunku ograniczającego dawania warunków ograniczających
Otwiera okno edycji istniejącego Zmień Zmień W Excelu 2010 zmieniono okno edy-
warunku ograniczającego (wymaga cji warunków ograniczających
wybrania go na liście)
Usuwa z listy istniejący warunek Usuń Usuń
ograniczający (wymaga wybrania
go na liście)
Otwiera okno opcji, umożliwiające Opcje Opcje W Excelu 2010 zmieniono okno opcji
zmianę domyślnych ustawień
wpływających na proces obliczeń
Skasowanie wszystkich danych Przywróć wszystko Resetuj wszystko
wprowadzonych przez użytko-
wnika oraz przywrócenie opcji do-
myślnych
Zapis modelu z Solvera w postaci Dostępne jako Zapisz model/ Załaduj/zapisz
grupy formuł w arkuszu Załaduj model w oknie op-
cji)
Ustawienie nieujemności wszyst- Dostępne jako Przyjmij nieu- Ustaw wartości nieujemne
kich zmiennych bez dodawania sto- jemne w oknie opcji; domy- dla zmiennych bez ograni-
sownego warunku ograniczającego ślnie niezaznaczone - brak czeń (domyślnie zaznaczone
do listy nieujemności wszystkich - ustawiona nieujemność
zmiennych) wszystkich zmiennych)
Typ algorytmu używanego do obli- Brak jawnego ustawienia Wybierz metodę rozwiązy- Ustawienie domyślne to algorytm
czeń (przełączenie na algorytm wania (ustawienie domyślne  uniwersalny GRG, a alternatywą
simpleks poprzez zaznacze- jest opisane jako Nieliniowa jest algorytm simpleks dla programo-
nie opcji Przyjmij model li- GRG) wania liniowego. W Excelu 2010 do-
niowy w oknie opcji) dano do wyboru tzw. algorytm ewo-
lucyjny
Porównanie wyglądu okien dodawania/edycji warunków ograniczających
Excel 2007 i wcześniejsze Excel 2010 i 2013
Okno dodawania nowych warunków ograniczających
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
10
Okno edycji istniejących warunków ograniczających
P. Kowalik, Laboratorium badań operacyjnych: wstęp do rozwiązywania zadań programowania liniowego w Excelu
11
Porównanie okna opcji Solvera dla Excela 2007 i wcześnieszych (pojedyncze okno  zrzut w lewym górnym
rogu strony) oraz dla Excela 2010 i póz
niejszych (pozostałe zrzuty  okno z trzema zakładkami).