W tej lekcji omówimy Solver — jedno z najbardziej zaawansowanych narzędzi analitycznych w Excelu. Solver to narzędzie do optymalizacji, które umożliwia znajdowanie optymalnych rozwiązań dla problemów decyzyjnych poprzez modyfikowanie zmiennych, tak aby spełniały określone ograniczenia i maksymalizowały lub minimalizowały określony wynik. Solver jest niezwykle przydatny w finansach, zarządzaniu zasobami, planowaniu produkcji, logistyce, a także wszędzie tam, gdzie optymalizacja jest kluczowa.

Co to jest Solver?

Solver to narzędzie do rozwiązywania problemów optymalizacyjnych, w których chcesz znaleźć najlepsze możliwe rozwiązanie na podstawie zestawu zmiennych wejściowych, ograniczeń oraz celu, który chcesz osiągnąć. Solver może być używany do maksymalizowania zysków, minimalizowania kosztów, optymalizacji alokacji zasobów oraz rozwiązywania złożonych problemów matematycznych.

Solver działa, modyfikując wartości w określonych komórkach (zmienne decyzyjne), aby uzyskać pożądany wynik w komórce docelowej (funkcja celu), przy jednoczesnym spełnieniu określonych ograniczeń.

Kiedy warto używać Solver?

Solver jest idealnym narzędziem w sytuacjach, gdy musisz rozwiązać problemy optymalizacyjne, które mają wiele zmiennych oraz ograniczeń. Przykłady zastosowań Solver obejmują:

• Optymalizację budżetu: Znajdowanie najlepszego sposobu alokacji zasobów finansowych przy jednoczesnym minimalizowaniu kosztów.
• Planowanie produkcji: Maksymalizowanie wydajności produkcji, przy jednoczesnym zachowaniu ograniczeń dotyczących zasobów, takich jak materiały, praca i czas.
• Planowanie logistyki: Minimalizowanie kosztów transportu, przy uwzględnieniu ograniczeń czasowych i geograficznych.
• Analiza inwestycji: Optymalizowanie portfela inwestycyjnego w celu maksymalizacji zysków, z uwzględnieniem ryzyka.

Instalacja Solver

Solver nie jest domyślnie włączony w Excelu, więc zanim go użyjesz, musisz go aktywować.

1. Przejdź do zakładki File (Plik), a następnie kliknij Options (Opcje).
2. W oknie Excel Options (Opcje Excela) wybierz Add-Ins (Dodatki).
3. W dolnej części okna znajdziesz pole Manage (Zarządzaj). Wybierz Excel Add-ins (Dodatki Excela) i kliknij Go (Idź).
4. W oknie Add-Ins zaznacz opcję Solver Add-In i kliknij OK.

Po dodaniu Solver pojawi się w zakładce Data (Dane) w grupie Analysis (Analiza).

Kluczowe elementy Solver

Solver opiera się na trzech głównych komponentach:

1. Funkcja celu (Objective): To komórka, którą chcesz zmaksymalizować, zminimalizować lub ustawić na określoną wartość. Przykładowo, funkcją celu może być maksymalizacja zysku lub minimalizacja kosztów.
2. Zmienna decyzyjna (Variable Cells): Są to komórki, których wartości Solver będzie zmieniać w celu osiągnięcia funkcji celu. Na przykład, mogą to być liczby określające, ile sztuk produktu wyprodukować, aby zmaksymalizować zysk.
3. Ograniczenia (Constraints): Są to warunki, które muszą zostać spełnione w procesie optymalizacji. Na przykład, ograniczenia mogą dotyczyć zasobów, takich jak dostępna ilość materiałów, liczba pracowników lub czas produkcji.

Przykład użycia Solver – Maksymalizacja zysku

Rozważmy przykład firmy produkującej dwa produkty: Produkt A i Produkt B. Firma chce maksymalizować zyski, ale ma ograniczone zasoby pracy i materiałów. Dane są następujące:

• Produkt A przynosi 50 zł zysku za sztukę.
• Produkt B przynosi 70 zł zysku za sztukę.
• Produkcja każdej sztuki Produktu A wymaga 2 godzin pracy i 3 jednostek materiału.
• Produkcja każdej sztuki Produktu B wymaga 4 godzin pracy i 5 jednostek materiału.
• Firma ma dostępne 100 godzin pracy i 120 jednostek materiału.

Celem jest maksymalizacja zysku, przy jednoczesnym ograniczeniu zasobów pracy i materiałów.

Krok 1: Przygotowanie arkusza

1. Wprowadź dane do arkusza:

• W komórkach A1 i B1 wpisz „Produkt A” i „Produkt B”.
• W komórkach A2 i B2 wpisz zyski na sztukę (50 i 70).
• W komórkach A3 i B3 wpisz liczbę godzin pracy na produkt (2 i 4).
• W komórkach A4 i B4 wpisz jednostki materiału na produkt (3 i 5).
• W komórkach D3 i D4 wpisz całkowitą dostępną liczbę godzin pracy (100) i materiałów (120).

2. Wprowadź zmienne decyzyjne:

• W komórkach A5 i B5 wprowadź 0 – będą to wartości, które Solver będzie zmieniać (liczba sztuk Produktu A i Produktu B do wyprodukowania).

3. W komórce C2 wprowadź formułę, która obliczy całkowity zysk:

=A5*A2 + B5*B2

4. W komórce C3 wprowadź formułę, która obliczy całkowitą liczbę godzin pracy:

=A5*A3 + B5*B3

W komórce C4 wprowadź formułę, która obliczy całkowite zużycie materiałów:

=A5*A4 + B5*B4

Krok 2: Uruchomienie Solver

1. Przejdź do zakładki Data i kliknij Solver.
2. W oknie Solver skonfiguruj następujące parametry:
   • W polu Set Objective wybierz komórkę C2 (całkowity zysk).
   • Wybierz opcję Max (maksymalizacja), ponieważ chcemy zmaksymalizować zysk.
   • W polu By Changing Variable Cells wprowadź zakres A5(liczba sztuk Produktu A i Produktu B).
   • Dodaj ograniczenia:
     • Kliknij Add (Dodaj), wybierz komórkę C3 (całkowita liczba godzin pracy) i ustaw ograniczenie „<=” (mniejsze lub równe) na wartość D3 (100 godzin).
     • Kliknij Add (Dodaj), wybierz komórkę C4 (całkowite zużycie materiałów) i ustaw ograniczenie „<=” na wartość D4 (120 jednostek materiału).
3. Kliknij Solve (Rozwiąż).

Krok 3: Interpretacja wyników

Po kliknięciu Solve, Solver obliczy optymalną liczbę sztuk Produktu A i Produktu B, jaką firma powinna wyprodukować, aby zmaksymalizować zyski, przy jednoczesnym zachowaniu ograniczeń dotyczących pracy i materiałów. Solver wyświetli komunikat z wynikami i zapyta, czy chcesz zachować znalezione rozwiązanie.

W wyniku optymalizacji Solver może na przykład zalecić wyprodukowanie 20 sztuk Produktu A i 15 sztuk Produktu B, co przyniesie maksymalny zysk przy dostępnych zasobach.

Przykład użycia Solver – Minimalizacja kosztów

Teraz rozważmy sytuację, w której firma chce zminimalizować koszty produkcji dwóch produktów, jednocześnie spełniając określone zapotrzebowanie na te produkty.

1. Zdefiniuj dane: Produkcja Produktu A kosztuje 40 zł za sztukę, a Produkcja Produktu B kosztuje 60 zł za sztukę. Firma musi wyprodukować co najmniej 50 sztuk Produktu A i 30 sztuk Produktu B. Koszt produkcji należy zminimalizować.

2. Przygotuj arkusz: Wprowadź dane podobnie jak w poprzednim przykładzie, ale tym razem formuła w komórce C2 będzie obliczać całkowite koszty produkcji:

=A5*A2 + B5*B2

3. Uruchom Solver:

• Ustaw funkcję celu na minimalizację kosztów (komórka C2).
• Zmiennymi będą liczba sztuk Produktu A i Produktu B (komórki A5).
• Dodaj ograniczenia: liczba sztuk Produktu A musi być większa lub równa 50, a Produktu B większa lub równa 30.

4. Rozwiąż problem: Solver obliczy minimalny koszt produkcji, spełniając wymagane ograniczenia dotyczące liczby produktów.

Typy problemów, które można rozwiązać za pomocą Solver

• Problemy liniowe: Gdy funkcja celu i ograniczenia są liniowe (tj. mają formę równania liniowego), Solver używa metod optymalizacji liniowej.
• Problemy nieliniowe: Solver obsługuje również bardziej złożone problemy nieliniowe, w których funkcja celu lub ograniczenia nie są liniowe.
• Problemy z ograniczeniami całkowitymi: Możesz skonfigurować Solver tak, aby zmienne decyzyjne były liczbami całkowitymi, co jest przydatne w sytuacjach, gdy liczby muszą reprezentować jednostki niepodzielne (np. liczba wyprodukowanych przedmiotów).

Podsumowanie

W tej lekcji dowiedziałeś się, jak korzystać z Solver w Excelu do rozwiązywania problemów optymalizacyjnych, takich jak maksymalizacja zysków i minimalizacja kosztów. Solver to potężne narzędzie, które pozwala analizować skomplikowane problemy decyzyjne, uwzględniając ograniczenia i zmienne. Dzięki temu możesz podejmować lepsze decyzje finansowe, operacyjne i logistyczne.

W kolejnych lekcjach przyjrzymy się jeszcze bardziej zaawansowanym technikom analizy danych, takim jak Analiza scenariuszy z wykorzystaniem Solver oraz Analiza wielokryterialna, która pozwala rozwiązywać problemy, w których musisz zrównoważyć wiele różnych celów i ograniczeń.

Gratulacje! Ukończyłeś lekcję 8.
Przejdź teraz do lekcji 9 >> Zaawansowane scenariusze z użyciem Solver w Excel

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