Microsoft EXCEL – SOLVER

11.. PPrrooggrraammoowwaanniiee lliinniioowwee zz wwyykkoorrzzyyssttaanniieemm ddooddaattkkuu MMiiccrroossoofftt EExxcceell SSoollvveerr

Cele Po ukończeniu tego laboratorium słuchacze potrafią korzystając z dodatku Solver:

• formułować funkcję celu, • definiować warunki ograniczające, • przeprowadzić minimalizację (maksymalizację) funkcji celu.

Ćwiczenie 1.1

DDeeffiinniioowwaanniiee zzaaddaańń zz wwyykkoorrzzyyssttaanniieemm ddooddaattkkuu SSoollvveerr W tym ćwiczeniu należy zapoznać się z definiowaniem zadań z wykorzystaniem dodatku Solver. 1. Instalowanie dodatku Solver Jeżeli w menu Narzędzia programu Excel nie występuje opcja Solver, to należy ją zainstalować. W tym celu:

z menu Narzędzia wybierz opcję Dodatki,

zaparafuj dodatek Solver i kliknij przycisk OK., jeśli wyświetlany jest komunikat pytający,czy zainstalować funkcję, kliknij

przycisk Tak. rozwiń ponownie menu Narzędzia i sprawdź, czy dodatek Solver jest dostępny.

2. Podstawowe narzędzia Solvera. W menu Narzędzia kliknij polecenie Solver. Pojawi się okno Solver – Parametry.

W polu Komórka celu podaje się adres lub nazwę komórki docelowej. Komórka celu musi zawierać formułę. Jeżeli rozwiązujemy zadanie związane z maksymalizacją funkcji celu, czyli oczekujemy jak największej wartości w komórce docelowej, wybieramy opcję Maks. Analogicznie w przypadku minimalizacji funkcji celu wybieramy opcję Min. Aby określić wartość w komórce docelowej, należy kliknąć opcję Wartość i wpisać wartość w dostępnym polu. W polu Komórki zmieniane podajemy nazwę lub adres każdej komórki zmiennej, oddzielając przecinkami adresy nie przylegających komórek. Komórki zmiennej muszą być bezpośrednio lub pośrednio związane z komórką docelową. Można określić maksymalnie 200 komórek zmienianych.

Aby w dodatku Solver automatycznie zaproponować komórki zmieniane dla komórki celu: 1. Kliknij przycisk Odgadnij. 2. W polu Warunki ograniczające (na dole strony) wprowadź wszystkie ograniczenia,

które chcesz zastosować. 3. Kliknij przycisk Rozwiąż.

Aby zanotować wyniki w arkuszu, kliknij przycisk Przechowaj rozwiązanie w oknie

dialogowym Solver - Wyniki.

Aby przywrócić pierwotne wartości, kliknij przycisk Przywróć wartości początkowe.

3. Zasady projektowania modeli poszukiwania rozwiązań za pomocą dodatku Solver Dodatek Solver pozwala zoptymalizować wartość formuły w jednej z komórek arkusza – nazywanej komórką celu. Zakresem działania jest grupa komórek związanych bezpośrednio lub pośrednio z formułą w komórce celu. Wartości w komórkach określonych przez użytkownika – nazywanych komórkami zmienianymi – są zmieniane tak, aby osiągnąć żądany wynik w komórce celu. Zakres zmian wartości występujących w modelu można ograniczyć, wprowadzając ograniczenia. Mogą one także dotyczyć innych komórek, które mają wpływ na formułę w komórce celu. Ograniczenia wprowadzone w problemie programu Solver. Można zastosować ograniczenia do ustawianych (zmiennych) komórek, do komórki docelowej lub innych komórek bezpośrednio lub pośrednio związanych z komórką docelową. Dla problemów liniowych nie ma ograniczenia liczby ograniczeń. Dla problemów nieliniowych każda ustawiana komórka może mieć następujące ograniczenia: ograniczenie dwójkowe, ograniczenie całkowite z górną, dolną lub obydwiema granicami. Ponadto można określić górną lub dolną granicę dla nie więcej niż 100 innych komórek. W ograniczeniach można używać następujących operatorów.

Operator Znaczenie <= Mniejsze niż lub równe >= Większe niż lub równe = Równe

int Całkowita (dotyczy tylko ustawianych komórek) bin Dwójkowa (dotyczy tylko ustawianych komórek)

Analiza problemu wielu zmiennych Dodatek Solver pozwala znaleźć maksymalną lub minimalną wartość jaką może osiągnąć pewna komórka po zmianie innych komórek – na przykład maksymalny zysk, który można osiągnąć, zmieniając wydatki na reklamę. Wybrane komórki muszą być powiązane ze sobą za pomocą formuł. W przeciwnym razie zmiany wartości w jednej z komórek nie spowodują zmian wartości w pozostałych komórkach. Algorytmy i metody używane w dodatku Solver W dodatku Microsoft Excel Solver wykorzystano program nieliniowej optymalizacji Generalized Reduced Gradient (GRG2), który opracowali Leon Lasdon z University of Texas w Austin oraz Allan Waren z Cleveland State University. W zagadnieniach liniowych i całkowitych wykorzystano metodę simpleks z ograniczeniami na zmienne oraz metodę "branch-and-bound", którą zaimplementowali John Watson i Dan Fylstra z Frontline Systems, Inc.

Ćwiczenie 1.2

RRoozzwwiiąązzyywwaanniiee uukkłłaaddóóww rróówwnnaańń lliinniioowwyycchh zz wwyykkoorrzzyyssttaanniieemm ddooddaattkkuu SSoollvveerr W tym ćwiczeniu należy rozwiązać układy równań liniowych z wykorzystaniem dodatku Solver. Harmonogram zadań: 1. Otwórz arkusz kalkulacyjny MS Excel wprowadź układ równań do rozwiązania (jak na

rysunku poniżej). 2. W komórce E7 wpisz x, a w komórce F7 wpisz y. Komórki E7 i F7 nazywają się

komórkami zmienianymi (Solver będzie czerpał z nich dane do obliczeń). Wartości początkowe w tych komórkach wynoszą 0.

3. W komórce D10 wpisz L(1)=, w komórce D11: L(2)=, a w komórce D12: P(1) + P(2)=, L(1) oznacza lewą stronę równania (1), a P(1) – jego prawą stronę (2).

4. W komórce E10 wpisz wzór odpowiadający lewej stronie równania (1) =7*E8-2*F8, a w komórce E11 wzór odpowiadający lewej stronie równania (2) = -3*E8+5*F8.

5. W komórce E12, która będzie komórką celu wpisz wzór sumujący prawe strony obu równań, czyli = E10+E11.

6. Zapisz arkusz w swoim folderze. 7. Po wpisaniu odpowiednich adresów wybierz z menu Narzędzia opcję Solver. 8. Uruchomienie Solvera powoduje otworzenie się okna dialogowego Solver – Parametry.

Wpisz w polu Komórka celu adres, w polu Równa się zaznacz wartości i wpisz 24 (jest to suma prawych stron równości (1) i (2). W polu Komórki zmieniane wpisz adresy komórek zmienianych czyli E8 i F8.

9. Po wykonaniu wyżej wymienionych czynności w polu Warunki ograniczające kliknij

przycisk Dodaj. Otworzy się okno dialogowe Dodaj warunek ograniczający, gdzie w polu Adres komórki wpisz E10, następnie za pomocą przycisku ze strzałką wybierz operator : =, a w polu Warunek ograniczający wpisz 13 (tyle wynosi prawa strona równania (1)).

10. Ponownie kliknij Dodaj i powtórz te same czynności wpisując adres komórki E11 i

warunek ograniczający = 11 (prawa strona równania (2)) i wyjdź przez wciśnięcie OK..

11. Kliknij polecenie Rozwiąż i po chwili otrzymują odpowiedź w rozwiniętym oknie Solver – Wyniki, że Solver znalazł rozwiązanie układu. W komórkach E8 i F8 pojawiają się poszukiwane wartości x= 3 oraz y= 4

12. Zapisują scenariusz, naciskając Zapisz scenariusz, a w oknie dialogowym Zapisz scenariusz w polu Nazwa scenariusza wpisują – Zadanie 1 i wyjdź przez OK.

Uwaga: Program iteracyjnie poszukiwał w wyznaczonych komórkach E8 i F8 (nazwanymi komórkami decyzyjnymi) wartości liczbowych, dopóki nie znalazł takich, które spełniły zadane kryteria. Kryteria te zostały zapisane w postaci wzorów w komorkach: E10, E11, E12. Zatem algorytm rozwiązania polegał na znalezieniu takich wartości liczbowych x i y aby po dodaniu lewych stron tych równań otrzymać liczbę równą sumie prawych stron równań. Zadania dodatkowe: 1. Zbuduj model uogólniony do rozwiązywania układów równań liniowych z dwoma niewiadomymi i rozwiąż układy:

a) 5x + 3y – 7 = 23x –4 -9x + 5y = 2 – 8x

b) 2x – y = -3 -4x + 2y = 6

c) x + y = 1 3x + 3y = 5

2. Rozwiąż powyższe układy metodą wykreślną 3. Zbuduj model uogólniony do rozwiązywania układów równań liniowych z trzema niewiadomymi i rozwiąż układy:

a) x + y = 3 y + z = 5 z + x = 4

b) 2x – 3y + z = 0 3x – 2y = 8 2x – 3z = -11

c) 3(x-1) + 2(y-2) + (z – 7) = -8 x + 2y + 7z = 10 x – y = 0