EXCEL SOLVER – optymalizacja

        Wiele pisałem o Excelu, o nowościach książkowych na ten temat. Czasami mam też jakieś „proszone” wykłady na uczelniach z racji jakiś tam kontaktów, czy wcześniejszych spotkań. Jak zapewne wszyscy wiedzą, którzy śledzą mojego bloga, że to moje ulubione narzędzie. Ostatnio, ktoś z moich znajomych zapytał mnie, czemu właśnie na tym swoim blogu nie próbuje pokazywać możliwości tego ulubionego przeze mnie narzędzia. Tak się składa, że moi znajomi to bardzo mądrzy ludzie – postanowiłem więc posłuchać rady. Na pewno nie będzie to kolejny e-book z serii „jak korzystać z Excela – 100 sekretnych sposobów o których nikt nie wie” albo coś w tym rodzaju – czego tysiące krążą po sieci nierzadko odpłatnie.     Postanowiłem więc i obiecałem to Państwu, że jeżeli mam posłuchać rady znajomego (dziękuje Michale), to będę starał skłonić Państwa do zaprzyjaźnienia się z tym narzędziem. Będę próbował odkrywać te jego fragmenty, które do czegoś prowadzą, do czegoś inspirują. Jako że książka jest również moim ulubionym tematem no i tematem numer jeden tego bloga, tam gdzie będzie tylko możliwość, będę się starał prezentować przykłady z książek – po to by namawiać Państwa do lektury dobrych podręczników. Czy mi się to uda – sami Państwo ocenicie. To tyle tytułem wstępu.

        Dziś chciałbym przedstawić Państwu dodatek do Excela – Solver. Każdy z Państwa, użytkowników dowolnej wersji Excela, może go sobie doinstalować. Niestety, niewiele książek go omawia, a nawet o nim wspomina, stąd dzisiejsze przykłady postaram się wymyślić sam i zaręczam będą bardzo proste. Na początek – jak zainstalować ten dodatek. W każdej wersji (2007, 2010 czy 2013) nieco inaczej. Najlepiej sięgnąć do pomocy (klawisz F1 albo „?” w prawym górnym rogu paska) gdzie prosto to opisano. Na zrzutach z ekranu, które załączam, będzie to Excel 2010. Jak już zainstalujemy ten dodatek to zakładka „DANE” będzie wyglądała następująco:

Po zainstalowaniu SolveraTo informacja dla nas, że Solver jest już doinstalowany.

Wyobraźmy sobie, że nasza firma produkuje konserwy. Dla uproszczenia są one w kształcie prostopadłościanu. Mamy tak zaprojektować wymiary tej konserwy, by przy stałej założonej jej objętości – 100cm3 zużyć najmniej blachy na jej wykonanie – bo oszczędzamy nie tylko kiedy jest kryzys. Aktualnie maja one wymiary 2cmx5cmx10cm – czy to dobry kształt ?założenia Sprawdźmy. Zapiszmy zależności czyli wymiary naszej konserwy, wprowadźmy wzór na objętość i powierzchnie blachy. Formuły wpisane w komórki B5 i B6 są pokazane obok nich.    Widzimy, że aktualnie na jedną konserwę zużywa- my aż 160cm2 blachy.  Przejdźmy wobec tego do zakładki „DANE” i przyciśnijmy prawym klawiszem „SOLVER”. Pojawi nam się okienko, w którym ustalimy wszystkie parametry.

    Naszym celem jest powierzchnia blachy czyli komórka B6 – zaznaczmy ją w oknie „ustaw cel”. Następnie zaznaczmy, iż chodzi nam o minimum w tej komórce. Określmy, które komórki można zmieniać – oczywiście w tym przypadku będą to wymiary puszki. Następnie wstawmy ograniczenia. W tym przypadku założyłem sobie, że żaden z boków puszki nie może być mniejszy niż 2 cm – no i najważniejsze – stała musi być objętość puszki – czyli w tym przypadku 100cm3. Nie chce wnikać w wybór metody rozwiązywania. Ci z Państwa, którzy spotkali się z zagadnieniem optymalizacji funkcji, będą orientować się w proponowanych przez Solver metodach. Wykonaliśmy całą pracę jaka do nas należała no i teraz po naciśnięciu klawisza „Rozwiąż” niech działa Solver. Po jakimś czasie (to już zależy od mocy komputera, ja specjalnie robiłem to na najstarszym komputerze jakim dysponuje i trwało to może ze dwie minuty) otrzymujemy taki obraz:Rozwiązanie naszego problemu

Jak widzimy wymiary naszej puszki się zmieniły (komórki B3, B4), zmienił się również nasz „cel” czyli powierzchnia użytej blachy. Zaoszczędziliśmy 3cm2 blachy na jednej puszce ! Czy to istotnie jest minimum powierzchni blachy? Użyjmy innej metody. Wróćmy do okna dialogowego parametrów dodatku Solver (zaznaczmy tą opcje). Wybierzmy metodę „nieliniowa GRG”. I znowu nie chcę się wgłębiać w jej opis i „użyteczność” – po prostu spróbujmy, może ona da jeszcze lepsze wyniki. Po sekundzie (!) mamy wynik, który jest super. Solver_najlepszy_wynik

Oszczędzamy 31cm2 blachy ! To prawie 20% ! Myślę, że możemy z tym wynikiem iść do szefa produkcji, dyrektora ekonomicznego i na efekty (gratyfikacje) nie długo trzeba będzie czekać.

Oczywiście, wybrałem tu bardzo prosty przykład. Dla wtajemniczonych oczywistym jest, że minimum tej funkcji leży w punkcie, gdzie wszystkie boki mają ten sam wymiar. Ale nie wychodząc poza problem „puszki”, gdy założymy, iż jej podstawa jest okręgiem – zadanie się trochę komplikuje. Proponuje pobawić się trochę tym frapującym, prawdziwie inżynierskim zajęciem. A może macie Państwo inny problem do rozwiązania – Excel i Solver to para, która może znacznie pogrubić nasze portfele – czego Państwu i sobie życzę.

Ten wpis został opublikowany w kategorii Excel i oznaczony tagami , , . Dodaj zakładkę do bezpośredniego odnośnika.

2 odpowiedzi na „EXCEL SOLVER – optymalizacja

  1. mark pisze:

    Super, wszystkie przykłady solvera ograniczają się do 4-5 komórek których wartość jest istotna w rozwiązaniu zagadnienia, tak to ja sobie mogę kalkulatorem obliczyć. Kiedy pojawia się problem i mamy 40 zmiennych oraz 10-12 decyzyjnych komórek do wyboru solver zamula całego kompa i po 2 godzinach wypluwa bzdury.
    Mam pytanie do fachowca-
    Czy solver nadaje się do ww grubszych problemów czy też zabrać się już za C++ ??
    pozdrawiam

    • admin pisze:

      Serdecznie dziękuje za komentarz.
      Ze względu na charakter wpisu używałem tylko prostych przykładów.
      W przeciwnym razie musiał bym zagłębić się w zagadnienia znacznie przerastające formułę tego bloga. Nie jest moim zamiarem pisanie podręcznika z zakresu metod numerycznych – chodź nie ukrywam, że to mnie pasjonuje. Próbując odpowiedzieć na Pana pytanie powiem iż jeżeli zagadnienia są nieco poważniejsze bądź poważne to czy zastosujemy C++, Visual Basic, Excela czy coś innego – to my definiujemy zadanie i parametry jego liczenia. Nie da się tego przeskoczyć bez znajomości analizy matematycznej chociażby w zakresie ustalenia dokładności obliczeń, kwantowalności i znajomości metody, którą stosujemy (czytaj metody jaką na przykład stosuje Solver). To są naprawdę bardzo ciekawe rzeczy, ale bez ich znajomości i nie do końca przez to, poprawne zdefiniowanie parametrów metody, każdy komputer możemy zmusić do nawet wielodniowej pracy, a co gorsze bez zadawalających nas wyników. Wystarczy wyobrazić sobie lokalne siodełko wartości funkcji, w którym przy źle zdefiniowanych parametrach, będziemy zmuszać komputer do wyliczeń funkcji celu w tym przedziale – gdy faktyczne optimum tej funkcji będzie zupełnie gdzie indziej. Jeżeli więc mogę coś doradzić, to dokładne zapoznanie się z zastosowaną metodą (najczęściej mamy tu pewien wybór) i rozpoczynanie obliczeń od małych dokładności, by ją zwiększać w miejscach, które uznamy za ważne pod względem funkcji celu.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Możesz użyć następujących tagów oraz atrybutów HTML-a: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>