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

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

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

Заливка — Сплошная заливка — Цвет — Чёрный.

Отключаем «границу» — обрамление маркера:

Border — No line

Граница — Нет линий.

Для линейного прогноза установим сплошную чёрную линию вместо оранжевых ромбиков:

Format Data Series — Series Options — Fill & Line — Line — Solid line

Формат ряда данных — Параметры ряда — Заливка и границы — Линия — Сплошная линия.

Устанавливаем цвет линии:

Color — Black

Цвет — Чёрный.