X-PDF

Регрессионный анализ. Параметры линейноймодели регрессии в Excel получают с помощью встроенной функции ЛИНЕЙН:

Поделиться статьей

Параметры линейноймодели регрессии в Excel получают с помощью встроенной функции ЛИНЕЙН:

ЛИНЕЙН(изв_знач_Y, изв_знач_Х, конст, статистика).

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

LINEST(изв_знач_Y, изв_знач_Х, тип линии, статистика).

Первые два аргумента задают диапазоны размещения выборочных данных для результативного и факторного признаков, соответственно. Два последних аргумента имеют логический тип с допустимыми значениями «истина» или «ложь» (1 или 0 в цифровом варианте). Если конст (тип линии) полагается равным «истина» либо отсутствует, то свободный член b 0 в уравнении регрессии может быть любым, в противном случае (конст – «ложь») b 0 принудительно полагается равным нулю. Последний аргумент статистика указывает, требуется ли вычислять дополнительную статистику по регрессии: если здесь введено значение «истина», то дополнительная статистика выдается, в противном случае (статистика – «ложь» или отсутствует) вычисляются только коэффициенты выборочного уравнения регрессии b 0 и b 1.

Поскольку в полном варианте (с дополнительной статистикой) функции ЛИНЕЙН и LINEST выдают массивы значений, их следует вводить специальным образом, а именно:

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

­ в Мастере функций выбирают ЛИНЕЙН или LINEST .

­ в диалоговом окне указывают аргументы: (изв_знач_Y, изв_знач_Х, 1, 1) .

­ команда на исполнение: &lt .Ctrl&gt .+&lt .Shift&gt .+&lt .Enter&gt . (! не используя кнопку «ОК»).

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

Угловой коэффициент 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).

Представленная информация была полезной?
ДА
58.54%
НЕТ
41.46%
Проголосовало: 984

Для нашего примера, по результатам применения СТЬДРАСПОБР(0,05 .3) (или TINV(0,05 .3)), найдём критическое значение t (a/2, n) = 3,18.

Так как коэффициент корреляции статистически значим.

5. Параметры модели линейной регрессии находим с помощью следующих функций:

ЛИНЕЙН(С2:С6 . В2:В6 . 1 . 1) &lt .Ctrl&gt .&lt .Shift&gt .&lt .Enter&gt ., либо

LINEST(С2:С6 . В2:В6 . 1 . 1) &lt .Ctrl&gt .&lt .Shift&gt .&lt .Enter&gt ..

Заметим, что здесь наиболее частая ошибка – несинхронное нажатие клавиш &lt .Ctrl&gt .&lt .Shift&gt .&lt .Enter&gt .. Ошибка сразу же обнаружится: вместо ожидаемых десяти значений получится всего одно! Для исправления вернитесь в строку формул и повторите запуск (&lt .Ctrl&gt .&lt .Shift&gt .&lt .Enter&gt .) аккуратно.

Результат работы функции ЛИНЕЙН (или 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 &lt . b1 &lt . 0,775 + 3,18×0,143, или

0,32 &lt . b1 &lt . 1,23 .

6,25 – 3,18×1,65 &lt . β0 &lt . 6,25 + 3,18×1,65, или

1,003 &lt . β0 &lt . 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
                   

Поделиться статьей
Автор статьи
Анастасия
Анастасия
Задать вопрос
Эксперт
Представленная информация была полезной?
ДА
58.54%
НЕТ
41.46%
Проголосовало: 984

или напишите нам прямо сейчас:

Написать в WhatsApp Написать в Telegram

ОБРАЗЦЫ ВОПРОСОВ ДЛЯ ТУРНИРА ЧГК

Поделиться статьей

Поделиться статьей(Выдержка из Чемпионата Днепропетровской области по «Что? Где? Когда?» среди юношей (09.11.2008) Редакторы: Оксана Балазанова, Александр Чижов) [Указания ведущим:


Поделиться статьей

ЛИТЕЙНЫЕ ДЕФЕКТЫ

Поделиться статьей

Поделиться статьейЛитейные дефекты — понятие относительное. Строго говоря, де­фект отливки следует рассматривать лишь как отступление от заданных требований. Например, одни


Поделиться статьей

Введение. Псковская Судная грамота – крупнейший памятник феодального права эпохи феодальной раздробленности на Руси

Поделиться статьей

Поделиться статьей1. Псковская Судная грамота – крупнейший памятник феодального права эпохи феодальной раздробленности на Руси. Специфика периода феодальной раздробленности –


Поделиться статьей

Нравственные проблемы современной биологии

Поделиться статьей

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


Поделиться статьей

Семейство Первоцветные — Primulaceae

Поделиться статьей

Поделиться статьейВключает 30 родов, около 1000 видов. Распространение: горные и умеренные области Северного полушария . многие виды произрастают в горах


Поделиться статьей

Вопрос 1. Понятие цены, функции и виды. Порядок ценообразования

Поделиться статьей

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


Поделиться статьей

или напишите нам прямо сейчас:

Написать в WhatsApp Написать в Telegram
Заявка
на расчет