Воскобойников Ю.Е., Воскобойникова Т.Н. - МУ к лабораторным и контрольным работам - Парный и множественный регрессионный анализ, страница 3

,

имеющая величину = 0.9901.

Задание. Определить по величине «наихудшее» уравнение регрессии.

Тема 3. ЛИНЕЙНАЯ МНОЖЕСТВЕННАЯ РЕГРЕССИЯ

Эта тема включает выполнение лабораторных работ, посвященных построению и исследованию уравнения линейной множественной регрессии вида

(3.1)

Пространственная выборка для построения этого уравнения взята из следующего примера.

Пример 3.1. Данные о сменной добыче угля на одного рабочего (переменная Y – измеряется в тоннах), мощности пласта (переменная X₁ – измеряется в метрах) и уровнем механизации работ в шахте (переменная X₂ – измеряется в процентах), характеризующие процесс добычи угля в 10 шахтах приведены в таблице 3.1.

Предполагая, что между переменными Y, X₁, X₂ существует линейная зависимость, необходимо найти аналитическое выражение для этой зависимости, т.е. построить уравнение линейной регрессии.

Таблица 3.1

Лабораторная работа № 3.1

Вычисление коэффициентов линейной множественной регрессии

Цель работы. Используя пространственную выборку таблицы 3.1 необходимо вычислить вектор коэффициентов

уравнения регрессии (3.1).

Расчетные соотношения. Вектор коэффициентов, найденный методом наименьших квадратов является решением следующей системы уравнений:

,

где - матрица размера , первый столбец которой составлен из 1, а другие два столбца составлены из значений ,т.е. матрица имеет следующую структуру (символы … означают не отображенные элементы)

,

а - вектор, составленный из 10 значений , т.е.

.

Матрица имеет обратную матрицу и тогда вектор коэффициентов вычисляется в виде:

. (3.2)

Матричные функции Excel. Для реализации этой матричной формулы в необходимо выполнить следующие операции: транспонирование; умножение матриц (частный случай – умножение матрицы на вектор); вычисление обратной матрицы. Все эти операции можно реализовать с помощью следующих матричных функций Excel. Для работы с этими функциями можно или а) обратиться к Мастеру функций и выбрать нужную категорию функций, затем указать имя функции и задать соответствующие диапазоны ячеек, или б) ввести с клавиатуры имя функции задать соответствующие диапазоны ячеек.

Транспонирование матрицы осуществляется с помощью функции ТРАНСП (категория функций – Ссылки и массивы). Обращение к функции имеет вид:

ТРАНСП (диапазон ячеек),

где параметр диапазон ячеек задает все элементы транспонируемой матрицы (или вектора).

Умножение матриц осуществляется с помощью функции МУМНОЖ (категория функций – Математические). Обращение к функции имеет вид:

МУМНОЖ(диапазон_1;диапазон_2),

где параметр диапазон_1 задает элементы первой из перемножаемых матриц, а параметр диапазон_2 – элементы второй матрицы. При этом перемножаемые матрицы должны иметь соответствующие размеры (если первая матрица , вторая - , то результатом будет матрица ).

Обращение матрицы (вычисление обратной матрицы) осуществляется с помощью функции МОБР (категория функций – Математические). Обращение к функции имеет вид:

МОБР (диапазон ячеек),

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

При использовании этих функций необходимо соблюдать следующий порядок действий:

• выделить фрагмент ячеек, в которые будет занесен результат выполнения матричных функций (при этом надо учитывать размеры исходных матриц);

• ввести арифметическое выражение, содержащее обращение к матричным функциям Excel;

• одновременно нажать клавиши [Ctrl], [Shift], [Enter]. Если этого не сделать, то вычислится только один элемент результирующей матрицы или вектора.

Решение. Сформируем матрицу и вектор (см. рис. 3.1).

Рис. 3.1. Вычисление коэффициентов множественной регрессии

Затем выполним формирование матрицы , вектора и вычисление вектора по формуле (3.2). Все эти вычисления показаны на рис. 3.1.

Получен вектор коэффициентов и тогда уравнение регрессии (3.1) примет вид:

. (3.3)

Лабораторная работа № 3.2

Вычисление коэффициентов линейной множественной регрессии и проверка значимости в режиме Регрессия

Цель работы. Используя пространственную выборку таблицы 3.1 и используя режим Регрессия необходимо вычислить вектор коэффициентов уравнения регрессии

. (3.4)

Режим Регрессия модуля Анализ данных. Табличный процессор Excel содержит модуль Анализ данных. Этот модуль позволяет выполнить статистический анализ выборочных данных (построение гистограмм, вычисление числовых характеристик и т.д.). Режим работы Регрессия этого модуля осуществляет вычисление коэффициентов линейной множественной регрессии с переменными, построение доверительные интервалы и проверку значимости уравнения регрессии.

Для вызова режима Регрессия модуля Анализ данных необходимо:

• обратиться к пункту меню Сервис;

• в появившемся меню выполнить команду Анализ данных;

• в списке режимов работы модуля Анализ данных выбрать режим Регрессия и щелкнуть на кнопке Ok.

После вызова режима Регрессия на экране появляется диалоговое окно (см. рис. 3.2), в котором задаются следующие параметры:

1. Входной интервал Y – вводится диапазон адресов ячеек, содержащих значения (ячейки должны составлять один столбец).

Рис. 3.2. Диалоговое окно режима Регрессия

1. Входной интервал X – вводится диапазон адресов ячеек, содержащих значения независимых переменных. Значения каждой переменной представляются одним столбцом. Количество переменных не более 16 (т.е. ).

2. Метки – включается если первая строка во входном диапазоне содержит заголовок. В этом случае автоматически будут созданы стандартные названия.

3. Уровень надежности – при включении этого параметра задается надежность при построении доверительных интервалов.

4. Константа-ноль – при включении этого параметра коэффициент .

5. Выходной интервал – при включении активизируется поле, в которое необходимо ввести адрес левой верхней ячейки выходного диапазона, который содержит ячейки с результатами вычислений режима Регрессия.

6. Новый рабочий лист – при включении этого параметра открывается новый лист, в который начиная с ячейки А1 вставляются результаты работы режима Регрессия.

7. Новая рабочая книга - при включении этого параметра открывается новая книга на первом листе которой начиная с ячейки А1 вставляются результаты работы режима Регрессия.

8. Остатки – при включении вычисляется столбец, содержащий невязки .

9. Стандартизованные остатки – при включении вычисляется столбец, содержащий стандартизованные остатки.

10. График остатков – при включении выводятся точечные графики невязки , в зависимости от значений переменных . Количество графиков равно числу переменных .

11. График подбора – при включении выводятся точечные графики предсказанных по построенной регрессии значений от значений переменных . Количество графиков равно числу переменных .

Решение. Первоначально введем в столбец С десять значений первой переменной, в столбец D - десять значений первой переменной (см. рис. 3.2), а в столбец F – десять значений зависимой переменной.

После этого вызовем режим Регрессия и в диалоговом окне зададим необходимые параметры (см. рис. 3.2). Результаты работы приводятся рис. 3.3 – 3.5. Заметим, из-за большой «ширины» таблиц, в которых выводятся результаты работы режима Регрессия, часть результатов помещены в другие ячейки.

Рис. 3.3. Результаты работы режима Регрессия

Дадим краткую интерпретацию показателям, значения которых вычисляются в режиме Регрессия. Первоначально рассмотрим показатели, объединенные названием Регрессионная статистика (см. рис. 3.3).

Множественный - корень квадратный из коэффициента детерминации.

квадрат – коэффициент детерминации .

Нормированный квадрат – приведенный коэффициент детерминации (см. формулу (2.1)).