Решение с использованием MS EXCEL

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

Предприятие выпускает изделия различного вида. Для изготовления этих изделий используются ресурсы различного типа (технические, денежные, людские и т.д.). Известны нормы расхода ресурсов – количество ресурса, необходимое для производства одного изделия, и прибыль, получаемая от реализации изделий каждого вида. Количество ресурсов ограничено имеющимися запасами. Необходимо составить оптимальный план производства продукции, т.е. определить, какое количество изделий каждого вида следует производить, чтобы прибыль от реализации изделий была максимальна с учетом ограничений.

Пример решения задачи

Дано: Предприятие выпускает четыре вида товаров, используя при этом три вида ресурсов (трудовые, сырье и финансы). Нормы расхода ресурсов на каждый вид товара заданы в таблице:

Известна прибыль от реализации товарной единицы каждого вида (руб.):

Предприятие располагает ограниченным количеством ресурсов каждого вида:

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

Решение с использованием MS EXCEL

1. Откроем новый рабочий лист и внесем на него исходные данные так, как это показано на рис. 1.

Рис.1

2. Подготовим таблицу для решения задачи, зададим целевую функцию и ограничения (см. рис.2).

· В диапазоне (B10:E10) будет рассчитано количество производимых изделий (оптимальный план производства, который нам необходимо составить). До начала расчетов в эти ячейки записываем значения 0.

· В ячейку С11 записываем формулу (1.1) для вычисления значения общей прибыли от реализации всех изделий. Эта формула может быть записана в следующем виде:

= B7*B10+C7*C10+D7*D10+E7*E10,

однако удобнее воспользоваться встроенной функцией СУММПРОИЗВ («сумма произведений»), которая позволяет перемножить последовательность чисел, взятых из различных рядов данных, а затем сложить произведения:

=СУММПРОИЗВ(B7:E7; B10:E10).

При вводе данных для этой функции перемножаемые диапазоны можно указать прямо в таблице (рис.3).

· В строках с 13 по 15 выпишем ограничения (1.2). В ячейках B13-B15 рассчитаем количество истраченных ресурсов. Так, в ячейку В13 занесем формулу

=СУММПРОИЗВ(B4:E4; B10:E10)

(количество ресурса 1, истраченного для производства всех товаров)

Аналогично, в ячейке В14 рассчитаем количество истраченного ресурса 2:

=СУММПРОИЗВ(B5:E5; B10:E10)

а в ячейке В15 – количество истраченного ресурса 3:

=СУММПРОИЗВ(B6:E6; B10:E10)

В ячейки С13-С15 записываем знак неравенства (<=). В ячейках D13-D15 располагаем правые части ограничений. Можно ввести числа, аналогичные числам в ячейках F4:F6, однако удобнее задать ссылки на эти ячейки. Так, в ячейку D13 помещаем формулу

=F4

· Для задания ограничений (1.3) в ячейки В16:В19 записываем ссылки на ячейки В10:Е10, в ячейки С16:С19 – знак неравенства (>=),в ячейки D16:D19 – значение 0.

Рис.2

Рис.3

3. Для выполнения расчета используем подпрограмму Поиск решения. Соответствующая команда находится в меню Сервис. При выполнении команды Поиск решения откроется диалоговое окно с тем же названием (рис.4). В этом окне нужно задать:

· В поле Установить целевую ячейку – адрес ячейки, в которой находится формулы для вычисления общей прибыли - C11.

Рис.4

· Переключатель - в положение Максимальному значению, поскольку необходимо найти максимум общей прибыли.

· В поле Изменяя ячейки - адрес диапазона ячеек, в котором находится количество производимых изделий – B10:E10.

· В окно Ограничения по одному добавить ограничения с помощью кнопки Добавить.

- Нажать кнопку Добавить.

- В открывшемся окне «Добавление ограничения» (рис.5) в поле Ссылка на ячейку задать адрес ячейки, содержащей формулу для левой части ограничения (например, B13). В списке знаков неравенств выбрать нужный знак. В поле Ограничение задать адрес ячейки, содержащей правую часть ограничения (например D13).

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

Рис. 5

· Нажать кнопку Параметры. Откроется диалоговое окно Параметры поиска решения (Рис. 6). В этом окне нужно установить флажок в поле Линейная модель и нажать кнопку ОК. Вновь откроется окно Поиск решения.

Для выполнения вычислений нужно нажать кнопку Выполнить. Результаты работы подпрограммы отображаются в окне Результаты поиска решения (рис.7). Сообщение «Решение найдено. Все ограничения и условия оптимальности выполнены» свидетельствует о том, что программе удалось найти решение поставленной задачи. Для того, чтобы просмотреть полученные результаты в таблице, нужно нажать кнопку ОК в этом окне. Если появляется сообщение «Поиск не может найти подходящего значения», задача поставлена неправильно. В этом случае нужно нажать кнопку Отмена и проверить правильность параметров поиска решения и формул в таблице.

Рис.6

Рис.1.7

4. Проанализируем полученное решение (рис.8).

Рис. 8

· Максимальная прибыль составляет 1320 (значение в ячейке С11).

· Оптимальный план производства содержится в диапазоне B10:E10. Товары 2 и 4 производить невыгодно – количества этих товаров равны 0. Товар 1 следует производить в количестве 10, товар 4 – в количестве 6.

· Общее количество сырьевых ресурсов, истраченных на производство, составит
84, трудовые и финансовые ресурсы будут истрачены полностью.

Дополнительные ограничения на план производства продукции

Кроме ограничений, наложенных на использование ресурсов для производства, возможно введение ограничений на количества производимых товаров. Так, в рассмотренном выше примере для получения максимальной прибыли товары 2 и 4 выпускать вообще не нужно. Потребуем, чтобы все товар1 производился в количестве не менее 1, товар2 – не менее 2, товары 3 и 4 – не менее 3.

Внесем изменения в таблицу Excel, как показано на рис. 9. В 8-ю строку запишем минимально требуемое количество производимых товаров. Значения в ячейках D16:D19 будут содержать ссылки на ячейки B8:E8.

Рис.9

Проанализируем полученное решение:

· Максимальная прибыль уменьшилась по сравнению с предыдущим планом и составляет 1175 (значение в ячейке С11).

· Товары 2, 3 и 4 следует производить в минимально допустимых количествах. Товар 1 следует производить в количестве 4,75.

· Общее количество сырьевых ресурсов, истраченных на производство, составит
59,5; трудовых – 12,75; финансовые ресурсы будут истрачены полностью.

Поиск по сайту: