4. Регрессия

Рис. 3.68. Гистограмма и плотность НР

Математическое моделирование, связанное с инструментальным (через меню) выполнением оптимизационных расчетов, например при определении коэффициентов полинома в задаче прогнозировании графиков нагрузки (разд. 3.7), имеет тот недостаток, что при изменении исходных данных (ошибка, опечатка и др.) требуется повторная процедура оптимизации. Такие повторы не требуются, если имеется функция, ставящая в соответствие заданному набору данных требуемые результирующие величины. Задача прогнозирования нагрузки как случайной величины на основе предшествующих наблюдений относится к классу задач корреляции и регрессии, где производится оценка корреляционных и регрессионных характеристик по выборке. Для случайного вектора (t,P) выборка (объема n) дает n пар значений признака (t₁,P₁), (t₂,P₂),…, (t_n,P_n). В этом случае говорят о связной выборке. Аппарат теорий корреляций и регрессий позволяет оценить характеристики этой связи. В Excel имеется набор статистических функций (ЛИНЕЙН(), ПРЕДСКАЗ(), ТЕНДЕНЦИЯ(), НАКЛОН(), ЛГРФПРИБЛ () и др. ), которые могут выполнить их расчет.

Функция ЛИНЕЙН(y; x; конст; статистика)

Эта функция использует метод наименьших квадратов, чтобы вычислить параметры линейной зависимости, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив параметров, который описывает данную линейную зависимость. Уравнение для линейной функции имеет следующий вид:

y = mx + b,

где зависимое значение y является функцией независимого значения x.

При большом числе факторов y = m₁x₁ + m₂x₂ + ... + b функция ЛИНЕЙН() возвращает массив {m_n;m_n-1;...;m₁;b}. Функция ЛИНЕЙН() может также возвращать дополнительную регрессионную статистику. В этом случае синтаксис функции - ЛИНЕЙН(Y; X; конст; статистика),

где Y - это заданное множество значений y. Если массив Y имеет один столбец (одну строку), то каждый столбец (каждая строка) массива X интерпретируется как отдельная переменная;

X - это необязательное множество значений параметра x (по умолчанию ряд {х=1,2,....}), которые соотносятся с y = mx + b. Массив X может содержать одно или несколько множеств (строк, столбцов) переменных. Массивы Y и X должны иметь одинаковую размерность;

конст - это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx;

Рис. 3.69. Статистики

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

Дополнительная регрессионая статистика

Параметры se₁,se₂,...,se_n, se_b - стандартные значения ошибок для коэффициентов m₁,m₂,...,m_n и для постоянной b (se_b = #Н/Д, если параметр конст имеет значение ЛОЖЬ);

r²- коэффициент детерминированности (квадрат коэффициента корреляции). Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями y; если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y;

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

F - F-статистика, или F-наблюдаемое значение. F - статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет;

df - степени свободы. Степени свободы полезны для нахождения F-критических значений (распределение Фишера) в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН();

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

ss_resid - остаточная сумма квадратов.

Замечание. Если имеется только одна независимая переменная x, можно получить крутизну и y-пересечение непосредственно, используя функции НАКЛОН() и ИНДЕКС(ЛИНЕЙН(Y;X); 2)

Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН(), зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией ЛИНЕЙН(). Когда имеется только одна независимая переменная x, m и b вычисляются по следующим формулам:

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

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

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