|
|
Примеры использования функций ЛИНЕЙН и ТЕНДЕНЦИЯ
Функция ТЕНДЕНЦИЯ Сокращённый синтаксис функции ТЕНДЕНЦИЯ аналогичен синтаксису функции ЛИНЕЙН, также в качестве аргументов используются два массива «известные_значения_y» и «известные_значения_x», при этом второй массив также может быть опущен. Также выполняется аппроксимация прямой линией (по методу наименьших квадратов). Но возвращает функция ТЕНДЕНЦИЯ не параметры регрессионной модели, а сразу смоделированные значения, соответствующие линейному тренду. Ещё два аргумента содержит полный синтаксис функции: ТЕНДЕНЦИЯ (известные_значения_y; известные_значения_x; новые_значения_x; конст). Функция возвращает значения y, соответствующие линии тренда, рассчитанной для массива «новые_значения_x». Приведём справочные сведения обо всех четырёх аргументах функции ТЕНДЕНЦИЯ: Известные_значения_y – множество значений y, которые уже известны для соотношения y = аx + b. Если массив «известные_значения_y» имеет один столбец, то каждый столбец массива «известные_значения_x» интерпретируется как отдельная переменная. Если массив «известные_значения_y» имеет одну строку, то каждая строка массива «известные_значения_x» интерпретируется как отдельная переменная. Известные_значения_x – необязательное множество значений x, которые уже известны для соотношения y = аx + b. Массив «известные_значения_x» может содержать одно или несколько множеств переменных. Если используется только одна переменная, то аргументы «известные_значения_y» и «известные_значения_x» могут быть диапазонами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то аргумент «известные_значения_y» должен быть вектором (то есть диапазоном высотой в одну строку или шириной в один столбец). Если аргумент «известные_значения_x» опущен, то предполагается, что это массив натуральных чисел {1;2;3;...} того же размера, что и массив «известные_значения_y». Новые_значения_x – новые значения x, для которых функция ТЕНДЕНЦИЯ возвращает соответствующие значения y. Аргумент «новые_значения_x», так же, как и аргумент «известные_значения_x», должен содержать по одному столбцу (или строке) для каждой независимой переменной. Таким образом, если «известные_значения_y» – это один столбец, то «известные_значения_x» и «новые_значения_x» должны иметь одинаковое количество столбцов. Если «известные_значения_y» – это одна строка, то аргументы «известные_значения_x» и «новые_значения_x» должны иметь одинаковое количество строк. Если аргумент «новые_значения_x» опущен, то предполагается, что он совпадает с аргументом «известные_значения_x». Если опущены оба аргумента – «известные_значения_x» и «новые_значения_x», – то предполагается, что это массивы {1;2;3;...} того же размера, что и «известные_значения_y». Конст – логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент «конст» имеет значение ИСТИНА или опущен, то b вычисляется обычным образом. Если аргумент «конст» имеет значение ЛОЖЬ, то b полагается равным 0 и значения а подбираются таким образом, чтобы выполнялось условие y = аx.
Замечание. Формулы, которые возвращают массивы, должны быть введены как формулы массива. При вводе константы массива для таких аргументов, как «известные_значения_x», следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. В данном примере нижняя часть таблицы содержит следующую формулу: =ТЕНДЕНЦИЯ(B2:B13;A2:A13;A15:A19).
Простая линейная регрессия Имеются сведения о выручке магазина за полгода. Предполагая, что продажи растут линейно, можно оценить количество продаж в последующие месяцы. Функция ЛИНЕЙН строит по экспериментальным данным регрессионную модель и возвращает коэффициенты, описывающие аппроксимирующую прямую Продажи = a Месяц + b. Это уравнение можно использовать для оценки продаж в любой месяц. Например, для оценки продаж в восьмой месяц можно использовать следующую формулу =СУММПРОИЗВ(ЛИНЕЙН(B2:B7; A2:A7);{8;1}). На рисунке соответствующая ячейка выделена зелёным цветом. Стоит напомнить, что функция СУММПРОИЗВ({а;b};{x;1}) возвратит значение, соответствующее выражению аx + b. А функция ЛИНЕЙН без дополнительных аргументов как раз и возвращает массив из двух коэффициентов. Меняя х (номер месяца), можно находить оценки соответствующих месяцу объёмов продаж.
Для этих же целей можно воспользоваться функцией ТЕНДЕНЦИЯ. Так, если в верхнюю ячейку с жёлтой заливкой ввести формулу =ТЕНДЕНЦИЯ($B$2:$B$7;$A$2:$A$7;A9), то её можно скопировать и в нижние ячейки.
|
|