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.

1

x

,

2

x

,

3

x

- zmienne decyzyjne

max

6

,

4

9

7

3

2

1

→

+

+

x

x

x

funkcja celu

przy ograniczeniach

9900

4

7

8

3

2

1

≤

+

+

x

x

x

2800

9

,

0

2

5

,

2

3

2

1

≤

+

+

x

x

x

ograniczenia funkcyjne

11700

1

,

4

10

1

3

2

1

=

+

+

x

x

x

12100

9

4

3

,

4

3

2

1

≥

+

+

x

x

x

0

,

0

,

0

3

2

1

≥

≥

≥

x

x

x

- 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:

A2 -

1

x

, B2 -

2

x

, C2 -

3

x

.

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

3

2

1

6

,

4

9

7

x

x

x

+

+

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

należałoby wpisać przy literalnym

„przełożeniu” zapisu matematycznego

na składnię Excela

K

om

ór

ka

Formuły z SUMA.ILOCZYNÓW odpowiada-

jące formułom „dosłownym”

Uwagi

3

2

1

6

,

4

9

7

x

x

x

+

+

=A4*A2+B4*B2+C4*C2

D4

=SUMA.ILOCZYNÓW(A4:C4;A$2:C$2)

Wprowadzona przez użyt-

kownika

3

2

1

4

7

8

x

x

x

+

+

=A6*A2+B6*B2+C6*C2

D6

=SUMA.ILOCZYNÓW(A6:C6;A$2:C$2)

Otrzymana przez

kopiowanie z D4

3

2

1

9

,

0

2

5

,

2

x

x

x

+

+

=A7*A2+B7*B2+C7*C2

D7

=SUMA.ILOCZYNÓW(A7:C7;A$2:C$2)

Otrzymana przez

kopiowanie z D4

3

2

1

1

,

4

10

1

x

x

x

+

+

=A8*A2+B8*B2+C8*C2

D8

=SUMA.ILOCZYNÓW(A8:C8;A$2:C$2)

Otrzymana przez

kopiowanie z D4

3

2

1

9

4

3

,

4

x

x

x

+

+

=A9*A2+B9*B2+C9*C2

D9

=SUMA.ILOCZYNÓW(A9:C9;A$2:C$2)

Otrzymana przez

kopiowanie z D4

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

1

x

,

2

x

,

3

x

- zmienne decyzyjne

D4

max

6

,

4

9

7

3

2

1

→

+

+

x

x

x

funkcja celu

przy ograniczeniach

D6

9900

4

7

8

3

2

1

≤

+

+

x

x

x

E6

D7

2800

9

,

0

2

5

,

2

3

2

1

≤

+

+

x

x

x

E7

ograniczenia funkcyjne

D8

11700

1

,

4

10

1

3

2

1

≤

+

+

x

x

x

E8

D9

12100

9

4

3

,

4

3

2

1

≤

+

+

x

x

x

E9

A2 B2 C2

0

,

0

,

0

3

2

1

≥

≥

≥

x

x

x

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

=

*

1

x

85,1767,

=

*
2

x

766,645 oraz

=

*

3

x

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-
łę z funkcją celu lub ewentualnie
jedną z komórek zmienianych

Komórka celu

Ustaw cel

Domyślne ustawienie tego pola to
komórka bieżąca

Rodzaj optymalizacji: maksy-
malizacja, minimalizacja, ustalona
wartość liczbowa funkcji celu

Równa

Na

Domyślne ustawienie to Maks

Deklaracja zakresu/zakresów ko-
mórek pełniących rolę zmiennych

Komórki zmieniane

Przez zmienianie komórek
zmiennych

Brak ustawienia domyślnego

„Odgadnięcie” zakresu/zakresów
komórek pełniących rolę zmien-
nych na podstawie formuły z funk-
cją celu

Odgadnij

brak

Najczęściej „odgadnięcie” jest błędne

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
warunku ograniczającego

Dodaj

Dodaj

W Excelu 2010 zmieniono okno do-
dawania warunków ograniczających

Otwiera okno edycji istniejącego
warunku ograniczającego (wymaga
wybrania go na liście)

Zmień

Zmień

W Excelu 2010 zmieniono okno edy-
cji warunków ograniczających

Usuwa z listy istniejący warunek
ograniczający (wymaga wybrania
go na liście)

Usuń

Usuń

Otwiera okno opcji, umożliwiające
zmianę domyślnych ustawień
wpływających na proces obliczeń

Opcje

Opcje

W Excelu 2010 zmieniono okno opcji

Skasowanie wszystkich danych
wprowadzonych przez użytko-
wnika oraz przywrócenie opcji do-
myślnych

Przywróć wszystko

Resetuj wszystko

Zapis modelu z Solvera w postaci
grupy formuł w arkuszu

Dostępne jako Zapisz model/
Załaduj model

w oknie op-

cji)

Załaduj/zapisz

Ustawienie nieujemności wszyst-
kich zmiennych bez dodawania sto-
sownego warunku ograniczającego
do listy

Dostępne jako Przyjmij nieu-
jemne

w oknie opcji; domy-

ś

lnie niezaznaczone - brak

nieujemności wszystkich
zmiennych)

Ustaw wartości nieujemne
dla zmiennych bez ograni-
czeń

(domyślnie zaznaczone

- ustawiona nieujemność
wszystkich zmiennych)

Typ algorytmu używanego do obli-
czeń

Brak jawnego ustawienia
(przełączenie na algorytm
simpleks poprzez zaznacze-
nie opcji Przyjmij model li-
niowy

w oknie opcji)

Wybierz metodę rozwiązy-
wania

(ustawienie domyślne

jest opisane jako Nieliniowa
GRG

)

Ustawienie domyślne to algorytm
„uniwersalny” GRG, a alternatywą
jest algorytm simpleks dla programo-
wania liniowego. W Excelu 2010 do-
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óźniejszych (pozostałe zrzuty – okno z trzema zakładkami).