Кореляционно-регресионный анализ в Excel и Mathcad: лабораторный практикум

82 Pages·2020·1.26 MB·Russian

by  Руденко Б. Д.

Министерство науки и высшего образования Российской Федерации
Сибирский государственный университет науки и технологий
имени академика М. Ф. Решетнева

Б. Д. Руденко

КОРЕЛЯЦИОННО-РЕГРЕСИОННЫЙ АНАЛИЗ
В EXCEL И MATHCAD

Лабораторный практикум для студентов бакалавриата
по направлению подготовки 35.03.02 «Технология
лесозаготовительных и деревоперерабатывающих производств»
всех форм обучения

Красноярск 2020
1
УДК 22.172(076.5)
ББК 519.2я73
Р83

Рецензенты:
кандидат технических наук, доцент В. Т. ИЗОТОВ
(ООО «Ермак-Т»);
кандидат технических наук, доцент Н. А. РОМАНОВА
(Сибирский государственный университет науки и технологий
имени академика М. Ф. Решетнева)

Печатается по решению редакционно-издательского совета университета

Руденко, Б. Д.
Р83 Кореляционно-регресионный анализ в Excel и Mathcad : лабора-
торный практикум / Б. Д. Руденко ; СибГУ им. М. Ф. Решетнева. –
Красноярск, 2020. – 82 с.

Цель лабораторного практикума – сформировать у студентов представ-
ление о реализации корреляционно-регрессионного анализа в процессе обра-
ботки данных в технологических дисциплинах по деревообработке, при ис-
пользовании таких распространённых программных продуктов как Excel
и MathCAD. Представлены теоретические сведения и описание алгоритма
работы по методике применения корреляционно регрессионного анализа,
в процессе получения математических зависимостей изучаемых вопросов.
Предназначено для бакалавров инженерно-технического и химического
профилей подготовки, также может быть полезно магистрантам, аспирантами
начинающим преподавателям.

УДК 22.172(076.5)
ББК519.2я73

© СибГУ им. М. Ф. Решетнева, 2020
© Руденко Б. Д., 2020
2
ОГЛАВЛЕНИЕ

Введение.................................................................................................. 4

1. Корреляционно-регрессионный анализ в Excel .......................... 6
1.1. Установка параметров линейной связи в Excel ........................ 6
1.2. Установление статистической значимости
и общего качества уравнения линейной
регрессии в Excel ........................................................................ 12
Контрольные вопросы и задания ...................................................... 17
Лабораторная работа 1. Установление статистической
значимости и общего качества уравнения
линейной регрессии ................................................................... 18

2. Работа с системой Mathcad ............................................................ 26
2.1. Основные возможности системы Mathcad ............................... 26
2.2. Главное меню системы .............................................................. 26
2.3. Начальные сведения о работе с системой ................................ 37
2.4. Входной язык системы Mathcad ................................................ 42
Контрольные вопросы и задания ..................................................... 49
Лабораторная работа 2. Использование ранжированных
переменных с применением Mathcad ....................................... 50

3. Корреляционно-регрессионный анализ в Mathcad ................... 57
3.1. Математические модели и статистические методы ................ 57
3.2. Корреляция между количественными признаками ................. 58
3.3. Корреляционный момент. Коэффициент корреляции ............ 58
3.4. Уравнение связи ......................................................................... 61
3.5. Определение параметров уравнения линейной связи ............. 62
3.6. Статистический анализ модели ................................................. 64
Контрольные вопросы и задания ..................................................... 66
Лабораторная работа 3. Математическая статистика I ............... 67
Лабораторная работа 4. Математическая статистика II .............. 73

Заключение ........................................................................................... 79

Библиографический список .............................................................. 80

Приложение. Перечень ключевых слов ............................................. 81

3
ВВЕДЕНИЕ

В настоящем учебном пособии рассматриваются две самые мас-
совые информационные системы: Excel и Mathcad, а также их приме-
нение для статистической обработке инженерных данных. Excel явля-
ется самой простой в использовании и широко распространенной сис-
темой. Mathcad по праву может называться современной, универсаль-
ной и массовой математической системой. Она позволяет выполнять
как численные, так и аналитические (символьные) вычисления, имеет
удобный математико-ориентированный интерфейс. Исключительно
велика роль таких систем в образовании. Они не только облегчают
решение сложных математических задач и снимают психологический
барьер в изучений математических методов, но и позволяют перейти
к реальному моделированию технических и технологических систем.
В пособии дано систематическое изложение основных базовых
статистических методов, используемых при обработке инженерных
данных. Приведены общая методология использования математиче-
ского инструментария в корреляционном и регрессионном анализе,
а также конкретное изложение основных соответствующих понятий
и методов. Отличительной особенностью пособия является соедине-
ние изучения математических методов и использования для их при-
менения электронных таблиц Ехсel и математической системы
Mathcad – систем, наиболее подходящих для статистических расчетов
и взаимно дополняющих друг друга.
В первую очередь пособие будет полезно студентам, изучаю-
щим математические методы при обработке инженерных данных,
математическую обработку массивов данных, аспирантам любой спе-
циальности, а также специалистам по прикладным исследованиям
технологических и технических процессов.
Данные методические указания предназначены для выполнения
лабораторных работ по дисциплине «Пакеты прикладных программ».
Дисциплина состоит из трех модулей: «аналитический», «вычисли-
тельный» и «инженерное проектирование».
При оформлении лабораторных работ, используя Microsoft
Word, сделать заголовок лабораторной работы, поместить и подпи-
сать полученные результаты. Каждая работа защищается преподава-
телю после оформления, для этого используются вопросы, помещен-
ные в конце каждой лабораторной работы.
Дисциплина изучается во втором семестре, форма промежуточ-
ной аттестации – зачёт.
4
Цель работы – дать сведения об основных процедурах, которые
позволяют произвести статистическую обработку данных и некото-
рые практические навыки решения таких задач.
Выполнение лабораторных работ способствует формированию
у студентов следующих компетенций:
– способности использовать пакеты прикладных программ для
расчета технологических параметров процессов и оборудования;
– способности осуществлять поиск, хранение, обработку и ана-
лиз информации из различных источников и баз данных, представ-
лять ее в требуемом формате с использованием информационных,
компьютерных и сетевых технологий.
Освоенные компетенции в дальнейшем обеспечат уверенность
студентов при дипломном проектировании, а также при выполнении
практических инженерных и технологических задач.
Перед выполнением лабораторных работ студентам рекоменду-
ется ознакомиться с перечнем ключевых слов (см. приложения).

5
1. КОРРЕЛЯЦИОННО-РЕГРЕССИОННЫЙ АНАЛИЗ
В EXCEL

1.1. УСТАНОВКА ПАРАМЕТРОВ ЛИНЕЙНОЙ СВЯЗИ В EXCEL

Для установки параметров (коэффициентов) линейной связи
введем данные рядов X и Y, наблюдаемую статистику независимой
и зависимой переменной, в ячейки, адреса которых представлены
на рис. 1.1.
Данные берем из работы 1, 1 и 2 столбик (диаметр и длина).

Рис. 1.1. Ввод данных независимой
и зависимой переменной

Будем определять параметры линейной парной регрессии вида

Y = a + bX + e. (1.1)

Рассмотрим разнообразные возможности решения этого вопро-
са, которые могут потребоваться в зависимости от ситуации.
В ячейке С3 подсчитаем среднее значения ряда X. Для этого вы-
зовем функцию СРЗНАЧ (панель статистических функций) и на поя-
вившейся панели введем адрес диапазона ряда X – А2:Ф10 (рис. 1.2).
В ячейку D4 введем среднее значение ряда Y, проделав анало-
гичную операцию, введя на панели функции СРЗНАЧ адрес ряда Y –
В2:В10.
6
В ячейке С6 подсчитаем стандартное отклонение ряда X, вызвав
функцию СТАНДОТКЛОН и введя диапазон ряда X, как показано
на рис. 1.3.

Рис. 1.2. Панель функции СРЗНАЧ

Рис. 1.3. Панель функции СТАНДОТКЛОН

В ячейку D6 введем стандартное отклонение ряда Y, проделав
аналогичную операцию.
В ячейке С8 подсчитаем коэффициент корреляции. Для этого
существует функция КОРРЕЛ, в окнах панели которой необходимо
ввести адреса диапазона как ряда X, так и ряда Y – (рис. 1.4).

7
Угловой коэффициент связи (параметр b) равен

b = r ⋅ σ / σ , (1.2)
y x

где r – коэффициент корреляции; σ , σ – стандартные отклонения со-
y x
ответственно зависимой и независимой переменной.

Рис. 1.4. Панель функции КОРРЕЛ

Параметр a устанавливается по формуле

a = ⎯Y – b ⋅⎯X, (1.3)

где ⎯Y и ⎯X – средние значения зависимой и независимой переменной.
Тогда параметры b и a, учитывая адреса показателей (см. рис. 1.4),
установим по формулам

b = С8*D6/C6;
a = D3 – C11*C3.

Результат представлен на рис. 1.5.

Рис. 1.5. Расчет параметров линейной
парной регрессии
8
Параметры линейной парной регрессии можно рассчитать и сра-
зу. Для этого в Excel существует функции НАКЛОН и ОТРЕЗОК.
Функция НАКЛОН служит для определения углового коэффициента
связи (параметра b), а функция ОТРЕЗОК – для определения свобод-
ного члена уравнения. На панели соответствующих функций необхо-
димо ввести адреса диапазонов Y и X (см. рис. 1.6 и 1.7).

Рис. 1.6. Панель функции НАКЛОН –
углового коэффициента связи

Рис. 1.7. Панель функции ОТРЕЗОК – свободного члена
уравнения (параметра а)

Кроме перечисленных возможностей, существует еще и сле-
дующая. Построим график по имеющимся данным. Чтобы ось X от-
ражала фактические данные, выберем тип диаграммы (рис. 1.8).
На построенной диаграмме выделим график функции, щелкнув
по ней левой кнопкой мыши. Выделение обозначится маркерами на
9
функции. Нажав правую кнопку мыши, выведем контекстно-
зависимое меню (рис. 1.9).

Рис. 1.8. Панель выбора
вида диаграммы

Рис. 1.9. Контекстно-зависимое меню
при выделенном ряде данных
10