Функция Excel TREND и другие способы анализа тенденций
В учебнике показано, как рассчитать тренд в Excel с помощью функции ТРЕНД, как создать тренды на графике и многое другое.
В наши дни, когда технологии, рынки и потребности клиентов меняются так быстро, очень важно двигаться в ногу с тенденциями, а не против них. Анализ трендов может помочь вам определить базовые закономерности в прошлом и текущих перемещениях данных, а также спрогнозировать будущее поведение.
Функция Excel ТРЕНД
Функция Excel TREND используется для расчета линейной линии тренда через заданный набор зависимых значений y и, при необходимости, набор независимых значений x и возвращаемых значений вдоль линии тренда.
Кроме того, функция TREND может продлить линию тренда в будущее, чтобы проецировать зависимые значения y для набора новых значений x.
Синтаксис функции Excel ТРЕНД следующий:
ТРЕНД(известные_y, [known_x’s], [new_x’s], [const])
Где:
Known_y’s (обязательно) — набор зависимых значений y, которые вам уже известны.
Известные_x (необязательно) — один или несколько наборов независимых значений x.
- Если используется только одна переменная x, известные_y и известные_x могут быть диапазонами любой формы, но одинакового размера.
- Если используется несколько переменных x, known_y должен быть вектором (один столбец или одна строка).
- Если опущено, предполагается, что known_x представляет собой массив серийных номеров {1,2,3,…}.
New_x’s (необязательно) — один или несколько наборов новых значений x, для которых вы хотите рассчитать тренд.
- Он должен иметь то же количество столбцов или строк, что и known_x.
- Если он опущен, предполагается, что он равен known_x.
Const (необязательно) — логическое значение, указывающее, как константа а в уравнении y = bx + a следует вычислить.
- Если TRUE или опущено, константа а рассчитывается нормально.
- Если FALSE, константа а устанавливается равным 0, а значения b корректируются, чтобы соответствовать уравнению y = bx.
Как функция TREND вычисляет линейную линию тренда
Функция Excel TREND находит линию, которая лучше всего соответствует вашим данным, используя метод наименьших квадратов. Уравнение для линии выглядит следующим образом.
Для одного диапазона значений x:
у = Ьх + а
Для нескольких диапазонов значений x:
у = b1x1 + b2x2 + … + bnxn + а
Где:
- у – зависимая переменная, которую вы пытаетесь вычислить.
- Икс – независимая переменная, которую вы используете для расчета у.
- a – точка пересечения (указывает, где линия пересекает ось y и равна значению y, когда x равно 0).
- б – уклон (указывает на крутизну линии).
Это классическое уравнение для линии наилучшего соответствия также используется функцией ЛИНЕЙН и анализом линейной регрессии.
Функция TREND как формула массива
Чтобы вернуть несколько новых значений y, функцию TREND следует ввести как формулу массива. Для этого выделите все ячейки, в которых вы хотите отобразить результаты, введите формулу и нажмите Ctrl + Shift + Enter, чтобы завершить ее. При этом формула будет заключена в {фигурные скобки}, что является визуальным признаком формулы массива. Поскольку новые значения возвращаются в виде массива, вы не сможете редактировать или удалять их по отдельности.
Примеры формул 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) формулы выводят следующие результаты:
Другие способы анализа тенденций в Excel
Функция ТРЕНД — самый популярный, но не единственный метод прогнозирования тренда в Excel. Ниже я кратко опишу несколько других техник.
ПРОГНОЗ Excel против ТРЕНДА
«Тренд» и «прогноз» — очень близкие понятия, но все же есть разница:
- Тренд — это то, что представляет текущие или прошедшие дни. Например, анализируя последние данные о продажах, вы можете определить тенденцию движения денежных средств и понять, как ваш бизнес работал и работает в настоящее время.
- Прогноз – это то, что относится к будущему. Например, анализируя исторические данные, вы можете спрогнозировать будущие изменения и предсказать, куда вас приведут текущие методы ведения бизнеса.
В терминах Excel это различие не столь очевидно, потому что функция TREND может не только вычислять текущие тренды, но и возвращать будущие значения y, т.е. делать прогнозирование трендов.
Разница между ТРЕНД и ПРОГНОЗ в Excel заключается в следующем:
- Функция ПРОГНОЗ может только прогнозировать будущие значения на основе существующих значений. Функция TREND может рассчитывать как текущие, так и будущие тренды.
- Функция ПРОГНОЗ используется как обычная формула и возвращает одно новое значение y для одного нового значения x. Функция TREND используется как формула массива и вычисляет несколько значений y для нескольких значений x.
При использовании для прогнозирования временных рядов обе функции дают один и тот же линейный тренд/прогноз, поскольку их расчеты основаны на одном и том же уравнении.
Пожалуйста, взгляните на скриншот ниже и сравните результаты, возвращаемые следующими формулами:
=ТЕНДЕНЦИЯ(B2:B13,A2:A13,A14:A17)
=ПРОГНОЗ(A14,$B$2:$B$13,$A$2:$A$13)
Дополнительные сведения см. в разделе Использование функции ПРОГНОЗ в Excel.
Нарисуйте линию тренда, чтобы визуализировать тренд
Линия тренда обычно используется для наблюдения за общей тенденцией ваших текущих данных, а также для прогнозирования движения данных в будущем.
Чтобы добавить тренд в существующую диаграмму, щелкните правой кнопкой мыши ряд данных и выберите «Добавить линию тренда». Это создаст линейную линию тренда по умолчанию для текущих данных и откроет окно Формат линии тренда панель, где вы можете выбрать другой тип линии тренда.
Чтобы спрогнозировать тренд, укажите количество периодов в Прогноз на Формат линии тренда панель:
- Чтобы спроецировать тренд в будущее, введите количество периодов в Вперед коробка.
- Чтобы экстраполировать тренд в прошлое, введите нужное число в поле Назад коробка.
Чтобы показать уравнение линии тренда, отметьте Отображение уравнения на графике коробка. Для большей точности вы можете показать больше цифр в уравнении линии тренда.
Как показано на изображении ниже, результаты уравнения линии тренда полностью соответствуют числам, возвращаемым формулами ПРОГНОЗ и ТЕНДЕНЦИЯ:
Дополнительные сведения см. в разделе Как добавить линию тренда в Excel.
Плавный тренд со скользящей средней
Еще одна простая техника, которая может помочь вам показать тренд, называется скользящая средняя (иначе скользящая средняя или же скользящее среднее). Этот метод сглаживает краткосрочные колебания выборочного временного ряда и выделяет долгосрочные закономерности или тенденции.
Вы можете рассчитать скользящее среднее вручную с помощью собственных формул или сделать линию тренда в Excel автоматически.
Чтобы отобразить линию тренда скользящей средней на графике, вам нужно сделать следующее:
- Щелкните правой кнопкой мыши ряд данных и выберите Добавить линию тренда.
- На Формат линии тренда панель, выберите Скользящее среднее и укажите желаемое количество периодов.
Вот как вы используете функцию ТРЕНД для расчета трендов в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец Книга Excel ТРЕНД. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
