Выполнение задания

Использование Excel для оптимизации

В состав Excel входит мощное инструментальное средство – Решатель. С помощью этого средства можно решать задачи нелинейного программирования. В частных случаях с помощью Решателя может быть получено решение функционального уравнения, системы линейных уравнений, найден максимум или минимум функции нескольких переменных.

Математическая формулировка

Задачи, которые могут быть решены с помощью Решателя, в общей постановке формулируются так.

Найти значения переменных x1, x2, …, xn, такие, что целевая функция f(x1, x2, …, xn) примет заданное значение, или минимальное значение, или максимальное значение. При этом могут быть заданы ограничения вида g(x1, x2, …, xn), принимающие заданные значения, или значения <= заданных, или значения >= заданных.

Искомые переменные – ячейки рабочего листа Excel называются регулируемыми ячейками. На регулируемые ячейки можно наложить дополнительные ограничения, например положительности или целочисленности значений.

Решатель может быть применен и для решения оптимизационных задач. Для этого следует пользоваться командой меню Сервис, Поиск решения.

Выполнение задания

Продемонстрируем эту возможность на примере, описанном в журнале «Компьютер Пресс» (№ 7, 1997) в статье В. Очкова «Как я продавал программу (компьютерный этюд)», в которой автор рассказал о применении Решателя для оптимизации своего заработка. Суть задачи состоит в том, что автор продал некоторую программу лакокрасочному предприятию за 14 млн. руб. Наличными деньгами предприятие не располагало, но было готово расплатиться в пределах этой суммы своей продукцией – краской. Краска выпускалась в двух видах тары – больших и малых банках (барабанах), емкость которых соответственно составляла 55 и 15 л, а стоимость пустых барабанов – 30 и 24 тыс. руб. Литр краски стоит 14600 руб. Автор статьи заинтересован в том, чтобы, не выходя за пределы договорной суммы, получить от лакокрасочников как можно больше краски. При этом имеется возможность лишь указать количество больших и малых барабанов с краской, но нельзя взять краску в разлив. Это типичная оптимизационная задача. Сначала следует убедиться, что количество полученной краски действительно зависит от принятого решения относительно количества барабанов.

Для облегчения выполнения расчета объема полученной краски в зависимости от количества взятых больших и малых барабанов следует применить рабочий лист Excel.

1. Создайте на пользовательском диске d свою рабочую папку.

2. Загрузите Excel.

3. Сохраните созданный файл под именем Оптимизация.xls в своей рабочей папке.

4. На листе Лист1 создайте таблицу, соответствующую рис. 10.1. При этом в ячейки B4:B7 и B10:B12 запишите очевидные расчетные формулы.

Рис. 10.1. Подготовка таблицы Excel для автоматизации выполнения расчетов

5. Сначала подсчитайте, сколько краски можно получить, если всю ее взять в малых барабанах. Вот понятная формула, которая позволяет прикинуть количество малых барабанов, которое можно получить в пределах договорной суммы: 14000000/243000 = 57,6. Итак, можно получить 57 малых барабанов.

6. Введите в ячейке B8 Вашей электронной таблицы значение 57 и нажмите на клавишу Enter. Если в веденных формулах Вы не допустили ошибок, то Вы увидите, что при таком решении всего будет получено 855 л краски, а у покупателя программы останутся не выбранными 149000 руб.

7. Заслуживает внимания другое решение – взять краску в больших барабанах, а остаток выбрать малыми барабанами. Количество больших барабанов, которое можно получить, очевидно, равно 14000000/833000 = 16,8.

8. Введите в ячейке B8 Вашей электронной таблицы значение 0, а в ячейке B9 значение 16. При этом Вы увидите, что будет получено
880 л краски, а остаток денег составит 672000 руб. Последнее решение по количеству полученной краски уже лучше, чем предыдущее. Кроме того, за счет остатка денег можно взять еще некоторое количество малых барабанов краски, которое равно 672000/243000 = 2,8.

9. Введите в ячейке B8 значение 2. Окончательный результат принятого решения позволяет получить 910 л краски, а остаток денег составит 186000 руб. Итак, во втором варианте будет получено на 55 л краски больше, чем в первом варианте. Возникает естественный вопрос: существует ли решение, при котором можно получить еще больше краски? Как Вы установите дальше, такое решение действительно существует. Для решения этой задачи поиска максимума полученной краски можно применить Решатель. В электронной таблице ячейки В8 и B9 будут играть роль регулируемых ячеек, а ячейка B10 – это ячейка с целевой функцией.

10. Проверьте наличие в меню Сервис команды Поиск решения. Если такой команды в этом пункте нет, то ее требуется установить. Для этого выполните команду Сервис, Надстройки, в появившемся окне Надстройки в списке надстроек установите флажок напротив строки Поиск решения и щелкните на кнопке ОК.

11. Выделите ячейку B10 с целевой функцией и выполните команду меню Сервис, Поиск решения. Появится окно Поиск решения
(рис. 10.2), в поле Установить целевую которого уже должна быть абсолютная ссылка на ячейку B10. Если же этой ссылки там не оказалось, то ее следует туда поместить. Для этого надо щелкнуть в поле Установить целевую, чтобы установить там курсор. Затем нужно щелкнуть на ячейке B10, в которой содержится целевая функция.

12. Проверьте, чтобы был включен переключатель, показывающий, что нужно найти максимум целевой функции.

13. Поместите в поле Изменяя ячейки диапазон регулируемых ячеек. Для этого поместите курсор в этом поле и выполните буксировку мыши при нажатой левой кнопке по регулируемым ячейкам (в рассматриваемом примере – это ячейки B8 и B9).

14. Добавьте ограничения, которые имеют место в рассматриваемом примере. Их два. Первое состоит в том, что сумма истраченных денег не должна превышать 14000000 руб. Щелкните на кнопке Добавить. В появившемся окне Добавление ограничения (рис. 10.3) в поле Ссылка на ячейку поместите ссылку на ячейку B11, в которой записана формула вычисления размера истраченной суммы.

Рис. 10.3. Добавление ограничения на сумму истраченных денег Рис. 10.2. Окно Поиск решения

15. В поле Ограничение поместите ссылку на ячейку B2, в которой указана сумма договора.

16. Выберите нужный знак отношения между полями Ссылка на ячейку и Ограничение. В результате этих действий содержание полей окна Добавление ограничения должно соответствовать рис. 10.3.

17. Щелкните на кнопке Добавить, чтобы учесть введенное ограничение и вывести новое окно для ввода второго ограничения, которое состоит в том, что количество малых барабанов (ячейка B8) и количество больших барабанов (ячейка B9) могут принимать только целочисленные значения.

18. В новом окне Добавление ограничения введите в поле Ссылка на ячейку диапазон B8:B9.

19. В поле, расположенном справа от поля Ссылка на ячейку, раскройте список значений и выберите значение цел. Окно Добавление ограничения должно выглядеть, как показывает рис. 10.4.

Рис. 10.4. Учет целочисленности количества барабанов

20. Щелчком на кнопке ОК закончите ввод ограничений. После этого содержание полей окна Поиск решения должно соответствовать рис. 10.2.

21. По умолчанию поиск экстремума целевой функции выполняется с допустимой погрешностью 5% (обычная инженерная погрешность). Эта погрешность для рассматриваемого примера слишком велика, так как соответствует 910·0,05=45,5 л краски, что намного больше емкости малого барабана. Погрешность поиска максимума в рассматриваемом примере не должна быть больше емкости малого барабана, которая составляет 15/910·100=1,65 % общего объема краски.

22. Для повышения требуемой точности поиска решения щелкните на кнопке Параметры окна Поиск решения.

23. В появившемся окне Параметры поиска решения (рис. 10.5) в поле Допустимое отклонение замените значение 5 на 1,5.

Рис. 10.5. Настройка параметров поиска решения

24. Установите в этом же окне флажок Неотрицательные значения, указав тем самым, что искомые количества барабанов краски не могут принимать отрицательные значения.

25. Закройте окно Параметры поиска решения щелчком на кнопке ОК. Подготовка к поиску решения закончена.

26. Чтобы начать поиск решения, щелкните на кнопке Выполнить окна Поиск решения. После окончания процесса поиска появится окно Результаты поиска.

27. В окне Результаты поиска включите переключатель Сохранить найденное решение и закройте окно щелчком на кнопке ОК.

28. Обратите внимание на значения регулируемых ячеек (рис. 10.6), которые они приобрели после окончания поиска максимума целевой функции. Проанализируйте полученное решение. Теперь получено 915 л краски, то есть на 5 л больше, чем давал предыдущий вариант решения.

Рис. 10.6. Результат поиска максимума объема полученной краски

Итак, наибольшее количество краски будет получено, если взять 15 больших и 6 малых барабанов с краской. И при этом у заказчика будет оставлено 47000 руб. И самое главное, теперь известно, что это лучшее решение.

29. Покажите преподавателю результаты выполненной работы.

30. Решите задачу минимизации суммы денег, оставленных у заказчика (ответ: 11000 руб, если взять 6 больших и 37 малых барабанов).

31. Покажите преподавателю полученное решение.

32. Закройте Excel.

33. Удалите свою рабочую папку.