Тема 3. Линейная множественная регрессия

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

(3.1)

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

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

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

Таблица 3.1

Номер шахты i	xi1	xi2	yi
1	8	5	5
2	11	8	10
3	12	8	10
4	9	5	7
5	8	7	5
6	8	8	6
7	9	6	6
8	9	4	5
9	8	5	6
10	12	7	8

Лабораторная работа № 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)