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).
1. Променливи клетки
2. Клетка с ограничение
3. Целева клетка
След изпълнението на Solver новите стойности са, както следва.
-
В раздела Данни, в групата Анализ щракнете върху Solver.
Забележка: Ако командата Solver или групата Анализ не са налични, трябва да активирате добавката Solver. Вижте: Как да активирате добавката Solver.
-
В полето Задаване на цел въведе препратка към клетка или име за целевата клетка. Целевата клетка трябва да съдържа формула.
-
Направете едно от следните неща:
-
Ако искате стойността на целевата клетка да бъде колкото е възможно по-голяма, щракнете върху Макс..
-
Ако искате стойността на целевата клетка да бъде колкото е възможно по-малка, щракнете върху Мин..
-
Ако искате целевата клетка да бъде конкретна стойност, щракнете върху Стойност на и след това въведете стойността в полето.
-
В полето Чрез променяне на променливите клетките въведете име или препратка за всеки диапазон от клетки за променливи на решението. Разделете несъседните препратки със запетаи. Променливите клетки трябва да бъдат свързани пряко или косвено с целевата клетка. Можете да зададете до 200 променливи клетки.
-
-
В полето При наложени ограничения въведете всички ограничения, които искате да наложите, като направите следното:
-
В диалоговия прозорец Параметри на Solver щракнете върху Добави.
-
В полето Препратка към клетка въведете препратката към клетка или името на диапазона от клетки, за които искате да ограничите стойността.
-
Щракнете върху желаната релация ( <=, =, >=, int, bin или dif ), която искате между клетката, към която има препратка, и ограничението. Ако щракнете върху int, в полето Ограничение се появява цяло число. Ако щракнете върху int, в полето Ограничение се появява цяло число. Ако щракнете върху dif, в полето Ограничение се появява alldifferent.
-
Ако изберете <=, =, или >= за отношението в полето Ограничение, въведете число, препратка към клетка, име или формула.
-
Направете едно от следните неща:
-
За да приемете ограничението и да добавите друго, щракнете върху Добави.
-
За да приемете ограничението и да се върнете в диалоговия прозорец Параметри на Solver, щракнете върху OK.
Забележка Можете да приложите отношенията int, bin и dif само в ограничения на клетки за променливи на решението.Можете да промените или изтриете съществуващо ограничение, като направите следното:
-
-
В диалоговия прозорец Параметри на Solver щракнете върху ограничението, което искате да промените или изтриете.
-
Щракнете върху Замени и направете промените, или щракнете върху Изтрий.
-
-
Щракнете върху Решавай и направете едно от следните неща:
-
За да запазите стойностите от решението в работния лист, щракнете върху Запази решението на Solver в диалоговия прозорец Резултати от Solver.
-
За да възстановите първоначалните стойности, преди да щракнете върху Решаване щракнете върху Възстанови първоначалните стойности.
-
Можете да прекъснете процеса на решаването, като натиснете клавиша Esc. Excel преизчислява работния лист с последните стойности, които са получени за клетките за променливи на решението.
-
За да създадете отчет, който е базиран на вашето решение, след като Solver намери решение, можете да щракнете върху тип на отчет в полето Отчети и след това да щракнете върху OK. Отчетът се създава в нов работен лист от вашата работна книга. Ако Solver не намира решение, налични са само някои отчета или няма отчети.
-
За да запишете вашите стойности на клетки за променливи на решението като сценарий, който да можете да покажете по-късно, щракнете върху Запиши сценария в диалоговия прозорец Резултати от Solver и след това въведете име за сценария в полето Име на сценарий.
-
-
След като дефинирате задачата, щракнете върху Опции в диалоговия прозорец Параметри на Solver.
-
В диалоговия прозорец Опции изберете квадратчето за отметка Покажи резултатите от итерациите, за да видите стойностите на всеки опит за решение, и след това щракнете върху OK.
-
В диалоговия прозорец Параметри на Solver щракнете върху Решавай.
-
В диалоговия прозорец Показване на опитите за решаване направете едно от следните неща:
-
За да спрете процеса на решаване и да покажете диалоговия прозорец Резултати от Solver, щракнете върху Стоп.
-
За да продължите процеса на решаване и да покажете следващия опит за решение, щракнете върху Продължи.
-
-
В диалоговия прозорец Параметри на Solver щракнете върху Опции.
-
Изберете или въведете стойности за всички опции на разделите Всички методи, Нелинеен GRG и Еволюционен в диалоговия прозорец.
-
В диалоговия прозорец Параметри на Solver щракнете върху Зареди/запиши.
-
Въведете диапазон от клетки за областта на модела и щракнете или върху Запиши, или върху Зареди.
Когато записвате модел, въведете препратката за първата клетка на вертикален диапазон от празни клетки, в който искате да поставите модела на задачата. Когато зареждате модел, въведете препратката за целия диапазон от клетки, който съдържа модела на задачата.
Съвет: Можете да запишете последните селекции в диалоговия прозорец Параметри на Solver заедно с работния лист чрез записване на работната книга. Всеки работен лист в работната книга може да има свои собствени селекции за Solver и всички те се записват. Можете също да дефинирате повече от една задача на работен лист, като щракнете върху Зареди/запиши, за да запишете задачите поотделно.
Можете да изберете кой да е от следните три алгоритъма или метода за решаване в диалоговия прозорец Параметри на Solver:
-
Нелинеен обобщен приведен градиент (GRG) Използвайте за нелинейни гладки задачи.
-
LP Simplex Използвайте за линейни задачи.
-
Еволюционен Използвайте за задачи, които не са гладки.
Важно: Трябва първо да разрешите добавката Solver. За повече информация вижте Зареждане на добавката Solver.
В следващия пример средствата за рекламиране през всяко тримесечие засягат броя на продадените изделия, определяйки косвено размера на приходите от продажби, свързаните разходи и печалбата. Solver може да променя тримесечните бюджети за реклама (клетки за променливи на решението B5:C5) до общо бюджетно ограничение от 20 000 лв. (клетка D5), докато общата печалба (целева клетка D7) достигне максималния възможен размер. Стойностите в променливите клетки се използват за изчисляване на печалбата за всяко тримесечие, така че те са свързани с формулата целева клетка D7, =SUM(Q1 Profit:Q2 Profit).
След изпълнението на Solver новите стойности са, както следва.
-
В Excel 2016 for Mac: Щракнете върху Данни > Solver.
В Excel for Mac 2011: Щракнете върху раздела Данни , под Анализ щракнете върху Solver.
-
В Задаване на цел въведете препратка към клетка или име за целевата клетка.
Забележка: Целевата клетка трябва да съдържа формула.
-
Направете едно от следните неща:
За да
Направете следното
Направете стойността на целевата клетка възможно най-голяма
Щракнете върху Макс.
Направете стойността на целевата клетка възможно най-малка
Щракнете върху Мин.
Задаване на определена стойност на целевата клетка
Щракнете върху Стойност на и след това въведете стойността в полето.
-
В полето Чрез променяне на променливите клетките въведете име или препратка за всеки диапазон от клетки за променливи на решението. Разделете несъседните препратки със запетаи.
Променливите клетки трябва да бъдат свързани пряко или косвено с целевата клетка. Можете да зададете до 200 променливи клетки.
-
В полето Подчинено на ограниченията добавете ограниченията, които искате да приложите.
За да добавите ограничение, изпълнете следните стъпки:
-
В диалоговия прозорец Параметри на Solver щракнете върху Добави.
-
В полето Препратка към клетка въведете препратката към клетка или името на диапазона от клетки, за които искате да ограничите стойността.
-
В изскачащото меню <= релация изберете желаната релация между адресираната клетка и ограничението. Ако изберете <=, =, или >=, в полето Ограничение въведете число, препратка към клетка, име или формула.
Забележка: Можете да прилагате релациите int, bin и dif само в ограниченията на клетките за променливи на решението.
-
Направете едно от следните неща:
За да
Направете следното
Приемете ограничението и добавете друго
Щракнете върху Добави.
Приемане на ограничението и връщане към диалоговия прозорец Параметри на Solver
Щракнете върху OK.
-
-
Щракнете върху Решаване и след това направете едно от следните неща:
За да
Направете следното
Запазване на стойностите на решението в листа
Щракнете върху Запази решението на Solver в диалоговия прозорец Резултати от Solver .
Възстановяване на първоначалните данни
Щракнете върху Възстановяване на първоначалните стойности.
Забележки:
-
За да прекъснете процеса на решаване, натиснете ESC. Excel преизчислява листа с последните стойности, които са намерени за настройваемите клетки.
-
За да създадете отчет, който е базиран на вашето решение, след като Solver намери решение, можете да щракнете върху тип на отчет в полето Отчети и след това да щракнете върху OK. Отчетът се създава в нов лист във вашата работна книга. Ако Solver не намери решение, опцията за създаване на отчет не е налична.
-
За да запишете вашите коригиращи стойности на клетки като сценарий, който можете да покажете по-късно, щракнете върху Запиши сценария в диалоговия прозорец Резултати от Solver и след това въведете име за сценария в полето Име на сценарий .
-
В Excel 2016 for Mac: Щракнете върху Данни > Solver.
В Excel for Mac 2011: Щракнете върху раздела Данни , под Анализ щракнете върху Solver.
-
След като дефинирате проблем, в диалоговия прозорец Параметри на Solver щракнете върху Опции.
-
Поставете отметка в квадратчето Показвай резултатите от итерацията , за да видите стойностите на всяко решение за изпробване, и след това щракнете върху OK.
-
В диалоговия прозорец Параметри на Solver щракнете върху Решавай.
-
В диалоговия прозорец Показване на пробно решение направете едно от следните неща:
За да
Направете следното
Спиране на процеса на решаване и показване на диалоговия прозорец "Резултати от Solver "
Щракнете върху Спри.
Продължаване на процеса на решаване и показване на следващото пробно решение
Щракнете върху Продължи.
-
В Excel 2016 for Mac: Щракнете върху Данни > Solver.
В Excel for Mac 2011: Щракнете върху раздела Данни , под Анализ щракнете върху Solver.
-
Щракнете върху Опции и след това в диалоговия прозорец Опции или Опции на Solver изберете една или повече от следните опции:
За да
Направете следното
Задаване на време за решение и итерации
В раздела Всички методи , под Ограничения на решаването, в полето Максимално време (секунди) въведете броя секунди, който искате да разрешите за времето на решението. След това в полето Итерации въведете максималния брой итерации, които искате да разрешите.
Забележка: Ако процесът на решение достигне максималното време или брой итерации, преди Solver да намери решение, Solver показва диалоговия прозорец Показване на пробно решение .
Задаване на степента на точност
В раздела Всички методи , в полето Точност на ограниченията въведете желаната степен на точност. Колкото по-малко е числото, толкова по-голяма е точността.
Задаване на степента на сходимост
В нелинейния или еволюционния grg раздел, в полето Сходимост въведете стойността на относителната промяна, която искате да разрешите, в последните пет итерации, преди Solver да спре с решение. Колкото по-малко е това число, толкова по-малко относителна промяна е разрешена.
-
Щракнете върху OK.
-
В диалоговия прозорец Параметри на Solver щракнете върху Реши или Затвори.
-
В Excel 2016 for Mac: Щракнете върху Данни > Solver.
В Excel for Mac 2011: Щракнете върху раздела Данни , под Анализ щракнете върху Solver.
-
Щракнете върху Зареждане/записване, въведете диапазон от клетки за областта на модела и след това щракнете върху Запиши или Зареди.
Когато записвате модел, въведете препратката за първата клетка на вертикален диапазон от празни клетки, в който искате да поставите модела на задачата. Когато зареждате модел, въведете препратката за целия диапазон от клетки, който съдържа модела на задачата.
Съвет: Можете да запишете последните селекции в диалоговия прозорец Параметри на Solver заедно с лист, като запишете работната книга. Всеки лист в работна книга може да има свои собствени селекции за Solver и всички те се записват. Можете също да дефинирате повече от един проблем за лист, като щракнете върху Зареди/запиши , за да запишете проблемите поотделно.
-
В Excel 2016 for Mac: Щракнете върху Данни > Solver.
В Excel for Mac 2011: Щракнете върху раздела Данни , под Анализ щракнете върху Solver.
-
В изскачащото меню Избор на метод за решаване изберете едно от следните неща:
|
Метод на решаване |
Описание |
|---|---|
|
Нелинеен GRG (генерализиран намален градиент) |
Изборът по подразбиране за модели, използващи повечето функции на Excel, различни от IF, CHOOSE, LOOKUP и други функции "стъпка". |
|
Simplex LP |
Използвайте този метод за проблеми с линейно програмиране. Вашият модел трябва да използва SUM, SUMPRODUCT, + - и * във формули, които зависят от променливите клетки. |
|
Еволюционен |
Този метод, базиран на генетични алгоритми, е най-добър, когато вашият модел използва IF, CHOOSE или LOOKUP с аргументи, които зависят от променливите клетки. |
Забележка: Части от програмния код на Solver са авторско право 1990-2010 от Frontline Systems, Inc. Частите са авторско право 1989 на Optimal Methods, Inc.
Тъй като програмите добавки не се поддържат в Excel за уеб, няма да можете да използвате добавката Solver, за да изпълните условен анализ на данните си, за да ви помогне да намерите оптимални решения.
Ако имате настолното приложение Excel, можете да използвате бутона Отвори в Excel , за да отворите работната книга и да използвате добавката Solver.
Още помощ за използването на Solver
За по-подробна помощ за контакт със Solver:
Frontline Systems, Inc.
Пощенска кутия 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 или да получите поддръжка в Общността за отговори от.
Вж. също
Използване на Solver за бюджетиране на главни букви
Използване на Solver за определяне на оптималната комбинация от продукти
Общ преглед на формулите в Excel
Начини за избягване на повредени формули
Откриване на грешки във формули
