Обычно я делаю любой регрессионный анализ на «питоне» – там и данные подготовить проще, и доступны сложные модели. Но простому пользователю (а особенно в учебных целях) нужно что-то попроще. В этой статье я покажу как делать базовый регрессионный анализ в Excel. Все будет просто, понятно, по шагам и с картинками. Поехали!
Эта статья прежде всего про инструмент. Читатель сам должен понимать свои задачи – никакой инструмент за вас это не придумает (ну кроме каких-нибудь разновидностей DQN-сетей при правильном применении
). Поэтому ориентируюсь на новичков в Excel, в мат. статистику не лезу.
). Поэтому ориентируюсь на новичков в Excel, в мат. статистику не лезу.Зачем это нужно?
В сложном понимании регрессионный анализ нужен в статистическом исследовании для определения зависимости одной величины от другой в разных разрезах. В практическом смысле (а простого человека интересует именно это) регрессионный анализ помогает сделать предсказание неизвестной величины на основе какого-то предыдущего «опыта». Хотим предсказать курс биткоина на завтра – это примерно об этом, правда, лучше не пытаться.
Перед регрессией иногда полезно сначала посмотреть обычную связь между столбцами – для этого есть коэффициент корреляции в Excel.
Шаг 1 – Подготовка данных
Для исследования нам нужно подготовить исходные данные – нам нужно создать два столбца (две величины), на основе которых мы и будем делать регрессионный анализ. Предлагаю простые и понятные данные, где все зависимости вы видите «на глаз». Сложное вы найдете и у себя.
Для учебных целей на представленных данных вторая величина – это всего лишь утроенная первая величина с небольшим шумом, который я добавил вручную.
ВАЖНО! В Y ставим то, что хотим предсказать, а в X – то, по чему предсказываем. Перепутаете – получите другое уравнение.
Шаг 2 – Включаем пакет анализа
По умолчанию пакет анализа, в котором и находится инструмент регрессии, в Excel не включен, поэтому нужно его включить.
Если текст ниже будет совсем непонятным, у нас есть отдельная очень подробная статья по добавлению пакета анализа в Excel.
Файл – Параметры – Надстройки – Надстройки Excel – Кнопка Перейти – Ставим галочку возле «Пакет анализа» – ОК
В результате выполнения этой цепочки действий у вас на вкладке «Данные» в Excel должна появиться кнопка «Анализ данных». В браузерном Excel ее может не быть – откройте файл в настольной программе.
Шаг 3 – Делаем регрессионный анализ
Переходим к самым важным действиям:
- Нажимаем по той самой кнопке «Анализ данных».
- В списке находим «Регрессия» и нажимаем ОК.
- Задаем два входных интервала. Для Y я задам второй столбец, для X – первый. Если вместе с цифрами выделили заголовки столбцов, поставьте галочку «Метки». Все остальное можно оставить как есть, тут уже дело под свою конкретную задачу. В том же парном трейдинге для двух коинтегрированных пар очень важно получить график остатков – и тут сразу доступна эта функция. Но в наших учебных целях это не нужно.
Шаг 4 – Анализ результата
Из статистики вы точно должны знать, раз оказались здесь, что существует много вариантов построения регрессии (степенная, логарифмическая, экспоненциальная и т.д.). Так вот этот способ строит самый простой ее тип – линейную регрессию, т.е. пытается на основе двух величин построить линейную функцию (или просто провести прямую линию через заданные точки на плоскости из двух величин) вида:
Y = aX + b
Т.е. зная коэффициенты a и b в этом уравнении, на основе первой величины X можно предсказать величину Y. Задача линейной регрессии – найти эти самые коэффициенты a и b. А теперь посмотрим на результаты нашего анализа, которые появились на отдельном листе (все самое важное выделил):
- R-квадрат – качество подгонки модели. Чем ближе к 1, тем плотнее точки лежат около линии, но это не доказывает причинно-следственную связь.
- Y-пересечение – коэффициент b в уравнении.
- Переменная X 1 – коэффициент a в уравнении.
Т.е. мы получили отличную зависимость (R-квадрат 0,98, ведь правда же, т.к. она была сразу задана вручную хоть и с шумами), а итоговое уравнение зависимости будет таким:
Y = 3,1X – 0,8
Немного округлил первый коэффициент, но суть улавливается легко. И кто помнит про линейную функцию, первый коэффициент как раз и показывает основную зависимость первой величины от второй – т.е. здесь разница между ними примерно в 3 раза, как и было задано в начале.
Коэффициенты можно получать и формулой. Начать проще с темы про формулы в Excel, а потом копать ЛИНЕЙН.
Бонус (быстрый способ) – Строим график
Как и писал выше, подобным в Excel лично я не занимаюсь – руки остаются связанными по всем направлениям. Но построением быстрых графиков в Excel все-таки периодически грешу, тем более здесь есть интересный функционал построения линий регрессий, причем не только линейных.
- На основе наших двух величин строю простой график, а лучше точечную диаграмму. Данные использую те же самые. Если с этим пока беда, вот отдельная инструкция – как сделать график в Excel.
А для обычных школьных функций есть отдельный разбор – график функции.
- Щелкаем ПРАВОЙ кнопкой мыши по самому графику (вот этой оранжевой линии) и выбираем «Добавить линию тренда».
- В правой части Excel появится список параметров под нашу регрессионную линию. Указываем тип линии тренда – в нашем случае оставляю линейную, но обращаем внимание, что здесь уже доступен расширенный перечень типов. Устанавливаю прогноз «вперед» еще на 10 периодов, а также ставлю галочку отображения уравнения (чтобы сравнить результат с предыдущим пунктом).
- Линия строится автоматически, а вместе с ней и уравнение. Обращаем внимание, что уравнение предсказанной линии на графике совпадает с тем, что мы вывели ранее. Если цифры в уравнении обрезались, увеличьте число знаков в формате подписи.
Надеюсь, изложил все доступно и понятно. Но еще раз – если нужны сложные расчеты, лучше переходить на профильные языки и среды.
Полезная статья, спасибо тебе
Хорошая штука эти анализы – многое могут показать.
На графике куда удобнее смотреть, чем в цифры эти.