Валентин Арьков - Статистический анализ взаимосвязи в Excel

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

Выделяем нужные столбцы и находим суммы по этим столбцам с помощью кнопки экспресс-анализа

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 значений. Теперь на графике все точки на месте. Настроим тип и цвет маркера.