Поиск допустимого решения

Постановка задачи на Excel

Рассмотрим задачу о размещении рекламы агентством Simpson & Son. Решим задачу методом приоритетов.

В электронной таблице мы должны отразить следующие данные:

· Количество минут рекламных роликов, размещенных на радио и телевидении;

· Стоимость показа;

· Рекламную аудиторию, бюджет;

· Отклонения от целей.

На рисунке (см. Рис. 13) приведен пример таблицы Excel для задачи размещения рекламы агентства Simpson & Son.

Рис. 13 Пример таблицы для задачи агентства Simpson & Son

Исходные данные. В ячейках B5:C8 находятся исходные данные задачи (коэффициенты левых частей ограничений) — см. Табл. 46. В ячейках F5:F8 — значения левых частей ограничений: необходимый объем рекламной аудитории, количество агентов, допустимый бюджет и максимально возможное число минут рекламы на радио.

Изменяемые ячейки (число минут показа рекламы). Введем произвольные начальные значения (план) минут показа рекламы в ячейки B11:C11.

Левые части ограничений (достигнутые значения при данном плане рекламной компании). В ячейках D5:D7 вычислены значения левых частей ограничений, так объем рекламной аудитории вычисляется по формуле

=СУММПРОИЗВ(B5:C5;$B$11:$C$11)

Аналогичные формулы в ячейках D6:D8.

Проверим, есть ли у задачи допустимые решения.

Для этого необходимо использовать процедуру «Поиск решения»:

а) Поле «Целевая ячейка» остается свободным, поскольку мы не ищем оптимального решения задачи, а лишь пытаемся найти некоторое допустимое решение.

б) В поле «Изменяя ячейки» вводим B11:C11.

в) В поле «Ограничения» указываем следующие условия:

D5>=F5

D6:D8<=F6:F8

В результате работы программы «Поиск решения» мы получаем сообщение о том, что допустимого решения, удовлетворяющего всем сформулированным ограничениям, получить не удается (см. Рис. 14).

Рис. 14 Результаты поиска допустимого решения

9.3. Целевое программирование: шаг 1.

Изменим планировку таблицы (см. Рис. 15).

Рис. 15 Таблица Шаг 1

Изменяемые ячейки. Введем дополнительные изменяемые ячейки C17:D18, соответствующие переменным отклонения. Ведем в эти ячейки произвольные начальные значения. Теперь изменяемыми ячейками являются B12:C12 и C17:D18.

Ограничения. В ячейках B17:B18 как и раньше подсчитываются левые части прежних ограничений, так в ячейке B17 формула

=СУММПРОИЗВ(B5:C5;$B$12:$C$12)

А в ячейках E17:E18 — вычисляются левые части новых ограничений с учетом переменных отклонения, так в E17 содержится формула

=B17+C17-D17

Аналогичная формула и в E18.

Добавляя условие равенства содержимого ячеек E17:E18 = G17:G18, мы обеспечиваем автоматический контроль величины отклонения от цели.

Целевая ячейка. Частной целью на первом шаге является минимизация недостатка аудитории, поэтому в ячейке B21 находится ссылка на содержимое ячейки C17, которая соответствует переменной отклонения .

Аналогично, второй частной целью является минимизация превышения бюджета, поэтому в ячейке B22 находится ссылка на ячейку D18, соответствующую переменной отклонения .

Выполним первый шаг целевого программирования и решим задачу только с первой частной целью.

Поиск решения (см. Рис. 16 )

В поле «Целевая ячейка» указываем B21, она подлежит минимизации, поскольку на первом шаге мы хотим достичь первой (главной) цели.

В поле «Изменяя ячейки» указываем B12:C12;C17:D18 (адреса второго интервала следует выделять с нажатой клавишей Ctrl).

Добавим в поле «Ограничения» следующее условие: E17:E18=G17:G18, которое обеспечивает выполнение Цели 1.

Рис. 16 Процедура «Поиск решения» на шаге 1

В результате работы процедуры «Поиск решения» мы получаем решение (см. Рис. 15, на котором и приведена таблица с оптимальным решением первого шага), которое не полностью удовлетворяет первой цели (значение в ячейке C17 равно 5), при этом вторая выполнена (значение ячейки D18 равно 0).

9.4. Целевое программирование: шаг 2.

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

Для этого внесем изменение в таблицу Шаг 1 (см. Рис. 17), которое позволит сохранить достигнутое значение первой цели: введем в ячейку D21 полученное на шаге 1 значение недостатка рекламной аудитории, равное 5.

Рис. 17 Целевое программирование. Шаг 2.

Поиск решения (см. Рис. 18).

В поле «Целевая ячейка» указываем B22, она подлежит минимизации, поскольку на втором шаге мы хотим минимизировать превышение бюджета.

Добавим в поле «Ограничениям» условие: B21=D21, которое не позволит ухудшить полученное оптимальное значение первой цели.

Рис. 18 Процедура «Поиск решения» на шаге 2

В результате работы процедуры «Поиск решения» мы получаем решение (оно приведено на Рис. 17), которое не отличается от решения, полученного на первом шаге.

Таким образом, оптимальное решение задачи о размещении рекламы агентством Simpson & Son, полученное методом приоритетов состоит в покупке 5 минут рекламы на радио и 2,5 минут рекламы на телевидении, это обеспечивает рекламную аудиторию в 40 млн. человек и требует бюджетных расходов $100 000.

Дата добавления: 2014-12-29