ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 28.07.2024
Просмотров: 45
Скачиваний: 1
ЛАБОРАТОРНАЯ РАБОТА №1
Решение задач оптимизации с помощью Excel
Цель работы: овладение методами линейного программирования, нахождение экстремума линейных функций.
Задание к работе:
Работу выполнил:
Работу защитил:
Гунин К.Д. ЗЭУ(2)-111
Теоретическое введение:
Основные возможности обработки экономической информации на основе табличного процессора Excel
Электронные таблицы Excel предназначены для упорядочения и обработки различных типов данных. Excel позволяет:
-
проводить сложные вычисления как с использованием оригинальных расчетных формул, так и с применением стандартных математических, статистических, финансовых и иных функций;
-
осуществлять табличную обработку данных и представлять результаты расчетов в виде графиков и диаграмм;
-
планировать и распределять ресурсы;
-
составлять статистические сводки и калькуляции, проводить статистические финансовые расчеты.
Многие экономические задачи связаны с нахождением оптимальных решений при наличии некоторых ограничений на искомые переменные. Такие ограничения существенно уменьшают размеры области, в которой производится поиск оптимума. Решение подобных задач возможно с помощью надстройки Поиск решения.
В Excel реализованы методы математического программирования для решения задач линейного и нелинейного программирования.
Задачи линейного программирования относятся к числу наиболее часто решаемых задач оптимизации.
Формально задача линейного программирования понимается как задача поиска экстремума линейной целевой функции F на множестве, задаваемом системой линейных равенств и неравенств.
Оптимизация - определение значений экономических показателей, при которых достигается оптимум, то есть оптимальное, наилучшее состояние системы. Чаще всего оптимуму соответствует достижение наивысшего результата при данных затратах ресурсов или достижение заданного результата при минимальных ресурсных затратах.
Задачей оптимизации в математике является нахождение экстремума (минимума или максимума) действительной функции в некоторой области. Как правило, рассматриваются области, заданные набором равенств и неравенств.
Методы, посредством которых решают задачи оптимизации, подразделяются на виды, соответствующие задачам, к которым они применяются:
-
Задачи оптимизации, в которых целевая функция и ограничения являются линейными функциями, разрешаются так называемыми методами линейного программирования.
-
В противном случае имеют дело с задачей нелинейного программирования и применяют соответствующие методы.
Помимо того, оптимизационные методы делятся на следующие группы:
-
аналитические методы;
-
численные методы;
-
графические методы.
Целевая функция в экстремальных задачах — функция, минимум или максимум которой требуется найти. Это ключевое понятие оптимального программирования. Найдя экстремум целевой функции и, следовательно, определив значения управляемых переменных, которые к нему приводят, мы тем самым находим оптимальное решение задачи. Таким образом, целевая функция выступает как критерий оптимальности решения задачи.
Различается ряд видов целевых функций: линейная, нелинейная, выпуклая, квадратичная и др. — в соответствии с формой математической зависимости, которую они отображают.
Задача №1
Для изготовления трех видов изделий А, В, С используется токарное, сварочное и шлифовальное оборудование. Затраты времени на обработку одного изделия для каждого из типов оборудования указаны в таблице 3. В ней же указан общий фонд рабочего времени каждого из типов используемого оборудования, а также прибыль от реализации одного изделия каждого вида.
Требуется определить, сколько изделий и какого вида следует изготовить предприятию, чтобы прибыль от их реализации была максимальной.
|
Оборудование |
Расход времени на 1 деталь |
Норма времени |
Расход времени |
||
|
А |
В |
С |
|||
|
Фрезерное |
2 |
4 |
5 |
120 |
120 |
|
Токарное |
1 |
8 |
6 |
280 |
168 |
|
Сварочное |
7 |
4 |
5 |
240 |
240 |
|
Шлифовальное |
4 |
6 |
7 |
360 |
204 |
|
Прибыль |
10 |
14 |
12 |
|
|
|
|
|
|
|
|
|
|
Количество деталей: |
24 |
18 |
0 |
Общая прибыль: |
492 |
|
Microsoft Excel 11.0 Отчет по результатам |
|
|
|
|
||
|
Рабочий лист: [зАДАЧА123.xls]Лист1 |
|
|
|
|
||
|
Отчет создан: 24.03.2012 8:43:22 |
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Целевая ячейка (Максимум) |
|
|
|
|
||
|
|
Ячейка |
Имя |
Исходное значение |
Результат |
|
|
|
|
$F$9 |
Общая прибыль: Расход времени |
492 |
492 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Изменяемые ячейки |
|
|
|
|
||
|
|
Ячейка |
Имя |
Исходное значение |
Результат |
|
|
|
|
$B$9 |
Количество деталей: А |
24 |
24 |
|
|
|
|
$C$9 |
Количество деталей: В |
18 |
18 |
|
|
|
|
$D$9 |
Количество деталей: С |
0 |
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ограничения |
|
|
|
|
||
|
|
Ячейка |
Имя |
Значение |
Формула |
Статус |
Разница |
|
|
$F$3 |
Фезерное Расход времени |
120 |
$F$3<=$E$3 |
связанное |
0 |
|
|
$F$4 |
Токарное Расход времени |
168 |
$F$4<=$E$4 |
не связан. |
112 |
|
|
$F$5 |
Сварочное Расход времени |
240 |
$F$5<=$E$5 |
связанное |
0 |
|
|
$F$6 |
Шлифовальное Расход времени |
204 |
$F$6<=$E$6 |
не связан. |
156 |
|
|
$B$9 |
Количество деталей: А |
24 |
$B$9>=0 |
не связан. |
24 |
|
|
$C$9 |
Количество деталей: В |
18 |
$C$9>=0 |
не связан. |
18 |
|
|
$D$9 |
Количество деталей: С |
0 |
$D$9>=0 |
связанное |
0 |
|
|
$B$9 |
Количество деталей: А |
24 |
$B$9=целое |
связанное |
0 |
|
|
$C$9 |
Количество деталей: В |
18 |
$C$9=целое |
связанное |
0 |
|
|
$D$9 |
Количество деталей: С |
0 |
$D$9=целое |
связанное |
0 |
|
|
|
|
|
|
|
|
Предприятие будет получать максимальную прибыль при изготовлении 24 шт. деталей А, 18 шт. деталей B и отказалось от изготовления деталей С.
Задача №2
Компания производит полки для ванных комнат двух размеров - А и В. Агенты по продаже считают, что в неделю на рынке может быть реализовано до 550 полок. Для каждой полки типа А требуется 2 м материала, а для полки типа В 3 м материала. Компания может получить до 1200 м материала в неделю. Для изготовления одной полки типа А требуется 12 мин машинного времени, а для одной полки типа В - 30 мин; ресурс машинного времени в неделю составляет 160 ч. Если прибыль от продажи полок типа А составляет 3$, а от типа В - 4$, то сколько полок каждого типа следует выпускать в неделю.
|
|
Вид продукции: |
Норма затрат: |
Затраты: |
|
|
Вид затрат: |
А |
В |
|
|
|
Материал |
2 |
3 |
1200 |
1200 |
|
Время |
12 |
30 |
9600 |
8400 |
|
Прибыль: |
3 |
4 |
|
1750 |
|
|
|
|
Норма выпуска: |
|
|
Выпуск каждой детали: |
450 |
100 |
550 |
|
|
Общий выпуск: |
550 |
|
|
|
|
Microsoft Excel 11.0 Отчет по результатам |
|
|
|
|||
|
Рабочий лист: [зАДАЧА123.xls]Лист2 |
|
|
|
|||
|
Отчет создан: 24.03.2012 8:50:54 |
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Целевая ячейка (Максимум) |
|
|
|
|
||
|
|
Ячейка |
Имя |
Исходное значение |
Результат |
|
|
|
|
$E$9 |
Прибыль: Затраты: |
1750 |
1750 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Изменяемые ячейки |
|
|
|
|
||
|
|
Ячейка |
Имя |
Исходное значение |
Результат |
|
|
|
|
$B$11 |
Выпуск каждой детали: А |
450 |
450 |
|
|
|
|
$C$11 |
Выпуск каждой детали: В |
100 |
100 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ограничения |
|
|
|
|
||
|
|
Ячейка |
Имя |
Значение |
Формула |
Статус |
Разница |
|
|
$B$12 |
Общий выпуск: А |
550 |
$B$12<=$D$11 |
связанное |
0 |
|
|
$E$7 |
Материал Затраты: |
1200 |
$E$7<=$D$7 |
связанное |
0 |
|
|
$E$8 |
Время Затраты: |
8400 |
$E$8<=$D$8 |
не связан. |
1200 |
|
|
$C$11 |
Выпуск каждой детали: В |
100 |
$C$11=целое |
связанное |
0 |
|
|
$B$11 |
Выпуск каждой детали: А |
450 |
$B$11=целое |
связанное |
0 |
|
|
$B$11 |
Выпуск каждой детали: А |
450 |
$B$11>=0 |
не связан. |
450 |
|
|
$C$11 |
Выпуск каждой детали: В |
100 |
$C$11>=0 |
не связан. |
100 |
|
|
|
|
|
|
|
|
Оптимальный объем выпуска полок за неделю предприятием типа А -450 шт, типа В - 100шт. в пределах недельной нормы 550 шт, при этом предприятие будет получать максимальную прибыль от продажи полок.
Задача №3
Автозавод выпускает две модели: «Каприз» и «Фиаско». На заводе работает 1 ООО неквалифицированных и 800 квалифицированных рабочих, каждому из которых оплачивается 40 часов в неделю. Для изготовления модели «Каприз» требуется 30 ч неквалифицированного труда и 50 ч квалифицированного труда; для «Фиаско» соответственно 40 и 20 ч. Каждая модель «Фиаско» требует затрат в размере 500$ на сырье и комплектующие изделия, «Каприз» 1 500$; суммарные затраты не должны превосходить 900 000 $ в неделю. Рабочие, осуществляющие доставку, работают по 5 дней в неделю и могут забрать с завода не более 210 машин в день. Каждая модель «Каприз» приносит фирме 1 000$ прибыли, «Фиаско» 500$. Какой объем выпуска каждой модели Вы бы порекомендовали?
|
|
Каприз |
Фиаско |
Факт |
Ресурс |
|
затраты на сырье и комплетующие |
1500 |
500 |
891000 |
900000 |
|
доставка |
|
|
1050 |
1050 |
|
квал труд |
50 |
20 |
31980 |
32000 |
|
невкал труд |
30 |
40 |
38340 |
40000 |
|
прибыль |
1000 |
500 |
708000 |
|
|
|
|
|
|
|
|
единицы |
366 |
684 |
|
|
|
|
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Исходя из возможностей автозавода (фонд рабочего времени за неделю, возможности затрат на сырье и комплектующие, возможности доставки) оптимальный объем выпуска машин марки Каприз составит 366 ед., марки Фигаро - 684 ед.
|
|
|
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||