Пакет MS Excel позволяет при построении уравнения линейной регрессии большую часть работы сделать очень быстро. Важно понять, как интерпретировать полученные результаты. Для построения модели регрессии необходимо выбрать пункт Сервис\Анализ данных\Регрессия (в Excel 2007 этот режим находится в блоке Данные/Анализ данных/Регрессия). Затем полученные результаты скопировать в блок для анализа.

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

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

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

· линейной (у = а + 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 существуют встроенные функции, с помощью которых можно рассчитать параметры модели линейной регрессии. Но быстрее это сделает надстройка «Пакет анализа».

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

1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».

2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.

3. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

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

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

1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».

2. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.

3. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

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

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

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

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

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

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

Теперь, когда мы перейдем во вкладку «Данные» , на ленте в блоке инструментов «Анализ» мы увидим новую кнопку – «Анализ данных» .

Виды регрессионного анализа

Существует несколько видов регрессий:

• параболическая;
• степенная;
• логарифмическая;
• экспоненциальная;
• показательная;
• гиперболическая;
• линейная регрессия.

О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.

Линейная регрессия в программе Excel

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

Общее уравнение регрессии линейного вида выглядит следующим образом: У = а0 + а1х1 +…+акхк. В этой формуле Y означает переменную, влияние факторов на которую мы пытаемся изучить. В нашем случае, это количество покупателей. Значение x – это различные факторы, влияющие на переменную. Параметры a являются коэффициентами регрессии. То есть, именно они определяют значимость того или иного фактора. Индекс k обозначает общее количество этих самых факторов.

Разбор результатов анализа

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

Одним из основных показателей является R-квадрат . В нем указывается качество модели. В нашем случае данный коэффициент равен 0,705 или около 70,5%. Это приемлемый уровень качества. Зависимость менее 0,5 является плохой.

Ещё один важный показатель расположен в ячейке на пересечении строки «Y-пересечение» и столбца «Коэффициенты» . Тут указывается какое значение будет у Y, а в нашем случае, это количество покупателей, при всех остальных факторах равных нулю. В этой таблице данное значение равно 58,04.

Значение на пересечении граф «Переменная X1» и «Коэффициенты» показывает уровень зависимости Y от X. В нашем случае — это уровень зависимости количества клиентов магазина от температуры. Коэффициент 1,31 считается довольно высоким показателем влияния.

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

По территориям региона приводятся данные за 200Х г.

Задание:

1. Постройте поле корреляции и сформулируйте гипотезу о форме связи.

2. Рассчитайте параметры уравнения линейной регрессии

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

7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости .

Решение:

Решим данную задачу с помощью Excel.

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

Чтобы построить поле корреляции можно воспользоваться ППП Excel. Введите исходные данные в последовательности: сначала х, затем у.

Выделите область ячеек, содержащую данные.

Затем выберете: Вставка / Точечная диаграмма / Точечная с маркерами как показано на рисунке 1.

Рисунок 1 Построение поля корреляции

Анализ поля корреляции показывает наличие близкой к прямолинейной зависимости, так как точки расположены практически по прямой линии.

2. Для расчёта параметров уравнения линейной регрессии
воспользуемся встроенной статистической функцией ЛИНЕЙН .

Для этого:

1) Откройте существующий файл, содержащий анализируемые данные;
2) Выделите область пустых ячеек 5×2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики.
3) Активизируйте Мастер функций : в главном меню выберете Формулы / Вставить функцию .
4) В окне Категория выберете Статистические , в окне функция - ЛИНЕЙН . Щёлкните по кнопке ОК как показано на Рисунке 2;

Рисунок 2 Диалоговое окно «Мастер функций»

5) Заполните аргументы функции:

Известные значения у

Известные значения х

Константа - логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;

Статистика - логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

Щёлкните по кнопке ОК ;

Рисунок 3 Диалоговое окно аргументов функции ЛИНЕЙН

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

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

Рисунок 4 Результат вычисления функции ЛИНЕЙН

Получили уровнение регрессии:

Делаем вывод: С увеличением среднедушевого прожиточного минимума на 1 руб. среднедневная заработная плата возрастает в среднем на 0,92 руб.

Означает, что 52% вариации заработной платы (у) объясняется вариацией фактора х - среднедушевого прожиточного минимума, а 48% - действием других факторов, не включённых в модель.

По вычисленному коэффициенту детерминации можно рассчитать коэффициент корреляции: .

Связь оценивается как тесная.

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

Для уравнения прямой средний (общий) коэффициент эластичности определим по формуле:

Средние значения найдём, выделив область ячеек со значениями х, и выберем Формулы / Автосумма / Среднее , и то же самое произведём со значениями у.

Рисунок 5 Расчёт средних значений функции и аргумент

Таким образом, при изменении среднедушевого прожиточного минимума на 1% от своего среднего значения среднедневная заработная плата изменится в среднем на 0,51%.

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

Порядок действий следующий:

1) проверьте доступ к Пакету анализа . В главном меню последовательно выберите: Файл/Параметры/Надстройки .

2) В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

3) В окне Надстройки установите флажок Пакет анализа , а затем нажмите кнопку ОК .

Если Пакет анализа отсутствует в списке поля Доступные надстройки , нажмите кнопку Обзор , чтобы выполнить поиск.

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

4) В главном меню последовательно выберите: Данные / Анализ данных / Инструменты анализа / Регрессия , а затем нажмите кнопку ОК .

5) Заполните диалоговое окно ввода данных и параметров вывода:

Входной интервал Y - диапазон, содержащий данные результативного признака;

Входной интервал X - диапазон, содержащий данные факторного признака;

Метки - флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Константа - ноль - флажок, указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал - достаточно указать левую верхнюю ячейку будущего диапазона;

6) Новый рабочий лист - можно задать произвольное имя нового листа.

Затем нажмите кнопку ОК .

Рисунок 6 Диалоговое окно ввода параметров инструмента Регрессия

Результаты регрессионного анализа для данных задачи представлены на рисунке 7.

Рисунок 7 Результат применения инструмента регрессия

5. Оценим с помощью средней ошибки аппроксимации качество уравнений. Воспользуемся результатами регрессионного анализа представленного на Рисунке 8.

Рисунок 8 Результат применения инструмента регрессия «Вывод остатка»

Составим новую таблицу как показано на рисунке 9. В графе С рассчитаем относительную ошибку аппроксимации по формуле:

Рисунок 9 Расчёт средней ошибки аппроксимации

Средняя ошибка аппроксимации рассчитывается по формуле:

Качество построенной модели оценивается как хорошее, так как не превышает 8 - 10%.

6. Из таблицы с регрессионной статистикой (Рисунок 4) выпишем фактическое значение F-критерия Фишера:

Поскольку при 5%-ном уровне значимости, то можно сделать вывод о значимости уравнения регрессии (связь доказана).

8. Оценку статистической значимости параметров регрессии проведём с помощью t-статистики Стьюдента и путём расчёта доверительного интервала каждого из показателей.

Выдвигаем гипотезу Н 0 о статистически незначимом отличии показателей от нуля:

.

для числа степеней свободы

На рисунке 7 имеются фактические значения t-статистики:

t-критерий для коэффициента корреляции можно рассчитать двумя способами:

I способ:

где - случайная ошибка коэффициента корреляции.

Данные для расчёта возьмём из таблицы на Рисунке 7.

II способ:

Фактические значения t-статистики превосходят табличные значения:

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

Доверительный интервал для параметра a определяется как

Для параметра a 95%-ные границы как показано на рисунке 7 составили:

Доверительный интервал для коэффициента регрессии определяется как

Для коэффициента регрессии b 95%-ные границы как показано на рисунке 7 составили:

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

7. Полученные оценки уравнения регрессии позволяют использовать его для прогноза. Если прогнозное значение прожиточного минимума составит:

Тогда прогнозное значение прожиточного минимума составит:

Ошибку прогноза рассчитаем по формуле:

где

Дисперсию посчитаем также с помощью ППП Excel. Для этого:

1) Активизируйте Мастер функций : в главном меню выберете Формулы / Вставить функцию .

3) Заполните диапазон, содержащий числовые данные факторного признака. Нажмите ОК .

Рисунок 10 Расчёт дисперсии

Получили значение дисперсии

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

Доверительные интервалы прогноза индивидуальных значений у при с вероятностью 0,95 определяются выражением:

Интервал достаточно широк, прежде всего, за счёт малого объёма наблюдений. В целом выполненный прогноз среднемесячной заработной платы оказался надёжным.

Условие задачи взято из: Практикум по эконометрике: Учеб. пособие / И.И. Елисеева, С.В. Курышева, Н.М. Гордеенко и др.; Под ред. И.И. Елисеевой. - М.: Финансы и статистика, 2003. - 192 с.: ил.

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

1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);

2) вызвать Мастер диаграмм и выбрать в группе Тип – Точечная и сразу нажать Готово ;

3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма , в котором следует выбрать пункт Добавить линию тренда ;

4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная ;

5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).

6) В этой же вкладке можно активизировать переключатель Поместить на диаграмму величину достоверности аппроксимации (R^2) . Эта величина есть квадрат коэффициента корреляции (4.3) и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R 2 близок к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R 2 близок к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).

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

§4.3. Основные виды нелинейной регрессии

Параболическая и полиномиальная регрессии.

Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):

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

Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:

где числа а i (i =0,1,…, n ) называются коэффициентами полиномиальной регрессии .

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

Степенной зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением степенной регрессии Y на Х . Параметры а и b называются коэффициентами степенной регрессии .

ln =lna +b· lnx . (4.11)

Это уравнение описывает прямую на плоскости с логарифмическими координатными осями lnx и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных lnx i и lnу i находились ближе всего к прямой (4.11).

Показательная регрессия.

Показательной (или экспоненциальной ) зависимостью величины Y от величины Х называется зависимость вида:

(или ). (4.12)

Это уравнение называется уравнением показательной (или экспоненциальной ) регрессии Y на Х . Параметры а (или k ) и b называются коэффициентами показательной (или экспоненциальной ) регрессии .

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

ln =x· lna +lnb (или ln =k·x +lnb ). (4.13)

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).

Логарифмическая регрессия.

Логарифмической зависимостью величины Y от величины Х называется зависимость вида:

=a +b· lnx . (4.14)

Это уравнение называется уравнением логарифмической регрессии Y на Х . Параметры а и b называются коэффициентами логарифмической регрессии .

Гиперболическая регрессия.

Гиперболической зависимостью величины Y от величины Х называется зависимость вида:

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

В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n .

К сожалению, в Excel нет функции, вычисляющих коэффициенты гиперболической регрессии. В тех случаях, когда заведомо не известно, что измеряемые величины связаны обратной пропорциональностью, рекомендуется вместо уравнения гиперболической регрессии искать уравнение степенной регрессии, так в Excel имеется процедура ее нахождения. Если же между измеряемыми величинами предполагается гиперболическая зависимость, то коэффициенты ее регрессии придется вычислять с помощью вспомогательных расчетных таблиц и операций суммирования по формулам (4.16-4.17).