Как сделать регрессию в Excel: полное руководство

Чтобы выполнить регрессию в Excel, включите Analysis ToolPak, затем перейдите в Данные > Анализ данных и выберите Регрессия. Выберите диапазон Y и диапазон X, укажите место для вывода и нажмите OK. Excel вернет коэффициенты, R-квадрат и другие статистические данные в новой таблице.
В этом руководстве рассматриваются три способа проведения регрессии в Excel: с помощью Analysis ToolPak, функции ЛИНЕЙН (LINEST) и линии тренда на диаграмме. Также приводится полный пример, объясняется, как интерпретировать результаты, и показывается более быстрый способ проверки взаимосвязей в больших файлах. Подробности взяты со страниц поддержки Microsoft, изученных 12 ноября 2026 года.
Что показывает регрессия
Регрессия измеряет, как изменяется одно значение при изменении другого. Значение, которое вы хотите объяснить, является зависимой переменной, или Y. Значения, которые, по вашему мнению, влияют на нее, являются независимыми переменными, или X.
Microsoft описывает инструмент Регрессия следующим образом. Он «выполняет линейный регрессионный анализ методом "наименьших квадратов" для построения прямой линии через набор наблюдений».
Эта линия имеет простое уравнение. Microsoft записывает его как y = mx + b, где m — это наклон, а b — точка пересечения. При наличии более чем одного X уравнение принимает вид y = m1x1 + m2x2 + ... + b.
С точки зрения бизнеса регрессия отвечает на такие вопросы:
- Насколько вырастут продажи за каждый дополнительный месяц роста?
- Как расходы на рекламу связаны с количеством регистраций?
- Какая часть изменения затрат объясняется объемом заказов?
Собственный пример Microsoft касается спортсменов. В нем говорится, что вы можете проанализировать, «как на показатели спортсмена влияют такие факторы, как возраст, рост и вес».
Регрессия показывает ассоциацию, а не доказательство причинно-следственной связи. Сильная связь между двумя значениями все равно может быть вызвана третьим фактором, который вы не измеряли.
Подготовка данных в Excel
Для регрессии нужны чистые, сопоставленные пары чисел. Каждая строка должна содержать одно наблюдение, при этом Y находится в одном столбце, а каждый X — в своем собственном столбце.
Вот данные из примера простой линейной регрессии Microsoft. В нем перечислены продажи за шесть месяцев в долларах.
| Месяц | Продажи |
|---|---|
| 1 | 3,100 |
| 2 | 4,500 |
| 3 | 4,400 |
| 4 | 5,400 |
| 5 | 7,500 |
| 6 | 8,100 |
Поместите Месяц в столбец A, а Продажи — в столбец B, с заголовками в строке 1. В примерах ниже используется именно такая структура. Данные располагаются в строках со 2 по 7, поэтому в формулах ниже используются эти диапазоны.
Несколько правил помогут сохранить достоверность результатов:
- Никаких пустых ячеек. Каждая строка должна содержать значения и Y, и X.
- Только числа. Значения, сохраненные как текст, вызовут ошибки.
- Достаточное количество строк. Шесть строк подходят для демонстрации, но для реального анализа требуется гораздо больше.
- Одна строка на одно наблюдение. Не смешивайте ежемесячные и еженедельные строки в одном диапазоне.
Включение Analysis ToolPak
Инструмент Регрессия находится в Analysis ToolPak, надстройке, которая поставляется вместе с Excel. Возможно, вам сначала придется ее включить.
Инструкции Microsoft для Excel в Windows:
- Выберите Файл, затем Параметры и выберите Надстройки.
- В поле Управление выберите Надстройки Excel и нажмите Перейти.
- В окне Надстройки установите флажок Analysis ToolPak и нажмите OK.
На Mac Microsoft рекомендует перейти в Инструменты > Надстройки Excel в меню файла. Если ToolPak нет в списке, выберите Обзор, чтобы найти его.
После включения вы увидите Анализ данных на вкладке Данные. Microsoft отмечает, что если команда отсутствует, «вам необходимо загрузить и активировать программу надстройки Analysis ToolPak».
Способ 1: Запуск регрессии с помощью Analysis ToolPak
Это наиболее полный метод. Он позволяет получить полную сводную таблицу за один шаг.
- Перейдите на вкладку Данные и выберите Анализ данных.
- Выберите Регрессия из списка и нажмите OK.
- В поле Входной интервал Y выберите столбец Продажи, включая заголовок.
- В поле Входной интервал X выберите столбец Месяц, включая заголовок.
- Установите флажок Метки, так как оба диапазона включают заголовки.
- Выберите Выходной интервал или Новый лист.
- Нажмите OK.
Excel создаст сводку из трех блоков: регрессионная статистика, таблица дисперсионного анализа (ANOVA) и таблица коэффициентов. В следующем разделе объясняется, как их читать.
Для множественной регрессии на шаге 4 выберите несколько смежных столбцов X. Microsoft отмечает, что инструмент может анализировать, «как на одну зависимую переменную влияют значения одной или нескольких независимых переменных».
В основе инструмента лежит функция листа. Microsoft заявляет: «Инструмент Регрессия использует функцию листа LINEST».
Способ 2: Использование функции LINEST (ЛИНЕЙН)
LINEST выдает те же числа в виде формулы. Это подходит тем, кто хочет, чтобы результаты обновлялись при изменении данных.
Синтаксис Microsoft: LINEST(известные_значения_y, [известные_значения_x], [конст], [статистика]). Известные_значения_y — это диапазон Продажи, а известные_значения_x — диапазон Месяц.
Для данных о продажах введите в пустую ячейку:
=LINEST(B2:B7, A2:A7)
Функция возвращает два значения: наклон, затем точку пересечения. Microsoft сообщает, что массив, который возвращает LINEST, имеет вид «{mn,mn-1,...,m1,b}», поэтому точка пересечения всегда идет последней.
Чтобы получить больше статистических данных, установите последний аргумент в значение TRUE:
=LINEST(B2:B7, A2:A7, TRUE, TRUE)
Если параметр статистики установлен в значение TRUE, LINEST возвращает стандартные ошибки, R-квадрат, F-статистику, степени свободы и суммы квадратов. Microsoft отмечает, что функция «должна вводиться как формула массива», так как она возвращает несколько значений одновременно.
На странице Microsoft также показан прогноз в одной ячейке. Формула ниже оценивает продажи за 9-й месяц на основе данных с 1-го по 6-й месяцы:
=SUM(LINEST(B2:B7, A2:A7)*{9,1})
Результат Microsoft для этого примера — 11,000.
Способ 3: Добавление линии тренда на точечную диаграмму
Линия тренда показывает линию регрессии на диаграмме. Это самый быстрый способ увидеть, подходит ли прямая линия для ваших данных.
- Выберите оба столбца данных.
- Вставьте точечную диаграмму.
- Выберите диаграмму, затем добавьте Линию тренда из меню элементов диаграммы.
- Откройте панель Формат линии тренда и оставьте параметр Линейная.
- Установите флажки, чтобы показать уравнение и значение R-квадрат на диаграмме.
Затем на диаграмме рядом с линией отобразится уравнение, например y = 1000x + 2000. Это совпадает с результатом LINEST для данных о продажах.
Линии тренда также могут прогнозировать наперед. Microsoft сообщает, что вы можете «установить значение в полях Вперед и Назад, чтобы спрогнозировать ваши данные на будущее».
Линия тренда хороша для быстрого ознакомления или слайда. Для получения стандартных ошибок, p-значений или при наличии нескольких переменных X используйте Способ 1 или Способ 2.
Какой метод использовать
Каждый метод подходит для разных задач. Выбирайте в зависимости от того, что вам нужно показать и кто будет это читать.
| Метод | Лучше всего для | Дает p-значения | Обновляется с данными |
|---|---|---|---|
| Analysis ToolPak | Полный отчет по одной модели | Да | Нет, нужно перезапускать инструмент |
| LINEST | Модели внутри рабочей таблицы | Нет, но дает статистику для их расчета | Да |
| Линия тренда | Быстрая визуальная проверка или слайд | Нет | Да |
Используйте ToolPak, когда вам нужно защитить результат на встрече. Его сводная таблица содержит все числа, о которых может спросить рецензент, включая p-значения для каждого коэффициента.
Используйте LINEST, когда модель питает другие формулы, например лист прогнозирования. Поскольку это формула, наклон и точка пересечения меняются сразу после добавления новых строк.
Используйте линию тренда, когда аудитории нужно увидеть закономерность. Диаграмму с линией и ее уравнением легче читать, чем таблицу. Многие аналитики используют все три способа: линию тренда для исследования, LINEST для построения и ToolPak для отчетности.
Пример регрессии
Вот что возвращают три метода для данных о продажах за шесть месяцев.
| Статистика | Значение | Что это значит |
|---|---|---|
| Наклон (m) | 1,000 | Продажи растут примерно на 1,000 в месяц |
| Пересечение (b) | 2,000 | Значение линии в месяце 0 |
| R-квадрат | 0.93 | Около 93% изменений в продажах следуют месячному тренду |
| Оценка для 9-го месяца | 11,000 | 1,000 × 9 + 2,000 |
Вы можете проверить наклон вручную. Средний месяц — 3.5, а среднее значение продаж — 5,500. Наклон — это сумма произведений отклонений, деленная на сумму квадратов отклонений месяца. Получается 17,500, деленное на 17.5, что равно 1,000.
Затем следует точка пересечения. Это среднее значение продаж минус наклон, умноженный на средний месяц: 5,500 минус 3,500, что равно 2,000.
R-квадрат говорит о том, насколько хорошо линия соответствует данным. Microsoft описывает его как значение, которое «варьируется от 0 до 1». При 1 линия проходит через каждую точку. При 0 Microsoft заявляет, что «уравнение регрессии не помогает в прогнозировании значения y».
Как читать результаты регрессии
Результаты Analysis ToolPak могут выглядеть перегруженными. Большинство решений основывается на нескольких числах.
- Множественный R. Корреляция между фактическими и прогнозируемыми значениями. Чем ближе к 1, тем точнее соответствие.
- R-квадрат. Доля вариации Y, объясняемая X. Здесь она составляет около 0.93.
- Нормированный R-квадрат. R-квадрат, скорректированный на количество переменных X. Используйте его при сравнении моделей с разным количеством входных данных.
- Стандартная ошибка. Типичное расстояние между фактическими точками и линией.
- Коэффициенты. Точка пересечения и наклон для каждого X. Они формируют ваше уравнение.
- P-значение. Вероятность увидеть такой большой коэффициент, если истинный эффект равен нулю. Многие аналитики считают значения ниже 0.05 значимыми.
- F и Значимость F. Тест всей модели. Малая Значимость F говорит о том, что модель объясняет больше, чем случайность.
Microsoft добавляет примечание о точности. В нем говорится: «Чем более линейны данные, тем точнее модель LINEST».
Читайте коэффициенты в простых единицах. Наклон 1,000 означает, что каждый дополнительный месяц добавляет в среднем около 1,000 к продажам за этот период.
Выполнение регрессии в Excel с помощью AI
Когда у вас много столбцов и вы не уверены, какие из них важны, проверка каждой пары вручную занимает много времени. Инструмент AI может проверить взаимосвязи во всем наборе данных и объяснить их.
Вот метод в три шага с использованием Powerdrill Bloom.
Шаг 1: Загрузите ваш файл Excel
Загрузите рабочую книгу с вашим столбцом Y и потенциальными столбцами X. На странице Excel Calculations вам предложат сначала загрузить книгу. Там добавлено: «Убедитесь, что ваши данные правильно отформатированы для обеспечения беспрепятственных вычислений».
Оставьте одну строку на наблюдение и четкие заголовки столбцов. AI использует заголовки, чтобы понять значение каждого столбца.
Шаг 2: Запросите анализ на естественном языке
Опишите, что вы хотите проверить. Например: «Выполни линейную регрессию продаж по месяцам и расходам на рекламу и объясни, что важнее».
На той же странице указано, что вы можете «ввести желаемый запрос на расчет в виде текста». Задавайте уточняющие вопросы, например, сохраняется ли взаимосвязь, если исключить один месяц-выброс.
Шаг 3: Проверьте результаты и постройте графики
Сверьте наклон с LINEST в вашей рабочей книге. Затем визуализируйте взаимосвязь. Scatter Graph Creator создан для того, чтобы «визуализировать взаимосвязи данных с помощью интерактивных и настраиваемых точечных диаграмм».
Для прогнозов, выходящих за рамки прямой линии, инструмент AI Forecasting строит прогнозные диаграммы с доверительными интервалами. Для получения дополнительной информации о самом методе см. наше пояснение по регрессионному анализу.
Распространенные ошибки, которых следует избегать
Вот ошибки, которые чаще всего возникают, когда люди запускают регрессию в Excel.
- Путаница между X и Y. Y — это значение, которое вы хотите объяснить. Их замена меняет наклон.
- Забытый флажок Метки. Если ваши диапазоны включают заголовки, отметьте Метки, иначе Excel воспримет заголовок как данные.
- Запуск регрессии в Excel для интернета. Microsoft заявляет, что вы «не можете создать регрессионный анализ» там, так как инструмент Регрессия недоступен. Используйте настольное приложение.
- Оценка только по R-квадрату. Высокий R-квадрат не доказывает причинно-следственную связь и может скрывать криволинейную закономерность. Всегда смотрите на точечную диаграмму.
- Использование слишком малого количества строк. Шесть точек могут случайно образовать ровную линию. Больше данных дает более надежный результат.
- Слишком далекая экстраполяция. Линия, построенная по месяцам с 1 по 6, может не быть верной для 24-го месяца.
- Игнорирование выбросов. Один необычный месяц может потянуть за собой всю линию. Проверяйте диаграмму на наличие точек, удаленных от остальных.
- Добавление множества взаимосвязанных столбцов X. Входные данные, которые изменяются синхронно, делают коэффициенты нестабильными. Microsoft отмечает, что LINEST «проверяет на коллинеарность» и удаляет избыточные столбцы X.
Когда вы будете готовы проверить взаимосвязи во всем наборе данных, попробуйте Powerdrill Bloom на ежемесячных показателях за прошлый год.
Часто задаваемые вопросы
Как сделать простую линейную регрессию в Excel?
Включите Analysis ToolPak, перейдите в Данные > Анализ данных и выберите Регрессия. Выберите диапазоны Y и X, отметьте Метки, если вы включили заголовки, и нажмите OK. Вы также можете использовать =LINEST(диапазон Y, диапазон X).
Можно ли сделать регрессию в Excel для интернета?
Не полностью. Microsoft заявляет, что вы можете просматривать результаты регрессии в Excel для интернета, но не можете создать там регрессионный анализ. Откройте файл в настольном приложении, чтобы запустить его.
Что означает R-квадрат в регрессии Excel?
R-квадрат показывает, какую часть вариации Y объясняет линия. Он варьируется от 0 до 1. Значение 0.93 означает, что около 93% вариации соответствует построенной линии.
Как сделать множественную регрессию в Excel?
Поместите каждую переменную X в свой собственный смежный столбец. В инструменте Регрессия выберите все столбцы X в качестве Входного интервала X. LINEST также принимает несколько столбцов X.
В чем разница между LINEST и инструментом Регрессия?
Они дают одинаковые коэффициенты. Microsoft заявляет, что инструмент Регрессия использует LINEST. Инструмент создает полную сводную таблицу, в то время как LINEST возвращает числа в виде формулы, которая обновляется вместе с вашими данными.
Источники: Поддержка Microsoft, Использование пакета анализа для выполнения сложного анализа данных · Поддержка Microsoft, функция ЛИНЕЙН (LINEST) · Поддержка Microsoft, Выполнение регрессионного анализа · Поддержка Microsoft, Добавление линии тренда или скользящего среднего на диаграмму.