3.4.3.2 Функции excel, связанные с инструментом «Регрессия»

Во многих случаях нет необходимости в полном регрессионном анализе. Тогда возможно использование некоторых функций программы MS EXCEL, в той или иной мере связанных с регрессионным анализом: НАКЛОН, ОТРЕЗОК, СТОШУХ, ЛИНЕЙН, ПРЕДСКАЗ, ТЕНДЕНЦИЯ, ЛГРФПРИБЛ, РОСТ. (Кроме того, в отличие от инструмента «Регрессия», функции ЛГРФПРИБЛ и РОСТ определяют нелинейную регрессию, см. ниже.)

Первые три из отмеченных выше функций имеют всего два аргумента: «известные значения y» и «известные значения x», т.е. все они имеют сходные окна (на рис. 4.4 представлено окно функции НАКЛОН).

Функция НАКЛОН устанавливает наклон линии линейной регрессии для точек данных, образованных известными значениями y_i и известными значениями x_i. Наклон определяется как частное от деления расстояния по вертикали на расстояние по горизонтали между двумя точками прямой, т.е. наклон - это скорость изменения значений вдоль прямой. Иначе говоря, результат вычисления функции НАКЛОН представляет собой коэффициент a₁ в уравнении регрессии (Результат расчёта этой и других функций, определяющих одно значение, появляется в окне аргументов сразу после внесения данных до нажатия«ОК».)

Рис. 4.4. Аргументы функции НАКЛОН

Функция ОТРЕЗОК, имеющая те же аргументы (известные значения x и известные значения y), что и функция НАКЛОН (рис. 4.4), вычисляет точку пересечения линии парной линейной регрессии с осью y, т.е. при x = 0. Иначе говоря, результат вычисления функции ОТРЕЗОК представляет собой коэффициент a₀ в уравнении регрессии

Функция СТОШУХ, имеющая те же аргументы, что и функции НАКЛОН, ОТРЕЗОК (рис. 4.4), принципиально отличается от них - она определяет не усреднённые результаты расчёта регрессионной модели, а стандартную ошибку предсказанных значений y в этой модели. Стандартная ошибка - это мера среднего рассеивания наблюдаемых значений (точек) вокруг подобранной линии регрессии, дающая некоторое представление о надежности уравнения регрессии для производства прогнозных расчетов. (Эта величина присутствует также в таблице «Регрессионная статистика» результатов регрессионного анализа.) Для парной регрессии стандартная ошибка оценки определяется следующим образом:

(4.3)

где - i-е фактическое значение результативного признака;

- i-е теоретическое значение результативного признака, т.е. полученное по уравнению регрессии для .

Функция ЛИНЕЙН (рис. 4.5) рассчитывает статистику для ряда с применением метода наименьших квадратов путём вычисления линейной зависимости, которая наилучшим образом аппроксимирует имеющиеся данные.

Рис. 4.5. Аргументы функции ЛИНЕЙН

Функция ЛИНЕЙН, в отличие от предыдущих функций, может использоваться для расчёта множественной регрессии (в случае задания нескольких диапазонов значений x), имеющей вид

y = m₁x₁ + m₂x₂ + ... + b. (4.4)

Если массив «известные значения y» в данном случае (рис. 4.5) и в других функциях задан столбцом, то каждый столбец массива «известные значения x» интерпретируется как отдельная переменная. Наоборот, если массив «известные значения y» задан строкой, то каждая строка массива «известные значения x» интерпретируется как отдельная переменная. При вводе «вручную» массива констант в качестве, например аргумента «известные значения x», следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. (Знаки-разделители могут быть различными в зависимости от настроек, заданных в окне «Язык и стандарты», открываемом с панели управления.)

Аргумент «Конст» (рис. 4.5) в данном случае и в других функциях - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если «Конст» имеет значение ИСТИНА или отсутствует, то b вычисляется обычным образом. Если «Конст» имеет значение ЛОЖЬ (ставится значение 0), то b в уравнении (4.4) полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение

y = m₁x₁+ ... + m_nx_n.

Статистика - логическое значение, которое указывает, требуются ли дополнительные статистические данные по регрессии. Если аргумент «Статистика» имеет значение 0 («ЛОЖЬ») или отсутствует, то функция ЛИНЕЙН подсчитает только коэффициенты регрессии. Если в качестве аргумента «Статистика» представлено любое другое число («ИСТИНА»), то производится расчёт дополнительной статистики.

Если для вывода результатов расчёта в MS EXCEL требуется не одна ячейка, необходимо предварительно выделить массив ячеек. Для функции ЛИНЕЙН необходимый массив состоит из пяти строк и числа столбцов, равного n +1. После введения всех аргументов необходимо набрать комбинацию клавиш CTRL+SHIFT+ENTER.(При нажатии «ОК» будет подсчитан только коэффициент при x.)

В дополнительную регрессионную статистику входят:

- стандартные значения ошибок коэффициентов m₁, m₂,... и постоянной b;

- коэффициент детерминации;

- стандартная ошибка для оценки y;

- F-статистика (F-наблюдаемое значение);

- степени свободы для нахождения F-критических значений;

- регрессионная сумма квадратов (sstotal);

- сумма квадратов остатков (ssresid).

Тогда сумма квадратов, обусловленных регрессией, может быть получена следующим образом: ssreg = sstotal - ssresid. То есть в рамках функции ЛИНЕЙН могут быть решены и задачи дисперсионного анализа. Таким образом, возможности функции ЛИНЕЙН с дополнительной статистикой в значительной степени сравнимы с возможностями инструмента «Регрессия». К её недостаткам относится то, что выводимые в определённом порядке результаты никак не обозначены. Поэтому для получения полного регрессионного анализа рекомендуется пользоваться инструментом «Регрессия».

Функция ТЕНДЕНЦИЯ (рис. 4.6) определяет значения в соответствии с линейным трендом; аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные значения y и известные значения x; рассчитывает значения y в соответствии с этой прямой для заданного массива новых значений x.

Рис. 4.6. Аргументы функции ТЕНДЕНЦИЯ

В аргументах функции ТЕНДЕНЦИЯ (рис. 4.6) массив «известные значения x» может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные значения y и известные значения x могут задаваться в любой форме при условии, что они имеют одинаковую размерность. Если в «известных значениях x» используется более одной переменной, то известные значения y должны быть вектором (т.е. интервалом высотой в одну строку или шириной в один столбец).

Аргумент «Новые значения x» - новые значения x, для которых ТЕНДЕНЦИЯ рассчитывает соответствующие значения y. «Новые значения x» должны содержать такое же количество (и так же расположенных) столбцов или строк, как и «известные значения x».

Можно использовать функцию ТЕНДЕНЦИЯ (для одного фактора x) для аппроксимации полиномиальной кривой, проводя регрессионный анализ для той же переменной, предварительно возведённой в различные степени. (Эффективнее использовать для этой цели графические опции, описанные в главе 5.)

Функция ПРЕДСКАЗ рассчитывает для парной регрессии прогнозируемое значение результативного признака в соответствии с линейным трендом (рис. 4.7).

Рис. 4.7. Аргументы функции ПРЕДСКАЗ

Функция ПРЕДСКАЗ является частным случаем функции ТЕНДЕНЦИЯ, когда последняя применяется к парной регрессии и её аргумент «новые значения х» имеет размерность в одну ячейку. Аргумент «х»: точка данных, для которой предсказывается значение у (рис. 4.7).

Функция ЛГРФПРИБЛ (рис. 4.8) сходна с функцией ЛИНЕЙН, но в отличие от последней аппроксимирует связь исследуемых параметров не прямой линией, а экспоненциальной кривой.

Уравнение кривой в случае нескольких массивов x имеет вид

y = b×(m₁^x1) ×…×(m_n^xn), (4.5)

где значения m являются основанием, возводимым в степень x, а значения b постоянны.

Рис. 4.8. Аргументы функции ЛГРФПРИБЛ

«Конст» (рис. 4.8) - логическое значение, как и в функциях, описывающих линейную регрессию. Но в данном случае оно указывает, требуется ли, чтобы константа b была равна 1 (единице). Если «Конст» имеет значение ИСТИНА или отсутствует, то b вычисляется обычным образом. Если «Конст» имеет значение ЛОЖЬ, то значения m подбираются так, чтобы выполнялось b = 1.

При выведении полной статистики (опция «Статистика») её структура полностью соответствует структуре, выводимой в этом случае функцией ЛИНЕЙН, и включает коэффициенты регрессии, их стандартные отклонения, число степеней свободы, коэффициент детерминации и суммы квадратов остатков.

Функция РОСТ (рис. 4.9) также применяется для аппроксимации связи x- и y-значений экспоненциальной кривой, но по сравнению с функцией ЛГРФПРИБЛ решает более скромные задачи - рассчитывает значения y для новых значений x, не касаясь рассеяния и статистики.

Рис. 4.9. Аргументы функции РОСТ