Надстройка Поиск решения в Microsoft Excel

1. Занятие 14

Надстройка Поиск решения

2. Надстройка «Поиск решения» в Microsoft Excel

Надстройка «Поиск решения» позволяет найти оптимальное значение для
результата, содержащегося в одной ячейке, называемой целевой, с учетом
ограничений на переменные.
«Поиск решения» работает с группой ячеек, прямо или косвенно связанных с
формулой в целевой ячейке. Чтобы получить заданный результат по формуле
из целевой ячейки, «Поиск решения» изменяет значения в назначенных
ячейках, называемых изменяемыми ячейками.
Для уменьшения количества значений, используемых в модели, применяются
ограничения, которые могут ссылаться на другие ячейки, влияющие на
формулу для целевой ячейки.

3. Решение задачи линейного программирования

Для того чтобы решить задачу линейного программирования (ЛП) в табличном
редакторе Microsoft Excel, необходимо выполнить следующие действия.
1. Ввести условие задачи:
• a) создать экранную форму для ввода условия задачи:
– -переменных,
– -целевой функции (ЦФ),
– -ограничений,
– -граничных условий;
• b) ввести исходные данные в экранную форму:
– -коэффициенты ЦФ,
– -коэффициенты при переменных в ограничениях,
– -правые части ограничений;
• c) ввести зависимости из математической модели в экранную форму:
– -формулу для расчета ЦФ,
– -формулы для расчета значений левых частей ограничений;

4. Решение задачи линейного программирования

• d) задать ЦФ (в окне "Поиск решения"):
– -целевую ячейку,
– -направление оптимизации ЦФ (максимум или минимум);
• e) ввести ограничения и граничные условия (в окне "Поиск
• решения"):
– -ячейки со значениями переменных,
– -граничные условия для допустимых значений переменных,
– -соотношения между правыми и левыми частями ограничений.
2. Решить задачу:
• a) установить параметры решения задачи (в окне "Поиск решения");
• b) запустить задачу на решение (в окне "Поиск решения");
• c) выбрать формат вывода решения (в окне "Результаты поиска решения").

5. Установка надстройки «Поиск решения»

Метод поиска решения не всегда
присутствует на вкладке «Данные». Прежде
чем использовать этот метод, его надо
добавить из дополнительных надстроек.
Для этого необходимо выбрать пункт меню
Файл. В появившемся окне нажать на
кнопку «Параметры Excel»

6. Установка надстройки «Поиск решения»

Затем выбрать строку «Надстройки» и в нижней части
появившегося окна нажать на кнопку «Перейти»:
В появившемся окне поставить галочку в строке «Поиск
решения» и нажать на кнопку ОК. В результате на
ленте Данные появится возможность вызвать метод
«Поиск решения».

7. Решение задачи линейного программирования

Формулировка задачи. Доход от продажи краски 1 равен 2 рублям, а от продажи краски 2одному рублю. Для производства красок используются два ресурса А и В. На производство
единицы краски 1 расходуется одна единица ресурса А и 3 единицы ресурса В. Для
производства единицы краски 2 расходуется 2 единицы ресурса А и единица ресурса В.
Запасы каждого ресурса равны 3 единицам. При каком производстве каждой краски будет
достигнут максимальный доход?
Для удобства запишем исходные данные задачи в следующую таблицу.
Обозначим неизвестное нам количество краски каждого вида через х1 и х2
соответственно. Тогда можно записать условие задачи в виде следующих формул:
Необходимо найти максимум выражения 2 * х1 + х2 (это суммарный доход от продажи)
при ограничениях х1 + 2 * х2 <= 3, 3 * x1 + x2 <= 3 (ограничения на запасы сырья).
Для неизвестным нам значениям переменных отводим
ячейки В3 и С3. В этих ячейках будет формироваться
решение задачи. Первоначально их необходимо
оставить пустыми, что интерпретируется в расчетах как
нулевое значение данных ячеек.

8. Решение задачи линейного программирования

В ячейку D4 вводится формула для вычисления
целевой функции задачи (суммарного дохода)
Z=2 * x1 + x2. Для задач линейного
программирования удобно использовать
математическую функцию «СУММПРОИЗВ».
Установим курсор в ячейку D4, перейдем на
вкладку «Формулы» и в группе математических
функций выберем функцию «СУММПРОИЗВ».
В окне мастера функций нажать ОК и в
появившемся окне в поле «Массив1»
ввести адреса изменяемых ячеек В3:С3.
В поле Массив2 вводятся адреса ячеек
содержащих цены на краски В4:С4.
После нажатия на кнопку ОК в ячейке
D4 появится результат вычисления
равный 0.
Использование функции
«СУММПРОИЗВ» обусловлено
удобством проведения расчетов во всех
задачах линейного программирования.

9. Решение задачи линейного программирования

Используя ту же функцию в ячейку
D7вводим формулу для вычисления
израсходованного количества
продукта А: х1 + 2 * х2, а в ячейку D8
вводим формулу для вычисления
израсходованного количества
продукта В: 3 * х1 + х2.
После ввода
формул таблица
будет выглядеть
так
2
1

10. Применение метода «Поиск решения»

Следующий шаг — применение метода поиска решения для нахождения
максимума целевой функции. Вызовем этот метод с вкладки «Данные» и начнем
заполнять поля появившегося окна.
Целевая ячейка- это ячейка со
значением целевой функции D4. Для
ввода этого значения необходимо
щелкнуть мышкой на этой ячейке. В
поле «Изменяя ячейки» вводятся
адреса ячеек, которые мы
зарезервировали под значения
неизвестных х1 и х2 (В3 и С3).Так как
наша задача- максимизировать доход,
мы устанавливаем флажок на кнопке
«максимальному значению».
Для ввода ограничений необходимо нажать
кнопку «Добавить» и ввести очередное
ограничение в появившееся окно.
Ограничения добавляются последовательно.
Для задачи ЛП необходимо ввести
дополнительное ограничение х1, х2 >= 0, так
как объем производства не может быть
отрицательным.

11. Применение метода «Поиск решения»

12. Параметры метода «Поиск решения»

Точность ограничения – точность
приближенных вычислений
Использовать автоматическое
масштабирование – для одновременного
использования очень маленьких и очень
больших величин
Показывать результаты итераций – если
вычисления занимают много времени
Игнорировать целочисленные ограничения
– используется при решении целочисленных
задач
Максимальное время – ограничение на
время счета
Число итераций – ограничение на число
итераций
Другие вкладки используются для настройки
нелинейных методов решения задач ЛП

13. Вычисление оптимального значения

После задания всех параметров необходимо нажать кнопку «Выполнить» и получить в
ячейках В3 и С3 искомое значение неизвестных, при котором достигается максимум
целевой функции при выдвинутых ограничениях.
Таким образом, максимальный доход
размером 2,4 рубля достигается при
производстве 0,6 объема краски 1 и 1,2
объема краски 2. При этом будут
полностью израсходованы запасы
ресурсов 1 и 2.
Результат вычисления можно сохранить
или вернуться к исходным данным.

14. Отчет по результатам поиска решения

15. Отчеты об устойчивости и пределах

16. Представление модели в виде таблицы

На первом занятии была рассмотрена модель производства стульев. Эта модель
была представлена в виде следующей таблицы.
Самостоятельно разобрать следующий пример построив все таблицы, применить
метод поиска решения и получить отчет о результатах

17. Формулы в таблице

После того как надстройка Поиск решения загрузится в память, на экране появится
диалоговое окно, поля которого необходимо заполнить.

18. Заполнение полей диалогового окна

В поле Установить целевую ячейку диалогового окна Поиск решения вводится адрес
ячейки, содержащей значение целевой функции. Для модели Oak Product в это поле
следует ввести D4.
Опции области Равной диалогового окна Поиск решения позволяют задать тип
оптимизации. В данном случае необходимо максимизировать значение показателя
эффективности, т.е. прибыль компании Oak Product. Для этого нужно щелкнуть на
переключателе максимальному значению.
Щелчок на кнопке минимальному значению укажет, что надо минимизировать целевую
функцию.
Можно также сделать значение целевой функции равным заданному числу, установив
переключатель значению и введя это число.

19. Заполнение полей диалогового окна

Следующее поле Изменяя ячейки позволяет указать переменные решения модели, в
данном случае это диапазон В4:С4.
Теперь необходимо задать для средства Поиск решения ограничения Щелчок на кнопке
Добавить открывает диалоговое окно Добавление ограничения, которое позволяет
вводить ограничения.

20. Ввод частей ограничений типа «меньше или равно»

Внимание! Интервал
ограничений можно
задавать только в том
случае, когда эти
ограничения расположены
компактно

21. Ввод частей ограничений типа «больше или равно»

Далее надо не забывать об условиях не отрицательности для содержимого ячеек В4 и С4
Чтобы ввести эти ограничения, сначала следует вернуться в диалоговое окно Поиск
решения из диалогового окна Добавление ограничения, щелкнув на кнопке ОК в этом
окне.
На данном этапе диалоговое окно Поиск решения для модели Oak Production должно
выглядеть так, как показано на следующем слайде.