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

Other ways to do trend analysis in Excel

The TREND function is the most popular but not the only trend projection method in Excel. Below I will briefly describe a few other techniques.


Excel FORECAST vs TREND

«Trend» and «forecast» are very close concepts, but still there is a difference:

  • Trend is something that represents the current or past days. For example, by analyzing the recent sales numbers, you can determine the cash flow trend and understand how your business has performed and is currently performing.
  • Forecast is something that relates to the future. For example, by analyzing the historical data, you can project future changes and predict where current business practices will take you.

In terms of Excel, this distinction is not so obvious because the TREND function can not only calculate current trends, but also return future y-values, i.e. do trend forecasting.

The difference between TREND and FORECAST in Excel is as follows:

  • The FORECAST function can only predict future values based on the existing values. The TREND function can calculate both current and future trends.
  • The FORECAST function is used as a regular formula and returns a single new y-value for a single new-x value. The TREND function is used as an array formula and computes multiple y-values for multiple x-values.

When used for time series forecasting, both functions produce the same linear trend/forecast because their calculations are based on the same equation.

Please take a look at the screenshot below and compare the results returned by the following formulas:

For more information, please see .

Draw a trendline to visualize the trend

A trendline is commonly used to observe the general trend in your current data as well as project future data movements.

To add a trend to an existing chart, right-click the data series, and then click Add Trendline… This will create the default linear trendline for the current data and open the Format Trendline pane where you can choose another trendline type.

To forecast a trend, specify the number of periods under Forecast on the Format Trendline pane:

  • To project the trend into the future, type the number of periods in the Forward box.
  • To extrapolate a trend into the past, type the desired number in the Backward box.

To show the trendline equation, check the Display Equation on chart box. For better accuracy, you can .

As shown in the image below, the results of the trendline equation are perfectly in line with the numbers returned by the FORECAST and TREND formulas:

For more information, please see How to add a trendline in Excel.

Smooth trend with moving average

Another simple technique that can help you show a trend is called moving average (aka rolling average or running average). This method smoothes out short-term fluctuations in a sample time series and highlights longer-term patterns or trends.

You can calculate moving average manually with your own formulas or have Excel make a trendline for you automatically.

To display a moving average trendline on a chart, here’s what you need to do:

  1. Right-click the data series and click Add Trendline.
  2. On the Format Trendline pane, select Moving Average and specify the desired number of periods.

That’s how you use the TREND function to calculate trends in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample Excel TREND workbook. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

  • Excel FORECAST and related functions with formula examples
  • How to forecast in Excel: linear and exponential smoothing forecasting
  • How to use Goal Seek in Excel for What-If analysis

Уравнение линии тренда в Excel

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

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

Линейная аппроксимация

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

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):

Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).

Получаем результат:

Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:. y = 4,503x + 6,1333

y = 4,503x + 6,1333

  • где 4,503 – показатель наклона;
  • 6,1333 – смещения;
  • y – последовательность значений,
  • х – номер периода.

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

Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.

Экспоненциальная линия тренда

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

Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

Строим график. Добавляем экспоненциальную линию.


Уравнение имеет следующий вид:

y = 7,6403е^-0,084x

  • где 7,6403 и -0,084 – константы;
  • е – основание натурального логарифма.

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

Логарифмическая линия тренда в Excel

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

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

Построим график и добавим логарифмическую линию тренда для прогноза продаж условного продукта:

R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Например:

Период 14 15 16 17 18 19 20
Прогноз 1005,4 1024,18 1041,74 1058,24 1073,8 1088,51 1102,47

Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.

Полиномиальная линия тренда в Excel

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

Полиномиальный тренд в Excel применяется для анализа большого набора данных о нестабильной величине. Посмотрим на примере первого набора значений (цены на нефть).

Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.

Зато такой тренд позволяет составлять более-менее точные прогнозы.

Excel TREND formula examples

At first sight, the syntax of the TREND function might seem excessively complicated, but the following examples will make things a lot easier.

TREND formula for time series trend analysis in Excel

Supposing you are analyzing some data for a sequential period of time and you want to spot a trend or pattern.

In this example, we have the month numbers (independent x-values) in A2:A13 and sales numbers (dependent y-values) in B2:B13. Based on this data, we want to determine the overall trend in the time series ignoring hills and valleys.

To have it done, select the range C2:C13, type the below formula and press Ctrl + Shift + Enter to complete it:

To draw the trendline, select the sales and trend values (B1:C13) and make a line chart (Insert tab > Charts group > Line or Area Chart).

As the result, you have both the numeric values for the line of best fit returned by the formula and a visual representation of those values in a graph:

Projecting a future trend

To predict a trend for the future, you just need to include a set of new x-values in your TREND formula.

For this, we extend our time series with a few more month numbers and do trend projection by using this formula:

Where:

  • B2:B13 is known_y’s
  • A2:A13 is known_x’s
  • A14:A17 is new_x’s

Enter the above formula in cells C14:C17 and remember to press Ctrl + Shift + Enter to complete it appropriately. After that, create a new line chart for the extended data set (B1:C17).

The below screenshot shows the calculated new y-values and extended trendline:

Excel Trend formula for multiple sets of x-values

In situation when you have two or more sets of independent x values, enter them in separate columns, and supply that entire range to the known_x’s argument of the TREND fucntion.

For example, with the known_x1 values in B2:B13, known_x2 values in C2:C13, and known_y values in D2:D13, you use the following formula to calculate trend:

Additionally, you can enter the new_x1 and new_x2 values in B14:B17 and C14:C17, respectively, and get the projected y-values with this formula:

If entered correctly (with the Ctrl + Shift + Enter shortcut), the formulas output the following results:

Торговля в трендовом канале

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

Примеры трендовых каналов на графике


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

Важно, чтобы получившиеся линии были параллельны друг другу

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

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

Как определить наличие тренда?

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

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

Чтобы верно построить линию тренда,  нужно определить опорные точки. Здесь единственно правильного метода не существует, но подскажем один результативный. Решив, какой тренд нам надо проанализировать, выбираем максимально низкий или высокий показатель графика стартовой точкой линии. Второй точкой станет следующее «донышко» или пик.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

В основной статье про МНК было рассмотрено приближение линейной функцией. В этой статье рассмотрим приближение полиномиальной функцией (с 3-й до 6-й степени) следующего вида: y=b+b1x+b2x 2 +b3x 3 +…+b6x 6

Примечание: В инструменте MS EXCEL Линия тренда, который доступен для диаграмм типа Точечная и График, можно построить линию тренда на основе полинома с максимальной степенью 6. В файле примера продемонстрировано полное совпадение линии тренда диаграммы и линии, вычисленной с помощью формул.

Покажем, как вычислить коэффициенты b линии тренда, заданной полиномом.

Как известно, квадратичная зависимость y=b+b1x+b2x 2 , подробно рассмотренная в статье МНК: Квадратичная зависимость в MS EXCEL, является частным случаем полиномиальной y=b+b1x+b2x 2 +b3x 3 +… зависимости (в этом случае степень полинома равна 2). Соответственно, используя тот же подход (приравнивание к 0 частных производных), можно вычислить коэффициенты любого полинома.

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

Для нахождения m+1 коэффициента полинома m-й степени составим систему из m+1 уравнения и решим ее методом обратной матрицы. Для квадратного уравнения (m=2) нам потребовалось вычислить сумму значений х с 1-й до 4-й степени, а для полинома m-й степени необходимо вычислить значения х с 1-й до 2*m степени.

Примечание: Для удобства суммы степеней значений х можно вычислить в отдельном диапазоне ( файл примера столбцы К:М).

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

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

Примечание: При использовании полиномов высокой степени необходимо следить за тем, чтобы количество пар значений (хi; yi) превышало степень полинома хотя бы на несколько значений (для обеспечения точности аппроксимации). Кроме того, график функции полинома степени m имеет m-1 точку перегиба. Понятно, что точек данных должно быть гораздо больше, чем точек перегиба, чтобы такой изменчивый тренд стал очевидным (если утрировать, то бессмысленно строить по двум точкам параболу, логичнее построить прямую).

Как видно из расчетов, в MS EXCEL этот путь является достаточно трудоемким. Гораздо проще в MS EXCEL реализовать другой подход для вычисления коэффициентов полинома — с помощью замены переменных.

С помощью замены переменных x i =xi полиномиальную зависимость y=b+b1x+b2x 2 +b3x 3 +… можно свести к линейной. Теперь переменная y зависит не от одной переменной х в m разных степенях, а от m независимых переменных xi. Поэтому для нахождения коэффициентов полинома мы можем использовать функцию ЛИНЕЙН() . Этот подход также продемонстрирован в файле примера .

Есть 3 способа расчета значений полинома в Excel:

  • 1-й способ с помощью графика;
  • 2-й способ с помощью функции Excel =ЛИНЕЙН();
  • 3-й способ с помощью Forecast4AC PRO;

Подробнее о полиноме и способе его расчета в Excel далее в нашей статье.

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

Что такое полином? Полином — это степенная функция y=ax 2 +bx+c (полином второй степени) и y=ax 3 +bx 2 +cx+d (полином третей степени) и т.д. Степень полинома определяет количество экстремумов (пиков), т.е. максимальных и минимальных значений на анализируемом промежутке времени.

У полинома второй степени y=ax 2 +bx+c один экстремум (на графике ниже 1 максимум).

У Полинома третьей степени y=ax 3 +bx 2 +cx+d может быть один или два экстремума.

Один экстремум


Два экстремума

У Полинома четвертой степени не более трех экстремумов и т.д.

Excel TREND function

The Excel TREND function is used to calculate a linear trend line through a given set of dependent y-values and, optionally, a set of independent x-values and return values along the trend line.

Additionally, the TREND function can extend the trendline into the future to project dependent y-values for a set of new x-values.

The syntax of the Excel TREND function is as follows:

Where:

Known_y’s (required) — a set of the dependent y-values that you already know.

Known_x’s (optional) — one or more sets of the independent x-values.

  • If only one x variable is used, known_y’s and known_x’s can be ranges of any shape but equal dimension.
  • If several x variables are used, known_y’s must be a vector (one column or one row).
  • If omitted, known_x’s is assumed to be the array of serial numbers {1,2,3,…}.

New_x’s (optional) — one or more sets of new x-values for which you want to calculate the trend.

  • It must have the same number of columns or rows as known_x’s.
  • If omitted, it is assumed to be equal to known_x’s.

Const (optional) — a logical value specifying how the constant a in the equation y = bx + a should be calculated.

  • If TRUE or omitted, the constant a is calculated normally.
  • If FALSE, the constant a is forced to 0, and the b-values are adjusted to fit the equation y = bx.

How TREND function calculates linear trendline

The Excel TREND Function finds the line that best fits your data by using the least squares method. The equation for the line is as follows.

For one range of x values:

y = bx + a

For multiple ranges of x values:

y = b1x1 + b2x2 + … + bnxn + a

Where:

  • y — the dependent variable you are trying to calculate.
  • x — the independent variable you are using to calculate y.
  • a — the intercept (indicates where the line intersects the y-axis and is equal to the value of y when x is 0).
  • b — the slope (indicates the steepness of the line).

This classic equation for the line of best fit is also used by the LINEST function and linear regression analysis.

TREND function as an array formula

To return multiple new y-values, the TREND function should be entered as an array formula. For this, select all the cells where you want the results to appear, type the formula and press Ctrl + Shift + Enter to complete it. As you do this, the formula will get enclosed in {curly braces}, which is a visual indication of an array formula. Since the new values are returned as an array, you won’t be able to edit or delete them individually.

Синтаксис

Аргументы

Обязательный. Множество значений y, которые уже известны для соотношения

  • Если массив «известные_значения_y» имеет один столбец, то каждый столбец массива «известные_значения_x» интерпретируется как отдельная переменная.
  • Если массив «известные_значения_y» имеет одну строку, то каждая строка массива «известные_значения_x» интерпретируется как отдельная переменная.

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

  • Массив «известные_значения_x» может содержать одно или несколько множеств переменных. Если используется только одна переменная, то аргументы «известные_значения_y» и «известные_значения_x» могут быть диапазонами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то аргумент «известные_значения_y» должен быть вектором (то есть диапазоном высотой в одну строку или шириной в один столбец).
  • Если аргумент «известные_значения_x» опущен, то предполагается, что это массив {1;2;3;…} того же размера, что и массив «известные_значения_y».

Обязательный. Новые значения x, для которых функция ТЕНДЕНЦИЯ возвращает соответствующие значения y

  • Аргумент «новые_значения_x», так же как и аргумент «известные_значения_x», должен содержать по одному столбцу (или строке) для каждой независимой переменной. Таким образом, если «известные_значения_y» — это один столбец, то «известные_значения_x» и «новые_значения_x» должны иметь одинаковое количество столбцов. Если «известные_значения_y» — это одна строка, то аргументы «известные_значения_x» и «новые_значения_x» должны иметь одинаковое количество строк.
  • Если аргумент «новые_значения_x» опущен, то предполагается, что он совпадает с аргументом «известные_значения_x».
  • Если опущены оба аргумента — «известные_значения_x» и «новые_значения_x», — то предполагается, что это массивы {1;2;3;…} того же размера, что и «известные_значения_y».

Необязательный. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

  • Если аргумент «конст» имеет значение ИСТИНА или опущен, то b вычисляется обычным образом.
  • Если аргумент «конст» имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось условие

Замечания

  • Сведения о том, каким образом Microsoft Excel аппроксимирует данные прямой, см. в описании функции ЛИНЕЙН.
  • Функцию ТЕНДЕНЦИЯ можно использовать для аппроксимации полиномиальной кривой, проводя регрессионный анализ для той же переменной, возведенной в различные степени. Например, пусть столбец A содержит значения y, а столбец B содержит значения x. Можно ввести значение x^2 в столбец C, x^3 в столбец D и т. д., а затем провести регрессионный анализ столбцов от B до D со столбцом A.
  • Формулы, которые возвращают массивы, должны быть введены как формулы массива.

    В Excel Web App невозможно создать формулу массива.​

  • При вводе константы массива для таких аргументов, как «известные_значения_x», следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк.

Метод прогноза по модели Хольта-Винтерса в Excel

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

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

Первые значения F1 и S1 обычно:

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

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

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

где Yt* — прогнозы истекли.

Среднеквадратичная ошибка (RMSE — Root Mean Square Error) — измеряет, насколько отклонение реализации прогнозируемой переменной от рассчитанных прогнозов.

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

Пустая книга на начальном экране не базируется на книге Book. xltx.

В более ранних версиях Excel при сохранении параметров книги, которые часто используются в шаблоне книги с именем Book. xltx, который хранится в папке XLStart (обычно C:Usersимя пользователяаппдаталокалмикрософтексцелкслстарт), этот шаблон оно будет автоматически открываться при создании новой пустой книги.

Когда вы запускаете Excel 2013, появляется начальный экран, и приложение Excel не открывает новую книгу автоматически. Пустая книга, которую вы можете щелкнуть на начальном экране, не связана с Book. xltx.

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

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

Новую книгу, использующую шаблон Книга.xltx, можно также создать, нажав клавиши CTRL+N.

Шаблон «Сохранить как» больше не находится на ленте «работа с диаграммами»

В Excel 2007-2010 можно сохранить диаграмму как шаблон, нажав кнопку Сохранить как шаблон на ленте (работа с диаграммами _гт_ конструктор _гт_ тип). В Excel 2013шаблон «Сохранить как » больше не доступен на ленте.

Чтобы сохранить диаграмму как шаблон, щелкните диаграмму правой кнопкой мыши и выберите команду Сохранить как шаблон. Excel сохранит диаграмму как шаблон диаграммы (*. крткс) в папке \Аппдатароамингмикрософттемплатесчартс. Если вы хотите использовать ее для создания или изменения диаграммы, откройте диалоговое окно Вставка диаграммы или изменение типа диаграммы , а затем на вкладке Все диаграммы откройте папку шаблоны .


С этим читают