Работа с регрессионным анализом в Excel

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

Как провести регрессионный анализ данных в Excel

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

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

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

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

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

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

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

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

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

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

Читайте также:
Считаем среднее значение в Microsoft Excel
Удаляем пустые строки в Excel
Настройка автофильтра в программе MS Excel
Вставляем таблицу из Word в 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. Однако адекватно обработать и привести в читабельный вид полученные результаты сможет только специально подготовленный человек.