Функция тренда в Excel — это статистическая функция, которая вычисляет линейную линию тренда на основе заданного линейного набора данных. Вычисляет прогнозные значения Y для заданных значений матрицы X и использует метод наименьших квадратов на основе данных из двух рядов данных. Функция тренда в Excel возвращает числа в линейном тренде, которые соответствуют известным точкам данных, то есть существующим данным, из которых тренд в Excel прогнозирует значения Y на основе значений X, которые должны быть линейными данными.
Что такое метод наименьших квадратов?
Это метод, используемый в регрессионном анализе, который находит линию наилучшего соответствия (которая представляет собой линию, проходящую через точечную диаграмму точек данных, которая в первую очередь указывает взаимосвязь между этими точками) для заданного набора данных, что помогает визуализировать взаимосвязь между точки данных.
Синтаксис
Ниже представлена формула ТРЕНДА в Excel.
Аргументы
Для заданного линейного уравнения y = m * x + c
Known_y’s: это обязательный аргумент, который представляет набор значений y, которые у нас уже есть в качестве существующих данных в наборе данных в соответствии с отношением y = mx + c.
Known_x: это необязательный аргумент, представляющий набор значений x, которые должны быть такими же длинными, как и набор known_y. Если этот аргумент опущен, множество known_x принимает значение (1, 2, 3.. и т д).
New_x — это также необязательный аргумент. Это числовые значения, представляющие значение new_x. Если new_x опущен, он устанавливается в known_x.
Const: это необязательный аргумент, который указывает, равно ли постоянное значение c 0. Если const имеет значение TRUE или опущено, c вычисляется нормально. Если false, предполагается, что c равно 0 (нулю), а значения m корректируются так, чтобы y = mx.
Как использовать функцию ТЕНДЕНЦИЯ в Excel?
Функция ТРЕНД в Excel очень проста и удобна в использовании. Давайте разберемся, как работает функция TREND, на нескольких примерах.
Вы можете скачать этот шаблон Excel функции TREND здесь — Шаблон Excel функции TREND
Пример # 1
В этом примере предположим, что у нас есть данные для результатов тестов с их средним баллом, теперь, используя эти данные, нам нужно сделать прогноз для среднего балла. У нас есть существующие данные в столбцах A и B, существующие значения среднего балла, соответствующие баллам, являются известными значениями Y, а существующие значения баллов являются известными значениями X. Мы дали некоторые значения для X значения в качестве оценки, и нам нужно предсказать значения Y, которые представляют собой средний балл на основе существующих значений.
Существующие значения:
Заданные значения и значения Y для прогнозирования:
Чтобы предсказать значения среднего балла для заданных результатов тестов в ячейках 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 к другим ячейкам, мы имеем
Производство:
Итак, используя функцию TREND в 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.
То, что нужно запомнить
- Существующие исторические данные, содержащие известные значения X и Y, должны быть линейными данными, то есть для заданных значений X значение Y должно следовать линейной кривой y = m * x + c. В противном случае выходные или прогнозируемые значения могут быть неточными.
- Функция ТЕНДЕНЦИЯ в Excel выводит #ЗНАЧ! Ошибка, когда известные значения X или Y не являются числовыми, или значение нового X не является числовым, или когда константный аргумент не является логическим (т.е. ИСТИНА или ЛОЖЬ)
- Функция ТЕНДЕНЦИЯ в Excel генерирует #ССЫЛКА! Известные ошибки значений X и Y имеют разную длину.