Как построить линию тренда в экселе. линия тренда в excel на разных графиках

Как диаграммы и графики нас обманывают

Однако диаграммы (особенно когда речь заходит о визуальном представлении большого объема данных), хотя и крайне удобны для восприятия, далеко не всегда очевидны.

Диаграмма построенная на основе таблицы в MS Excel

Эта таблица показывает среднее число посетителей некого сайта в сутки по месяцам, а также количество просмотров страниц на одного посетителя. Логично, что просмотров страниц всегда должно быть больше, чем посетителей, так как один пользователь может просмотреть сразу несколько страниц.

Не менее логично и то, что чем больше страниц просматривает посетитель, тем лучше сайт – он захватывает внимание пользователя и заставляет его углубиться в чтение. Что видит владелец сайта из нашей диаграммы? Что дела у него идут хорошо! В летние месяцы был сезонный спад интереса, но осенью показатели вернулись и даже превысили показатели весны

Выводы? Продолжаем в том же духе и вскоре добьемся успеха!

Что видит владелец сайта из нашей диаграммы? Что дела у него идут хорошо! В летние месяцы был сезонный спад интереса, но осенью показатели вернулись и даже превысили показатели весны. Выводы? Продолжаем в том же духе и вскоре добьемся успеха!

Наглядна диаграмма? Вполне. А вот очевидна ли она? Давайте разберемся.

Советы по настройке линии

Чтобы сделать ваш график еще более красивым, вы можете изменить заголовок диаграммы, легенду, оси, линии сетки и другие элементы, как описано в этом руководстве: Как настроить график в Excel. А ниже вы найдете несколько советов, касающихся непосредственно настройки линии.

Отображать среднее/контрольное значение на линии

В некоторых ситуациях, например, когда вы устанавливаете относительно большие интервалы для вертикальной оси Y, вашим пользователям может быть сложно определить точную точку, где линия пересекает столбцы. Нет проблем, просто покажите это значение на графике. Вот как вы можете это сделать:

1. Нажмите на строку, чтобы выбрать ее:
2. Выделив всю строку, щелкните последнюю точку данных. Это отменит выбор всех других точек данных, так что останется выбранной только последняя:
3. Щелкните правой кнопкой мыши выбранную точку данных и выберите «Добавить метку данных» в контекстном меню:

Метка появится в конце строки, предоставляя больше информации для просмотра диаграмм:

Добавьте текстовую метку для линии

Чтобы еще больше улучшить график, вы можете добавить текстовую метку к линии, чтобы указать, что это на самом деле. Вот шаги для этой настройки:

1. Выберите последнюю точку данных на линии и добавьте к ней метку данных, как описано в .
2. Нажмите на метку, чтобы выбрать ее, затем щелкните внутри поля метки, удалите существующее значение и введите текст:
3. Наведите указатель мыши на поле метки, пока указатель мыши не примет форму четырехсторонней стрелки, а затем перетащите метку немного выше линии:
4. Щелкните метку правой кнопкой мыши и выберите «Шрифт…» в контекстном меню.
5. Настройте стиль, размер и цвет шрифта по своему усмотрению:

Когда закончите, удалите легенду диаграммы, потому что теперь она излишняя, и наслаждайтесь более красивым и четким видом вашей диаграммы:

Изменить тип линии

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

1. Дважды щелкните строку.
2. На Серия данных формата панель, иди Заполнение и линия > Линияоткройте раскрывающийся список Тип штриха и выберите нужный тип.

Например, вы можете выбрать Square Dot:

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

Продлить линию до краев области диаграммы

Как вы можете заметить, горизонтальная линия всегда начинается и заканчивается в середине баров. Но что, если вы хотите, чтобы он растянулся до правого и левого краев диаграммы?

Вот быстрое решение: дважды щелкните по горизонтальной оси, чтобы открыть Формат оси панель, переключиться на Параметры оси и выберите положение оси на делениях:

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

В качестве обходного пути вы можете возиться с исходными данными вместо того, чтобы возиться с настройками графика:

1. Вставьте новую строку перед первой и после последней строки с вашими данными.
2. Скопируйте среднее/контрольное/целевое значение в новые строки и оставьте ячейки в первых двух столбцах пустыми, как показано на снимке экрана ниже.
3. Выделите всю таблицу с пустыми ячейками и вставьте столбцово-линейную диаграмму.

Теперь на нашем графике хорошо видно, насколько первый и последний бары далеки от среднего:

Кончик. Если вы хотите нарисовать вертикальную линию на точечной диаграмме, гистограмме или линейном графике, вы найдете подробное руководство в этом руководстве: Как вставить вертикальную линию в диаграмму Excel.

Вот как вы добавляете линию в график Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Добавление линии тренда на график

Для примера возьмем средние цены на нефть с 2000 года из открытых источников. Данные для анализа внесем в таблицу:

Линия тренда в Excel – это график аппроксимирующей функции. Для чего он нужен – для составления прогнозов на основе статистических данных. С этой целью необходимо продлить линию и определить ее значения.

Если R2 = 1, то ошибка аппроксимации равняется нулю. В нашем примере выбор линейной аппроксимации дал низкую достоверность и плохой результат. Прогноз будет неточным.

Внимание. Линию тренда нельзя добавить следующим типам графиков и диаграмм:. Добавить трендлайнДобавить трендлайн

Добавить трендлайнДобавить трендлайн

Можете ли вы анализировать тенденции данных с помощью спарклайнов?

Да, вы определенно можете! Спарклайны — это небольшие диаграммы, которые вы видите внутри отдельных ячеек рабочего листа, которые можно использовать для отображения анализа тенденций в Excel или для визуального представления ваших данных и отображения тенденции в данных

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

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

Вот как вы можете сделать это:

1. Нажмите на пустую ячейку рядом с данными, которые вы хотите проиллюстрировать на спарклайне.
2. Затем вам нужно нажать на кнопку Вставить; оттуда вы направляетесь к набору Sparklines, а затем либо на столбец, линию или выигрыш / проигрыш.
3. Перейдите в поле «Диапазон данных», где вам нужно будет ввести диапазоны ячеек, которые содержат данные, которые вы хотите отобразить в спарклайне. Например, если ваши данные расположены в A, B, C и D строки 3, вам нужно ввести A3: D3.
4. Кроме того, вы также можете выбрать диапазон ячеек на листе напрямую. Вы можете временно свернуть диалоговое окно, а затем снова открыть его.
5. Как только вы нажмете на OK, вы увидите раздел Sparkline Tools на ленте. Вы можете воспользоваться рекомендациями на вкладке «Дизайн», чтобы адаптировать спарклайны в соответствии со своими предпочтениями.

Как вы настраиваете свои спарклайны?

Закончив создание спарклайнов, вы можете в любое время изменить его формат, стиль и тип:

1. Сначала вам нужно выбрать спарклайны, которые вы хотите настроить.
2. Для этого вам нужно перейти на вкладку «Дизайн» и выбрать параметры, которые вы хотите выбрать.
3. Здесь вы можете изменить формат или стиль спарклайнов, а также настройки оси.
4. При нажатии на параметр «Тип оси даты» в Excel откроется новое диалоговое окно «Спарклайн». здесь вы можете выбрать диапазон вашей рабочей книги, в котором есть все значения данных, которые вы ищете в своих данных спарклайна.
5. После завершения настройки вы увидите результаты в своем листе Excel.

Заключение по анализу трендов в Excel

Вышеуказанные три являются наиболее распространенными методами анализ тенденций Excel, Первая является наиболее простой формой и использует только графическое представление доступных данных. Второй использовал прошлые данные и усреднил их. Последний из них использовал формальную процедуру регрессионного анализа, которая была предоставлена в пакете инструментов анализа данных Excel и сегодня является стандартной процедурой для оценки взаимосвязи и тенденции. Вы также можете скачать образец файла по этой ссылке.

Как диаграммы и графики нас обманывают

Однако диаграммы (особенно когда речь заходит о визуальном представлении большого объема данных), хотя и крайне удобны для восприятия, далеко не всегда очевидны.

Проиллюстрирую свои слова простейшим примером:

Диаграмма построенная на основе таблицы в MS Excel

Эта таблица показывает среднее число посетителей некого сайта в сутки по месяцам, а также количество просмотров страниц на одного посетителя. Логично, что просмотров страниц всегда должно быть больше, чем посетителей, так как один пользователь может просмотреть сразу несколько страниц.

Не менее логично и то, что чем больше страниц просматривает посетитель, тем лучше сайт — он захватывает внимание пользователя и заставляет его углубиться в чтение. Что видит владелец сайта из нашей диаграммы? Что дела у него идут хорошо! В летние месяцы был сезонный спад интереса, но осенью показатели вернулись и даже превысили показатели весны

Выводы? Продолжаем в том же духе и вскоре добьемся успеха!

Что видит владелец сайта из нашей диаграммы? Что дела у него идут хорошо! В летние месяцы был сезонный спад интереса, но осенью показатели вернулись и даже превысили показатели весны. Выводы? Продолжаем в том же духе и вскоре добьемся успеха!

Наглядна диаграмма? Вполне. А вот очевидна ли она? Давайте разберемся.

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

В Google Sheets выбираем Редактор диаграмм
-> Дополнительные
и ставим галочку возле Линии тренда
. В настройках выбираем Ярлык
— Уравнение
и Показать R^2
.

Если вы делаете все в MS Excel, то правой кнопкой мыши кликаем на график и в выпадающем меню выбираем «Добавить линию тренда».

По умолчанию строится линейная функция. Справа выбираем «Показывать уравнение на диаграмме» и «Величину достоверности аппроксимации R^2».

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.

Как построить график спроса и предложения в Excel

Спрос – это желание обладать товаром или услугой, подкрепленное возможностью. То есть «хочу и могу». Не просто потребность, а платежеспособность в отношении определенного продукта в существующих рыночных условиях.

Величина спроса – число товаров и услуг, которое человек готов купить в данный момент, в данном месте, за данную цену.

На величину объема сбыта влияют прямо и косвенно множество факторов:

1. активность рекламной кампании;
2. мода;
3. вкус покупателя, ожидания;
4. размер дохода потребителя;
5. полезность товара;
6. доступность;
7. стоимость схожих категорий товаров и т.д.

Зависимость между величиной спроса и факторами – это функция спроса. В экономической практике принято рассматривать функцию спроса от цены. В данном случае все определяющие величину спроса факторы считаются неизменными.

Графическая иллюстрация функции спроса от цены – кривая спроса. Основное свойство данного экономического параметра: уменьшение цены ведет к возрастанию сбыта продукта. И, напротив, высокая стоимость продукта ограничивает спрос на него.

Обратная зависимость имеет фундаментальный характер. Потому ее считают законом спроса. Изобразим его наглядно с помощью графика.

1. Внесем данные по ценам на товар и по количеству проданных единиц в шкалу спроса:
2. Переходим на вкладку «Вставка», инструмент «Диаграммы» — выбираем тип графика.
3. Для настройки делаем график активным, чтобы появилось дополнительная группа закладок под названием «Работа с диаграмами». Выбераем закладку «Конструктор», а в ней инструмент «Выбрать данные».
4. В окне «Выбор источника данных» из левой колонки «Элементы легенды (ряды)» удаляем данные «Продано».
5. В этом же окне в правой колонке «Подписи горизонтальной оси (категории)» жмем «Изменить».
6. Выделяем диапазон ячеек B2:B6 чтобы автоматически заполнить параметрами поле в появившимся окне «Подписи оси».

Обратите внимание! Количество продукции – ось абсцисс (горизонтальная). Цена – ось ординат (вертикальная)

Полноценный анализ ситуации на рынке невозможен без рассмотрения предложения. Это совокупность продуктов и услуг, которые присутствуют на рынке и предлагаются продавцом покупателю за определенную цену.

У данной экономической категории есть величина (число товаров и услуг, предлагаемых в конкретный временной промежуток, в конкретном месте, по определенной цене).

Цена предложения – прогнозируемый показатель. Это минимальная сумма, за которую продавец согласен предложить потребителю свой товар.

Объем предложения зависит, соответственно, от цены. Только в данном случае наблюдается обратная зависимость (ср.: объем): чем ниже цена, тем меньше предлагаемой продукции. Продавец лучше придержит часть товара на складе, чем отдаст за бесценок. Хотя на объем предложения влияет не только стоимость.

Функция предложения от цены показывает зависимость величины предложения от его денежной оценки.

Добавим в демонстрационную табличку еще один столбец. Условно назовем его «Предложено»:

Теперь отобразим на графике сразу 2 показателя: «Спрос» и «Предложение». В одной области. Для этой цели подойдет точечная диаграмма.

Выделяем таблицу с исходными данными и выберем инструмент: «Вставка»-«Точечная»-«Точечная с гладкими кривыми и маркерами».

Снова выбираем «Конструктор»-«Выбрать данные» и задаем параметры в окне «Изменение ряда» для графиков:спрос:
предложение:

Следим, чтобы горизонталь показывала количество, а вертикаль – цену. Получаем результат:

Интерпретируем. Пересечение графиков иллюстрирует становление равновесной цены (50 рублей) и равновесного количества продаж (300 единиц). Область выше равновесной цены – избыток продукции. Производитель вынужден постепенно уменьшать стоимость. Область ниже равновесной цены – дефицит. Цены будут повышаться.

Часть 10. Подбор формул по графику. Линия тренда

Для рассмотренных выше задач удавалось построить уравнение или систему уравнений.

Но во многих случаях при решении практических задач имеются лишь экспериментальные (результаты измерений, статистические, справочные, опытные) данные. По ним с определенной мерой близости пытаются восстановить эмпирическую формулу (уравнение), которая может быть использована для поиска решения, моделирования, оценки решений, прогнозов.

Процесс подбора эмпирической формулы P(x)
для опытной зависимости F(x)
называется аппроксимацией
(сглаживанием). Для зависимостей с одним неизвестным в Excel используются графики, а для зависимостей со многими неизвестными – пары функций из группы Статистические
ЛИНЕЙН и ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ.

В настоящем разделе рассматривается аппроксимация экспериментальных данных с помощью графиков Excel: на основе данных стоится график, к нему подбирается линия тренда

, т.е. аппроксимирующая функция, которая с максимальной степенью близости приближается к опытной зависимости.

Степень близости подбираемой функции оценивается коэффициентом детерминации R2

. Если нет других теоретических соображений, то выбирают функцию с коэффициентом R2
, стремящимся к 1. Отметим, что подбор формул с использованием линии тренда позволяет установить как вид эмпирической формулы, так и определить численные значения неизвестных параметров.

Excel предоставляет 5 видов аппроксимирующих функций:

1. Линейная – y=cx+b
. Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.

2. Полиномиальная – y=c0+c1x+c2x2+…+c6x6
. Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.

3. Логарифмическая – y=c
lnx+b
. Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.

4. Степенная – y=cxb
, (х
>0и y
>0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.

5. Экспоненциальная – y=cebx
, (e
– основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.

Для всех 5-ти видов функций используется аппроксимация данных по методу наименьших квадратов (см. справку по F1 «линия тренда»).

В качестве примера
рассмотрим зависимость продаж от рекламы, заданную следующими статистическими данными по некоторой фирме:

(тыс. руб.)	1,5	2,5	3,5	4,5	5,5
Продажи (тыс. руб.)

Необходимо построить функцию, наилучшим образом отражающую эту зависимость. Кроме того, необходимо оценить продажи для рекламных вложений в 6 тыс. руб.

Приступим к решению
. В первую очередь введите эти данные в Excel и постройте график, как на рис. 38. Как видно, график построен на основании диапазона B2:J2. Далее, щелкнув правой кнопкой мыши по графику, добавьте линию тренда, как показано на рис. 38.

Чтобы подписать ось Х соответствующими значениями рекламы (как на рис. 38), следует в ниспадающем меню (рис. 38) выбрать пункт И
сходные данные
. В открывшемся одноименном окне, в закладке Ряд
, в поле П
одписи оси Х
, укажите диапазон ячеек, где записаны значения Х (здесь $B$1:$K$1):

В открывшемся окне настройки (рис. 39), на закладке Тип
выберите для аппроксимации логарифмическую
линию тренда (по виду графика). На закладке Параметры
установите флажки, отображающие на графике уравнение и коэффициент детерминации.

После нажатия ОК
Вы получите результат, как на рис. 40. Коэффициент детерминации R2=
0.9846, что является неплохой степенью близости. Для подтверждения правильности выбранной функции (поскольку других теоретических соображений нет) спрогнозируйте развитие продаж на 10 периодов вперед. Для этого щелкните правой кнопкой по линии тренда – измените формат – после этого в поле Прогноз: вперед на:
установите 10 (рис.

После установки прогноза Вы увидите изменение кривой графика на 10 периодов наблюдения вперед, как на рис. 42. Он с большой долей вероятности отражает дальнейшее увеличение продаж с увеличением рекламных вложений.

Вычисление по полученной формуле =237,96*LN(6)+5,9606 в Excel дает значение 432 тыс. руб.

В Excel имеется функция ПРЕДСКАЗ(), которая вычисляет будущее значение Y по существующим парам значений X и Y значениям с использованием линейной регрессии. Функция Y по возможности должна быть линейной, т.е. описываться уравнением типа c+bx
.

Функция предсказания для нашего примера запишется так: =ПРЕДСКАЗ(K1;B2:J2;B1:J1). Запишите – должно получится значение 643,6 тыс. руб.

Часть11. Контрольные задания

Предыдущая12345678910111213141516Следующая

Легкая версия построения

Процесс построения линии тренда состоит из трех этапов: ввод в
excel исходных данных, построение графика, выбор линии тренда и ее
параметров.

Начнем с ввода данных.

1. Создаем в Excel таблицу с исходными данными.

(Рисунок 1)

2. Выделяем ячейки B3:B17 и перейдя на закладку «Вставка»
выбираем «График».

(Рисунок 2)

3. После того как график построен, можно добавить подписи и
заголовок.

Для начала кликнем левой кнопкой мыши по границе графика, чтобы
выделить его.

Затем перейдем на закладку «Конструктор» и выберем «Макет
1».

(Рисунок 3)

4. Переходим к построению линии тренда. Для этого снова выделяем
график и переходим на закладку «Макет».

(Рисунок 4)

5. Нажимаем на кнопку «Линия тренда» и выбираем «линейное
приближение» или «экспоненциальное приближение».

(Рисунок 5)

Так мы построили первичную Линию тренда, которая может мало
соответствовать действительности.

Это наш промежуточный результат.

(Рисунок 6)

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

Построение уравнений регрессии с помощью линий тренда в MS Excel при хронометражных наблюдениях

Как поступить в случае, если для определенных объемов/размеров продукции хронометражные замеры отсутствуют? Или число замеров недостаточно, а дополнительные наблюдения в ближайшее время осуществить невозможно? Наилучший способ решения данной проблемы – построение расчетных зависимостей (уравнений регрессии) с помощью линий тренда в MS Excel.

Рассмотрим реальную ситуацию: на складе с целью установления величины трудовых затрат по коробочной отборке заказа были проведены хронометражные наблюдения. Результаты этих наблюдений представлены в таблице 1 ниже.

Впоследствии возникла необходимость определения затрат времени на отборку 0,6 и 0,9 м3 товара/заказа. В связи с невозможностью проведения дополнительных хронометражных исследований затраты времени на отборку данных объемов заказа были рассчитаны с помощью уравнений регрессии в MS Excel. Для этого таблица 1 была преобразована в таблицу 2.

Выбор точечной диаграммы, рис. 1

Следующий шаг: курсор мыши был установлен на одной из точек графика и с помощью правой кнопки мыши было вызвано контекстное меню, в котором был выбран пункт: «добавить линию тренда» (рис.2).

Добавление линии тренда, рис. 2

В появившемся окне настройки формата линии тренда (рис. 3) были последовательно выбраны: тип линии линейная/степенная и установлены флажки на следующие пункты: «показать уравнение на диаграмме» и «поместить на диаграмме величину достоверности аппроксимации (R^2)» (коэффициент детерминации).

Формат линии тренда, рис. 3

В результате были получены графики, представленные на рис. 4 и 5.

• Линейная расчетная зависимость, рис. 4
• Степенная расчетная зависимость, рис. 5

Наглядный анализ графиков однозначно свидетельствует о близости полученных зависимостей. Кроме того, величина достоверности аппроксимации (R^2), которую также называют коэффициентом детерминации, в случае обеих зависимостей составляет одну и ту же величину 0,97.

Известно, что чем ближе коэффициент детерминации к 1, тем больше линия тренда соответствует действительности. Также можно констатировать, что изменение затрат времени на обработку заказа на 97% объясняется изменением количества товара.

Поэтому в данном случае не принципиально: какую расчетную зависимость выбрать в качестве основной для последующего расчета временных затрат.

Примем за основную — линейную расчетную зависимость. Тогда значения затрат времени в зависимости от количества товара будут определяться по формуле: y = 54,511x + 0,1489. Результаты этих расчетов для количества товара, по которому ранее были проведены хронометражные наблюдения, представлены в таблице 3 ниже.

Определим среднее отклонение затрат времени, рассчитанных по уравнению регрессии от затрат времени, рассчитанных по данным хронометражных наблюдений: (-0,05+0,10-0,05+0,01)/4=0,0019.

Таким образом, затраты времени, рассчитанные по уравнению регрессии отличаются от затрат времени, рассчитанных по данным хронометражных наблюдений всего на 0,19%.

Расхождение данных ничтожно мало.

1. По формуле: y = 54,511x + 0,1489 установим затраты времени для количества товара, по которому ранее не были проведены хронометражные наблюдения (таблица 4).
2. Таким образом, построение расчетных зависимостей с помощью линий тренда в MS Excel – это отличный способ установления затрат времени по операциям, которые в силу различных причин не были охвачены хронометражными наблюдениями.

Как добавить линию в существующий график Excel

Для добавления линии к существующему графику требуется еще несколько шагов, поэтому во многих случаях гораздо быстрее будет создать новую комбинированную диаграмму с нуля, как описано выше.

Но если вы уже потратили довольно много времени на разработку графика, вам не захочется выполнять одну и ту же работу дважды. В этом случае следуйте приведенным ниже инструкциям, чтобы добавить линию на график. На бумаге этот процесс может показаться немного сложным, но в Excel вы справитесь за пару минут.

1. Вставьте новый столбец рядом с исходными данными. Если вы хотите нарисовать среднюю линию, заполните только что добавленный столбец формулой среднего значения, описанной в предыдущем примере. Если вы добавляете контрольную строку или целевую строку, поместите целевые значения в новый столбец, как показано на снимке экрана ниже:
2. Щелкните правой кнопкой мыши существующий график и выберите «Выбрать данные…» в контекстном меню:
3. в Выберите источник данных диалоговом окне нажмите кнопку «Добавить» в Легендарные записи (серия)
4. в Редактировать серию диалоговом окне выполните следующие действия:
   • в Название сериивведите желаемое имя, скажите «Целевая линия».
   • Щелкните в Значение серии поле и выберите целевые значения без заголовка столбца.
   • Нажмите ХОРОШО дважды, чтобы закрыть оба диалоговых окна.
5. Целевая серия линий добавляется на график (оранжевые столбцы на скриншоте ниже). Щелкните его правой кнопкой мыши и выберите «Изменить тип диаграммы серии…» в контекстном меню:
6. в Изменить тип диаграммы диалоговое окно, убедитесь, Комбо > Пользовательская комбинация выбран шаблон, который должен быть по умолчанию. Для Целевая линия серия, выберите Linefrom the Тип диаграммы раскрывающийся список и нажмите ХОРОШО.

Сделанный! На график добавляется горизонтальная целевая линия:

Базовые понятия

Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:

Y(t) = a0 + a1*t + E

Y — это объем продаж, та переменная, которую мы будем объяснять временем и от которого она зависит, то есть Y(t);

t — номер периода (порядковый номер месяца), который объясняет план продаж Y;

a0 — это нулевой коэффициент регрессии, который показывает значение Y(t), при отсутствии влияния объясняющего фактора (t=0);

a1 — коэффициент регрессии, который показывает, на сколько исследуемый показатель продаж Y зависит от влияющего фактора t;

E — случайные возмущения, которые отражают влияния других неучтенных в модели факторов, кроме времени t.