Excel Solver: Оптимизиране на резултати, добавяне на ограничения и запазване на решения като сценарии

Вие настройвате своя модел Excel Solver с помощта на диалоговия прозорец Параметри на решаване. Използвате полето Set Objective, за да посочите целевата клетка, и използвате групата To, за да кажете на Excel Solver какво искате от целевата клетка: максималната възможна стойност; минималната възможна стойност; или конкретна стойност. И накрая, използвате полето Чрез промяна на променливи клетки, за да посочите клетките, които Solver може да използва за добавяне на стойности, за да оптимизира резултата.

Оптимизиране на резултатите от Excel Solver

Когато Solver намери решение, можете да изберете или Keep Solver Solution или Restore Original Values. Ако изберете Keep Solver Solution, Excel променя постоянно работния лист. Не можете да отмените промените.

С готов за работа модел на работен лист, готов за решаване, ето стъпките, които трябва да следвате, за да намерите оптимален резултат за вашия модел с помощта на Solver:

Изберете Данни → Решавач.
Excel отваря диалоговия прозорец Параметри на решаване.

В полето Set Objective въведете адреса на целевата клетка на вашия модел.
Имайте предвид, че ако щракнете върху клетката, за да я въведете, Solver автоматично въвежда абсолютен адрес на клетка (например $B$14 вместо B14). Solver работи добре така или иначе.

В групата До изберете опция:

• Max: Връща максималната възможна стойност.
• Мин: Връща минималната възможна стойност.
• Стойност на: Въведете число, за да зададете целевата клетка на това число.

  За примерния модел е избрана стойност на и е въведено 0 в текстовото поле.

В полето Чрез промяна на променливи клетки въведете адресите на клетките, които искате да промени Solver, докато търси решение.
В примера променящите се клетки са B4 и C4. Следното изображение показва завършения диалогов прозорец Параметри на решаване.

Завършеният диалогов прозорец Параметри на решаване.

Щракнете върху Решаване.
Solver се захваща с работата. Тъй като Solver работи върху проблема, може да видите диалоговите прозорци Покажи пробно решение да се показват един или повече пъти.

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

Диалоговият прозорец Резултати от решаването и решението на проблема с рентабилността.

Изберете опцията Keep Solver Solution.
Ако не искате да приемете резултата, изберете вместо това опцията Възстановяване на оригиналните стойности.

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

Можете да помолите Solver да покаже един или повече отчети, които ви дават допълнителна информация за резултатите. В диалоговия прозорец Резултати от решаването използвайте списъка с отчети, за да изберете всеки отчет, който искате да видите:

• Отговор: Показва информация за целевата клетка на модела, променливите клетки и ограниченията. За целевата клетка и променливите клетки, Solver показва първоначалните и крайните стойности.
• Чувствителност: Опитите да се покаже колко чувствително е решението към промените във формулите на модела. Оформлението на отчета за чувствителността зависи от типа модел, който използвате.
• Ограничения: Показва целевата клетка и нейната стойност, както и променливите клетки и техните адреси, имена и стойности.

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

• Simplex LP: Използвайте, ако моделът на вашия работен лист е линеен. Най-просто казано, линеен модел е този, в който променливите не се повишават до никакви степени и не се използва нито една от така наречените трансцендентни функции - като SIN и COS.
• GRG Nonlinear: Използвайте, ако моделът на вашия работен лист е нелинеен и гладък. Най-общо казано, гладкият модел е този, при който графиката на използваното уравнение не показва остри ръбове или счупвания.
• Еволюционно: Използвайте, ако моделът на вашия работен лист е нелинеен и негладък.

Трябва ли да се тревожите за нещо от това? Почти сигурно не. Excel Solver по подразбиране използва GRG Nonlinear и това трябва да работи за почти всичко, което правите с Solver.

Добавяне на ограничения към Excel Solver

Реалният свят поставя ограничения и условия върху формулите. Една фабрика може да има максимален капацитет от 10 000 единици на ден, броят на служителите в една компания не може да бъде отрицателно число и вашите рекламни разходи може да бъдат ограничени до 10 процента от общите разходи.

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

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

Ето как да стартирате Solver с ограничения, добавени към оптимизацията:

Изберете Данни → Решавач.
Excel отваря диалоговия прозорец Параметри на решаване.

Използвайте полето Задаване на цел, групата До и полето Чрез промяна на променливи клетки, за да настроите Solver, както е описано по-горе.

Щракнете върху Добавяне.
Excel показва диалоговия прозорец Добавяне на ограничение.

В полето Справка на клетка въведете адреса на клетката, която искате да ограничите.
Можете да въведете адреса или да изберете клетката в работния лист.

В падащия списък изберете оператора, който искате да използвате.
През повечето време използвате оператор за сравнение, като равен на (=) или по-голям от (>). Използвайте оператора int (цело число), когато имате нужда от ограничение, като например общ брой служители, да бъде цяло число вместо реално число (тоест число с десетичен компонент; не можете да имате 10,5 служители!). Използвайте оператора bin (двоичен), когато имате ограничение, което трябва да бъде TRUE или FALSE (или 1 или 0).

Ако сте избрали оператор за сравнение в стъпка 5, в полето Ограничение въведете стойността, с която искате да ограничите клетката.
Това изображение показва пример за завършен диалогов прозорец за добавяне на ограничение. В примерния модел това ограничение казва на Solver да намери решение, така че печалбата от продукта на надуваемата дъска за дартс (клетка B12) да е равна на 0.

Завършеният диалогов прозорец Добавяне на ограничение.

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

За примера добавяте ограничение, което изисква печалбата от продукта Dog Polisher (клетка C12) да бъде 0.

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

Excel се връща в диалоговия прозорец Параметри на решаване и показва вашите ограничения в списъчното поле Subject to the Constraints.

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

Във всеки диалогов прозорец Покажи пробно решение, който се показва, щракнете върху Продължи, за да преместите нещата.
Изображението по-долу показва примерното решение за рентабилност с добавени ограничения. Забележете, че не само клетката Обща печалба (B14) е зададена на 0, но и двете клетки за печалба на продукта (B12 и C12).

Диалоговият прозорец Резултати от решаването и окончателното решение на проблема с рентабилността.

Изберете опцията Keep Solver Solution.
Ако не искате да приемете резултата, изберете вместо това опцията Възстановяване на оригиналните стойности.

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

Можете да добавите максимум 100 ограничения. Освен това, ако трябва да направите промяна в ограничение, преди да започнете да решавате, изберете ограничението в списъчното поле Subject to the Constraints, щракнете върху Промяна и след това направете своите корекции в диалоговия прозорец Промяна на ограничението, който се показва. Ако искате да изтриете ограничение, което вече не ви е необходимо, изберете ограничението и след това щракнете върху Изтрий.

Запазете решение на Excel Solver като сценарий

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

Следвайте тези стъпки, за да запазите решение на Solver като сценарий:

Изберете Данни → Решавач.
Excel отваря диалоговия прозорец Параметри на решаване.

Използвайте полето Задаване на цел, групата До, полето Чрез промяна на променливи клетки и списъка Предмет на ограниченията, за да настроите Solver, както е описано по-горе.

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

Всеки път, когато се появи диалоговият прозорец Покажи пробно решение, изберете Продължи.
Когато оптимизацията приключи, Excel показва диалоговия прозорец Резултати от решаването.

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

В диалоговия прозорец Име на сценария въведете име за сценария и след това щракнете върху OK.
Excel ви връща в диалоговия прозорец Резултати от решаването.

Изберете опцията Keep Solver Solution.
Ако не искате да приемете резултата, изберете вместо това опцията Възстановяване на оригиналните стойности.

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