Липецкий государственный технический университет
Кафедра Информатики
полное или сокращенное название кафедры
КУРСОВАЯ РАБОТА
ТЕМА: РЕШЕНИЕ ЗАДАЧ В MS EXSEL
По Информатике
наименование дисциплины
Студент Ноготков В.В.
подпись, дата фамилия, инициалы
Группа ТВ-18
Руководитель
К.т.н. доцент Суслова С.А
ученая степень,ученое звание подпись, дата фамилия, инициалы
Липецк 2018
Содержание
Задание №1 Решение задачи линейной оптимизации………………………3
Исходные данные…………………………………………………………………….............3
Ход выполнения работы……………………………………………........................................3
Результаты решения задачи…...…………………………………………………………......6
Задание №2 Множественная регрессия……………………………………….7
Задание №1
Решение задачи линейной оптимизации (производственный план)
Исходные данные
Производитель элементов центрального отопления изготавливает радиаторы четырех моделей: А, В, С, D. Ограничение на их производство обусловлено имеющимися фондами рабочей силы и стального листа. Необходимые данные приведены в таблице.
| Показатели | Модели радиаторов | Фонды | |||
| А | В | С | D | ||
| Необходимое количество рабочей силы (чел. ч) | 0,5 | 1,5 | 1,5 | ≤500 | |
| Необходимое количество стального листа (м3) | ≤2500 | ||||
| Прибыли от продажи 1 радиатора (тыс. р.) | 12,5 |
Определить такой план выпуска радиаторов, при котором прибыль от их реализации будет максимальной.
Ход выполнения работы
Математическая модель:
Для составления модели обозначим ежемесячный выпуск радиаторов видов А, В, С и D соответственно через х1, х2, х3, х4.
Тогда ограничения модели можно записать в виде:
Целевая функция:
Ограничения (1) и целевая функция (2) составляют математическую модель задачи. Подготовил начальный рабочий лист с исходными данными и формулами (см. Рисунок 4).
1. Введём следующие формулы:
- левые части ограничений:
D9:=D5*H9+E5*I9+F5*J9+G5*K9,
D10: =H9*D6+I9*E6+J9*F6+K9*G6.
- целевая функция:
B12: = D7*H9+E7*I9+J9*F7+K9*G7;
- разности:
F9: = H5-D9,
F10: = H6-D10,
2. В ячейки H9, I9, J9 и K9 введём 0 – начальные значения неизвестных х1, х2, х3, x4.
Риc.4 Рабочий лист с исходными данными и формулам
3. Затем активизируем окно диалога «Параметры поиска решения» в меню «Данные», в котором укажем следующие значения (см. Рисунок 5):
– ссылка на ячейку, содержащую формулу расчёта целевой функции;
– ссылка на диапазон ячеек, в которых содержатся неизвестные;
– система ограничений (см. Рисунок 5).
Рис.5 Окно диалога "Параметры поиска решения"
4. Для ввода системы ограничений используем кнопку Добавить
(см. Рисунок 6).
Рис.6 Окно диалога "Добавление ограничения"
5. Завершается ввод ограничения щелчком по кнопке «Найти решение»(в более ранних версиях кнопка «Выполнить »), и по окончании расчёта появляется окно «Результаты поиска решения», в котором сообщается, что решение найдено (см. Рисунок 7).
Рис.7 Окно диалога "Результаты поиска решения
6. Анализ полученных результатов (см. Рисунок 8) показывает:
При наличии данных ограничений прибыль от реализации радиаторов будет максимальной в том случае, если изготавливаться будут радиаторы двух модельных видов:A и C в количестве 400 и 150 соответственно.
Рис. 8 Рабочий лист Excel с решением задачи.
Рис. 9 Отчет о результатах.
Задание №2
Множественная регрессия
Методические указания к выполнению работы
 |
1. Я построил корреляционную матрицу, используя функцию " Данные. Анализ данных. Корреляция " табличного процессора MS Excel (Рис. 1)
Рис. 1. Окно диалога "Данные. Анализ данных. Корреляция".
 |
2. После чего я получил корреляционную матрицу (Рис. 2).
3. Из матрицы следует, что наибольший коэффициент корреляции между аргументами х1 и х2, так как rx1x2 = 0,477.
Для дальнейшего рассмотрения оставляем фактор х2,так как он меньше коррелирует с фактором х3 (r х2х3 = 0,324 < r х1х3= 0,435).
Таким образом, далее будет строиться регрессия следующего вида:
ŷ = а0 + а1 · х2 + а2 · х3.
 |
4. Для построения уравнения линейной регрессии я использую функцию " Сервис. Анализ данных. Регрессия " (Рис. 3).
Рис. 3. Окно диалога "Данные. Анализ данных. Регрессия".
5. Далее я задаю соответствующие диапазоны данных в диалоговом окне и получите таблицы А, Б, В (рис. 4–6).
 |
Таблица А
Рис. 4. Регрессионная статистика
 |
Таблица Б
Рис. 5. Дисперсионный анализ
Таблица В
Рис. 6. Коэффициенты уравнения
Рис. 6. Коэффициенты уравнения
6. Из таблицы В следует, что уравнение регрессии имеет вид:
ŷ = 85,411 + 0,4362· х2 +(0,1779)· х3.
7. Коэффициент множественной корреляции определяется из таблицы А:
8. Проверка значимости уравнения регрессии основана на использовании F -критерия Фишера. Фактическое значение критерия берётся из таблицы Б, то есть Fфакт =2,3194.
9. Для определения табличных значений используйте встроенную функцию
{=FРАСПОБР(α; k1; k2)}.
10. Задаю параметры k1 = 2; k2 = 29-2 -1= 26; α = 0,05 и α = 0,01. Получаю Fфакт.0,05 = 3,369, Fфакт.0,01 = 5,526. Откуда следует, что уравнение регрессии значимо и при α = 0,05 и при α = 0,01.