Статистический анализ взаимосвязи в Excel

Таблица со скрытыми строками стала более компактной. На скрытые строки намекает только двойная разделительная линия между строками 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, а второй элемент — а₁. Уравнение регрессии записываем с помощью ссылок на эти две ячейки.

Уравнение регрессии

Переходим к графикам. Построим диаграмму разброса. Указываем диапазоны для «иксов» и «игреков». Однако на графике появляется всего две точки вместо 120.

Диаграмма разброса

Получается, что когда мы скрываем строки в таблице, эти данные не отображаются на графике. Нам хотелось бы держать все данные и графики перед глазами. Поэтому будем использовать для диаграммы разброса данные с другого листа, на котором отображены все 120 значений. Теперь на графике все точки на месте. Настроим тип и цвет маркера.

Диаграмма разброса

Добавим линию регрессии. Поскольку мы строим прямую линию, нам будет достаточно найти всего две точки. Сделаем вспомогательную табличку. Зададим два крайних значения «икс»: 1000 и 2000. Вычислим прогноз по уравнению регрессии для «игрека».

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

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

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

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

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

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

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

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

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

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

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

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

Сравнение результатов