W tej lekcji kontynuujemy temat optymalizacji z użyciem narzędzia Solver w Excelu, ale tym razem skupimy się na bardziej złożonych scenariuszach. Przeanalizujemy, jak można rozwiązywać wielokryterialne problemy decyzyjne, ustawiać zaawansowane ograniczenia oraz jak Solver może być wykorzystany w różnych kontekstach, takich jak zarządzanie zasobami, planowanie finansowe i logistyka. Nauczymy się również, jak tworzyć różne warianty optymalizacyjne i jak analizować wyniki, aby podejmować najlepsze decyzje.

Wprowadzenie do wielokryterialnych problemów decyzyjnych

W rzeczywistości wiele problemów decyzyjnych wymaga rozważenia więcej niż jednego celu. Na przykład, firma może chcieć maksymalizować zysk, minimalizując jednocześnie koszty produkcji i utrzymując wysoką jakość produktów. Tego typu problemy wymagają podejścia wielokryterialnego, gdzie Solver pomaga w znalezieniu równowagi między różnymi celami.

Przy użyciu Solver możesz optymalizować funkcję celu (np. maksymalizować zysk) przy spełnieniu wielu ograniczeń (np. limity zasobów, koszty, minimalna liczba wyprodukowanych jednostek).

Rozszerzenie funkcji celu

W prostszych scenariuszach Solver pozwala na optymalizację jednej funkcji celu (np. maksymalizacja zysku). Jednak w bardziej złożonych sytuacjach możesz mieć wiele celów, które chcesz osiągnąć. Przykład: Firma może chcieć zmaksymalizować zysk, ale jednocześnie minimalizować koszty transportu i utrzymać optymalny poziom zapasów.

Aby rozwiązać problem z wieloma celami, można podejść do tego na dwa sposoby:

1. Zdefiniowanie priorytetów: Skupiasz się na jednym celu (np. maksymalizacja zysku) jako funkcji celu, a inne cele (np. minimalizacja kosztów) ustawiasz jako ograniczenia.
2. Wprowadzenie wielokryterialnej funkcji celu: Możesz zsumować różne cele do jednej funkcji, stosując wagi, które reprezentują znaczenie każdego celu. Przykład: Możesz stworzyć funkcję celu, która będzie kombinacją zysku i kosztów z wagami, które określają, jak ważne są poszczególne czynniki.

Przykład wielokryterialnej optymalizacji: Planowanie produkcji

Rozważmy firmę, która produkuje dwa rodzaje produktów: Produkt A i Produkt B. Celem firmy jest maksymalizacja zysku, ale jednocześnie chcą minimalizować koszty produkcji oraz utrzymać zapasy na minimalnym poziomie.

Dane:

• Produkt A przynosi 40 zł zysku za sztukę, a jego koszt produkcji wynosi 30 zł.
• Produkt B przynosi 60 zł zysku za sztukę, a jego koszt produkcji wynosi 50 zł.
• Firma ma do dyspozycji 200 godzin pracy, gdzie Produkcja A wymaga 3 godziny pracy na jednostkę, a Produkcja B wymaga 5 godzin.
• Firma musi wyprodukować co najmniej 20 sztuk każdego produktu, a zapasy powinny być minimalne.

Krok 1: Przygotowanie danych w arkuszu

1. Wprowadź dane o zyskach i kosztach dla każdego produktu w arkuszu:

• W komórkach A1 i B1 wpisz „Produkt A” i „Produkt B”.
• W komórkach A2 i B2 wpisz zysk na jednostkę (40 i 60).
• W komórkach A3 i B3 wpisz koszt na jednostkę (30 i 50).
• W komórkach A4 i B4 wpisz godziny pracy na jednostkę (3 i 5).
• W komórkach A5 i B5 wpisz wymagane minimalne zapasy (20 i 20).

2. Wprowadź zmienne decyzyjne (liczba sztuk produktów):

• W komórkach A6 i B6 wprowadź początkową wartość 0 (Solver będzie optymalizować te wartości).

3. W komórce C2 wprowadź formułę obliczającą całkowity zysk:

=A6*A2 + B6*B2

4. W komórce C3 wprowadź formułę obliczającą całkowity koszt produkcji:

=A6*A3 + B6*B3

5. W komórce C4 wprowadź formułę obliczającą całkowitą liczbę godzin pracy:

=A6*A4 + B6*B4

Krok 2: Ustawienie Solver dla wielokryterialnej optymalizacji

1. Funkcja celu: W tym przykładzie funkcją celu będzie maksymalizacja zysku, czyli komórka C2.

• Przejdź do zakładki Data i wybierz Solver.
• W polu Set Objective wybierz komórkę C2.
• Wybierz Max (maksymalizacja).

2. Zmienna decyzyjna: Zmiennymi decyzyjnymi będą liczby sztuk Produktu A i Produktu B, więc w polu By Changing Variable Cells wprowadź zakres A6.

3. Ograniczenia: Dodaj ograniczenia dla problemu:

• Całkowita liczba godzin pracy (komórka C4) musi być mniejsza lub równa 200 (liczba dostępnych godzin pracy). Dodaj ograniczenie C4 <= 200.
• Liczba wyprodukowanych sztuk Produktu A i Produktu B musi być większa lub równa 20 (minimalna liczba zapasów). Dodaj ograniczenia A6 >= 20 oraz B6 >= 20.

4. Minimalizacja kosztów: Aby zminimalizować koszty, możesz dodać to jako dodatkowy cel, przekształcając problem w podejście z wagami:

• – Możesz użyć formuły, która obliczy „ważony cel”. Na przykład, wprowadź wagi dla zysku i kosztów w innych komórkach (np. 0.7 dla zysku i 0.3 dla kosztów).
• – Stwórz formułę, która połączy oba cele w jedną funkcję: markdown

=0.7*C2 - 0.3*C3

• – Ustaw tę komórkę jako funkcję celu w Solver.

5. Rozwiązanie: Po skonfigurowaniu wszystkich parametrów kliknij Solve.

Krok 3: Analiza wyników

Solver znajdzie optymalną liczbę sztuk Produktu A i Produktu B, które należy wyprodukować, aby zmaksymalizować zysk, jednocześnie minimalizując koszty produkcji i spełniając wszystkie ograniczenia dotyczące pracy oraz minimalnych zapasów. W wyniku otrzymasz optymalne rozwiązanie, które uwzględnia wszystkie cele.

Zaawansowane ograniczenia

Solver umożliwia dodanie zaawansowanych ograniczeń, które pozwalają na modelowanie jeszcze bardziej złożonych problemów decyzyjnych. Oto kilka przykładów:

1. Ograniczenia dotyczące zmiennych całkowitych: Możesz ustawić, aby zmienne decyzyjne były liczbami całkowitymi. Jest to przydatne w sytuacjach, gdy musisz wyprodukować pełne jednostki (np. nie można wyprodukować 0,5 produktu).
   • Aby to zrobić, w oknie Solver kliknij Add, wybierz zmienną decyzyjną i wybierz opcję int (integer).
2. Ograniczenia dotyczące maksymalnej produkcji: W niektórych przypadkach może istnieć limit na maksymalną liczbę sztuk, które można wyprodukować. Możesz dodać ograniczenie, które określi maksymalną liczbę produktów do wyprodukowania.
3. Zmienność zmiennych: Jeśli chcesz, aby pewne zmienne były ograniczone do konkretnego przedziału wartości, możesz to zrobić poprzez dodanie odpowiednich ograniczeń. Na przykład, zmienna decyzyjna może mieć zakres od 10 do 50.

Optymalizacja w różnych scenariuszach

Po znalezieniu jednego optymalnego rozwiązania możesz chcieć przetestować różne scenariusze. Możesz na przykład zobaczyć, co się stanie, gdy zmienią się dostępne zasoby pracy lub koszty produkcji. Scenariusze można łatwo przetestować, zmieniając wartości w arkuszu i ponownie uruchamiając Solver.

Scenariusze: Zmiana kosztów produkcji

Załóżmy, że koszty produkcji Produktu A i Produktu B wzrosły i chcesz zobaczyć, jak to wpłynie na optymalną liczbę jednostek do wyprodukowania. Możesz po prostu zmienić wartości kosztów w komórkach A3 i B3, a następnie ponownie uruchomić Solver, aby zobaczyć, jak zmienią się wyniki.

Scenariusze: Zmiana dostępnych zasobów pracy

Jeśli liczba dostępnych godzin pracy się zmienia, możesz zaktualizować wartość w komórce odpowiadającej za liczbę godzin (np. komórka D4) i sprawdzić, jak to wpłynie na rozwiązanie. Solver automatycznie dostosuje liczbę sztuk do wyprodukowania, aby spełnić nowe warunki.

Podsumowanie

W tej lekcji nauczyłeś się, jak korzystać z Solver w Excelu w bardziej zaawansowany sposób, rozwiązując problemy wielokryterialne z różnymi ograniczeniami. Omówiliśmy, jak optymalizować zyski, minimalizować koszty oraz zarządzać zasobami, a także jak wykorzystać Solver do podejmowania decyzji w scenariuszach wymagających zaawansowanej analizy. Dzięki takim technikom możesz lepiej zrozumieć złożone problemy biznesowe i optymalizować swoje decyzje, niezależnie od tego, czy dotyczą one produkcji, finansów, czy logistyki.

W kolejnych lekcjach przyjrzymy się bardziej szczegółowo innym zaawansowanym narzędziom analizy, takim jak Analysis ToolPak oraz narzędziom do statystycznej analizy danych, które pomogą w zaawansowanych analizach i prognozach.

Gratulacje! Ukończyłeś lekcję 9.
Przejdź teraz do lekcji 10 >> Analysis ToolPak – Narzędzie do zaawansowanej analizy danych w Excelu

Spis Treści - darmowy kurs Excel

Wprowadzenie: Czym jest arkusz kalkulacyjny?
Lekcja 1: Wprowadzanie i formatowanie danych w Excelu
Lekcja 2: Korzystanie z formuł i funkcji w Excelu
Lekcja 3: Tabele i zarządzanie danymi w Excelu
Lekcja 4: Tworzenie wykresów w Excelu
Lekcja 5: Tabele przestawne w Excelu
Lekcja 6: Zaawansowane techniki filtrowania i sortowania danych w Excelu
Lekcja 7: What-If Analysis w Excelu
Lekcja 8: Solver i zaawansowana optymalizacja w Excelu
Lekcja 9: Zaawansowane scenariusze z użyciem Solver w Excel
Lekcja 10: Analysis ToolPak – Narzędzie do zaawansowanej analizy danych w Excelu
Lekcja 11: Power Query w Excelu – zaawansowane techniki pracy z danymi
Lekcja 12: Power Pivot – Analiza dużych zestawów danych w Excelu
Lekcja 13: Power BI – Tworzenie interaktywnych raportów i wizualizacji danych
Lekcja 14: Power Automate – Automatyzacja procesów biznesowych i Power BI
Lekcja 15: Power Apps – Tworzenie aplikacji na podstawie danych z Excela i Power BI
Lekcja 16: Zaawansowane funkcje DAX w Power BI i Power Pivot
Lekcja 17: Tworzenie zaawansowanych raportów w Power BI – Case Study
Lekcja 18: Zarządzanie dużymi bazami danych w Power BI
Lekcja 19: Analiza danych z wykorzystaniem Excela, Power BI i innych narzędzi