 |
Матрица Х с дополнительным единичным столбцом.
|
|
|
|
| Кол-во комнат | Район | План. | Материал стен | Этаж | Этажность | Sоб | Sжил | Sкух | Тел. | Санузел | Балкон/ лоджия | Плита | |
| 4,00 | 13,00 | 5,00 | 1,00 | 1,00 | 9,00 | 82,00 | 51,00 | 9,00 | 1,00 | 3,00 | 1,00 | 1,00 | |
| 3,00 | 10,00 | 5,00 | 2,00 | 6,00 | 9,00 | 64,00 | 40,00 | 9,00 | 1,00 | 3,00 | 3,00 | 1,00 | |
| 4,00 | 11,00 | 5,00 | 1,00 | 8,00 | 10,00 | 83,00 | 50,00 | 9,00 | 1,00 | 3,00 | 2,00 | 3,00 | |
| 3,00 | 8,00 | 3,00 | 1,00 | 5,00 | 5,00 | 61,00 | 45,00 | 6,00 | 1,00 | 3,00 | 2,00 | 3,00 | |
| 3,00 | 10,00 | 5,00 | 2,00 | 3,00 | 5,00 | 120,00 | 80,00 | 12,00 | 2,00 | 1,00 | 2,00 | 3,00 | |
| 3,00 | 10,00 | 2,00 | 1,00 | 2,00 | 5,00 | 63,00 | 40,00 | 8,00 | 1,00 | 3,00 | 3,00 | 3,00 | |
| 5,00 | 42,00 | 5,00 | 1,00 | 4,00 | 9,00 | 98,00 | 65,00 | 9,00 | 1,00 | 1,00 | 1,00 | 3,00 | |
| 4,00 | 13,00 | 5,00 | 1,00 | 1,00 | 9,00 | 82,00 | 50,00 | 9,00 | 1,00 | 3,00 | 2,00 | 3,00 | |
| 4,00 | 15,00 | 3,00 | 1,00 | 4,00 | 5,00 | 64,00 | 43,00 | 7,00 | 1,00 | 1,00 | 1,00 | 3,00 | |
| 1,00 | 3,00 | 1,00 | 1,00 | 1,00 | 5,00 | 42,00 | 18,00 | 9,00 | 1,00 | 3,00 | 2,00 | 3,00 | |
| 4,00 | 15,00 | 3,00 | 1,00 | 1,00 | 5,00 | 62,00 | 48,00 | 6,00 | 1,00 | 2,00 | 1,00 | 2,00 | |
| 3,00 | 8,00 | 3,00 | 1,00 | 3,00 | 5,00 | 48,00 | 26,00 | 7,00 | 2,00 | 3,00 | 2,00 | 2,00 | |
| 3,00 | 8,00 | 3,00 | 2,00 | 2,00 | 5,00 | 63,00 | 48,00 | 6,00 | 1,00 | 3,00 | 2,00 | 3,00 | |
| 3,00 | 9,00 | 2,00 | 1,00 | 1,00 | 5,00 | 63,00 | 40,00 | 9,00 | 1,00 | 3,00 | 3,00 | 3,00 | |
| 3,00 | 9,00 | 5,00 | 1,00 | 6,00 | 10,00 | 68,00 | 40,00 | 12,00 | 1,00 | 3,00 | 2,00 | 3,00 | |
| 3,00 | 10,00 | 5,00 | 1,00 | 2,00 | 9,00 | 64,00 | 40,00 | 9,00 | 2,00 | 2,00 | 2,00 | 3,00 | |
| 4,00 | 13,00 | 5,00 | 1,00 | 2,00 | 9,00 | 82,00 | 50,00 | 9,00 | 1,00 | 3,00 | 4,00 | 3,00 | |
| 4,00 | 11,00 | 5,00 | 1,00 | 10,00 | 10,00 | 81,00 | 50,00 | 12,00 | 1,00 | 1,00 | 4,00 | 1,00 | |
| 5,00 | 51,00 | 5,00 | 1,00 | 4,00 | 9,00 | 99,00 | 65,00 | 9,00 | 1,00 | 3,00 | 4,00 | 3,00 | |
| 3,00 | 8,00 | 3,00 | 1,00 | 2,00 | 5,00 | 62,00 | 45,00 | 7,00 | 1,00 | 2,00 | 2,00 | 2,00 |
Чтобы получить матрицу ХТ, строим матрицу X, копируем ее и затем, нажав правую кнопку мыши ->Специальная вставка->Транспонирование получаем матрицу XT. (Рисунок 2).
Рисунок 2. Использование инструмента Специальная вставка в Excel.
Используя функцию МУМНОЖ находим произведение матриц.
Как правильно использовать функцию МУМНОЖ в Excel?
Замечания
Количество столбцов первой матрицы должно совпадать с количеством строк аргумента второй; при этом обе матрицы должны содержать только числа.
|
|
|
Функция МУМНОЖ возвращает значение ошибки #ЗНАЧ! в следующих случаях:
Если какая-либо ячейка пуста или содержит текст.
Если число столбцов в аргументе «массив1» отличается от числа строк в аргументе «массив2».
Выполните следующие действия
1. Выделите диапазон ячеек, так, чтобы количество строк было равно количеству строк матрицы XT, количество столбцов равно количеству столбцов матрицы X.
2. В строке формул ввести знак «=», затем выбрать функцию МУМНОЖ с помощью мастера формул. (Рисунок 3).
Рисунок 3. Использование функции МУМНОЖ в Excel.
3. В массив1 введите диапазон матрицы X, в массив2 – диапазон матрицы XT при этом не выделяйте заголовок строки или столбца.
4. Нажмите сочетание клавиш CTRL+SHIFT+ВВОД. Если формула не будет введена как формула массива, в ячейке, куда вводилась формула, будет единственное значение.
Обращаем матрицу XTX с помощью функции МОБР (аналогично используем сочетание клавиш CTRL+SHIFT+ВВОД для получения результата).
С помощью функции МУМНОЖ находим произведение 
, затем произведение 
, т.е вектор 
.
Таким образом, получаем коэффициенты регрессии (Таблица 6).
Таблица 6.
Коэффициенты регрессии.
| Параметры | Условное обозначение | Коэффициенты | |
| Свободный член | 
| 3378,409982 | |
| Количество комнат | 
| -494,5901574 | |
| Район | 
| -35,00366057 | |
| Планировка | 
| 75,7406312 | |
| Материал стен | 
| -15,80521891 | |
| Этаж | 
| 80,10442546 | |
| Этажность | 
| 59,83505707 | |
| Sоб | 
| 127,975324 | |
| Sжил | 
| -78,09928757 | |
| Sкух | 
| -437,5724046 | |
| Телефон | 
| 451,2625396 | |
| Санузел | 
| -299,9117913 | |
| Балкон/лоджия | 
| -14,93231822 | |
| Плита | 
| -369,6484116 |
Замечание: Эти коэффициенты должны совпасть с коэффициентами из таблицы 3.
Расчет стандартной ошибки.
Для получения оценки стандартной ошибки сформируем вектор, состоящий из диагональных элементов уже имеющейся у нас матрицы 
(Таблица 7).
|
|
|
Таблица 7.
Матрица
| 21,39 | -2,80 | 0,07 | 2,78 | -2,53 | 0,00 | -1,06 | 0,15 | -0,22 | -0,82 | -3,56 | -1,07 | 0,39 | -0,35 |
| -2,80 | 0,65 | -0,01 | -0,23 | 0,47 | -0,01 | 0,02 | -0,01 | 0,00 | 0,15 | 0,28 | 0,10 | -0,05 | 0,07 |
| 0,07 | -0,01 | 0,00 | 0,02 | -0,01 | 0,00 | -0,01 | 0,00 | 0,00 | 0,00 | -0,01 | 0,00 | 0,00 | 0,00 |
| 2,78 | -0,23 | 0,02 | 0,97 | -0,38 | 0,00 | -0,48 | 0,01 | -0,05 | 0,03 | -0,90 | -0,05 | 0,06 | 0,08 |
| -2,53 | 0,47 | -0,01 | -0,38 | 1,04 | -0,02 | 0,16 | 0,00 | -0,01 | 0,05 | 0,26 | -0,03 | -0,04 | 0,09 |
| 0,00 | -0,01 | 0,00 | 0,00 | -0,02 | 0,02 | -0,01 | 0,00 | 0,00 | 0,00 | 0,01 | 0,02 | -0,01 | 0,00 |
| -1,06 | 0,02 | -0,01 | -0,48 | 0,16 | -0,01 | 0,29 | -0,01 | 0,03 | -0,06 | 0,45 | -0,02 | -0,01 | -0,03 |
| 0,15 | -0,01 | 0,00 | 0,01 | 0,00 | 0,00 | -0,01 | 0,01 | -0,01 | -0,02 | -0,03 | -0,01 | 0,00 | 0,00 |
| -0,22 | 0,00 | 0,00 | -0,05 | -0,01 | 0,00 | 0,03 | -0,01 | 0,01 | 0,01 | 0,07 | 0,02 | 0,00 | 0,00 |
| -0,82 | 0,15 | 0,00 | 0,03 | 0,05 | 0,00 | -0,06 | -0,02 | 0,01 | 0,12 | 0,00 | 0,07 | -0,03 | 0,02 |
| -3,56 | 0,28 | -0,01 | -0,90 | 0,26 | 0,01 | 0,45 | -0,03 | 0,07 | 0,00 | 1,38 | 0,14 | -0,06 | -0,08 |
| -1,07 | 0,10 | 0,00 | -0,05 | -0,03 | 0,02 | -0,02 | -0,01 | 0,02 | 0,07 | 0,14 | 0,21 | -0,06 | -0,03 |
| 0,39 | -0,05 | 0,00 | 0,06 | -0,04 | -0,01 | -0,01 | 0,00 | 0,00 | -0,03 | -0,06 | -0,06 | 0,09 | 0,01 |
| -0,35 | 0,07 | 0,00 | 0,08 | 0,09 | 0,00 | -0,03 | 0,00 | 0,00 | 0,02 | -0,08 | -0,03 | 0,01 | 0,14 |
Обозначим полученный вектор как 
.
Рассчитаем выборочную остаточную дисперсию (несмещенную оценку параметра 
):
, (6),
где 
– остатки, (7)
n – количество наблюдений,
p – число степеней свободы (количество независимых переменных в уравнении (1)).
Таким образом, 
.
Теперь рассчитаем стандартные ошибки коэффициентов регрессии (Таблица 8):
, (8)
Таблица 8.
Стандартные ошибки коэффициентов.
| Стандартная ошибка | |
| Свободный член | 1304,48 |
| Количество комнат | 226,77 |
| Район | 10,31 |
| Планировка | 277,57 |
| Материал стен | 287,54 |
| Этаж | 35,31 |
| Этажность | 150,93 |
| Sоб | 22,35 |
| Sжил | 31,19 |
| Sкух | 97,68 |
| Телефон | 331,79 |
| Санузел | 127,84 |
| Балкон/лоджия | 86,06 |
| Плита | 105,08 |
Уравнение регрессии имеет вид:
| Y | =3378,41 | -494,59X1 | -35,00X2 | +75,74X3 | -15,81X4 | +80,10X5 | +59,84X6 + |
| (1304,48) | (226,77) | (10,31) | (277,57) | (287,54) | (35,31) | (150,93) |
| +127,98X7 | -78,10X8 | -437,57X9 | +451,26X10 | -299,91X11 | -14,93X12 | -369,65X13 | (9) |
| (22,35) | (31,19) | (97,68) | (331,79) | (127,84) | 86,06 | (105,08) |
|
|
|
