Подбор коэффициентов регрессии

↑

▷ Содержание

⇐ ПредыдущаяСтр 2 из 7Следующая ⇒

     M3(X3,Y3)

M2(X2,Y2)

M4(X4,Y4)

M1(X1,Y1)                                        X

погрешности D

¶A

¶B

Выведем эти выражения:

¶[å(Yi–A–BXi)2]

¶A

=2å(Yi–A–BXi)(–1)

¶[å(Yi–A–BXi)2]

¶B

 =2å(Yi–A–BXi)(–Xi)

Преобразуем полученные формулы и приравняем их 0

2å(–Yi +BXi+A) =0

2å(–XiYi +BXi2+AXi) =0

Сократим выражения на 2 и раскроем скобки. Тогда

–åYi +BåXi + Aå1 =0

–å(XiYi) +BåXi2+AåXi =0

Мы получили систему из двух линейных алгебраических уравнений, в которой неизвестными являются A и B, а сумма N единиц равна N (в нашем случае å1=4). Перенесем свободные члены в правую часть и для упрощения записи опустим индексы при знаке суммирования. Окончательно получим:

A

B

C

D

E

F

G

Уравнение регрессии

Y=

a+bx

=

1,8

+0.64x

R=

0,722

i

Xi

Yi

Xi*Yi

X2

Yрасч.

Y2

2,44

1,0

3,07

25,0

3,71

36,0

4,35

25,0

4,98

16,0

5,62

9,0

6,25

16,0

6,89

36,0

7,53

81,0

8,16

100,0

å

Рис.

 2-2

BåX + NA = åY

BåX2+AåX =åXY

Решив эту систему, получим:

B=

NåXY – åXåY

A=

åY-BåX

NåX2 – åXåX

N

Оценить функциональную близость (в линейном смысле) значений Х и Y можно с помощью коэффициента корреляции R, который находится по следующей формуле

R=

NåXY – åXåY

Ö(NåX2 – åXåX)  Ö(NåY2 – åYåY)

Принято считать, что при R£0,3 наблюдается слабая линейная связь, при R=0,3¸0,7 – средняя, при R³0,7 – сильная, при R³0,9 – весьма сильная связь, при R=1 – полная функциональная связь (все точки Y(X) лежат на одной прямой). Таким образом, R помогает оценить “надежность” наших предположений.

Необходимые вычисления выполним, пользуясь таблицей на рис. 2-2. Здесь клетки, заполненные числами с жирным шрифтом предполагают ввод формул. Клеточные функции E2, D2, G2 получены из соответствующих формул для значений B, A, R (см. выше). В этих формулах знак å означает сумму соответствующих величин (у нас 14-я строка), знак N – количество опытов (у нас 10), вычисляется функцией СЧЁТ).

D4=B4*C4, E4=B4^2, G4=C4^2, F4=E$2+D$2*B4,

E2=(СЧЁТ(B4:B13)*D14-B14*C14)/(СЧЁТ(B4:B13)*E14-B14*B14), D2=(C14-E2*B14)/СЧЁТ(B4:B13),

G2=(СЧЁТ(B4:B13)*D14-B14*C14)/(КОРЕНЬ(СЧЁТ(B4:B13)*E14-B14*B14)*

КОРЕНЬ(СЧЁТ(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

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-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 – для экспоненты (точки). Исходные точки обведены овалом.

0

Рис.2-8

Y = a+bX+cX2+dX3+eX4+ ...

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

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

A

B

C

D

E

Познавательные статьи:



Техника нижней прямой подачи мяча

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

Стандарт Порядок надевания противочумного костюма

Общеразвивающие упражнения без предметов