Отчёт по
лабораторным работам по ЭММ вариант 8
Задача №3
Даны коэффициенты прямых
поставок а
и конечный продукт y
Требуется определить:
1.
межотраслевые
поставки продукции.
2.
проверить
продуктивность матрицы А.
Решение:
А=
, Y=
1)
В ячейке B6:D8 запишем
элементы матрицы Е-А. Массив Е-А задан как диапазон ячеек. Выделим
диапазон B10:D12 для
размещения обратной матрицы В=(Е-А)
и введём формулу для
вычислений МОБР(B6:D8). Затем следует нажать клавиши CTRL+SHIFT+ENTER.
Все
элементы матрицы коэффициентов полных затрат В неотрицательны, следовательно,
матрица А продуктивна.
2)
В ячейки G10:G12 запишем
элементы вектора конечного продукта Y. Выделим
диапазон B15:B17 для размещения вектора валового выпуска Х,
вычисляемого по формуле Х=(Е-А)
*Y. Затем вводим
формулу для вычислений МУМНОЖ(B10:D12, G10:G12). Затем следует нажать клавиши CTRL+SHIFT+ENTER.
3)
Межотраслевые
поставки Х вычисляем по формуле х=а*Х
.
4)
Заполняем схему
МОБ(табл. 2).
Задача №4
Исходные данные транспортной
задачи приведены схематически: внутри прямоугольника заданы удельные
транспортные затраты на перевозку единицы груза (с) , слева указаны мощности поставщика (а) , а сверху –мощность потребителей (b
). Сформулировать
экономико-математическую модель исходной транспортной задачи, найти оптимальный
план закрепления поставщиков за потребителями (x
), установить единственность или не единственность
оптимального плана , используя Поиск
решения.
|
Мощности поставщиков
|
Мощности потребителей
|
|
40
|
30
|
20
|
50
|
|
60
|
2
|
4
|
5
|
1
|
|
70
|
2
|
3
|
9
|
4
|
|
50
|
8
|
4
|
2
|
5
|
В
данной задаче суммарные запасы равны суммарным потребностям, т.е
Ввод условий задачи состоит из следующих шагов:
1) Создание формы для ввода условий задачи.
2) Ввод исходных данных.
3) Ввод зависимостей из математической модели.
4) Назначение целевой функции.
5) Ввод ограничений и граничных условий.
Создание формы для ввода условий задачи.
Изменяем ячейки B3:E6. В эти ячейки
будет записан оптимальный план перевозок - х. Введены исходные данные задачи.
Диалоговое окно Поиск
решения.
После вызова Поиска
решения курсор подвести в поле «Установить целевую ячейку» и ввести адрес :
В15. Ввести направление целевой функции «минимальному
значению». Поместить курсор в поле «Изменяя ячейки». Ввести адреса
изменяемых ячеек В3:Е6. Далее следует добавить
ограничения.
Диалоговое
окно Добавление ограничения.
Все
грузы должны быть перевезены, т.е
Диалоговое
окно Добавление ограничения.
Все потребности должны быть
удовлетворены, т.е.
После
ввода последнего ограничения вместо добавить
ввести ОК. На экране появиться окно Поиск
решения с введенными ограничениями.
Решение:
Решение
задачи производиться сразу же после ввода данных, когда на экране находиться
диалоговое окно Поиск решения. С
помощью окна Параметры можно вводить
условия для решения оптимизационных задач. В нашей задаче следует установить
флажок «неотрицательные значения» и «линейная модель». Нажать кнопку ОК.
Опять появиться диалоговое окно Поиск
решения.
Установка
параметров.
Выполнить.
Решение найдено
В
результате решения получен оптимальный план
|
Матрица перевозок (изменяемые ячейки)
|
|
60
30
50
|
10
30
0
|
30
0
0
|
20
0
0
|
0
2,33Е-12
50
|
|
140
|
40
|
30
|
20
|
50
|
Задача №5
В таблице для каждого
варианта заданы три временных ряда: 1-нарастающая по кварталам прибыль
коммерческого банка y
, второй и третий
ряд-процентные ставки этого банка по кредитованию юридических лиц х1, и депозитным вкладам х2, за тот же период.
|
110 88
78 89 82
80 76 78
76 70
15 20
22 14 25
28 25 28
30 31
42 47
50 48 67
57 61 59
65 54
|
Требуется:
1.
Оценить качество
построенной модели. Вычислить для модели среднюю ошибку аппроксимации и
коэффицент детерминации.
2.
Проанализировать
влияние факторов на зависимую переменную по модели ( для каждого коэффициента
регрессии вычислить коэффициент эластичности, 
) и оценить их значимость при 
1,86
Решение:
1)
Выберете
команду Сервис=>Анализ данных.
2)
В диалоговом окне
Анализ данных выберете инструмент Регрессия, а затем щёлкните ОК.
3)
В диалоговом окне
Регрессия в поле «Входной интервал Y» введите адрес одного диапазонов ячеек , который
представляет зависимую переменную. В поле «Входной интервал Х» введите адрес
одного или нескольких диапазонов, которые содержат значение независимых
переменных.
4)
Выберете
параметры вывода. В данном примере-установите переключатель «Новая рабочая книга»
5)
В поле «Остатки»
поставьте необходимые флажки.
6)
ОК.
Диалоговое окно Регрессия.
Задача №6
|
№ п/п
|
Реклама
|
№ п/п
|
Реклама
|
|
1
2
3
4
5
6
7
8
|
4
4,8
3,8
8,7
8,2
9,7
14,7
18,7
|
9
10
11
12
13
14
15
16
|
19,8
10,6
8,6
6,5
12,6
6,5
5,8
5,7
|
Решение:
Для
решения поставленной задачи необходимо выполнить следующую последовательность
действий.
·
Выделить ячейки
А1:А16, содержащие наименование временного ряда и исходные данные.
·
Вызвать Мастер
диаграмм.
·
Выбрать тип
диаграммы: график, выбрать вид: первый (шаг 1).
·
Шаг 2. Щёлкнуть
кнопку Далее.
·
Шаг 3. Щёлкнуть
кнопку Далее.
·
Шаг 4 Щёлкнуть
кнопку Готово. На экране - построенный график.
·
Щёлкнуть правой
кнопкой на линии графика. График выделен метками.
·
Выбрать тип
Линейная в диалоговом окне Линия тренда (потом Логарифмическая и Полиномиальная
третьей степени.
·
Вкладка
Параметры. Назначаем показывать уравнение на диаграмме.
Выбор вида тренда для временного ряда.
Задача №1
Используя Поиск решения,
решить задачу оптимального использования ресурсов на максимум общей стоимости.
Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в
соответствующей таблице.
Предприятие выпускает 4 вида
продукции и использует 3 типа основного оборудования: токарное, фрезерное,
шлифовальное. Затраты на изготовление единицы продукции приведены в таблице,
там же указан общий фонд рабочего времени, а так же цена изделия каждого вида.
|
Тип оборудования
|
Нормы затрат
ресурсов на еденицу продукции
|
Общий фонд рабочего
времени
|
|
А
|
Б
|
В
|
Г
|
|
Токарное
Фрезерное
Шлифовальное
|
2
1
1
|
1
0
2
|
1
2
1
|
3
1
0
|
300
70
340
|
|
цена
|
8
|
3
|
2
|
1
|
|
В задаче требуется определить:
1. План выпуска продукции из условия максимизации её
стоимости.
2. Ценность каждого ресурса и его приоритет при решении
задачи увеличения запаса ресурса.
3. Максимальный интервал изменения запасов каждого из
ресурсов, в пределах которого структура оптимального решения, т.е. номенклатура
выпускаемой продукции, остаётся без изменения.
4. Суммарную стоимостную оценку ресурсов, используемых
при производстве единицы каждого
изделия. Выпуск, какой продукции нерентабелен?
5. Как измениться общая стоимость выпускаемой продукции и
план её выпуска, если фонд времени шлифовального оборудования увеличить на 24
ч?
6. Целесообразно ли выпускать Д ценой 11ед., если нормы
затрат оборудования составляют 8,2 и 2 ед.?
Решение:
1. Подготовим форму для ввода условий
2. В задаче оптимальные значения вектора Х=(Х1,
Х2 ,Х3, Х4) будут
помешены в ячейки B3:Е3, оптимальное значение целевой функции-в ячейке F4.
Введена форма для ввода данных.
3. Введём исходные данные в созданную форму.
Данные
введены.
4. Введём зависимость для целевой функции.
·
Курсор в F4.
·
Курсор на кнопку Мастер функций.
·
Щелчок левой
кнопкой мыши. На экране диалоговое окно Мастер
функций шаг 1 из 2.
·
Курсор в окно Категория на категорию Математические.
·
Щелчок левой
кнопкой мыши.
·
Курсор в окно Функции на СУММПРОИЗВ.
·
Щелчок левой
кнопкой мыши.
·
В массив 1 ввести
адреса ячеек В3:Е3.
·
В массив 2 ввести
адреса ячеек В4:Е4.
·
Готово.
5. Ведём зависимость для левых частей ограничений.
·
Курсор в F4.
·
Копировать в
буфер.
·
Курсор в F7.
·
Вставить из
буфера.
·
Курсор в F8.
·
Вставить из
буфера.
·
Курсор в F9.
·
Вставить из
буфера.
На этом ввод зависимостей закончен.
Вводиться функция для вычисления целевой функции.
Запуск Поиска
решения.
После
выбора команд Сервис=>Поиск решения
появится диалоговое окно Поиск решения
В
диалоговом окне Поиск решения есть
три основных параметра:
·
Установить целевую ячейку
·
Изменяя ячейки
·
Ограничения
6.
Назначение
целевой функции.
·
Курсор в поле
«Установить целевую ячейку».
·
Ввести адрес $F$4.
·
Вести направление
целевой функции: Максимальному значению.
Вести адреса искомых переменных:
·
Курсор в поле
«Изменяя ячейки».
·
Ввести адреса B$3:E$3
7.
Ввод ограничений.
·
Курсор в поле
«Добавить». Появиться диалоговое окно Добавление
ограничений.
·
В поле «Ссылка на
ячейку» ввести адрес $F$7.
·
Ввести знак
ограничения <=.
·
Курсор в правое
окно.
·
Ввести адрес $H$7.
·
Добавить. На
экране опять диалоговое окно Добавление
ограничения.
·
Ввести остальные
ограничения.
·
После ввода
последнего ограничения ввести ОК.
На
экране появится диалоговое окно Поиск
решения с введёнными условиями.
8. Ввод параметров для решения ЗЛП.
·
Открыть окно Параметры поиска решения.
·
Установит флажок Линейная модель, что обеспечивает
применения симплекс-метода.
·
Установить флажок
Неотрицательные значения.
Ввод
правых и левых частей ограничений.
Введены все условия для решения задачи.
·
ОК. (на экране
диалоговое окно Поиска решения).
·
Выполнить. ( На
экране диалоговое окно Результаты поиска
решения).
Ввод параметров.
Результаты поиска решения.
Решение найдено.
|
Microsoft
Excel 10.0 Отчет по результатам
|
|
|
|
|
|
Рабочий
лист: [Книга 2.xls]Лист3
|
|
|
|
|
|
Отчет
создан: 10.10.2008 12:34:42
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Целевая
ячейка (Максимум)
|
|
|
|
|
|
|
Ячейка
|
Имя
|
Исходное
значение
|
Результат
|
|
|
|
|
$F$4
|
коэф.
в целевой функции целевая функция
|
0
|
965
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Изменяемые
ячейки
|
|
|
|
|
|
|
Ячейка
|
Имя
|
Исходное
значение
|
Результат
|
|
|
|
|
$B$3
|
значение
Х1
|
0
|
70
|
|
|
|
|
$C$3
|
значение
Х2
|
0
|
135
|
|
|
|
|
$D$3
|
значение
Х3
|
0
|
0
|
|
|
|
|
$E$3
|
значение
Х4
|
0
|
0
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ограничения
|
|
|
|
|
|
|
Ячейка
|
Имя
|
Значение
|
Формула
|
Статус
|
Разница
|
|
|
$F$7
|
Токарное
левая часть
|
275
|
$F$7<=$H$7
|
не
связан.
|
25
|
|
|
$F$8
|
Фрезерное
левая часть
|
70
|
$F$8<=$H$8
|
связанное
|
0
|
|
|
$F$9
|
Шлифовальное
левая часть
|
340
|
$F$9<=$H$9
|
связанное
|
0
|
|
|
$F$7
|
Токарное
левая часть
|
275
|
$F$7<=$H$7
|
не
связан.
|
25
|
|
|
$F$8
|
Фрезерное
левая часть
|
70
|
$F$8<=$H$8
|
связанное
|
0
|
|
|
$F$9
|
Шлифовальное
левая часть
|
340
|
$F$9<=$H$9
|
связанное
|
0
|
|
|
|
|
|
|
|
|
В отчёте по результатам
содержаться оптимальные значения переменных Х1, Х2, Х3, Х4, которые
соответственно равны 70,135,0,0. значение целевой функции -965, а так же левые
части ограничений.
|
Microsoft
Excel 10.0 Отчет по устойчивости
|
|
|
|
|
|
|
Рабочий
лист: [Книга 2.xls]Лист3
|
|
|
|
|
|
|
|
Отчет
создан: 10.10.2008 12:35:51
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Изменяемые
ячейки
|
|
|
|
|
|
|
|
|
|
|
Результ.
|
Нормир.
|
Целевой
|
Допустимое
|
Допустимое
|
|
|
|
Ячейка
|
Имя
|
значение
|
стоимость
|
Коэффициент
|
Увеличение
|
Уменьшение
|
|
|
|
$B$3
|
значение
Х1
|
70
|
0
|
8
|
1E+30
|
5,5
|
|
|
|
$C$3
|
значение
Х2
|
135
|
0
|
3
|
11
|
3
|
|
|
|
$D$3
|
значение
Х3
|
0
|
-12,5
|
1,999999999
|
12,5
|
1E+30
|
|
|
|
$E$3
|
значение
Х4
|
0
|
-5,5
|
1
|
5,5
|
1E+30
|
|
|
|
|
|
|
|
|
|
|
|
|
Ограничения
|
|
|
|
|
|
|
|
|
|
|
Результ.
|
Теневая
|
Ограничение
|
Допустимое
|
Допустимое
|
|
|
|
Ячейка
|
Имя
|
значение
|
Цена
|
Правая
часть
|
Увеличение
|
Уменьшение
|
|
|
|
$F$7
|
Токарное
левая часть
|
275
|
0
|
300
|
1E+30
|
25
|
|
|
|
$F$8
|
Фрезерное
левая часть
|
70
|
0
|
70
|
1E+30
|
0
|
|
|
|
$F$9
|
Шлифовальное
левая часть
|
340
|
0
|
340
|
1E+30
|
0
|
|
|
|
$F$7
|
Токарное
левая часть
|
275
|
0
|
300
|
1E+30
|
25
|
|
|
|
$F$8
|
Фрезерное
левая часть
|
70
|
6,5
|
70
|
0
|
70
|
|
|
|
$F$9
|
Шлифовальное
левая часть
|
340
|
1,5
|
340
|
0
|
270
|
|
|
|
|
|
|
|
|
|
|
|
Задача №2
В
портфеле ценных бумаг содержаться 2 вида бумаг: бумаги (А и В) каждая из бумаг
обладает своей доходностью и своим риском. Необходимо сформулировать
оптимальный портфель ценных бумаг с точки зрения минимизации общего выроста
портфеля и обеспечения обязательной доходности.
Опишем математическую модель задачи:
Пусть
Х1-удельный вес бумаг А в портфели; Х2-удельный вес бумаг В портфели. Пусть m1 доход бумаг А, пусть m2 доход бумаг В, пусть 
-доход портфеля в целом. Пусть 
-риск бумаг А, 
-риск бумаг В, 
-риск в целом, и r-коэффициент между доходами бумаг А и В.
r =0,18
Математическая модель задачи имеет вид:
Решение:
1. Для задачи подготовим форму для ввода условий.
2. В наше задаче Х1- удельный вес бумаг А,
Х2 – удельный вес бумаг В.
Оптимальное значения
вектора 
=(х1, х2,) будут помещены в
ячейках А3:В3, оптимальное значение целевой функции – в ячейке D4.
3. Введём исходные данные в созданную форму.
Введена форма для ввода
данных.
Данные введены.
4. Введём зависимость для целевой функции.
·
Курсор в D4.
·
Курсор на кнопку Мастер функций.
·
Щелчок левой
кнопкой мыши. На экране диалоговое окно Мастер
функций шаг 1 из 2.
·
Курсор в окно Категория на категорию Математические.
·
Щелчок левой
кнопкой мыши.
·
Курсор в окно Функции на СУММПРОИЗВ.
·
Щелчок левой
кнопкой мыши.
·
В массив 1 ввести
адреса ячеек A3:B3.
·
В массив 2 ввести
адреса ячеек A4:B4.
·
Готово.
5. Ведём зависимость для левых частей ограничений.
·
Курсор в D4.
·
Копировать в
буфер.
·
Курсор в C6.
·
Вставить из
буфера.
·
Курсор в C7.
·
Вставить из
буфера.
На этом ввод зависимостей закончен.
Вводиться функция для вычисления целевой функции.
Введены
зависимости для всех ограничений.
Запуск
Поиска решения.
После
выбора команд Сервис=>Поиск решения
появится диалоговое окно Поиск решения
В
диалоговом окне Поиск решения есть
три основных параметра:
·
Установить целевую ячейку
·
Изменяя ячейки
·
Ограничения
6.
Назначение
целевой функции.
·
Курсор в поле
«Установить целевую ячейку».
·
Ввести адрес $D$4.
·
Вести направление
целевой функции: Минимальному значению.
Вести адреса искомых переменных:
·
Курсор в поле
«Изменяя ячейки».
·
Ввести адреса А$3:В$3
7.
Ввод ограничений.
·
Курсор в поле
«Добавить». Появиться диалоговое окно Добавление
ограничений.
·
В поле «Ссылка на
ячейку» ввести адрес $С$6.
·
Ввести знак
ограничения <=.
·
Курсор в правое
окно.
·
Ввести адрес $D$6.
·
Добавить. На
экране опять диалоговое окно Добавление
ограничения.
·
Ввести остальные
ограничения.
·
После ввода
последнего ограничения ввести ОК.
На
экране появится диалоговое окно Поиск
решения с введёнными условиями.
8. Ввод параметров для решения ЗЛП.
·
Открыть окно Параметры поиска решения.
·
Установит флажок Линейная модель, что обеспечивает
применения симплекс-метода.
·
Установить флажок
Неотрицательные значения.
Введены все условия для решения задачи.
9.
Найдем целевую ячейку с помощью формулы
·
Ставим курсор в D3
·
Наводим курсор на
вставку функцию
·
На экране
появляется диалоговое окно мастер
функций.
·
Выбираем функцию Корень.
·
Появляется ввод данных для функции Корень
·
Вводим значения для функции корень
·
Нажимаем Ок.
·
Выводим отчёт.
Решение найдено.
Решение найдено.
|
Microsoft
Excel 10.0 Отчет по результатам
|
|
Рабочий
лист: [Книга1]Лист1
|
|
Отчет
создан: 10.10.2008 12:16:38
|
|
Целевая ячейка (Минимум)
|
|
|
|
|
|
|
Ячейка
|
Имя
|
Исходное значение
|
Результат
|
|
|
|
|
|
$D$3
|
|
10,42650469
|
10,42650469
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Изменяемые ячейки
|
|
|
|
|
|
|
Ячейка
|
Имя
|
Исходное значение
|
Результат
|
|
|
|
|
|
$A$2
|
X1
|
0,4
|
0,4
|
|
|
|
|
|
$B$2
|
X2
|
0,6
|
0,6
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ограничения
|
|
|
|
|
|
|
|
Ячейка
|
Имя
|
Значение
|
Формула
|
Статус
|
Разница
|
|
|
|
$C$5
|
|
22
|
$C$5>=$D$5
|
связанное
|
0
|
|
|
|
$C$6
|
|
1
|
$C$6=$D$6
|
не связан.
|
0
|
|
|
|
|
|
|
|
|
|
|
Содержимое протокола Результаты
В отчет включаются исходные и конечные
значения целевой и влияющих ячеек, дополнительные сведения об ограничениях. В отчете по результатам содержатся
оптимальные значения переменных 
, которые соответственно равны 0,4; 0,6; значение целевой
функции –10,4265.
|
Microsoft Excel 10.0 Отчет по
устойчивости
|
|
Рабочий лист: [Книга1]Лист1
|
|
|
Отчет создан: 10.10.2008 12:16:46
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Изменяемые ячейки
|
|
|
|
|
|
|
Результ.
|
Нормир.
|
|
|
|
Ячейка
|
Имя
|
значение
|
градиент
|
|
|
|
$A$2
|
X1
|
0,4
|
0
|
|
|
|
$B$2
|
X2
|
0,6
|
0
|
|
|
|
|
|
|
|
|
|
Ограничения
|
|
|
|
|
|
|
|
Результ.
|
Лагранжа
|
|
|
|
Ячейка
|
Имя
|
значение
|
Множитель
|
|
|
|
$C$5
|
|
22
|
0,753720156
|
|
|
|
$C$6
|
|
1
|
-6,155337207
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Содержимое протокола Устойчивость
Отчет, содержащий сведения о
чувствительности решения к малым изменениям в изменяемых ячейках или в формулах
ограничений.