Валентин Арьков - Статистический анализ взаимосвязи в 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. Вычислим прогноз по уравнению регрессии для «игрека».

Вспомогательная таблица

Добавим этот массив как данные для графика. Настроим тип и цвет линии. Отключим маркеры.

Диаграмма разброса и линия регрессии

Рассмотрим построенный график и убедимся в правильности расчётов. Линия регрессии проходит в среднем по исходным точкам. Значит, грубых ошибок у нас нет.

На рисунке приводится окончательный вид нашей страницы отчёта. Здесь есть заголовки, формулы, таблицы, и график. Читателю будет легко понять, что и как было сделано.

Оформление отчёта

Далее самостоятельно постройте нелинейную регрессию второго и третьего порядка.

Уравнение второго порядка — «икс» участвует во второй степени. Система нормальных уравнений для регрессии второго порядка — см. формулы.

Регрессия второго порядка

Уравнение третьего порядка — «икс» участвует в третьей степени. Система нормальных уравнений для регрессии третьего порядка — см. формулы.

Регрессия третьего порядка

Нанесите линии регрессии на общий график.

Сравним уравнения регрессии, полученные разными способами. Обратим внимание, насколько они похожи на исходный вариант задания.

На новом листе разместим свой вариант задания, а также уравнения, полученные разными способами.

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

Все уравнения представим в единой форме, например, в порядке возрастания показателя степени «икс».

Если всё сделано правильно, уравнения одного порядка, полученные разными способами, будут очень похожи друг на друга. Возможна небольшая разница в самых младших разрядах из-за вычислительных погрешностей.

Будет небольшое отличие полученных уравнений регрессии от варианта задания. Это погрешность из-за наличия случайной составляющей, которую мы добавили в исходные данные.

В предыдущих разделах мы рассмотрели уравнение линейной регрессии «Y на X». Существует и второй вариант — обратное уравнение. Это регрессия «X на Y» — см. уравнения.

Уравнения регрессии Y (X)и X (Y)

Построим обратное уравнение с помощью надстройки. В качестве «иксов» указываем «игреки» и наоборот.

Чтобы найти коэффициенты уравнения регрессии X (Y), нам понадобится решить систему нормальных уравнений:

Система нормальных уравнений для X (Y)

Получаем следующее уравнение регрессии — см. формулы.

Оценки уравнений регрессии

Сформируем вспомогательную таблицу для построения прямой линии на графике. Выбираем крайние точки по Y: 2000 и 2700. Можно выбрать любые значения, выходящие за границы поля графика. Позже при настройке масштаба по осям на графике останется только видимая часть линий. Главное — занять нашей линией всё поле графика. Вычисляем значения Xпо уравнению регрессии.

Регрессия Y (X)

Наносим обе линии регрессии на диаграмму разброса.

Настроим тип графика для каждого набора данных. Выбираем в контекстном меню

Change Chart Type

Изменить тип диаграммы.

Устанавливаем комбинированный тип графика:

Combo

Комбинированная.

Выбираем тип графика — диаграмма разброса:

Scatter

Точечная.

Для линий регрессии Y (X)и X (Y)выбираем тип графика — ломаная линия:

Scatter with Straight Lines

Точечная с прямыми отрезками.

Чтобы оси координат были общими для всех графиков, снимаем отметки в колонке

Secondary Axis

Вспомогательная ось.

Выбор типа графиков

Настроим масштаб по осям и цвет линий.

Включаем вывод легенды на графике:

Chart Elements — Legend

Элементы диаграммы — Легенда.

В регрессионном анализе обнаружено одно интересное свойство. Наши прямые линии Y (X)и X (Y)должны пересекаться в точке {Хср, Yср}.

Чтобы продемонстрировать это свойство, возьмём первые уравнения из систем нормальных уравнения для Y (X)и X (Y). Поделим уравнения на n — см. формулы. Если сумму значений поделить на их количество, получится СРЕДНЕЕ ЗНАЧЕНИЕ. В наших формулах среднее обозначено чертой: «икс с чертой» и «игрек с чертой».

Точка пересечения линий

Можно видеть, что точка {Хср, Yср}является общей для обоих уравнений. Другими словами, уравнения линий регрессии выполняются для указанных значений.

Вычисляем средние значения Xи Y. Наносим эту точку на график. Настраиваем тип и размер маркера, цвет заливки и границы.