Создание формы для решения задачи

Для этого необходимо выполнить резервирование изменяемых ячеек, поэтому в блок ячеек В2: G7 вводится знак «1» – так резервируется место, где после решения задачи будет находиться распределение продавцов по торговым местам, обеспечивающее максимальную выручку.

Ввод исходных данных

Для ввода данных необходимо создание еще одной матрицы, но уже с содержанием заданных значений в блок ячеек B10: G15 (рис. 9).

Ввод граничных условий

Для ввода граничных условий необходимо указать объемы (кол-во) торговых точек. Для этого в ячейке А2 нужно выбрать знак ∑ на Панели инструментов, затем выделить необходимые для суммирования ячейки В2: G2 и нажать ENTER для подтверждения ввода формулы суммирования. Аналогичные действия выполнить для ячеек А3, А4, А5, А6, А7. Далее вводим условия по количеству продавцов на рабочих местах, для этого необходимо поместить курсор в ячейке В8 и выбрать знак ∑, при этом автоматически выделяется весь столбец В2: В7, и нажать ENTER для подтверждения ввода формулы суммирования столбца. Аналогичные действия выполнить для столбцов С8, D8, E8, F8, G8.

Рис. 9. Создание формы для ввода условий задачи

 

Назначение целевой функции

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

- помещаем курсор в ячейку В17 и запускаем Мастер функции (значок f_x);

- в окне Категория выбираем Математические;

- в окне Функция при помощи спинера выбрать СУММПРОИЗВ;

- нажать кнопку ОК;

- в окне СУММПРОИЗВ указать адреса массивов, элементы которых обрабатываются этой функцией.

В нашей задаче целевая функция представляет собой произведение результатов оценочной деятельности каждого продавца в каждой торговой точке (расположенных ячейках В10: G15 ) и объемов продаж для каждого продавца, назначенного на конкретное место работы (ячейки В2: G7 ). Для этого необходимо:

- в поле Массив 1 указать адреса В10: G15;

- в поле Массив 2 указать адреса В2: G7;

- нажать кнопку ОК – подтверждение окончания ввода адресов массивов.

В поле ячейки В17 появится некоторое число, равное произведению единичных назначений на оценочную деятельность каждого продавца (в данной задаче – это число 2085, рис. 10).

Рис. 10. Введены зависимости из математической модели

 

Ввод зависимостей из математической модели

Для этого необходимо выполнить следующие действия:

- выбрать Сервис – Поиск решения;

- поместить курсор в поле Установить целевую (ячейку)

- ввести адрес $В$17 (таким образом мы резервируем ячейку, куда после решения задачи помещается значение целевой функции) или поместить курсор в ячейку В17, а затем выбрать Поиск решения. При этом в поле адреса целевой ячейки будет автоматически введён адрес $В$17;

- установить направление изменения целевой функции, равное максимальному значению;

- ввести адреса изменяемых ячеек B2: G7. Для этого необходимо:

- выбрать Изменяя ячейки;

- ввести адреса $В$2: $G$7 или щелкнуть на красной стрелке рядом с этим полем, выйти в таблицу с матрицей назначений, выделить блок ячеек B2: G7, щелкнуть на красной стрелке и вернуться в блок Поиск решения. Такая последовательность действий приводит к тому, что будут введены нужные адреса.

Ввод ограничений задачи

В матрицу назначений, содержащую исходные данные по зада­че, необходимо ввести условие размещения всех продавцов по торговым точкам реализации (рис. 11).

Рис. 11. Распределение продавцов по торговым точкам

 

Для этого необходимо:

- выбрать Добавить ограничения;

- в поле Ссылка на ячейку ввести адреса $А$2: $А$7;

- в среднем поле установить знак «=». Для этого щелкнуть спинер и выбрать необходимый знак «=»;

- в поле Ограничение установить значение 1.

- для подтверждения введенного условия нажать кнопку ОК.

Далее вводится ограничение, которое реализует условие удов­летворения объемов продаж. Для этого необходимо:

- выбрать Добавить ограничения;

- в поле Ссылка на ячейку ввести адреса $А$8: $G$8;

- в поле знака выбрать при помощи спинера знак «=»;

- в поле Ограничение установить значение 1;

- нажать кнопку ОК;

- выбрать Добавить ограничения;

- в поле Ссылка на ячейку ввести адреса $В$2: $G$7;

- в поле знака выбрать при помощи спинера знак «< =»;

- в поле Ограничение установить значение 1;

- нажать кнопку ОК;

- выбрать Добавить ограничения;

- в поле Ссылка на ячейку ввести адреса $В$2: $G$7;

- в поле знака выбрать при помощи спинера знак «целое»;

- нажать кнопку ОК;

- выбрать Добавить ограничения;

- в поле Ссылка на ячейку ввести адреса $ В$2: $G$7;

- в поле знака выбрать при помощи спинера знак «> =»;

- в поле Ограничение установить значение 0;

- нажать кнопку ОК;

- после этого надо вернуться в поле Поиск решения;

- после ввода всех ограничений ввести ОК. На экране появится окно Поиск решения с введенными ограничениями (рис. 12).

Рис. 12. Ввод зависимостей из математической модели

Ввод параметров

С помощью окна Параметры можно вводить условия для реше­ния оптимизационных задач. В нашей задаче следует установить флажок Неотрицательные значения и флажок Линейная модель. Нажать кнопку ОК. Опять появится диалоговое окно Поиск решения. Далее необходимо:

- щелкнуть по кнопке Параметры;

- выбрать переключатель Линейная модель;

- выбрать переключатель Неотрицательные значения (так как назначение конкретного продавца на конкретное место работы не могут быть отрицательными);

- нажать кнопку ОК. После этого произойдет переход в поле Поиск решения;

- нажать кнопку Выполнить.

Решение задачи выполняется сразу же после ввода данных, когда на экране находится диалоговое окно Поиск решения. Нажать кноп­ку Выполнить. На экране появится диалоговое окно Результаты поиска решения (рис. 13).

В результате нами был получен оптимальный план распределения продавцов:

Рис. 13. Результаты решения

План распределения продавцов имеет следующий вид:

Продавец А распределён в торговую точку 4, его выручка составляет 83 д.ед.;

Продавец В распределён в торговую точку 2, его выручка составляет 60 д.ед;

Продавец C распределён в торговую точку 3, его выручка составляет 40 д.ед;

Продавец D распределён в торговую точку 5, его выручка составляет 70 д.ед;

Продавец E распределён в торговую точку 6, его выручка составляет 60 д.ед;

Продавец F распределён в торговую точку 1, его выручка составляет 65 д.ед.

Общая сумма выручки при таком распределении составляет 371 д.ед.

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

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

Пример 3. Задача об оптимальном использовании ограниченных ресурсов. Предприятие имеет в своем распоряжении определенное количество производственных ресурсов: трудовые, денежные средства, сырье, оборудование, производственные площади и т.п. Допустим, например, ресурсы трех видов – трудовые, сырье и оборудование имеются в количестве, соответственно, 80 чел.-дней, 480 кг, 130 станко-часов. Предприятие может выпускать изделия четырех видов. Информация о количестве единиц каждого ресурса, необходимых для производства одного изделия каждого вида (о нормах расхода производственных ресурсов), и доходах, получаемых предприятием от реализации, единицы каждого вида продукции, приведена в таблице 7.

Таблица 7

Данные условия задачи

Ресурсы	Нормы расхода ресурсов на единицу иэдвлия	Наличие ресурсов
изделие А	изделие В	изделие С	изделие D
Труд
Сырье
Оборудование
Цена (тыс. руб.)

Требуется составить такой план выпуска продукции, при котором будет получен максимальный доход от реализации продукции (сбыт всей выпущенной продукции обеспечен).

⇐ Предыдущая12345678Следующая ⇒

Поделиться: