ЗАДАЧА ОБ ИСПОЛЬЗОВАНИИ СЫРЬЯ

КОМПЬЮТЕРНОЕ МОДЕЛИРОВАНИЕ В EXCEL
ЗАДАЧА ОБ ИСПОЛЬЗОВАНИИ СЫРЬЯ
Что такое линейное программирование
Многие экономические процессы описываются математическими моделями, в которых требуется найти такие значения переменных параметров, при которых достигается максимальное или минимальное значение линейной функции от этих переменных, при различных ограничениях, задаваемых линейными управлениями или неравенствами. Искомые переменные, называются контролируемыми факторами, функция – целевой функцией. Задачи такого типа называются задачами линейного программирования.
Модели линейного программирования в экономике и управлении используются как инструмент оптимизации при планировании производства, составление планов перевозов и т.д.
Постановка задачи
Предприятие выпускает курс n видов продукции, которые обозначим: P1, P2,, Pn. Для этого используется m вида сырья: S1, S2, , Sm, запасы которого равны соответственно b1, b2, , bm. Известно, что расход i-го вида сырья для производства единицы j-го вида продукции Pj равен aij. От реализации единицы j-го вида продукции Pj предприятие получает доход, равный Cj. Требуется составить такой план производства каждого вида продукции, чтобы при имеющихся запасах сырья обеспечить предприятию максимальный суммарный доход.
Математическая модель
Составим план производства для ателье, занимающегося пошивом туристического снаряжения – палаток. Для пошива используется три вида материалов (сырья): водоотталкивающая ткань, утеплитель, москитная сетка. Представим данные с двумя видами продукции (палатки двух моделей) и тремя видами материалов (водоотталкивающая ткань, утеплитель, москитная сетка) в виде таблицы.
Материалы(Si)
Запасы материалов (bi), м
Расход материалов на продукцию Pj, м



Палата (модель 1)
Палата (модель 2)

Водооттал. ткань
105
7
4

Утеплитель
68
3
5

Москитная сетка
66
1
6

Удельный доход от реализации (Сj)
5
6


Ячейки, выделенные фоном, содержат значения расхода каждого вида сырья (материалов) на производство единицы каждого вида продукции. Это и есть матрица aij. Такой расход сырья называется удельным. Обозначим план пошива палаток модели 1 через X (шт.), а план пошива палат моделей палаток модели 2 через Y (шт.). При таком плане расход материалов, например водоотталкивающей ткани, составит 7 ( X + 4 ( Y метров. Поскольку расход материала не может превышать имеющиеся запасы, получаем ограничения по расходу водоотталкивающей ткани 7 ( X + 4 ( Y ( 105. Аналогические рассуждения приводят к ограничениям и по другим видам материалов. Кроме того, значения X и Y не могут быть отрицательными. Сформулированные условия запишем в виде системы неравенств, которым должны удовлетворять неизвестные X и Y:

Доход от реализации одной палатки модели 1 равен 5 единицам стоимости (например, 5 тыс. руб.), а доход от реализации палатки модели 2 - 6 единицам стоимости. Тогда суммарный доход предприятия от реализации всей произведенной продукции определится формулой: Z =5( X + 6 ( Y. Следовательно, Z есть функция от X и Y. Z(X, Y) является целевой функцией, поскольку целью производства является получение максимального дохода.
Таким образом, математическая формулировка задачи звучит так: требуется найти такое решение системы линейных неравенств (1)-(5),при котором целевая функция Z(X, Y) принимает максимальное значение.
Решение с помощью электронных таблиц
Рассмотренную задачу можно решить с помощью табличного процессора. Подготовим данные, как это показано в Таб.1. В ячейках В2 и В3 будет получено решение, т.е. найдены объемы производства каждого вида продукции, при которых суммарных доход, вычисляемый в ячейках В17, принимает максимальное значение. Диапазон ячеек В13:В15 содержит формулы, с помощью которых задаются левые части неравенств (1)-(3), ограничивающих расход сырья. Диапазон ячеек D13:D15 содержит запасы материалов.
Таблица 1.

A
B
C
D

1
Объем производства




2
Палатки (модель 1)
11



3
Палатки (модель 2)
7



4





5





6
Материалы
Запасы материалов
Палатки (модель 1)
Палатки (модель 2)

7
Водооттал. ткань
105
7
4

8
Утеплитель
68
3
5

9
Москитная сетка
66
1
6

10
К°Удельный доход от реализации
5
6

11





12
Ограничения




13

=C7*$B$2+D7*$B$3
(
105

14

=C8*$B$2+D8*$B$3
(
68

15

=C9*$B$2+D9*$B$3
(
66

16





17
Суммарный доход
=C10*B2+D10*B3




Установим курсор в ячейку В17, в которой должно быть вычислено значение целевой функции, и выполним команду Поиск решения из меню Сервис. В открывшемся окне необходимо произвести установки, показанные на рис. 1.
Для этого выполняются следующие действия:
В поле Установить целевую ячейку вводится адрес ячейки В17.
Для поля Равной выбирается параметр максимальному значению.
В поле Изменяя ячейки вводится диапазон ячеек с неизвестными В2:В3.
Щелчком на кнопке Добавить вызывается окно Добавить ограничение.
Для ввода первого ограничения в поле Ссылка на ячейку указывается адрес ячейки В13, а в поле Ограничение – адрес ячейки D13. Между ними выбирается знак отношения (( и нажимается кнопка Добавить. Аналогично добавляются два оставшихся ограничения.
Щелчком на кнопке параметры вызывается окно Параметры поиска решения (рис. 2), в котором необходимо отметить, что ищутся неотрицательные значения X и Y и используется линейная модель. Это означает то, что целевая функция линейно зависит от переменных X и Y.
Неотрицательные решения системы линейных неравенств, при которых целевая функция (суммарный доход) принимает максимальное значение, табличный процессор Microsoft Excel находит приближенно, используя итерационный метод поиска, который называется методом Ньютона. Поэтому в качестве параметров указывается предельное число итераций и относительная погрешность. После того как все установки сделаны, следует нажать кнопку Выполнить.
В результате в ячейках В2 и В3 будет получено решение – объем производства палаток первой и второй моделей (Таб.2), а в ячейке В17 – максимальный доход, полученный от реализации такого объема продукции. Как исследовало ожидать, полученные значения совпадают с результатами графического метода решения задачи: X = 11, Y = 7, Z = 97.


Таблица 2. Результаты решение задачи

A
B
C
D

1
Объем производства




2
Палатки (модель 1)
11



3
Палатки (модель 2)
7



4





5





6
Материалы
Запасы материалов
Палатки (модель 1)
Палатки (модель 2)

7
Водооттал. ткань
105
7
4

8
Утеплитель
68
3
5

9
Москитная сетка
66
1
6

10
Удельный доход от реализации
5
6

11





12
Ограничения




13

105
(
105

14

68
(
68

15

53
(
66

16





17
Суммарный доход
97




Задача 1.
В ресторане готовятся фирменные блюда трех видов (блюдо А, блюдо В и блюдо С) с использованием при приготовлении ингредиентов трех видов (ингредиент 1, ингредиент 2 и ингредиент 3). Расход ингредиентов в граммах на блюдо задается следующей таблицей:
Вид ингредиента
Блюдо А
Блюдо В
Блюдо C

Ингредиент 1
20
50
10

Ингредиент 2
20
0
40

Ингредиент 3
20
10
10

Стоимость приготовления блюд одинакова (100 руб.).
Ежедневно в ресторан поступает 5 кг ингредиента 1 и по 4 кг ингредиентов видов 2 и 3. Каково оптимальное соотношение дневного производства блюд различного вида, если производственные мощности ресторана позволяют использовать весь запас поступивших продуктов?
Задача 2.
Пошивочная мастерская планирует выпуск двух видов костюмов: мужских и женских. На женский костюм требуется 1 м шерсти, 2 м лавсана и 1 человеко-день трудозатрат. На мужской костюм 3,5 м шерсти, 0,5 м лавсана и 1 человеко-день трудозатрат. Всего имеется 350 м шерсти и 240 м лавсана, 150 человеко-дней трудозатрат. Предусматривается выпуск не менее 110 костюмов, причем, необходимо обеспечить прибыль не менее 1400 руб. Определите оптимальное количество костюмов каждого вида, если прибыль от реализации женского костюма составляет 10 руб., а мужского 20 руб.

Литература:
Информатика и ИКТ. Профильный уровень: учебник для 11 класса/ И.Г. Семакин, Е.К.Хеннер, Л.В.Шестакова. - М.:БИНОМ. Лаборатория Знаний,2012.-350с.: ил.
Решение математических задач средствами Excel:Практикум/ В.Я.Гельман. -СПб.:Питер,2003-237 с.:ил.
Рисунок 1

Рисунок 2



15

Приложенные файлы


Добавить комментарий