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

Solver jest dodatkiem do programu Microsoft Excel umożliwiającym przeprowadzanie analiza warunkowa. Za pomocą dodatku Solver można znaleźć optymalną (maksymalną lub minimalną) wartość formuła w jednej komórce — zwanej komórką celu — podlegającej ograniczeniom, czyli limitom, dotyczącym wartości innych komórek z formułą znajdujących się w arkuszu. Dodatek Solver pracuje z grupą komórek, zwanych zmiennymi decyzyjnymi lub po prostu komórkami zmiennych, które służą do obliczania formuł w komórkach celu i komórkach ograniczeń. Dodatek Solver dostosowuje wartości w komórkach zmiennych decyzyjnych tak, aby spełnić limity obejmujące komórki ograniczeń i uzyskać pożądany wynik w komórce celu.

Mówiąc w uproszczeniu, za pomocą dodatku Solver można ustalić maksymalną lub minimalną wartość określonej komórki przez zmianę innych komórek. Można na przykład zmienić przewidywany budżet reklamowy i zobaczyć wpływ tej zmiany na prognozowaną kwotę zysku.

Uwaga: W dodatku Solver przeznaczonym dla programu Excel w wersji wcześniejszej niż wersja 2007 komórka celu była nazywana komórką docelową, a komórki zmiennych decyzyjnych — komórkami zmienianymi lub komórkami dostosowywanymi. W dodatku Solver dla programu Excel 2010 wprowadzono wiele ulepszeń, dlatego sposób korzystania z tego dodatku w programie Excel 2007 nieco się różni.

W poniższym przykładzie poziom reklam w każdym kwartale wpływa na liczbę sprzedanych jednostek, określając pośrednio kwotę przychodów ze sprzedaży, skojarzone wydatki i zysk. Solver może zmieniać budżety kwartalne na reklamy (komórki zmiennych decyzyjnych B5:C5) na całkowite ograniczenie budżetowe w wysokości 20 000 zł (komórka F5), aż do osiągnięcia maksymalnej możliwej kwoty w zyskach całkowitych (komórka F7 celu). Wartości w komórkach zmiennych służą do obliczania zysków dla poszczególnych kwartałów, tak aby były powiązane z komórką celu formuły F7, =SUMA (Zysk:KW1 Zysk:2 Zysk).

Przed obliczeniem przez dodatek Solver

1. Komórki zmiennych

2. Komórka ograniczenia

3. Komórka celu

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

Po obliczeniu przez dodatek Solver

  1. Na karcie Dane w grupie Analiza kliknij pozycję Solver.
    Obraz Wstążki programu Excel

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

    Obraz przedstawiający okno dialogowe dodatku Solver dla programu Excel w wersjach nowszych niż 2010
  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 relację ( <=, =, >=, int, binlub dif ), która ma zostać pomiędzy komórką, do której odwołuje się odwołanie, a ograniczeniem. Jeśli klikniesz opcję int, w polu Ograniczenie zostanie wyświetlona liczba całkowita. Jeśli klikniesz opcję binarna, w polu Ograniczenie zostanie wyświetlona opcja binarna. Jeśli klikniesz ikonę dif, wszystkieróżne pojawi się w polu Ograniczenie.

    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 Solver — Parametry można zapisać w arkuszu, zapisując skoroszyt. Każdy arkusz w skoroszycie może mieć własne zaznaczenia do solver i wszystkie są zapisywane. Można również zdefiniować więcej niż jeden problem dla arkusza, klikając pozycję Załaduj/Zapisz, aby zapisać poszczególne problemy.

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.

Ważne: Najpierw włącz dodatek Solver. Aby uzyskać więcej informacji, zobacz Ładowanie dodatku Solver.

W poniższym przykładzie poziom reklam w każdym kwartale wpływa na liczbę sprzedanych jednostek, określając pośrednio kwotę przychodów ze sprzedaży, skojarzone wydatki i zysk. Solver może zmieniać budżety kwartalne na reklamę (komórki zmiennych decyzyjnych B5:C5) na całkowite ograniczenie budżetowe w wysokości 20 000 zł (komórka D5), aż do osiągnięcia maksymalnej możliwej kwoty w zyskach całkowitych (komórka D7 celu). Wartości w komórkach zmiennych służą do obliczania zysków dla poszczególnych kwartałów, tak aby były powiązane z komórką celu formuły D7, =SUMA(Zysk kw1:Zysk KW2).

Przykład obliczeń przy użyciu dodatku Solver

Objaśnienie 1 komórek zmiennych

Objaśnienie 2 komórka Ograniczona

Objaśnienie 3 celu

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

Przykład obliczeń przy użyciu dodatku Solver z nowymi wartościami

  1. W Excel 2016 dla komputerów Mac: Kliknij pozycję Dane > Solver.

    Solver

    W Excel dla komputerów Mac 2011: Kliknij kartę Dane, a następnie w obszarze Analizakliknij przycisk Solver.

    Karta Dane, grupa Analiza, Dodatek Solver

  2. W polachUstaw 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ści

    Jak najsymańsza wartość w komórce celu

    Kliknij pozycję Maksimum.

    Jak najmniejsza wartość w komórce celu

    Kliknij pozycję Minimum.

    Ustawianie określonej wartości w komórce celu

    Kliknij pozycję Wartośćz , 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 nieprzylegją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 ograniczeniom dodaj wszelkie 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 relację między komórką, do której odwołuje się odwołanie, a ograniczeniem. Jeśli wybierzesz <=, =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ści

    Zaakceptowanie ograniczenia i dodanie kolejnego

    Kliknij Dodaj.

    Zaakceptowanie ograniczenia i powrót do okna dialogowego Parametry do solver

    Kliknij przycisk OK.

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

    Cel

    Czynności

    Przechowuj wartości rozwiązania w arkuszu

    Kliknij przycisk Zachowaj rozwiązanie do solver w oknie dialogowym Solver — wyniki.

    Przywracanie oryginalnych danych

    Kliknij pozycję Przywróć oryginalne wartości.

Uwagi: 

  1. Aby przerwać proces rozwiązania, naciśnij klawisz ESC. Excel ponownie obliczy arkusz za pomocą ostatnich wartości, które można znaleźć dla dostosowywanych komórek.

  2. 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 solver nie znajdzie rozwiązania, opcja utworzenia raportu jest niedostępna.

  3. Aby zapisać dostosowania wartości komórek jako scenariusz do późniejszego wyświetlania, kliknij przycisk Zapisz scenariusz w oknie dialogowym Wyniki do solver, a następnie wpisz nazwę scenariusza w polu Nazwa scenariusza.

  1. W Excel 2016 dla komputerów Mac: Kliknij pozycję Dane > Solver.

    Solver

    W Excel dla komputerów Mac 2011: Kliknij kartę Dane, a następnie w obszarze Analizakliknij przycisk Solver.

    Karta Dane, grupa Analiza, Dodatek Solver

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

  3. Zaznacz pole wyboru Pokaż wyniki iteracji, aby wyświetlić wartości poszczególnych rozwiązań próbnych, 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ści

    Zatrzymywanie procesu rozwiązywania i wyświetlanie okna dialogowego Solver — Wyniki

    Kliknij przycisk Zatrzymaj.

    Kontynuowanie procesu tworzenia rozwiązania i wyświetlanie następnego rozwiązania próbnego

    Kliknij pozycję Continue (Kontynuuj).

  1. W Excel 2016 dla komputerów Mac: Kliknij pozycję Dane > Solver.

    Solver

    W Excel dla komputerów Mac 2011: Kliknij kartę Dane, a następnie w obszarze Analizakliknij przycisk Solver.

    Karta Dane, grupa Analiza, Dodatek Solver

  2. Kliknij przyciskOpcje, a następnie w oknie dialogowym Opcje lub Opcje dopłacania wybierz jedną lub więcej z następujących opcji:

    Cel

    Czynności

    Ustawianie czasu i iteracji rozwiązania

    Na karcie Wszystkie metody w obszarze Limity rozwiązywania w polu Maksymalny czas (sekundy) wpisz liczbę sekund, na którą chcesz zezwolić na czas rozwiązania. Następnie w polu Iteracje wpisz maksymalną liczbę iteracji, na którą chcesz zezwolić.

    Uwaga: Jeśli przed znalezieniem rozwiązania przez proces rozwiązywania zostanie osiągnieny maksymalny czas lub liczbę iteracji, do czasu, w którym rozwiązanie zostanie znalezione, w doliczach solver zostanie wyświetlone okno dialogowe Pokazywanie rozwiązania próbnego.

    Ustawianie stopnia dokładności

    Na karcie Wszystkie metody w polu Dokładność ograniczenia wpisz stopień dokładności. Im mniejsza jest liczba, tym wyższa dokładność.

    Ustawianie stopnia schłodu

    Na karcie Nieliniowa GRG lub Ewolucyjna w polu Po użyciu wpisz wartość zmiany względnej, jaka ma być wzmiętna w ostatnich pięciu iteracjach, zanim solver przestanie działać z rozwiązaniem. Im mniejsza jest liczba, tym mniejsza zmiana względna jest dozwolona.

  3. Kliknij przycisk OK.

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

  1. W Excel 2016 dla komputerów Mac: Kliknij pozycję Dane > Solver.

    Solver

    W Excel dla komputerów Mac 2011: Kliknij kartę Dane, a następnie w obszarze Analizakliknij przycisk Solver.

    Karta Dane, grupa Analiza, Dodatek Solver

  2. Kliknij przycisk Załaduj/Zapisz,wprowadź zakres komórek dla obszaru modelu, a następnie 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 Solver — Parametry można zapisać w arkuszu, zapisując skoroszyt. Każdy arkusz w skoroszycie może mieć własne zaznaczenia do solver i wszystkie są zapisywane. Możesz również zdefiniować więcej niż jeden problem dla arkusza, klikając pozycję Załaduj/Zapisz, aby zapisać poszczególne problemy.

  1. W Excel 2016 dla komputerów Mac: Kliknij pozycję Dane > Solver.

    Solver

    W Excel dla komputerów Mac 2011: Kliknij kartę Dane, a następnie w obszarze Analizakliknij przycisk Solver.

    Karta Dane, grupa Analiza, Dodatek Solver

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

Metoda rozwiązywania

Opis

NIEliniowa GRG (uogólniony zredukowany gradient)

Jest to opcja domyślna w przypadku modeli używających większości funkcji Excel innych niż JEŻELI, WYBIERZ, ODNOŚNIK i innych funkcji "kroku".

Simplex LP

Tej metody należy używać do programowania liniowego. W modelu należy używać funkcji SUMA, SUMA.IPRODUKTÓW, + - i * w formułach zależnych od komórek zmiennych.

Ewolucyjna

Ta metoda jest najlepsza, gdy w modelu są używane algorytmy JEŻELI, WYBIERZ lub ODNOŚNIK z argumentami zależnymi od komórek zmiennych.

Uwaga: Część kodu programu Solver jest chronione prawem autorskim w latach 1990–2010 roku przez firmę Frontline Systems, Inc. Część jest chronione prawem autorskim w 1989 roku przez firmę Optimal Methods, Inc.

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

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

Więcej pomocy dotyczącej korzystania z dodatku Solver

Aby uzyskać bardziej szczegółową pomoc dotyczącą dodatku Solver, skontaktuj się z firmą

Frontline Systems, Inc.
Po.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
Witryna internetowa: http://www.solver.com
Poczta e-mail: info@solver.com
Pomocy do dorównowej w www.solver.com.

Część kodu źródłowego dodatku Solver została zastrzeżona w latach 1990–2009 roku przez firmę Frontline Systems, Inc. Część została zastrzeżona w 1989 roku przez firmę Optimal Methods, Inc.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Zobacz też

Korzystanie z do narzędzia Solver do budżetowania kapitału

Określanie optymalnej kombinacji produktów za pomocą do rozwiązywania

Wprowadzenie do analizy symulacji

Omówienie formuł w programie Excel

Jak unikać niepoprawnych formuł

Wykrywanie błędów w formułach

Skróty klawiaturowe w programie Excel

Funkcje programu Excel (lista alfabetyczna)

Funkcje programu Excel (według kategorii)

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

×