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

Przykład obliczeń z użyciem dodatku Solver

W poniższym przykładzie poziom reklam w każdym kwartale wpływa na liczbę sprzedanych jednostek, pośrednio ustalając wielkość przychodów ze sprzedaży, skojarzone wydatki i zysk. Dodatek Solver może zmienić budżety kwartalne na reklamy (komórki zmiennych decyzyjnych B5:C5), do całkowitego ograniczenia budżetowego w wysokości 20 000 USD (komórka F5), dopóki całkowity zysk (komórka celu F7) nie osiągnie maksymalnej możliwej kwoty. Wartości w komórkach zmiennych są używane do obliczania zysku dla każdego kwartału, więc są powiązane z komórką celu formuły F7, =SUMA(Zysk W1 kw.:Q2 Zysk).

1. Komórki zmiennych

2. Komórka ograniczona

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 są dostępne, musisz aktywować dodatek Solver. Aby uzyskać więcej informacji, zobacz Jak aktywować 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ść komórki celu była jak najszersza, wybierz pozycję Maksimum.
   • Jeśli chcesz, aby wartość komórki celu była jak najmniejsza, wybierz pozycję Min.
   • Jeśli komórka celu ma być określoną wartością, wybierz pozycję 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 nieprzylegające odwołania przecinkami. 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 Podlegaj ograniczeniom wprowadź ograniczenia, które chcesz zastosować, 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. Zaznacz odpowiednią relację ( <=, =, >=, int, bin lub dif ), która ma się między komórką, do których odwołuje się odwołanie, a ograniczeniem. Jeśli wybierzesz pozycję int, w polu Ograniczenie zostanie wyświetlona liczba całkowita. W przypadku wybrania przedziału w polu Ograniczenie zostanie wyświetlony plik binarny. Jeśli wybierzesz opcję dif, w polu Ograniczenie zostanie wyświetlony element zróżnicujący.

   4. Jeśli wybierzesz <=, =lub >= dla relacji w polu Ograniczenie , wpisz liczbę, odwołanie do komórki lub nazwę 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 typu int, bin i dif można stosować tylko w przypadku ograniczeń w komórkach zmiennych decyzyjnych.

   6. Możesz zmienić lub usunąć istniejące ograniczenie, 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 czynności.

   • Aby zachować wartości rozwiązania w arkuszu, w oknie dialogowym Wyniki dodatku Solver wybierz pozycję Zachowaj rozwiązanie dodatku Solver.
   • Aby przywrócić oryginalne wartości przed wybraniem pozycji Rozwiąż, wybierz pozycję Przywróć oryginalne wartości.
   • Możesz przerwać proces rozwiązania, naciskając klawisz Esc. Program Excel ponownie oblicza arkusz przy użyciu ostatnich znalezionych wartości dla komórek zmiennych decyzyjnych.
   • Aby utworzyć raport oparty na rozwiązaniu, gdy dodatek Solver znajdzie rozwiązanie, 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, który można wyświetlić później, wybierz pozycję 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 pozycję 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 wersji próbnej, 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 pozycję Załaduj/Zapisz.

2. Wprowadź zakres komórek dla obszaru modelu i wybierz pozycję Zapisz lub Załaduj.
   Podczas zapisywania modelu wprowadź odwołanie do pierwszej komórki pionowego zakresu pustych komórek, w której chcesz umieścić 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ć w arkuszu, zapisując skoroszyt. Każdy arkusz w skoroszycie może mieć własne zaznaczenia dodatku Solver i wszystkie z nich są zapisywane. Możesz również zdefiniować więcej niż jeden problem dla arkusza, wybierając pozycję Załaduj/Zapisz , aby zapisać problemy pojedynczo.

Metody rozwiązywania używane przez dodatek Solver

W oknie dialogowym Parametry dodatku Solver możesz wybrać dowolny z trzech poniższych algorytmów lub metody rozwiązywania.

• Uogólniony zredukowany gradient (GRG) nieliniowy: Do użycia w przypadku problemów, które są gładkie nieliniowe.
• LP Simplex: Służy do rozwiązywania problemów liniowych.
• Ewolucyjnej: Do użycia w przypadku problemów, które nie są gładkie.

Przykład obliczeń z użyciem dodatku Solver

W poniższym przykładzie poziom reklam w każdym kwartale wpływa na liczbę sprzedanych jednostek, pośrednio ustalając wielkość przychodów ze sprzedaży, skojarzone wydatki i zysk. Dodatek Solver może zmienić budżety kwartalne na reklamy (komórki zmiennych decyzyjnych B5:C5), do całkowitego ograniczenia budżetowego w wysokości 20 000 USD (komórka D5), aż całkowity zysk (komórka celu D7) osiągnie maksymalną możliwą kwotę. Wartości w komórkach zmiennych są używane do obliczania zysku dla każdego kwartału, więc są powiązane z komórką celu formuły D7, =SUMA(Zysk Q1:Q2 Zysk).

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 pozycjęSolverdanych>.

   

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ść
   Zwiększanie wartości komórki celu	Wybierz pozycję Maksimum.
   Jak najmniejsza wartość komórki celu	Wybierz pozycję Minimum.
   Ustawianie określonej wartości w komórce celu	Wybierz pozycję Wartość of , 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 nieprzylegające odwołania przecinkami.
   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 ograniczeniom dodaj ograniczenia, które chcesz zastosować.
   Aby dodać ograniczenie, wykonaj 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. W menu podręcznym <= relacja wybierz odpowiednią relację między komórką, do których odwołuje się odwołanie, a ograniczeniem. Jeśli wybierzesz pozycję< =, =lub >=, w polu Ograniczenie wpisz liczbę, odwołanie do komórki lub nazwę albo formułę.

      Uwaga

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

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

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

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

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

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

1. Wybierz pozycjęSolverdanych>.

   

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

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 pozycję 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świetlanie następnego rozwiązania próbnego	Wybierz pozycję Kontynuuj.

Zmienianie sposobu znajdowania rozwiązań przez dodatek Solver

1. Wybierz pozycjęSolverdanych>.

   

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

   Aby	Czynność
   Ustawianie czasu i iteracji rozwiązania	Na karcie Wszystkie metody w obszarze Limity rozwiązywania w polu Maks. czasu (sekund) wpisz liczbę sekund, na jaką chcesz zezwolić na czas rozwiązania. Następnie w polu Iteracje wpisz maksymalną liczbę iteracji, na które chcesz zezwolić. Uwaga: Jeśli proces rozwiązywania osiągnie maksymalną liczbę iteracji, zanim dodatek Solver znajdzie rozwiązanie, dodatek Solver wyświetli okno dialogowe Pokazywanie rozwiązania próbnego .
   Ustawianie stopnia dokładności	Na karcie Wszystkie metody w polu Dokładność ograniczenia wpisz odpowiedni stopień dokładności. Im mniejsza jest liczba, tym większa dokładność.
   Ustawianie stopnia konwergencji	Na karcie GrG Nonlinear or Evolutionary (Nieliniowe lub Ewolucyjne ) w polu Konwergencja wpisz wartość względnej zmiany, na którą chcesz zezwolić w ostatnich pięciu iteracjach, zanim dodatek Solver zatrzyma się na podstawie rozwiązania. Im mniejsza jest liczba, tym mniejsza zmiana względna jest dozwolona.

3. Wybierz przycisk OK.

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

Zapisywanie i ładowanie modelu problemu

1. Wybierz pozycjęSolverdanych>.

   

2. Wybierz pozycję Załaduj/Zapisz, wprowadź zakres komórek dla obszaru modelu, a następnie wybierz pozycję Zapisz lub Załaduj.
   Podczas zapisywania modelu wprowadź odwołanie do pierwszej komórki pionowego zakresu pustych komórek, w której chcesz umieścić 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ć w arkuszu, zapisując skoroszyt. Każdy arkusz w skoroszycie może mieć własne zaznaczenia dodatku Solver i wszystkie z nich są zapisywane. Możesz również zdefiniować więcej niż jeden problem dla arkusza, wybierając pozycję Załaduj/Zapisz , aby zapisać problemy pojedynczo.

Metody rozwiązywania używane przez dodatek Solver

1. Wybierz pozycjęSolverdanych>.

   

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

Ponieważ programy dodatków nie są obsługiwane w Excel dla sieci web, nie można używać dodatku Solver do przeprowadzania analizy warunkowej danych w celu znalezienia optymalnych rozwiązań.

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