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

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

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

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

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

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

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

   Забележка:  Ако командата Solver или групата Анализ не са налични, трябва да активирате добавката Solver. Вижте: Как да активирате добавката 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 може да променя тримесечните бюджети за реклама (клетки за променливи на решението B5:C5) до общо бюджетно ограничение от 20 000 лв. (клетка D5), докато общата печалба (целева клетка D7) достигне максималния възможен размер. Стойностите в променливите клетки се използват за изчисляване на печалбата за всяко тримесечие, така че те са свързани с формулата целева клетка D7, =SUM(Q1 Profit:Q2 Profit).

променливи клетки

клетка с ограничения

целева клетка

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

1. Щракнете върху Данни > Solver.

   

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

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

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

   За да	Направете следното
   Направете стойността на целевата клетка възможно най-голяма	Щракнете върху Макс.
   Направете стойността на целевата клетка възможно най-малка	Щракнете върху Мин.
   Задаване на определена стойност на целевата клетка	Щракнете върху Стойност на и след това въведете стойността в полето.

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

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

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

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

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

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

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

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

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

   За да	Направете следното
   Приемете ограничението и добавете друго	Щракнете върху Добави.
   Приемане на ограничението и връщане към диалоговия прозорец Параметри на Solver	Щракнете върху OK.

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

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

1. Щракнете върху Данни > Solver.

   

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

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

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

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

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

1. Щракнете върху Данни > Solver.

   

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

   За да	Направете следното
   Задаване на време за решение и итерации	В раздела Всички методи , под Ограничения на решаването, в полето Максимално време (секунди) въведете броя секунди, който искате да разрешите за времето на решението. След това в полето Итерации въведете максималния брой итерации, които искате да разрешите. Забележка: Ако процесът на решение достигне максималното време или брой итерации, преди Solver да намери решение, Solver показва диалоговия прозорец Показване на пробно решение .
   Задаване на степента на точност	В раздела Всички методи , в полето Точност на ограниченията въведете желаната степен на точност. Колкото по-малко е числото, толкова по-голяма е точността.
   Задаване на степента на сходимост	В нелинейния или еволюционния grg раздел, в полето Сходимост въведете стойността на относителната промяна, която искате да разрешите, в последните пет итерации, преди Solver да спре с решение. Колкото по-малко е това число, толкова по-малко относителна промяна е разрешена.

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

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

1. Щракнете върху Данни > Solver.

   

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

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

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

1. Щракнете върху Данни > Solver.

   

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