Нахождение решения задачи о выпуске продукции средствами Microsoft Excel

Задание 6

1. Найдите оптимальное решение задачи средствами Microsoft Excel.

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

2. Откройте из папки МАТ_МОД файл lab_1(с), содержащий экранную форму для ввода условия задачи.

3. Введите исходные данные в экранную форму.

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

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

6. Если Вы получили такой же результат (см. Рис. 13), то сохраните файл в своей папке с именем lab_1(с).

7. Найдите целочисленное решение данной задачи и сохраните его в файле с именем lab_1(d).

В окне Параметры поиска решения (Поиск решения à Параметры) укажите, что это должна быть линейная модель.

Рис. 13. Оптимальное решение

Если Вы получили результат, аналогичный тому, что приведен на Рис. 14, то пригласите преподавателя и продемонстрируйте ему вашу работу.

Рис. 14. Целочисленное решение

3. Резюме:

1) Решение задачи линейного программирования осуществляется в несколько этапов:

· построение математической модели задачи;

· создание экранной формы для задачи;

· ввод исходных данных в экранную форму;

· ввод формул, описывающих математическую модель;

· заполнение всех необходимых для нахождения решения полей в окне «Поиск решения» (установите целевую ячейку, направление целевой функции; укажите изменяемые ячейки, задайте ограничения и необходимые параметры поиска решения);

· анализ полученного результата.

2) Нахождение оптимального решения:

· Сервис à Поиск решения.

3) Определение параметров поиска решения:

· Сервис à  Поиск решения à Параметры поиска решения.

4) Нахождение целочисленного оптимального решения:

· Сервис à Поиск решения;

· в поле «Ограничения» задать ограничение целочисленности необходимых переменных.

4. Контрольные задания

1. Используя MS Excel, найти решение для модели ЛП, соответствующей заданному варианту (табл.4). Сохраните файл с решением под именем zadanie_1(a).

Номер варианта соответствует номеру компьютера, на котором работает студент.

Таблица 4

Контрольное задание №1

№		Математическая модель	№	Математическая модель
1			2
3			4
5			6
7			8
9			10
	Продолжение табл. 4
11			12

2. Найдите для своего варианта оптимальное решение задачи линейного программирования, используя MS Excel, первоначально построив математическую модель задачи линейного программирования. Сохраните файл с решением под именем zadanie_1(b).

1) Для изготовления двух изделий используются три вида сырья. Общее количество сырья, расход (кг) на изготовление единицы изделия и цена единицы каждого изделия представлены в таблице 5. Составить оптимальный план производства изделий B1 и B2, обеспечивающий максимальный по стоимости выпуск продукции.

Таблица 5

Вариант 1

Сырье	Вид изделия		Запасы сырья
	В1	В2
А1	4	3	120
А2	4	10	200
А3	0	15	180
Цена единицы изделия (тыс.руб.)	5	6

2) На трех станках обрабатываются два изделия. Каждое изделие проходит обработку на каждом станке. В таблице 6 задана трудоемкость обработки каждого изделия на каждом станке, фонд полезного времени работы станков и отпускная цена единицы изделия. Найти план производства изделий, при котором прибыль предприятия от их реализации будет максимальной.

Таблица 6

Вариант 2

Станки	Вид изделия		Фонд времени
	В1	В2
А1	4	2	48
А2	0	3	36
А3	2	2	40
Прибыль (тыс.руб.)	15	12

3) На четырех станках обрабатываются два изделия. Каждое изделие проходит обработку на каждом станке. В таблице 7 задана трудоемкость обработки одного изделия и фонд полезного времени работы станков. Составить план производства изделий, обеспечивающий максимальную загрузку станков.

Таблица 7

Вариант 3

Станки	Вид изделия		Фонд времени
	В1	В2
А1	5	7	70
А2	2	1	18
А3	0	2	16
А4	1	0	8

4) Предприятие изготовляет два вида изделия. Каждое изделие требует обработки на трех видах оборудования. Известны прибыль, получаемая от реализации каждого изделия, трудоемкость обработки изделий и фонд времени работы оборудования (Таблица 8). Сколько нужно изготовить тех или иных изделий, чтобы прибыль была наибольшей?

Таблица 8

Вариант 4

Станки	Вид изделия		Запасы сырья
	В1	В2
А1	3	8	24
А2	2	7	14
А3	0	12	24
Прибыль (тыс.руб.)	2	3

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

Таблица 9

Вариант 5

Вид сырья	Затраты пряжи на 10 шт. (кг)		Запасы (кг)
	Свитер	Кофточка
Шерсть	4	2	900
Силон	2	1	400
Нитрон	1	1	300
Прибыль (тыс.руб.)	6	5

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

Таблица 10

Вариант 6

Оборудование	Норма затрат времени на один комплект изделий, ст.ч./ед.		Фонд работы оборуд. ст.ч.
	Продукция первого вида	Продукция второго вида
А	2	2	14
Б	1	2	9
В	0	4	16
Прибыль (тыс.руб.)	2	3

7) Для изготовления двух видов продуктов A1 и A2 используются три вида сырья: В1, В2, В3. Расход сырья для изготовления 1 кг продуктов и запасы (кг) приведены в таблице 11. Составить план производства, обеспечивающий максимальный по стоимости выпуск продукции.

Таблица 11

Вариант 7

Сырье	Вид изделия		Запасы сырья
	А1	А2
В1	4	2	32
В2	2	3	32
В3	2	4	36
Цена 1 кг продукта (тыс.руб.)	5	8

8) Для откорма крупного рогатого скота используются два вида кормов B1 и В2, в которые входят питательные вещества A1, A2, A3, A4. Содержание количества единиц питательных веществ в 1 кг каждого вида корма, стоимость 1 кг корма и минимальное содержание питательных веществ в дневном рационе животного представлены в таблице 12. Составить рацион при условии минимальной стоимости.

Таблица 12

Вариант 8

Питательные вещества	Вид кормов		Минимально необходимое количество питательных веществ
	В1	В2
А1	3	4	24
А2	1	2	18
А3	4	0	20
А4	0	1	6
Стоимость 1 кг (тыс. руб.)	2	1

9) Предприятие изготовляет два вида продукции: №1 и №2. Каждая продукция требует обработки на четырех видах оборудования. Известны прибыль, получаемая от реализации каждой продукции, трудоемкость обработки продукции и фонд времени работы оборудования (Таблица 13). Составить план производства, при котором прибыль предприятия от реализации всей продукции будет максимальной.

Таблица 13

Вариант 9

Оборудование	Вид продукции		Запасы сырья
	№1	№2
А1	2	1	16
А2	1	1	10
А3	0	1	6
А4	1	0	7
Прибыль (тыс. руб.)	3	4

10) Для изготовления двух видов конфет A и Б на кондитерской фабрике используются три вида сырья: какао, сахар, наполнитель. Общее количество запасов сырья, расход (кг) на изготовление 10 кг конфет и прибыль от реализации 10 кг конфет представлены в таблице 14. Необходимо определить, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной.

Таблица 14

Вариант 10

Сырье	Вид конфет		Запасы сырья
	А	Б
Какао	18	15	360
Сахар	6	4	192
Наполнитель	5	3	180
Прибыль (руб.)	90	100

11) Для изготовления двух видов компота в столовой используются три вида сухофруктов: яблоки, вишня и слива. Общее количество запасов сухофруктов, расход (кг) на изготовление единицы компота и цена единицы каждого компота представлены в таблице 15. Составить оптимальный план производства компота B1 и B2, обеспечивающий максимальный товарный выпуск продукции.

Таблица 15

Вариант 11

Вид сухофруктов	Вид компота		Запасы (кг)
	В1	В2
Яблоки	1,2	0,8	48
Вишня	0,6	0,2	16
Слива	0	1	50
Цена (руб.)	12	6

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

Таблица 16

Вариант 12

Сырье	Вид удобрения		Минимально необходимое количество веществ
	А1	А2
Азот	3	2	10
Фосфор	4	6	20
Калий	1	3	7
Цена 1 кг удобрения (руб.)	30	40

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

Вопросы для самоконтроля

1) Что такое математическое и линейное программирование?

2) Какова общая форма записи модели ЛП?

3) Что такое допустимое и оптимальное решения?

4) Каковы основные этапы построения математической модели ЛП?

5) Каковы основные этапы решения задач ЛП в MS Excel?

6) Каков вид и способы задания формул для целевой ячейки и ячеек левых частей ограничений?

7) В чем смысл использования символа $ в формулах MS Excel?

8) Почему при вводе формул в ячейки ЦФ и левых частей ограничений в них отображаются нулевые значения?

9) Каким образом в MS Excel задается направление оптимизации ЦФ?

10) Какие ячейки экранной формы выполняют иллюстративную функцию, а какие необходимы для решения задачи?

11) Поясните общий порядок работы с окном «Поиск решения».

12) Каким образом можно изменять, добавлять, удалять ограничения в окне «Поиск решения»?

13) Какие сообщения выдаются в MS Excel в случаях: успешного решения задачи ЛП; несовместности системы ограничений задачи; неограниченности ЦФ?

14) Объясните смысл параметров, задаваемых в окне «Параметры поиска решения».

15) Каковы особенности решения в MS Excel целочисленных задач ЛП?

Лабораторная работа 2