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

Строим диаграмму разброса, как описано выше.

Выбираем второй ряд данных:

Select Data — Select Data Source — Add

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

Добавляем новые данные для графика. В качестве значений xберём условные средние «иксы», а в качестве y — условные средние «игреки». На графике появляются новые точки.

Изменяем тип диаграммы: щёлкаем правой кнопкой по графику и выбираем комбинированный график:

Change Chart Type — Combo

Изменить тип диаграммы — Комбинированная.

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

Scatter

Точечная.

Для условного среднего выбираем ломаную линию:

Scatter with Straight Lines

Точечная с прямыми отрезками и маркерами.

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

Secondary Axis

Вспомогательная ось.

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

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

В процессе настройки графиков можно видеть, как меняется изображение. При выборе данных для графиков мы не указывали названия рядов, поэтому они названы по умолчанию 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

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

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

Border — No line

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

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

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

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

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

Color — Black

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

Оставляем только линию и убираем маркеры:

Marker — Marker Options — None

Маркер — Параметры маркера — Нет.

Задаём толщину линии:

Line — Width — 1 pt

Линия — Ширина — 1 пт.

И снова — проблема перевода. ШИРИНА чаще бывает у полосы или прямоугольника. ТОЛЩИНА ЛИНИИ звучит как-то более привычно. Но это, конечно же, дело вкуса — если работой занимается любитель, дилетант, то есть человек не является специалистом. Особенно, если переводить отдельные слова и не обращать внимание на окружающий текст, который называется КОНТЕКСТ.