Как работает функция тенденция в excel

ТЕНДЕНЦИЯ (функция ТЕНДЕНЦИЯ)

Функция TREND возвращает значения по линейному тренду. Он соответствует прямой линии (с использованием метода наименьшего квадрата) к known_y массива и known_x в. TREND возвращает y-значения вдоль этой строки для массива new_x что вы указываете.

Примечание: Если у вас есть текущая версия Microsoft 365, то вы можете ввести формулу в верхнем левом диапазоне выходных данных (ячейка E16 в этом примере), а затем нажмите ENTER, чтобы подтвердить формулу в качестве динамической формулы массива. В противном случае формула должна быть введена в качестве устаревшей формулы массива, сначала выбрав диапазон выходных данных (E16:E20), введите формулу в верхнем левом элементе выходного диапазона (E16), а затем нажмите CTRL-SHIFT-ENTER, чтобы подтвердить это. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

«TREND (known_y,«known_x’s», «new_x»,« «конст»)

Аргументы функции ТЕНДЕНЦИЯ описаны ниже.

Набор y-значений, которые вы уже знаете в отношениях y и mx b

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

Если массив "известные_значения_y" содержит одну строку, каждая строка массива "известные_значения_x" интерпретируется как отдельная переменная.

Дополнительный набор x-значений, которые вы, возможно, уже знаете в отношениях y и mx b

Массив известные_значения_x может включать одно или более множеств переменных. Если используется только одна переменная, то аргументы "известные_значения_y" и "известные_значения_x" могут быть диапазонами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то аргумент "известные_значения_y" должен быть вектором (то есть диапазоном высотой в одну строку или шириной в один столбец).

Если аргумент "известные_значения_x" опущен, то предполагается, что это массив <1;2;3;. >того же размера, что и "известные_значения_y".

Новые x-значения, для которых вы хотите, чтобы TREND вернул соответствующие по-значения

Аргумент "новые_значения_x", так же как и аргумент "известные_значения_x", должен содержать по одному столбцу (или строке) для каждой независимой переменной. Таким образом, если "известные_значения_y" — это один столбец, то "известные_значения_x" и "новые_значения_x" должны иметь одинаковое количество столбцов. Если "известные_значения_y" — это одна строка, то аргументы "известные_значения_x" и "новые_значения_x" должны иметь одинаковое количество строк.

Если аргумент "новые_значения_x" опущен, то предполагается, что он совпадает с аргументом "известные_значения_x".

Если опущены оба аргумента — "известные_значения_x" и "новые_значения_x", — то предполагается, что это массивы <1;2;3;. >того же размера, что и "известные_значения_y".

Логическое значение, определяющее, следует ли принудать константе b равной 0

Если аргумент "конст" имеет значение ИСТИНА или опущен, то b вычисляется обычным образом.

Если аргумент "конст" имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось условие y = mx.

Для получения информации о том Microsoft Excel соответствует строке данных, см.

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

Формулы, которые возвращают массивы должны быть введены в качестве массива формулы с Ctrl’Shift-Enter, если у вас есть текущая версия Microsoft 365, и тогда вы можете просто нажать Введите.

При вводе константы массива для аргумента (например, "известные_значения_x") следует использовать точки с запятой для разделения значений в одной строке и двоеточия для разделения строк.

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

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Метод наименьших квадратов в Excel — использование функции ТЕНДЕНЦИЯ

Метод наименьших квадратов — это математическая процедура составления линейного уравнения, максимально соответствующего набору упорядоченных пар, путем нахождения значений для a и b, коэффициентов в уравнении прямой. Цель метода наименьших квадратов состоит в минимизации общей квадратичной ошибки между значениями y и ŷ. Если для каждой точки мы определяем ошибку ŷ, метод наименьших квадратов минимизирует:

где n = число упорядоченных пар вокруг линии. максимально соответствующей данным.

Это понятие проиллюстрировано на рисунке

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

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

Поскольку своей целью мы определили задачу узнать, увеличивается ли со временем число предметов, «Месяц» будет независимой переменной, а «Число предметов» — зависимой.

С помощью метода наименьших квадратов определяем уравнение, максимально соответствующее данным, путем вычисления значений a, отрезка на оси y, и b, наклона линии:

где x_ср — среднее значение x, независимой переменной, y_ср — среднее значение y, независимой переменной.

В таблице ниже суммированы необходимые для этих уравнений вычисления.

Кривая эффекта для нашего примера с ванной будет определяться следующим уравнением:

Поскольку наше уравнение имеет положительный наклон — 0.976, парень имеет доказательство того, что число предметов на столике со временем увеличивается со средней скоростью 1 предмет в месяц. На графике представлена кривая эффекта с упорядоченными парами.

Ожидание в отношении числа предметов в течение следующего полугода (месяца 16) будет вычисляться так:

ŷ = 5.13 + 0.976x = 5.13 + 0.976(16)

20.7 = 21 предмет

Так что, пора нашему герою предпринимать какие-нибудь действия.

Функция ТЕНДЕНЦИЯ в Excel

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

ТЕНДЕНЦИЯ (известные значения Y; известные значения X; новые значения X; конст)

известные значения Y – массив зависимых переменных, в нашем случае, количество предметов на столике

известные значения X – массив независимых переменных, в нашем случае это месяц

новые значения X – новые значения X (месяца) для которого функция ТЕНДЕНЦИЯ возвращает ожидаемое значение зависимых переменных (количество предметов)

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

Например, на рисунке показана функция ТЕНДЕНЦИЯ, используемая для определения ожидаемого количества предметов на столике в ванной для 16-го месяца.