Типовые задачи оптимизации и их решение средствами Excel

Оформить таблицу рамками  можно и с помощью кнопки Линии рамки панели инструментов Форматирование[12].

2.2. Подбор параметров

 

Большинство функций, которые  используются при моделировании, могут в данный момент отсутствовать в меню. При необходимости, отсутствующие функции можно загрузить. Для этого следует выбрать пункты меню Сервис, Надстройки… - открывается диалоговое окно «Надстройки». В открывшемся окне установить флажок у нужной программы и щёлкнуть на кнопке ОК – нужная надстройка загружается и в меню появляется соответствующий пункт.

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

Рассмотрим действие функции подбора параметров на примере таблицы, изображённой на рис. 2. В этой таблице представлена калькуляция доходов и расходов некоторого предприятия, выпускающего некоторую условную продукцию[12].

Рис. 2. Исходная таблица

Исходными данными в  этой таблице являются: количество экземпляров, цена одного экземпляра продукции, себестоимость одного экземпляра, % накладных расходов, затраты на зарплату и затраты на рекламу. Остальные показатели рассчитываются по следующим соотношениям:

Доход = Цена продукции*Количество экземпляров;

Себестоимость реализованной продукции = Себестоимость продукции*Количество экземпляров;

Валовая прибыль = Доход – Себестоимость  реализованной продукции;

Накладные расходы = % накладных расходов * Доход;

Валовые издержки = Затраты на зарплату + Затраты на рекламу + Накладные расходы;

Прибыль от продажи = Валовая  прибыль – Валовые издержки.

Пусть требуется  увеличить прибыль от продажи  до 50000 р. Решить эту задачу можно различными способами: увеличить количество экземпляров; уменьшить валовые издержки за счёт уменьшения % накладных расходов, затрат на зарплату или рекламу; снижения себестоимости экземпляра продукции; увеличения цены продукции.

Посмотрим, как  это можно сделать за счёт увеличения цены продукции. Выполнить эту операцию можно в таком порядке:

1. Выбрать пункты меню Сервис, Подбор параметра – открывается диалоговое окно «Подбор параметра» (рис. 3.).
2. В поле Установить в ячейке: указывается абсолютный адрес целевой ячейки. В данном случае - $B$14. Ввести этот адрес можно с клавиатуры, либо щелчком мыши в пределах соответствующей ячейки.

Рис. 3. Диалоговое окно Подбор параметра

3. Перевести курсор в поле Значение и ввести требуемую величину (например, 50000).
4. Перевести курсор в поле Изменяя значение ячейки: и ввести туда абсолютный адрес изменяемой ячейки. В данном случае $B$16.
5. Щёлкнуть на кнопке ОК – на экран выводится результат подбора (рис. 4.).

Как видно из приведенного примера, чтобы увеличить прибыль  от продажи до 50000 р., нужно поднять цену одного экземпляра продукции до 6,86 р.

Рис. 4. Таблица и диалоговое окно Результат подбора параметра

В некоторых случаях  задача решается очень медленно, выполняется  много шагов итерации. Можно в  этом случае щелчком мыши на кнопке Пауза прерывать решение задачи, контролировать промежуточные результаты, а затем, если нужно, продолжать решение щелчком мыши на кнопке Шаг[15].

2.3. Поиск решений

Табличный процессор Excel позволяет решать довольно сложные задачи со многими неизвестными и ограничениями. Для этого применяется математический метод линейной оптимизации. В процессоре такие задачи решает специальное средство – Поиск решения. Осваивать это будем на примере следующей задачи.

Некоторое предприятие  производит некоторую условную продукцию (Товар 1, Товар 2, …, Товар 6). Все необходимые данные сведены в таблицу (рис. 5).

В этой таблице:

Всего издержки = Издержки * Штук;

Прибыль = Всего издержки * % прибыли;

Объём продаж = Всего издержки + Прибыль.

Исходными данными являются: Издержки (издержки на производство единицы соответствующего товара), Штук и % прибыли.

В строке Суммы выводятся суммарные данные по соответствующим столбцам.

Перед предприятием стоит задача организовать производственный процесс так, чтобы не допустить превышения определённой суммы издержек (например, 4,5 млн.). Кроме того, ограничивается суммарное количество товара (например, 35000).

Нужно определить количество единиц по каждому виду товаров, при которых соблюдаются оговоренные выше условия.

Рис. 5. Таблица Планирование загрузки оборудования

Чтобы решить эту задачу, нужно выбрать пункты меню Сервис, Поиск решения – открывается диалоговое окно «Поиск решения» (рис. 6).

В поле Установить целевую следует ввести абсолютный адрес целевой ячейки (в данном случае - $D$11). В области Равной: активизировать переключатель значению: и в поле справа от него ввести требуемое значение (в данном случае – число 4500000).

В области Изменяя ячейки: в данном случае нужно ввести абсолютный адрес блока ячеек $C$4:$C$9.

Рис. 6. Диалоговое окно Поиск решения

В поле Ограничения: следует задать некоторые условия. В данном случае такими ограничениями являются максимальное суммарное значение количества выпускаемого товара, а также то, что значения количества видов выпускаемого товара могут быть только целыми значениями. Чтобы задать эти ограничения, следует щёлкнуть на кнопке Добавить – открывается диалоговое окно «Добавление ограничения» (рис.7).

Рис. 7. Диалоговое окно Добавление ограничений

Первым является ограничение  на максимально допустимое суммарное количество товаров – не более 35000. Поэтому в поле Ссылка на ячейку: следует ввести абсолютный адрес $C$11, выбрать оператор <=, в поле Ограничение: ввести значение 35000. Затем щёлкнуть на кнопке Добавить и ввести второе ограничение (адрес блока ячеек $C$4:$C$9, в списке операторов – целое). После этого щёлкнуть на кнопке ОК – возвращается окно «Поиск решения». Введённые ограничения записаны в поле Ограничения:.

Затем щелчком на кнопке Параметры следует открыть диалоговое окно «Параметры поиска решения» (рис. 8) и в нём задать нужные параметры (для данной задачи, например, следует ввести величину относительной погрешности, равную 0,001) и щёлкнуть на кнопке ОК – возвращается окно «Поиск решения»[4].

Рис. 8. Диалоговое окно Параметры поиска решения

Чтобы запустить  решение задачи, следует щёлкнуть на кнопке Выполнить – система начинает поиск решения. После того, как система найдёт решение, на экран выводится диалоговое окно «Результаты поиска решения», в котором содержится сообщение о результате решения, а в таблице в соответствующих ячейках появляются результаты решения (рис. 9).

В диалоговом окне предлагается Сохранить найденное решение или Восстановить исходные значения. В списке Тип отчета можно задать, а затем сохранить отчёт. Отчёт создаётся на отдельном листе. Пример такого отчёта для типа Результаты приведен на рис. 10 и рис. 11.

Рис. 9. Таблица Планирование загрузки оборудования с найденным решением

Рис. 10. Отчёт (начало отчёта)

В области Изменяя ячейки: в данном случае нужно ввести абсолютный адрес блока ячеек $C$4:$C$9.

В столбце Штук отображается оптимальное количество товара каждого вида, которое следует производить при заданных условиях.

Рис. 11. Отчёт (продолжение отчёта)

В отчёте имеется область  заголовка, в котором отображены наименование отчёта, к какому листу рабочей книги он относится, а также дата и время создания отчёта.

В области Целевая ячейка (Значение) отображаются абсолютный адрес целевой ячейки, её имя, исходное значение и результат решения. Подобные сведения отображаются в области Изменяемые ячейки (исходные значения и результаты решения по типам товаров).

В области Ограничения отображаются ограничения, которые были заданы при решении задачи, результирующие значения, статус и разница[11].

 

2.4. ДИСПЕТЧЕР СЦЕНАРИЕВ  «ЧТО – ЕСЛИ»

2.4.1. Создание  сценария.

Создание сценариев  происходит следующим образом:

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

 

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

 

• Ввести Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога ввести изменяемые ячейки. Когда этот сценарий будет готов, ввести следующий.
• Нажать кнопку Добавить, чтобы создать второй сценарий. Ввести название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.
• Закрыть окно диалога Диспетчер сценариев кнопкой Закрыть.
• Просмотр сценария Excel сохраняет сценарии вместе с листом текущей книги, и просмотр их командой Сервис /Сценарии возможен только при открытии данного листа. Просмотр сценария выполняется следующим образом:
• Выполнить команду Сервис/Сценарии. Открывается окно диалога:
• Выбрать из списка сценарий для просмотра.
• Нажать кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.
• Выбрать из списка другие сценарии и воспользоваться кнопкой Вывести для сравнения результатов моделей «что – если». После завершения нажать кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа[3].

2.4.2. Создание отчетов по сценарию

Сравнивать различные сценарии можно, переходя от сценария к сценарию с помощью кнопки показать в окне диалога Диспетчер сценариев, но, иногда, возникает необходимость в создании отчета с обобщенной информацией о различных сценариях листа. Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги. Создание отчета по сценарию происходит следующим образом:

• Выполнить команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.
• Нажать кнопку Отчет. Открывается окно диалога Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.

 

3. Примеры решения задач оптимизации

3.1. Транспортная  задача

Некоторая фирма имеет 4 фабрики и 5 центров распределения её товаров. Фабрики располагаются в четырёх разных городах. Условно назовём их соответственно Фабрика 1, Фабрика 2, Фабрика 3, Фабрика 4. Их производственные возможности составляют соответственно 200, 150, 225 и 175 единиц продукции ежедневно. Распределительные центры располагаются в пяти городах. Условно назовём их Центр 1, Центр 2, Цунтр3, Центр 4, Центр5. Потребности их соответственно составляют 100, 200, 50, 250 и 150 единиц продукции ежедневно. Хранение на фабрике не поставленной в центр распределения единицы продукции обходится в 3,75 грн в день, а штраф за просрочку поставки заказанной потребителем в центре распределения единицы продукции, но там не находящейся, равен 12,5 грн в день. Стоимость перевозки единицы продукции с фабрик в пункты распределения приведены в таблице 1.

 

Таблица 1

Транспортные расходы

	Центр 1	Центр 2	Центр 3	Центр 4	Центр 5	Производство
Фабрика 1	1,5	2	1,75	2,25	2,25	200
Фабрика 2	2,5	2	1,75	1	1,5	150
Фабрика 3	2	1,5	1,5	1,75	1,75	225
Фабрика 4	2	0,5	1,75	1,75	1,75	175
Потребность	100	200	50	250	150

 

Необходимо так спланировать перевозки, чтобы минимизировать суммарные транспортные расходы.

Данная модель сбалансирована, т. е. суммарный объём произведенной продукции (200+150+225+175=750) равен суммарному объёму потребностей в ней (100+200+50+250+150=750). Поэтому здесь не нужно учитывать издержки, связанные как со складированием, так и с недопоставками продукции. В противном случае в модель следует ввести: