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

Вставка — Диаграмма — Вставить точечную (X, Y) или пузырьковую диаграмму — Точечная — Точечная.

Добавляем набор данных для построения диаграммы разброса:

Select Data — Add

Выбрать данные — Добавить.

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

Format Axis — Axis options — Bounds — Minimum/Maximum

Формат оси — Параметры оси — Границы — Минимум/Максимум.

Устанавливаем тип маркеров — жирные точки:

Format Data Series — Series Options — Fill & Line — Marker — Marker Options — Built-in — •

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

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

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 . Для начала нам нужно будет подсчитать эти суммы.

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