Статистический анализ взаимосвязи в 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 мы работаем с ДИАПАЗОНОМ ячеек. Мы указываем диапазон в качестве входного аргумента функции. Мы вводим функцию массива в диапазон ячеек, чтобы получить результат в виде массива. Мы используем функции массива для работы с матрицами.

Надеемся, что ситуация с массивами и матрицами немного прояснилась. Теперь разберёмся, как построить регрессию с помощью матриц.

Рассмотрим пример линейного уравнения. Это уравнение прямой линии. Чтобы найти коэффициенты такого уравнения регрессии, нам понадобится решить систему нормальных уравнений — см. формулы.

Система нормальных уравнений

Здесь неизвестными являются коэффициенты а₀ и а₁. Известными являются суммы «иксов» и «игреков» в разных видах, а также количество точек n. Для начала нам нужно будет подсчитать эти суммы.

Скопируем исходные данные на новый лист и добавим дополнительные столбцы для расчёта сумм.

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

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

Quick Analysis

Быстрый анализ.

Использование экспресс-анализа подробно описано в первой работе. Ссылка на учебное пособие находится в конце данного выпуска.

Быстрый расчёт сумм

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

Заголовок строки «Сумма»

Чтобы уместить наши расчёты на одном листе в пределах видимости, скроем середину большой таблицы исходных данных. Выделим «лишние» строки с 6 по 123, проведя мышкой с нажатой левой кнопкой по «серым» заголовкам строк и в контекстном меню выберем

Hide

Скрыть.

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

Скрываем лишние строки