Ход работы. 1. Для построения регрессионной прямой по методу наименьших квадратов можно воспользоваться двумя подходами: 1) решить систему линейных алгебраических

1. Для построения регрессионной прямой по методу наименьших квадратов можно воспользоваться двумя подходами: 1) решить систему линейных алгебраических уравнений, корнями которой будут коэффициенты и уравнения прямой регрессии (6.2); 2) воспользоваться встроенными функциями Microsoft Excel. Опишем оба подхода к решению задачи.

· Вычислите вспомогательные суммы xi, yi, xi^2, yi*xi (функция СУММ (диапазон_значений) или кнопка на палитре инструментов со знаком 'сигма'), а также подсчитайте количество точек данных (функция СЧЕТ (диапазон_значений)). Составьте матрицу из полученных значений согласно формулам метода наименьших квадратов.

· Для вычисления корней системы можно 1) найти обратную матрицу (функция МОБР (массив), при том надо помнить следующее, для того чтобы получить матрицу необходимо сначала выделить тот диапазон ячеек, в которых предполагается получить значения элементов обратной матрицы, затем ввести функцию в строке формул, и, наконец, нажать комбинацию клавиш CTRL+SHIFT+ENTER. Если нажать одну клавишу ENTER, то Excel вычислит только первый элемент матрицы. Это относится ко всем функциям массива Excel). Умножить обратную матрицу на столбец свободных членов (функция МУМНОЖ (массив1; массив2). Это также функция массива). В результате получаются коэффициенты прямой. 2) по методу Крамера вычислить определители системы и вспомогательные определители переменных (функция МОПРЕД (матрица) – для получения результата достаточно нажать ENTER). Затем вычислить корни системы – коэффициенты прямой.

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

Функция ЛИНЕЙН

Синтаксис:

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

Извест_значения_y – это множество значений y, которые уже известны для соотношения (6.2).

Замечания:

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

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

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

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

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

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

· Если статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид: {mn;mn-1;...;m1;b:sen;sen1;...;se1; seb:r2;sey:F;df:ssreg; ssresid}. Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициенты и постоянную .

Функция ИНДЕКС

Синтаксис:

ИНДЕКС (массив;номер_строки;номер_столбца)

Массив – это интервал ячеек или массив констант. Номер_строки – это номер строки в массиве, из которой нужно возвращать значение. Если номер_строки опущен, то аргумент номер_столбца нужно задавать обязательно. Номер_столбца – это номер столбца в массиве, из которого нужно возвращать значение.

Замечания:

· Если номер_столбца опущен, то аргумент номер_строки нужно задавать обязательно. Если используются оба аргумента номер_строки и номер_столбца, то функция ИНДЕКС возвращает значение, находящееся в ячейке на пересечении номер_строки и номер_столбца.

· Если массив содержит только одну строку или один столбец, то соответствующий аргумент номер_строки или номер_столбца не является обязательным.

· Если массив занимает больше, чем одну строку и больше, чем один столбец, а задан только один аргумент номер_строки или номер_столбца, то функция ИНДЕКС возвращает массив из целой строки или целого столбца аргумента массив.

· Если задать номер_строки или номер_столбца равным 0 (нулю), то функция ИНДЕКС вернет массив значений для целого столбца или целой строки, соответственно. Для того, чтобы использовать значения, возвращаемые как массив, функцию ИНДЕКС нужно ввести как формулу массива в горизонтальный интервал ячеек. Для ввода формулы массива нажмите клавиши CTRL+SHIFT+ENTER.

Таким образом, для вычисления коэффициента в строке формул запишем:

= ИНДЕКС (ЛИНЕЙН (B2: B20;A2:A20);1).

Для вычисления коэффициента –

= ИНДЕКС (ЛИНЕЙН (B2:B20;A2:A20);2).

Полученные по обоим способам значения коэффициентов прямой будут идентичны.

3. Постройте столбец значений регрессионной прямой, вычисленных по формуле (6.2), где х – известные значения аргумента, и – коэффициенты, вычисленные одним из описанных способов.

4. Постройте графики исходной зависимости и МНК-прямой в одних осях (мастер диаграмм).

5. Вычислите значения оценки СКО для МНК-прямой и погрешности Dх по формуле в точках .

6. Нанесите полученные значения погрешности на тот же график. Для точек МНК-прямой в диалоговом окне Формат рядов данных показать обе планки погрешностей по Х. Сравнить с вычисленными значениями погрешностей.

7. Выполните построение МНК-прямой и вычисление различных статистических параметров с помощью категорией «Регрессия» Пакета анализа Excel. Сравните полученные результаты с результатами предыдущих пунктов работы.

Контрольные вопросы

1. В чем суть метода наименьших квадратов построения линейной эмпирической зависимости? Сформулируйте принцип Лежандра.

2. Что такое остаточная дисперсия? Каковы статистические характеристики остаточной дисперсии при нормальном законе распределения?

3. Какие статистические критерии используются для проверки адекватности модели опытным данным при наличии и отсутствии независимой несмещенной оценки дисперсии?

4. Что такое погрешность? Как определить погрешность эмпирической зависимости?