Решение задачи фирмы в табличном процессоре Microsoft Excel
(слайд)
Процесс решения оптимизационной задачи в Excel включает следующие этапы:
• подготовка исходных данных,
• формирование модели,
• настройка параметров расчетов,
• нахождение оптимального решения,
• сохранение полученных результатов.
Размещение информации на рабочем листе.
Для проведения расчетов необходимо определить так называемые изменяемые ячейки, которые отводятся под переменные задачи.
В одну из ячеек рабочего листа заносится формула, вычисляющая значение целевой функции. Эта ячейка называется целевой.
В специально отведенные ячейки вводятся формулы, которые задают вычисление функций, стоящих в левой части ограничений задачи.
Разместим на рабочем листе исходную информацию к задаче из табл. 1.
(слайд)
Диапазон B10: D10 отведем под изменяемые ячейки. В нем будут находиться значения переменных.
(слайд)
Диапазон Е5:Е7 отведем под левые части ресурсных ограничений. Он будет содержать формулы, подсчитывающие расход ресурсов.
(слайд)
Ячейку В11 сделаем целевой.
В ней будет находиться формула, задающая правило вычисления целевой функции.
Рядом с ячейкой В11, а также с диапазонами Е5:Е7 и B10: D10 поместим пояснительные надписи (рис. 1).
Рисунок 1.- Исходные данные для расчетов
Создание имен.Для более наглядного представления исходных данных и результатов введенная информация обычно снабжается поясняющими надписями, а целевой ячейке, изменяемым ячейкам и диапазонам ячеек с формулами даются имена.
Дадим имена следующим диапазонам и ячейкам, которые будут использованы при создании модели расчета оптимального плана:
(слайд)
• диапазону В10:Е10 (переменные); -Выпуск
• диапазону Е5:Е7 (левые части ресурсных ограничений);-Расход
• диапазону G5:G7 (правые части ресурсных ограничений);-Наличие
• ячейке В11 (целевая функция).Прибыль
Замечание.Создание имен основных диапазонов не является обязательным шагом при подготовке исходных данных. Однако использование имен делает работу с моделью более удобной и позволяет при ее формировании избежать ошибок, связанных с неправильным заданием ее параметров.
Для создания имени диапазона проще всего использовать расположенную рядом с ним пояснительную надпись.
Так, чтобы создать имя диапазона B10:D10, нужно выделить его, а затем нажать комбинацию клавиш Ctrl+F3или выбрать пункты меню Вставка, Имяи Присвоить.
В нем Excel предлагает присвоить этому диапазону имя Выпуск, который содержится в ячейке, расположенной слева от него. После нажатия клавиши Enterили щелчка по кнопке ОК операция присвоения имени будет выполнена.
(слайд)
По той же схеме дадим имена диапазонам Е5:Е7 и G5:G7. Первый диапазон получит имя Расход, а второй - имя Наличие.
Чтобы дать имя Прибыль целевой ячейке В11, нужно активизировать ее, щелкнув по ней мышью, а потом нажать комбинацию клавиш Ctrl+ F3и кнопку ОК.
Отметим, что в окне Присвоение имениможно удалить или изменить уже имеющиеся и создать новые имена.
Ввод формул целевой функции и ограничений. Так как целевая функция и все соотношения в модели линейны, для ввода их формул удобно использовать функцию СУММПРОИЗВ. Ее аргументами являются числовые массивы, имеющие одинаковые размеры, а значением - сумма попарных произведений их элементов.
(слайд)
Для ввода этой функции в целевую ячейку следует выполнить следующие действия:
1.Щелчком мыши по целевой ячейке сделать ее активной.
2.Выбрать команду Функция из меню Вставка или щелкнуть по кнопке Мастерфункций fâ на панели инструментов. На экране появится первое окно мастера функций (рис. 3).
3.В списке категорий, расположенном в левом окне, выбрать элемент
Математические.Затем найти в появившемся в правом окне списке функцию СУММПРОИЗВ и щелкнуть по кнопке ОК.
Рисунок3- Выбор функции СУММПРОИЗВ
На экране появится второе диалоговое окно (рис. 24). В поле Массив1,отведенное под адреса ячеек с элементами первого массива, введем B8:D8 (удельную прибыль изделий).
Для этого лучше не набирать адреса ячеек с помощью клавиатуры, а указать их протаскиванием мыши по ячейкам диапазона.
Рисунок4- Задание аргументов функции СУММПРОИЗВ
В поле Массив2нужно ввести ссылку на диапазон B10:D10, содержащий объемы выпуска изделий. Так как он имеет имя, то проще не указывать его местоположение при помощи мыши, а ввести в поле имя диапазона.
Для этого следует нажать клавишу F3. Откроется окно Вставка имени,содержащее список имен ячеек и диапазонов, доступных на рабочем листе (рис. 5).
Рисунок5- Диалоговое окно Вставка имени
Щелчком мыши выделим нужное имя Выпуск, а затем щелкнем по кнопке ОК. Это имя будет вставлено в поле Массив 2.
После завершения ввода в ячейке Прибыль (В 11) будет содержаться формула
=СУММПРОИЗВ(В8:D8; Выпуск),
определяющая целевую функцию. В самой ячейке на рабочем листе появится число 0 - значение целевой функции при нулевых значениях переменных (пустые значения в ячейках Excel при вычислениях значений формул интерпретирует как нулевые).
Затем следует ввести формулы, описывающие расход ресурсов при выполнении планового задания. Требуемые для этого объемы ресурсов задаются функциями, расположенными в левой части ресурсных ограничений (2)-(4).
Для соответствующих формул был отведен диапазон ячеек Е5:Е7. Сначала введем в ячейку Е5 формулу, задающую расход сырья. Для этого снова используем функцию СУММПРОИЗВ.
В качестве первого массива в окне мастера функций выберем диапазон B5:D5 (нормы затрат сырья), а в качестве второго массива - диапазон Выпуск (объемы выпуска). После завершения ввода формула в ячейке Е5 имеет такой вид:
=СУММПРОИЗВ(В5:D5; Выпуск).
Теперь нужно скопировать эту формулу в ячейки Е6 и Е7. Для этого проще всего использовать маркер заполнения в правом нижнем углу ячейки Е5.
Формирование модели для расчетов
После завершения ввода формул, задающих целевую функцию и ограничения задачи, нужно сформировать модель для расчета решения задачи.
Для этого следует щелчком мыши активизировать целевую ячейку и выбрать пункт Поиск решения2в меню Сервис.На экране появится
Рисунок6- Создание модели в окне Поиск решения
диалоговое окно Поиск решения(рис. 2.6), в котором нужно задать следующие параметры: целевую ячейку, тип экстремума, изменяемые ячейки и ограничения решаемой задачи.
Указание целевой ячейки и типа экстремума. В поле, отведенном под целевую ячейку, должна находиться ссылка на нее в виде адреса или имени.
Поскольку перед вызовом окна Поиск решенияцелевая ячейка была активизирована, ее имя уже содержится в этом поле.
Нужно также указать тип экстремума в решаемой задаче.
Изначально (по умолчанию) в окне установлено решение задачи на максимум.
Так как в нашей задаче также ищется максимум целевой функции, эту установку следует оставить без изменения.
Задание изменяемых ячеек.В поле раздела Изменяя ячейкидолжна содержаться ссылка на диапазон изменяемых ячеек.
Проще всего щелчком мыши активизировать это поле, а затем с помощью клавиши F3 вызвать список имен и выбрать в этом списке имя диапазона Выпуск.
Можно также ввести ссылку на этот диапазон путем его указания мышью на рабочем листе.
Задание ограничений. Для задания ограничений следует щелкнуть по кнопке Добавить.Появится диалоговое окно Добавление ограничения,содержащее три поля (рис. 7). В левое поле Ссылка на ячейкуследует ввести ссылку на ячейку с формулой, задающей левую часть ограничения.
В центральное поле вводится оператор сравнения, выбираемый из раскрывающегося списка.
В этом списке содержатся операторы сравнения =, >=, <=, оператор цел, определяющий, что значениями переменной могут быть только целые числа, а также оператор двоим, предназначенный для задания двоичных переменных, которые могут принимать лишь одно из двух значений: 0 или 1.
Рисунок 7-Окно Добавление ограничения
Правая часть ограничения задается в правом поле Ограничениев виде числа или ссылки на ячейку рабочего листа. Задав ограничение, можно либо щелкнуть по кнопке ОК и вернуться в окно Поиск решения,либо щелкнуть по кнопке Добавитьи задать новое ограничение.
Excel позволяет задать нескольких однотипных ограничений в виде одного «интервального» ограничения.
Так как все ресурсные ограничения (2)-( 4) имеют один тип «меньше или равно», их можно задать все сразу, а не вводить по отдельности.
Для этого нужно в окне Добавление ограниченияслева ввести ссылку на диапазон с формулами левых частей ограничений, вызвав нажатием клавиши F3 список имен и выбрав из него имя Расход.
Затем в центральном поле следует выбрать из списка тип ограничений <=, а в правом поле Ограничениеввести ссылку на диапазон, содержащий правые части ограничений (см. рис. 7).
Для этого также лучше использовать его имя Наличие. В результате интервальное ограничение будет иметь следующий вид:
Расход <= Наличие.
Оно равносильно следующим трем ограничениям:
$E$5<=$G$5; $E$6<=$G$6, $E$7 <= $G$7.
Задача.1 кроме ресурсных ограничений содержит условие неотрицательности переменных. Его проще всего задать в окне Параметры поиска решения.
В любое введенное ограничение можно внести изменения. Для этого следует сначала щелкнуть по нему, а затем - по кнопке Изменить.
Появится окно Изменение ограничения,по структуре идентичное окну Добавление ограничения,в котором будет находиться изменяемое ограничение.
После внесения изменений нужно щелкнуть по кнопке ОК.
Настройка параметров расчетов
После задания всех ограничений нужно указать Excel, что введенная задача является задачей ЛП.
Тогда в качестве процедуры решения задачи будет выбран симплекс-метод, а создаваемые отчеты будут содержать более полную информацию о решении.
В противном случае Excel использует одну из процедур решения задачи нелинейного программирования.
Для этого нужно щелкнуть по кнопке Параметры.Появится окно Параметры поиска решения(рис..8), которое содержит текущие значения основных параметров, используемых Excel при поиске решения.
В этом окне следует установить щелчком мыши «галочку» в поле Линейная модель.
Рисунок 8-. Окно Параметры поиска решения
Если ранее в разделе Ограниченияне было введено условие неотрицательности переменных, то его можно задать здесь, установив «галочку» в поле Неотрицательные значения.
Если решается задача, в которой значения целевой функции очень сильно отличаются от значений переменных, то следует включить опцию Автоматическое масштабирование.Значения остальных параметров изменять не нужно.
Нахождение оптимального решения
Для нахождения решения задачи следует вернуться в окно Поиск решенияи нажать кнопку Выполнить.
В зависимости от типа задачи Excel выбирает подходящую процедуру поиска решения (ППР). Все используемые ППР имеют итеративный характер.
Результаты очередной итерации сравниваются с результатами, полученными на предыдущем шаге.
Процесс вычислений прекращается, когда выполнено одно из условий завершения работы процедуры:
• Решение найдено.Все ограничения и условия оптимальности выполнены (Сообщение об успешном решении задачи)
• ППР не может найти подходящего решение ( Сообщение при несовместной системе ограничений задачи);
• Значения целевой ячейки не сходятся.(Сообщение при неограниченности ЦФ в требуемом направлении.)
В нашей задаче оптимальное решение существует; и после завершения работы ППР на рабочем листе в отведенных ячейках появляются оптимальный план выпуска и его характеристики: величина прибыли и затраты ресурсов (рис..9).
Результаты решения задачи таковы: максимальную прибыль, равную 2120 руб. (В11 = 2120), можно получить, выпустив 56 единиц изделия 1 (В 10 = 56) и 18 единиц изделия 2 (С10 = 18), т. е. в оптимальный план вошли эти виды изделий.
Изделие 3 выпускать не нужно (D10 = 0). Будет израсходовано 388 кг сырья (Е5 = 388), т.е. его остаток составит 12 кг.
Остальные два вида ресурсов (оборудование и труд) будут израсходованы полностью.
Рисунок 9- Результаты решения задачи фирмы
Сведения об оптимальных оценках ресурсов содержатся в отчете по устойчивости. Его описание приводится в следующем пункте.
Сохранение полученных результатов
После завершения работы ППР на экран выводится диалоговое окно Результаты поиска решения(рис.10).
В нем содержится сообщение о том, что оптимальное решение найдено, и предлагается сохранить его на рабочем листе. Если принять это предложение (нажать кнопку ОК), то в изменяемых ячейках будут сохранены оптимальные значения переменных задачи.
Также будут сохранены и значения в ячейках, которые содержат формулы, зависящие от изменяемых ячеек.
Если же будет выбран пункт Восстановить исходные значенияили нажата кнопка Отмена,то будут восстановлены исходные значения изменяемых, а также зависящих от них ячеек.
Excel предлагает создать три типа отчетов: по результатам, устойчивости и пределам.
Последний отчет малоинформативен, поэтому обычно сохраняют только два отчета: по результатам и устойчивости.
Для создания отчета следует выделить его название в списке Тип отчета,а затем нажать кнопку ОК.
Чтобы выделить сразу несколько отчетов, нужно щелкать мышью по их названиям, держа нажатой клавишу Ctrl.
Каждый отчет помещается на отдельный лист и получает имя, содержащее название типа отчета и номер (количество созданных отчетов данного типа).
Можно также сохранить полученное решение в виде сценария. Эта возможность удобна при проведении серии расчетов, так как позволяет быстро создать итоговую таблицу, содержащую сведения обо всех решениях.
Отчет по результатам содержит информацию о решении задачи. Он состоит из трех таблиц (рис. 11).
Первая таблица содержит сведения об оптимальном значении целевой функции (прибыли фирмы), а вторая - о начальных и оптимальных значениях переменных (объемах выпуска).
В третьей таблице приводится информация об ограничениях в оптимальном плане. В столбце Значениесодержатся сведения о затратах ресурсов (значении левой части ограничений).
В столбце Разницаданы значения остатков каждого ресурса, которые равны разности между его наличием (значением правой части ограничения) и затратами.
Столбец Статуссодержит информацию о состоянии ограничения. Если ресурс используется в оптимальном плане полностью, то соответствующее ограничение является связанным; если же ресурс недоиспользуется, то ограничение - несвязанное.
Таким образом, из отчета видно, что в оптимальном плане оборудование и труд используются полностью, но остается излишек сырья, равный 12кг. Этот факт уже был установлен ранее.
