Дефиниране и решаване на задача с помощта на Solver

Solver е програма добавка на Microsoft Excel, която можете да използвате за условен анализ. Можете да използвате Solver, за да да намирате оптимална (максимална или минимална) стойност за Формула в една клетка – наречена целева клетка – подчинена на ограничаващи условия или граници за стойностите в други клетки с формули в даден работен лист. Solver работи с групи от клетки, наречени променливи на решението или просто променливи клетки, които се използват в изчисляването на формулите в целевата клетка и клетките с ограничения. Solver настройва стойностите в променливите на решението, за да удовлетворяват границите в клетките с ограничения, и поражда желания от вас резултат за целевата клетка.

Казано по-просто, можете да използвате Solver, за да определите максималната или минималната стойност на една клетка, като промените други клетки. Можете например да промените размера на вашия прогнозен бюджет за рекламиране и да видите ефекта върху размера на вашата прогнозна печалба.

Забележка: Версиите на Solver, по-стари от Excel 2007, споменават целевата клетка като "клетка цел" и клетките за променливи на решението като "променящи се клетки" или "настройваеми клетки". Много подобрения са направени в добавката Solver за Excel 2010, така че ако използвате Excel 2007, вашата среда за работа ще бъде малко по-различна.

В следващия пример средствата за рекламиране през всяко тримесечие засягат броя на продадените изделия, определяйки косвено размера на приходите от продажби, свързаните разходи и печалбата. Solver може да променя тримесечните бюджети за рекламиране (клетки за променливи на решението B5:C5) до обща сума на бюджетните ограничения от $20 000 (клетка F5), докато общата печалба (целева клетка F7) достигне максималния възможен размер. Стойностите в променливите клетки се използват, за да се изчисли печалбата за всяко тримесечие, така че те са свързани с формулата в целевата клетка F7, =SUM(Q1 Profit:Q2 Profit).

Преди оценката със Solver

1. Променливи клетки

2. Клетка с ограничение

3. Целева клетка

След изпълнението на Solver новите стойности са, както следва.

След оценката със Solver

  1. В раздела Данни, в групата Анализ щракнете върху Solver.
    Упражнение в Powerpoint 2010

    Забележка:  Ако командата Solver или групата Анализ не са налични, трябва да активирате добавката Solver. Вижте: Как да активирате добавката Solver.

    Изображение на диалоговия прозорец "Excel 2010+ Solver"
  2. В полето Задаване на цел въведе препратка към клетка или име за целевата клетка. Целевата клетка трябва да съдържа формула.

  3. Направете едно от следните неща:

    • Ако искате стойността на целевата клетка да бъде колкото е възможно по-голяма, щракнете върху Макс..

    • Ако искате стойността на целевата клетка да бъде колкото е възможно по-малка, щракнете върху Мин..

    • Ако искате целевата клетка да бъде конкретна стойност, щракнете върху Стойност на и след това въведете стойността в полето.

    • В полето Чрез променяне на променливите клетките въведете име или препратка за всеки диапазон от клетки за променливи на решението. Разделете несъседните препратки със запетаи. Променливите клетки трябва да бъдат свързани пряко или косвено с целевата клетка. Можете да зададете до 200 променливи клетки.

  4. В полето При наложени ограничения въведете всички ограничения, които искате да наложите, като направите следното:

    1. В диалоговия прозорец Параметри на Solver щракнете върху Добави.

    2. В полето Препратка към клетка въведете препратката към клетка или името на диапазона от клетки, за които искате да ограничите стойността.

    3. Щракнете върху релацията(<= , =, >=, int, binили dif ), която искате между клетката с препратка и ограничението. Ако щракнете върху int, в полето Ограничение се показва цяло число. Ако щракнете върху int, в полето Ограничение се появява цяло число. Ако щракнете върху dif, alldifferent се появява в полето Ограничение.

    4. Ако изберете <=, =, или >= за отношението в полето Ограничение, въведете число, препратка към клетка, име или формула.

    5. Направете едно от следните неща:

      • За да приемете ограничението и да добавите друго, щракнете върху Добави.

      • За да приемете ограничението и да се върнете в диалоговия прозорец Параметри на Solver, щракнете върху OK.
        Забележка    Можете да приложите отношенията int, bin и dif само в ограничения на клетки за променливи на решението.

        Можете да промените или изтриете съществуващо ограничение, като направите следното:

    6. В диалоговия прозорец Параметри на Solver щракнете върху ограничението, което искате да промените или изтриете.

    7. Щракнете върху Замени и направете промените, или щракнете върху Изтрий.

  5. Щракнете върху Решавай и направете едно от следните неща:

    • За да запазите стойностите от решението в работния лист, щракнете върху Запази решението на Solver в диалоговия прозорец Резултати от Solver.

    • За да възстановите първоначалните стойности, преди да щракнете върху Решаване щракнете върху Възстанови първоначалните стойности.

    • Можете да прекъснете процеса на решаването, като натиснете клавиша Esc. Excel преизчислява работния лист с последните стойности, които са получени за клетките за променливи на решението.

    • За да създадете отчет, който е базиран на вашето решение, след като Solver намери решение, можете да щракнете върху тип на отчет в полето Отчети и след това да щракнете върху OK. Отчетът се създава в нов работен лист от вашата работна книга. Ако Solver не намира решение, налични са само някои отчета или няма отчети.

    • За да запишете вашите стойности на клетки за променливи на решението като сценарий, който да можете да покажете по-късно, щракнете върху Запиши сценария в диалоговия прозорец Резултати от Solver и след това въведете име за сценария в полето Име на сценарий.

  1. След като дефинирате задачата, щракнете върху Опции в диалоговия прозорец Параметри на Solver.

  2. В диалоговия прозорец Опции изберете квадратчето за отметка Покажи резултатите от итерациите, за да видите стойностите на всеки опит за решение, и след това щракнете върху OK.

  3. В диалоговия прозорец Параметри на Solver щракнете върху Решавай.

  4. В диалоговия прозорец Показване на опитите за решаване направете едно от следните неща:

    • За да спрете процеса на решаване и да покажете диалоговия прозорец Резултати от Solver, щракнете върху Стоп.

    • За да продължите процеса на решаване и да покажете следващия опит за решение, щракнете върху Продължи.

  1. В диалоговия прозорец Параметри на Solver щракнете върху Опции.

  2. Изберете или въведете стойности за всички опции на разделите Всички методи, Нелинеен GRG и Еволюционен в диалоговия прозорец.

  1. В диалоговия прозорец Параметри на Solver щракнете върху Зареди/запиши.

  2. Въведете диапазон от клетки за областта на модела и щракнете или върху Запиши, или върху Зареди.

    Когато записвате модел, въведете препратката за първата клетка на вертикален диапазон от празни клетки, в който искате да поставите модела на задачата. Когато зареждате модел, въведете препратката за целия диапазон от клетки, който съдържа модела на задачата.

    Съвет: Можете да запишете последните селекции в диалоговия прозорец Параметри на Solver заедно с работния лист чрез записване на работната книга. Всеки работен лист в работната книга може да има свои собствени селекции за Solver и всички те се записват. Можете също да дефинирате повече от една задача на работен лист, като щракнете върху Зареди/запиши, за да запишете задачите поотделно.

Можете да изберете кой да е от следните три алгоритъма или метода за решаване в диалоговия прозорец Параметри на Solver:

  • Нелинеен обобщен приведен градиент (GRG)    Използвайте за нелинейни гладки задачи.

  • LP Simplex    Използвайте за линейни задачи.

  • Еволюционен    Използвайте за задачи, които не са гладки.

Важно: Първо трябва да разрешите добавката Solver. За повече информация вижте Зареждане на добавката Solver.

В следващия пример средствата за рекламиране през всяко тримесечие засягат броя на продадените изделия, определяйки косвено размера на приходите от продажби, свързаните разходи и печалбата. Solver може да променя тримесечните бюджети за реклама (клетки с променливи за решение B5:C5), до общо бюджетно ограничение от 20 000 лв. (клетка D5), докато общата печалба (цел клетка D7) достигне максималната възможна сума. Стойностите в променливите клетки се използват за изчисляване на печалбата за всяко тримесечие, така че те са свързани с формулата цел клетка D7, =SUM(Q1 Печалба:Q2 Печалба).

Example Solver evaluation

Изнесено означение 1 променливи клетки

Споделяне на работна книга ограничена клетка

Callout 3 цел

След изпълнението на Solver новите стойности са, както следва.

PowerPoint Web App

  1. В Excel 2016 for Mac: Щракнете върху Данни > Solver.

    Решател

    В Excel for Mac 2011: Щракнете върху раздела Данни, под Анализщракнете върху Solver.

    Data tab, Analysis group, Solver Add-In

  2. В Задаване нацел въведете препратка към клетка име за целната клетка.

    Забележка: Целевата клетка трябва да съдържа формула.

  3. Направете едно от следните неща:

    За да

    Направете следното

    Направете стойността на обективната клетка възможно най-голяма

    Щракнете върху Макс.

    Направете стойността на целната клетка възможно най-малка

    Щракнете върху Мин.

    Задаване на целната клетка на определена стойност

    Щракнете върху Стойностна и след това въведете стойността в полето.

  4. В полето Чрез променяне на променливите клетките въведете име или препратка за всеки диапазон от клетки за променливи на решението. Разделете несъседните препратки със запетаи.

    Променливите клетки трябва да бъдат свързани пряко или косвено с целевата клетка. Можете да зададете до 200 променливи клетки.

  5. В полето Тема на ограниченията добавете всички ограничения, които искате да приложите.

    За да добавите ограничение, изпълнете следните стъпки:

    1. В диалоговия прозорец Параметри на Solver щракнете върху Добави.

    2. В полето Препратка към клетка въведете препратката към клетка или името на диапазона от клетки, за които искате да ограничите стойността.

    3. В изскачащ <= релация изберете релацията, която искате, между клетката с препратка и ограничението. Ако изберете<=, =или >=в полето Ограничение въведете число, препратка към клетка или име или формула.

      Забележка: Можете да приложите релациите int, bin и dif само в ограничения върху клетки с променливи на решение.

    4. Направете едно от следните неща:

    За да

    Направете следното

    Приемете ограничението и добавете друг

    Щракнете върху Добави.

    Приемете ограничението и се върнете към диалоговия прозорец Параметри на Solver

    Щракнете върху OK.

  6. Щракнете върху Решаванеи след това направете едно от следните неща:

    За да

    Направете следното

    Запазване на стойностите на решението в листа

    Щракнете върху Запази решението на Solver в диалоговия прозорец Резултати от Solver.

    Възстановяване на първоначалните данни

    Щракнете върху Възстановяване на първоначалните стойности.

Забележки: 

  1. За да прекъснете процеса на решение, натиснете ESC . Excel преизчислява листа с последните стойности, които са намерени за настройваемите клетки.

  2. За да създадете отчет, който е базиран на вашето решение, след като Solver намери решение, можете да щракнете върху тип на отчет в полето Отчети и след това да щракнете върху OK. Отчетът се създава в нов лист във вашата работна книга. Ако Solver не намери решение, опцията за създаване на отчет не е налична.

  3. За да запишете стойностите на клетките като сценарий, който можете да покажете по-късно, щракнете върху Запиши сценария в диалоговия прозорец Резултати от Solver и след това въведете име за сценария в полето Име на сценарий.

  1. В Excel 2016 for Mac: Щракнете върху Данни > Solver.

    Решател

    В Excel for Mac 2011: Щракнете върху раздела Данни, под Анализщракнете върху Solver.

    Data tab, Analysis group, Solver Add-In

  2. След като дефинирате проблем, в диалоговия прозорец Параметри на Solver щракнете върху Опции.

  3. Поставете отметка в квадратчето Показвай резултатите от итерацията, за да видите стойностите на всяко пробно решение, и след това щракнете върху OK.

  4. В диалоговия прозорец Параметри на Solver щракнете върху Решавай.

  5. В диалоговия прозорец Покажи пробното решение направете едно от следните неща:

    За да

    Направете следното

    Спиране на процеса на решение и показване на диалоговия прозорец Резултати от Solver

    Щракнете върху Спри.

    Продължете процеса на решение и покажете следващото пробно решение

    Щракнете върху Продължи.

  1. В Excel 2016 for Mac: Щракнете върху Данни > Solver.

    Решател

    В Excel for Mac 2011: Щракнете върху раздела Данни, под Анализщракнете върху Solver.

    Data tab, Analysis group, Solver Add-In

  2. Щракнете върхуОпции и след това в диалоговия прозорец Опции или Опции за Solver изберете една или повече от следните опции:

    За да

    Направете следното

    Задаване на време и итерации на решение

    В раздела Всички методи, под Ограничения за решаване, в полето Максимално време (секунди) въведете броя секунди, които искате да разрешите за времето на решението. След това в полето Итерации въведете максималния брой итерации, които искате да разрешите.

    Забележка: Ако процесът на решение достигне максималното време или брой итерации, преди Solver да намери решение, Solver показва диалоговия прозорец Покажи пробното решение.

    Задаване на степента на точност

    В раздела Всички методи, в полето Точност на ограничението въведете степента на точност, която искате. Толкова по-малко е числото, толкова по-висока е точността.

    Задаване на степента на конвергенция

    В раздела GRG Nonlinearили Evolutionary, в полето Конвергенция въведете количеството относителна промяна, която искате да разрешите в последните пет итерации, преди Solver да спре с решение. По-малкото число, толкова по-малка е относителната промяна.

  3. Щракнете върху OK.

  4. В диалоговия прозорец Параметри на Solver щракнете върху Решаване илиЗатваряне.

  1. В Excel 2016 for Mac: Щракнете върху Данни > Solver.

    Решател

    В Excel for Mac 2011: Щракнете върху раздела Данни, под Анализщракнете върху Solver.

    Data tab, Analysis group, Solver Add-In

  2. Щракнете върху Зареждане/записване, въведете диапазон от клетки за областта на модела и след това щракнете върху Запиши илиЗареди.

    Когато записвате модел, въведете препратката за първата клетка на вертикален диапазон от празни клетки, в който искате да поставите модела на задачата. Когато зареждате модел, въведете препратката за целия диапазон от клетки, който съдържа модела на задачата.

    Съвет: Можете да запишете последните селекции в диалоговия прозорец Параметри на Solver с лист, като запишете работната книга. Всеки лист в работна книга може да има собствени селекции на Solver и всички те да бъдат записани. Можете също да дефинирате повече от един проблем за лист, като щракнете върху Зареждане/записване, за да запишете проблемите поотделно.

  1. В Excel 2016 for Mac: Щракнете върху Данни > Solver.

    Решател

    В Excel for Mac 2011: Щракнете върху раздела Данни, под Анализщракнете върху Solver.

    Data tab, Analysis group, Solver Add-In

  2. В изскачащо меню Избор на метод за решаване изберете едно от следните неща:

Метод на решаване

Описание

GRG (генерализиран намален градиент) Nonlinear

Изборът по подразбиране за модели, използващи повечето функции на Excel, различни от IF, CHOOSE, LOOKUP и други функции "стъпка".

Simplex LP

Използвайте този метод за проблеми с линейното програмиране. Вашият модел трябва да използва SUM, SUMPRODUCT, + – и * във формули, които зависят от променливите клетки.

Еволюционен

Този метод, базиран на генетични алгоритми, е най-добър, когато вашият модел използва IF, CHOOSE или LOOKUP с аргументи, които зависят от променливите клетки.

Забележка: Части от програмния код на Solver са авторско право 1990-2010 от Frontline Systems, Inc. Частите са авторско право 1989 по оптимални методи, Inc.

Тъй като програмите за добавки не се поддържат в Excel за уеб, няма да можете да използвате добавката Solver, за да изпълните т.нар. анализ на вашите данни, за да ви помогне да намерите оптимални решения.

Ако имате настолното приложение Excel, можете да използвате бутона Отвори в Excel, за да отворите работната книга, за да използвате добавката Solver.

Още помощ за използването на Solver

За по-подробна помощ за контакта на Solver:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
Уеб сайт: http://www.solver.com
Имейл: помощ info@solver.com
Solver на www.solver.com.

Части от програмния код на Solver са авторско право 1990-2009 на Frontline Systems, Inc. Други части са авторско право 1989 на Optimal Methods, Inc.

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на Excel, да получите поддръжка в общността за отговори или да предложите нова функция или подобрение на User Voice за Excel.

Вж. също

Използване на Solver за бюджетиране на главни букви

Използване на Solver за определяне на оптималния продуктов микс

Въведение в условния анализ

Общ преглед на формулите в Excel

Начини за избягване на повредени формули

Откриване на грешки във формули

Клавишни комбинации в Excel

Функции на Excel (по азбучен ред)

Функции на Excel (по категории)

Нуждаете се от още помощ?

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×