4. Ход работы

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

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

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

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

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

Синтаксис:

ЛИНЕЙН(извест_значения_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. Вычислите значения оценки СКО для МНК-прямой и погрешности х по формулев точках.

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

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