1Zastosowanie Solvera do przykładów dydaktycznych z optymalizacji
1.1. Wstęp
Jak już wspomniałem we Wprowadzeniu podczas wykładów z optymalizacji decyzji gospodarczych największe zainteresowanie wśród studentów wzbudziło zastosowanie dodatku Solver wchodzącego w skład arkusza kalkulacyjnego Excel. Chociaż przykłady dotyczyły tylko wybranych i przy tym drobnych obszarów, to i tak niektórzy ze słuchaczy widzieli potrzebę ich implementacji w swojej pracy zawodowej. Zarówno na kierunku Zarządzanie, jak i Logistyka są studenci, którzy prowadzą własne firmy, np. cięcia kamienia budowlanego, jak też ozdobnego. Było to zachętą do zaprezentowania ich w tej monografii[1]. Na wstępie jednak przyjrzyjmy się menu głównemu Excela w wersji 16.0 wchodzącego w skład pakietu Microsoft 365. Występują tu zakładki: Plik, Narzędzia główne, Wstawianie, Układ strony, Formuły, Dane, Recenzja, Widok, Pomoc. Po kliknięciu na zakładkę Dane zauważymy z prawej strony funkcję Solver (zob. rysunek 1.1).
Rys. 1.1. Solver w ramach opcji zakładki Dane
Źródło: Opracowanie własne na podstawie menu programu Microsoft Excel 16.0.
Jeśli mamy otwarty plik zadania decyzyjnego i ustawieni jesteśmy w komórce celu, to po akceptacji Solvera pojawia się nam okno dialogowe. Przykład ten dotyczy określenia optymalnej liczby produkcji wyrobów W1 i W2, jako zmiennych x1 i x2. Dla realizacji procesu optymalizacji musimy wprowadzić do tabeli danych formuły wykorzystania zasobów oraz formułę funkcji celu - widoczną na rysunku 1.2[2],[3]. Sformułowanie manualne zadania decyzyjnego optymalizacyjnego, dążącego do maksymalnej kwoty przychodu ze sprzedaży wyrobów, sprowadza się do określenia funkcji celu (FC), ograniczeń (Ci) oraz warunków brzegowych, w których zmienne są liczbami całkowitymi[4]:
FC: 30 x1 + 20 x2 ? max
C1: 2x1 + x2 ? 1000
C2: 3x1 + 3x2 ? 2400
C3: 1,5x1 ? 600
x1, x2 ? 0; x1, x2 ? C
Przyjmujemy wstępnie, że x1 i x2 równa się zero.
Rys. 1.2. Zdefiniowanie parametrów zadania decyzyjnego w Solverze
Źródło: Opracowanie własne na podstawie menu programu Microsoft Excel 16.0.
Procedura postępowania w Solverze jest następująca:
1. Rozplanowanie tabeli zadania decyzyjnego na arkuszu kalkulacyjnym Excela.
2. Wprowadzamy wyrażenia do komórek wykorzystania zasobów oraz komórki funkcji celu.
3. Ustawiamy się w komórce celu: $C$9.
4. Wywołujemy menu: Dane/Solver. Pojawia się okno dialogowe z zaznaczoną adresowaniem bezwzględnym komórką celu.
5. Wypełniamy komórki zmieniane: $D$3:$E$3; Dodajemy warunki ograniczające i brzegowe:
$C$5:C7<=$B$5:$B$7
$D$3:$E$3 >= 0 i $D$3:$E$3 int (warunek liczb całkowitych)
Warunki dodajemy poprzez pomocnicze okno dialogowe, np. - zob. rysunek 1.3:
Rys. 1.3. Dodanie ograniczenia całkowitoliczebności
Źródło: Opracowanie własne na podstawie menu programu Microsoft Excel 16.0.
6. Klikamy na przycisk Rozwiąż i w oknie dialogowym zaznaczamy wyświetlenie raportu wyników (rysunek 1.4).
Rys. 1.4. Zaznaczenie wygenerowania raportu wyników z procesu optymalizacji
Źródło: Opracowanie własne na podstawie menu programu Microsoft Excel 16.0.
Po OK pojawia się nam rozwiązanie w tabeli danych (zob. rysunek 1.5) i w skoroszycie Excela zostaje dopisany plik raportu (rysunek 1.6).
Rys. 1.5. Rozwiązanie zadania produkcji wyrobów
Źródło: Opracowanie własne na podstawie menu programu Microsoft Excel 16.0.
Rys. 1.6. Raport wyników
Źródło: Opracowanie własne na podstawie menu programu Microsoft Excel 16.0.
Zatem liczba wyrobów W1 = 200 szt., a wyrobu W2 = 600 szt. Funkcja celu, czyli przychód ze sprzedaży wynosi 18 000 zł - z uwzględnieniem cen wyrobów. Widzimy wykorzystanie zasobu S1 i S2 w pełni, a zasobu S3 tylko 300 kg, tak więc w magazynie pozostaje jeszcze: (600 - 300) = 300 kg. Na rysunku 1.5 widzimy też przykład formuły wykorzystania zasobu S1 w komórce C5.
W zaprezentowanym raporcie zwróćmy uwagę na wartości początkowe zmiennych decyzyjnych, które są równe zeru oraz końcowe po optymalizacji. Ponadto warto przeanalizować sposób zapisu formuł dotyczących ograniczenia zasobów, zmiennych, a także zaznaczenia warunku całkowitoliczebności.
1.2. Minimalizacja odpadów
Jako przykład rozpatrzmy następujące zadanie: Tartak otrzymał zamówienie na wykonanie kompletów belek składających się z: 300 kompletów, w tym belki (5 po 2,3 m, 4 po 2 m, 2 po 0,4 m); 100 kompletów, składających się z belek (2 po 2,3 m, 3 po 2 m, 3 po 0,4 m). Tartak ma do dyspozycji drewniane bale o długości 5,3 m. Należy tak pociąć bale, aby odpady były jak najmniejsze[5],[6].
Na początku musimy wyodrębnić kilka sposobów cięcia belek z bali i niech to będą sposoby zaprezentowane poniżej.
Długość [m]
Sposób 1
Sposób 2
Sposób 3
2,3
2
0
1
2,0
0
2
1
0,4
1
3
2
Odpad [m]
0,3
0,1
0,2
Obliczenie odpadów: sposób 1: 5,3 - (4,6 + 0,4); sposób 2: 5,3 - (4 + 1,2); sposób 3: 5,3 - (4,3 + 0,8). Potrzeba jeszcze określić liczbę poszczególnych belek w dwóch zamówieniach:
- belki 2,3 m (300 × 5 + 100 × 2) = 1700 szt.,
- belki 2,0 m (300 × 4 + 100 × 3) = 1500 szt.,
- belki 0,4 m (300 × 2 + 100 × 3) = 900 szt.
Przyjmijmy jako zmienne: x1 - liczba cięć (bali po 5,3 m) sposobem 1, x2 - liczba cięć sposobem 2, x3 - liczba cięć sposobem 3. Musimy zrealizować nasze dwa zamówienia, zatem ograniczenia stanowią liczby pociętych belek po 2,3 m, 2,0 m oraz 0,4 m. Jako warunki brzegowe wprowadzamy nieujemność (x1, x2, x3 ? 0 oraz całkowitoliczbowość zmiennych (x1, x2 i x3 jako "int"). Tak więc sformułowanie zadania decyzyjnego na minimalizację odpadu jest następujące:
FC: 0,3 x1 + 0,1 x2 + 0,2 x3 ? min
C1: 2x1 + 0x2 + 1x3 ? 1700
C2: 0x1 + 2x2 + 1x3 ? 1500
C3: 1x1 + 3x2 + 2x3 ? 900
x1, x2, x3 ? 0
x1, x2, x3 ? C
Podobnie jak dla przykładu podanego we Wstępie utwórzmy tabelę danych wejściowych z wprowadzeniem do odpowiednich komórek formuł w zakresie wykorzystania zasobów (komórki: C5:C7), jak też formułę funkcji celu (komórka: C9). W komórkach zmiennych wprowadzamy początkowo zero.
Rys. 1.7. Dane wejściowe zadania minimalizacji odpadu
Źródło: Opracowanie własne w Microsoft Excel 16.0.
Zwróćmy teraz uwagę na sposób wypełnienia okna dialogowego Parametry dodatku Solver. Jako metodę rozwiązywania wybrano LP simpleks dotyczącą problemów liniowych (zob. rysunek 1.8). Do dyspozycji jest jeszcze metoda nieliniowa GRG oraz ewolucyjna.
Po kliknięciu na Rozwiąż pojawia się komunikat z możliwością wskazania potrzeby raportu (rysunek 1.9).
Rys. 1.8. Parametry zadania minimalizacji odpadu
Źródło: Opracowanie własne w dodatku Solver.
Rys. 1.9. Komunikat o rozwiązaniu zadania i wskazanie potrzeby raportu wyników
Źródło: Opracowanie własne w dodatku Solver.
Solver zaproponował zastosowanie pierwszego sposobu cięcia bali 850 razy, drugiego 750 razy a z trzeciego zrezygnował. W tych warunkach minimalna liczba odpadu wynosi łącznie 330 m (zob. rysunek 1.10).
Rys. 1.10. Efekt postępowania iteracyjnego Solvera w zakresie optymalizacji cięcia bali
Źródło: Opracowanie własne w dodatku Solver.
Proponuję jeszcze zwrócić uwagę na podany w formie skróconej, wygenerowany raport wyników (rys. 1.11). Zadanie decyzyjne zrealizowane zostało w trzech iteracjach w czasie zaledwie 0,015 sekundy.
Rys.1.11. Fragment raportu wyników zadania decyzyjnego dotyczącego cięcia bali drewnianych
Źródło: Opracowanie własne w dodatku Solver.
Z dalszej części raportu dowiadujemy się, że zostały w pełni spełnione ograniczenia dotyczące belek 2,3 m oraz 2,0 m, a w odniesieniu do krótkich belek po 0,4 m występuje spora nadwyżka ilościowa, bo (3100 - 900) = 2200 sztuk.
1.3. Minimalizacja kosztu karmienia psów
Jako kolejny przykład rozpatrzmy problem minimalizacji kosztów: Hodowca psów rasowych uważa, że prawidłowo karmiony pies musi otrzymywać odpowiednie dzienne dawki witamin w wysokości przynajmniej: witaminy C - 150 j., D - 15 j. i A - 25 j. Hodowca może wykorzystać trzy specjalistyczne karmy dla psów, których ceny za 1 kg wynoszą odpowiednio: K1 - 7 zł., K2 - 5 zł., K3 - 9 zł. Należy przy tym pamiętać, że karmy K2 nie powinno się podawać więcej niż 450 g dziennie. Należy ułożyć liniowe zadanie decyzyjne minimalizujące koszt karmienia psów i rozwiązać problem w Solverze[7]. Zawartość witamin w 1 kg poszczególnych karm jest następująca:
Witamina [j.]
K1
K2
K3
C
150
100
175
D
20
10
15
A
0
25
30
Cena [zł]
7
5
9
Przyjmijmy jako zmienne: x1 - ilość karmy K1, x2 - ilość karmy K2, x3 - ilość karmy K3. Ograniczenia stanowią: dawka witaminy C, dawka witaminy D, dawka witaminy A, ilość maksymalna karmy K2. Przy tych założeniach sformułowane zadanie decyzyjne ma następującą postać:
FC: 7 x1 + 5 x2 + 9 x3 ? min
C1: 150 x1 + 100 x2 + 175 x3 ? 150
C2: 20 x1 + 10 x2 + 15 x3 ? 15
C3: 0 x1 + 25 x2 + 30 x3 ? 25
C4: 0 x1 + 1 x2 + 0 x3 ? 0,45
x1, x2, x3 ? 0
Utwórzmy podobnie jak dla wcześniejszego przykładu tabelę danych wejściowych do Solvera. Ustawiamy się w komórce celu, określamy formuły wykorzystania zasobów, formułę funkcji celu i w wyniku postępowania iteracyjnego uzyskujemy efekt zaprezentowany na rysunku 1.12. Tak więc optymalne dzienne dawki karm wynoszą: K1 = 0,1813 kg, K2 = 0,45 kg, K3 = 0,4583 kg, a minimalny koszt karmienia psów rasowych wyniesie wówczas 7,64 zł.
Rys. 1.12. Tabela wynikowa po optymalizacji kosztu karmienia psów
Źródło: Opracowanie własne w dodatku Solver.
Prawidłowe określenie parametrów Solvera w oknie dialogowym jest bardzo istotne i dlatego zaprezentowano jeszcze jego skróconą postać na rysunku 1.13.
Rys. 1.13. Parametry zadania decyzyjnego w Solverze dotyczące minimalizacji dziennego kosztu karmienia psów
Źródło: Opracowanie własne w dodatku Solver.
1.4. Maksymalizacja liczby kompletów
Zaprezentowany teraz przykład wzbudził, w trakcie ćwiczeń z przedmiotu optymalizacja decyzji gospodarczych, duże zainteresowanie studentów. Pojawiła się jednak trudność w określeniu zmiennych, zwłaszcza zmiennej - liczba kompletów, a tekst tego zadania jest następujący: Firma wycina kształtki A i B ze standardowego paska blachy czterema sposobami. Liczba kształtek uzyskanych przy danym sposobie wycinana jest następująca[8],[9]:
I
II
III
IV
A
3
5
2
6
B
4
3
6
2
Ze względów technologicznych częstość stosowania czwartego sposobu cięcia nie może przekraczać 50% drugiego sposobu cięcia. Firma sprzedaje kształtki w kompletach: 1 kształtka A i 2 kształtki B. Firma posiada 50 pasków blachy. Celem firmy jest maksymalizacja liczby kompletów. Należy sformułować problem w postaci zadania programowania liniowego (PL).
Jako zmienne przyjęto: x1 - krotność stosowania I sposobu cięcia (liczba zużytych pasków blachy), x2 - krotność stosowania sposobu II, x3 - krotność stosowania sposobu III, x4 - krotność stosowania sposobu IV, y - liczba kompletów kształtek A i B. Ograniczeniami są: liczba pasków blachy, liczba kształtek A w kompletach, liczba kształtek B w kompletach, krotność stosowania IV sposobu cięcia. Ponadto założono nieujemność zmiennych oraz ich całkowitoliczbowość. Przy tych założeniach nasze zadanie decyzyjne można sformułować następująco:
FC: y ? max
C1: x1 + x2 + x3 + x4 ? 50
C2: 3x1 + 5x2 + 2x3 + 6x4 ? y
C3: 4x1 + 3x2 + 6x3 + 2x4 ? 2y
C4: 0x1 + 0x2 + 0x3 + 1x4 ? 0,5x2
x1, x2, x3, x4 ? 0
x1, x2, x3, x4, y ? C
Efektem pracy Solvera jest krotność sposobów cięcia W1 = 0, W2 = 12, W3 = 38, W4 = 0 oraz liczba kompletów y = 132 (zob. rysunek 1.14).
Rys. 1.14. Rozwiązanie zadania decyzyjnego maksymalizacji liczby kompletów kształtek
Źródło: Opracowanie własne w dodatku Solver.
Zobaczmy jeszcze określenie parametrów dodatku Solver (rys. 1.15).
Rys. 1.15. Fragment okna z parametrami dodatku Solver zadania dotyczącego maksymalizacji liczby kompletów
Źródło: Opracowanie własne w dodatku Solver.
1.5. Uwzględnienie wcześniejszego kontraktu
Mamy teraz następujące zadanie decyzyjne: Przedsiębiorstwo wytwarza dwa produkty P1 i P2 wykorzystując w tym celu surowce S1 i S2, których zasoby wynoszą S1 = 12 kg, S2 = 36 kg. Do produkcji P1 zużywa się 4 kg S1 oraz 6 kg S2, natomiast do wytworzenia P2 potrzeba 2 kg S1 i 6 kg S2. Zysk ze sprzedanej jednej sztuki P1 wynosi 40 tys. zł, a ze sprzedaży P2 - 10 tys. zł. Przedsiębiorstwo podpisało kontrakt na dostawę jednostki P1 i dwóch jednostek P2 i musi się z tego wywiązać. Ułóż liniowe zadanie decyzyjne i rozwiąż je w Solverze, w celu uzyskania maksymalnego zysku z produkcji wyrobów P1 i P2[10].
Określenie zmiennych: x1 - liczba produktów P1, x2 - liczba produktów P2. Ograniczeniami są: zasób surowca S1, zasób surowca S2, kontrakt na dostawę 1 sztuki P1, kontrakt na dostawę 2 sztuk P2. Ponadto nieujemność i całkowitość zmiennych x1 i x2. Przy tych założeniach sformułujmy teraz zadanie decyzyjne:
FC: 40x1 + 10x2 ? max
C1: 4x1 + 2x2 ? 12
C2: 6x1 + 6x2 ? 36
C3: 1x1 + 0x2 ? 1
C4: 0x1 + 1x2 ? 2
x1, x2 ? 0; x1, x2 ? C
Inicjujemy zmienne x1, x2 wartościami zero w komórkach D3 i E3 arkusza kalkulacyjnego Excel. W komórkach C5:C7 wpisujemy formuły wykorzystania zasobów, a w komórce C10 formułę na funkcje celu. Efekt pracy Solvera pokazano na rysunku 1.16. Rozwiązaniem jest x1 = 2 szt. oraz x2 = 2 szt., a maksymalny zysk wynosi 100 zł.
Rys. 1.16. Rozwiązanie zadania optymalizacyjnego z określeniem wstępnym zawartych kontraktów
Źródło: Opracowanie własne w dodatku Solver.
Parametry Solvera, ze wskazaniem komórek, zastosowane do omawianego zadania sformułowane są następująco:
funkcja celu - komórka $C$10,
komórki zmieniane: $D$3:$E$3,
ograniczenia ? $C$5 ? $B$5, $C$6 ? $B$6, $C$7 ? $B$7, $C$8 ? $B$8, $D$3:$E$3 = całkowita, $D$3:$E$3 ? 0.
Przypisy
[1] Niektóre z przykładów, ujętych w tej części monografii, bazują na propozycjach zagadnień optymalizacyjnych zawartych w publikacjach firmy Microsoft Corporation:
- Analiza arkusza, wymiana danych, tworzenie, automatyzacja prac, Podręcznik użytkownika, tom 2;
- Praca z pakietem Microsoft Office 97. Konkretne rozwiązania w codziennej pracy, Ireland 1995;
- Microsoft Excel. Arkusz kalkulacyjny z grafiką reprezentacyjną i bazą danych, wersja 4.0 dla Windows TM, Ireland 1992;
- Microsoft Excel. Opis funkcji, Ireland 1993.
[2] To zadanie, jak i dalsze zaprezentowane w tym rozdziale, wykonywane były na laptopie w trakcie zajęć z przedmiotu optymalizacja decyzji gospodarczych.
[3] W opracowaniu, przy redagowaniu tekstów przykładów, bazowano na analogicznych problemach decyzyjnych zademonstrowanych w książce: M. Duczmal, W. Wornalkiewicz, Wstęp do ekonometrii i badań operacyjnych. Zbiór przykładów z zastosowaniem mikrokomputera, Instytut Śląski, Opole 2010.
[4] Przykład stanowi analogię do zadania 2 (s. 21) zawartego w książce: M. Anhplcer, H. Gaspars, A. Owczarkowski, Przykłady i zadania z badań operacyjnych i ekonometrii, Wydawnictwo Akademii Ekonomicznej w Poznaniu, Poznań 2003.
[5] Temat stanowi analogię do przykładu 6 (s. 43) zamieszczonego [w:] Z. Jędrzejczak, K. Kukuła, J. Skrzypek, A. Walkosz, Badania operacyjne w przykładach i zadaniach, K. Kukuła (red. nauk.,), Wydawnictwo Naukowe PWN, Warszawa 2004.
[6] Temat bazuje na przykładzie 3 (s. 10): M. Anholcer, H. Gaspars, A. Owczarkowski, Przykłady i zadania z badań operacyjnych i ekonometrii, Wydawnictwo Akademii Ekonomicznej w Poznaniu, Poznań 2003.
[7] Przy opracowaniu tego tematu oparto się na przykładzie 2 (s. 8): M. Anholcer, H. Gaspars, A. Owczarkowski, Przykłady i zdania z badań operacyjnych i ekonometrii, Wydawnictwo Akademii Ekonomicznej w Poznaniu, Poznań 2003.
[8] Temat stanowi analogię do zadania 28 (s. 47) podanego [w:] Z. Jędrzejczak, K. Kukuła, J. Skrzypek, A. Walkosz, Badania operacyjne w przykładach i zadaniach, K. Kukuła (red. nauk.), Wydawnictwo Naukowe PWN, Warszawa 2004.
[9] Temat bazuje na przykładzie 4 (s. 12) opracowania: M. Anholcer, H. Gaspars, A. Owczarkowski, Przykłady i zadania z badań operacyjnych i ekonometrii, Wydawnictwo Akademii Ekonomicznej w Poznaniu, Poznań 2003.
[10] Tekst przykładu bazuje na zadaniu 2 (s. 21) w książce: M. Anholcer, H. Gaspars, A. Owczarkowski, Przykłady i zadania z badań operacyjnych i ekonometrii, Wydawnictwo Akademii Ekonomicznej w Poznaniu, Poznań 2003.
