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

Формат ряда данных — Параметры ряда — Заливка и границы — Маркер — Параметры маркера — Встроенный — Тип — •.

Устанавливаем чёрный цвет для заливки маркеров:

Format Data Series — Series Options — Fill & Line — Marker — Fill — Solid Fill — Color — Black

Формат ряда данных — Параметры ряда — Заливка и границы — Маркер — Заливка — Сплошная заливка — Цвет — Чёрный.

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

Чтобы нанести на график линию регрессию, сделаем вспомогательную таблицу. Первый столбец — десять значений «икс» от минимального до максимального. В соответствии с нашим вариантом задания, диапазон от 1000 до 2000. Для получения десяти значений берём шаг 100 единиц.

Вводим числа 1000 и 1100. Выделяем диапазон из этих двух ячеек и тянем вниз маркер заполнения. Вводим формулы для квадратов и кубов. Вычисляем прогнозы по уравнениям регрессии. Не забываем зафиксировать значения коэффициентов, нажав клавишу F4.

Данные для графиков

Добавляем данные для графиков. Настраиваем тип и цвет линий. Добавляем легенду. В этом случае легенда будет полезной. Она поможет различать наши три линии.

Вид нашего уравнения регрессии указываем при выборе данных для графика в окне Edit Seriesв строке Series name.

Вид уравнения регрессии

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

Линии регрессии

Третий способ регрессионного анализа в 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 по работе с матрицами — обращение и умножение.