Toderelt.ru

ПК Журнал ТодерельТ
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

Линейный регрессионный анализ

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

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

В данном уравнении:

  • Y – переменная, влияние на которую нужно найти;
  • X – факторы, влияющие на переменную;
  • A – коэффициенты регрессии, определяющие значимости факторов;
  • N – общее количество факторов.

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

Таблица зависимости осадков от температуры в Эксель

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

  1. Щелкаем по кнопке “Анализ данных”.Применение функции Анализ данных в Excel
  2. В открывшемся окошке отмечаем пункт “Регрессия”, после чего щелкаем OK.Выбор регрессии как инструмента для анализа данных в Эксель
  3. Перед нами появится окно, в котором нужно настроить параметры регрессии:
    • в поле “Входной интервал_Y” пишем координаты диапазона ячеек, в которых находятся переменные, влияние на которые нам нужно выяснить. У нас это столбец “Количество осадков, мм”. Координаты диапазона можно указать как вручную, используя клавиши на клавиатуре, так и выделив его в самой таблице с помощью зажатой левой кнопки мыши.
    • в поле “Входной интервал_X” указываем координаты диапазона ячеек с данными, влияние которых нам нужно найти. В нашем случае – это столбец “Среднесуточная температура”.
    • Остальные параметры не являются обязательными и, чаще всего, остаются незаполненными. У нас есть возможность установить метки, значения уровня надежности в процентах, константу-ноль, график нормальной вероятности и т.д. Пожалуй, самым важным здесь является способ вывода результатов анализа. Доступны следующие варианты: на новом листе (по умолчанию), в новой книге или в указанном диапазоне на этом же листе. Мы оставим все как есть и жмем кнопку OK.Настройка параметров регрессии для анализа данных в Эксель

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

Подключения пакета анализа

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

  1. Откройте документ с табличными данными и нажмите по кнопке “Файл” в верхней левой части интерфейса программы.
  2. Перейдя в окошко настроек программы нажмите по кнопке “Параметры”, что расположена в левом меню.
  3. В открывшемся окне “Параметры” нажмите по пункту “Надстройки”. Он расположен в левой части окна.
  4. В самой нижней части окна найдите и воспользуйтесь строкой “Управление”. Там, из контекстного меню выберите пункт “Надстройки Excel”.
  5. Нажмите кнопку “Перейти” для получения списка надстроек.
  6. Появится окошко с доступными надстройками Excel. Там установите галочку у пункта “Пакет анализа”. Нажмите “Ок”, чтобы эта надстройка добавилась в рабочую область программы.

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

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

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

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

Настройка линейного регрессионного анализа

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

Общее уравнение, которое будет использовано для проведения анализа выглядит следующим образом: У = а0 + а1х1 +…+акхк:

  • Где Y — это переменная, влияние внешних факторов на которую мы рассматриваем. В рассматриваемом примере это количество покупателей.
  • Где X — это фактор или факторы, оказывающие влияние на данную переменную.
  • Где A — это различные коэффициенты регрессии.
  • Индекс K обозначает общее количество факторов регрессии.

Давайте рассмотрим процесс регрессионного анализа на конкретном примере:

  1. Откройте таблицу с заполненными данными. Перейдите во вклакду “Данные”. Там воспользуйтесь инструментом “Анализ данных”, который появится после выполнения предыдущей инструкции.
  2. Выберите наиболее подходящий для вашей задачи тип анализа. В нашем случае это “Регрессия”. Нажмите по ней и затем “Ок” для перехода к настройке анализа.
  3. В появившемся окне настроек обязательными для заполнения являются два поля: “Входной интервал Y” и “Входной интервал X”. Они заполняются следующим образом:
    • “Входной интервал Y”. Сюда укажите диапазон адресов ячеек, где указаны данные, влияние на которые нам нужно изучить. В рассматриваемой таблице это будет “Количество покупателей”. Адрес нужных ячеек можно как вписать вручную, так и выделить их в самой таблице.
    • “Входной интервал X”. Сюда укажите значение данных ячеек, которые должны оказывать влияние на Y. В нашем случае это ячейки со средней дневной температурой на улице.
  4. Остальные данные оставляйте заполненными по умолчанию. Если вам нужно что-то настроить дополнительно, то конечно, можете внести свои корректировки.
  5. Завершив заполнение окна с настройками регрессионного анализа нажмите “Ок” для получения результата.

Изучение полученного результата

Результаты анализа по умолчанию будут выведены на отдельный лист. К сожалению, неопытному пользователю может быть достаточно сложно их разобрать, так как вряд ли там будет написано что-то вроде “средняя температура оказывает такое-то влияние”. Вот перечень параметров, на которые вам требуется обратить внимание и их расшифровка:

  • R-квадрат. Основный показатель, на который требуется обратить внимание. В нем вы видите качество рассматриваемой модели, то есть качество всего анализа. Значение выше 0.7 является приемлемым, следовательно, анализу можно доверять.
  • Пересечение строки “Y-пересечение” и столбца “Коэффициенты”. В этом блоке указывается значение Y, которое предположительно будет без влияния посторонних факторов. В нашем случае это количество покупателей.
  • Пересечение граф “Переменная X1” и “Коэффициенты” указывает на уровень зависимости Y от X. В рассматриваемом примере это зависимость количества посетителей от средней дневной температуры. Значение выше 1 считается высоким показателем.

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

Выводы по результатам анализа

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

Важным показателем является R-квадрат, в котором отображается качество модели. Мы можем видеть, что у нас он составляет 0,705, если перевести в проценты, то получим 70,5%.

Это нормальный уровень качества, а вот зависимость менее 0,5 – это плохо.

Другим важным показателем является место в таблице обведенное красным со значением 58,04. Это количество покупателей, если все остальные факторы равны 0.

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

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

Составление линейных прогнозов: функция ТЕНДЕНЦИЯ

Использование функции рабочего листа ТЕНДЕНЦИЯ — это самый простой спо­соб регрессионного анализа. Предположим, результаты ваших наблю­дений внесены в ячейки А2:А11, а дни месяца расположены в ячейках В2:В11, как на рисунке 1.

Выделите ячейки С2:С11 и введите следующую формулу, используя формулу массива:
= ТЕНДЕНЦИЯ (А2:А11;В2:В11).
Для этого необходимо:

1) Запустить Мастер функций.

2) В появившемся диалоговом окне выбрать функции СтатистическиеТенденция.

3) Ведите нужные диапазоны ячеек.

4) Когда рассчиталось одно значение из выделенного диапазона необходимо щёлкнуть мышью на формульную строку (после знака равенства).

5) Для ввода формулы массива нажмите комбинацию клавиш

  1. Каждый результат в ячейках С2:С11 получается на основе одной и той же формулы массива, внутри которой «спрятано» более сложное выражение – линейное уравнение регрессии. В данном случае формула имеет следующий вид:


Рисунок 1 — С помощью функции ТЕНДЕНЦИЯ можно прогнозировать базовую линию (линейный тренд) результатов наблюдений, а также значения показателя, выходящие за пределы конца базовой линии
Ячейка С2: = 9,13 + 0,61*1

Ячейка С3: = 9,13 + 0,61*2

  1. Поскольку все значения прогноза составляются на основе одних и тех же по­казателей отрезка, отсекаемого на оси ординат, и углового коэффициента, прогноз не отражает происходящих изменений во временном ряду.
  1. В данном примере функция ТЕНДЕНЦИЯ вычисляет прогноз, основанный на связи между фактическими результатами наблюдений и числами 1-10, ко­торые могут отражать либо первых десять дней месяца, либо первых десять месяцев года. Excel выражает первый аргумент как аргумент изв_знач_у (известные значения у) функции ТЕНДЕНЦИЯ, а второй — как аргумент функции изв_знач_х (известные значения х).

Введите в ячейку В12 число 11, а в ячейку С12 — следующее:

= ТЕНДЕНЦИЯ (А2:А11;В2:В11;В12)
Первый аргумент — А2:А11 — определяет данные наблюдений базовой линии (изв_знач_у); второй аргумент — В2:В11 — определяет временные моменты, в кото­рые эти данные были получены (изв_знач_х). Значение 11 в ячейке В12 является нов_знач_х (новым значением х) и определяет время, которое связывается с перспектив­ной оценкой.

Формула фактически говорит о следующем: «Если известно, каким образом у-значения в диапазоне А2:А11 соотносятся с х-значениями в диапазоне B2:B11, то какой результат у-значения мы получим, зная новое х-значение временного момен­та, равное 11?». Полученное значение 15,87 является прогнозом на основе фактических данных на пока еще не наступивший одиннадцатый временной отсчет.

Введя в ячейку В12 большее значение, вы сможете прогнозировать данные более позднего временного момента, чем непосредственно следующий за текущим. Предположим, что результаты наблюдений в ячейках А2:А11 отражают ежемесячные объёмы продаж с января по октябрь 2002 года. В этом случае число 24, введённое в ячейку В12, будет определять 24 месяц, т.е. декабрь 2003 года. Выполняя вычисления с помощью функции ТЕНДЕНЦИЯ, получаем результат 23,82, который и будет отражать прогнозируемый объём продаж на декабрь 2003 года, полученный на основе фактических результатов наблюдений за период с января по октябрь 2002 года.

Кроме того, существует возможность одновременного прогнозирования данных для нескольких новых временных моментов. Например, введите числа 11-24 в ячейки В12:В25, а затем выделите ячейки С12:С25 и введите с помощью формулы массива следующее:

= ТЕНДЕНЦИЯ (А2:А11;В2:В11;В12:В25)
Excel вернет в ячейки С12:С25 прогноз на временные моменты с 11 по 24. Дан­ный прогноз будет базироваться на связи между данными наблюдений базовой линии диапазона А2:А11 и временными моментами базовой линии с 1 по 10, указанными в ячейках В2:В11.

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

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

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

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

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

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

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

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

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

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

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

    В меню необходимо выбрать «Сервис» — «Надстройки» — «Пакет анализа». Затем снова заходим в «Сервис» и

Окно параметров регрессии Excel

Окно параметров регрессии Excel

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

При выполнении регрессионного анализа Microsoft Excel определяет для каждой точки квадрат разности между прогнозируемым значением Y и фактическим значением Y.

Как рассчитать значения полинома в Excel?

Есть 3 способа расчета значений полинома в Excel:

  • 1-й способ с помощью графика;
  • 2-й способ с помощью функции Excel =ЛИНЕЙН;
  • 3-й способ с помощью Forecast4AC PRO;

1-й способ расчета полинома — с помощью графика

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

график полинома

На график добавляем полином 6-й степени.

добавляем линию тренда в Excel

polinom 6 stepeni

Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»

polinom na grafik

После этого уравнение выводится на график y = 3,7066x 6 — 234,94x 5 + 4973,6x 4 — 35930x 3 — 7576,8x 2 + 645515x + 5E+06 . Для того чтобы последний коэффициент сделать читаемым, мы зажимаем левую кнопку мыши и выделяем уравнение полинома

выделяем уравнение тренда

Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»

формат подписи полинома

В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».

формат подписи полинома

Получаем уравнение полинома в читаемом формате:

y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35

уравнение полинома

Из этого уравнения берем коэффициенты a, b, c, d, g, m, v, и вводим в соответствующие ячейки Excel

коэффициенты полинома

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение вместо X.

номер временного ряда для полинома

Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

вводим формулу полинома в ячейку

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

Теперь протягиваем формулу до конца временного ряда и получаем рассчитанные значения полиномиального тренда для каждого периода.

2-й способ расчета полинома в Excel — функция ЛИНЕЙН()

Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel =ЛИНЕЙН()

Для расчета коэффициентов в формулу =ЛИНЕЙН(известные значения y, известные значения x, константа, статистика) вводим:

  • «известные значения y» (объёмы продаж за периоды),
  • «известные значения x» (порядковый номер временного ряда),
  • в константу ставим «1»,
  • в статистику «0»

Получаем следующего вида формулу:

Линейн формула Excel

Теперь, чтобы формула Линейн() рассчитала коэффициенты полинома, нам в неё надо дописать степень полинома, коэффициенты которого мы хотим рассчитать.

Для этого в часть формулы с «известными значениями x» вписываем степень полинома:

  • ^ <1:2:3:4:5:6>— для расчета коэффициентов полинома 6-й степени
  • ^ <1:2:3:4:5>— для расчета коэффициентов полинома 5-й степени
  • ^ <1_2>— для расчета коэффициентов полинома 2-й степени

вводим степень полинома

Получаем формулу следующего вида:

Вводим формулу в ячейку, получаем 3,71 —- значение (a) для полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v

Для того, чтобы Excel рассчитал все 7 коэффициентов полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v, необходимо:

1. Установить курсор в ячейку с формулой и выделить 7 соседних ячеек справа, как на рисунке:

ustanovit kursor

2. Нажать на клавишу F2

uravnenie polinoma 6stepeni 2sposob

3. Затем одновременно — клавиши CTRL + SHIFT + ВВОД (т.е. ввести формулу массива, как это сделать читайте подробно в статье «Как ввести формулу массива»)

uravnenie polinoma 6stepeni 2sposob

Получаем 7 коэффициентов полиномиального тренда 6-й степени.

Рассчитаем значения полиномиального тренда с помощью полученных коэффициентов. Подставляем в уравнение y=3,7* x ^ 6 -234,9* x ^ 5 +4973,5* x ^ 4 -35929,9 * x^3 -7576,7 * x^2 +645514,7* x +4693169,3 номера периодов X, для которых хотим рассчитать значения полинома.

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение полинома вместо X.

номер временного ряда для полинома

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

вводим формулу полинома в ячейку

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

Теперь протягиваем формулу до конца временного ряда и получаем рассчитанные значения полиномиального тренда для каждого периода.

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

3-й способ расчета значений полиномиальных трендов — Forecast4AC PRO

Устанавливаем курсор в начало временного ряда

уравнение полинома

Заходим в настройки Forecast4AC PRO, выбираем «Прогноз с ростом и сезонностью», «Полином 6-й степени», нажимаем кнопку «Рассчитать».

функция полинома

Заходим в лист с пошаговым расчетом «ForPol6», находим строку «Сложившийся тренд»:

копируем полином

Копируем значения в наш лист.

Получаем значения полинома 6-й степени, рассчитанные 3 способами с помощью:

  1. Коэффициентов полиномиального тренда выведенных на график;
  2. Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
  3. и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

голоса
Рейтинг статьи
Читать еще:  Как трансформировать изображение в Adobe Photoshop
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector