Проведение регрессионного анализа с использованием функции “ЛИНЕЙН”.

При подготовке к четвертому вопросу следует обратить внимание на то что,функция ЛИНЕЙН возвращают данные регрессионного анализа, включая наклон и смещение графика относительно оси Y. Функция рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Задание функции в виде формулы массива означает, что ее необходимо заключить в фигурные скобки. Практически это осуществляется нажатием сочетания клавиш CTRL+SHIFT+ENTER.

Уравнение для прямой линии имеет следующий вид:

y = mx + b или y = m1x1 + m2x2 +... + b

(в случае нескольких диапазонов значений x)

где зависимое значение y является функцией независимого значения x. Значения m — это коэффициенты, соответствующие каждой независимой переменной x, а b — это постоянная. Заметим, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn;mn-1;...;m1;b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику (см. Справку).

Задание 5. Выполнить расчет коэффициентов регрессии, используя данные Таблицы 5.

Таблица 5.

Для этого используем синтаксис:

ЛИНЕЙН (известные_значения_y;известные_значения_x;конст; статистика)

Известные_значения_y — это множество значений y, которые уже известны в соотношении y = mx + b.

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

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

Известные_значения_x — это необязательное множество значений x, которые уже известны в соотношении y = mx + b.

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

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

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

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

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

Статистика — это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

· Если аргумент статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику (см. Справку)

· Если аргумент статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

Замечания:

· Любую прямую можно описать ее наклоном и пересечением с осью y: Наклон (m). Для того, чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x1,y1) и (x2,y2); тогда наклон равен (y2 - y1)/(x2 - x1).

Y-пересечение (b): Y-пересечением прямой, обычно обозначаемым через b, является значение y для точки, в которой прямая пересекает ось y.

Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любyю точку на прямой, подставляя значения y или x в уравнение. Можно также использовать функцию ТЕНДЕНЦИЯ.

· Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией ЛИНЕЙН. Функция ЛИНЕЙН использует метод наименьших квадратов для определения наилучшей аппроксимации данных.

· Функции аппроксимации ЛИНЕЙН и ЛГРФПРИБЛ могут вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую данные. Однако они не дают ответа на вопрос, какой из двух результатов в наибольшей степени подходит для решения поставленной задачи. Можно также вычислить функцию ТЕНДЕНЦИЯ (известные_значения_y; известные_значения_x) для прямой или функцию РОСТ (известные_значения_y; известные_значения_x) для экспоненциальной кривой. Эти функции, если не задавать аргумент новые_значения_x, возвращают массив вычисленных значений y для фактических значений x в соответствии с прямой или кривой. Теперь можно сравнить вычисленные значения с фактическими значениями. Можно также построить диаграммы для визуального сравнения.

· Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов. Затем Microsoft Excel подсчитывает сумму квадратов разностей между фактическими значениями y и средним значением y, которая называется общей суммой квадратов (регрессионая сумма квадратов + остаточная сумма квадратов). Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности R2, который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными.

· Заметьте, что значения y, предсказанные с помощью уравнения регрессии, возможно не будут правильными, если они располагаются вне интервала значений y, которые использовались для определения уравнения.

Результат выполнения Задания 5 см. в таблице 6.

Таблица 6.