Definiowanie i rozwiązywanie problemów za pomocą dodatku Solver

Przykład obliczeń z użyciem dodatku Solver

W poniższym przykładzie poziom reklamy w poszczególnych kwartałach wpływa na liczbę sprzedanych sztuk, pośrednio określając wartość przychodu ze sprzedaży, związanych z nim wydatków oraz zysku. Dodatek Solver może zmieniać kwartalne budżety reklamowe (komórki zmiennych decyzyjnych B5:C5), nie przekraczając ograniczenia całkowitego budżetu równego 20 000 zł (komórka F5), aż do osiągnięcia największego możliwego całkowitego zysku (komórka celu F7). Wartości w komórkach zmiennych są używane do obliczenia zysku dla każdego kwartału i są związane z formułą w komórce celu F7, =SUMA(Zysk Kw1:Zysk Kw2).

1. Komórki zmiennych

2. Komórka ograniczenia

3. Komórka celu

Po uruchomieniu dodatku Solver nowe wartości są następujące.

Definiowanie i rozwiązywanie problemu

1. Na karcie Dane w grupie Analiza wybierz pozycję Solver.
   

   Uwaga

   Jeśli polecenie Solver lub grupa Analiza nie jest dostępna, musisz uaktywnić dodatek Solver. Aby uzyskać więcej informacji, zobacz Jak uaktywnić dodatek Solver.

   

2. W polu Ustaw cel wprowadź odwołanie do komórki lub nazwę komórki celu. Komórka celu musi zawierać formułę.

3. Wykonaj jedną z następujących czynności.

   • Jeśli chcesz, aby wartość w komórce celu była jak największa, wybierz opcję Maksimum.
   • Aby wartość w komórce celu była jak najmniejsza, wybierz opcję Minimum.
   • Jeśli chcesz, aby komórka celu miała określoną wartość, wybierz opcję Wartość, a następnie wpisz wartość w polu.
   • W polu Przez zmienianie komórek zmiennych wprowadź nazwę lub odwołanie dla każdego zakresu komórek zmiennych decyzyjnych. Oddziel przecinkami nieprzylegające odwołania. Komórki zmiennych muszą być bezpośrednio lub pośrednio związane z komórką celu. Można określić do 200 komórek zmiennych.

4. W polu Podleganie ograniczeniom wprowadź ograniczenia, które mają zostać zastosowane, wykonując poniższe czynności.

   1. W oknie dialogowym Parametry dodatku Solver wybierz pozycję Dodaj.

   2. W polu Odwołanie do komórki wprowadź odwołanie do komórki lub nazwę zakresu komórek, których wartość ma zostać ograniczona.

   3. Wybierz relację ( <=, =, >=, int, bin lub dif ), która ma zachodzić między wskazaną komórką a ograniczeniem. W przypadku wybrania opcji int w polu Ograniczenie jest wyświetlana liczba całkowita. Jeśli wybierzesz bin, w polu Ograniczenie pojawi się wartość binarna. W przypadku wybrania opcji dif, w polu Ograniczenie pojawi się wartość alldifferent.

   4. Jeśli dla relacji w polu Ograniczenie wybierzesz <wartość =, = lub >=, wpisz liczbę, odwołanie do komórki lub nazwę komórki albo formułę.

   5. Wykonaj jedną z następujących czynności.

      • Aby zaakceptować ograniczenie i dodać kolejne, wybierz pozycję Dodaj.

      • Aby zaakceptować ograniczenie i powrócić do okna dialogowego Parametry dodatku Solver, wybierz przycisk OK.

        Uwaga

        Relacje int, bin i dif można stosować tylko w ograniczeniach w komórkach zmiennych decyzyjnych.

   6. Ograniczenie można zmienić lub usunąć, wykonując następujące czynności.

      • W oknie dialogowym Parametry dodatku Solver wybierz ograniczenie, które chcesz zmienić lub usunąć.
      • Wybierz pozycję Zmień , a następnie wprowadź zmiany lub wybierz pozycję Usuń.

5. Wybierz pozycję Rozwiąż i wykonaj jedną z następujących akcji.

   • Aby zachować wartości rozwiązania w arkuszu, w oknie dialogowym Wyniki dodatku Solver wybierz pozycję Zachowaj rozwiązanie dodatku Solver.
   • Aby przywrócić wartości pierwotne sprzed wybrania opcji Rozwiąż, wybierz pozycję Przywróć wartości oryginalne.
   • Proces wyszukiwania rozwiązania można przerwać, naciskając klawisz ESC. Program Excel ponownie obliczy arkusz, używając najnowszych wartości w komórkach zmiennych decyzyjnych.
   • Aby utworzyć raport oparty na rozwiązaniu użytkownika po znalezieniu rozwiązania przez dodatek Solver, wybierz typ raportu w polu Raporty, a następnie wybierz przycisk OK. Raport zostanie utworzony w nowym arkuszu w skoroszycie. Jeśli dodatek Solver nie znajdzie rozwiązania, dostępne będą tylko niektóre raporty lub żadne raporty nie będą niedostępne.
   • Aby zapisać wartości komórek zmiennych decyzyjnych jako scenariusz do późniejszego wyświetlania, wybierz opcję Zapisz scenariusz w oknie dialogowym Wyniki dodatku Solver , a następnie wpisz nazwę scenariusza w polu Nazwa scenariusza .

Wyświetlanie kolejnych rozwiązań próbnych dodatku Solver

1. Po zdefiniowaniu problemu wybierz pozycję Opcje w oknie dialogowym Parametry dodatku Solver .

2. W oknie dialogowym Opcje zaznacz pole wyboru Pokaż wyniki iteracji, aby wyświetlić wartości każdego rozwiązania próbnego, a następnie wybierz przycisk OK.

3. W oknie dialogowym Parametry dodatku Solver wybierz przycisk Rozwiąż.

4. W oknie dialogowym Pokazywanie rozwiązania próbnego wykonaj jedną z następujących czynności:

   • Aby zatrzymać proces rozwiązywania i wyświetlić okno dialogowe Wyniki dodatku Solver , wybierz pozycję Zatrzymaj.
   • Aby kontynuować proces rozwiązywania i wyświetlić następne rozwiązanie próbne, wybierz pozycję Kontynuuj.

Zmienianie sposobu znajdowania rozwiązań przez dodatek Solver

1. W oknie dialogowym Parametry dodatku Solver wybierz pozycję Opcje.
2. Wybierz lub wprowadź wartości dowolnych z opcji podanych na kartach Wszystkie metody, Nieliniowa GRG i Ewolucyjna w oknie dialogowym.

Zapisywanie i ładowanie modelu problemu

1. W oknie dialogowym Parametry dodatku Solver wybierz przycisk Załaduj/Zapisz.

2. Wprowadź zakres komórek dla obszaru modelu i wybierz opcję Zapisz lub Załaduj.
   Zapisując model, wprowadź odwołanie do pierwszej komórki należącej do pionowego zakresu pustych komórek, w których ma zostać umieszczony model problemu. Ładując model, wprowadź odniesienie do całego zakresu komórek zawierających model problemu.

   Porada

   Ostatnie zaznaczenia w oknie dialogowym Parametry dodatku Solver można zapisać wraz z arkuszem, zapisując skoroszyt. Każdy arkusz w skoroszycie może mieć własne zaznaczenia dodatku Solver. Wszystkie są zapisywane. Możesz również zdefiniować więcej niż jeden problem w arkuszu , wybierając pozycję Załaduj/Zapisz w celu zapisania problemów osobno.

Metody rozwiązywania używane przez dodatek Solver

W oknie dialogowym Parametry dodatku Solver można wybrać dowolny z następujących trzech algorytmów (metod rozwiązywania).

• Uogólniony zredukowany gradient (GRG) nieliniowy: Do użycia w przypadku problemów o charakterze gładkim i nieliniowym.
• LP simpleks: Do użycia w przypadku problemów o charakterze liniowym.
• Ewolucyjne: Do użycia w przypadku problemów o charakterze niegładkim.

Przykład obliczeń z użyciem dodatku Solver

W poniższym przykładzie poziom reklamy w poszczególnych kwartałach wpływa na liczbę sprzedanych sztuk, pośrednio określając wartość przychodu ze sprzedaży, związanych z nim wydatków oraz zysku. Dodatek Solver może zmieniać kwartalne budżety reklamowe (komórki zmiennych decyzyjnych B5:C5), nie przekraczając ograniczenia całkowitego budżetu równego 20 000 zł (komórka D5), aż do osiągnięcia największego możliwego całkowitego zysku (komórka celu D7). Wartości w komórkach zmiennych są używane do obliczenia zysku dla każdego kwartału, a więc są związane z formułą w komórce celu D7, =SUMA(Zysk Kw1:Zysk Kw2).

Komórki zmiennych

Komórka ograniczona

Komórka celu

Po uruchomieniu dodatku Solver nowe wartości są następujące.

Definiowanie i rozwiązywanie problemu

1. Wybierz dodatek Data>Solver.

   

2. W polu Ustaw cel wprowadź odwołanie do komórki lub nazwę komórki celu.

   Uwaga

   Komórka celu musi zawierać formułę.

3. Wykonaj jedną z następujących czynności.

   Aby	Czynność
   Ustaw wartość komórki celu jako jak największą	Wybierz pozycję Max.
   Ustaw wartość komórki celu jako możliwie najmniejszą	Wybierz pozycję Min.
   Ustawianie określonej wartości w komórce celu	Wybierz pozycję Wartość , a następnie wpisz wartość w polu.

4. W polu Przez zmienianie komórek zmiennych wprowadź nazwę lub odwołanie dla każdego zakresu komórek zmiennych decyzyjnych. Oddziel przecinkami nieprzylegające odwołania.
   Komórki zmiennych muszą być bezpośrednio lub pośrednio związane z komórką celu. Można określić do 200 komórek zmiennych.

5. W polu Podlegający ograniczeniom dodaj ograniczenia, które chcesz zastosować.
   Aby dodać ograniczenie, wykonaj następujące kroki.

   1. W oknie dialogowym Parametry dodatku Solver wybierz pozycję Dodaj.

   2. W polu Odwołanie do komórki wprowadź odwołanie do komórki lub nazwę zakresu komórek, których wartość ma zostać ograniczona.

   3. W menu podręcznym <= relacja wybierz odpowiednią relację między komórką, której dotyczy odwołanie, a ograniczeniem. W przypadku wybrania wartości <=, = lub >= w polu Ograniczenie wpisz liczbę, odwołanie do komórki lub nazwę komórki albo formułę.

      Uwaga

      Relacje int, bin i dif można stosować tylko w ograniczeniach w komórkach zmiennych decyzyjnych.

   4. Wykonaj jedną z następujących czynności.

   Aby	Czynność
   Zaakceptuj ograniczenie i dodaj kolejne	Wybierz pozycję Dodaj.
   Zaakceptuj ograniczenie i wróć do okna dialogowego Parametry dodatku Solver	Wybierz przycisk OK.

6. Wybierz pozycję Rozwiąż, a następnie wykonaj jedną z następujących akcji.

   Aby	Czynność
   Zachowaj wartości rozwiązania w arkuszu	W oknie dialogowym Wyniki dodatku Solver wybierz pozycję Zachowaj rozwiązanie dodatku Solver.
   Przywracanie oryginalnych danych	Wybierz pozycję Przywróć wartości oryginalne.

Wyświetlanie kolejnych rozwiązań próbnych dodatku Solver

1. Wybierz dodatek Data>Solver.

   

2. Po zdefiniowaniu problemu wybierz pozycję Opcje w oknie dialogowym Parametry dodatku Solver.

3. Zaznacz pole wyboru Pokaż wyniki iteracji, aby wyświetlić wartości każdego rozwiązania próbnego, a następnie wybierz przycisk OK.

4. W oknie dialogowym Parametry dodatku Solver wybierz przycisk Rozwiąż.

5. W oknie dialogowym Pokazywanie rozwiązania próbnego wykonaj jedną z następujących czynności:

   Aby	Czynność
   Zatrzymywanie procesu rozwiązywania i wyświetlanie okna dialogowego Wyniki dodatku Solver	Wybierz pozycję Zatrzymaj.
   Kontynuowanie procesu rozwiązywania i wyświetlenie następnego rozwiązania próbnego	Wybierz pozycję Kontynuuj.

Zmienianie sposobu znajdowania rozwiązań przez dodatek Solver

1. Wybierz dodatek Data>Solver.

   

2. Wybierz pozycję Opcje, a następnie w oknie dialogowym Opcje lub Opcje dodatku Solver wybierz jedną lub kilka z następujących opcji:

   Aby	Czynność
   Ustawianie czasu rozwiązania i iteracji	Na karcie Wszystkie metody w obszarze Granice rozwiązywania w polu Maksymalny czas (w sekundach) wpisz liczbę sekund, po której chcesz przeznaczyć czas rozwiązania. Następnie w polu Iteracje wpisz maksymalną liczbę iteracji, na jaką chcesz zezwolić. Uwaga: Jeśli proces rozwiązywania osiągnie maksymalny czas lub maksymalną liczbę iteracji, zanim dodatek Solver znajdzie rozwiązanie, zostanie wyświetlone okno dialogowe Pokaż rozwiązanie próbne .
   Ustawianie stopnia precyzji	Na karcie Wszystkie metody w polu Dokładność wiązania wpisz odpowiedni stopień dokładności. Im mniejsza liczba, tym większa precyzja.
   Ustawianie stopnia zbieżności	Na karcie Nieliniowa lub Ewolucyjna GRG w polu Zbieżność wpisz wartość zmiany względnej, która ma być dozwolona w ostatnich pięciu iteracjach, zanim dodatek Solver zatrzyma się w danym rozwiązaniu. Im mniejsza liczba, tym mniejsza dozwolona zmiana względna.

3. Wybierz przycisk OK.

4. W oknie dialogowym Parametry dodatku Solver wybierz przycisk Rozwiąż lub Zamknij.

Zapisywanie i ładowanie modelu problemu

1. Wybierz dodatek Data>Solver.

   

2. Wybierz opcję Załaduj/Zapisz, wprowadź zakres komórek dla obszaru modelu, a następnie wybierz opcję Zapisz lub Załaduj.
   Zapisując model, wprowadź odwołanie do pierwszej komórki należącej do pionowego zakresu pustych komórek, w których ma zostać umieszczony model problemu. Ładując model, wprowadź odniesienie do całego zakresu komórek zawierających model problemu.

   Porada

   Ostatnie zaznaczenia w oknie dialogowym Parametry dodatku Solver można zapisać wraz z arkuszem, zapisując skoroszyt. Każdy arkusz w skoroszycie może mieć własne zaznaczenia dodatku Solver. Wszystkie są zapisywane. Możesz również zdefiniować więcej niż jeden problem dla arkusza, wybierając pozycję Załaduj/Zapisz, aby zapisać problemy osobno.

Metody rozwiązywania używane przez dodatek Solver

1. Wybierz dodatek Data>Solver.

   

2. W menu podręcznym Wybierz metodę rozwiązywania wybierz jedną z następujących opcji:

Ponieważ dodatki nie są obsługiwane w programie Excel dla sieci Web, nie można używać dodatku Solver do uruchamiania analizy warunkowej danych w celu znalezienia rozwiązań optymalnych.

Jeśli masz aplikację klasyczną Excel, możesz otworzyć skoroszyt za pomocą przycisku Otwórz w programie Excel w celu użycia dodatku Solver.