Как построить линию тренда в excel
Добавьте линию тренда на диаграмму, чтобы показать визуальные тенденции изменения данных.
Примечание: Эти действия применимы Office 2013 и более новых версиях. Ищете по Office 2010 г.?
Добавление линии тренда
Щелкните значок «+» в правом верхнем углу диаграммы.
Выберите пункт Линия тренда.
Примечание: Excel отображает параметр Линия тренда только в том случае, если выбрана диаграмма с более чем одним рядом данных без выбора ряда данных.
В диалоговом окне Добавление линии тренда выберите нужные параметры рядов данных и нажмите кнопку ОК.
Форматирование линии тренда
Щелкните в любом месте диаграммы.
На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.
Нажмите кнопку Формат выделения.
В области Формат линии тренда выберите параметр Линия тренда, чтобы выбрать линию тренда для диаграммы. Форматирование линии тренда — это статистический способ измерения данных: 
Задайте значение в полях «Вперед» и «Назад», чтобы проецируемые данные в будущем.
Добавление линии скользящего среднего
Линию тренда можно отформатирование на линию скользящего среднего.
Щелкните в любом месте диаграммы.
На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.
Нажмите кнопку Формат выделения.
В области Формат линии тренда в области Параметры линии трендавыберите Скользящие средниезначения . При необходимости укажите баллы.
Примечание: Число точек в линии тренда скользящего среднего равно общему числу точек в ряду, которое меньше числа, заданная для точки.
Добавление линии тренда или скользящего среднего на диаграмму в Office 2010 г.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните ряд данных, для которого требуется добавить линию тренда или линейную фильтрацию, или выполните указанные ниже действия, чтобы выбрать ряд данных из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
Примечание: Если выбрана диаграмма с несколькими рядами данных, но сам ряд данных не выбран, откроется диалоговое окно Добавление линии тренда. В поле со списком выберите нужный ряд данных, а затем нажмите кнопку ОК.
На вкладке Макет в группе Анализ выберите пункт Линия тренда.
Выполните одно из указанных ниже действий.
Выберите подходящий предопределенный параметр линии тренда.
Примечание: Линия тренда будет применена без возможности выбора конкретных параметров.
Нажмите Дополнительные параметры линии тренда, а затем в категории Параметры линии тренда в разделе Построение линии тренда (аппроксимация и сглаживание) выберите нужный тип линии тренда.
Используемый тип
Создание прямой линии тренда путем расчета по методу наименьших квадратов с помощью следующего уравнения:
где m — это наклон, а b — смещение.
Логарифмическая
Построение логарифмической линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:
где c и b — константы, а «ln» — натуральный логарифм.
Полиномиальная
Построение полиномиальной или криволинейной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:
где b и 
являются константами.
Построение степенной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:
где c и b — константы.
Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.
Экспоненциальная
Построение экспоненциальной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:
где c и b — константы, e — основание натурального логарифма.
Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.
Линейная фильтрация
Построение линии тренда с линейной фильтрацией по следующей формуле: 
Примечание: Число точек, образующих линию тренда с скользящее среднее, равно общему числу точек ряда за вычетом числа, указанного для параметра «Точки».
Величина достоверности аппроксимации
Построение линии тренда с указанием на диаграмме величина достоверности аппроксимации, вычисляемой по следующей формуле:
Этот параметр линии тренда располагается на вкладке Параметры диалогового окна Добавление линии тренда или Формат линии тренда.
Примечание: Отображаемая вместе с линией тренда величина достоверности аппроксимации не является скорректированной. Для логарифмической, степенной и экспоненциальной линий тренда в Excel используется видоизмененная модель регрессии.
Если выбран тип Полиномиальная, введите в поле Степень наибольшую степень для независимой переменной.
Если выбран тип Линейная фильтрация, введите в поле Точки число точек, используемых для расчета линейного фильтра.
При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения X.
При добавлении линии тренда в график, гистограмму, диаграмму с областями или линейчатую диаграмму линия тренда рассчитывается исходя из предположения, что значения x равны 1, 2, 3, 4, 5, 6 и т. д. Такое допущение делается вне зависимости от того, являются ли значения x числовыми или текстовыми. Чтобы рассчитать линию тренда на основе числовых значений x, следует использовать точечную диаграмму.
В Excel название линии тренда назначается автоматически, но его можно изменить. В диалоговом окне Формат линии тренда в категории Параметры линии тренда в разделе Название аппроксимирующей (сглаженной) кривой выберите параметр Другое, а затем укажите название в поле Другое.
Кроме того, можно добавить линейную фильтрацию, которая сглаживает отклонения в данных и более четко показывает форму линии тренда.
Если изменить диаграмму или ряд данных таким образом, что они больше не будут поддерживать соответствующую линию тренда (например, если изменить тип диаграммы на объемную диаграмму или изменить представление отчет сводной диаграммы или связанный отчет сводной таблицы), линия тренда больше не будет отображаться на диаграмме.
Для данных в строке (без диаграммы) наиболее точные прямые или экспоненциальные линии тренда можно создать с помощью автозаполнения или статистических функций, таких как РОСТ() или ТЕНДЕНЦИЯ().
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
Чтобы изменить параметры цвета, типа или тени линии тренда, выберите категорию Цвет линии, Тип линии или Тень и задайте нужные значения.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
Чтобы указать число периодов для включения в прогноз, в разделе Прогноз укажите число периодов в поле вперед на или назад на.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
Установите флажок пересечение кривой с осью Y в точке, а затем в поле пересечение кривой с осью Y в точке введите значение, чтобы задать точку пересечения линии тренда с вертикальной осью (осью значений).
Примечание: Это можно сделать только при использовании экспоненциальной, прямой или полиномиальной линии тренда.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
Чтобы показать на диаграмме уравнение линии тренда, установите флажок показывать уравнение на диаграмме.
Примечание: Уравнения линии тренда нельзя показать для скользящего среднего.
Совет: Формула линии тренда округлена, чтобы сделать ее более понятной. Однако вы можете изменить количество цифр для выбранной подписи линии тренда в поле Число десятичных разрядов на вкладке Число в диалоговом окне Формат подписи линии тренда. (Вкладка Формат, группа Текущий выделение,кнопка Формат выделения).
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, для которой требуется показать величина достоверности аппроксимации, или выполните указанные ниже действия, чтобы выбрать линию тренда из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
На вкладке Параметры линии тренда установите флажок поместить на диаграмму величину достоверности аппроксимации (R^2).
Примечание: Для скользящего среднего величину достоверности аппроксимации отобразить нельзя.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо удалить, или выполните следующие действия, чтобы выбрать линию тренда из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
Выполните одно из указанных ниже действий.
На вкладке Макет в группе Анализ нажмите Линия тренда, а затем выберите пункт Нет.
Нажмите клавишу DELETE.
Совет: Вы также можете удалить линию тренда сразу после ее добавления на диаграмму, нажав кнопку Отменить панели быстрого доступа или нажав CTRL+Z.
Добавление линии тренда
В меню Вид выберите пункт Разметка страницы.
На диаграмме выберите ряд данных, к который вы хотите добавить линию тренда, а затем перейдите на вкладку Конструктор диаграмм.
Например, щелкните одну из линий графика. Будут выделены все маркер данных этого ряд данных.
На вкладке Конструктор нажмите кнопку Добавить элемент диаграммыи выберите линия тренда.
Выберите вариант линии тренда или нажмите кнопку Дополнительные параметры линии тренда.
Вы можете выбрать один из следующих вариантов:
Вы также можете дать линии тренда имя и выбрать параметры прогноза.
Удаление линии тренда
В меню Вид выберите пункт Разметка страницы.
Щелкните диаграмму с линией тренда и перейдите на вкладку Конструктор диаграмм.
Нажмите кнопку Добавить элемент диаграммы,выберите линия трендаи щелкните Нет.
Также можно щелкнуть линию тренда и нажать кнопку DELETE.
Щелкните в любом месте диаграммы, чтобы отдемонстрировать вкладку Диаграмма на ленте.
Нажмите кнопку Формат, чтобы открыть параметры форматирования диаграммы.
Откройте ряд, который вы хотите использовать в качестве основы для линии
тренда 
Настройте линию тренда в нужном вам направлении.
Используйте переключатель, чтобы показать или скрыть линию тренда.
Важно: Начиная с Excel 2005 г., Excel скорректировал способ расчета значения R2 для линейных линий тренда на диаграммах, для которых для перехватить линию тренда установлено значение 0 (0). Эта корректировка исправит вычисления, которые дают неправильные значения R 2, и выровняет вычисление R2 с функцией LINEST. В результате на диаграммах, созданных в предыдущих Excel, могут отображаться разные значения R2. Дополнительные сведения см. в таблице Изменения внутренних вычислений линейных линий тренда на диаграмме.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Создание диаграмм и графиков в Excel
ПОСТРОЕНИЕ ЛИНИИ ТРЕНДА В EXCEL ДЛЯ АНАЛИЗА ГРАФИКА
Очень часто на графиках с большим количеством информации содержит колебания (шумы), из-за которых усложняется восприятия информации. Благодаря линиям тренду можно экспонировать главную тенденцию, которую отображает график. Линия тренда как будто визуально отфильтровывает шумы на графике, а информация становится более понятной.
КАК ДОБАВИТЬ ЛИНИЮ ТРЕНДА НА ГРАФИК
Данные в диапазоне A1:B7 отобразим на графике и найдем линейную функциональную зависимость y=f(x):
Выделите диапазон A1:B7 и выберите инструмент: «Вставка»-«Диаграммы»-«Точечная»-«Точечная с гладкими кривыми и маркерами». 
Когда график активный нам доступная дополнительная панель, а в ней выберите инструмент: «Работа с диаграммами»-«Макет»-«Линия тренда»-«Линейное приближение»
Сделайте двойной щелчок по лини тренду и в появившемся окне поставьте галочку напротив опции: «показывать уравнение на диаграмме».
Примечание. Если на графике находится несколько линий данных, тогда предварительно следует выделить линию того графика для которого следует включить линию тренда.
ПРОГНОЗИРОВАНИЕ В EXCEL С ПОМОЩЬЮ ЛИНИИ ТРЕНДА
Сменим график на гистограмму, чтобы сравнить их уравнения:
Щелкните правой кнопкой по графику и выберите опцию из контекстного меню: «Изменить тип диаграммы».
В появившемся окне выберите параметр: «Гистограмма»-«Гистограмма с группировкой».
Как видно оба графика отображают одни и те же данные, но у них разные уравнения трендовых линий, сохраняется только направление тенденции (снизу вверх).
Из этого следует, что линия тренда это только аппроксимирующий (приближенный) инструмент.
Сравним уравнения на разных типах графиков. На оси координат XY:
прямая с уравнением: y=2,4229x-3,519 пересекает вертикальную ось Y в отрицательном значении оси: y=-3,519.
прямая с уравнением: y=2,4229x+18,287 пересекает вертикальную ось Y на уровне: y=18,287.
Чтобы визуально убедится в этом, сделаем следующее:
Снова измените гистограмму на точечную диаграмму. Правой кнопкой по гистограмме «Изменить тип диаграммы», далее «Точечная»-«Точечная с гладкими кривыми и маркерами».
Сделайте двойной щелчок по линии тренда и измените ее параметры так: «Прогноз»-«назад на:» установите значение 12,0. И нажмите ОК.
Теперь, даже визуально видно, что угол наклонения линии на разных типов графиков отличается. Но направление тенденции сохраняется. Это удобный инструмент для визуального восприятия тенденции. Но при анализе графиков его следует воспринимать как аппроксимирующий вспомогательный инструмент.
Построение линии тренда в Microsoft Excel
Одной из важных составляющих любого анализа является определение основной тенденции событий. Имея эти данные можно составить прогноз дальнейшего развития ситуации. Особенно наглядно это видно на примере линии тренда на графике. Давайте выясним, как в программе Microsoft Excel её можно построить.
Линия тренда в Excel
Приложение Эксель предоставляет возможность построение линии тренда при помощи графика. При этом, исходные данные для его формирования берутся из заранее подготовленной таблицы.
Построение графика
Для того, чтобы построить график, нужно иметь готовую таблицу, на основании которой он будет формироваться. В качестве примера возьмем данные о стоимости доллара в рублях за определенный период времени.
-
Строим таблицу, где в одном столбике будут располагаться временные отрезки (в нашем случае даты), а в другом – величина, динамика которой будет отображаться в графике.
Создание линии тренда
Теперь нужно непосредственно добавить линию тренда.
- Находясь во вкладке «Макет» кликаем по кнопке «Линия тренда», которая расположена в блоке инструментов «Анализ». Из открывшегося списка выбираем пункт «Экспоненциальное приближение» или «Линейное приближение».
- После этого, линия тренда добавляется на график. По умолчанию она имеет черный цвет.
Настройка линии тренда
Имеется возможность дополнительной настройки линии.
- Последовательно переходим во вкладке «Макет» по пунктам меню «Анализ», «Линия тренда» и «Дополнительные параметры линии тренда…».
- Открывается окно параметров, можно произвести различные настройки. Например, можно выполнить изменение типа сглаживания и аппроксимации, выбрав один из шести пунктов:
- Полиномиальная;
- Линейная;
- Степенная;
- Логарифмическая;
- Экспоненциальная;
- Линейная фильтрация.
Для того, чтобы определить достоверность нашей модели, устанавливаем галочку около пункта «Поместить на диаграмму величину достоверности аппроксимации». Чтобы посмотреть результат, жмем на кнопку «Закрыть».
Если вас не удовлетворяет уровень достоверности, то можете вернуться опять в параметры и сменить тип сглаживания и аппроксимации. Затем, сформировать коэффициент заново.
Прогнозирование
Главной задачей линии тренда является возможность составить по ней прогноз дальнейшего развития событий.
- Опять переходим в параметры. В блоке настроек «Прогноз» в соответствующих полях указываем насколько периодов вперед или назад нужно продолжить линию тренда для прогнозирования. Жмем на кнопку «Закрыть».
- Опять переходим к графику. В нем видно, что линия удлинена. Теперь по ней можно определить, какой приблизительный показатель прогнозируется на определенную дату при сохранении текущей тенденции.
Как видим, в Эксель не составляет труда построить линию тренда. Программа предоставляет инструменты, чтобы её можно было настроить для максимально корректного отображения показателей. На основании графика можно сделать прогноз на конкретный временной период.
Как работать с линиями тренда в диаграммах Microsoft Excel
Вы можете добавить линию тренда на диаграмму в Excel, чтобы показать общую картину данных во времени. Вы также можете расширить линии тренда для прогнозирования будущих данных. Excel позволяет легко сделать все это.
Линия тренда (или линия наилучшего соответствия) — это прямая или изогнутая линия, которая визуализирует общее направление значений. Обычно они используются, чтобы показать тенденцию во времени.
В этой статье мы расскажем, как добавлять различные линии тренда, форматировать их и расширять для будущих данных.
Добавить линию тренда
Вы можете добавить линию тренда на диаграмму Excel всего за несколько кликов. Давайте добавим линию тренда к линейному графику.
Выберите диаграмму, нажмите кнопку «Элементы диаграммы», а затем установите флажок «Линия тренда».
Это добавляет на диаграмму линейную линию тренда по умолчанию.
Доступны разные линии тренда, поэтому рекомендуется выбрать ту, которая лучше всего работает с шаблоном ваших данных.
Щелкните стрелку рядом с параметром «Линия тренда», чтобы использовать другие тенденции, включая экспоненциальную или скользящую среднюю.
Некоторые из основных типов линий тренда включают:
Линейный: прямая линия, показывающая постоянную скорость увеличения или уменьшения значений.
Экспоненциальная: эта линия тренда визуализирует увеличение или уменьшение значений с все более высокой скоростью. Линия более изогнута, чем линейная линия тренда.
Логарифмический: этот тип лучше всего использовать, когда данные быстро увеличиваются или уменьшаются, а затем выравниваются.
Скользящее среднее: чтобы сгладить колебания данных и более четко показать тренд, используйте этот тип линии тренда. Он использует указанное количество точек данных (две по умолчанию), усредняет их, а затем использует это значение в качестве точки на линии тренда.
Чтобы увидеть полный набор параметров, нажмите «Дополнительные параметры».
Откроется панель «Форматирование линии тренда», на которой представлены все типы линий тренда и дополнительные параметры. Мы рассмотрим больше из них позже в этой статье.
Выберите из списка линию тренда, которую вы хотите использовать, и она будет добавлена к вашей диаграмме.
Добавление линий тренда к нескольким рядам данных
В первом примере на линейном графике был только один ряд данных, а на следующей столбчатой диаграмме — два.
Если вы хотите применить линию тренда только к одному из рядов данных, щелкните правой кнопкой мыши нужный элемент. Затем выберите в меню «Добавить линию тренда».
Откроется панель «Форматирование линии тренда», на которой вы сможете выбрать нужную линию тренда.
В этом примере линия тренда скользящего среднего была добавлена к рядам данных графиков Tea.
Если вы нажмете кнопку «Элементы диаграммы», чтобы добавить линию тренда, не выбирая сначала ряд данных, Excel спросит вас, в какой ряд данных вы хотите добавить линию тренда.
Вы можете добавить линию тренда к нескольким рядам данных.
На следующем изображении линия тренда была добавлена к ряду данных чая и кофе.
Вы также можете добавлять разные линии тренда к одному и тому же ряду данных.
В этом примере на график добавлены линии тренда Linear и Moving Average.
Отформатируйте линии тренда
Линии тренда добавляются в виде пунктирной линии и соответствуют цвету ряда данных, которому они назначены. Возможно, вы захотите отформатировать линию тренда по-другому, особенно если у вас есть несколько линий тренда на диаграмме.
Откройте панель «Форматировать линию тренда», дважды щелкнув линию тренда, которую нужно отформатировать, или щелкнув правой кнопкой мыши и выбрав «Форматировать линию тренда».
Щелкните категорию «Заливка и линия», а затем вы можете выбрать другой цвет, ширину, тип штриховки и другие параметры для линии тренда.
В следующем примере я изменил цвет на оранжевый, чтобы он отличался от цвета столбца. Я также увеличил ширину до 2 пунктов и изменил тип штриха.
Расширьте линию тренда для прогнозирования будущих значений
Очень интересная функция линий тренда в Excel — это возможность продлить их в будущем. Это дает нам представление о том, какие будущие значения могут быть основаны на текущей тенденции данных.
На панели «Форматирование линии тренда» щелкните категорию «Параметры линии тренда», а затем введите значение в поле «Вперед» в разделе «Прогноз».
Отображение значения R-Squared
Значение R-квадрат — это число, которое показывает, насколько хорошо ваша линия тренда соответствует вашим данным. Чем ближе значение R-квадрата к 1, тем лучше соответствие линии тренда.
На панели «Форматирование линии тренда» щелкните категорию «Параметры линии тренда», а затем установите флажок «Отображать значение R-квадрата на диаграмме».
Показано значение 0,81. Это разумное соответствие, так как значение выше 0,75 обычно считается приемлемым — чем ближе к 1, тем лучше.
Если значение R-квадрата низкое, вы можете попробовать другие типы линий тренда, чтобы увидеть, подходят ли они для ваших данных.