Система нормальных уравнений
Третий способ регрессионного анализа в Excel — это построение уравнения регрессии путём решения системы уравнений. Для этого мы будем использовать функции массивов для выполнения операций над матрицами.
Чтобы не запутаться, давайте определимся с названиями. В этом разделе мы используем два названия для одного и того же: массив, матрица и диапазон.
МАССИВ (термин из области программирования) — это особый тип данных. Переменная такого типа хранит несколько значений. Это элементы массива, к которым обращаются по одному или нескольким номерам (индексам). У массива может быть несколько измерений.
В пакете Excel мы будем работать с одномерными и двумерными массивами. Формулы массивов Excel работают с аргументами-массивами и могут выдавать результат тоже в виде массива. Формулы массивов вводят особым образом — мы с этим уже немного познакомились.
МАТРИЦА (термин из математики) — это прямоугольная таблица чисел. У матрицы может быть одно или два измерения. С матрицами выполняют различные действия, например, сложение и умножение.
Матрицы часто используют при решении систем уравнений. С матрицей можно работать и без компьютера — тогда это просто табличка с цифрами или буквами, записанными на бумаге. Если с матрицей работать в пакете программ, то её нужно будет хранить в переменной типа «массив».
С точки зрения Excel мы работаем с ДИАПАЗОНОМ ячеек. Мы указываем диапазон в качестве входного аргумента функции. Мы вводим функцию массива в диапазон ячеек, чтобы получить результат в виде массива. Мы используем функции массива для работы с матрицами.
Надеемся, что ситуация с массивами и матрицами немного прояснилась. Теперь разберёмся, как построить регрессию с помощью матриц.
Рассмотрим пример линейного уравнения. Это уравнение прямой линии. Чтобы найти коэффициенты такого уравнения регрессии, нам понадобится решить систему нормальных уравнений — см. формулы.
Система нормальных уравнений
Здесь неизвестными являются коэффициенты а0 и а1. Известными являются суммы «иксов» и «игреков» в разных видах, а также количество точек n. Для начала нам нужно будет подсчитать эти суммы.
Скопируем исходные данные на новый лист и добавим дополнительные столбцы для расчёта сумм.
Вспомогательная таблица
Выделяем нужные столбцы и находим суммы по этим столбцам с помощью кнопки экспресс-анализа
Quick Analysis
Быстрый анализ.
Использование экспресс-анализа подробно описано в первой работе. Ссылка на учебное пособие находится в конце данного выпуска.
Быстрый расчёт сумм
Указываем в заголовке последней строки, что здесь находится сумма.
Заголовок строки «Сумма»
Чтобы уместить наши расчёты на одном листе в пределах видимости, скроем середину большой таблицы исходных данных. Выделим «лишние» строки с 6 по 123, проведя мышкой с нажатой левой кнопкой по «серым» заголовкам строк и в контекстном меню выберем
Hide
Скрыть.
Для вызова контекстного меню как всегда используем правую кнопку мыши.
Скрываем лишние строки
Таблица со скрытыми строками стала более компактной. На скрытые строки намекает только двойная разделительная линия между строками 5 и 124. Если понадобится снова показать всю таблицу, можно выделить её (в нашем случае это строки от 5 до 124) и нажать
Unhide
Показать.
Таблица со скрытыми строками
На этом листе будет несколько таблиц, которые мы обведём рамочкой. Выделим нашу таблицу и выберем в верхнем меню:
Home — Font — Borders — Thick Outside Borders
Главная — Шрифт — Границы — Толстые внешние границы.
Обрамление таблицы
Появляется рамка, которая показывает, где находится наша таблица. Такое же обрамление мы сделаем и вокруг следующих таблиц (матриц) на этом рабочем листе.
Таблица с обрамлением
Исходные данные готовы.
Возьмём систему нормальных уравнений и запишем её в матричном виде. Получается одно матричное уравнение, в котором участвуют матрицы A, X и Y — см. формулы. Систему уравнений решаем путём умножения на обратную матрицу.
Решение матричного уравнения
Чтобы иметь перед глазами формулы для расчётов и чтобы не запутать читателя, выпишем основные соотношения на листе бумаги. Сфотографируем формулы и вставим их на текущий лист Excel. Набирать формулы — довольно долгое занятие. К тому же, надо иногда учиться писать от руки. Это очень полезно — развивает и руки, и голову.
Формулы для расчётов
Сформируем матрицы X и Y. Все необходимые суммы уже подсчитаны. Объём выборки n тоже известен. Это число строк в таблице исходных данных — в соответствии с вариантом задания. Используем ссылки на нужные ячейки. Рисуем рамки, чтобы выделить каждую матрицу.
Матрицы для системы уравнений
Для решения системы нормальных уравнений нам предстоит найти обратную матрицу для X и умножить её на матрицу Y. Для этого мы будем использовать две функции Excel по работе с матрицами — обращение и умножение.
Функция нахождения обратной матрицы (обращение матрицы) MINVERSE возвращает обратную матрицу для матрицы, которая хранится в указанном массиве:
MINVERSE (array)
МОБР (массив).
Функция умножения матриц MMULT находит произведение двух матриц, которые хранятся в указанных массивах:
MMULT (array1, array2)
МУМНОЖ (матрица1;матрица2).
Обе функции работают с массивами и выдают результат в виде массива.
Ввод функции массива выполняем так же, как и раньше. Печатаем следующее выражение и нажимаем ОК:
=MMULT (MINVERSE (C127:D128),C130:C131)
В текущей ячейке появляется одно число. Но результат решения системы — матрица А, столбец из двух ячеек. Поэтому выделяем вертикальный диапазон из двух ячеек, начиная с ячейки, в которую мы записали нашу формулу масива. Нажимаем клавишу F2, а затем комбинацию клавиш Ctrl + Shift + Enter.
Получаем результат решения системы уравнения — два числа, два коэффициента уравнения регрессии.
Решение системы уравнений
Зная коэффициенты, можно записать уравнение регрессии. Напомним, что первый элемент в матрице А — это а0, а второй элемент — а1. Уравнение регрессии записываем с помощью ссылок на эти две ячейки.
Уравнение регрессии
Переходим к графикам. Построим диаграмму разброса. Указываем диапазоны для «иксов» и «игреков». Однако на графике появляется всего две точки вместо 120.
Диаграмма разброса
Получается, что когда мы скрываем строки в таблице, эти данные не отображаются на графике. Нам хотелось бы держать все данные и графики перед глазами. Поэтому будем использовать для диаграммы разброса данные с другого листа, на котором отображены все 120 значений. Теперь на графике все точки на месте. Настроим тип и цвет маркера.
Диаграмма разброса
Добавим линию регрессии. Поскольку мы строим прямую линию, нам будет достаточно найти всего две точки. Сделаем вспомогательную табличку. Зададим два крайних значения «икс»: 1000 и 2000. Вычислим прогноз по уравнению регрессии для «игрека».
Вспомогательная таблица
Добавим этот массив как данные для графика. Настроим тип и цвет линии. Отключим маркеры.
Диаграмма разброса и линия регрессии
Рассмотрим построенный график и убедимся в правильности расчётов. Линия регрессии проходит в среднем по исходным точкам. Значит, грубых ошибок у нас нет.
На рисунке приводится окончательный вид нашей страницы отчёта. Здесь есть заголовки, формулы, таблицы, и график. Читателю будет легко понять, что и как было сделано.
Оформление отчёта
Далее самостоятельно постройте нелинейную регрессию второго и третьего порядка.
Уравнение второго порядка — «икс» участвует во второй степени. Система нормальных уравнений для регрессии второго порядка — см. формулы.
Регрессия второго порядка
Уравнение третьего порядка — «икс» участвует в третьей степени. Система нормальных уравнений для регрессии третьего порядка — см. формулы.
Регрессия третьего порядка
Нанесите линии регрессии на общий график.