ВВЕДЕНИЕ. Сущность регрессионного анализа и цели работы

ПРИМЕНЕНИЕ ПРОГРАММЫ MS EXCEL

ДЛЯ РЕГРЕССИОННОГО АНАЛИЗА

ПРОИЗВОДСТВЕННЫХ ПРОЦЕССОВ

 

Учебно-методическое пособие

 

Киров

УДК 004.9:519.2:(621+669)

В 926

Допущено к изданию методическим советом факультета автоматизации машиностроения ФГБОУ ВПО «ВятГУ» в качестве учебно-методического пособия для студентов направлений 151900.62, 151900.68 «Конструкторско-технологическое обеспечение машиностроительных производств», 150700.62 «Машиностроение» всех профилей подготовки, всех форм обучения

 

Рецензент

заведующий кафедрой ТМ ФГБОУ ВПО «ВятГУ», профессор,

кандидат технических наук Е.А. Куимов

 

 

Вылегжанин А. Ю.

В 926 Применение программы MS Excel для регрессионного анализа

производственных процессов/ А. Ю. Вылегжанин, С.П. Грачёв,

И.М. Певзнер, М.З. Певзнер. – Киров : ФГБОУ ВПО «ВятГУ»,

2014. – 33 с.

УДК 004.9:519.2:(621+669)

 

Учебно-методическое пособие предназначено для студентов направлений 151900.62, 151900.68 «Конструкторско-технологическое обеспечение машиностроительных производств», 150700.62 «Машиностроение» по дисциплинам «Прикладная статистика», «Основы научных исследований», «Статистический контроль качества», «Методология научных исследований».

 

Тех. редактор А.В. Куликова

 

© ФГБОУ ВПО «ВятГУ», 2014

ОГЛАВЛЕНИЕ

 

ВВЕДЕНИЕ. Сущность регрессионного анализа и цели работы .......4

1. Подготовка к проведению работы. …….................................6

2. Метод наименьших квадратов при построении регрессионной модели (использование надстройки MS Excel «Поиск решения»)……

3. Однофакторный регрессионный анализ…………………………8

3.1. Инструмент анализа «Регрессия»…………………… …..8

3.1.1. Основные опции инструмента «Регрессия»…………… ..8

3.1.2. Опции инструмента «Регрессия», связанные с оценкой

дисперсии относительно рассматриваемой математической модели……..12

3.2. Статистические функции MS Excel, основанные на линейной

регрессии………………………………………………………………………12

3.3. Статистические функции MS Еxcel, основанные на

экспоненциальной регрессии………………………………………………16

3.4. Графические опции, предназначенные для анализа регрессионной модели….................................................................................................19

3.4.1. Построение регрессионной зависимости и освоение способов

её аппроксимации……………………………………………

2.4.2. Выбор вида аппроксимирующей функции ……………………

3.4.3. Анализ влияния степени полинома на точность аппроксимации………………………………………

4. Многофакторный регрессионный анализ……………………

4.1. Анализ и оценка достоверности модели в рамках инструмента «Регрессия»………………………………………………………………..

4.2. Оценка значимости коэффициентов регрессии и совершенствование математической модели…………………………………………

4.3. Статистические функции MS Еxcel, предназначенные для

многофакторного регрессионного анализа………………………………..

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

ВВЕДЕНИЕ. Сущность регрессионного анализа и цели работы

Методы статистического анализа являются непременным атрибутом многочисленных международных стандартов по управлению качеством продукции и их отечественных аналогов [1, 2]. Регрессионный анализ является важнейшим методом многомерного статистического анализа – обширной области прикладной статистики, занимающейся установлением взаимосвязи случайных величин, в частности параметров технологического процесса с характеристиками качества продукции, технико-экономическими показателями и т.д. В широком понятии этого слова регрессионный анализ состоит из трёх основных тесно взаимосвязанных компонентов: корреляционного, дисперсионного и собственно регрессионного анализов [3, 4]. Предметом настоящей лабораторной работы является собственно регрессионный анализ (РА) безотносительно двух других видов анализа.

Регрессионный анализ объединяет круг задач, связанных с построением функциональных зависимостей между двумя группами числовых («интервальных» или «относительных») переменных: факторов х_i и значений функции («отклика»)у.

Предполагается, что наблюдаемое в опыте значение отклика у_j состоит из двух частей [3, 4]:

- одна из них независима от факторов х_i (случайна по отношению к х_i) и обозначается ε. Случайное слагаемое ε выра­жает либо внутренне присущую отклику изменчивость, либо влияние на него неучтённых факторов, либо то и дру­гое вместе;

- другая часть является функцией, закономерно зависящей от одного фактора y = f(х) (однофакторный) или (в общем случае) от нескольких факторов y = f(х_i) - многофакторный РА.

Во всех случаях задача РА состоит в установлении зависимости у=f(х_i) + ε, причём необходимо, чтобы эта зависимость наилучшим образом описывала экспериментальные точки (возможно более близко к ним приближалась). Функция f(х_i) может выражаться линейной (линейный РА), полиномом второй и более высокой степени или иной зависимостью (нелинейный РА).

Применяемый здесь классический РА основан на двух допущениях [4]:

а) все опыты были проведены независимо друг от друга;

б) дисперсия случайных составляющих ε остава­лась неизменной во всех опытах (свойство гомоскедастичности).

Важным достижением человечества в последние десятилетия является освоение информационных технологий, позволяющее внедрить давно разработанные, но требующие достаточно трудоёмких вычислений статистические методы в реальную практику производственного предприятия. Программа MS Excel кроме достаточно больших возможностей среди разработанных к настоящему времени различных статистических программных средств имеет то несомненное преимущество, что является непременным атрибутом любого общедоступного офиса [5, 6]. Следовательно, освоение её статистических функций и инструментов, предназначенных в частности для РА, является необходимым и в тоже время наиболее выполнимым условием обеспечения конкурентоспособности продукции.

С целью адекватного (возможно более точного) описания экспериментальных точек искомой регрессионной зависимостью, как правило[1], применяется метод наименьших квадратов (МНК) – краеугольный камень в основании современной прикладной статистики. Этот метод, разработанный великим немецким математиком К. Ф. Гауссом, основан на минимизации суммы квадратов отклонений экспериментальных точек от линии регрессии.

В настоящей лабораторной работе будут рассмотрены доступные в программе MS Excel аналитические и графические способы РА, а также способы совершенствования и проверки достоверности полученной регрессионной модели. В первой части работы (глава 2) рассмотрен способ последовательной реализации МНК при построении регрессионной модели средствами линейного программирования[2] с помощью надстройки MS Excel «Поиск решения». В последующих частях та же задача решается исключительно быстро с использованием инструмента анализа «Регрессия» и стандартных статистических функций. При этом в основе автоматически производимых расчётов лежит тот же МНК, что и в гл. 2, но они производятся «скрытно» от глаз пользователя и не требуют специального программирования. Графические опции MS Excel позволяют очень наглядно представить результаты каждого анализа.

Таким образом, данная лабораторная работа позволяет закрепить теоретические знания и получить всесторонние практические навыки по использованию программы MS Excel для РА.