2015-05-18
2346
Параметры линейноймодели регрессии в Excel получают с помощью встроенной функции ЛИНЕЙН:
ЛИНЕЙН(изв_знач_Y, изв_знач_Х, конст, статистика).
В Calc аналогичная функция называется LINEST:
LINEST(изв_знач_Y, изв_знач_Х, тип линии, статистика).
Первые два аргумента задают диапазоны размещения выборочных данных для результативного и факторного признаков, соответственно. Два последних аргумента имеют логический тип с допустимыми значениями «истина» или «ложь» (1 или 0 в цифровом варианте). Если конст (тип линии) полагается равным «истина» либо отсутствует, то свободный член b 0 в уравнении регрессии может быть любым, в противном случае (конст – «ложь») b 0 принудительно полагается равным нулю. Последний аргумент статистика указывает, требуется ли вычислять дополнительную статистику по регрессии: если здесь введено значение «истина», то дополнительная статистика выдается, в противном случае (статистика – «ложь» или отсутствует) вычисляются только коэффициенты выборочного уравнения регрессии b 0 и b 1.
Поскольку в полном варианте (с дополнительной статистикой) функции ЛИНЕЙН и LINEST выдают массивы значений, их следует вводить специальным образом, а именно:
выделяют блок из пяти строк и двух столбцов, необходимый для размещения результатов;
в Мастере функций выбирают ЛИНЕЙН или LINEST;
в диалоговом окне указывают аргументы: (изв_знач_Y, изв_знач_Х, 1, 1);
команда на исполнение: <Ctrl>+<Shift>+<Enter> (! не используя кнопку «ОК»).
При правильном обращении к встроенной функции выделенная под результаты область заполняется следующим образом.
| Угловой коэффициент b 1 | Свободный член b 0 |
| Стандартная ошибка b 1 | Стандартная ошибка b 0 |
| Коэффициент детерминации R2 | Стандартная ошибка регрессии S |
| F-статистика Фишера | Число степеней свободы ν |
| Регрессионная дисперсия | Остаточная дисперсия |
Перечисленные здесь параметры модели линейной регрессии не нуждаются в дополнительном разъяснении. Напомним только, что статистика Фишера Fm,v при m = 1 равна квадрату статистики Стьюдента. Тогда наблюдаемое значение
t -статистики 
его используют при проверке гипотез о значимости параметров регрессионной модели.
Таким образом, построение уравнения регрессии и его статистический анализ, аналогичный по полноте рассмотренному выше «ручному» варианту, обеспечивается автоматизированными средствами MS Excel и OO Calc.
Продемонстрируем сказанное на том же примере изучения связи между размером прожиточного минимума Y и величиной расходов на питание X (рис. 13).
Рис. 13. Выборочные данные
1. Размер прожиточного минимума (Y) является результативным признаком, а величина расходов на питание (X) – факторным признаком.
2. Корреляционное поле (рис. 14), представляющее данные рис. 13, строим с помощью Мастера диаграмм (тип диаграммы – «Точечная»).
Рис. 14. Корреляционное поле для изучаемых признаков
3. По виду корреляционного поля предполагаем, что линейная функция f (x) = β0 + β1 ∙x пригодна для описания связи между Х и Y.
4. Для вычисления выборочного коэффициента корреляции в Excel используем функцию КОРРЕЛ, а в Calc – CORREL, в качестве аргументов укажем область размещения массивов X и Y (рис. 13):
КОРРЕЛ(В2:В6; С2:С6), или CORREL(В2:В6; С2:С6).
Полученная в результате оценка коэффициента корреляции rxy проверяется на существенное отличие от нуля.
В нашем примере получим rxy = 0,952, тогда наблюдаемое значение t -статистики:
Критические точки распределения Стьюдента для заданного уровня значимости a и числа степеней свободы n = n – 2 в Excel находят с помощью встроенной функции СТЬДРАСПОБР(a, n). Соответствующая функция в Calc – TINV(a, n).
Для нашего примера, по результатам применения СТЬДРАСПОБР(0,05;3) (или TINV(0,05;3)), найдём критическое значение t (a/2, n) = 3,18.
Так как 
коэффициент корреляции статистически значим.
5. Параметры модели линейной регрессии находим с помощью следующих функций:
ЛИНЕЙН(С2:С6; В2:В6; 1; 1) <Ctrl><Shift><Enter>, либо
LINEST(С2:С6; В2:В6; 1; 1) <Ctrl><Shift><Enter>.
Заметим, что здесь наиболее частая ошибка – несинхронное нажатие клавиш <Ctrl><Shift><Enter>. Ошибка сразу же обнаружится: вместо ожидаемых десяти значений получится всего одно! Для исправления вернитесь в строку формул и повторите запуск (<Ctrl><Shift><Enter>) аккуратно.
Результат работы функции ЛИНЕЙН (или LINEST) для нашего примера представлен на рис. 15.
Рис. 15. Оценки параметров линейной регрессии в примере
Оценки коэффициентов уравнения регрессии b 0 и b 1 указаны в ячейках В10 и А10 (рис. 15), соответственно, тогда выборочное уравнение регрессии имеет вид: 
= 6,25+0,775∙ х.
6. Оценка значимости коэффициентов b 0 и b 1.
Стандартные ошибки коэффициентов уравнения регрессии 
и 
приведены в ячейках А11 и В11 (рис. 15), соответственно. Тогда наблюдаемые значения t -статистик:
; 
.
Сравнение этих величин с найденным ранее (в п. 4) критическим значением t (a/2, n) = 3,18, свидетельствует о значимости коэффициентов уравнения регрессии.
Параметры модели, найденные выше, обеспечивают построение доверительных интервалов для теоретических коэффициентов уравнения регрессии b1 и b0. При заданном уровне значимости a = 0,05 имеем:
0,775 – 3,18×0,143 < b1 < 0,775 + 3,18×0,143, или
0,32 < b1 < 1,23;
6,25 – 3,18×1,65 < β0 < 6,25 + 3,18×1,65, или
1,003 < β0 < 11,497.
7. Коэффициент детерминации R2 = rxy 2 ≈ 0,91 приведен в ячейке А12 (рис. 15), его значимость следует из установленной выше (в п. 4) значимости коэффициента корреляции.
Таким образом, полученные в электронных таблицах результаты подтверждают правильность «ручного» варианта решения поставленной задачи. Понятно, что вывод также сохраняется.
Варианты к заданию № 1
В таблице (табл. 15) приведены данные величины прибыли (млн руб.) магазинов в зависимости от их площади (м2). Изучить парную зависимость этих признаков по приведённым выборочным значениям.
Таблица 15
| № варианта п/п | № магазина | ||||||||
| Вариант 1 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,5 | 0,7 | 1,1 | 1,5 | 2,0 | ||||
| Вариант 2 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,6 | 0,8 | 1,2 | 1,5 | 2,0 | ||||
| Вариант 3 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,5 | 0,8 | 1,2 | 1,5 | 1,9 | ||||
| Вариант 4 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,5 | 0,7 | 1,2 | 1,5 | 1,8 | ||||
| Вариант 5 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,5 | 0,7 | 1,2 | 1,5 | 2,0 | ||||
| Вариант 6 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,6 | 0,8 | 1,2 | 1,4 | 2,0 | ||||
Продолжение табл. 15
| № варианта п/п | № магазина | ||||||||
| Вариант 7 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,5 | 0,8 | 1,1 | 1,5 | 1,9 | ||||
| Вариант 8 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,5 | 0,7 | 1,0 | 1,5 | 1,8 | ||||
| Вариант 9 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,5 | 0,7 | 1,2 | 1,5 | 2,0 | ||||
| Вариант 10 | |||||||||
| Площадь | |||||||||
| Прибыль | 0,6 | 0,8 | 1,2 | 1,4 | 2,0 | ||||
