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

Инструкция . Укажите количество исходных данных. Полученное решение сохраняется в файле Word . Также автоматически создается шаблон решения в Excel . Примечание : если необходимо определить параметры параболической зависимости (y = ax 2 + bx + c), то можно воспользоваться сервисом Аналитическое выравнивание .
Ограничить однородную совокупность единиц, устранив аномальные объекты наблюдения можно через метод Ирвина или по правилу трех сигм (устранить те единицы, для которых значение объясняющего фактора отклоняется от среднего более, чем на утроенное среднеквадратичное отклонение).

Виды нелинейной регрессии

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

Уравнению регрессии первого порядка - это уравнение парной линейной регрессии .

Уравнение регрессии второго порядка это полиномальное уравнение регрессии второго порядка: y = a + bx + cx 2 .

Уравнение регрессии третьего порядка соответственно полиномальное уравнение регрессии третьего порядка: y = a + bx + cx 2 + dx 3 .

Чтобы привести нелинейные зависимости к линейной используют методы линеаризации (см. метод выравнивания):

1. Замена переменных.
2. Логарифмирование обеих частей уравнения.
3. Комбинированный.

y = f(x)	Преобразование	Метод линеаризации
y = b x a	Y = ln(y); X = ln(x)	Логарифмирование
y = b e ax	Y = ln(y); X = x	Комбинированный
y = 1/(ax+b)	Y = 1/y; X = x	Замена переменных
y = x/(ax+b)	Y = x/y; X = x	Замена переменных. Пример
y = aln(x)+b	Y = y; X = ln(x)	Комбинированный
y = a + bx + cx 2	x 1 = x; x 2 = x 2	Замена переменных
y = a + bx + cx 2 + dx 3	x 1 = x; x 2 = x 2 ; x 3 = x 3	Замена переменных
y = a + b/x	x 1 = 1/x	Замена переменных
y = a + sqrt(x)b	x 1 = sqrt(x)	Замена переменных

Пример . По данным, взятым из соответствующей таблицы, выполнить следующие действия:

1. Построить поле корреляции и сформулировать гипотезу о форме связи.
2. Рассчитать параметры уравнений линейной, степенной, экспоненциальной, полулогарифмической, обратной, гиперболической парной регрессии.
3. Оценить тесноту связи с помощью показателей корреляции и детерминации.
4. Дать с помощью среднего (общего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом.
5. Оценить с помощью средней ошибки аппроксимации качество уравнений.
6. Оценить с помощью F-критерия Фишера статистическую надежность результатов регрессионного моделирования. По значениям характеристик, рассчитанных в пп. 4, 5 и данном пункте, выбрать лучшее уравнение регрессии и дать его обоснование.
7. Рассчитать прогнозное значение результата, если прогнозное значение фактора увеличится на 15% от его среднего уровня. Определить доверительный интервал прогноза для уровня значимости α=0,05 .
8. Оценить полученные результаты, выводы оформить в аналитической записке.

Год	Фактическое конечное потребление домашних хозяйств (в текущих ценах), млрд. руб. (1995 г. - трлн. руб.), y	Среднедушевые денежные доходы населения (в месяц), руб. (1995 г. - тыс. руб.), х
1995	872	515,9
2000	3813	2281,1
2001	5014	3062
2002	6400	3947,2
2003	7708	5170,4
2004	9848	6410,3
2005	12455	8111,9
2006	15284	10196
2007	18928	12602,7
2008	23695	14940,6
2009	25151	16856,9

Решение. В калькуляторе последовательно выбираем виды нелинейной регрессии . Получим таблицу следующего вида.
Экспоненциальное уравнение регрессии имеет вид y = a e bx
После линеаризации получим: ln(y) = ln(a) + bx
Получаем эмпирические коэффициенты регрессии: b = 0.000162, a = 7.8132
Уравнение регрессии: y = e 7.81321500 e 0.000162x = 2473.06858e 0.000162x

Степенное уравнение регрессии имеет вид y = a x b
После линеаризации получим: ln(y) = ln(a) + b ln(x)
Эмпирические коэффициенты регрессии: b = 0.9626, a = 0.7714
Уравнение регрессии: y = e 0.77143204 x 0.9626 = 2.16286x 0.9626

Гиперболическое уравнение регрессии имеет вид y = b/x + a + ε
После линеаризации получим: y=bx + a
Эмпирические коэффициенты регрессии: b = 21089190.1984, a = 4585.5706
Эмпирическое уравнение регрессии: y = 21089190.1984 / x + 4585.5706

Логарифмическое уравнение регрессии имеет вид y = b ln(x) + a + ε
Эмпирические коэффициенты регрессии: b = 7142.4505, a = -49694.9535
Уравнение регрессии: y = 7142.4505 ln(x) - 49694.9535

Показательное уравнение регрессии имеет вид y = a b x + ε
После линеаризации получим: ln(y) = ln(a) + x ln(b)
Эмпирические коэффициенты регрессии: b = 0.000162, a = 7.8132
y = e 7.8132 *e 0.000162x = 2473.06858*1.00016 x

x	y	1/x	ln(x)	ln(y)
515.9	872	0.00194	6.25	6.77
2281.1	3813	0.000438	7.73	8.25
3062	5014	0.000327	8.03	8.52
3947.2	6400	0.000253	8.28	8.76
5170.4	7708	0.000193	8.55	8.95
6410.3	9848	0.000156	8.77	9.2
8111.9	12455	0.000123	9	9.43
10196	15284	9.8E-5	9.23	9.63
12602.7	18928	7.9E-5	9.44	9.85
14940.6	23695	6.7E-5	9.61	10.07
16856.9	25151	5.9E-5	9.73	10.13

Степенная регрессия

Степенная функция имеет вид y = bx a . Приведем эту функцию к линейному виду, для этого прологарифмируем обе части: . Пусть = y * , = x * , = b * , тогда y * = ax * + b * . Требуется найти два параметра: a и b * . Для этого составим функцию i * - (ax i * +b *)) 2 , раскроем скобки i * - ax i * - b *) 2 и составим систему:

Пусть А = i * , В = i * , С = i * x i * , D = i *2 , тогда система примет вид: aD + bA = C

Решим эту систему линейных алгебраических уравнений методом Крамера и, таким образом, найдем искомые значения параметров a и b * :

Таблица. Имеются точки

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

a = 1,000922 , b = 1,585807. Так как показатель степени переменной примерно равен единице, то график функции будет иметь вид прямой.

График функции y = 1,585807x 1,000922:

Блок-схема:

Параболическая регрессия

Квадратичная функция имеет вид y = ax 2 + bx + c, следовательно, требуется найти три параметра: a, b, c, с условием, что даны координаты n точек. Для этого составим функцию S = i - (ax i 2 + bx i + c)) 2 , раскроем скобки S = i - ax i 2 - bx i - c) 2 и составим систему:

Решим эту систему линейных алгебраических уравнений методом Крамера и, таким образом, найдем искомые значения параметров a, b и c:

Таблица. Имеются точки:

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

a = 0,5272728 , b = -5,627879 , c = 14,87333.

График функции y = 0,5272728x 2 - 5,627879x + 14,87333:

Блок-схема

Решение уравнений вида f(x)=0

Уравнение вида f(x) = 0 является нелинейным алгебраическим уравнением с одной переменной, где функция f(x) определена и непрерывна на конечном или бесконечном интервале a < x < b. Всякое значение C???, обращающее функцию f(x) в ноль, называется корнем уравнения f(x) = 0. Большинство алгебраических нелинейных уравнений вида f(x) = 0 аналитически (т.е. точно) не решается, поэтому на практике для нахождения корней часто используются численные методы.

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

Ещё один вид однофакторной регрессии – аппроксимация степенными полиномами вида:

Естественно желание получить как можно простую зависимость, ограничиваясь степенным полиномам второй степени, т.е. параболической зависимостью:
(5.5.2)

Вычислим частные производные по коэффициентам b 0 , b 1 и b 2 :

(5.5.3)

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

(5.5.4)

Решая систему нормальных уравнений (5.5.2) для конкретного случая значений x i * , y i * ;
получим оптимальные значения b 0 , b 1 и b 2 . Для аппроксимации зависимостью (5.5.2) и тем более (5.5.1) не получены простые формулы для вычисления коэффициентов и как правило их вычисление производят по стандартным процедурам в матричном виде:

(5.5.5)

На рис.5.5.1 приведён типовой пример аппроксимации параболической зависимостью:

9 (5;9)

(1;1)

1

1 2 3 4 5 х

Рис.5.5.1. Координаты экспериментальных точек и аппроксимиру-

щая их параболическая зависимость

Пример 5.1. Провести аппроксимацию результатов эксперимента, приведённых в таблице 5.1.1, линейным уравнением регрессии
.

Таблица 5.1.1

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

у

9

4

1 2 3 4 5 х

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

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

коэффициент линейной корреляции между переменными х и у :

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

Критическое значение критерия Стьюдента t крит находится по статистическим таблицам для рекомендуемого уровня значимости α=0.05 и для n -2 степеней свободы. Если вычисленное значение r xy не меньше критического значения r крит , то корреляционная связь между переменными x и y считается сушественной. Произведём вычисления:

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

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

Таким образом, получили линейное уравнение регрессии:

По уравнению регрессии проведём прямую линию на рис.5.1.2.

у (5;9.8)

9

4

(0;-0.2) 1 2 3 4 5 х

Рис.5.1.2. Координаты экспериментальных точек и аппроксимиру-

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

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

Таблица 5.1.2

Вычислим среднюю квадратическую ошибку и её отношение к среднему значению:

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

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

Из статистической таблицы для 3 степеней свободы выпишем строки с уровнем значимости -и значением критерия Стьюдента– t в таблицу 5.1.3.

Таблица 5.1.3

Уровень значимости коэффициентов уравнения регрессии:

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

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

Проверка:

Результат проверки – положительный, что свидетельствует о корректности проведённых вычислений.

Вычислим критерий Фишера:

при двух степенях свободы:

По статистическим таблицам находим критические значения критерия Фишера для двух рекомендуемых градаций уровня значимости:

Так как вычисленное значение критерия Фишера превосходит критическое дл уровня значимости 0,01, то будем считать, что уровень значимости по критерию Фишера меньше 0,01, что будем считать удовлетворительным.

Вычислим коэффициент множественной детерминации:

для двух степеней свободы

По статистической таблице для рекомендуемого уровня значимости 0,05и двух найденных степеней свободы находим критическое значение коэффициента множественной детерминации:

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

Таким образом, полученные расчётные параметры по отношению стандартной ошибки к среднему значению и уровню значимости по критерию Стьюдента являются неудовлетворительными, поэтому целесообразно для аппроксимации подобрать другую аппроксимирующую зависимость.

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

Экспериментальное распределение случайных чисел, приведённое в таблице 5.1.1, при аппроксимации линейной зависимостью, не привело к удовлетворительному результату, в т.ч. по незначимости коэффициента уравнения регрессии при свободном члене, поэтому для улучшения качества аппроксимации попробуем её провести линейной зависимостью без свободного члена:

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

Таким образом, получили уравнение регрессии:

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

Таблица 5.2.1

x i

По уравнению регрессии
на рис.5.2.1 проведём прямую линию.

у (5;9. 73 )

(0;0) 1 2 3 4 5 х

Рис.5.2.1. Координаты экспериментальных точек и аппроксимиру-

ющая их линейная зависимость

Для оценки качества аппроксимации проведём вычисления показателей качества аналогично вычислениям, приведённым в примере 5.1.

(осталось старым);

с 4-мя степенями свободы;

для

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

Пример 5.3. Для улучшения качества аппроксимации примеров 5.1 и 5.2 проведём нелинейную аппроксимацию зависимостью
. Для этого первоначально произведём промежуточные вычисления и их результаты поместим в таблицу 5.3.1.

Значения

Таблица 5.3.1

X 2
(lnX ) 2
lnX·lnY

Дополнительно вычислим:

Произведём аппроксимацию зависимостью
. По формулам (5.3.7), (5.3.8) вычислим коэффициентыb 0 и b 1 :

По формулам (5.3.11) вычислим коэффициенты A 0 и A 1 :

Для вычисления стандартной ошибки проведены промежуточные вычисления, представленные в таблице 5.3.2.

Таблица 5.3.2

Y i	y i

Сумма: 7,5968

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

Пример 5.4. Попробуем провести аппроксимацию ещё одной нелинейной зависимостью
. По формулам (5.3.9), (5.3.10) по данным таблицы 5.3.1 вычислим коэффициентыb 0 и b 1 :

Получили промежуточную зависимость:

По формулам (5.3.13) вычислим коэффициенты C 0 и C 1 :

Получили окончательную зависимость:

Для вычисления стандартной ошибки проведём промежуточные вычисления и поместим их в таблицу 5.4.1.

Таблица 5.4.1

Y i	y i

Сумма: 21,83152

Вычислим стандартную ошибку:

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

Пример 5.5. Аппроксимация экспериментального распределения случайных чисел математической зависимостью y = b · lnx

Исходные данные как и в предыдущих примерах приведены в таблице 5.4.1 и на рис.5.4.1.

Таблица 5.4.1

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

. По формуле (5.4.3) вычислим коэффициент b :

Для оценки качества аппроксимации проведём промежуточные вычисления, представленные в таблице 5.4.2, по которым вычислим величину ошибки и отношение стандартной ошибки к среднему значению.

Таблица 5.4.2

Так как по отношению стандартной ошибки к среднему значению превышено рекомендуемое значение 0,05, то результат будем считать неудовлетворительным. В частности, отметим, что наибольшее отклонение даёт значение х=1, так как при этом значении lnx =0. Поэтому проведём аппроксимацию зависимстью y = b 0 +b 1 ·lnx

Вспомогательные вычисления представим в виде таблицы 5.4.3.

Таблица 5.4.3

По формулам (5.4.6) и (5.4.7) вычислим коэффициенты b 0 и b 1 :

9 (5;9.12)

4

1 (1;0.93)

1 2 3 4 5 х

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

Уровень значимости чуть выше рекомендованного значения 0,05 (
).

Ввиду того, что по главному показателю – отношению стандартной ошибки к среднему значению получено почти двукратное превышение рекомендуемого уровня 0,05 результаты будем считать приемлемыми. Отметим, что вычисленное значение критерия Стьюдента t b 0 =2,922 отличается от критического
сравнительно на небольшую величину.

Пример 5.6. Проведём аппроксимацию экспериментальных данных примера 5.1 гиперболической зависимостью
. Для того, чтобы вычислить коэффициентовb 0 и b 1 проведём предварительные вычисления, приведённые в таблице 5.6.1.

Таблица 5.6.1

X i	x i =1/X i		x i 2	x i y i

По результатам таблицы 5.6.1 по формулам (5.4.8) и (5.4.9) вычислим коэффициенты b 0 и b 1 :

Таким образом, получено гиперболическое уравнение регрессии

.

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

Таблица 5.6.2

X i

По результатам таблицы 5.6.2 вычислим стандартную ошибку и отношение стандартной ошибки к среднему значению:

Ввиду того, что отношение стандартной ошибки к среднему значению превышает рекомендуемое значение 0,05 делаем заключение о непригодности результатов аппроксимации.

Пример 5.7.

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

Вычислим коэффициенты этой зависимости b 0 , b 1 , b 11 в матричном виде по формуле:

Нелинейные уравнения регрессии, связывающие результативный показатель с оптимальными значениями проведения профилактических работ башенных кранов, получены с помощью процедуры множественной регрессии пакета прикладных программ Statistica 6.0. Далее приведем результаты регрессионного анализа для результативного показателя эффективности по таблице 5.7.1.

Таблица 5.7.1

В таблице 5.7.2 приведены результаты нелинейной регрессии для результативного показателя эффективности и в таблице 5.7.3 результаты анализа остатков.

Таблица 5.7.2

Таблица 5.7.3

Рис. 3.7.36. Анализ остатков.

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

Отношение стандартной ошибки к среднему значению:

14780/1017890=0,0145 < 0,05.

Так как отношение стандартной ошибки к среднему значению не превышает рекомендуемого значения 0,05 то результаты аппроксимации можно считать приемлемыми. В качестве недостатка по таблице 5.7.2 следует отметить превышение рекомендуемого уровня значимости 0.05 всеми вычисленными коэффициентами.

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

где , – параметр, показывающий среднее значение результативного признака при условии полной изоляции влияния фактора (х=0); – коэффициент пропорциональности изменения результата при условии абсолютного прироста признака-фактора на каждую его единицу; с – коэффициент ускорения (замедления) прироста результативного признака на каждую единицу фактора.

Положив в основу вычисления параметров , , с способ наименьших квадратов и приняв условно срединное значение ранжированного ряда за начальное, будем иметь Σх=0, Σх 3 =0. При этом система уравнений в упрощенном виде будет:

Из этих уравнений можно найти параметры , , с, которые в общем виде можно записать так:

(11.20)

(11.22)

Отсюда видно, что для определения параметров , , с необходимо рассчитать следующие значения: Σ у, Σ ху, Σ х 2 , Σ х 2 у, Σ х 4 . С этой целью можно воспользоваться макетом табл. 11.9.

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

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

Параболической регрессии

№ п.п.	х	у	ху	х 2	х 2 у	х 4
	х 1	у 1	х 1 у 1
	х 2	у 2	х 2 у 2
…	…	…	…	…	…	…
n	х n	у n	х n у n
Σ	Σх	Σу	Σху	Σх 2	Σх 2 у	Σх 4

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

Т а б л и ц а 11.10. Расчет вспомогательных данных для уравнения

Параболической регрессии

№ п.п.	х, %	у, тыс.т	ху	х 2	х 2 у	х 4
	1,0	5,0	5,0	1,0	5,0	1,0
	1,5	7,0	10,5	2,3	15,8	5,0
…	…	…	…	…	…	…
n	8,0	20,0	160,0	64,0
Σ

Подставим конкретные значения Σ у=495, Σ ху=600, Σ х 2 =750, Σ х 2 у=12375, Σ х 4 =18750, имеющиеся в табл. 11.10, в формулы (11.20), (11.21), (11.22). Получим

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

(11.23)

Уравнение 11.23 показывает, что в условиях заданной выборочной совокупности средний урожай (валовой сбор) картофеля (10 тыс. ц) может быть получен без влияния изучаемого фактора – повышения удельного веса посевов культуры в структуре посевных площадей, т.е. при таком условии, когда колебания удельного веса посевов не будут оказывать воздействие на размер урожая картофеля (х=0). Параметр (коэффициент пропорциональности) в=0,8 показывает, что каждый процент повышения удельного веса посевов обеспечивает прирост урожая в среднем на 0,8 тыс. т, а параметр с=0,1 свидетельствует о том, что на один процент (в квадрате) ускоряется приращение урожая в среднем на 0,1 тыс. т картофеля.

Регрессионный и корреляционный анализ – статистические методы исследования. Это наиболее распространенные способы показать зависимость какого-либо параметра от одной или нескольких независимых переменных.

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

Регрессионный анализ в Excel

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

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

Регрессия бывает:

• линейной (у = а + bx);
• параболической (y = a + bx + cx 2);
• экспоненциальной (y = a * exp(bx));
• степенной (y = a*x^b);
• гиперболической (y = b/x + a);
• логарифмической (y = b * 1n(x) + a);
• показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

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

Модель линейной регрессии имеет следующий вид:

У = а 0 + а 1 х 1 +…+а к х к.

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

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

Активируем мощный аналитический инструмент:

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.



Корреляционный анализ в Excel

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

Если связь имеется, то влечет ли увеличение одного параметра повышение (положительная корреляция) либо уменьшение (отрицательная) другого. Корреляционный анализ помогает аналитику определиться, можно ли по величине одного показателя предсказать возможное значение другого.

Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

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

Ставим курсор в любую ячейку и нажимаем кнопку fx.

1. В категории «Статистические» выбираем функцию КОРРЕЛ.
2. Аргумент «Массив 1» - первый диапазон значений – время работы станка: А2:А14.
3. Аргумент «Массив 2» - второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.

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

Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

Полученные коэффициенты отобразятся в корреляционной матрице. Наподобие такой:

Корреляционно-регрессионный анализ

На практике эти две методики часто применяются вместе.

Пример:

Теперь стали видны и данные регрессионного анализа.