Статистические вычисления

теория одномерн. наб. данных

	A	B	C	D	E	F	G	H	I
1	1 Общий подход к построению уравнения регрессии на примере линейной модели
2	Заданны одномерные наборы данных
3				таблица 1
4	7	31	12	74	25
5	15	48	56	66	58
6	24	35	98	6	79
7	51	67	83	18	91
8	1) Построить гистограмму относительных частот с шагом 10.   2) Построить полигон относительных частот   3) Вычислить типические значения:   – выборочное среднее (        ),   – медиану (mx),   – экстремальные значения  (min xi, max xi),   – нижний квартиль (Q25),   – верхний квартиль (Q75),   – выборочную дисперсию (Dв),   – исправленную выборочную дисперсию (D*),   – среднеквадратичное отклонение (S) .

Исследование одномерных наборов

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Для построения гистограммы нам необходимо построить таблицу 2. В столбце «Частота» вносим частоту попадания выбранных значений в данный интервал.   В ячейку Н3 вводим формулу =G3*F3, и растягиваем до ячейки Н12.   В столбце «Накопленная частота» в ячейку I3 вводим формулу =G3.   В ячейку I4 вводим формулу =I3+G4, и растягиваем до ячейки I12.   В столбце «Частость» находим отношение накопленной частоты к общей сумме частот (вводим формулу =I3/G13 и протягиваем до конца).   Гистограмма строится по значениям столбцов «Середина» и «Частота».   Полигон строится по значениям столбцов «Середина» и «Частость».   Построение гистограммы относительных частот с шагом h = 10 представлено на рисунке 1.
2
3
4	Построение полигона относительных частот представлено на рисунке 2.Для вычисления типических значений вводим в соответствующие ячейки формулы:   – выборочное среднее =СРЗНАЧ(H3:H12)/2,   – медиану =МЕДИАНА(F3:F12),   – экстремальные значения  =МИН(B2:B21), =МАКС(B2:B21),   – нижний квартиль =КВАРТИЛЬ(B2:B21;1),   – верхний квартиль =КВАРТИЛЬ(B2:B21;3),   – выборочную дисперсию =ДИСП(B2:B21),   – исправленную выборочную дисперсию =ДИСПР(B2:B21),   – среднеквадратичное отклонение =СРОТКЛ(B2:B21) .   С помощью функций MS Excel были вычислены следующие типические значения:   – выборочное среднее (  = 63,63636),   – медиана (mx = 50),   – экстремальные значения  (min xi = 6, max xi = 98),   – нижний квартиль (Q25 = 22,5),   – верхний квартиль (Q75 =68,75),   – выборочную дисперсию (Dв = 860,4842),   – исправленную выборочную дисперсию (D* = 817,46),   – среднеквадратичное отклонение (S = 25,18) .
5										таблица 2						рис. 1
6				1		2	3	4	5	6	7
7	Одномерный набор данных	7		Интервал		Середина	Частота		Накопительная частота	Частность	Частность, %
8		15		0	10	5	2	10	2	0.1	10
9		24		10	20	15	3	45	5	0.25	25
10		51		20	30	25	2	50	7	0.35	35
11		31		30	40	35	2	70	9	0.45	45
12		48		40	50	45	1	45	10	0.5	50
13		35		50	60	55	3	165	13	0.65	65
14		67		60	70	65	2	130	15	0.75	75
15		12		70	80	75	2	150	17	0.85	85
16		56		80	90	85	1	85	18	0.9	90
17		98		90	100	95	2	190	20	1	100
18		83		Сумма			20	940
19		74		Максимальное			3
20		66		Выборочное среднее						85.63636						Рис.2
21		6		Медиана						50
22		18		Экстемальное значение
23		25		Минимальное						6
24		58		Максимальное						98
25		79		Нижний квартиль						22.5
26		91		Верхний квартиль						68.75
27				Выборочная дисперсия						860.48421
28				Исправленная выборочная дисперсия						817.46
29				Среднеквадратичное отклонение						25.18
30
31
32
33
34
35	Вывод:
36	С помощью функций MS Excel были вычислены следующие типические значения:
37	–      выборочное среднее (						85.64	)
38	–      медиана (mx = 50),
39	–      экстремальные значения (min xi = 6, max xi = 98),
40	–      нижний квартиль (Q25 = 22,5),
41	–      верхний квартиль (Q75 = 68,75),
42	–      выборочную дисперсию (Dв = 860,5),
43	–      исправленную выборочную дисперсию (D* = 817,5),
44	–      среднеквадратичное отклонение (S = 25,2) .

Теория исслед. двумерн. наб. да

	A	B	C	D	E	F	J
1	2 Функции рабочего листа для уравнения линейной регрессии
2	Рассмотрим, как решается задача нелинейной оптимизации с помощью средства поиска решений на примере построения линейного уравнения регрессии. Имеются две наблюдаемые величины х и у, например, объем реализации фирмы, торгующей подержанными автомобилями, за шесть недель ее работы. Значения этих наблюдаемых величин приведены в таблице 2, где х — отчетная неделя, а у — объем реализации за эту неделю.
3	Значения наблюдаемых величин
4	№	X	Y
5	1	1.10	39.82
6	2	2.50	34.93
7	3	3.00	33.03
8	4	3.50	28.66
9	5	8.60	2.63
10	6	6.35	15.82
11	7	8.10	6.05
12	8	9.90	0.85
13	9	12.60	14.53
14	10	7.00	12.78
15	11	8.50	5.21
16	12	13.10	16.32
17	13	3.14	32.18
18	14	2.70	33.34
19	15	11.00	9.47
20	Необходимо построить линейную модель у =mx+b, наилучшим образом описывающую наблюдаемые значения. Обычно m и b подбираются так, чтобы минимизировать сумму квадратов разностей между наблюдаемыми и теоретическими значениями зависимой переменной.
21
22
23

Иссследование двумерных наборов

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Для решения этой задачи отведем под переменные m и b ячейки С3 и DЗ, соответственно, а в ячейку E3 введем минимизируемую функцию   {=СУММКВРАЗН(В2:В16;DЗ+CЗ*А2:А16)}   Функция СУММКВРАЗН (SUMSQ) вычисляет сумму квадратов разностей для элементов указанных массивов
2
3	X	Y	Теоритическое значение Y	m	b
4	1.1	39.82	34.45735	-2.73366	37.46438
5	2.5	34.93	30.63023	-2.73366	37.46438	871.32535
6	3	33.03	29.26340						Динамика продаж
7	3.5	28.66	27.89657
8	8.6	2.63	13.95490
9	6.35	15.82	20.10564
10	8.1	6.05	15.32173
11	9.9	0.85	10.40115
12	12.6	14.53	3.02026
13	7	12.78	18.32876
14	8.5	5.21	14.22827
15	13.1	16.32	1.65343
16	3.14	32.18	28.88069
17	2.7	33.34	30.08350
18	11	9.47	7.39412
19
20
21	Теперь выберем команду Данные, Поиск решения и заполним открывшееся диалоговое окно Поиск решения.   Отметим, что на переменные m и b ограничения не налагаются. В результате вычислений средство поиска решений найдет: m = -2,734 и  b = 37,464.   Параметры m и b линейной модели у = mх +b из предыдущего раздела можно определить с помощью функций НАКЛОН (slope) и ОТРЕЗОК (intercept).   В ячейках С2 и D2 (рисунок 5) найдены m и b, соответственно, по формулам:   =НАКЛОН (В2:В16;А2:А16);   =ОТРЕЗОК(В2:В16;А2:А16).   Коэффициенты m и b можно найти и другим способом. Построим точечный график по диапазону ячеек А2:В16, выделим точки графика двойным щелчком, а затем щелкним их правой кнопкой мыши.   В раскрывшемся контекстном меню выберем команду Линии тренда (Trendline). В диалоговом окне Линия тренда (Trendline) на вкладке Тип (Туре) в группе Построение линии тренда (аппроксимация и сглаживание) (Trend/Regression type) выберем параметр Линейная (Linear), а на вкладке Параметры (Options) установим флажки Показывать уравнение на диаграмме (Display Equation on Chart) и поместим на диаграмму величину достоверности аппроксимации (R^2) (Display R-squared) (т.е. на диаграмму необходимо поместить значение квадрата коэффициента корреляции). Квадрат коэффициента корреляции равен 0,648, следовательно, линейная модель может быть использована для предсказания результатов.   По коэффициенту корреляции можно судить о правомерности использования линейного уравнения регрессии. Если он лежит в диапазоне от 0,9 до 1, то данную зависимость можно использовать для предсказания результата. Чем ближе к единице коэффициент корреляции, тем более обоснованно это указывает на линейную зависимость между наблюдаемыми величинами. Если коэффициент корреляции близок к -1, то это говорит об обратной зависимости между наблюдаемыми величинами.   На основе найденных коэффициентов уравнения регрессии можно определить теоретическое значение наблюдаемой величины у. Вычислим теоретическое значение у в ячейке С2 при х из А2 по формуле =$D$2*A2+$E$2.   Вычислим теоретические значения у.
22	Как видно из графика, квадрат коэффициента корреляции равен 0,648, следовательно, линейная модель может быть использована для предсказания результатов.   По коэффициенту корреляции можно судить о правомерности использования линейного уравнения регрессии. Если он лежит в диапазоне от 0,9 до 1, то данную зависимость можно использовать для предсказания результата. Чем ближе к единице коэффициент корреляции, тем более обоснованно это указывает на линейную зависимость между наблюдаемыми величинами. Если коэффициент корреляции близок к -1, то это говорит об обратной зависимости между наблюдаемыми величинами.
23
24
25
26
27
28

Экспоненциальная модель

	A	B	C	D	E	F	G	H	I	J
1	3 Экспоненциальная модель
2	Другой часто встречающейся на практике регрессионной моделью является экспоненциальная модель, которая описывается уравнением у = bтх.   Одномерную экспоненциальную модель можно построить графически.   В диапазоне ячеек В17:В19 введена формула построения линейного тренда:   {=ТЕНДЕНЦИЯ(В2:В16;А2:А16;А17:А19) }   В диапазоне ячеек С17:С19 введена формула построения экспоненциального тренда:   {=РОСТ(В2:В16;А2:А16;А17:А19)}   Линейный и экспоненциальный тренды тесно связаны между собой. В диапазон ячеек D2:D19 введена формула:   {=ЕХР(ТЕНДЕНЦИЯ(LN(В2:В16);А2:А16;А2:А19))}   Как видно из таблицы, значения в диапазонах С2:С19 и D2:D19 совпадают. В диапазоны ячеек F2:G2 и F3:G3 введены формулы   {=ЛИНЕЙН(В2:В16;А2:А16)}   {=ЛГРФПРИБЛ(В2:В16;А2:А16)}   для определения параметров линейной и экспоненциальной моделей. На графике приведены результаты построения экспоненциального уравнения тренда продажи.
3
4
5
6	X	Y	Теоритическое значение Y			m	b
7	1.1	39.82	34.45735	34.18395	Функция ЛИНЕЙН	-2.73366	-2.73366
8	2.5	34.93	30.63023	26.83426	ФУНКЦИЯ  ЛГРФПРИБЛ	0.84121	0.84121
9	3	33.03	29.26340	24.61174	Ln(m)
10	3.5	28.66	27.89657	22.57330
11	8.6	2.63	13.95490	9.34571
12	6.35	15.82	20.10564	13.79035
13	8.1	6.05	15.32173	10.18966		Динамика продаж
14	9.9	0.85	10.40115	7.46430
15	12.6	14.53	3.02026	4.67987
16	7	12.78	18.32876	12.32434
17	8.5	5.21	14.22827	9.50872
18	13.1	16.32	1.65343	4.29226
19	3.14	32.18	28.88069	24.02310
20	2.7	33.34	30.08350	25.92214
21	11	9.47	7.39412	6.17142
22	4.5	25.16291	18.98893	18.98893
23	9.6	11.22124	7.86172	7.86172
24	10	10.12778	7.33635	7.33635
25		Лин. прогноз	Нелин. прогноз	Преобразов. линейн. прогноз
26
27
28	Квадрат коэффициента корреляции экспоненциальной модели равен 0,3813 (Лист "Экспоненциальная модель")
29	и меньше квадрата коэффициента корреляции линейной модели (0,6485) (Лист "Исследование двумерных наборов")
30	Таким образом, в данном примере линейная модель более достоверно описывает зависимость между
31	наблюдаемыми величинами.