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

Если на графике будет две вертикальных оси, то будет свой масштаб для каждого набора данных. Такие графики будет невозможно сравнивать. Нам нужен общий, единый масштаб.

Комбинация графиков

В процессе настройки графиков можно видеть, как меняется изображение. При выборе данных для графиков мы не указывали названия рядов, поэтому они названы по умолчанию Series1 и Series2. Пока на графике не так много данных, это не доставляет неудобств. В следующей работе всё-таки придётся задать имена для каждого набора данных, чтобы легче было работать с несколькими графиками.

Как и раньше, настраиваем масштаб, заголовки, цвета. График готов.

Условное среднее на диаграмме разброса

Надстройка

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

В этом разделе используем статистическую надстройку:

Data — Analysis — Data Analysis — Regression

Данные — Анализ — Анализ данных — Регрессия.

Линейная регрессия

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

Regression

Регрессия.

Указываем диапазоны ячеек для входных интервалов фактора X и результата Y. Обратите внимание, что в первой строке вводятся «игреки», а во второй — «иксы». Выделяем диапазоны ячеек с заголовками и указываем это в пункте

Labels

Метки.

В уравнении регрессии будет свободный член, поэтому убеждаемся, что сняли выбор пункта

Constant is Zero

Константа — ноль.

Указываем первую ячейку для вывода результатов

Output options — Output Range

Параметры вывода — Выходной интервал.

Ещё нас интересует график. Поэтому отмечаем пункт

Residuals — Line Fit Plots

Остатки — График подбора.

Регрессию иногда не слишком грамотно называют «подбор» или «подгонка». На самом деле это попытка перевести английский термин fit. В качестве упражнения предлагаем заглянуть в Википедию и ознакомиться с англоязычным разделом Curve fitting и его русскоязычным вариантом Приближение с помощью кривых.

Более грамотный перевод слова FIT — это АППРОКСИМАЦИЯ, то есть приблизительная замена сложной функции или большого массива данных упрощённой функцией. Слово «аппроксимация» буквально означает «приближение», или «приблизительная замена».

Для нас это пример того, какие проблемы возникают у переводчиков англоязычных программных средств. Для грамотного перевода требуется профессиональное, грамотное владение предметной областью и терминологией на обоих языках. Естественно, это слишком «дорого» и «нерентабельно».

Параметры регрессионного анализа

На экран выводится несколько таблиц и один график.

Результаты регрессионного анализа

Среди результатов регрессионного анализа нас будут интересовать прежде всего коэффициенты уравнения. Рассмотрим третью табличку поподробнее. Увеличим ширину столбцов. Для точного вывода всех доступных разрядов можно щёлкнуть по ячейке и взглянуть на строку формул.

Коэффициенты уравнения

В таблице коэффициентов имеется две строчки.

В первой строке выводится СВОБОДНЫЙ ЧЛЕН уравнения регрессии. Строка обозначена следующим образом:

Intercept

Y-пересечение.

Имеется в виду значение Y, при котором линия регрессии ПЕРЕСЕКАЕТ вертикальную ось координат. В нашем примере свободный член уравнения обозначен буквой a.

a = 1379.7259819748.

Во второй строке выводится КОЭФФИЦИЕНТ РЕГРЕССИИ:

X

Переменная X 1.

В нашем уравнении коэффициент регрессии, то есть коэффициент при «иксе», обозначен буквой b.

b = 0.664739003611188.

Второй результат анализа, который мы рассмотрим, — это график под названием Line Fit Plot. Имеется в виду графическое изображение ЛИНЕЙНОЙ АППРОКСИМАЦИИ. Predicted Y — это ЛИНЕЙНЫЙ ПРОГНОЗ, то есть оценка значения результативного признака по уравнению регрессии.

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

Настроим масштаб по осям, увеличим график и отключим ЛЕГЕНДУ ДИАГРАММЫ — это условные обозначения в правой части графика. Пока у нас всего два ряда данных, нетрудно сообразить, кто есть кто.

Для исходных данных установим чёрные круглые маркеры вместо синих ромбиков.

Выбираем тип маркера большую точку (закрашенный кружочек):

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

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

Настраиваем заливку чёрным цветом:

Fill — Solid fill — Color — Black