КОРЕНЬ(СЧЁТ(B4:B13)*G14-C14*C14)).

Для анализа результатов найдем значение функции Y(X) для всех заданных аргументов (столбец F). Видим, что расхождение между фактическими и полученными значениями достаточно заметно. Для вычисления коэффициента корреляции R нам понадобилось еще значение суммы квадратов функции (столбец G). В нашем случае R=0,722.

Содержимое клетки Е2 представлено в пользовательском формате вида +#,00"x";–0,00"х" с тем, чтобы отображался и знак плюс и буква Х. В строке 14 все формулы являются суммами вышележащих ячеек в диапазоне с 4 по 13 строки.

Полученное уравнение регрессии таково: Y=1,8+0,64X.

Таким образом, если нам понадобится вычислить ожидаемое значение прибыли Y в будущем, например, при капиталовложениях в сумме 20 единиц, нужно подставить их в найденную функцию Y=1,8+0,64*20=14,6. Однако достоверность такого предположения может оказаться не достаточно высокой, ввиду того, что линейное описание процесса, возможно, слишком примитивно. Техника аппроксимации более сложными функциями будет изучена ниже.

Сначала рассмотрим встроенные функции Excel (ЛИНЕЙН() и ТЕНДЕНЦИЯ()) для более быстрого нахождения коэффициентов уравнения линейной регрессии.

üЛИНЕЙН(<известное Y>;<известное X>) – вычисляет два коэффициента линейного уравнения регрессии для множества значений независимой переменной Х и зависимой – Y. Результат выводится в две смежные ячейки – сначала коэффициент при Х, затем – свободный член. Ввиду этого функция должна вводиться как функция обработки массива. Сначала в В4 вводим функцию (без фигурных скобок) и нажимаем Enter, далее выделяем одновременно ячейки В4 и С4 и переходим в режим редактирования клавишей F2. Потом нажимаем клавиши Ctrl+Shift+Enter (вместо обычного Enter).

Пример. Если исходные данные расположены как показано на рис. 2-3, и в В3:C3 введена функция

{=ЛИНЕЙН(B2:K2;B1:K1)},

результаты в клетках В4 и С4 можно интерпретировать как коэффициенты линейного уравнения регрессии y=0,6364x+1,8.

üТЕНДЕНЦИЯ(<известное Y>;<известное X>;<новое X>) – вычисляет ожидаемое новое значение Y для нового Х, если известны некоторые опытные значения X и Y. Вычисления делаются в предположении, что Х и Y зависят линейно.

Пример: Исходные данные расположены (рис. 2-3) в клетке G3, результат – в Н3. Видим, что в предположении линейного тренда при Х=12 ожидаемое Y=9,44.

Рис.2-4  

H4=ТЕНДЕНЦИЯ(B$2:K$2;B$1:K$1;G4).

	A	B	C	D	E	F	G	H	I	J	K
	X
	Y
		0,636	1,8					9,44		Рис.	2-3

Таким образом, при Х=12 ожидается Y=9,44.

Используя значения X и Y с помощью Excel, построим график, совмещенный с линией регрессии (линией тренда), как показано на рис. 2-4.

В Excel имеется очень простой способ строить линейную аппроксимацию равноотстоящих значений аргумента. Для этого нужно выделить известные значения прогнозируемой величины и потянуть за маркер заполнения, удерживая правую кнопку мыши. Затем, из появившегося контекстного меню (его фрагмент приведен на рис. 2-5) выбрать пункт Линейное приближение. В заполняемых клетках мы обнаружим значения, вычисленные системой для найденного ею линейного уравнения регрессии.

На рис.2-6 исходными данными являются 2,4,5. Остальные числа являются вычисленным прогнозом в предположении линейной связи аргументов в соответствии с найденным Excel уравнением.

5

6,67

8,17

9,67

11,17

12,67

Линейное приближение Экспоненциальное приближение Прогрессия   Рис.2-5 Рис. 2-6

Здесь же (рис. 2-7) доступно и Экспоненциальное приближение.

5

8,55

13,52

21,37

33,80

53,44

Рис. 2-7

Графическое отображение обеих кривых представлено на рис. 2-8.

С помощью средств деловой графики можно не только построить необходимые кривые, но получить линии тренда и соответствующие им уравнения Y(X). Здесь y=1,5x+0,6667 для линейного закона (обозначены кружками), y=1,368e^0,4581x – для экспоненты (точки). Исходные точки обведены овалом.

Рис.2-8  

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

Y = a+bX+cX²+dX³+eX⁴+...

Для розыска коэффициентов такого уравнения воспользуемся средством Поиск решения (вкладка Данные, группа Анализ).

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

	A	B	C	D	E
	Подбор коэффициентов регрессии
	Коэф. Y(х)	a	b	c	d
	a+bx	1,800	0,636
	a+bx+cx2	4,050	–0,489	0,102
	a+bx+cx2+dx3	–2,033	4,907	–1,067	0,071
			П о г р е	ш н о с	т и
	Аргумент X	Функция Y(Х)	Прямая	Пара- бола	3-я степень
		1,00	2,063	7,095	0,769
		5,00	3,714	2,305	0,851
		6,00	5,248	6,227	1,013
		5,00	0,428	1,608	0,003
		4,00	0,964	0,027	0,455
		3,00	6,855	3,240	1,663
		4,00	5,083	2,693	0,104
		6,00	0,794	0,471	0,644
		9,00	2,169	1,131	2,753
		10,00	3,372	0,371	1,387
	Сумма квадратов:	30,691	25,168	9,641
				Рис.3-1

В общем случае это выражение вида

(<Функция Y(x)> – <Функция регрессии>)2.

Для уравнения первого порядка (прямой)

(<Функция Y(x)> – (a+b(x))2.

Таким образом, для первой клетки погрешности

Прямой: C8=($B8–($B$3+$C$3*$A8))^2.

Аналогичные формулы заносятся (копируются) во все нижележащие клетки (область С9:С17). В клетке С18 вычисляется сумма погрешностей для всех точек С18=СУММ(С8:С17).

Нашей целью является приведение этой погрешности к минимуму путем изменения значений коэффициентов уравнения прямой (клеток В3 и С3). В исходном состоянии они пустые.

Для поиска оптимальных значений в окне Поиск решения в качестве целевой ячейки следует установить клетку С18, а в качестве изменяемых параметров – область В3:С3 (рис. 3-2). Результаты, полученные в изменяемых ячейках, соответствуют уравнению вида y=1,8+0,6364x. Общая погрешность приближения (клетка С18) составила 30,69.

Поиск решения

Установить целевую ячейку

$C$18

Равной: š максимальному значению

ž минимальному значению

Изменяя ячейки:––––––––––––––––

$B$3:$C$

Рис.3-2

Аналогичным образом заполняется столбец D8:D18 погрешностей для полинома второй степени (параболы).

Здесь D8=($B8–($B$4+$C$4*$A8+$D$4*$A8^2))^2.

В окне Поиск решения целевая ячейка – D18, изменяемые параметры – область В4:D4. Полученный результат соответствует уравнению

y = 4,05 – 0,4886x + 0,1023x2.

Аналогично для уравнения третьей степени

E8=($B8–($B$5+$C$5*$A8+$D$5*$A8^2+$E$5*A8^3))^2.

Рис.3-3

В окне Поиск решения целевая ячейка – E18, изменяемые параметры – область B4:E5. Результат описывается уравнением

y= – 2,0333 + 4,907x – 1,0676x2+ 0,0709x3.

Точно так же (в таблице не показано) может быть сформировано уравнение четвертой степени

y=–10,083 + 15,227x – 4,844x2+ 0,5869x3– 0,0235x4.

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

30,6909 Þ 25,1682 Þ 9,6408 Þ 0,5775.

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

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

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

Отметим, что средства деловой графики позволяют найти уравнения регрессии (до 6 степени включительно) и не прибегая к вычислениям. Если, после того как была построена кривая функции Y(X), щелкнуть на ней правой кнопкой мыши, в появившемся контекстном меню можно выбрать пункт Добавить линию тренда, который предъявляет окно Формат линия тренда. Здесь можно выбрать вид уравнения аппроксимации и его степень, а если установить флаг Показывать уравнение на диаграмме, то на графике мы увидим не только линию тренда, но и его уравнение. Здесь же можно визуально оценить поведение анализируемого процесса в будущем/прошлом, если установить Прогноз вперед/назад на заданное число единиц независимого аргумента Х. К сожалению, предъявляемая функция отображается как текст, и не может быть непосредственно использована в вычислениях.

Дата добавления: 2015-10-28