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

На левом графике можно видеть явную связь (точную функциональную зависимость) между случайными числами Х и Е — при одинаковой настройке генератора: 1234 и 1234. На этом графике просматривается кривая нормального распределения. Она используется для создания случайного числа с заданным распределением. Разные настройки 1234 и 5678 дают действительно независимые случайные числа. Учтём на будущее.

Выделим два столбца с готовыми данными — с заголовками. Вставим данные на новый лист. Выберем режим вставки значений из буфера обмена.

Вставка значений

При выборе режима вставки из буфера можно сразу увидеть результат на экране. Нажимаем кнопку

Values

Значения.

После вставки получаем числа вместо формул в ячейках таблицы. Теперь никакие наши действия не приведут к обновлению и изменению данных.

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

Пришло время посмотреть на график наших исходных данных. На диаграмме разброса каждая пара чисел Х и Y изображается отдельной точкой. Точки на графике НЕ СОЕДИНЯЮТ линиями. В примере «Рост — Вес» нет никакой связи между параметрами соседей по парте. Поэтому каждый человек — это отдельная точка на графике.

Выделяем два столбца с округлёнными значениями X и Y. Выбираем в меню:

Insert — Charts — Insert Scatter (X, Y) or Bubble Chart — Scatter — Scatter

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

Вставка диаграммы разброса

По умолчанию диаграмма разброса выглядит не слишком привлекательно — см. график. Настроим оформление графика.

Диаграмма разброса по умолчанию

Настроим масштаб по осям, чтобы диаграмма заполняла всё поле графика. Дважды щёлкнем по горизонтальной оси. В диалоговом окне

Format Axis

Формат оси

выбираем раздел

Axis Options

Параметры оси.

Устанавливаем пределы по горизонтальной оси от 1000 до 2000.

Масштаб по оси

Щёлкнем по вертикальной оси и выберем такие значения, чтобы диаграмма разброса занимала всё место на графике.

Теперь настроим заголовки. Щёлкнем по графику и нажмём на кнопку

Chart Elements

Элементы диаграммы.

Это квадратная кнопка с символом ПЛЮС справа вверху.

Элементы графика

Отмечаем пункт

Axis Titles

Названия осей.

Заголовки осей

Отредактируем заголовки и укажем, где находятся наши «иксы» и «игреки». Для дальнейшего украшения развернём заголовок вертикальной оси на 45 градусов. Щёлкнем по заголовку вертикальной оси и выберем в меню:

Format Axis Title — Text Options — TextBox — Text Box — Text direction — Horizontal

Формат названия оси — Параметры текста — Надпись — Надпись — Направление текста — Горизонтально.

Далее установим чёрный цвет для точек-маркеров. Щёлкнем по маркерам и установим в меню чёрный цвет:

Format Data Series — Series Options — Fill & Line — Marker — Marker Options — Fill — Solid fill — Color — Black

Формат ряда данных — Параметры ряда — Заливка и границы — Маркер — Параметры маркера — Заливка — Сплошная заливка — Цвет — Чёрный.

Здесь же отключим обрамление маркеров:

Format Data Series — Series Options — Fill & Line — Marker — Marker Options — Border — No line

Формат ряда данных — Параметры ряда — Заливка и границы — Маркер — Параметры маркера — Граница — Нет линий.

После настроек диаграмма разброса должна выглядеть следующим образом — см. рисунок.

Оформленная диаграмма

Корреляционный анализ

Корреляционный анализ позволяет исследовать тесноту связи, то есть степень разброса точек вокруг линии. Чем ближе точки к линии регрессии, тем лучше ТЕСНОТА СВЯЗИ. Имеется в виду линия, которую МОЖНО построить в среднем по этом точкам. На самом деле при анализе взаимосвязи перед нами находятся только точки, а линии пока ещё НЕТ.

Теснота линейной связи оценивается с помощью КОЭФФИЦИЕНТА ЛИНЕЙНОЙ КОРРЕЛЯЦИИ r. Здесь говорится именно о ЛИНЕЙНОЙ связи и анализируется разброс вокруг будущей, возможной ПРЯМОЙ линии. Другими словами, мы выясняем, есть ли смысл в построении прямой линии в среднем по нашим точкам.

Коэффициент корреляции принимает значения от —1 до +1 включительно.