Как сделать тенденцию в excel
Обновлено: 03.07.2024
При добавлении линейного тренда на график Excel, программа может отображать уравнение прямо на графике (смотри рисунок ниже). Вы можете использовать это уравнение для расчета будущих продаж. Функции FORECAST (ПРЕДСКАЗ) и TREND (ТЕНДЕНЦИЯ) дают тот же результат.
Пояснение: Excel использует метод наименьших квадратов, чтобы найти линию, которая соответствует точкам наилучшим образом. Значение R 2 равно 0.9295, что является очень хорошим значением. Чем оно ближе к 1, тем лучше линия соответствует данным.
-
Используйте уравнение для расчета будущих продаж:
Примечание: Когда мы протягиваем функцию FORECAST (ПРЕДСКАЗ) вниз, абсолютные ссылки ($B$2:$B$11 и $A$2:$A$11) остаются такими же, в то время как относительная ссылка (А12) изменяется на A13 и A14.
-
Если вам больше нравятся формулы массива, используйте функцию TREND (ТЕНДЕНЦИЯ) для расчета будущих продаж:
Примечание: Сначала выделите диапазон E12:E14. Затем введите формулу и нажмите Ctrl+Shift+Enter. Строка формул заключит ее в фигурные скобки, показывая, что это формула массива <>. Чтобы удалить формулу, выделите диапазон E12:E14 и нажмите клавишу Delete.
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Постановка задачи
Исходные данные
Для начала, давайте определимся, какие у нас есть исходные данные и что нам нужно получить на выходе. Фактически, все что у нас есть, это некоторые исторические данные. Если мы говорим о прогнозировании продаж, то историческими данными будут продажи за предыдущие периоды.
Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.
Составляющие прогноза
Следующий шаг: давайте определимся, что нам нужно учесть при построении прогноза. Когда мы исследуем наши данные, нам необходимо учесть следующие факторы:
- Изменение нашей пронозируемой величины (например, продаж) подчиняется некоторому закону. Другими словами, в временном ряде можно проследить некую тенденцию. В математике такая тенденция называется трендом.
- Изменение значений в временном ряде может зависить от промежутка времени. Другими словами, при построении модели необходимо будет учесть коэффициент сезонности. Например, продажи арбузов в январе и августе не могут быть одинаковыми, т.к. это сезонный продукт и летом продажи значительно выше.
- Изменение значений в временном ряде периодически повторяется, т.е. наблюдается некоторая цикличность.
Эти три пункта в совокупность образуют регулярную составляющую временного ряда.
Примечание. Не обязательно все три элемента регулярной составляющей должны присутствовать в временном ряде.
Однако, помимо регулярной составляющей, в временном ряде присутствует еще некоторое случайное отклонение. Интуитивно это понятно — продажи могут зависеть от многих факторов, некоторые из которых могут быть случайными.
Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.
Виды моделей
Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”
Обычно выделяют два основных вида:
- Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
- Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения
Иногда также выделают смешанную модель в отдельную группу:
- Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения
Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.
Решение задачи с помощью Excel
Итак, необходимые теоретические знания мы с вами получили, пришло время применить их на практике. Мы будем с вами использовать классическую аддитивную модель для построения прогноза. Однако, мы построим с вами два прогноза:
- с использованием линейного тренда
- с использованием полиномиального тренда
Во всех руководствах, как правило, разбирается только линейный тренд, поэтому полиномиальная модель будет крайне полезна для вас и вашей работы!
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Модель с линейным трендом
Пусть у нас есть исходная информация по продажам за 2 года:
Учитывая, что мы используем линейный тренд, то нам необходимо найти коэффициенты уравнения
- y — значения продаж
- x — номер периода
- a — коэффициент наклона прямой тренда
- b — свободный член тренда
Рассчитать коэффициенты данного уравнения можно с помощью формулы массива и функции ЛИНЕЙН. Нам необходимо будет сделать следующую последовательность действий:
- Выделяем две ячейки рядом
- Ставим курсор в поле формул и вводим формулу =ЛИНЕЙН(C4:C27;B4:B27)
- Нажимаем Ctrl+Shift+Enter, чтобы активировать формулу массива
На выходе мы получили 2 числа: первое — коэффициент a, второе — свободный член b.
Теперь нам нужно рассчитать для каждого периода значение линейного тренда. Сделать это крайне просто — достаточно в полученное уравнение подставить известные номера периодов. Например, в нашем случае, мы прописываем формулу =B4*$F$4+$G$4 в ячейке I4 и протягиваем ее вниз по всем периодам.
Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Можем сделать следующим образом: в ячейке J4 прописываем формулу =(C4+C16)/СРЗНАЧ($C$4:$C$27)/2 и протягиваем вниз на 12 месяцев (т.е. до J15).
Что нам это дало? Мы посчитали, сколько суммарно продавалось каждый январь/каждый февраль и так далее, а потом разделили это на среднее значение продаж за все два периода.
То есть мы выяснили, как продажи двух январей отклонялись от средних продаж за два года, как продажи двух февралей отклонялись и так далее. Это и дает нам коэффициент сезонности. В конце формулы делим на 2, т.к. в расчете фигурировало 2 периода.
Примечание. Рассчитали только 12 коэффициентов, т.к. один коэффициент учитывает продажи сразу за 2 аналогичных периода.
Итак, теперь мы на финишной прямой. Нам осталось рассчитать тренд для будущих периодов и учесть коэффициент сезонности для них. Давайте амбициозно построим прогноз на год вперед.
Сначала создаем столбец, в котором прописываем номера будущих периодов. В нашем случае нумерация начинается с 25 периода.
Далее, для расчета значения тренда просто прописываем уже известную нам формулу =L4*$F$4+$G$4 и протягиваем вниз на все 12 прогнозируемых периодов.
И последний штрих — умножаем полученное значение на коэффициент сезонности. Вуаля, это и есть итоговый ответ в данной модели!
Модель с полиномиальным трендом
Конструкция, которую мы только что с вами построили, достаточно проста. Но у нее есть один большой минус — далеко не всегда она дает достоверные результаты.
Посмотрите сами, какая модель более точно аппроксимирует наши точки — линейный тренд (прямая зеленая линия) или полиномиальный тренд (красная кривая)? Ответ очевиден. Поэтому сейчас мы с вами и разберем, как построить полиномиальную модель в Excel.
Пусть все исходные данные у нас будут такими же. Для простоты модели будем учитывать только тренд, без сезонной составляющей.
Для начала давайте определимся, чем полиномиальный тренд отличается от обычного линейного. Правильно — формой уравнения. У линейного тренда мы разбирали обычный график прямой:
У полиномиального тренда же уравнение выглядит иначе:
где конечная степень определяется степенью полинома.
Т.е. для полинома 4 степени необходимо найти коэффициенты уравнения:
Согласитесь, выглядит немного страшно. Однако, ничего страшного нет, и мы с легкостью можем решить эту задачку с помощью уже известных нам методов.
Кстати говоря, мы можем легко сами себя проверить. Давайте построим график наших продаж и добавим к нему полиномиальный тренд.
Теперь вы наглядно можете видеть, как рассчитанный тренд аппроксимирует исходные данные и как выглядит само уравнение. Можно сравнить уравнение на графике с вашими коэффициентами. Сходится? Значит сделали все верно!
Помимо всего прочего, вы можете сразу оценить точность аппроксимации (не полностью, но хотя бы первично). Это делается с помощью коэффициента R^2. Тут у вас снова есть два пути:
Заключение
Мы с вами подробно разобрали вопрос прогнозирования — изучили необходимые термины и виды моделей, построили аддитивную модель в Excel с использованием линейного и полиномиального тренда, а также научились отображать результаты своих вычислений на графиках. Все это позволит вам эффективно внедрять полученные знания на работе, усложнять существующие модели и уточнять прогнозы. Чем большим количеством методов и инструментов вы будете владеть, тем выше будет ваш профессиональный уровень и статус на рынке труда.
Автор: Алексанян Андрон, эксперт SF Education
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Функция ТЕНДЕНЦИЯ - это статистическая функция, которая использует метод наименьших квадратов для сопоставления точки данных в линейном тренде, для которого она возвращает одно или несколько чисел. По сути, он предсказывает значение y с учетом исторических значений x и y. Более подробно этот процесс объясняется в следующем разделе.
Метод наименьших квадратов используется в регрессионном анализе для поиска наиболее подходящей линии. Итак, этот метод рисует линию, чтобы показать взаимосвязь между точками для данного набора данных.
Как работает функция ТЕНДЕНЦИЯ
Функцию ТЕНДЕНЦИЯ необходимо ввести в ячейку как формулу. Чтобы войти в функцию ТЕНДЕНЦИЯ, выполните следующие действия с подробной информацией о каждой переменной:
- Щелкните ячейку, появится первое прогнозируемое значение..
- Введите "= ТЕНДЕНЦИЯ (" в ячейку без кавычек.
- После открытой круглой скобки необходимо добавить известный массив значений y, за которым следует запятая. При выборе этого массива имейте в виду, что когда массив данных выбирается как столбец или строка, переменные переменных x интерпретируются отдельно. Эти значения будут историческими данными, которые у вас уже есть.
- Известный массив значений x добавляется с запятой. Эти переменные известны в линейном уравнении y = mx + b. Известные x могут быть одним набором или несколькими переменными. Это зависит от того, сколько у вас знает переменных y. Например, если я пытался спрогнозировать свое будущее потребление газа на основе прошлых дней, день 1, день 2 и день 3 могут быть моими известными переменными x. Убедитесь, что размер массива известных значений y такой же, как и размер известных значений x. Когда в функции используется более одной переменной, известные переменные y должны быть вектором (высота и ширина выбранных диапазонов должны совпадать).
- Новые значения x добавляются с запятой. Новые значения x не обязательны. Они представляют собой последовательность новых значений. Итак, глядя на пример на последнем шаге, новое значение x будет днем 4, днем 5, днем 6 и так далее. Я рекомендую использовать эту переменную для более точного ответа, потому что при опущении новые x считаются такими же, как известные x. Вы заметите, что ваши данные тренда могут быть недооценены, если вы планируете прогнозировать, а эта переменная не используется. При использовании количество прогнозируемых y будет зависеть от того, сколько новых переменных x выбрано.
- Наконец, выбирается постоянное значение, за которым следует закрывающая скобка. Константа также является необязательной переменной. Это логическое значение указывает, должно ли b в уравнении равняться нулю или нет. Есть 3 разных подхода к этой логической настройке:
- Установите для константы значение ИСТИНА, чтобы нормально вычислить y = mx + b.
- Опустите настройку для обычного вычисления y = mx + b.
- Установите для константы значение FALSE, чтобы b было равно нулю. В этом случае значения m будут скорректированы так, что y = mx.
Синтаксис функции ТЕНДЕНЦИЯ полностью показан ниже:
= ТЕНДЕНЦИЯ (известные_y, [известные_x], [новые_x], [const])
Вставка функции тренда
Функцию ТЕНДЕНЦИЯ можно добавить другим способом, вставив ее в ячейку из списка статистических функций. Используя этот метод, Excel проведет вас через создание функции, позволяя вводить функциональные переменные в поля с пояснениями для каждой записи переменной. Этот метод подходит для этого типа функций, так как используются несколько переменных, которые могут смешаться при создании формулы вручную путем ввода ее в ячейку. Чтобы использовать этот метод типа вставки:
Каждый шаг в этом процессе показан на иллюстрации ниже.
Выбор функции тренда
Затем появляется окно аргументов функции, в которое можно добавить каждую из переменных функции. Массивы можно ввести или выбрать из электронной таблицы, щелкнув стрелку справа от полей массива. Можно ввести постоянное значение (ИСТИНА или ЛОЖЬ) или выбрать ссылку на ячейку, содержащую значение, щелкнув стрелку справа от этого поля. Помните, что постоянное значение не является обязательным и не требуется для общего прогноза тренда. По завершении нажмите кнопку ОК.
Окно функциональных аргументов
Пример прогнозирования годового тренда продаж Amazon
В этом примере функция Trend используется для прогнозирования годовых продаж Amazon Inc. для следующих 4 последовательных 10 000 отчетов о доходах. Сюда входят период с 2020 по 2024 год с результатами тренда, основанными на исторических данных о продажах. См. Необработанные данные в таблице ниже.
Исторические данные о продажах Amazon Inc.
Ниже я подключил все эти значения данных в Excel. Опять же, моя цель - спрогнозировать общие продажи Amazon в следующие несколько лет на основе их истории продаж. Вместо вставки из статистической опции я буду использовать инструмент вставки для поиска функции ТЕНДЕНЦИЯ.
Данные о продажах Amazon Inc. перенесены в Excel
Затем я вставляю все свои диапазоны. Первым будет массив известных значений y. Это уже известные цифры продаж. Итак, я могу выбрать стрелку справа от известного поля y, а затем выбрать массив.
Пришло время выбрать известные x. Это будут годы, соответствующие только что отобранным показателям продаж. Наконец, выбираются неизвестные x. Неизвестные x - это годы, для которых я хочу вернуть прогнозируемые значения y.
После нажатия на кнопку OK каждое из предсказанных значений y предсказывается. Помните, что для ввода функции необходимо использовать только одну ячейку, а остальные значения станут на свои места в зависимости от того, сколько неизвестных значений x выбрано.
Ошибка разлива
Если в результате вы получите ошибку разлива, убедитесь, что в ячейках, где должны появиться новые значения y, нет данных. Например, если вы ищете тенденции на период с 2021 по 2025 год, формула будет помещена там, где появится новое значение y для 2021 года, в то время как другие годы должны остаться пустыми.
Если вы все еще получаете ошибки, я бы очистил эти ячейки или даже попытался ввести ваши данные еще несколько раз.
Рекомендации
Статьи по Теме
Чтобы узнать больше об использовании функций в Excel, я рекомендую следующую книгу. Я использую Библию Excel в течение многих лет, чтобы лучше понять все аспекты этого продукта Microsoft.
Глядя на любой набор данных распределенных во времени (динамический ряд), мы можем визуально определить падения и подъемы показателей, которые он содержит. Закономерность подъемов и падений называется трендом, который может говорить о том, увеличиваются или уменьшаются наши данные.
Пожалуй, цикл статей о прогнозировании я начну с самого простого — построении функции тренда. Для примера возьмем данные о продажах и построим модель, которая опишет зависимость продаж от времени.
Базовые понятия
Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:
Y — это объем продаж, та переменная, которую мы будем объяснять временем и от которого она зависит, то есть Y(t);
t — номер периода (порядковый номер месяца), который объясняет план продаж Y;
a0 — это нулевой коэффициент регрессии, который показывает значение Y(t), при отсутствии влияния объясняющего фактора (t=0);
a1 — коэффициент регрессии, который показывает, на сколько исследуемый показатель продаж Y зависит от влияющего фактора t;
E — случайные возмущения, которые отражают влияния других неучтенных в модели факторов, кроме времени t.
Построение модели
Итак, мы знаем объем продаж за прошедшие 9 месяцев. Вот, что из себя представляет наша табличка:
Следующее, что мы должны сделать — это определить коэффициенты a0 и a1 для прогнозирования объема продаж за 10-ый месяц.
Определение коэффициентов модели
Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:
В Google Sheets выбираем Редактор диаграмм -> Дополнительные и ставим галочку возле Линии тренда. В настройках выбираем Ярлык — Уравнение и Показать R^2.
Вот, что получилось:
На графике мы видим уравнение функции:
y = 4856*x + 105104
Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.
У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.
Прогнозируем
Чтобы рассчитать продажи за 10-ый месяц, подставляем в функцию тренда 10 вместо x. То есть,
y = 4856*10 + 105104
Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.
Таким образом вы можете спрогнозировать данные на несколько месяцев вперед, но без учета других факторов ваш прогноз будет лежать на линии тренда и будет не таким информативным как хотелось бы. К тому же, долгосрочный прогноз, сделанный таким способом будет очень приблизительным.
Повысить точность модели можно добавлением сезонности к функции тренда, что мы и сделаем в следующей статье.
Екатерина Шипова
Магистр прикладной математики и информатики, веб-аналитик. Сертифицированный специалист Google Аnalytics и Яндекс.Метрика.
Читайте также: