Чтобы добавить линию тренда в Excel, выделите диаграмму, нажмите зелёный плюс «Элементы диаграммы» в её правом верхнем углу и отметьте «Линия тренда». Excel проведёт прямую по методу наименьших квадратов. Тип линии, уравнение, R² и прогноз на несколько периодов вперёд настраиваются на панели «Формат линии тренда».
Ниже разбираем это на примере продаж за год: как добавить линию, какой тип выбрать, как прочитать уравнение и где тренд обманывает. Если диаграммы ещё нет, сначала постройте её по нашей инструкции как сделать график в Excel по данным таблицы.
CheckroiПодборка курсов по Microsoft Excel685 курсов • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Что показывает линия тренда
Линия тренда — это прямая или кривая, которая проходит как можно ближе ко всем точкам ряда. Она отвечает на вопрос, куда в среднем движутся данные, если отбросить случайные скачки.
Excel подбирает линию методом наименьших квадратов: так сказано в справке Microsoft о параметрах линии тренда. Это та же простая регрессия, о которой мы подробно писали в статье что такое регрессия в анализе данных.
Весь разбор идёт на одном примере, заказах интернет-магазина по месяцам:
| Месяц | Заказы | Месяц | Заказы |
|---|---|---|---|
| Январь | 120 | Июль | 175 |
| Февраль | 135 | Август | 181 |
| Март | 128 | Сентябрь | 176 |
| Апрель | 150 | Октябрь | 198 |
| Май | 162 | Ноябрь | 205 |
| Июнь | 158 | Декабрь | 214 |
В марте, июне и сентябре заказы проседают, но общий рост виден и без расчётов. Линия тренда превращает это «на глаз» в число: примерно плюс 8 заказов в месяц.
В биржевом анализе линией тренда называют другое: её проводят вручную по минимумам или максимумам цены. В Excel линия всегда расчётная, и рисовать её от руки не нужно.
Как добавить линию тренда в Excel: пошаговая инструкция
Порядок для Excel 2021, 2024 и Microsoft 365 на Windows по справке Microsoft:
- Щёлкните по диаграмме, чтобы её выделить.
- Нажмите кнопку «Элементы диаграммы» (зелёный плюс справа сверху).
- Отметьте «Линия тренда». Появится прямая линейного тренда.
- Если в диаграмме несколько рядов, Excel откроет окно «Добавление линии тренда»: выберите ряд и нажмите «ОК».
- Чтобы поменять тип или включить прогноз, щёлкните по диаграмме, на вкладке «Формат» выберите линию тренда в раскрывающемся списке и нажмите «Формат выделения». Откроется панель «Формат линии тренда».
На Mac путь другой: выделите на диаграмме ряд, откройте вкладку «Конструктор диаграмм» → «Добавить элемент диаграммы» → «Линия тренда» и выберите тип или «Дополнительные параметры линии тренда».
Чтобы удалить линию, щёлкните по ней и нажмите Delete.
То же самое в Google Таблицах
Скриншоты к статье сняты в Google Таблицах. Приём там тот же, отличаются только названия пунктов. Если Excel под рукой нет, этот путь подойдёт целиком, а про совместную работу двух программ у нас есть отдельный разбор Google Таблиц и Excel.
Выделяем таблицу вместе с заголовками и открываем «Вставка» → «Диаграмма».
Таблицы сразу строят столбчатую диаграмму и открывают справа редактор. Для тренда этот тип подходит: по справке Google, линию тренда можно добавить на линейчатые, столбчатые и точечные диаграммы, а также на графики.
Дальше вкладка «Дополнительные» → раздел «Серии». Внизу раздела отмечаем «Линия тренда», под флажком появляется список типов.
Остаётся выбрать в списке ярлыков уравнение и включить R². Уравнение и коэффициент Таблицы выводят в легенду диаграммы.
На нашем примере получилось 8.24*x + 121, R² = 0.962. Что значат эти числа и почему Excel покажет другое свободное слагаемое, разбираем ниже.
Канал основателя Checkroi Вани Буявца3 700 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединитьсяКакой тип линии тренда выбрать
Типов в Excel шесть. Когда какой брать, Microsoft объясняет в справке о параметрах линии тренда, ниже коротко.
| Тип в Excel | Когда подходит | Ограничение |
|---|---|---|
| Линейная | Величина растёт или падает с постоянной скоростью | Нет |
| Логарифмическая | Сначала быстрый рост или спад, потом выход на плато | Работает и с отрицательными значениями |
| Полиномиальная | Данные то растут, то падают; степень задаётся по числу пиков и впадин | Высокая степень подгоняет линию под шум |
| Степенная | Рост с постоянным ускорением, как разгон автомобиля | Нули и отрицательные значения не допускаются |
| Экспоненциальная | Скорость изменения всё время растёт | Нули и отрицательные значения не допускаются |
| Линейная фильтрация | Сгладить колебания, это скользящее среднее по заданному числу точек | На точечной диаграмме сначала отсортируйте значения X |
Для заказов из примера мы сравнили три варианта. Линейная даёт R² = 0,962, полином второй степени 0,962, третьей 0,964. Кривая почти не добавила точности, значит, лишние параметры подгоняют линию под шум, и честнее оставить прямую.
Скользящее среднее устроено проще. При трёх точках первое значение линии равно среднему за январь, февраль и март: (120 + 135 + 128) / 3 ≈ 127,7. Как считать обычное среднее в ячейке, мы показывали в инструкции как найти среднее значение в Excel.
Уравнение, R² и прогноз
На панели «Формат линии тренда» под списком типов есть две галочки: показать уравнение на диаграмме и поместить на неё величину достоверности аппроксимации R². Включите обе, если тренд нужен не для красоты, а для выводов.
Как прочитать уравнение
Уравнение линейного тренда имеет вид y = mx + b: m здесь наклон, b свободный член. В нашем примере наклон 8,24: каждый месяц в среднем приносит на 8 заказов больше.
На графике с подписями-месяцами x означает порядковый номер точки, а не дату. Отсюда расхождение со скриншотом. Функция ТЕНДЕНЦИЯ, если не дать ей значения x, по справке Microsoft подставляет номера 1, 2, 3 и так далее. С такими номерами прямая для наших данных выходит y = 8,24x + 113,24. Google Таблицы пишут в легенде 121: это 113,24 + 8,24, то есть месяцы там пронумерованы с нуля.
Наклон от нумерации не зависит, меняется только b. Если подставляете уравнение в свои расчёты, сначала проверьте, с какого номера считается x.
Что значит R²
R² принимает значения от 0 до 1. Чем оно ближе к единице, тем точнее линия описывает точки. У нас 0,962: прямая объясняет почти весь разброс заказов, а на сезонные провалы приходится остаток.
R² не говорит о причинах. Рост может идти от рекламы, сезона или случайности, и линия этого не различает. Почему связь двух величин ещё не доказывает причину, подробно разобрано в статье что такое корреляция.
Прогноз на будущие периоды
В блоке прогноза на той же панели есть поля «Вперед» и «Назад». Число в них продлевает линию на столько периодов. Поставьте 2 во «Вперед», и линия дотянется до двух месяцев следующего года.
То же значение можно получить формулой, без диаграммы:
=ТЕНДЕНЦИЯ(B2:B13;;13)
=ТЕНДЕНЦИЯ(B2:B13;;14)Первая формула даёт 220,42 заказа на 13-й месяц, вторая 228,67 на 14-й. Пропущенный второй аргумент функция заменяет номерами 1, 2, 3, так что это продолжение той же прямой y = 8,24x + 113,24.
Частые ошибки и что с ними делать
- Экспоненциальная или степенная недоступны. В данных есть нули или отрицательные числа. Эти типы с ними не работают: уберите такие точки из ряда или выберите линейную либо полиномиальную линию.
- Вместо линии открылось окно выбора ряда. В диаграмме несколько рядов, и ни один не выделен. Выберите нужный ряд в окне «Добавление линии тренда» или заранее щёлкните по нему.
- Полином шестой степени с R² = 0,99. Чем выше степень, тем лучше кривая повторяет каждую точку, включая случайные. Для прогноза такая линия бесполезна: за краем данных она уходит вверх или вниз. Берите самую простую линию с близким R².
- Прогноз на год вперёд по шести точкам. Тренд продлевает прошлое и не знает о будущих изменениях. Короткий ряд выдерживает прогноз на один-два периода, дальше это гадание.
- В старом файле R² стал другим. Начиная с версии 2005 Excel иначе считает R² для линейного тренда, который пересекает ось в нуле. Это исправление ошибки, и Microsoft описывает его отдельно. Сама линия при этом не меняется.
Точные коэффициенты удобнее считать формулой ЛИНЕЙН(B2:B13), а не переписывать с диаграммы: в подписи числа округлены. Если таблица живёт в браузере, линия тренда есть и в Excel онлайн: там её включают переключателем в параметрах ряда на панели форматирования.
Где освоить Excel и анализ данных системно
Линия тренда закрывает одну задачу: показать направление. Чтобы строить прогнозы с сезонностью, проверять гипотезы и собирать отчёты, нужны функции, сводные таблицы и основы статистики. Ниже курсы по Excel из нашего каталога, от базовых до аналитических.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 31 900 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для аналитика Перейти на сайт курса |  Moscow Business Academy | 17 200 ₽ | 2866 ₽/мес. | 2 месяца | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
Если тренд нужен для работы с продажами и метриками, посмотрите также наш обзор предиктивной аналитики: там описано, какие методы идут после линии тренда.
