Definiowanie i rozwiązywanie problemów za pomocą 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 (Q1 Zysk:Q2 Zysk).

1. Komórki zmiennych

2. Komórka ograniczenia

3. Komórka celu

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

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

   Uwaga: Jeśli polecenie Solver lub grupa Analiza nie są dostępne, trzeba aktywować dodatek Solver. Zobacz: Jak aktywować dodatek Solver.

   

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

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

   • Aby wartość w komórce celu była jak największa, kliknij opcję Maks.

   • Aby wartość w komórce celu była jak najmniejsza, kliknij opcję Min.

   • Aby określić konkretną wartość w komórce celu, kliknij 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 Podlegających ograniczeniom wprowadź ograniczenia, które mają zostać zastosowane, wykonując następujące czynności:

   1. W oknie dialogowym Parametry dodatku Solver kliknij przycisk 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. Kliknij odpowiednią relację ( <=, =, >=, int, bin lub dif), która ma się znaleźć między komórką, do których odwołuje się odwołanie, a ograniczeniem. Jeśli klikniesz pozycję int, w polu Ograniczenie zostanie wyświetlona liczba całkowita. Po kliknięciu przedziału w polu Ograniczenie zostanie wyświetlony plik binarny. Jeśli klikniesz pozycję dif, w polu Ograniczenie zostanie wyświetlona wartość zróżnicowania.

   4. Jeśli w polu Ograniczenie wybierzesz relację <=, = 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ć następne, kliknij przycisk Dodaj.

      • Aby zaakceptować ograniczenie i powrócić do okna dialogowego Parametry dodatku Solver, kliknij przycisk OK.
        Uwaga    Relacjeint, bin i dif mogą być stosowane tylko w przypadku ograniczeń obejmujących komórki zmiennych decyzyjnych.

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

   6. W oknie dialogowym Parametry dodatku Solver kliknij ograniczenie, które chcesz zmienić lub usunąć.

   7. Kliknij przycisk Zmień i wprowadź wymagane zmiany lub kliknij przycisk Usuń.

5. Kliknij przycisk Rozwiąż i wykonaj jedną z następujących czynności:

   • Aby przechowywać wartości rozwiązania w arkuszu, w oknie dialogowym Wyniki dodatku Solver kliknij przycisk Zachowaj rozwiązanie dodatku Solver.

   • Aby przywrócić wartości sprzed kliknięcia przycisku Rozwiąż, kliknij pozycję Przywróć wartości pierwotne.

   • Proces wyszukiwania rozwiązania można przerwać, naciskając klawisz ESC. W programie Excel arkusz zostanie ponownie obliczony przy użyciu najnowszych wartości w komórkach zmiennych decyzyjnych.

   • Aby utworzyć raport oparty na rozwiązaniu użytkownika po znalezieniu rozwiązania przez dodatek Solver, należy kliknąć typ raportu w polu Raporty, a następnie kliknąć 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, należy kliknąć przycisk Zapisz scenariusz w oknie dialogowym Wyniki dodatku Solver, a następnie wpisać nazwę scenariusza w polu Nazwa scenariusza.

1. Po zdefiniowaniu problemu kliknij przycisk Opcje w oknie dialogowym Solver — Parametry.

2. W oknie dialogowym Opcje zaznacz pole wyboru Pokazuj wyniki iteracji, aby wyświetlić wartości poszczególnych rozwiązań próbnych, a następnie kliknij przycisk OK.

3. W oknie dialogowym Solver — Parametry kliknij 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 Solver — Wyniki, kliknij przycisk Zatrzymaj.

   • Aby kontynuować proces rozwiązywania i wyświetlić następne rozwiązanie próbne, kliknij przycisk Kontynuuj.

1. W oknie dialogowym Solver — parametry kliknij przycisk Opcje.

2. Wybierz lub wprowadź wartości dowolnych z opcji podanych na kartach Wszystkie metody, Nieliniowa GRG i Ewolucyjna w oknie dialogowym.

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

2. Wprowadź zakres komórek dla obszaru modelu i kliknij przycisk 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ć 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 w arkuszu, klikając pozycję Załaduj/Zapisz , aby zapisać problemy pojedynczo.

W oknie dialogowym Parametry dodatku Solver można wybrać dowolny spośród następujących trzech algorytmów (czyli metod rozwiązywania):

• Nieliniowa GRG (uogólniony zredukowany gradient)    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.

• Ewolucyjna    Do użycia w przypadku problemów o charakterze niegładkim.

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órek zmiennych

komórka ograniczona

komórka Celu

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

1. Kliknij pozycję Dane > 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:

   Cel	Czynność
   Zwiększanie wartości komórki celu	Kliknij pozycję Maksimum.
   Jak najmniejsza wartość komórki celu	Kliknij pozycję Minimum.
   Ustawianie określonej wartości w komórce celu	Kliknij 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 następujące czynności:

   1. W oknie dialogowym Parametry dodatku Solver kliknij przycisk 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:

   Cel	Czynność
   Zaakceptuj ograniczenie i dodaj kolejne	Kliknij przycisk Dodaj.
   Akceptowanie ograniczenia i powrót do okna dialogowego Parametry dodatku Solver	Kliknij przycisk OK.

6. Kliknij przycisk Rozwiąż, a następnie wykonaj jedną z następujących czynności:

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

1. Kliknij pozycję Dane > Solver.

   

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

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

4. W oknie dialogowym Solver — Parametry kliknij przycisk Rozwiąż.

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

   Cel	Czynność
   Zatrzymywanie procesu rozwiązywania i wyświetlanie okna dialogowego Wyniki dodatku Solver	Kliknij pozycję Zatrzymaj.
   Kontynuowanie procesu rozwiązywania i wyświetlanie następnego rozwiązania próbnego	Kliknij pozycję Continue (Kontynuuj).

1. Kliknij pozycję Dane > Solver.

   

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

   Cel	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. Kliknij przycisk OK.

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

1. Kliknij pozycję Dane > Solver.

   

2. Kliknij pozycję Załaduj/Zapisz, wprowadź zakres komórek dla obszaru modelu, a następnie kliknij pozycję 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ć 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 dotyczący arkusza, klikając pozycję Załaduj/Zapisz , aby zapisać problemy pojedynczo.

1. Kliknij pozycję Dane > Solver.

   

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