 |
Нахождение решения задачи о выпуске продукции средствами Microsoft Excel
|
|
|
|
Задание 6
1. Найдите оптимальное решение задачи средствами Microsoft Excel.
Для этого выполните следующую последовательность действий:
2. Создайте экранную форму для ввода условия задачи.
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
| № | Математическая модель | № | Математическая модель | ||
| 
| ||||
| 
| ||||
| 
| ||||
| 
| ||||
| 
| ||||
| Продолжение табл. 4 | |||||
| 
| ||||
2. Найдите для своего варианта оптимальное решение задачи линейного программирования, используя MS Excel, первоначально построив математическую модель задачи линейного программирования. Сохраните файл с решением под именем zadanie_1(b).
1) Для изготовления двух изделий используются три вида сырья. Общее количество сырья, расход (кг) на изготовление единицы изделия и цена единицы каждого изделия представлены в таблице 5. Составить оптимальный план производства изделий B1 и B2, обеспечивающий максимальный по стоимости выпуск продукции.
Таблица 5
Вариант 1
| Сырье | Вид изделия | Запасы сырья | |
| В1 | В2 | ||
| А1 | |||
| А2 | |||
| А3 | |||
| Цена единицы изделия (тыс.руб.) |
2) На трех станках обрабатываются два изделия. Каждое изделие проходит обработку на каждом станке. В таблице 6 задана трудоемкость обработки каждого изделия на каждом станке, фонд полезного времени работы станков и отпускная цена единицы изделия. Найти план производства изделий, при котором прибыль предприятия от их реализации будет максимальной.
Таблица 6
Вариант 2
|
|
|
| Станки | Вид изделия | Фонд времени | |
| В1 | В2 | ||
| А1 | |||
| А2 | |||
| А3 | |||
| Прибыль (тыс.руб.) |
3) На четырех станках обрабатываются два изделия. Каждое изделие проходит обработку на каждом станке. В таблице 7 задана трудоемкость обработки одного изделия и фонд полезного времени работы станков. Составить план производства изделий, обеспечивающий максимальную загрузку станков.
Таблица 7
Вариант 3
| Станки | Вид изделия | Фонд времени | |
| В1 | В2 | ||
| А1 | |||
| А2 | |||
| А3 | |||
| А4 |
4) Предприятие изготовляет два вида изделия. Каждое изделие требует обработки на трех видах оборудования. Известны прибыль, получаемая от реализации каждого изделия, трудоемкость обработки изделий и фонд времени работы оборудования (Таблица 8). Сколько нужно изготовить тех или иных изделий, чтобы прибыль была наибольшей?
Таблица 8
Вариант 4
| Станки | Вид изделия | Запасы сырья | |
| В1 | В2 | ||
| А1 | |||
| А2 | |||
| А3 | |||
| Прибыль (тыс.руб.) |
5) Трикотажная фабрика использует для производства свитеров и кофточек чистую шерсть, силон и нитрон. Количество пряжи каждого вида (кг), необходимой для изготовления 10 изделий, запасы каждого вида пряжи, а также прибыль, получаемая от их реализации, приведены в таблице 9. Составить план производства изделий, обеспечивающий получение максимальной прибыли.
Таблица 9
Вариант 5
| Вид сырья | Затраты пряжи на 10 шт. (кг) | Запасы (кг) | |
| Свитер | Кофточка | ||
| Шерсть | |||
| Силон | |||
| Нитрон | |||
| Прибыль (тыс.руб.) |
6) Для производства двух видов продукции в цехе используется три группы оборудования. Нормы затрат времени на один комплект изделий, фонд времени работы оборудования, а также прибыль, получаемая от реализации единицы продукции, приведены в таблице 10. Определить вариант загрузки оборудования, обеспечивающий максимальную прибыль от реализации продукции.
Таблица 10
Вариант 6
| Оборудование | Норма затрат времени на один комплект изделий, ст.ч./ед. | Фонд работы оборуд. ст.ч. | |
| Продукция первого вида | Продукция второго вида | ||
| А | |||
| Б | |||
| В | |||
| Прибыль (тыс.руб.) |
7) Для изготовления двух видов продуктов A1 и A2 используются три вида сырья: В1, В2, В3. Расход сырья для изготовления 1 кг продуктов и запасы (кг) приведены в таблице 11. Составить план производства, обеспечивающий максимальный по стоимости выпуск продукции.
|
|
|
Таблица 11
Вариант 7
| Сырье | Вид изделия | Запасы сырья | |
| А1 | А2 | ||
| В1 | |||
| В2 | |||
| В3 | |||
| Цена 1 кг продукта (тыс.руб.) |
8) Для откорма крупного рогатого скота используются два вида кормов B1 и В2, в которые входят питательные вещества A1, A2, A3, A4. Содержание количества единиц питательных веществ в 1 кг каждого вида корма, стоимость 1 кг корма и минимальное содержание питательных веществ в дневном рационе животного представлены в таблице 12. Составить рацион при условии минимальной стоимости.
Таблица 12
Вариант 8
| Питательные вещества | Вид кормов | Минимально необходимое количество питательных веществ | |
| В1 | В2 | ||
| А1 | |||
| А2 | |||
| А3 | |||
| А4 | |||
| Стоимость 1 кг (тыс. руб.) |
9) Предприятие изготовляет два вида продукции: №1 и №2. Каждая продукция требует обработки на четырех видах оборудования. Известны прибыль, получаемая от реализации каждой продукции, трудоемкость обработки продукции и фонд времени работы оборудования (Таблица 13). Составить план производства, при котором прибыль предприятия от реализации всей продукции будет максимальной.
Таблица 13
Вариант 9
| Оборудование | Вид продукции | Запасы сырья | |
| №1 | №2 | ||
| А1 | |||
| А2 | |||
| А3 | |||
| А4 | |||
| Прибыль (тыс. руб.) |
10) Для изготовления двух видов конфет A и Б на кондитерской фабрике используются три вида сырья: какао, сахар, наполнитель. Общее количество запасов сырья, расход (кг) на изготовление 10 кг конфет и прибыль от реализации 10 кг конфет представлены в таблице 14. Необходимо определить, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной.
|
|
|
Таблица 14
Вариант 10
| Сырье | Вид конфет | Запасы сырья | |
| А | Б | ||
| Какао | |||
| Сахар | |||
| Наполнитель | |||
| Прибыль (руб.) |
11) Для изготовления двух видов компота в столовой используются три вида сухофруктов: яблоки, вишня и слива. Общее количество запасов сухофруктов, расход (кг) на изготовление единицы компота и цена единицы каждого компота представлены в таблице 15. Составить оптимальный план производства компота B1 и B2, обеспечивающий максимальный товарный выпуск продукции.
Таблица 15
Вариант 11
| Вид сухофруктов | Вид компота | Запасы (кг) | |
| В1 | В2 | ||
| Яблоки | 1,2 | 0,8 | |
| Вишня | 0,6 | 0,2 | |
| Слива | |||
| Цена (руб.) |
12) Для подкорма почвы используются два вида удобрений, в которые входят: азот, фосфор, калий. Содержание количества единиц вещества в 1 кг каждого вида удобрения, стоимость 1 кг удобрения и минимальное содержание в удобрении представлены в таблице 16. Составить оптимальный план закупки удобрений при условии минимальной стоимости.
Таблица 16
Вариант 12
| Сырье | Вид удобрения | Минимально необходимое количество веществ | |
| А1 | А2 | ||
| Азот | |||
| Фосфор | |||
| Калий | |||
| Цена 1 кг удобрения (руб.) |
После того, как вся работа будет проделана, пригласите преподавателя и продемонстрируйте ему результат.
Вопросы для самоконтроля
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
|
|
|
