Методические указания к выполнению задания.

Для решения задачи на персональном компьютере средствами Excel составьте математическую (табличную) модель записи исходных данных, как показано в таблице 2.

Таблица 2

группы оборудования	Удельные нормы занятости оборудования на производство единицы продукции		Общий фонд занятости групп оборудования
	Прод. А	Прод. B
группа №1	a1	b1	z1
группа №2	a2	b2	z2
группа №3	a3	b3	z3
Стоимость продукции в руб.	st1	st2	Fmax
План выпуска продукции	х1	х2

2. Сформулируйте критерий оптимизации;

Запишите целевую функцию: → F = st₁∙ х₁+ st₂∙ х₂ max (необходимо найти максимум функции)

3. Задайте начальный план решения и ограничения системой линейных неравенств вида: a_i х₁+b_i х₂ ≤ z_i

4. Для возможности ввода этих неравенств в диалоговое окно «Добавление ограничения» инструмента Поиск решения преобразуйте их следующим образом:

х₁ ≤ ( z_i - b_i х₂)/ a_i

5. Найдите решение задачи в соответствии с вашим вариантом, указанным в таблице 1., используя инструмент Поиск решения.

Задание 4

Оптимизация производственных и коммерческих операций

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

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

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

ПРИМЕРЫ РЕШЕНИЯ ТИПОВЫХ ЗАДАЧ

1. Выбор ассортимента продукции (целочисленного программирования).

2. Определение линейных размеров емкости (нелинейного программирования)

3. Раскрепление поставок по поставщикам (транспортная задача).

Решение задачи целочисленного программирования

Разработать такой план выпуска изделий, который даст максимальную прибыль от продажи этих изделий, причем известно, что “Изделие 1” нужно выпустить в количестве не меньше 17 штук. Данные приведены в таблице.

Изделия	Нормативная трудоемкость по группам оборудования в часах			Оптовая Цена.(руб.)
	I	II	III
Изделие 1	60	40	0	100
Изделие 2	10	20	10	120
Изделие 3	30	0	0	200
Фонд времени в часах	6000	3000	2000

1. Заполнить электронную таблицу MS Excel исходными данными, например, следующим образом:

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

B10=СУММПРОИЗВ(B6:B8;$F$6:$F$8) C10=СУММПРОИЗВ(C6:C8;$F$6:$F$8) D10=СУММПРОИЗВ(D6:D8;$F$6:$F$8)	затраты (трудоемкость)
F11=СУММПРОИЗВ(E6:E8;$F$6:$F$8)	оптовая цена по всем изделиям

3.Выбрать инструмент “Поиск решения” из меню “Сервис”. Предъявляется окно “Поиск решения”, которое заполняется в соответствии с поставленной задачей и заполненной в пункте “1)” таблицей следующим образом (ограничения вводятся с помощью кнопки “Добавить”):

4.Нажать кнопку “Выполнить” для решения поставленной задачи. Если вычисления оказались успешными, программа предъявит окно итогов. Решение можно сохранить или отказаться (“Восстановить исходные значения”). Кроме того, можно получить один из видов отчетов (Результаты, Устойчивость или Пределы).

Решение задачи нелинейного программирования

Определить линейные размеры прямоугольного ящика, у которого сумма линейных размеров не превышает 3.8 м, объем ящика должен быть максимальный.

1. Математическая модель задачи:

Пусть x, у, z - линейные размеры ящика

Тогда, согласно условию задачи:

V=Х*У*Z;

Х+У+Z<=3,8;

Х>=0, У>=0, Z>=0;

Начальные значения (X,Y,Z)=(1,1,1).

2. Заполнить электронную таблицу MS Excel исходными данными, например, следующим образом:

3.Предполагая, что искомые линейные размеры будут находиться в ячейках A5, B5, C5, введем начальные значения и формулу для вычисления суммы линейных размеров и формулу для расчета объема ящика (целевая функция):

A5=1 B5=1 C5=1	начальные значения
D5=СУММ($A$5:$C$5)	формула для вычисления суммы линейных размеров
E6=$A$5*$B$5*$C$5	формула для расчета объема ящика (целевая функция)

4.Выбрать инструмент “Поиск решения” из меню “Сервис”. Предъявляется окно “Поиск решения”, которое заполняется в соответствии с поставленной задачей и заполненной в пункте “1)” таблицей следующим образом (ограничения вводятся с помощью кнопки “Добавить”):

5.Нажать кнопку “Выполнить” для решения поставленной задачи. Если вычисления оказались успешными, программа предъявит окно итогов. Решение можно сохранить или отказаться (“Восстановить исходные значения”). Кроме того, можно получить один из видов отчетов (Результаты, Устойчивость или Пределы).

Решение транспортной задачи.

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

База	Завод
	№1	№2	№3	№4
А	180	100	370	190
Б	140	150	210	170
В	240	270	140	250

Известно, что база А может фактически в течение планового периода перерабатывает 170 тонн металлопроката база Б -180 тонн, а база В - 250 тонн металлопроката В свою очередь завод №1 в течение данного планового периода потребляет 150 тонн металлопроката, завод №2 - 90 тонн, завод №3 - 200 тонн, завод №4 -160 тонн. Стоимость перевозки 1 тонны груза на 1 км составляет 15 руб.

1.Заполнить электронную таблицу MS Excel исходными данными, например, следующим образом:

2.Предполагая, что искомое количество изделий будет находиться в ячейках с В11 по Е13 введем формулы для вычисления объема поставленного груза на каждый завод, объем отгрузки с каждой базы и формулу для расчета затрат на перевозку (целевая функция):

B15=СУММ(B11:B13) C15=СУММ(C11:C13) D15=СУММ(D11:D13) E15=СУММ(E11:E13)	формулы вычисления объема поставленного груза на каждый завод
F11=СУММ(B11:E11) F12=СУММ(B12:E12) F13=СУММ(B13:E13)	формулы объема отгрузки с каждой базы
F15=$F$3*СУММ(B11:E13)	формула для расчета затрат на перевозку (целевая функция)

3.Выбрать инструмент “Поиск решения” из меню “Сервис”. Предъявляется окно “Поиск решения”, которое заполняется в соответствии с поставленной задачей и заполненной в пункте “1)” таблицей следующим образом (ограничения вводятся с помощью кнопки “Добавить”):

4.Нажать кнопку “Выполнить” для решения поставленной задачи. Если вычисления оказались успешными, программа предъявит окно итогов. Решение можно сохранить или отказаться (“Восстановить исходные значения”). Кроме того, можно получить один из видов отчетов (Результаты, Устойчивость или Пределы).