Функция тенденция (trend)

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

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

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

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

(Рисунок 1)

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

(Рисунок 2)

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

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

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

(Рисунок 3)

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

(Рисунок 4)

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

(Рисунок 5)

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

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

(Рисунок 6)

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

Предупреждения

Информация о статье

Категории: Microsoft Excel

На других языках:

English: Do Trend Analysis in Excel, Español: hacer un análisis de tendencia en Excel, Italiano: Eseguire un’Analisi di Andamento del Mercato in Excel, Português: Fazer Análise de Tendências Utilizando o Microsoft Excel, Deutsch: In Excel eine Trendanalyse machen, Français: faire une modélisation avec Excel, Bahasa Indonesia: Melakukan Analisis Tren di Excel, Nederlands: Een trendanalyse maken in Excel, Tiếng Việt: Phân tích xu hướng trong Excel, ไทย: วิเคราะห์แนวโน้ม (Trend Analysis) ด้วย Excel, العربية: تحليل اتجاه البيانات في برنامج إكسل, हिन्दी: एक्सेल में ट्रेंड एनालिसिस (Trend Analysis) करें (Do Trend Analysis in Excel)

• Печать
• Править
• Написать благодарственное письмо авторам

Эту страницу просматривали 20 863 раз.

Была ли эта статья полезной?

СЦЕПИТЬ

В английской версии: CONCATENATE

Что делает: склеивает несколько ячеек в одну.

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

Допустим, нам нужно вывести для отчёта такую фразу для каждого месяца: «Январь: заработано столько-то рублей, потрачено столько-то, остаток вот такой». Чтобы не собирать это всё вручную, пишем формулу:

=CONCATENATE(A2;»: заработано «;B2;», потрачено «;D2;», остаток: «;E2)

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

ВПР

В английской версии: VLOOKUP

Что делает: сопоставляет данные из одного столбца с другим.

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

=VLOOKUP(«Март»;A2:D13;4;FALSE)

Функция возьмёт наш «Март», проверит весь диапазон, который мы указали, найдёт в нём наше слово и выведет результат из четвёртого столбца, который соответствует марту. Параметр FALSE означает, что нам нужно точное совпадение, — если хватит примерного, поставьте TRUE.

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

Как пользоваться FORECAST.ETS

FORECAST.ETS имеет три обязательных аргумента и три необязательных аргумента:

= FORECAST.ETS (целевая_дата, значения, временная шкала, , , )

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

Сезонность — положительное целое число, представляющее длину сезонного паттерна. Значение по умолчанию — 1, что означает, что Excel автоматически определяет сезонность. Ноль означает отсутствие сезонности.

Data_completion: FORECAST.ETS поддерживает до 30% отсутствующих данных и может корректировать эти отсутствующие точки данных, используя ноль вместо них или интерполируя на основе соседних точек данных.

Агрегирование: FORECAST.ETS будет агрегировать значения с одной и той же меткой времени, даже если метки времени требуют постоянного шага или интервала. В аргументе используется число, представляющее параметр агрегирования, по умолчанию равный нулю или среднему значению, а также другие параметры, включая SUM, COUNT, MIN, MAX или MEDIAN.

Прогнозируем

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

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

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

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

Именно поэтому применяются специальные функции.

Сейчас мы их построим, настроим и проанализируем.

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

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

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

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

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

• лепестковый;
• круговой;
• поверхностный;
• кольцевой;
• объемный;
• с накоплением.

Разбираемся с трендами в MS Excel

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

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

Самое время бить тревогу и… знакомится с такой штукой как линия тренда .

СУММЕСЛИМН

В английской версии: SUMIFS

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

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

=SUMIFS(B2:B13;C2:C13;»работа»;E2:E13;»удалёнка»)

Здесь мы первым параметром задаём, из какого столбца будем брать числа для суммы, потом два параметра — фильтр по источнику, и последние два — выбираем только те, где вид стоит «удалёнка»:

Окно функциональных аргументов

Пример прогнозирования годового тренда продаж Amazon

В этом примере функция Trend используется для прогнозирования годовых продаж Amazon Inc. для следующих 4 последовательных 10 000 отчетов о доходах. Сюда входят период с 2020 по 2024 год с результатами тренда, основанными на исторических данных о продажах. См. Необработанные данные в таблице ниже.

Исторические данные о продажах Amazon Inc.

Год	Годовые продажи (в миллионах)
2015	$107,006
2016	$135,987
2017	$177,866
2018	$232,887
2019	$280,522

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

Данные о продажах Amazon Inc. перенесены в Excel

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

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

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

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

Ошибка разлива

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

Если вы все еще получаете ошибки, я бы очистил эти ячейки или даже попытался ввести ваши данные еще несколько раз.

Рекомендации

Microsoft. (нет данных). ТЕНДЕНЦИЯ. Получено 5 января 2020 г. с сайта https://support.office.com/en-us/article/trend-function-e2f135f0-8827-4096-9873-9a7cf7b51ef1.

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

Популярные статьи

1

PEST-анализ: что это такое и как его провести на примерах

Стратегический менеджмент – это работа с неопределенностью во внутренней и, особенно, во вне…

23.08.2019

 • 
Евгения Чернова

2

Расчет себестоимости

Расчет себестоимости – очень сложный процесс

Важно не только правильно обобщить все затраты. Надо..

17.03.2020

 • 
Ольга Воробьева

3

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

Анализ финансовых результатов деятельности предприятия дает понимание, насколько эффективно оно ра…

31.01.2020

 • 
Ольга Воробьева

4

Система 5S на производстве: секреты успешного внедрения

Термин «5S» стал популярен в 1980-х годах в производственном секторе Японии. В это время успехи ко…

22.07.2019

 • 
Ильнар Фархутдинов

Прогнозирование в экономике

Определение 1

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

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

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

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

Замечание 1

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

МИНЕСЛИ и МАКСЕСЛИ

В английской версии: MINIF, MAXIF.

Что делают: находят минимальное и максимальное значение по какому-то условию в заданных диапазонах.

Допустим, у нас есть таблица доходов и расходов, причём доходы получены из разных источников:

С помощью функций МИНЕСЛИ и МАКСЕСЛИ мы можем найти минимальные и максимальные значения по заданному параметру. Например, найдём минимальный доход, который мы получили с внешних заказов. Для этого напишем формулу:

=MINIFS(B2:B13;C2:C13;»заказ»)

Первый параметр — это диапазон, где ищем минимальное значение, второй — диапазон, по которому мы будем проверять наше условие, и третий — само условие. Получается, что формула возьмёт слово «заказ», найдёт в столбце C все совпадения с ним, а потом найдёт в столбце B минимальное значение:

Точно так же можно найти максимальный доход на работе — вдруг работать только с заказами выгоднее:

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

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

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

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

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

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

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

y = 4856*x + 105104

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

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

Алгоритм прогнозирования объёма продаж в MS Excel

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

В данной статье представлен один из возможных алгоритмов построения прогноза объёма реализации для продуктов с сезонным характером продаж. Сразу следует отметить, что перечень таких товаров гораздо шире, чем это кажется. Дело в том, что понятие “сезон” в прогнозировании применим к любым систематическим колебаниям, например, если речь идёт об изучении товарооборота в течение недели под термином “сезон” понимается один день. Кроме того, цикл колебаний может существенно отличаться (как в большую, так и в меньшую сторону) от величины один год. И если удаётся выявить величину цикла этих колебаний, то такой временной ряд можно использовать для прогнозирования с использованием аддитивных и мультипликативных моделей.

Аддитивную модель прогнозирования можно представить в виде формулы:

где: F – прогнозируемое значение; Т – тренд; S – сезонная компонента; Е – ошибка прогноза.

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

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

Рис. 1. Аддитивная и мультипликативные модели прогнозирования.

Алгоритм построения прогнозной модели

Для прогнозирования объема продаж, имеющего сезонный характер, предлагается следующий алгоритм построения прогнозной модели:

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

2 .Вычитая из фактических значений объёмов продаж значения тренда, определяют величины сезонной компоненты и корректируют таким образом, чтобы их сумма была равна нулю.

3.Рассчитываются ошибки модели как разности между фактическими значениями и значениями модели .

4.Строится модель прогнозирования:

где: F – прогнозируемое значение; Т – тренд; S – сезонная компонента; Е — ошибка модели.

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

F пр t = a F ф t-1 + (1-а) F м t

где: F пр t — прогнозное значение объёма продаж; F ф t- 1 – фактическое значение объёма продаж в предыдущем году; F м t — значение модели; а – константа сглаживания

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

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

Применение алгоритма рассмотрим на следующем примере.

Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого “Пломбир” одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.

Таблица 1. Фактические объёмы реализации продукции

голоса

Рейтинг статьи

Функция ПРОГНОЗ Синтаксис и входные данные:

FORECAST.ETS (целевая_дата, значения, временная шкала, , , )

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

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

Лента новостей — массив дат / времени или независимых числовых данных с постоянным шагом между ними.

Сезонность (необязательно) — число, представляющее длину сезонного шаблона:

Завершение данных (необязательно) — учитывает недостающие баллы.

Агрегирование (необязательно) — указывает, как агрегировать несколько значений данных с одной и той же меткой времени.

Функция FORECAST.ETS в Excel используется для прогнозирования данных с использованием алгоритма экспоненциального сглаживания.

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

Допустим, у меня есть таблица данных о продажах по месяцам:

Я хочу знать, какой будет прогнозируемый объем продаж на октябрь 2022 года на основе исторических данных, представленных в таблице:

= FORECAST.ETS (ДАТА (2020,10,1); C3: C12, B3: B12)

FORECAST.ETS возвращает результат продаж в размере 21 202 долларов США в октябре 2022 года. Мы можем визуализировать это и прогнозы на месяцы между ними, применив формулу к развернутой таблице данных:

Визуализация прогноза:

Часть 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 TREND

На первый взгляд, синтаксис функции ТРЕНД может показаться чрезмерно сложным, но следующие примеры сделают его намного проще.

Формула TREND для анализа тенденций временных рядов в Excel

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

В этом примере у нас есть номера месяцев (независимые значения x) в A2:A13 и номера продаж (зависимые значения y) в B2:B13. На основе этих данных мы хотим определить общую тенденцию во временном ряду, игнорируя холмы и долины.

Чтобы это сделать, выберите диапазон C2:C13, введите приведенную ниже формулу и нажмите Ctrl + Shift + Enter, чтобы завершить ее:

=ТЕНДЕНЦИЯ(B2:B13;A2:A13)

Чтобы нарисовать линию тренда, выберите значения продаж и тренда (B1:C13) и постройте линейный график (Вставлять вкладка > Графики группа > Линейная или площадная диаграмма).

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

Проектирование будущего тренда

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

Для этого мы расширяем наш временной ряд еще несколькими числами месяцев и делаем прогноз тренда, используя эту формулу:

=ТЕНДЕНЦИЯ(B2:B13,A2:A13,A14:A17)

Где:

• B2:B13 известен_у
• A2:A13 известен_x
• A14:A17 принадлежит new_x

Введите приведенную выше формулу в ячейки C14: C17 и не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ее надлежащим образом. После этого создайте новую линейную диаграмму для расширенного набора данных (B1:C17).

На приведенном ниже снимке экрана показаны рассчитанные новые значения y и расширенная линия тренда:

Формула тренда Excel для нескольких наборов значений x

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

Например, со значениями known_x1 в B2:B13, значениями known_x2 в C2:C13 и значениями known_y в D2:D13 вы используете следующую формулу для расчета тренда:

=ТЕНДЕНЦИЯ(D2:D13;B2:C13)

Кроме того, вы можете ввести значения new_x1 и new_x2 в B14:B17 и C14:C17 соответственно и получить прогнозируемые значения y с помощью этой формулы:

=ТЕНДЕНЦИЯ(D2:D13,B2:C13,B14:C17)

При правильном вводе (с помощью сочетания клавиш Ctrl + Shift + Enter) формулы выводят следующие результаты:

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

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

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

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

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

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

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

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

Как можно исправить прогнозные значения тренда?

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

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

Мы можем влиять на параметры a и b линейного уравнения y = a + bx, тем самым изменяя значения:

• если изменяем значение «a» линейного тренда, то меняется уровень тренда (поднимается параллельно);
• если изменяем значение «b» линейного тренда, то увеличиваем наклон тренда;

Таким образом, мы можем изменять наклон и уровень тренда, как в отдельности, так и одновременно (подробнее в примере):

Руководитель отдела продаж

Нажимая на кнопку, вы даете согласие на обработку персональных данных и соглашаетесь с политикой конфиденциальности