Регрессионный анализ в Excel: уравнение, R² и три пути

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

Excel строит такое уравнение тремя разными способами. Самый быстрый занимает четыре клика мышкой и не требует ни одной формулы.

🧮Онлайн-калькулятор линейной регрессииВставьте два столбца — уравнение, R² и вывод посчитаются сами→

Что даёт регрессия и чем отличается от корреляции

Корреляция отвечает одним числом: связь есть, она такой-то силы и такого-то направления. И на этом останавливается.

Регрессия проводит через облако точек прямую и записывает её уравнением ŷ = a + b·x. Теперь по любому значению X можно назвать ожидаемое значение Y — и сказать, на сколько единиц меняется Y при росте X на единицу.

Чем они отличаются по сути — в статье «Корреляция или регрессия».

Регрессия имеет смысл не всегда — проверьте себя по списку.

• Понятно, что от чего зависит. Y — то, что предсказываем (результат), X — то, по чему предсказываем (фактор). Поменяете местами — получите другое уравнение.
• Оба показателя числовые. Секунды, баллы, повторения, проценты. Для «пол» и «группа» нужна другая модель.
• Связь похожа на прямую. Постройте диаграмму рассеяния: точки должны тянуться вдоль линии, а не по дуге.
• Нет грубых выбросов. Одна аномальная точка разворачивает прямую сильнее, чем все остальные вместе.
• Наблюдений хотя бы 20. На десяти парах уравнение получится, но значимости у него не будет.

Дальше считаем на реальных данных из диплома по педагогике: у 20 первокурсников замерили ситуативную тревожность и балл за экзамен по 100-балльной шкале. Ту же пару показателей мы разбирали в статье про корреляцию в Excel, там получилось r = −0,62.

Тревожность (это X) лежит в столбце B, балл за экзамен (это Y) — в столбце C, строки со 2-й по 21-ю.

Формулы в Excel: НАКЛОН, ОТРЕЗОК, КВПИРСОН

Три функции дают всё, что нужно для уравнения и его качества:

=НАКЛОН(C2:C21;B2:B21)
=ОТРЕЗОК(C2:C21;B2:B21)
=КВПИРСОН(C2:C21;B2:B21)

НАКЛОН — это b, коэффициент регрессии. ОТРЕЗОК — это a, свободный член. КВПИРСОН — коэффициент детерминации R². В английском Excel они называются SLOPE, INTERCEPT и RSQ.

Вот что получается на наших двадцати парах.

Уравнение регрессии по данным о тревожности и успеваемости (n = 20)

Уравнение читается так: ŷ = 151,52 − 1,52 · x. Рост тревожности на один балл сопровождается снижением экзаменационного балла в среднем на 1,52 балла.

Функция ПРЕДСКАЗ (FORECAST) подставляет значение в это же уравнение: при тревожности 50 баллов ожидаемый результат — около 76 баллов.

Быстрый путь: линия тренда на диаграмме рассеяния

Формулы здесь не нужны: уравнение и R² Excel напишет прямо на графике, а сам график уйдёт в третью главу рисунком.

1. Выделите оба столбца с данными: B2:C21. Слева должен стоять X, справа Y — тут порядок обратный тому, что в функциях.
2. Вставка → Диаграммы → Точечная (значок с разбросанными точками), первый вариант — без линий.
3. Щёлкните правой кнопкой по любой точке на диаграмме → «Добавить линию тренда».
4. В открывшейся панели тип оставьте «Линейная».
5. Внизу панели поставьте две галочки: «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации (R^2)».
6. На графике появятся подписи y = -1,5152x + 151,52 и R² = 0,3838.

Excel пишет уравнение в школьном порядке — сначала слагаемое с иксом. В диплом его переносят в привычном виде: ŷ = 151,52 − 1,52 · x.

Чего линия тренда не даёт: значимости. Ни F, ни p-значения на графике нет — за ними нужен третий способ.

Полный отчёт через «Пакет анализа»

Надстройка «Анализ данных» считает регрессию целиком: коэффициенты, R², значимость модели и значимость каждого коэффициента. Если кнопки «Анализ данных» на вкладке «Данные» нет, её нужно включить — пять шагов описаны в статье про пакет анализа.

1. Данные → Анализ данных → Регрессия → ОК.
2. Входной интервал Y — столбец с тем, что предсказываем: C1:C21.
3. Входной интервал X — столбец с фактором: B1:B21.
4. Метки — галочка, раз захватили строку с заголовками.
5. Параметры вывода — «Новый рабочий лист».
6. При желании отметьте «График подбора» — Excel нарисует точки и прогноз.

Excel выдаст три блока таблиц. В диплом из всего отчёта уходят пять чисел.

Что означают строки отчёта «Регрессия» на наших данных

«Значимость F» меньше 0,05 — модель работает лучше, чем прогноз по среднему. В парной регрессии она всегда совпадает с P-значением коэффициента: предиктор в модели один.

Как читать R²: что считать хорошим результатом

R² показывает, какую долю разброса Y объяснила модель. Наши 0,38 означают: тревожность объясняет 38% различий в экзаменационных баллах, остальные 62% зависят от того, чего мы не измеряли.

Границы на шкале — ориентир для «человеческих» наук, где на результат влияют десятки неучтённых факторов. В физике R² = 0,38 назвали бы провалом, в дипломе по педагогике это рабочая модель, если она статистически значима.

Обратная крайность тоже подозрительна: R² выше 0,95 обычно означает, что вы предсказываете показатель по его же составной части — например, общий балл теста по одной из его шкал. Спорные случаи разобраны в статье про коэффициент детерминации R².

Всё считается в готовом файле

Если считать нужно не одно уравнение, а всю статистику для третьей главы — есть готовая книга Excel. Вы вставляете свои данные в жёлтые ячейки, и всё считается само.

Регрессия лежит на листе «18. Линейная регрессия»: столбцы X и Y уже подписаны, поэтому перепутать порядок аргументов там негде. Вставили два столбца — получили уравнение, R², значимость и формулировку для работы.

Три способа посчитать — что выбрать

← листайте вправо, чтобы сравнить →

Коэффициенты во всех трёх колонках получатся одинаковые. Разница — в том, где можно ошибиться и сколько работы остаётся после расчёта.

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

Формулировка держится на трёх вещах: само уравнение, R² и значимость модели.

Для оценки влияния ситуативной тревожности на академическую успеваемость построена модель парной линейной регрессии: ŷ = 151,52 − 1,52 · x. Модель статистически значима (F = 11,21; p = 0,004) и объясняет 38% дисперсии экзаменационного балла (R² = 0,38). Повышение ситуативной тревожности на 1 балл сопровождается снижением экзаменационного балла в среднем на 1,52 балла.

Если модель значимости не набрала, это тоже результат:

Уравнение регрессии статистически незначимо (F = 1,84; p = 0,19), коэффициент детерминации R² = 0,09. Прогнозировать успеваемость по данному показателю нельзя.

Частые ошибки

• Путают порядок аргументов в НАКЛОН и ОТРЕЗОК. Excel посчитает обратную зависимость и не предупредит — в работу уедет чужое уравнение.Первым аргументом всегда идёт Y, тот показатель, который предсказываете: =НАКЛОН(Y;X).
• На диаграмме ставят X справа, а Y слева. В точечной диаграмме первый выделенный столбец Excel считает осью X, и линия тренда описывает не ту зависимость.Расположите столбцы в порядке «X, затем Y» — обратном тому, что нужен функциям.
• Пишут в выводах «влияет», получив только R². Регрессия описывает совместное изменение, а не доказывает причину: оба показателя могут зависеть от третьего.Пишите «сопровождается снижением», «связано с», а причинность обосновывайте теорией из первой главы.
• Приводят уравнение без значимости. Прямая строится через любое облако точек, даже случайное — само по себе уравнение ничего не подтверждает.Добавьте F и p из отчёта «Регрессия», а лучше сразу и R².
• Прогнозируют далеко за пределами данных. Модель построена на тревожности 40–55 баллов, а прогноз делают для 20 или 80.Подставляйте в уравнение только значения из диапазона исходных данных.

Частые вопросы

Какая функция считает уравнение регрессии в Excel

Две: =НАКЛОН(Y;X) даёт коэффициент b, =ОТРЕЗОК(Y;X) — свободный член a. Вместе они складываются в уравнение ŷ = a + b·x. В английской версии это SLOPE и INTERCEPT.

Как посчитать коэффициент детерминации в Excel

Функцией =КВПИРСОН(Y;X) (RSQ). Тот же результат даёт линия тренда с галочкой «величина достоверности аппроксимации» и строка «R-квадрат» в отчёте «Регрессия». Все три способа выдают одно число.

Что делать, если нужно посчитать регрессию по нескольким факторам

В поле «Входной интервал X» отчёта «Регрессия» укажите сразу несколько соседних столбцов. Функции НАКЛОН и ОТРЕЗОК для этого не годятся — они работают с одним предиктором. Разбор — в статье про множественную регрессию.

Почему НАКЛОН выдаёт ошибку

#Н/Д — диапазоны Y и X разной длины. #ДЕЛ/0! — в столбце X все значения одинаковые, наклон прямой посчитать невозможно. Ещё одна частая причина — числа вставлены из Word как текст: они выравниваются по левому краю и в расчёт не попадают.

Можно ли строить регрессию по баллам теста

Формально линейная регрессия рассчитана на числовые измерения, а баллы опросников — порядковая шкала. На практике в дипломах так делают, но связь лучше дополнительно подтвердить корреляцией Спирмена и честно назвать это ограничением исследования.

Короткий алгоритм

1. Разложите данные в два столбца: X — фактор, Y — то, что предсказываете. Пара значений на строку, без пустых ячеек.
2. Постройте точечную диаграмму (выделяя сначала X, потом Y) и посмотрите на облако: есть ли линейная тенденция и нет ли выбросов.
3. Добавьте линию тренда с галочками «уравнение» и «R²» — это самый быстрый результат.
4. Для точных коэффициентов введите =НАКЛОН(Y;X) и =ОТРЕЗОК(Y;X), помня, что Y идёт первым.
5. Значимость возьмите из «Данные → Анализ данных → Регрессия»: строки «Значимость F» и «P-значение».
6. Запишите вывод: уравнение, R² в процентах, F и p.

Или пропустите все шаги: скачайте файл выше либо вставьте данные в калькулятор линейной регрессии.

Что ещё почитать

• Линейная регрессия: полное руководство — метод наименьших квадратов, условия и проверка значимости по существу.
• Корреляция или регрессия: что выбрать — какой метод отвечает на ваш вопрос.
• Коэффициент детерминации R² — как читать значение и что делать, если оно близко к нулю.
• Корреляция в Excel: КОРРЕЛ и Спирмен — те же данные, но про силу связи.
• Пакет анализа данных в Excel — как включить надстройку и что ещё она считает.

Если не уверены, какой метод нужен именно в вашей работе, загляните в базу методов или напишите нам — поможем с расчётами.