Функция ТЕНДЕНЦИЯ в Excel (формула, примеры) | Как использовать тренд в Excel

Функция тренда в Excel

Функция тренда в Excel - это статистическая функция, которая вычисляет линейную линию тренда на основе заданного линейного набора данных. Он вычисляет прогнозные значения Y для заданных значений массива X и использует метод наименьших квадратов на основе данных двух рядов данных. Функция тренда в Excel возвращает числа в линейном тренде, совпадающем с известными точками данных, то есть существующими данными, по которым тренд в Excel предсказывает значения Y в зависимости от значений X, которые должны быть линейными данными.

Что такое метод наименьших квадратов?

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

Синтаксис

Ниже представлена ​​формула ТЕНДЕНЦИИ в Excel.

Аргументы

Для данного линейного уравнения y = m * x + c

Known_y’s : это обязательный аргумент, который представляет набор значений y, которые у нас уже есть как существующие данные в наборе данных, которые соответствуют соотношению y = mx + c.

Known_x's: это необязательный аргумент, который представляет набор значений x, которые должны иметь ту же длину, что и набор известных_y. Если этот аргумент опущен, набор известных_x принимает значение (1, 2, 3… и так далее).

New_x's: это также необязательный аргумент. Это числовые значения, представляющие значение new_x. Если аргумент new_x опущен, он устанавливается равным известному_x.

Const: это необязательный аргумент, который указывает, равняется ли постоянное значение c 0. Если const имеет значение ИСТИНА или опущено, c вычисляется нормально. Если false, c принимается равным 0 (нулю), а значения m корректируются так, чтобы y = mx.

Как использовать функцию ТЕНДЕНЦИЯ в Excel?

Функция ТЕНДЕНЦИЯ в Excel очень проста и удобна в использовании. Давайте разберемся с работой функции ТЕНДЕНЦИЯ на некоторых примерах.

Вы можете скачать этот шаблон Excel с функцией TREND здесь - Шаблон Excel с функцией TREND

Пример # 1

В этом примере предположим, что у нас есть данные для результатов тестов с их средним баллом, теперь, используя эти данные, нам нужно сделать прогноз для среднего балла. У нас есть существующие данные в столбцах A и B, существующие значения GPA, соответствующие баллам, - это известные значения Y, а существующие значения баллов - это известные значения X. Мы дали некоторые значения для значений X в виде Score, и нам нужно предсказать значения Y, которые являются средним баллом на основе существующих значений.

Существующие значения:

Заданные значения и значения Y, которые необходимо предсказать:

Чтобы предсказать значения GPA для заданных тестов в ячейках D2, D3 и D4, мы будем использовать функцию ТЕНДЕНЦИЯ в Excel.

Формула ТЕНДЕНЦИЯ в excel будет принимать существующие значения известных X и Y, и мы передадим новые значения X для вычисления значений Y в ячейках E2, E3 и E4.

Формула ТЕНДЕНЦИИ в Excel будет следующей:

= ТЕНДЕНЦИЯ ($ A $ 2: $ A $ 16, $ B $ 2: $ B $ 16, D2)

Мы зафиксировали диапазон для известных значений X и Y, и передаем новое значение X в качестве опорного значения. Применяя ту же формулу ТЕНДЕНЦИИ в Excel к другим ячейкам, мы имеем

Выход:

Итак, используя функцию ТЕНДЕНЦИЯ в Excel выше, мы предсказали три значения Y для заданных новых результатов тестов.

Пример # 2 - Прогнозирование роста продаж

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

Существующие данные содержат даты в столбце A и выручку от продаж в столбце B, нам необходимо рассчитать ориентировочную стоимость продаж на следующие 5 месяцев. Исторические данные приведены ниже:

Чтобы спрогнозировать продажи на ближайшие месяцы в следующем году, мы будем использовать функцию ТЕНДЕНЦИЯ в Excel, поскольку стоимость продаж растет линейно, заданные известные значения Y - это доход от продаж, а известные значения X - это даты окончания месяца, новые значения X - это даты следующих 3 месяцев, то есть 31.01.2018, 28.02.2018 и 31.03.2018, и нам нужно вычислить оценочные значения продаж на основе исторических данных задано в диапазоне A1: B13.

Формула ТЕНДЕНЦИЯ в excel будет принимать существующие значения известных X и Y, и мы передадим новые значения X для вычисления значений Y в ячейках E2, E3 и E4.

Формула ТЕНДЕНЦИИ в Excel будет следующей:

= ТЕНДЕНЦИЯ ($ B $ 2: $ B $ 13, $ A $ 2: $ A $ 13, D2)

Мы зафиксировали диапазон для известных значений X и Y, и передаем новое значение X в качестве опорного значения. Применяя ту же формулу ТЕНДЕНЦИИ в Excel к другим ячейкам, которые у нас есть,

Выход:

Итак, используя функцию ТЕНДЕНЦИЯ, описанную выше, мы спрогнозировали ориентировочные значения продаж для данных предстоящих месяцев в ячейках D2, D3 и D4.

То, что нужно запомнить

  1. Существующие исторические данные, которые содержат известные значения X и Y, должны быть линейными данными, которые для заданных значений X значение Y должно соответствовать линейной кривой y = m * x + c, в противном случае выходные или прогнозируемые значения могут быть неточным.
  2. Функция ТЕНДЕНЦИЯ в Excel генерирует # ЗНАЧ! Ошибка, когда заданные известные значения X или Y не являются числовыми или значение нового X не является числовым, а также когда аргумент const не является логическим значением (то есть TRUE или FALSE)
  3. Функция ТЕНДЕНЦИЯ в Excel генерирует # ССЫЛКА! Известные погрешности значений X и Y имеют разную длину.