Как сделать тенденцию в excel?

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

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

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

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

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

Как сделать тенденцию в excel?

(Рисунок 1)

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

Как сделать тенденцию в excel?

(Рисунок 2)

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

  1. Для начала кликнем левой кнопкой мыши по границе графика, чтобы
    выделить его.
  2. Затем перейдем на закладку «Конструктор» и выберем «Макет
    1».
  3. Далее печатаем текст заголовка.

Как сделать тенденцию в excel?

(Рисунок 3)

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

Как сделать тенденцию в excel?

(Рисунок 4)

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

Как сделать тенденцию в excel?

  • (Рисунок 5)
  • Так мы построили первичную Линию тренда, которая может мало
    соответствовать действительности.
  • Это наш промежуточный результат.

Как сделать тенденцию в excel?

(Рисунок 6)

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

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

6. Нажимаем на кнопку «Линия тренда» и выбираем «Дополнительные
параметры и линии тренда».

Как сделать тенденцию в excel?

(Рисунок 7)

7. В окне «Формат линии тренда», мы ставим флажок напротив
«поместить на диаграмму величину достоверности аппроксимации R^2 и
нажимаем кнопку «закрыть».

Видим на диаграмме коэффициент R^2= 0,6442

Как сделать тенденцию в excel?

(Рисунок 8)

8. Отменяем изменения. Выделяем график, нажимаем на закладку
«Макет», кнопку «линия тренда» и выбираем «Нет».

9. Переходим в окно «Формат линии тренда», но уже для того,
чтобы выбрать «Полиноминальную» линию тренда, меняем степень,
добиваясь показателей коэффициента R^2= 0,8321

Как сделать тенденцию в excel?

(Рисунок 9)

Прогноз

Если нам нужно предположить, какие данные могли бы быть получены
в следующем измерении, в окне «Формат линии тренда», указываем
количество периодов на которые делается прогноз.

Как сделать тенденцию в excel?

(Рисунок 10)

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

Вывод

  1. И в заключение если Вам интересна формула по которой построен
    тренд, в коне «Формат линии тренда» поставьте флажок напротив
    «показать уравнение на диаграмме».
  2. Теперь Вы знаете, как выполнить задание и построить линию
    тренда, даже в такой программе как excel 2010.
  3. Задавайте вопросы, не стесняйтесь.
  4. Сергей Титов

Источник: https://www.tech-office2010.ru/page/kak-postroit-liniju-trenda-v-excel-2010

Archie Goodwin

Знакомство с понятием тренд (тенденция) и примеры построения модели линейного тренда в Excel и прогнозирования на основе этой модели, как часть анализа динамического ряда.

Тренд — это закономерность описывающая подъем или падение показателя в динамике.

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

Как сделать тенденцию в excel?

Тренд как модель

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

Итак, что же собой представляет тренд как модель? Это совокупность расчетных коэффициентов уравнения, которые выражают регрессионную зависимость показателя (Y) от изменения времени (t). То есть, это точно такая же регрессия, как и те, что мы рассматривали ранее, только влияющим фактором здесь выступает именно показатель времени.

Модель линейного тренда

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

Общий вид уравнения линейного тренда:

Где a0 – это нулевой коэффициент регрессии, то есть, то каким будет Y в случае, если влияющий фактор будет равен нулю, a1 – коэффициент регрессии, который выражает степень зависимости исследуемого показателя Y от влияющего фактора t, Ɛ – случайная компонента или стандартная ошибка, по сути являет собой разницу между реально существующими значениями Y и расчетными. t – это единственный влияющий фактор – время.

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

Рассчитать коэффициенты модели можно стандартным Методом наименьших квадратов (МНК). Со всеми этими расчетами Microsoft Excel справляется на ура самостоятельно, при чем, чтобы получить модель линейного тренда либо готовый прогноз существует целых пять способов, которые мы по отдельности разберем ниже.

Графический способ получения линейного тренда

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

Как сделать тенденцию в excel?

Добавим к исходным данным еще один столбец, который назовем t и пометим цифрами по возрастающей порядковые номера всех зафиксированных значений ВВП за указанный период с 2004-го по 2012-й гг. – 9 лет или 9 периодов.

Далее на основе этих исходных данных построим точечную диаграмму: вкладка в меню – Вставка, подраздел Диаграммы Точечная, Точечная с гладкими кривыми маркерами.

Как сделать тенденцию в excel?

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

В качестве значения поля Имя ряда выбираем ячейку, которая содержит текст, наиболее полно отвечающий названию графика. В поле Значения X указываем интервал ячеек стобца t – влияющего фактора.

В поле Значения Y указываем интервал ячеек столбца с известными значениями ВВП (Y) – исследуемого показателя.

Как сделать тенденцию в excel?

Заполнив указанные поля, несколько раз нажимаем кнопку ОК и получаем готовый график динамики. Теперь выделяем правой кнопкой мыши саму линию графика и из появившегося контекстного меню выбираем пункт Добавить линию тренда

Как сделать тенденцию в excel?

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

Как сделать тенденцию в excel?

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

Построение линейного тренда с помощью формулы ЛИНЕЙН

Как сделать тенденцию в excel?

Суть этого метода сводится к поиску коэффициентов линейного тренда с помощью функции ЛИНЕЙН, затем, подставляя эти влияющие коэффициенты в уравнение, получим прогнозную модель.

Нам потребуется выделить две рядом стоящие ячейки (на скриншоте это ячейки A38 и B38), далее в строке формул вверху (выделено красным на скриншоте выше) вызываем функцию, написав «=ЛИНЕЙН(», после чего эксель выведет подсказки того, что требуется для этой функции, а именно:

  1. выделяем диапазон с известными значениями описываемого показателя Y (в нашем случае ВВП, на скриншоте диапазон выделен синим) и ставим точку с запятой
  2. указываем диапазон влияющих факторов X (в нашем случае это показатель t, порядковый номер периодов, на скриншоте выделено зеленым) и ставим точку с запятой
  3. следующий по порядку требуемый параметр для функции – это определение того нужно ли рассчитывать константу, так как мы изначально рассматриваем модель с константой (коэффициент a0), то ставим либо «ИСТИНА» либо «1» и точку с запятой
  4. далее нужно указать требуется ли расчет параметров статистики (в случае, если бы мы рассматривали этот вариант, то изначально пришлось бы выделить диапазон «под формулу» на несколько строк ниже). Указывать необходимость расчета параметров статистики, а именно стандартного значение ошибки для коэффициентов, коэффициента детерминированности, стандартной ошибки для Y, критерия Фишера, степеней свободы и пр., есть смысл только тогда, когда вы понимаете, что они означают, в этом случае ставим либо «ИСТИНА», либо «1». В случае упрощенного моделирования, которому мы пытаемся научиться, на этом этапе прописывания формулы, ставим «ЛОЖЬ» либо «0» и добавляем после закрывающую скобочку «)»
  5. чтобы «оживить» формулу, то есть заставить ее работать после прописывания всех необходимых параметров, не достаточно нажать кнопку Enter, необходимо последовательно зажать три клавиши: Ctrl, Shift, Enter

Как сделать тенденцию в excel?

  • Как видим на скриншоте выше, выделенные нами под формулу ячейки заполнились расчетными значениями коэффициентов регрессии для линейного тренда, в ячейке B38 находится коэффициент a0, а в ячейке A38 — коэффициент зависимости от параметра t (или x), то есть a1. Подставляем полученные значения в уравнение линейной функции и получаем готовую модель в математическом выражении – y = 169 572,2+138 454,3*t
  • Чтобы получить расчетные значения Y по модели и, соответственно, чтобы получить прогноз, нужно просто подставить формулу в ячейку экселя, а вместо t указать ссылку на ячейку с требуемым номером периода (смотрите на скриншоте ячейку D25).
  • Для сравнения полученной модели с реальными данными, можно построить два графика, где в качестве Х указать порядковый номер периода, а в качестве Y в одном случае – реальный ВВП, а, в другом – расчетный (на скриншоте диаграмма справа).

Построение линейного тренда с помощью инструмента Регрессия в Пакете анализа

В статье Линейная регрессия в Excel через Анализ данных, по сути, полностью описан этот метод, единственная же разница в том, что в наших исходных данных только один влияющий фактор Х (номер периода – t).

Читайте также:  Как сделать зависимые таблицы в excel?

Как сделать тенденцию в excel?

Как видно на рисунке выше, диапазон данных с известными значениями ВВП выделен как входной интервал Y, а соответствующий ему диапазон с номерами периодов t – как входной интервал Х. Итоги расчетов Пакетом анализа выносятся на отдельный лист и выглядит как набор таблиц (см.

рисунок ниже) на котором нас интересуют ячейки, которые были закрашены мною в желтый и зеленый цвета. По аналогии с порядком, расписанным в указанной выше статье, из полученных коэффициентов собирается модель линейного тренда y=169 572,2+138 454,3*t, на основе которой и делаются прогнозы.

Как сделать тенденцию в excel?

Прогнозирование с помощью линейного тренда через функцию ТЕНДЕНЦИЯ

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

  1. В целевую ячейку (ту ячейку, где хотим видеть результат) ставим знак равно и вызываем волшебную функцию, прописав «ТЕНДЕНЦИЯ(», далее необходимо выделить диапазон известных значений Y, то есть столбец с известными значениями ВВП, после ставим точку с запятой и выделяем диапазон с известными значениями Х, то есть с номерами периодов t, которые соответствуют столбцу с известными значениями ВВП, опять ставим точку с запятой и выделяем ячейку с номером периода, для которого мы делаем прогноз (правда, в нашем случае, номер периода можно указать не ссылкой на ячейку, а просто цифрой прямо в формуле), далее ставим еще одну точку с запятой и указываем ИСТИНА или 1, в качестве подтверждения для расчета коэффициента a0, наконец, ставим закрывающую скобочку и нажимаем клавишу Enter.
  2. Минус данного метода в том, что он не показывает ни уравнения модели, ни его коэффициентов, из-за чего нельзя сказать, что на основе такой-то модели мы получили такой-то прогноз, также как и нет какого-либо отражения параметров качества модели, того таки коэффициента детерминации, по которому можно было бы сказать имеет ли смысл брать во внимание полученный прогноз или нет.

Прогнозирование с помощью линейного тренда через функцию ПРЕДСКАЗ

Суть данной функции целиком и полностью идентична предыдущей, разница лишь в порядке прописывания исходных данных в формуле и в том, что нет настройки для наличия или отсутствия коэффициента a0 (то есть функция подразумевает, что этот коэффициент, в любом случае, есть)

  • Как видно с рисунка выше, в целевую ячейку прописываем «=ПРЕДСКАЗ(» и затем указываем ячейку с номером периода, для которого необходимо просчитать значение по линейному тренду, то есть прогноз, после ставим точку с запятой, далее выделяем диапазон известных значений Y, то есть столбец с известными значениями ВВП, после ставим точку с запятой и выделяем диапазон с известными значениями Х, то есть с номерами периодов t, которые соответствуют столбцу с известными значениями ВВП и, наконец, ставим закрывающую скобочку и жмем клавишу Enter.
  • Полученные результаты, как и в методе выше, это лишь готовый результат расчета прогнозного значения по линейной трендовой модели, он не выдает ни погрешностей, ни самой модели в математическом выражении.

Подводя итог к статье

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

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

В свою очередь, последние два метода также идентичны между собой и максимально быстро вам дадут ответ, например, на вопрос: «Какой прогноз продаж на следующий год?».

Источник: http://archie-goodwin.net/load/specializirovannye_blogi/ms_office/linejnyj_trend_v_excel/28-1-0-429

Прогноз продаж в Excel

Хитрости » 23 Март 2017       Дмитрий       72054 просмотров

  • Скачать файл, используемый в видеоуроке:
  •   Прогноз_продаж.xls (59,5 KiB, 16 541 скачиваний)
  • А в довершение мы построим красивый график с прогнозом.

Прогнозирование продаж является неотъемлемой частью при планировании работы коммерческих и финансовых служб, поэтому задача довольно актуальная. Вариантов построения прогнозов достаточное множество, но я хочу показать как сделать простой, но в то же время достаточно жизнеспособный прогноз «на скорую руку», без лишних телодвижений и поправок «на ветер»(читайте как: без кучи доп.расчетов, которые применяются для создания более точных прогнозов). Почему я это уточняю? Потому что на мой взгляд, каким бы точным ни был прогноз продаж – это всего лишь предположение и быть уверенным в том, что именно так и будет развиваться ход событий, никак нельзя.
И тем не менее при помощи встроенных в Excel функций мы можем построить довольно неплохой прогноз даже с учетом сезонности. Плюс я хочу показать как сделать не просто прогноз, а прогноз с отклонениями – пессимистичный и оптимистичный. С помощью подобной модели можно будет выстроить тактику продаж таким образом, чтобы постараться максимально «вписаться» в границы между пессимистичным и оптимистичным прогнозом. Исходные данные
Для расчета прогноза потребуются данные о продажах за ранние периоды. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года. На мой взгляд это тот минимум, на основании которого можно построить весьма точный прогноз с учетом прошлого опыта. Именно из таких данных и будем исходить. Предположим, что у нас есть данные с января 2013 года по август 2015, в табличном виде:Как сделать тенденцию в excel?
Нам необходимо рассчитать прогноз продаж на будущий год: с сентября 2015 по август 2016 и отразить это на графике. Я специально беру рваный период посреди года, чтобы показать, что начало прогноза может быть с любой даты.

Чтобы дальше в статье не запутать вас столбцами и где они должны быть добавлены, сразу приведу конечную структуру:
Как сделать тенденцию в excel?
Т.е. у нас должно быть именно в указанном порядке 7 столбцов: Период; Продажи компании, руб.; Прогноз; Оптимистичный; Пессимистичный; Коэффициент сезонности; Отклонение. И чтобы все получилось они должны идти точно в таком же порядке, как на картинке выше.

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

  Прогноз_продаж.xls (59,5 KiB, 16 541 скачиваний)

В файле два листа:

  • Исходные данные — только фактические данные по продажам, без доп.столбцов, чтобы можно было самостоятельно с нуля построить модель
  • Прогноз — лист с готовыми функциями и графиком прогноза

В самый низ таблицы, после последней фактической даты, я добавил даты, на которые необходимо построить прогноз(от сен.2015 до авг.2016).

Расчет прогноза
Для расчета непосредственно прогноза в Excel есть специальная функция, которая основываясь на данных предыдущих периодов предсказывает вероятные значения для указанной даты. Она так и называется – ПРЕДСКАЗ(FORECAST). Функция основана на линейной регрессии и специально предназначена именно для прогнозирования продаж, потребления товара и пр. В столбец Прогноз (столбец C – сразу после столбца с суммами продаж) в ячейку C34 записываем функцию (и распространяем на все прогнозируемые даты – C34:C45):
=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)
Сама функция требует указания следующих входных данных:

  • х — Дата, значение для которой необходимо спрогонозировать (A34)
  • Известные значения y — ссылка на ячейки таблицы с суммами продаж за известные периоды ($B$2:$B$33)
  • Известные значения x — ссылка на ячейки таблицы с дата продаж за известные периоды ($A$2:$A$33)

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

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

Для этого добавим в столбец Коэффициент сезонности следующую формулу:
=(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12
=(($B$2:$B$13+$B$14:$B$25)/SUM($B$2:$B$25))*12
Формула вводится в ячейку как формула массива и сразу в 12 ячеек(чтобы получить коэффициенты для каждого месяца года).

Для этого сначала выделяем ячейки F2:F13 -переходим в строку формул и вводим формулу выше. После указания верных ссылок на нужные ячейки завершаем ввод формулы одновременным нажатием трех клавиш: Ctrl+Shift+Enter. Если этого не сделать, то функция вернет значение ошибки #ЗНАЧ!(#VALUE!)

Подробнее про принцип работы формулы: она берет отдельно сумму каждого месяца за 2013 и 2014 год, складывает их. Делит полученное значение на общую сумму продаж за весь период целых месяцев(т.е.

24 месяца) и умножает на 12, чтобы получить коэффициент именно за один месяц. И так для каждого месяца. Т.е. для ячейки F2 расчет будет выглядеть следующим образом:
=((56 769+68 521)/ 1 542 293)*12
=((сумма за янв.2013 + сумма за янв.

2014)/ общая сумма за два года(янв.2013 – дек.2014))*12

В результате для января получим коэффициент 0,974834224106574, для февраля — 0,989928632237843 и т.д.

Я для наглядности назначил ячейкам процентный формат(правая кнопка мыши —Формат ячеек -вкладка ЧислоПроцентный(Format cellsNumberPercent), два знака после запятой):

Теперь добавим учет этих коэффициентов для расчета прогноза в имеющуюся функцию ПРЕДСКАЗ(ячейки C34:C45):
=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)*ИНДЕКС($F$2:$F$13;МЕСЯЦ(A34))
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)*INDEX($F$2:$F$13,MONTH(A34))
Здесь применяется функция ИНДЕКС(INDEX), в которой первым аргументом указываем ссылку на 12 ячеек с коэффициентами сезонности($F$2:$F$13), а вторым – номер месяца, чтобы вернуть коэффициент именно для нужного месяца(для этого используем функцию МЕСЯЦ(MONTH), которая возвращает только номер месяца из указанной даты). Для сентября 2015 это будет выглядеть так:
=ПРЕДСКАЗ(A34; $B$2:$B$33; $A$2:$A$33)*ИНДЕКС({97,48%:98,99%:90,38%:94,66%:100,86%:99,02%:100,66%:110,39%:100,47%:104,82%:105,13%:97,14%}; 9)

Основную задачу выполнили – у нас есть прогноз на будущие периоды. Теперь осталось в дополнение к самому прогнозу, создать допустимые верхние и нижние границы, которые часто еще называют оптимистичный прогноз и пессимистичный(но по сути это просто возможное отклонение от прогнозных данных). Такой прогноз даст нам возможность более гибко планировать тактику на будущие периоды.

Читайте также:  Как сделать рубли в Excel?

Для того, чтобы построить такие прогнозы необходимо рассчитать допустимое отклонение от прогнозируемых значений. Здесь так же будем использовать имеющиеся в Excel функции. В ячейку G2 запишем формулу:
=ДОВЕРИТ(0,05; СТАНДОТКЛОН(C34:C45); СЧЁТ(C34:C45))
=CONFIDENCE(0.

05,STDEV(C34:C45),COUNT(C34:C45))
ДОВЕРИТ(CONFIDENCE) – возвращает доверительный интервал, используя нормальное распределение.

  • алфа – уровень значимости для вычисления доверительного уровня. Используемое в формуле 0,05 означает доверительный уровень в 95%. В большинстве случаев это оптимальное значение
  • станд_откл – стандартное отклонение генеральной совокупности. Должно быть известно. Но т.к. мы этими данными не располагаем – то это значение вычисляем при помощи функции СТАНДОТКЛОН(STDEV), передавая ей для расчетов спрогнозированные данные
  • размер – указывается целое число, обозначающее количество данных для выборки. Как правило равно количеству спрогнозированных данных. У нас количество определяется функцией СЧЁТ, которая подсчитывает количество чисел в указанных ячейках.

Теперь в ячейки столбцов Оптимистичный и Пессимистичный(D и E), начиная со строки 34, запишем такие формулы:
Оптимистичный: =$C34+$G$2
Пессимистичный: =$C34-$G$2
Как сделать тенденцию в excel?

Т.е. мы для оптимистичного прогноза берем сумму прогноза и прибавляем к ней сумму рассчитанного отклонения. А для пессимистичного, мы сумму отклонения вычитаем. Вот мы и получили все необходимые данные.

График
Но было бы кощунством с нашей стороны проделать такую работу и не использовать возможности Excel для построения красивого графика. Придется добавить немного шаманства(на деле, мы уже начали шаманить, когда стали записывать прогноз в отдельный столбец, а не продолжать его в том же столбце, что и фактические продажи). В ячейки C33, D33 и E33 скопируем значение из ячейки B33, чтобы они все имели одинаковые значения:Как сделать тенденцию в excel?

Теперь выделяем все данные (A1:E45), переходим на вкладку Вставка(Insert) – группа Диаграммы(Charts)График(Line). И получим такую картину:
Как сделать тенденцию в excel?

Наглядно и сразу понятно что к чему и чего можно ожидать.

  • Синим – фактические продажи
  • Оранжевый – прогноз
  • Серый – Оптимистичный прогноз
  • Желтый – Пессимистичный

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

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

Как сделать тенденцию в excel?
Выделяем необходимые данные из двух столбцов -переходим на вкладку Данные(Data) -группа Прогноз(Forecast)Лист прогноза(Forecast Sheet):
Как сделать тенденцию в excel?
В появившемся окне раскрываем пункт Параметры(Options) и настраиваем:
Как сделать тенденцию в excel?

  • Завершение прогноза(Forecase End) – указывается дата, которой должен заканчиваться прогноз. Я советую всегда проверять эту дату, т.к. по умолчанию Excel почти всегда выставляет некую среднюю дату, которая отличается от необходимой.
  • Начало прогноза(Forecase Start) – указывается дата, с которой необходимо начать строить прогноз. Как правило это последняя дата фактических данных. Если указать дату, которая будет раньше последней даты фактических данных, то для построения прогноза будут использоваться данные только ДО этой даты (так же это называется «ретроспективным прогнозированием»).
  • Доверительный интервал(Confidence interval) – этот пункт поможет понять, насколько точно построен прогноз. Чем больше будет доверительный интервал, тем меньше точность прогноза и чем меньше доверительный интервал – тем выше точность прогноза. Что вполне логично. По умолчанию определяется для 95% точек, хотя его можно изменить в соответствующем поле. Если интервал создавать не нужно – снять галочку.
  • Сезонность(Seasonality) – как понятно из названия, отвечает за определение фактора сезонности. Лучше оставлять автоматическим, при котором сезонность определяется на основании всех точек месяцев(т.е. 12). Но если этот фактор необходимо рассчитывать из иного количества точек, то необходимо выбрать Установка вручную и указать нужное количество точек. Но следует учитывать, что если точек будет недостаточно – то прогноз может быть очень неточным и график в итоге будет иметь вид, далекий от ожидаемого.
  • Диапазон временной шкалы(Timeline Range) – указывается диапазон значений с датами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон значений.
  • Диапазон значений(Values Range) – указывается диапазон значений с суммами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон временной шкалы.
  • Заполнить отсутствующие точки с помощью(Fill Missing Poins Using) – если каких-то данных не хватает(например, имеются пропуски в ячейках с суммами), то можно выбрать чем эти данные заполнить. По умолчанию используется интерполяция. Это означает, что отсутствующие данные вычисляется как взвешенное среднее соседних ячеек, если отсутствует менее 30 % точек. Если необходимо заполнять отсутствующие точки нулями, то необходимо выбрать из выпадающего списка пункт Нули.
  • Объединить дубликаты с помощью(Aggregate Duplicates Using) – если в фактических данных есть повторяющиеся даты, то Excel объединит их в одну точку с этой датой, а в качестве суммы подставит среднее арифметическое для этой даты. Это оптимальный вариант, но так же допускается выбрать из списка и другую функцию: Количество, СЧЁТЗ, Максимум, Медиана, Минимум, Сумма.
  • Включить статистические данные прогноза(Include Forecast Statistics) – при включении данного пункта на листе с таблицей графика правее основных данных будет создана таблица с дополнительной статистической информации о прогнозе. В таблице при помощи функции ПРЕДСКАЗ.ЕTS.СТАТ будут рассчитаны коэффициенты сглаживания (Альфа, Бета, Гамма), и метрики ошибок (MASE, SMAPE, MAE, RMSE).
  1. После нажатия кнопки Создать(Create) будет создан новый лист, в котором будет создана таблица со всеми необходимыми данными и формулами и готовым графиком:

    если при создании был отмечен пункт Включить статистические данные прогноза(Include Forecast Statistics), то правее таблицы основных данных будет так же создана таблица статистических данных:

  2. Скачать файл:
  3.   Прогноз_продаж.xls (59,5 KiB, 16 541 скачиваний)
  4. Так же см.:
    Как быстро подобрать оптимальный вариант решения
    Автообновляемая сводная таблица

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/prognoz-prodazh-v-excel/

Функция ТЕНДЕНЦИЯ в Excel для составления прогнозов

Для составления простых прогнозов можно использовать функцию ТЕНДЕНЦИЯ в Excel. С ее помощью рассчитывают будущие значения изучаемого показателя в соответствии с линейным трендом.

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

Но получить математическое описание и статистическую характеристику модели тренда посредством ТЕНДЕНЦИИ невозможно.

Опишем аргументы функции:

  1. Диапазон данных y. Обязательный аргумент. Массив известных значений y для уравнения y = ax + b.
  2. Диапазон значений x. Обязательный аргумент, включающий массив уже известных для соотношения y = ax + b значений x.
  3. Новые значения x. Обязательный аргумент. Диапазон переменных x, для которых необходимо рассчитать значения y.
  4. Константа. Необязательное логическое значение. Если нужно, чтобы значения тенденции рассчитывались без учета коэффициента b (соблюдалось соотношение y = ax), ставим 0.

Особенности работы функции ТЕНДЕНЦИЯ:

  1. Если диапазон известных значений y находится в одном столбце (одной строке), то каждый столбец (строка) с известными значениями x воспринимается как отдельная переменная.
  2. В массиве с известными значениями x может быть несколько переменных. Но если применяется только одна, диапазоны с известными значениями x и y должны быть соразмерны. Если используется несколько переменных, то диапазон с заданными значениями y должен вмещаться в одной строке или в одном столбце.
  3. Если диапазон с известными показателями x не указан, то функция предполагает массив 1; 2; 3; 4;…, соразмерный диапазону с заданными значениями y.
  4. Диапазон с новыми значениями x должен вмещаться в такое же количество строк или столбцов, как и диапазон с известными значениями y. То есть быть соразмерным независимым переменным.
  5. Если аргумент с новыми значениями x не указан, то функция считает его равным аргументу с известными значениями x. Если и известные показатели не заданы, то предполагается массив 1; 2; 3; 4;…, соразмерный диапазону с заданными параметрами y.
  6. Чтобы функция вернула массив, формулу нужно вводить как формулу массива.
  • Приведем примеры функции ТЕНДЕНЦИЯ.
  • Функцию ТЕНДЕНЦИЯ хорошо использовать для временного ряда, где данные увеличиваются или уменьшаются с постоянной скоростью.
  • Временной ряд товарооборота по месяцам с двумя переменными:



Как сделать тенденцию в excel?

Сначала рассчитаем значения линейного тренда с помощью графика Excel. По оси Х – номера месяцев, по оси Y – объем товарооборота.

Как сделать тенденцию в excel?

Добавим на график линию тренда и его уравнение.

Как сделать тенденцию в excel?

Для прогнозирования будущих продаж нужно рассчитать показатели линейного тренда для анализируемых данных и для будущих периодов, используя уравнение тренда y = 490,26x + 37747.

Показатели линейного тренда будем считать для каждого месяца.

Как сделать тенденцию в excel?

Получаем данные тренда и для будущих периодов: 16 – 19. Так, в 16 месяце спрогнозированное значение продаж – 45591,16 тыс. руб.

Теперь спрогнозируем товарооборот с помощью встроенной функции ТЕНДЕНЦИЯ. Вызываем «Мастер функций». В категории «Статистические» находим нужную. Заполняем аргументы:

Как сделать тенденцию в excel?

Известные значения y – диапазон с объемами продаж. Данные необходимо зафиксировать (кнопка F4), чтобы при размножении формулы массив сохранился.

Известные значения x – номера месяцев, для которых функция рассчитает данные для линейного тренда. Данные тоже фиксируем.

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

Задаем аргумент «Конст»: 1. Функция при расчете значений тренда учтет коэффициент a.

Обратите внимание: диапазоны известных значений соразмерны.

Как сделать тенденцию в excel?

Функция ТЕНДЕНЦИЯ дала нам те же прогнозные показатели на 16-19 периоды.

Источник: https://exceltable.com/funkcii-excel/funkciya-tendenciya

Построение функции тренда в Excel. Быстрый прогноз без учета сезонности

Как сделать тенденцию в excel?

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

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

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

  • Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:
  • Y(t) = a0 + a1*t + E
  • Y — это объем продаж, та переменная, которую мы будем объяснять временем и от которого она зависит, то есть Y(t);
  • t —  номер периода (порядковый номер месяца), который объясняет план продаж Y;
  • a0 — это нулевой коэффициент регрессии, который показывает значение Y(t), при отсутствии влияния объясняющего фактора (t=0);
  • a1 — коэффициент регрессии, который показывает, на сколько исследуемый показатель продаж Y зависит от влияющего фактора t;
  • E — случайные возмущения, которые отражают влияния других неучтенных в модели факторов, кроме времени t.

Построение модели

  1. Итак, мы знаем объем продаж за прошедшие 9 месяцев. Вот, что из себя представляет наша табличка:
  2. Как сделать тенденцию в excel?
  3. Следующее, что мы должны сделать — это определить коэффициенты a0 и a1 для прогнозирования объема продаж за 10-ый месяц.

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

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

Как сделать тенденцию в excel?

Источник: https://thisisdata.ru/blog/postroyeniye-funktsiy-trenda-v-excel/

Линия тренда в Excel. Виды линий тренда

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

В пакете программ от Microsoft есть замечательная возможность Excel, которая поможет создать достаточно точный прогноз с помощью этот инструмент — линия тренда в Excel.

Построить этот инструмент анализа довольно, просто, ниже приведено подробное описание процесса и видов линий тренда.

Как сделать тенденцию в excel?
Линия тренда в Excel. Процесс построения

Линия тренда — это один из основных инструментов анализа данных

Чтобы сформировать линию тренда, необхдимо совершить три этапа, а именно:
1. Создать таблицу;

2. Построить диаграмму;

3. Выбрать тип линии тренда.

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

Сперва стоит создать таблицу с исходными данными. Следом выделить необходимый диапазон и, перейдя во вкладку «Вставка», выбрать функцию «График».

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

Чтобы совершить это достаточно нажав левой кнопкой мыши по графику выбрать закладку под названием «Конструктор» и выбрать «Макет ». Следом остается просто ввести заголовок.

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

Различные вариации линии тренда

В зависимости от особенностей вводимых пользователем данных, стоит выбрать один из представленных вариантов, далее представлено описание видов линии тренда
• Экспоненциальная аппроксимация.

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

Однако если же данные, что были введены в таблицу, содержат нулевые или же отрицательные характеристики, данный вид неприемлем.

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

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

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

• Степенная аппроксимация. Характеризует монотонное возрастание и убывание величины, но применение ее невозможно, если данные имеют отрицательные и нулевые значения.

• Скользящее среднее. Используется чтобы наглядно показать прямую зависимость одного от другого, путем сглаживания всех точек колебания. Это достигается путем выделения среднего значения между двумя соседними точками. Таким образом, график усредняется, а количество точек сокращается до значения, что было выбрано в меню «Точки» пользователем.

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

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

Линия тренда в Excel. Настройка параметров функциональной линии

Нажав на кнопку «Линия тренда», выбираем необходимое меню под названием «Дополнительные параметры».

В появившемся окне следует нажать на «Формат линии тренда», а после поставить и отметку напротив значения «поместить на диаграмму величину достоверности аппроксимации R^2».

После этого закрываем меню, нажав на соответственную кнопку. На самой же диаграмме появляется коэффициент R^2= 0,6442.

После этого отменяем вводимые изменения. Выделив график и нажав на вкладку «Макет», следом нажимаем на «Линию тренда» и наживаем на «Нет». Следом, перейдя в функцию «Формат линии тренда», нажимаем на полиноминальную линию и пытаемся добиться значения R^2= 0,8321, меняя степень.

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

Поделитесь нашей статьей в ваших соцсетях:

Источник: https://excelworks.ru/2016/01/13/1715/

Прогнозирование в один клик в Excel 2016

Статья первоначально опубликована командой Excel (Excel Team) на английском языке здесь.

 «Точные расчеты делать сложно, особенно когда дело касается прогнозов на будущее», — говорил датский физик Нильс Бор. Что ж, прогнозирование в один клик в Excel значительно упрощает эту задачу.

Мы часто используем Excel для анализа данных временных рядов (например, о продажах, использовании сервера или инвентаризации), стараясь выявить повторяющиеся сезонные закономерности и тенденции. В Excel 2016 новые функции прогноза на листе и прогноз одним кликом помогут объяснить данные и понять будущие тенденции.

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

Есть множество способов создания прогноза по хронологическим данным. До появления Excel 2016 многие пользовались функцией листа FORECAST(), с помощью которой создается линейный прогноз или экстраполяция на основе продленных линий тренда в свойствах диаграммы.

В новой функции Excel 2016 используется другой алгоритм: экспоненциальное сглаживание (ETS). Это один из самых популярных способов прогнозирования, который уже стал отраслевым стандартом.

Одно из главных преимуществ метода ETS — возможность обнаруживать сезонные закономерности и доверительные интервалы.

Сезонные закономерности

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

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

 Еще один пример — почасовые данные о дорожном движении, где оптимальная сезонность представлена 24 точками (часами).

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

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

Желательно, чтобы полных сезонных циклов было хотя бы 2–3. Автоматически обнаруживаемое значение в разделе сезонности можно найти в диалоговом окне Создание прогноза в разделе Параметры.

Если сезонные данные обнаружить не удалось или сезонность известна заранее, то это значение можно переопределить, выбрав параметр Задать вручную.

  1. Доверительные интервалы
  2. Помимо прогнозирования будущих значений для введенного временного ряда, прогноз ETS также может определять доверительный интервал.

Доверительный интервал — это диапазон, окружающий каждое прогнозируемое значение, в который, согласно прогнозу (с нормальным распределением), должно попасть 95 % будущих точек.

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

Значение коэффициента доверия, по умолчанию равное 95 %, можно изменить с помощью стрелок вверх и вниз в следующих целях:

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

Как создать прогноз

Чтобы создать лист прогноза, сначала подготовьте набор данных временного ряда (с временным рядом и рядом значений). Затем на вкладке «Данные» нажмите на кнопку Лист прогноза. Запустится диалоговое окно создания прогноза с пошаговыми инструкциями.

 Подробные инструкции о том, как создать прогноз, см. на странице Создание прогноза в Excel 2016 для Windows. Второй вариант — если вы умеете пользоваться функциями листа, то можете сделать то же самое с помощью новых функций FORECAST.ETS*, описанных здесь: Справка по функциям прогнозирования.

 Функции листа обладают теми же возможностями, что и мастер прогнозов.

Как узнать, точен ли прогноз? Можно ли ему доверять?

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

Посмотрите на ширину доверительного интервала (см. выше).

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

  • В примере внизу это отмечено красным цветом: как видно, прогноз был очень близок к фактическим данным.
  • В этом случае тот же самый результат получился бы с помощью функций листа, если ввести только часть хронологического ряда, а затем сравнить прогноз с фактическими данными.
  • Если вы разбираетесь в статистике, установите флажок Включить статистические данные прогноза, чтобы отобразить сводные показатели точности.
  • Установите флажок «Включить статистические данные прогноза», чтобы отобразить таблицу статистических значений в прогнозе.

Все статистические данные вычисляются с помощью функции FORECAST.ETS.STAT() и описаны здесь.

Поделитесь своим мнением

Мы только что кратко рассказали вам о новых функциях, доступных в Excel 2016. Опробуйте их сами и поделитесь своими идеями о других функциях и улучшениях, которых недостает в Excel.

Источник: https://blogs.technet.microsoft.com/tasush/2015/11/16/excel-2016/

Ссылка на основную публикацию
Adblock
detector