Функция ТЕНДЕНЦИЯ в 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. По оси Х – номера месяцев, по оси Y – объем товарооборота.

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

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

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

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

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

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

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

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

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

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

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