ОБРАБОТКА ЭД в EXCEL


Чтобы посмотреть этот PDF файл с форматированием и разметкой, скачайте его и откройте на своем компьютере.
3

ОБРАБОТКА
ЭКСПЕРИМЕНТАЛЬНЫХ
ДАННЫХ

В MS EX
C
EL













Хабаровск


2012

4

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

Федеральное государственное бюджетное учреждение

высшего профессионального образования

«Тихоокеанский государственный университет»









ОБРАБОТКА ЭКСПЕРИМЕН
ТАЛЬНЫХ ДАННЫХ

В MS EX
C
EL

Методические указания
к

выполнени
ю

лабораторных работ


для ст
у
дентов
дневной формы обучения













Хабаровск

Издательство ТОГУ

20
1
2

5

УДК 519.86R.6:004.67(076)


Обработка

экспериментальных данных

в MS Ex
c
el

:
м
етодические указания
к

выполне
нию

лабораторных работ для студентов дневной формы обучения /
сост.
Е
.
Г
.
Ага
пова
,
Е. А.
Битехтина
.



Хабаровск : Изд
-
во Тихоокеан. гос. ун
-
та, R0
1
2
.



3
2


с.





Методические указания содержат описание
наиболее распространенных
методов обработки результатов наблюдений
,
решений
основных задач парного
и множественного регрессионного анализа
.
Все методы снабжены подробными
комментариями и рассматриваются на простых и конкретных примерах, что
дает возможност
ь использовать
работу в качестве справочника по приведенным
методам
.

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

дисциплин

«Обра
ботка
экспериментальных данных

на ЭВМ
»
, «Математическая статистика»,
«Эконометрика»
.










Печатается в соответствии с решениями кафедры «Прикладная математика»
и методического совета факультета
компьютерных и фундаментальных наук
.










Тихоокеански
й государственный университет, R0
1
2



6


ОБРАБОТКА ЭКСПЕРИМЕН
ТАЛЬНЫХ ДАННЫХ

В MS EX
C
EL


Методические указания

к

выполнени
ю

лабораторных работ

для студентов дневной формы обуч
ения



Агапо
ва
Елена

Григорье
вна

Битехтина Екатерина Андреевна





Главный редактор
Л. А. Суевалова

Редактор
Л. С. Бакаева




Подписано в печать Формат 60х84 1/16.

Бумага писчая.
Гарнитура «Таймс».
Печать цифровая.

Усл. печ. л.

Тираж
1
00

экз. Заказ








Издательство Тихоокеанского государственного университета.

68003
5, Хабаровск, ул. Тихоокеанская, 136.

Отдел

оперативной полиграфии


издательства


Тихоокеанского

государственного

университета.

680035, Хабаровск, ул. Тихоокеанская, 136.

7

ОБЩИЕ СВЕДЕНИЯ


Методы обработки экспериментальных данных
(
ОЭД) начали
разрабатываться более двух веков тому назад в связи с необходимостью
решения практических задач по агробиологии, медицине, экономике,
социологии. Полученные при этом результаты составили фундамент такой
научной дисциплины, как математическая ст
атистика. Первыми работами,
положившими начало статистике как самостоятельной науке, были книги
Дж.

Граунта «Естественные и политические наблюдения, перечисленные в
прилагаемом оглавлении и сделанные над бюллетенями смертности …»
(1662

г.) и У.

Пети «Два о
черка по политической арифметике, относящиеся к
людям, зданиям, больницам Лондона и Парижа» (168R г
.).


Современный уровень ‒
естественнонаучного

эксперимента характеризу
-
ется большими потоками информации. При этом визуальный просмотр данных,
не говоря уже

об анал
изе, не
возможен без применения ЭВМ.

О
бработка
результатов экспериментов предполагает знание основных понятий и методов
теории вероятностей и математической статистики. Выявление характерных
классов задач в
ОЭД

и стандартных методов их решения
позво
ля
ет выделить
обработку результатов экспериментов из многообраз
ия задач прикладной
статистики.


Как правило, основным подходом в решении многих задач является
м
етод
н
аименьших
к
вадратов (МНК) в его различных модификациях. Однако МНК
эффективно работает тол
ько для линейных моделей, а на практике встречаются
ситуации, когда связь искомого параметра с измеряемой величиной сугубо
нелинейная. В этом случае применяют
н
елинейный МНК или другие методы
обработки. Знакомство со всеми этими методами расширяет арсенал
средств,
находящихся в распоряжении обработчика, что особенно важно в сложных
случаях, например, когда измерения производятся при воздействии большого
числа факторов, мешающих их проведению.


Появление электронных таблиц (табличных процессоров) привело к т
ому,
что статистические методы, ранее доступные лишь узкому кругу математиков,
стали
использоваться

широк
и
м круг
ом

специалистов разных областей.
Дальнейшее развитие программного обеспечения привело к созданию
большого количества прикладных пакетов по стат
истике.
Удобной
универсальной вычислительной средой для решения задач
ОЭД
является

табличный процессор

MS

Excel
.
Поэтому основной целью данных
методических указаний является

изложение (в форме лабораторных работ)
численных методик решения основных задач парного и множественного
регрессионного
анализа в вычислительной среде
табличного процессора

MS

Excel
.

Жирным шрифтом в работе выделены команды меню, названия панелей
инструмен
тов и диалоговых окон и их элементов.


8

Тема
1
.
ОЦЕНКА ПОКАЗАТЕЛЕЙ КАЧЕСТВА ОБЪЕКТА

ПО РЕЗУЛЬТАТАМ ЭКСПЕРИМЕНТОВ


В этой теме р
ассмотр
ены

основны
е

задач
и

математической статистики о
первичной статистической обработке данных
.

В статистике, как правило,
статистические данные являются результатами наблюдений над некоторой
случайной величиной
X
.

Пример
.

В результате эксперимента были получены значения
переменн
ой

X

(табл.

1.1).


Таблица 1.1

i

1

2

3

4

5

6

7

8

9

10

X

14
,85

14,80

14,84

14,81

14,63

14,81

14,80

14,85

14,84

14,80



Л
абораторная работа

1
.1


СТАТИСТИЧЕСКИЕ ХАРАК
ТЕРИСТИКИ СЛУЧАЙНЫХ
ВЕЛИЧИН


Цель работы
.
Н
аучиться вычислять оценки основных числовых
характеристик по экспериментальным данным.

Статистическая ф
ункция
СЧЕТ

используется
д
ля определения числа
значений
(
рис.

1.
1
)
.



Рис. 1.
1
.


Диапазон ячеек указы
ва
ется адресами первой и последней ячейки

данных
,
записанными через двоеточие
, например

В6:В15

(рис
.

1.
2
)
.

Ф
ункци
я

СРЗНАЧ

рассчитывает

среднее значение выборки.

Функция

СТАНДОТКЛОН



стандартное отклонение выборки
.

Аргументами этих функций служит все тот же диапазон ячеек.

С
татистическая

ф
ункция
СТЬЮДРАСПОБР

используется
д
ля нахождения
коэффициента Стьюдента
.

Этот коэффициент зависит от
вероятности ошибки
(при
обычно

задаваемой надежности
95

%

вероятность

ошибки составляет
5

%
)
и от числа степеней свободы
n‒1
.

Также это значение можно найти по таблице
9

к
ритически
х

значени
й

t
-
критерия
(прил. 1).



Рис. 1.
2
.



Для нахождения доверительного интервала используется
обычная
формула умножения

«
СТЬЮДРАСПОБР
*
Х
ср
»
.


Решение.

Д
анны
е из

примера в
ведем в столбец
B

(табл.

1
.2
).

В столбцах

D

и

Е



подсказки характеристик
, которые
мы будем рассчитывать.
Используя
приведенные выше функции, в столбец
F

поместим результаты
.

По найденному
значению ячейки
F11

о
кончательный результат
доверительного интервала

можно записать так: с
95 %
-
н
ой надежностью
Х 14,8
1
±0,0
46
. В заключение
вычислим

относительную ошибку определения

доверительного интервала
:



ДИ/Х
ср

(формула:
«
=F11/F7
»
). Значение относительной ошибки обычно
выражают в процентах,
в нашем случае

0,3

%.


Таблица 1.
2




Л
абораторная работа

1.2


ОЦЕНКА ПАРАМЕТРОВ И ОПРЕДЕЛЕНИЕ

ЗАКОНА РАСПРЕДЕЛЕНИЯ


Пример.

Исследуется случайная величина
ܺ



число пр
авонарушений в
течение одних суток в некотором городе
N
. Получены данные за первые 150
10

суток года
.

3

5

4

4

5

8

2

3

1

6

6

1

2

5

5

4

4

4

3

4

5

5

2

2

3

4

3

2

4

4

8

10

1

4

3

3

2

5

7

5

3

6

7

5

6

1

4

6

4

5

4

5

7

6

5

3

5

5

8

7

7

5

5

4

5

3

3

6

3

5

2

2

2

6

2

5

6

8

4

4

8

3

6

4

4

5

5

7

5

5

3

5

4

5

5

4

7

6

9

3

3

5

6

6

3

4

5

2

6

7

5

5

4

2

5

4

2

6

2

7

5

5

8

5

3

5

2

5

3

7

4

6

3

6

0

4

4

4

5

2

7

7

3

1

1

3

6

5

7

6


Цель работы
.
Требуется провести первичную с
татистическую обработку
данных
,

проверить гипотезу о виде
распределения случайной величины с
помощью критерия согласия Пирсона.

Расчетные соотношения.

Данная задача решается с помощью
статистических процедур
Анализа данных
и

статистических функций
библиотеки встроенных функций
MS

Excel
. Пр
иведем алгоритм решения
задачи.

1.
Ввод данных
.

В диапазон ячеек
А1:А
N

вв
ести

выборочные значения










.

2.
Построение вариационного ряда
.
Скопировать содержимое ячеек
А1:А
N

в
ячейки
В1:В
N
.

Упорядочить выборочные значения, используя кнопку
сортировки по возрастанию.

3.
П
остроение статистического ряда
выборки
. В ячейки
С1:СК

ввести
k

различных выборочных значений.

В меню
Данные
выделить строку
Анализ
данных
,

выделить
процедуру
Гистограмма
.
В поле
Входной интервал
диалогового окна
Гистограмма
ввести
ссылку

на диапазон
А1:А
N
.

В поле
Интервал карманов

ввести ссылку на диапазон
С1:СК
.

Активизировать поле
Выходной интервал
и в
вести

в это поле

ссылку


левая верхняя ячейка, в
которую будет введена таблица результатов решений.

Установить флажок
Вывод графика.

Составить табл
.

1
.3

стат
истического ряда по следующему

образцу:

Таблица 1
.3




различные
выборочные

значения

݊


частота выборочного
значения

݊



относительная частота
выборочного значения

݊




накопленная
относительная

частота


Первые столбцы заполнить копированием. Относительные и накопленные
частоты вычислить с использованием формул.

4.
Построение полигонов относительных и накопленных
относительных
частот.

11

Скопировать первый и третий столбцы табл
.

1.
3.

Выделить их.

Используя меню
Вставка
, примени
ть к выделенным числам средство

диаграммы
Точечная.
Полученный

график есть полигон относительных частот.

Если эти же действия
проделать с
первы
м

и четверты
м

столбц
ами

табл
.

1
.3
,

то получим

полигон
накопленных частот


сглажен
ный график эмпирической функции
распределения.

5.
Определение выборочных характеристик.

В меню
Данные
выделить
п
одменю
Анализ данных
,

выделить

п
роцедуру

Описательная статистика
, в

поле ввода
Входной интервал
в
вести ссылку на диапазон ячеек,
содержащий
статистические данные
А1:А
N
.

Установить флажок
Итоговая статистика.

Активизировать поле
Выходной интервал
, в
вести в это поле ссылку


л
евая
верхняя ячейка, в которую будет введена таблица результатов решений.

6.
Проверка гипотезы о виде распреде
ления случайной величины с помощь
ю

критерия согласия Пирсона
.

Заполнить табл
.
1.4
.

Таблица
1.4




различные
выборочные

значения

݊


частота
выборочного
значения

݌


теоретическая
вероятность
выборочного
значения



݊





݌


теоретическая
частота
выборочного
значения




݊



݊



݊




Первые столбцы заполнить копированием, а оставшиеся


вычисленными по
формулам значениями.


Если проверяется гипотеза

о распределении Пуассона
, то теоретические
вероятности
݌


вычислить с помощью функции
ПУАССОН










. Здесь





выборочное среднее, оно определяется в пункте 5,
0



параметр,
показывающий, что вычисляется вероятность того, что случайная величина,
распределенная по закону Пуассона, принимает значение


.


Если проверяется гипотеза

о биномиальном распределении

случайной
величины, то теоретические вероятности
݌


вычислить с помощью функции
БИНОМРАСП










, при этом вероятность успеха


в одном
испытании определить по формуле
ܲ








где





выборочное среднее.

В случае других распределений

воспользоваться справкой о
статистических функциях библиотеки встроенных функций

MS

Excel
.


Значение






݊



݊



݊







является наблюдаемым значением случайной величины






Число степеней
свободы этой случайной величины равно
ݎ

݇

ʹ

при проверке гипотезы о
распределении Пуассона и

ݎ

݇

͵
, если проверяется гипотеза о
биномиальном распределении. Критическое значение случайной величины





определить с помощью функции


кр

ХИRОБР





,

где



уровень
12

значимости. Полученное наблюдаемое значение





сравнить с



кр


Е
сли






кр
, то гипотеза о виде распределения принимается при уровне
значимости



Е
сли






кр
, то гипотеза отвергается с уровнем значимости



Решение.

По предложенному алгоритму прове
дем

первичную
статистическую обработку данных.

Согласно пункту 3 алгоритма

находим
݇

ͳͳ


различных выборочных значений

0

1

2

3

4

5

6

7

8

9


10

С помощью пакета

Анализ данных
получаем статистический ряд выборки и
его графическое представление (рис. 1
.3
).




Рис. 1
.3
.


Построенная гистограмма позволяет сделать предположение о виде
распределения случайной величины
X
.
В результате з
аполн
ения

табл
.

1.3
получим табл
.

1.5
,

в

третьем столбце
которой,
представлены относи
тельные, а в
четвертом


накопленные относительные частоты выборочных значений.


Таблица 1.5



С
огласно

пункту

4

алгоритма
получим полигоны относительных и
накопленных частот
(рис.

1.4, 1.5
)
.


13



Рис.
1.4
.





Рис.
1.5
.


С
огласно пункту 5 алгоритма
получаем выборочные

характеристики

(табл. 1.6).


Таблица 1.
6



Проверим гипотезу о распределении случайной величины по закону Пуассона.
В качестве точечной оценки параметра распределения выбираем выборочное
0

0,05

0,1

0,15

0,2

0,25

0,3

0

1

2

3

4

5

6

7

8

9

10

Ряд1

0

0,2

0,4

0,6

0,8

1

1,2

0

1

2

3

4

5

6

7

8

9

10

Ряд1

14

среднее





Ͷ

Ͷͻ
.
В результате з
аполн
ения

табл
.

1.4 получим табл
.

1.
7
.

Наблюдаемое значение случайной величины



ͻ

ʹ͸ͳͷʹͺ
. Оно получено

суммированием чисел последнего столбца
т
абл
.

1
.
6.

Критическое значение


кр

ХИRОБР



ݎ


ͳ͸

ͻͳͺͻͺ
,

где


Ͳ

Ͳͷ

ݎ

݇

ʹ

ͳͳ

ʹ

ͻ


Так

как





кр
, то гипотеза о

р
аспределении по закону Пуассона при уровне

значимости


Ͳ

Ͳͷ

не противоречит опытным данным.




Таблица 1.7




Тема
2
. ЛИНЕЙНАЯ ПАРНАЯ РЕГРЕССИЯ



Эта тема включает выполнение лабораторных работ, посвященных
построению и исследованию
уравнения линейной регрессии вида





݂




ܽ

ܾ





















































ʹ

ͳ


Построение линейной регрессии сводится к оценке ее параметров
a
,
b
.
Классический подход к оцениванию параметров линейной регрессии основан
на
методе наименьших квадратов

(МНК).

Пример
.

Для определения зависимости между сменной добычей угля на
одного рабочего (переменная
Y
, измеряемая в тоннах) и мощностью угольного
пласта
(переменная
X
, измеряемая в метрах) на 10 шахтах были проведены
исследования, результаты кото
рых представлены
в
табл.

2
.1.


Таблица
2
.1

i

1

2

3

4

5

6

7

8

9

10

X

8

11

12

9

8

8

9

9

8

12

Y

5

10

10

7

5

6

6

5

6

8




15

Л
абораторная работа

2
.1


ВЫЧИСЛЕНИЕ КОЭФФИЦИЕ
НТОВ

УРАВНЕНИЯ
ЛИНЕЙНОЙ РЕГРЕССИИ




Цель работы.
Вычисление коэффициентов уравнения линейной регрессии
по пространственной выборке таб
л
.
2
.1.


Расчетные соотношения.

Коэффициенты, определяемые на основе
МНК
,
являются решением системы
уравнений






݊ܽ

ܾ






ܽ



ܾ














(
2
.2)

Решая эту систему уравнений, получ
и
м
































































ܽ




ܾ































































ʹ

͵













































ܾ






















ܿ݋





ݏ























































ʹ

Ͷ


где
ܿ݋


















выборочная ковариация
;

ݏ





выборочное значение
дисперсии величины
x
, определяемой п
о формуле:
































































ݏ





































































ʹ

ͷ


Решение.

Вычислим
значения
,

используя
данные
табл
.

2
.
2:




ͻ

Ͷ







͸

ͺ










ͻͲ

ͺ








͸͸

Ͷ
.



Таблица R.R

A

B

C

D

E

F

G

H

I

J

K

№ п
/п

x

y

xy

x
2

y
2

(у ‒


)

(у ‒


)
2

y
^
i

(y ‒
y
^
i
)

(y ‒
y
^
i
)
2

1

8

5

40

64

25


1,8

3,24

5,38

0,38

0,1429

2

8

5

40

64

25


1,8

3,24

5,38

0,38

0,1429

3

8

6

48

64

36


0,8

0,64

5,38


0,62

0,3869

4

8

6

48

64

36


0,8

0,64

5,38


0,62

0,3869

5

9

7

63

81

49

0,2

0,04

6,39


0,61

0,3672

6

9

6

54

81

36


0,8

0,64

6,39

0,39

0,1552

7

9

5

45

81

25


1,8

3,24

6,39

1,39

1,9432

8

11

10

110

121

100

3,2

10,24

8,43


1,57

2,4775

9

12

10

120

144

100

3,2

10,24

9,44


0,56

0,3114

10

12

8

96

144

64

1,2

1,44

9,44

1,44

2,0794

С
умма


94

68

664

908

496

0

33,6

68

0

8,3934

Средн
ее

9,4

6,8

66,4

90,8

49,6







Коэффициенты
a
,
b

вычисли
м

по формулам (
2
.
3),
(
2
.
4
) соответственно
:

ܽ


ʹ

͹ͷ






ܾ

ͳ

Ͳʹ


а само уравнение регрессии (
2
.1) примет вид



































































݂





ʹ

͹ͷ

ͳ

Ͳʹ

































ʹ

͸


16

Л
абораторная работа

2
.2


ВЫЧИСЛЕНИЕ ВЫБОР
ОЧНОГО
КОЭФФИЦИЕНТА

КОРРЕЛЯЦИИ



Цель работы.
Вычисление выборочного коэффициента корреляции по
пространственной выборке таб
л
.
2
.1.

Расчетные соотношения.

Выборочный коэффициент корреляции
определяется соотношением







(
2
.
7
)

где

;

;

.






(
2
.
8
)


Решение.

Используя
вычисл
ения лабораторной работы
2
.1
,
данные
табл
.

2
.2

и
формулы (R.7), (R.8)
,

получим:

ݏ


ͳ

ͷ͸










Ͷͻ

͸



ݏ


ͳ

ͺ͵



ݎ


͸͸

Ͷ

ͻ

Ͷ

͸

ͺ
ͳ

ͷ͸

ͳ

ͺ͵

ʹ

Ͷͺ
ʹ

ͺ͸

Ͳ

ͺ͹


Величина
ݎ


Ͳ

ͺ͹

говорит о сильной положительной линейной связи.



Л
абораторная работа

2
.
3


ПРОВЕРКА ЗНАЧИМОСТИ
УРАВНЕНИЯ ЛИНЕЙНО
Й
РЕГРЕССИИ
ПО КРИТЕРИЮ ФИШЕРА


Цель работы.

По данным табл
.

2
.1 оценить
при

уровне


= 0,
05 значимость
уравнения регрессии
(2.6)
,

пост
роенного в лабораторной работе

2
.1.

Критерий Фишера

(
F
-
критерий)
.
Уравнение парной регрессии значимо
при
уровне значимости

, если вы
полняется следующее неравенство
:














ܳ


݊

ʹ

ܳ















































ʹ

ͻ


Величины
Q
r
,
Q
e

являются факторной и остаточной
суммами квадратов
соответственно
:
















































ܳ
















ܳ







































ʹ

ͳͲ






Величина
















табличное
значени
е

F
-
р
аспределения с числами
степеней

свободы
k
1

1 и
k
2

=
n



2

квантиля уровня


= 1




(прил.
2
)
.
Эт
у

вероятность можно также определить с помощью функции
F
РАСП
ОБР
:
































































ʟ ʠʞʝ ʟ



ͳ

݊

ʹ
























ʹ

ͳͳ


17

З
начение
F

для
линейной

парной регрессии
можно вычислить
через
коэффициент корреляции:



































































ݎ


ͳ

ݎ



݊

ʹ











































ʹ

ͳʹ


В
еличина

=
R
2

называе
тся коэффициентом детерминации и
характеризует долю дисперсии результативного признака
,
объясняемую
регрессией, в общей дисперсии результативного признака:














































ܴ


ͳ

ܳ

ܳ

ˆˇˈ

ܳ



































ʹ

ͳ͵






Соответственно величина

характеризует долю дисперсии
,
вызванную влиянием
остальных, не учтенных в модели

факторов.

Решение.

В
ычисл
им

значения
Q
e
,

и критерий
F

по

данным

табл
.

2
.2
.

В столбце
I

значения вычисляются по формуле
(2.6)
.

И
так, п
олучены
следующие значения
:

ܳ


ͺ

͵ͻ




ܳ


ܳ

ܳ


͵͵

͸

ͺ

͵ͻ

ʹͷ

ʹͳ
,



ܳ


݊

ʹ

ܳ


ʹͷ

ʹͳ

ͺ
ͺ

͵ͻ

ʹͶ

Ͳ͵


По формуле (
2
.1
1
)
при
k
1

= 1
,
k
2

= 8

или по таблице (прил.
2
)
вычисляем
квантиль
F
0.95; 1; 8

= 5
,
3R. Неравенств
о (R.9
) выполняется, т. е. R4
,
0
3

� 5
,
32
,

и
поэтому уравнение регрессии
(2.6)

значимо
.



Л
абораторная работа

2
.4


ВЫЧИСЛЕНИЕ КОЭФФИЦИЕ
НТ
ОВ

УРАВНЕНИЯ ЛИНЕЙНОЙ Р
ЕГРЕССИИ



Цель
работы.

Вычисление коэффициентов ур
авнения линейной регрессии
.

В
вычислительной среде
табличного процессора

MS

Excel

эта задача
решается
п
ри помощи статистических функций
НАКЛОН

(наклон прямой
относительно оси
Х
, коэффициент
b
) и
ОТРЕЗОК

(отрезок
,

отсекаемый прямой
на оси
Y
, коэффициент
a
).

Для знакомства с этим
и

возможностями введем необходимые исходные
данные (табл
.

2
.3
).

В
столбцах

В

и
С

вводятся данные
табл. R.1
, записи в
столбце

Е

играют роль подсказок,
столбец

F

заполняется по мере

обработки.

В
ячейку

F3

в
вод
ится

функция

НАКЛОН
,
в ячейку
F
4



ОТРЕЗОК
.

Обе

эти
функции имеют
два аргумента: диапазон ячеек со значениями
Y

(
С3:С1
2
)

и
диапазон ячеек со значениями
Х

(
В3:В1
2
).

С
татистическая функция
КВПИРСОН

вычисляет значение коэффициента
детерминации
.

18


Таблица R.
3



Функция
ЛИНЕЙН
(изв_знач_у;

изв_знач_х;

константа;

стат)

вычисляет

коэффициенты линейной регрессии,
к
оэффициент детерминации
R
2
,

F
-
статистик
у.

В

поле «
изв_знач_у
»

вводится

диапазон значений
Y

(
С3:С1
2
)
;
«
изв_знач_х
»



диапазон значений

Х

(
В3:В1
2
)
;

константа

устанавливается на
0
, если заранее известно, что свободный член равен
0
,

и на
1

в противном
случае;

стат

устанавливается на
0
, если не нужен вывод дополнительных
сведений регрессионного анализа
,

и на
1

в противно
м случае.

Порядок использования функции
ЛИНЕЙН
:

1.

Выделить область пустых ячеек 5

R (5 строк, R столбца) для вывода
результатов регрессионной статистики и 1

R для вывода только коэффициентов

a
,
b
.

2.

Ввести функцию
ЛИНЕЙН

вручную или через
Мастер функций
.

3.

После корректного ввода функции в левой верхней ячейке выделенной
таблицы появится первый итоговый элемент таблицы. Чтобы раскрыть всю
таблицу, следует
сначала

нажать клавишу
F
2
, а затем
одновременно нажать
клавиши
[
Ctrl
]
,
[
Shift
]
,
[
Enter
]
.

Далее появляется
следующая
регрессионная
статистика, представленн
ая

в

табл.

2.
4
.

Таблица R.
4

Значение коэффициента
b

Значение коэффициента
a

Среднеквадратическое отклонение
b

Среднеквадратическое отклонение
a

Коэффициент детерминации
R
2

Среднеквадратическое отклонение
у

F
-
статистика

Число степеней свободы

Регрессионная сумма квадратов

Остаточная сумма квадратов


Решение.

В

результате выполнения

вышеуказанных
действи
й

получим
табл
.

2
.
5
.

Значения в табл
. 2.5

совпадают с
о

значениями
,

полученными в
лабораторных работах
2.1, 2.3.

19

Таблица R.
5

Значение коэффициента
b

1,0164


2,754

Значение коэффициента
a

Среднеквадратическое
отклонение
b

0,2074

1,9759

Среднеквадратическое
отклонение
a

Коэффициент детерминации
R
2

0,7502

1,0243

Среднеквадратическое
отклонение
у

F
-
статистика

24,025

8

Число степеней свободы

Регрессионная сумма
квадратов

25,207

8,3934

Остаточная сумма квадратов




Л
абораторная работа

2
.5


АВТОКОРРЕЛЯЦИЯ ОСТАТ
КОВ.

СТАТИСТИКА
ДАРБИНА‒
УОТСОНА


Цель работы.

Н
аучиться пользоваться статистикой Дарбина

Уотсона.

Одна из предпосылок МНК


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


это корреляция между
наблюдаемыми

показателями, упорядоченными во времени (временные ряды)
или в пространстве (перекрестные ряды). Нам неизвестны истинные значения
отклонений
e
i

=

y
i


f
(
x
i
)
,
i

= 1,

,
n
.
Поэтому выводы об их независимости
делаются на основе оценок
e
i
,
i

= 1,

,
n
.

При этом обычно проверяется

некоррелированность только соседних величин.
При наличии автокорреляции
остатков полученное уравнение регрессии считается неудовлетворительным.


Критерий
Дарбина‒
Уотсона


наиболее известный способ обнаружения
автокорреляции
первого порядка.

Пусть
n


число наблюдений,
k

‒ число
факторов модели, уровень значимости
α
. Для
n
,
k
,
α

по таблицам распределения
Дарбина‒
Уотсона

(прил. 3) находим числа
d
l

и
d
u
.
Вычисляе
м

статистик
у

Дарбина‒
Уотсона


ܹ



݁


݁










݁









Здесь
e
i

=
y
i



(
ax
i

+
b
)

‒ остатки, вычисленные
по уравнени
ю

линейной
регрессии
y

=
a

+
b
x

для
статистически
х

данны
х

(x
i
,
y
i
)
.

Если
DW


d
l
,
то это свидетельствует о положительной автокорреляции
остатков. Если
DW



4 ‒

d
l
,

то это свидетельствует об отрицательной
автокорреляции остатков. При

d
u


DW

< 4 ‒
d
u

гипотеза об отсутствии
автокорреляции остатков принимается. Если

d
l


DW


d
u


или


4


d
u


DW

4


d
l
,

20

то гипотеза об отсутствии автокорреляции остатков не может б
ыть ни принята,
ни отв
е
ргнута.

Решение.

Вернемся

к примеру
. Определим наличие автокоррел
яции с
помощью критерия Дарбина‒
Уотсона.
В

столбц
е

К

табл. R.R

получили


݁



ͺ

͵ͻ






Во второй столбец



табл. R.
6

введем д
анные столбца
J

табл. R.R
.

Из

каждого

числа R
-
го столбца вычитаем предыдущее число этого же столбца и результат
введем в третий столбец









Статистика
Дарбина‒Уотсона равна

ܹ



݁


݁










݁







ͳ͸

͹ͺ
ͺ

͵ͻ

ʹ


Та
блица R.6



п/п























1

0,38





2

0,38

0

0

3

‒0,6R

‒1,00

1

4

‒0,6R

0

0

5

‒0,61

0,01

0

6

0,39

1

1

7

1,39

1

1

8

‒1,57

‒R,96

8,76

9

‒0,56

1,01

1,02

10

1,44

2

4

С
умма




16,78


По таблице
распределения
Дарбина

Уотсона

(прил. 3) находим числа
d
l

= 0,88
и

d
u

1,3R при
n

= 10,
k

= 1
. Так как

d
u


DW

< 4 ‒
d
u


(1,32
2 2,68),

то
гипотеза об отсутствии автокорреляции остатков не отклоняется на уровне
значимости
0,05
. Это является одним из подтверждений высокого качества
модели.



Тема
3
. НЕЛИНЕЙНАЯ ПАРНАЯ
РЕГРЕССИЯ



Эта

тема включает выполнение лабораторных работ, посвященных
построению уравнен
ия нелинейной парной регрессии.

21

Пример
. В табл
.

3
.1 приведены значения независимой переменной

(
т
оварооборот, тыс. р.
) и значения зависимой переменной
(

у
ровень издержек
обращения по отношению к товарообороту, %
).


Таблица
3
.1

X

7

10

15

20

30

45

60

120

Y

10

9

7,2

6

6,3

5,8

5,4

5



Л
абораторная работа

3
.1


ПОСТРОЕНИЕ НЕЛИНЕЙНО
Й РЕГРЕССИИ



Цель работы.

П
остроить уравнение нелинейной регрессии и вычислить
индекс

детерминации
.

Расчетные соотношения.

В случае нелинейных зависимостей с помощью
некоторых преобразований можно перейти к линейным. Приведем
некоторые
из нелинейных
функций и их модификацию к линейной функции


табл
.

3
.2.


Таблица
3
.2


п/п

Исходная
спецификация

Преобразование

х


х*

Преобразо
-
вание

у


у*

Вычисление

b

по

b
*

Вычисле
ние

a

по

a
*

1



ܽ

ܾ







ͳ


y* = y

b = b*

a = a*

2



ͳ
ܽ

ܾ



x* = x

y* =

1
y


b = b*

a = a*

3




ܽ

ܾ



x* =

1
x


y* =

1
y


b = a*

a = b*

4

y = ae
b x +


x* = x

y* = lny

b = b*

a = e
a*

5

y = a e
b
_
x

+


x* =

1
x


y* = lny

b = b*

a = e
a*

6



ͳ
ܽ

ܾ
݁





x* =
e
-
x

y* =

1
y


b = b*

a = a*

7

y = ax
b
e


x* = lnx

y* = lny

b = b*

a = e
a*


Решение.

На
рис
.

3
.1

и
зобра
жены

данные
в виде
диаграмм
ы
.
Из этого
рисунка видно, что
зависимость между показателями нелинейная. Будем искать
регрессионную зависимость в виде
степенной:


ܽ




переходя к

логарифмам

22

(модификация табл.
3
.2)
, получ
им линейную зависимость


y
* =
a
* +
bx
*,
где



y
* =
lny
,
x
* =
lnx
.



Рис
.

3
.1.


Определяем коэффициенты
а*,
b

для модифицированных данных.
Исходные

данные
и о
сновные вычисления приведены в табл
.

3
.
3.


Таблица
3
.
3



п/п

x

x*

=

lnx

y*

=

lny

x
2

y
2









)
2

y
^
x

(y


y
^
x
)
2

1

7

1,95

2,30

3,79

5,30

10,00

9,12

0,77

2

10

2,30

2,20

5,30

4,83

4,68

8,37

0,40

3

15

2,71

1,97

7,33

3,90

0,13

7,59

0,15

4

20

3,00

1,79

8,97

3,21

0,70

7,08

1,17

5

30

3,40

1,84

11,57

3,39

0,29

6,42

0,02

6

45

3,81

1,76

14,49

3,09

1,08

5,82

0,00

7

60

4,09

1,69

16,76

2,84

2,07

5,43

0,00

8

120

4,79

1,61

22,92

2,59

3,38

4,60

0,16

Итого


26,04

15,16

91,14

29,15

22,32

-

2,67


Аналогично лабора
торной работе
2
.1 определим к
оэффициенты
а*

и

b

из
с
оотношений

(
2
.3), (
2
.4):

а*


2,68
;

b




0,24
.

По величине
ܽ


ʹ

͸ͺ

потенци
и
рованием находим
исходный
коэффициент
а
:
ܽ

݁




ͳͶ

ͷͺ


Таким образом,
уравнение регрессии имеет вид:
y

= 14,58

x

0,24
.

Изобразим
эту кривую на диаграмме
с исходными данными
(
рис
.

3
.2).

Аналогично лабораторной работе
2
.3 вычислим

ܳ


ʹ

͸͹




ܳ


ܳ

ܳ


ʹʹ

͵ʹ

ʹ

͸͹

ͳͻ

͸ͷ


0

2

4

6

8

10

12

0

20

40

60

80

100

120

Издержки, %

Товарооборот, тыс. р.

23



Рис
.

3
.2
.


Согласно формуле (
2
.13)
индекс

детерминации равен

ܴ


ͳ

ܳ

ܳ

ͳ

ʹ

͸͹
ʹʹ

͵ʹ

ͳ

Ͳ

ͳʹ

Ͳ

ͺͺ


Вычисленное значение говорит о том, что уравнением регрессии
объясняется 88

% дисперсии результативного признака, а на долю прочих
факторов

приходится лишь 1R

%.



Л
абораторная работа

3
.
2


ПОСТРОЕНИЕ НЕЛИНЕЙНО
Й РЕГРЕССИИ

С ИСПОЛЬЗОВАНИЕМ
К
ОМАНДЫ «ДОБАВИТЬ ЛИН
ИЮ ТРЕНДА»


Цель работы.

П
остроить уравнение нелинейной регрессии с
использованием команды «Добавить линию тренда»
.

Команда
«Добавить линию тренда
»

и
спользуется для выделения тренда
(медленных изменений) при анализе временных рядов. Однако эту команду
можно использовать и для построения уравнения нелинейной регрессии,
рассматривая в качестве времени

t

независимую переменную

x
.

Эта команда позволяет построить следующие уравнения регрессии:



линейн
ая




ܽ

ܾ




полиноминальн
ая




ܽ

ܾ



ܾ




ܾ




ܾ




ܾ




ܾ







логарифмическ
ая




ܽ

ܾ




степенн
ая




ܽ





экспоненциальн
ая




ܽ
݁



Для построения одной из перечи
сленных регрессий необходимо выполнить
следующие шаги:

0

2

4

6

8

10

12

0

20

40

60

80

100

120

Издержки, %

Товарооборот, тыс. р.

24

Шаг 1.

В
вести по столбцам исходные данные








݅

ͳ

ʹ



݊


(рис.
3
.
3
).

Шаг R.

По этим данным построить график в де
картовой системе координат
(
рис
.
3
.
3
).

Шаг 3.

Установить курсор на
любую точку
построенно
го

график
а
, сделать
щелчок правой кнопкой и в появившемся контекстном меню выполнить
команду
Добавить линию тренда

(
рис.

3
.
3
).





Рис.
3
.3
.


Шаг 4.

В появившемся диалоговом окне выбрать нужное уравнение
регрессии.

Шаг 5.

«
В
ключить»
необходимые опции:



«Показать уравнение на диаграмме»



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

коэффициентами;



«Поместить на диаграмму величину достоверности аппроксимации
(
R
^R)»



на диаграмме будет показан
о

значение
коэфф
ициента

детерминации

(для нелинейной регрессии

индекс детерминации)
.


Если по построенному уравнению

регрессии необходимо выполнить
прогноз, то нужно указать число

периодов прогноза
.

Шаг 6.
После за
дания всех перечисленных опций
на диаграмме появится
формула построенного уравнения регрессии и значение индекса детерминации
.

Решение.

По

данны
м

табл. 3.1
построить степенное
уравнени
е регрессии




ܽ




После
выполн
ения

перечисленны
х

шаг
ов

п
олуч
и
м уравнение



ͳͶ

ͷͺ



Ͳ

ʹͶ
,

для которого
индекс

детерминации равен
R
² 0,877

(
рис.
3
.
4
). Такая величина говорит о хорошем соответствии построенного уравнения
исходным данным.



25



x

y







1

7

10


2

10

9

3

15

7,2

4

20

6

5

30

6,3

6

45

5,8

7

60

5,4

8

120

5











Рис.
3.4
.




Л
абораторная работа

3
.3


В
ЗВЕШЕННЫЙ МЕТОД НАИМ
ЕНЬШИХ КВАДРАТОВ

Цель работы
.

Освоение применения взвешенного метода наименьших
квадратов для коррекции гетероскедастичности остатков.

Расчетные
соотношения.

После при
менения обычного МНК выясняется

гетероскедастичность остатков: стандартное отклонение остатков линей
но

увеличивается при увеличении нез
ависимой переменной. Необходимо

применить модификацию взве
шенного МНК для коррекции такой

гетероскедастичности.

Решение.

Построить точечную диаграмму и
сходных данных, поместить на
нее

линию тренда, его уравнение и
индекс

детерминации.

1.

C
формировать массив остатков.

2.

Модифицировать массив н
езависимой переменной следующим
образом
:

x
i
*

=

1/х
i
.

3.

Модифицировать массив зависимой переменной следующи
м

образом
:


y
i
*

=y
i

i
.

4.

По полученным данным модифицированной регрессии
x
i
*

,
y
i
*

построить
диаграмму облака рассеяния, поместить на не
е

линию тренда с уравнением
линии регрессии и
индекс
ом детерминации. Сравнить это уравнение с
уравнением исходной регрессии.



y = 14,578x
‒Ͳ ʹͶͳ

R( Ͳ ͺ͹͹ͺ

0

2

4

6

8

10

12

0

50

100

150

Ряд1

Степенная
(Ряд1)

Линия

регрессии


26

Те
ма
4
. ЛИНЕЙНАЯ МНОЖЕСТВЕННАЯ
РЕГРЕССИЯ



Эта тема включает выполнение лабораторных работ, посвященных
построению и исследованию
уравнения линейной множественной регрессии
вида












ܾ


ܾ




ܾ






(
4
.1)

Пример
. Данные о сменной добыче угля на одного рабочего (переменная
Y
,
и
змеряе
ма
я в тоннах), мощности пласта (переменная
X
1
, и
змеряе
ма
я в метрах) и
об
уровне механизации работ в шахте (переменная
X
2
,

измеряе
ма
я в процентах),
характеризующие процесс добычи угля в 10 шахтах
,

приведены в табл
.

4
.1.





Таблица
4
.1

Номер шахты

i

X
1

X
2

Y

1

8

5

5

2

11

8

10

3

12

8

10

4

9

5

7

5

8

7

5

6

8

8

6

7

9

6

6

8

9

4

5

9

8

5

6

10

12

7

8


Предполагая, что между переменными
Y
,
X
1
,
X
2

существует линейная
зависимость,

необходимо найти аналитическое выражение для этой
зависимости, т.

е. построить уравнение линейной регрессии.



Л
абораторная работа

4
.1


ВЫЧИСЛЕНИЕ КОЭФФИЦИЕ
НТОВ Л
ИНЕЙНОЙ
МНОЖЕСТВЕННОЙ РЕГРЕС
СИИ


Цель работы.

Для

пространственн
ой

выборк
и

табл
.
4
.1 необходимо
вычислить вектор коэффициентов
ܾ


ܾ

ܾ

ܾ



уравнения регрессии (4
.1).

Расчетные соотношения.
Вектор коэффициентов, найденный методом
наименьших квадратов
,

является решением следующей системы уравнений:

ܺ

ܾܺ

ܺ

ܻ


27

г
де

X



матрица размер
ности

, первый столбец которой составлен из 1, а
другие два столбца составлены из значений











т.

е. матрица
X

имеет
структуру


ܺ


ͳ
ͺ
ͷ
ͳ
ͳͳ
ͺ



ͳ
ͳʹ
͹



а

Y


вектор, составленный из 10 значений
,
т.

е.

ܻ


ͷ
ͳͲ

ͺ



Матрица
ܺ

ܺ

имеет обратную матрицу

ܺ

ܺ





и тогда вектор коэффициентов
равен


ܾ


ܺ

ܺ




ܺ

ܻ





(
4
.2)

Для реа
лизации этой матричной формулы

необходимо выполнить
следующие операции: транспонирование; умножение матриц (частный случай


умножение матрицы на вектор); вычисление обратной матрицы. Все эти
операции можно

реализовать с помощью
матричных функций
MS

Excel

категории

функций
Ссылки и мас
сивы
. Для раб
оты с этими функциями
можно либо
обратиться к
Мастеру функций

и выбрать нужную категорию
функций, затем указать имя функции и задать соответствующие диапазоны
ячеек,
либо непосредственно
ввести с клавиатуры имя функции
и
задать
соответствующие

диапазоны ячеек.

Ф
ункци
я

ТРАНСП
(
диапазон ячеек
)

осуществляет

т
ранспонирование
матрицы
,
где параметр
диапазон ячеек

задает все элементы транспонируемой
матрицы (или вектора).

Ф
ункци
я

МУМНОЖ(диапазон_1;

диапазон_R)
осуществляет

у
множение
матриц

в
категори
и

функций
Математические
.

П
араметр
диапазон_1

задает
элементы первой из перемножаемых матриц, а параметр
диапазон_R



элементы второй матрицы. При этом перемножаемые матрицы должны иметь
соответствующие размер
ности

(если первая матрица
размерности
݊

݇
, вторая


݇

݉
, то результатом будет матрица

размерности
݊

݉
).

Ф
ункци
я

МОБР(диапазон ячеек)

осуществляет

о
бращение матрицы
(вычисление обратной матрицы)
в
категори
и

функций

Математические
.

П
араметр
диапазон ячеек

задает все элементы обращаемой матрицы,

которая
должна быть квадратной и невырожденной.

При использовании этих функций
необходимо соблюдать следующий
порядок действий:

1.

В
ыделить фрагмент ячеек, в которые будет занесен результат выполнения
матричных функций (при этом надо учитывать размеры
исходных матриц);

2.

В
вести арифметическое выражение, содержащее обращение к матричным
функциям
MS

Excel
;

28

3.

О
дновременно нажать клавиши
[
Ctrl
]
,
[
Shift
]
,
[
Enter
]
. Если этого не
сделать, то вычислится только один элемент результирующей матрицы или
вектора.

Решение.
Сформируем матрицу
X

и вектор

Y
.
Затем выполним
формирование матрицы
ܺ

ܺ
, вектора

ܺ

ܻ

и вычисление вектора

ܾ


ܾ



ܾ



ܾ




по формуле (4
.2
). В результате по
луч
им

вектор
коэффициентов
ܾ



͵

ͷ͵ͻ͵
Ͳ

ͺͷ͵ͻ
Ͳ

͵͸͹Ͳ


и тогда уравнение регрессии (
4
.1) примет вид














͵

ͷ͵ͻ͵

Ͳ

ͺͷ͵ͻ




Ͳ

͵͸͹






(
4
.3)



Л
абораторная работа

4
.2


ВЫЧИСЛЕНИЕ КОЭФФИЦИЕ
НТОВ ЛИНЕЙНОЙ
МНОЖЕСТВЕННОЙ РЕГРЕС
СИИ И ПРОВЕРКА ЗНАЧИ
МОСТИ

В РЕЖИМЕ

РЕГРЕССИЯ


Цель работы.

И
спользуя режим
Регрессия
,

вычислить вектор
коэффициентов уравнения регрессии

(
4
.1).

Табличный процессор

MS

Excel

содержит модуль

Анализ данных
.
Этот

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

д.). Режим
работы
Регрессия

этого модуля осуществляет вычисление коэффициентов
линейной множественной регрессии с
k

переменными, построение
доверительны
х

интервал
ов

и проверку значимости уравнения регрессии.

После вызова режима

Регрессия

на экране появляется диалоговое окно

(рис.
4
.
1
), в котором задаются следующие параметры:




Рис.
4
.1.

29

1.

Входной интервал
Y



вводится диапазон адресов ячеек, содержащих
значения

(ячейки должны составлять один столбец).

2.

Входной интервал
X



вводится диапазон адресов ячеек, содержащих
значения независимых переменных. Значения каждой переменной
представляются одним столбцом. Количество переменных

не более 16.

3.

Метки


включается
,

если первая строка во входном диапазоне
содержит заголовок. В этом случае автоматически будут созданы
стандартные названия.

4.

Уровень надежности


при включении этого параметра задается
надежность

при построении доверительных ин
тервалов.

5.

Константа
-
ноль



при включении этого параметра коэффициент
ܾ


Ͳ


6.

Выходной интервал


при включении активизируется поле, в которое
необходимо ввести адрес левой верхней ячейки выходного диапазона,
который содержит ячейки с результатами вычислени
й режима
Регрессия
.

7.

Новый рабочий лист


при включении этого параметра открывается
новый лист, в который
,

начиная с ячейки
А1
,

вставляются результаты работы
режима
Регрессия
.

8.

Новая рабочая книга



при включении этого параметра открывается
новая книга
,

на первом листе которой
,

начиная с ячейки
А1
,

вставляются
результаты работы режима
Регрессия
.

9.

Остатки


при включении вычисляется столбец, содержащий невязки







݅

ͳ



݊


10.

Стандартизованные остатки


при включении вычисляется столбец,
содержащий

стандартизованные остатки.

11.

График остатков


при включении выводятся точечные графики
невязки







݅

ͳ



݊

в зависимости от значений переменных





݆

ͳ



݇


Количество графиков равно числу

k

переменных
.

12.


График подбора


при включении выводятся точечные графики
предсказанных по построенной регрессии значений




от значений
переменных



݆

ͳ



݇


Количество графиков равно числу

k

переменных


.

Решение
.
Первоначально введем
в столбец
С

десять значений первой
переменной

X
1
,
в столбец
D



десять значений
второй

переменной

X
2
, а в
столбец
F



десять значений зависимой переменной

Y
.

После этого вызовем режим
Регрессия

и в диалоговом окне зададим
необходимые параметры (рис.
4
.
1
). Результаты
работы прив
е
д
ены

на

рис.
4
.
2



4
.
3
. Заметим,
что
из
-
за большой «ширины» таблиц, в которых
выводятся результаты работы режима

Регрессия
,
часть результатов помещен
а

в другие ячейки.

Дадим краткую интерпретацию показателям, значения которых
вычисляются в
режиме

Регрессия
.
Первоначально рассмотрим показатели,
объединенные названием
Регрессионная статистика

(рис.
4
.
2
).

30



Рис.
4
.2.


Множественный

R


корень квадратный из
коэффициента

детерминации.


R
-
квадрат



коэффициент

детерминации
ܴ



Нормированный
R
-
квадрат



приве
денный
коэффициент

детерминации

ܴ



ͳ


݊

ͳ

ܳ


݊

݇

ܳ

ͳ

݊

ͳ
݊

݇

ͳ

ܴ




Стандартная ошибка


оценка

для среднеквадратического отклонения

σ
.

Наблюдения


число наблюдений

n
.

Перейдем к показателям, объединенны
м

названием
Дисперсионный анализ
(рис.
4
.
2
).

Столбец

df



число степеней свободы. Для строки
Регрессия

показатель
равен числу независимых переменных
݇


݇

ͳ


для строки
Остаток
он
равен
݇


݊


݇


ͳ


݊

݇


для строки
Итого
݇


݇


݊

ͳ


Столбец
SS



сумма квадратов отклонений. Для строки
Регрессия

показатель равен величине

(см. формулы (
2
.1
0
))
;
для строки
Остаток


равен величине
(см. формулы (
2
.1
0
))
;

для строки
Итого
.

Столбец

дисперсии, вычисленные по формуле

,

т.

е. дисперсия
на одну ст
епень свободы.

Столбец


значение
, равное

F
-
критерию Ф
ишера, вычисленно
му

по
формуле




ܳ

݇


ܳ

݇




31

Столбец З
начимость

F



значение уровня значимости, соответствующее
вычисленной величине
F
-
критерия

и равное вероятности
ܲ



݇



݇








где


݇



݇





случайная величина, подчиняющаяся распределению Фишера с
݇



݇


степенями свободы.
Если вероятность меньше уровня значимости
α
(обычно
α

=

0,05
), то построенная регрессия является значимой
.

Перейдем к следующей группе показателей, объединенных в табл
.

4.2
.


Таблица 4.R


Коэффициенты

Стандартная ошибка

t
-
статистика

Y

пересечение

‒3,539

1,907

‒1,8564

Переменная
X
1

0,854

0,221

3,8726

Переменная
X
2

0,367

0,243

1,5108






P
-
з
начение

Нижние 95

%

Верхние 95

%


0
,1058

‒8,0477

0,9690


0,0061

0,3325

1,3753


‒0,1746

‒0,R074

0,9415


Столбец Коэффициенты



вычисленные значения коэффициентов
ܾ



ܾ




ܾ



расположенных сверху вниз.

Столбец Стандартная ошибка


значения
ݏ






݆

Ͳ



݇


вычисленные по
формуле

ݏ




ݏ



ܺ

ܺ




௝௝


Столбец

t
-
статистика


значения статистик



.

Столбец Р
-
значение


содержит вероятности случайных событий

ܲ

ݐ

݊

݇








где

ݐ

݊

݇




с
лучайная величина, подчиняющаяся
распределению Стьюдента с

n



k

с
тепенями свободы.

Если эта вероятность меньше уровня значимости
, то
принимается
гипотеза о значимости соответствующего коэффициента регрессии.

Из
табл. 4.R

видно, что значимым коэффициентом является только
коэффициент

при
x
1
:
b
1

=

0,854
,
так как
Р
-
значение

при
x
1

равно
0,0061
, что
меньше
α
.

Столбцы Нижние 95

% и Верхние 95

%



соответственно нижние и верхние
интервалы для оцениваемых коэффициентов


.

Перейдем к следующей группе показателей, объединенных в табл
.

4.3
.

Столбец Наблюдение


содержит номера наблюдений.

Столбец Предсказанное У


значения




,
вычисленные по
построенному
уравнению регрессии.

Столбец Остатки


значения невязок












32

Таблица 4.
3




В заключение

рассмотрения результатов работы режима
Регрессия
приведем график невязок ‒
остатк
ов








при заданных значениях только
второй переменной

(рис
.

4
.
3
)
. Наличие чередующихся положительных и
отрицательных значений невязок является
косвенным
признаком
отсутствия
систематической ошибки
(неучтенной независимой переменной) в построенном
уравнении регрессии.




Рис.
4
.
3
.



33

БИБЛИОГРАФИЧЕСКИЙ СПИСОК


1.

Брандт З.

Анализ данных. Статистические и вычислительные методы для
научных работников и инженеров

: учеб. пособие

/ З. Брандт ; пер.
с англ.

О. И. Волкова
.

‒ М.

: Мир, R003. ‒

686 с.

2.

Вадзинский Р.

Статистические вычисления в среде
Excel
. Библиотека
пользователя

Р.
Вадзинский
.

‒ СПб.

: Питер, R008. ‒ 608

с.

3.

Горелова

Г.

В.

Теория вероятностей и математическая статистика в
примерах и задачах с применением Excl : уче
б. пособие / Г.

В.

Горелова,
И. А
.

Кацко.


3
-
е изд., доп. и перераб.


Ростов н/Д : Фе
никс, R005.


480 с.

4.

Колемаев В. А.

Теория вероятностей и математическая статистика
:
учебник

/ В. А. Колемаев, В. Н. Калинина. ‒ М. : ИНФРА
-
М, R000. ‒ 336 с.

5.

Лемешко Б. Ю.

Некоторые приложения, рекомендуемые к использованию
в задачах прикладной математической статистики [Электронный ресурс]

:
учеб. пособие / Б. Ю. Лемешко. ‒ Режим доступа:
http
://
ami
.
nstu
.
ru
/~
headr
d
/
applied
/
index
.
html
.

6.

Минько

А.

А.

Статистический анализ в MS Excl / А.

А.

Минько.


М. :
Издательский дом «Вильямс», R004.


448 с.

7.

Розанов Ю.

А.

Теория вероятностей, случайные процессы и
математическая статистика

: учебник / Ю. А. Розанов. ‒ R
-
е изд.,
доп. ‒ М.

: Наука, 1989. ‒ 31R с.

8.

Себер Дж.

Линейный регрессионный анализ

/ Дж. Себер

; пер.
с англ.

В. П. Носко. ‒ М.

: Мир, 1980. ‒ 456 с.

9.

Тюрин

Ю.


Н.

Анализ данных на компьютере / Ю.

Н.

Тюрин,

А.


А.


Макаров

; под ред. В.

Э.

Фигурнова.


3
-
е изд., п
ерераб. и доп.



М. : ИНФРА
-
М, R003.


544 с.

10.

Ходасевич Г.

Б
. Обработка экспериментальных данных на ЭВМ. Ч
.
1.
Обработка одномерных данных [Электронный ресурс]

: учеб. пособие /

Г. Б. Ходасевич. ‒ Режим доступа:

http
://
www
.
dvo
.
sut
.
ru
/
libr
/
opds
/
il
30

hodo
_
part
1/4.
htm
.



34


Приложение
1

Критические значения
-
критерия Стьюде
нта

при уровне значимости 0,10;
0,05
;

0,01 (
двухсторонний)

Число
степеней
свободы
d
.
f
.


Число
степеней
свободы
d
.
f
.


0,10

0,05

0,01

0,10

0,05

0,01


1

6,3138

12,706

63,657

18

1,7341

2,1009

2,8784


2

2,9200

4,3027

9,9248

19

1,7291

2,0930

2,8609


3

2,3534

3,1825

5,8409

20

1,7247

2,0860

2,8453


4

2,1318

2,7764

4,5041

21

1,7207

2,0796

2,8314


5

2,0150

2,5706

4,0321

22

1,7171

2,0739

2,8188


6

1,9432

2,4469

3,7074

23

1,7139

2,0687

2,8073


7

1,8946

2,3646

3,4995

24

1,7109

2,0639

2,7969


8

1,8595

2,3060

3,3554

25

1,7081

2,0595

2,7874


9

1,8331

2,2622

3,2498

26

1,7056

2,0555

2,7787

10

1,8125

2,2281

3,1693

27

1,7033

2,0518

2,7707

11

1,7959

2,2010

3,1058

28

1,7011

2,0484

2,7633

12

1,7823

2,1788

3,0545

29

1,6991

2,0452

2,7564

13

1,7709

2,1604

3,0123

30

1,6973

2,0423

2,7500

14

1,7613

2,1448

2,9768

40

1,6839

2,0211

2,7045

15

1,7530

2,1315

2,9467

60

1,6707

2,0003

2,6603

16

1,7459

2,1199

2,9208

120

1,6577

1,9799

2,6174

17

1,7396

2,1098

2,8982


1,6449

1,9600

2,5758



35

Приложение R


Таблица значений

F
-
критерия Фишера при уровне значимости

k
2


1

2

3

4

5

6

8

12

24



1

161,5

199,5

215,7

224,6

230,2

233,9

238,9

243,9

249,0

254
,
3


2

18,51

19,00

19,16

19,25

19,30

19,33

19,37

19,41

19,45

19,50


3

10,13

9,55

9,28

9,12

9,01

8,94

8,84

8,74

8,64

8,53


4

7,71

6,94

6,59

6,39

6,26

6,16

6,04

5,91

5,77

5,63


5

6,61

5,79

5,41

5,19

5,05

4,95

4,82

4,68

4,53

4,36


6

5,99

5,14

4,76

4,53

4,39

4,28

4,15

4,00

3,84

3,67


7

5,59

4,74

4,35

4,12

3,97

3,87

3,73

3,57

3,41

3,23


8

5,32

4,46

4,07

3,84

3,69

3,58

3,44

3,28

3,12

2,93


9

5,12

4,26

3,86

3,63

3,48

3,37

3,23

3,07

2,90

2,71


10

4,96

4,10

3,71

3,48

3,33

3,22

3,07

2,91

2,74

2,54


11

4,84

3,98

3,59

3,36

3,20

3,09

2,95

2,79

2,61

2,40


12

4,75

3,88

3,49

3,26

3,11

3,00

2,85

2,69

2,50

2,30


13

4,67

3,80

3,41

3,18

3,02

2,92

2,77

2,60

2,42

2,21


14

4,60

3,74

3,34

3,11

2,96

2,85

2,70

2,53

2,35

2,13


15

4,54

3,68

3,29

3,06

2,90

2,79

2,64

2,48

2,29

2,07


16

4,49

3,63

3,24

3,01

2,85

2,74

2,59

2,42

2,24

2,01


17

4,45

3,59

3,20

2,96

2,81

2,70

2,55

2,38

2,19

1,96


18

4,41

3,55

3,16

2,93

2,77

2,66

2,51

2,34

2,15

1,92


19

4,38

3,52

3,13

2,90

2,74

2,63

2,48

2,31

2,11

1,88


20

4,35

3,49

3,10

2,87

2,71

2,60

2,45

2,28

2,08

1,84


21

4,32

3,47

3,07

2,84

2,68

2,57

2,42

2,25

2,05

1,81


22

4,30

3,44

3,05

2,82

2,66

2,55

2,40

2,23

2,03

1,78


23

4,28

3,42

3,03

2,80

2,64

2,53

2,38

2,20

2,00

1,76


24

4,26

3,40

3,01

2,78

2,62

2,51

2,36

2,18

1,98

1,73


25

4,24

3,38

2,99

2,76

2,60

2,49

2,34

2,16

1,96

1,71


26

4,22

3,37

2,98

2,74

2,59

2,47

2,32

2,15

1,95

1,69


27

4,21

3,35

2,96

2,73

2,57

2,46

2,30

2,13

1,93

1,67


28

4,20

3,34

2,95

2,71

2,56

2,44

2,29

2,12

1,91

1,65


29

4,18

3,33

2,93

2,70

2,54

2,43

2,28

2,10

1,90

1,64


30

4,17

3,32

2,92

2,69

2,53

2,42

2,27

2,09

1,89

1,62


35

4,12

3,26

2,87

2,64

2,48

2,37

2,22

2,04

1,83

1,57


40

4,08

3,23

2,84

2,61

2,45

2,34

2,18

2,00

1,79

1,51


45

4,06

3,21

2,81

2,58

2,42

2,31

2,15

1,97

1,76

1,48


50

4,03

3,18

2,79

2,56

2,40

2,29

2,13

1,95

1,74

1,44


60

4,00

3,15

2,76

2,52

2,37

2,25

2,10

1,92

1,70

1,39


70

3,98

3,13

2,74

2,50

2,35

2,23

2,07

1,89

1,67

1,35


80

3,96

3,11

2,72

2,49

2,33

2,21

2,06

1,88

1,65

1,31


90

3,95

3,10

2,71

2,47

2,32

2,20

2,04

1,86

1,64

1,28

100

3,94

3,09

2,70

2,46

2,30

2,19

2,03

1,85

1,63

1,26

125

3,92

3,07

2,68

2,44

2,29

2,17

2,01

1,83

1,60

1,21

150

3,90

3,06

2,66

2,43

2,27

2,16

2,00

1,82

1,59

1,18

200

3,89

3,04

2,65

2,42

2,26

2,14

1,98

1,80

1,57

1,14



36

Приложение 3


Значения статистик Дарбина‒
Уотсона

d
l
,
d
u

при 5%
-
ном

уровне значимости


k = 1

k = 2

k = 3

k = 4

k = 5

d
l

d
u


d
l

d
u


d
l

d
u


d
l

d
u


d
l

d
u


6

0,61

1,40









7

0,70

1,36

0,47

1,90







8

0,76

1,33

0,56

1,78

0,37

2,29





9

0,82

1,32

0,63

1,70

0,46

2,13





10

0,88

1,32

0,70

1,64

0,53

2,02





11

0,93

1,32

0,66

1,60

0,60

1,93





12

0,97

1,33

0,81

1,58

0,66

1,86





13

1,01

1,34

0,86

1,56

0,72

1,82





14

1,05

1,35

0,91

1,55

0,77

1,78





15

1,08

1,36

0,95

1,54

0,82

1,75

0,69

1,97

0,56

2,21

16

1,10

1,37

0,98

1,54

0,86

1,73

0,74

1,93

0,62

2,15

17

1,13

1,38

1,02

1,54

0,90

1,71

0,78

1,90

0,67

2,10

18

1,16

1,39

1,05

1,53

0,93

1,69

0,82

1,87

0,71

2,06

19

1,18

1,40

1,08

1,53

0,97

1,68

0,85

1,85

0,75

2,02

20

1,20

1,41

1,10

1,54

1,00

1,68

0,90

1,83

0,79

1,99

21

1,22

1,42

1,13

1,54

1,03

1,67

0,93

1,81

0,83

1,96

22

1,24

1,43

1,15

1,54

1,05

1,66

0,96

1,80

0,86

1,94

23

1,26

1,44

1,17

1,54

1,08

1,66

0,99

1,79

0,90

1,92

24

1,27

1,45

1,19

1,55

1,10

1,66

1,01

1,78

0,93

1,99

25

1,29

1,45

1,21

1,55

1,12

1,66

1,04

1,77

0,95

1,89

26

1,30

1,46

1,22

1,55

1,14

1,65

1,06

1,76

0,98

1,88

27

1,32

1,47

1,24

1,56

1,16

1,65

1,08

1,76

1,01

1,86

28

1,33

1,48

1,26

1,56

1,18

1,65

1,10

1,75

1,03

1,85

29

1,34

1,48

1,27

1,56

1,20

1,65

1,12

1,74

1,05

1,84

30

1,35

1,49

1,28

1,57

1,21

1,65

1,14

1,74

1,07

1,83


ОГЛАВЛЕНИЕ


ОБЩИЕ СВЕДЕНИЯ

…..
…………………
...
………
……….


……..


…...
........


3



Тема 1.

ОЦЕНКА ПОКАЗАТЕЛЕЙ КАЧЕСТВА ОБЪЕКТА ПО РЕЗУЛЬТАТАМ



ЭКСПЕРИМЕНТОВ…………………
………..
………
……………
……

…..
….


4



Тема
2
. ЛИНЕЙНАЯ


ПАРНАЯ


РЕГРЕССИЯ
………………………

………
….

….
.

10



Тема
3
. НЕЛИНЕЙНАЯ

ПАРНАЯ

РЕГРЕССИЯ
………………………
……………

.

1
6



Тема
4
. ЛИНЕЙНАЯ

МНОЖЕСТВЕННАЯ

РЕГРЕССИЯ
..
……
……………
….


..

22



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



………
.

29



Приложение 1.
Критические значения
-
критерия Стьюд
ента при уровне значимости


0,10;

0
,05
;

0,01 (двухсторонний)
.......
..................
...............................
.......


3
0



Приложение R.
Таблица значений
F
-
критерия Фишера при уровне значимости


α 0,05…
…………………………………………………………….
…….


3
1



Приложение 3.
Значения статистик
Дарбина‒
Уотсона

݀


݀


при 5
%
-
ном уровне


з
начимости…
………………………………………
………..
………….



3
2



Приложенные файлы

  • pdf 14784204
    Размер файла: 1 MB Загрузок: 0

Добавить комментарий