Регрессионный анализ в excel пример

Содержание:

Использование Пакета анализа EXCEL для построения множественной линейной регрессионной модели
Линейная регрессия в Excel
Основные задачи и виды регрессии
- Инструкция построения модели
Основные задачи и виды регрессии
- Инструкция построения модели
Разбор результатов анализа
Решение эконометрики в Экселе
Пакет анализа Excel (программа «Регрессия»)
Недостаток R2{\displaystyle R^{2}} и альтернативные показатели
Анализ результатов регрессии для R-квадрата
Изучение результатов и выводы

Использование Пакета анализа EXCEL для построения множественной линейной регрессионной модели

Проведем множественный регрессионный анализ с помощью надстройки MS EXCEL Пакет анализа .

Эффективно использовать надстройку Пакет анализа могут только пользователи знакомые с теорией множественного регрессионного анализа .

В данной статье решены следующие задачи:

Показано как в MS EXCEL выполнить регрессионный анализ с помощью надстройки Пакет анализа (инструмент Регрессия), т.е. как вызвать надстройку и правильно заполнить входные данные;
Даны пояснения по разделам отчета, формированного надстройкой;
Даны комментарии обо всех показателях, рассчитанных надстройкой, и приведены ссылки на соответствующие разделы статей, посвященные простой линейной регрессии .

В надстройке Пакет анализа для построения линейной регрессионной модели (как простой , так и множественной ) имеется специальный инструмент Регрессия .

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

Входной интервалY : ссылка на массив значений переменной Y. Ссылку можно указать с заголовком. В этом случае, при выводе результатов надстройка использует Ваш заголовок (для этого в окне требуется установить галочку Метки );
Входной интервал Х : ссылка на значения переменных Х (нужно указать все столбцы со значениями Х). Ссылку рекомендуется делать на диапазон с заголовками (в окне не забудьте установить галочку Метки );
Константа-ноль : если галочка установлена, то надстройка подбирает плоскость регрессии с b =0;
Уровень надежности : Это значение используется для построения доверительных интервалов для наклона и сдвига . Уровень надежности = 1- альфа . Если галочка не установлена или установлена, но уровень значимости = 95%, то надстройка все равно рассчитывает границы доверительных интервалов, причем дублирует их. Если галочка установлена, а уровень надежности отличен от 95%, то рассчитываются 2 доверительных интервала : один для 95%, другой для введенного значения. Для демонстрации вышесказанного введем 90%;
Выходной интервал: диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона;
Остатки : будут вычислены остатки модели , т.е. разница между наблюденными и предсказанными значениями Yi для всех наблюдений n;
Стандартизированные остатки : Вышеуказанные значения остатков будут поделены на значение их стандартного отклонения ;
График остатков : Для каждой переменной X _j будет построена точечная диаграмма : значения остатков и соответствующее значение Х _ji (при прогнозировании на основании значений 2-х переменных Х будет построено 2 диаграммы (j=1 и 2));
График подбора: Для каждой переменной X _j будут построены точечные диаграммы с двумя рядами данных : точки данных (X _ji ;Y _i ) и (X _ji ;Y _{iпредсказанное} );
График нормальной вероятности: Будет построена точечная диаграмма с названием График нормального распределения . По сути — это график значений переменной Y, отсортированных по возрастанию .

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

Тот же результат можно получить с помощью формул (см. файл примера лист Надстройка , столбцы I:T).

Результаты вычислений, выполненных надстройкой, полностью совпадают с вычислениями сделанными нами в статье про множественную линейную регрессию с помощью функций ЛИНЕЙН() , ТЕНДЕНЦИЯ() и др. Использование альтернативных формул помогает разобраться с алгоритмом расчета показателей регрессии.

Отчет, сформированный надстройкой, состоит из следующих разделов:

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

Abc и xyz-анализ в оптимизации ассортимента компании

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

щелкаем по кнопке «Анализ данных»;
в открывшемся окне нажимаем на кнопку «Регрессия»;
в появившуюся вкладку вводим диапазон значений для Y (количество уволившихся работников) и для X (их зарплаты);
подтверждаем свои действия нажатием кнопки «Ok».

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

Обратите внимание! В Excel есть возможность самостоятельно задать место, которое вы предпочитаете для этой цели. Например, это может быть тот же лист, где находятся значения Y и X, или даже новая книга, специально предназначенная для хранения подобных данных

Основные задачи и виды регрессии

Еженедельный технический анализ финансовых рынков

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

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

Основные задачи построения регрессионной модели заключаются в следующем:

Отбор значимых независимых переменных (Х1, Х2, …, Xk).
Выбор вида функции.
Построение оценок для коэффициентов.
Построение доверительных интервалов и функции регрессии.
Проверка значимости вычисленных оценок и построенного уравнения регрессии.

Регрессионный анализ бывает нескольких видов:

парный (1 зависимая и 1 независимая переменные);
множественный (несколько независимых переменных).

Уравнения регрессии бывает двух видов:

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

Инструкция построения модели

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

Для дальнейшего вычисления следует использоваться функцию «Линейн ()», указывая Значения Y, Значения Х, Конст и статистику. После этого определите множество точек на линии регрессии с помощью функции «Тенденция» — Значения Y, Значения Х, Новые значения, Конст. При помощи заданных параметров вычислите неизвестное значение коэффициентов, опираясь на заданные условия поставленной задачи.

Основные задачи и виды регрессии

Фундаментальный анализ рынка и методика его проведения

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

Основные задачи построения регрессионной модели заключаются в следующем:

Отбор значимых независимых переменных (Х1, Х2, …, Xk).
Выбор вида функции.
Построение оценок для коэффициентов.
Построение доверительных интервалов и функции регрессии.
Проверка значимости вычисленных оценок и построенного уравнения регрессии.

Регрессионный анализ бывает нескольких видов:

парный (1 зависимая и 1 независимая переменные);
множественный (несколько независимых переменных).

Уравнения регрессии бывает двух видов:

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

Инструкция построения модели

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

КОРРЕЛЯЦИОННО-РЕГРЕССИОННЫЙ АНАЛИЗ В MS EXCEL

1. Создайте файл исходных данных в MS Excel (например, таблица 2)

2. Построение корреляционного поля

Для построения корреляционного поля в командной строке выбираем меню Вставка/ Диаграмма . В появившемся диалоговом окне выберите тип диаграммы: Точечная ; вид: Точечная диаграмма , позволяющая сравнить пары значений (Рис. 22).

Рисунок 22 – Выбор типа диаграммы

Рисунок 23– Вид окна при выборе диапазона и рядов Рисунок 25 – Вид окна, шаг 4

2. В контекстном меню выбираем команду Добавить линию тренда.

3. В появившемся диалоговом окне выбираем тип графика (в нашем примере линейная) и параметры уравнения, как показано на рисунке 26.

Нажимаем ОК. Результат представлен на рисунке 27.

Рисунок 27 – Корреляционное поле зависимости производительности труда от фондовооруженности

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

от коэффициента сменности оборудования

3. Построение корреляционной матрицы.

Для построения корреляционной матрицы в меню Сервис выбираем Анализ данных.

С помощью инструмента анализа данных Регрессия , помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Для этого необходимо проверить доступ к пакету анализа. В главном меню последовательно выберите Сервис/ Надстройки . Установите флажок Пакет анализа (Рисунок 29)

Рисунок 30 – Диалоговое окно Анализ данных

После нажатия ОК в появившемся диалоговом окне указываем входной интервал (в нашем примере А2:D26), группирование (в нашем случае по столбцам) и параметры вывода, как показано на рисунке 31.

Результат расчетов представлен в таблице 4.

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

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

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

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

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

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

Решение эконометрики в Экселе

Задача 1. Парная регрессия.
Для исходных данных, приведенных ниже, рассчитайте

коэффициенты линейного регрессионного уравнения
рассчитайте остаточную дисперсию
вычислите значения коэффициентов корреляции и детерминации
рассчитайте коэффициент эластичности
рассчитайте доверительные границы уравнения регрессии (по уровню 0,95, t=2,44)
в одной системе координат постройте: уравнение регрессии, экспериментальные точки, доверительные границы уравнения регрессии

Отчет (pdf), Расчеты (xlsx)

Задача 2. Построить требуемое уравнение регрессии. Вычислить коэффициент детерминации, коэффициент эластичности, бета коэффициент и дать их смысловую нагрузку в терминах задачи. Проверить адекватность уравнения с помощью F теста. Найти дисперсии оценок и 95% доверительные интервалы для параметров регрессии. Данные взять из таблицы. Найти прогнозируемое значение объясняемой переменной для некоторого значения объясняющей переменной, не заданной в таблице. Построить уравнение линейной регрессии объема валового выпуска (в млн. руб.) от стоимости основных производственных фондов (млн. руб.).

Отчет (pdf), Расчеты (xlsx)

Задача 3. Множественная регрессия.
Построить требуемое уравнение регрессии. Вычислить коэффициент детерминации, частные коэффициенты эластичности, частные бета коэффициенты и дать их смысловую нагрузку в терминах задачи. Проверить адекватность уравнения с помощью F теста. Найти оценку матрицы ковариаций оценок параметров регрессии и 95% доверительные интервалы для параметров регрессии. Проверить наличие мультиколлинеарности в модели. Данные взять из таблицы.
Построить уравнение линейной регрессии себестоимости единицы товара (в сотнях руб.) от величины энерговооруженности (кВт) и производительности труда (тов/час).

Отчет (pdf), Расчеты (xlsx)

Задача 4. Трендовые модели
Проверить ряд на наличие тренда. Сгладить ряд методом простой скользящей средней $(m = 3)$, экспоненциальным сглаживанием $(\alpha = 0,3; \alpha = 0,8)$. Построить исходный и сглаженные ряды. На основании построенных рядов определить вид трендовой модели. Построить трендовую модель.
Сделать прогноз изучаемого признака на два шага вперед.
87; 77; 75; 74; 69; 66; 62; 61; 59; 57; 57; 52; 50; 48; 46; 43; 43; 41; 38; 35

Отчет (pdf), Расчеты (xlsx)

Задача 5.
По заданным статистическим данным постройте линейную модель множественной регрессии и исследуйте её.

Постройте линейную модель множественной регрессии.
Запишите стандартизованное уравнение множественной регрессии. На основе стандартизованных коэффициентов регрессии и средних коэффициентов эластичности ранжировать факторы по степени их влияния на результат.
Найдите коэффициенты парной, частной и множественной корреляции. Проанализируйте их.
Найдите скорректированный коэффициент множественной детерминации. Сравните его с нескорректированным (общим) коэффициентом детерминации.
С помощью F-критерия Фишера оценить статистическую надежность уравнения регрессии и коэффициента детерминации $R^2_{y x_1 x_2}$.
С помощью частных F-критериев Фишера оценить целесообразность включения в уравнение множественной регрессии фактора $x_1$ после $x_2$ и фактора $x_2$ после $x_1$.
Составьте уравнение линейной парной регрессии, оставив лишь один значащий фактор.

Отчет (pdf), Расчеты (xlsx)

Задача 6. По данным опроса 15 женщин, находящихся в роддоме, исследовать
зависимость веса новорожденного (у) от среднего числа сигарет (х), выкуриваемых матерью в день, с учетом числа уже имеющихся у матери детей (z).

Может пригодиться: примеры решений по эконометрике, лабораторные по статистике в Excel

Заказать решение задач по эконометрике просто

Оценим уже сегодня

Пакет анализа Excel (программа «Регрессия»)

Расчет параметров уравнения линейной регрессии, проверку их статистической значимости и построения интервальных оценок можно выполнить значительно быстрее автоматически при использовании Пакета анализа Excel (программа «Регрессия»)

Пусть исходные данные примера 2.1 (расходы на питание – личный доход) представлены в Excel.

Выбираем команду Анализ данных→Регрессия.

В диалоговом окне режимаРегрессиязадаются следующие параметры:

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

Входной интервал Х – вводится ссылка на ячейки, содержащие факторные признаки.

Метки – установите флажок в активное состояние, если выделены и заголовки столбцов.

Константа- ноль – установите флажок в активное состояние, если оцениваете регрессионное уравнение без свободного члена.

При необходимости задаются и другие параметры.

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

Результаты работы программы «Регрессия» полностью совпадают с полученными ранее расчетами.

При необходимости выводятся предсказанные значения

Коэффициенты регрессии, их стандартные ошибки и коэффициент детерминации составляют:

a= -1,75; b=0,775;

= 1,65;
=0,143;
= 0,907

Результаты регрессионного анализа принято записывать в виде:

ȳ= -1,75+0,775х ;

= 0,907,

где в скобках указаны стандартные ошибки коэффициентов регрессии.

Статическая значимость коэффициента

F – тесту. ПосколькуF= 0,0124 2

se_y — стандартная ошибка для оценки y;

F — F-статистика, используемая для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет;

df — степени свободы, используемые для нахождения F-критических значений в статистической таблице (для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой функции ЛИНЕЙН);

ss_reg — регрессионая сумма квадратов;

ss_resid — остаточная сумма квадратов.

Характеристики выводятся на экран дисплея в виде приведенного ниже массива (таблицы):

Недостаток R2{\displaystyle R^{2}} и альтернативные показатели

Основная проблема применения (выборочного) R2{\displaystyle R^{2}} заключается в том, что его значение увеличивается (не уменьшается) от добавления в модель новых переменных, даже если эти переменные никакого отношения к объясняемой переменной не имеют! Поэтому сравнение моделей с разным количеством факторов с помощью коэффициента детерминации, вообще говоря, некорректно. Для этих целей можно использовать альтернативные показатели.

Скорректированный (adjusted) R2{\displaystyle R^{2}}

Для того, чтобы была возможность сравнивать модели с разным числом факторов так, чтобы число регрессоров (факторов) не влияло на статистику R2{\displaystyle R^{2}} обычно используется скорректированный коэффициент детерминации, в котором используются несмещённые оценки дисперсий:

Radj2=1−s2sy2=1−SSres(n−k)SStot(n−1)=1−(1−R2)(n−1)(n−k)⩽R2{\displaystyle R_{adj}^{2}=1-{\frac {s^{2}}{s_{y}^{2}}}=1-{\frac {SS_{res}/(n-k)}{SS_{tot}/(n-1)}}=1-(1-R^{2}){(n-1) \over (n-k)}\leqslant R^{2}}

который даёт штраф за дополнительно включённые факторы, где n — количество наблюдений, а k — количество параметров.

Данный показатель всегда меньше единицы, но теоретически может быть и меньше нуля (только при очень маленьком значении обычного коэффициента детерминации и большом количестве факторов). Поэтому теряется интерпретация показателя как «доли». Тем не менее, применение показателя в сравнении вполне обоснованно.

Для моделей с одинаковой зависимой переменной и одинаковым объёмом выборки сравнение моделей с помощью скорректированного коэффициента детерминации эквивалентно их сравнению с помощью остаточной дисперсии s2=SSres(n−k){\displaystyle s^{2}=SS_{res}/(n-k)} или стандартной ошибки модели s{\displaystyle s}. Разница только в том, что последние критерии чем меньше, тем лучше.

Информационные критерии

AIC — информационный критерий Акаике — применяется исключительно для сравнения моделей. Чем меньше значение, тем лучше. Часто используется для сравнения моделей временных рядов с разным количеством лагов. AIC=2kn+ln⁡SSresn{\displaystyle AIC={2k \over n}+\ln {SS_{res} \over n}}, где k— количество параметров модели.
BIC или SC — байесовский информационный критерий Шварца — используется и интерпретируется аналогично AIC. BIC=kln⁡nn+ln⁡SSresn{\displaystyle BIC={k\ln {n} \over n}+\ln {SS_{res} \over n}}. Даёт больший штраф за включение лишних лагов в модель, чем AIC.

R2{\displaystyle R^{2}}-обобщённый (extended)

В случае отсутствия в линейной множественной МНК регрессии константы свойства коэффициента детерминации могут нарушаться для конкретной реализации. Поэтому модели регрессии со свободным членом и без него нельзя сравнивать по критерию R2{\displaystyle R^{2}}. Эта проблема решается с помощью построения обобщённого коэффициента детерминации Rextended2{\displaystyle R_{extended}^{2}}, который совпадает с исходным для случая МНК регрессии со свободным членом, и для которого выполняются четыре свойства, перечисленные выше. Суть этого метода заключается в рассмотрении проекции единичного вектора на плоскость объясняющих переменных.

Для случая регрессии без свободного члена:
Rextended2=1−Y′∗(I−P(X))∗YY′∗(I−π(X))∗Y{\displaystyle R_{extended}^{2}=1-{Y’*(I-P(X))*Y \over Y’*(I-\pi (X))*Y}},
где X — матрица nxk значений факторов, P(X)=X∗(X′∗X)−1∗X′{\displaystyle P(X)=X*(X’*X)^{-1}*X’} — проектор на плоскость X, π(X)=P(X)∗in∗in′∗P(X)in′∗P(X)∗in{\displaystyle \pi (X)={P(X)*i_{n}*i_{n}’*P(X) \over i_{n}’*P(X)*i_{n}}}, где in{\displaystyle i_{n}} — единичный вектор nx1.

Rextended2{\displaystyle R_{extended}^{2}}с условием небольшой модификации, также подходит для сравнения между собой регрессий, построенных с помощью: МНК, обобщённого метода наименьших квадратов (ОМНК), условного метода наименьших квадратов (УМНК), обобщённо-условного метода наименьших квадратов (ОУМНК).

Анализ результатов регрессии для R-квадрата

В Excel данные полученные в ходе обработки данных рассматриваемого примера имеют вид:

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

В данном примере R-квадрат = 0,755 (75,5%), т. е. расчетные параметры модели объясняют зависимость между рассматриваемыми параметрами на 75,5 %. Чем выше значение коэффициента детерминации, тем выбранная модель считается более применимой для конкретной задачи. Считается, что она корректно описывает реальную ситуацию при значении R-квадрата выше 0,8. Если R-квадрата 2 (RI) представляет собой числовую характеристику доли общего разброса и показывает, разброс какой части экспериментальных данных, т.е. значений зависимой переменной соответствует уравнению линейной регрессии. В рассматриваемой задаче эта величина равна 84,8%, т. е. статистические данные с высокой степенью точности описываются полученным УР.

F-статистика, называемая также критерием Фишера, используется для оценки значимости линейной зависимости, опровергая или подтверждая гипотезу о ее существовании.

Значение t-статистики (критерий Стьюдента) помогает оценивать значимость коэффициента при неизвестной либо свободного члена линейной зависимости. Если значение t-критерия > t_кр, то гипотеза о незначимости свободного члена линейного уравнения отвергается.

В рассматриваемой задаче для свободного члена посредством инструментов «Эксель» было получено, что t=169,20903, а p=2,89Е-12, т. е. имеем нулевую вероятность того, что будет отвергнута верная гипотеза о незначимости свободного члена. Для коэффициента при неизвестной t=5,79405, а p=0,001158. Иными словами вероятность того, что будет отвергнута верная гипотеза о незначимости коэффициента при неизвестной, равна 0,12%.

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

Изучение результатов и выводы

«Собираем» из округленных данных, представленных выше на листе табличного процессора Excel, уравнение регрессии:

СП = 0,103*СОФ + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.

В более привычном математическом виде его можно записать, как:

y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844

Данные для АО «MMM» представлены в таблице:

Подставив их в уравнение регрессии, получают цифру в 64,72 млн американских долларов. Это значит, что акции АО «MMM» не стоит приобретать, так как их стоимость в 70 млн американских долларов достаточно завышена.

Как видим, использование табличного процессора «Эксель» и уравнения регрессии позволило принять обоснованное решение относительно целесообразности вполне конкретной сделки.

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

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