Файл: Lab-Reg2-1.doc

дополнительную статистику по регрессии. Если статистика имеет значение ИСТИНА (статистика=1), то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид: {b_n;b_n-1;...;b₁;b₀:se_n;se_n-1;...;se₁;se₀: R²;se_v:F;df:ss_reg;ss_resid}. Если статистика имеет значение ЛОЖЬ (статистика=0) или опущена, то функция ЛИНЕЙН возвращает только коэффициенты b и постоянную b₀.

В приведенной ниже таблице показано, в каком порядке возвращается дополнительная регрессионная статистика.

Таблица 2.

В таблице 3 приведено описание дополнительной регрессионной статистики.

Таблица 3.

Дополнительную информацию смотрите в разделе справочной информации мастера функций.

4.2. Определение коэффициентов регрессии и параметров

РЕГРЕССИОННОЙ СТАТИСТИКИ

Регрессионный анализ выборки наблюдений будем проводить в рабочем листе «Расчет». В указанном рабочем листе при помощи функции ЛИНЕЙН определим коэффициенты уравнения регрессии вида 1) P = b₀ + b₁  U + b₂  T_. Для этого выделим в рабочем листе диапазон ячеек I2:K6 (число столбцов определяется по количеству коэффициентов уравнения регрессии), установим курсор мыши в строку формул и введем следующую формулу: =ЛИНЕЙН(Матрицы!B2:B53;Матрицы!C2:D53;1;1),

где Матрицы!B2:B53 - диапазон матрицы зависимой переменной (функция давления P_i);

где Матрицы!C2:D53 - диапазон матрицы независимых переменных (в данном случае U_i и T_i).

Поскольку функция ЛИНЕЙН возвращается массив значений, ввод формулы (или редактирование) завершаем нажатием клавиш Ctrl-Shift-Enter.

Используя описания регрессионной статистики таблиц 2 и 3 находим в полученном массиве значения коэффициентов регрессии b_n;b_n-1;...;b₁;b₀ и коэффициент детерминации R².

4.3. Проверка значимости коэффициентовI

Для проверки значимости коэффициентов регрессии используем t-статистику. Для проверки неравенства (14) в ячейке H7 вычисляем критическое значение распределения Стьюдента по уровню значимости a = 0,05 и n-(k+1) степеням свободы с помощью встроенной функции:

СТЬЮДРАСПОБР(0.05; <степеньсвободы>).

Определим оценочные значения (14) для коэффициентов уравнения регрессии . Для этого в ячейкиI7,J7,K7 введем формулы =ABS(I2/I3), =ABS(J2/J3), и =ABS(K2/K3) соответственно.

Вычисленное соотношение (14), сравниваем с критическим значением t_кр и делаем вывод о значимости коэффициентов регрессии для каждого уравнения.

4.4. Проверка значимости уравнения регрессии

Для оценки значимости уравнения регрессии используется критерий Фишера. Наблюдаемое значение F_набл в формуле (15) определим из таблицы регрессионной статистики (табл 2,3).

Критическое значение F-распределения Фишера F_кр с (k;n-k-1) степенями свободы определяем с помощью встроенной функции:

=FРАСПОБР(0.05;k;n-k-1) - где n - число наблюдений, k - число независимых параметров уравнения.

Проводим сравнение F_набл и F_кр.

4.5. Вычисление расчетных значений регрессии и построение диаграмм

Для определения расчетных значений функции давления, подставим в уравнение регрессии с вычисленными коэффициентами регрессии текущие значения независимых параметров. Для этого в ячейку C2 введем следующую формулу: =$K$2+$J$2*Матрицы!C2+I$2*Матрицы!D2. Распространим эту формулу на весь диапазон наблюдений, т.е. на диапазон ячеек С2:С53.

Для сравнительного анализа и графической интерпретации расчетных и экспериментальных наблюдений функции давления построим точечную диаграмму по времени (Рис.1).

Рис.1.

4.6. Построение других регрессионных моделей

При оценке параметров функции давления с помощью уравнения регрессии вида 2) из (17):

P_i = b₀ + b₁  U_i + b₂  M_i

построим матрицу независимых переменных на рабочем листе «Матрицы»_.

В столбец F заполним значения первой независимой переменной U_i, а столбец G заполним значения второй независимой переменной численно равной значениям расхода топлива М_i (табл.4). При обращении к функции ЛИНЕЙН в качестве второго параметра будет указан диапазон матрицы независимых переменных F2:G53. Далее обработка и регрессионный анализ проводится в соответствии с разделами 4.1.-4.5.

Оценка уравнений регрессии проводится для всех регрессионных моделей (17). В таблице 5 приведен типовой расчет коэффициентов уравнений регрессии и параметров регрессионной статистики для различных моделей регрессии.

Таблица 4.

Таблица 5.

Типовой расчет коэффициентов уравнений регрессии и параметров

регрессионной статистики для различных моделей регрессии