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

Сравним уравнения регрессии, полученные разными способами. Обратим внимание, насколько они похожи на исходный вариант задания.

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

Коэффициенты уравнений копировать не будем. Используем ссылки на те ячейки, в которых хранятся результаты расчетов.

Все уравнения представим в единой форме, например, в порядке возрастания показателя степени «икс».

Если всё сделано правильно, уравнения одного порядка, полученные разными способами, будут очень похожи друг на друга. Возможна небольшая разница в самых младших разрядах из-за вычислительных погрешностей.

Будет небольшое отличие полученных уравнений регрессии от варианта задания. Это погрешность из-за наличия случайной составляющей, которую мы добавили в исходные данные.

Связь уравнений Y (X) и X (Y)

В предыдущих разделах мы рассмотрели уравнение линейной регрессии «Y на X». Существует и второй вариант — обратное уравнение. Это регрессия «X на Y» — см. уравнения.

Уравнения регрессии Y (X) и X (Y)

Построим обратное уравнение с помощью надстройки. В качестве «иксов» указываем «игреки» и наоборот.

Чтобы найти коэффициенты уравнения регрессии X (Y), нам понадобится решить систему нормальных уравнений:

Система нормальных уравнений для X (Y)

Получаем следующее уравнение регрессии — см. формулы.

Оценки уравнений регрессии

Сформируем вспомогательную таблицу для построения прямой линии на графике. Выбираем крайние точки по Y: 2000 и 2700. Можно выбрать любые значения, выходящие за границы поля графика. Позже при настройке масштаба по осям на графике останется только видимая часть линий. Главное — занять нашей линией всё поле графика. Вычисляем значения X по уравнению регрессии.

Регрессия Y (X)

Наносим обе линии регрессии на диаграмму разброса.

Настроим тип графика для каждого набора данных. Выбираем в контекстном меню

Change Chart Type

Изменить тип диаграммы.

Устанавливаем комбинированный тип графика:

Combo

Комбинированная.

Выбираем тип графика — диаграмма разброса:

Scatter

Точечная.

Для линий регрессии Y (X) и X (Y) выбираем тип графика — ломаная линия:

Scatter with Straight Lines

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

Чтобы оси координат были общими для всех графиков, снимаем отметки в колонке

Secondary Axis

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

Выбор типа графиков

Настроим масштаб по осям и цвет линий.

Включаем вывод легенды на графике:

Chart Elements — Legend

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

В регрессионном анализе обнаружено одно интересное свойство. Наши прямые линии Y (X) и X (Y) должны пересекаться в точке {Хср, Yср}.

Чтобы продемонстрировать это свойство, возьмём первые уравнения из систем нормальных уравнения для Y (X) и X (Y). Поделим уравнения на n — см. формулы. Если сумму значений поделить на их количество, получится СРЕДНЕЕ ЗНАЧЕНИЕ. В наших формулах среднее обозначено чертой: «икс с чертой» и «игрек с чертой».

Точка пересечения линий

Можно видеть, что точка {Хср, Yср} является общей для обоих уравнений. Другими словами, уравнения линий регрессии выполняются для указанных значений.

Вычисляем средние значения X и Y. Наносим эту точку на график. Настраиваем тип и размер маркера, цвет заливки и границы.

Пересечение линий регрессии

Убеждаемся, что линии регрессии действительно пересекаются в указанной точке.

Второе примечательное свойство линейной регрессии — это взаимосвязь коэффициентов регрессии с коэффициентом линейной корреляции — см. формулы.

Взаимосвязь коэффициентов

Проверяем выполнение указанных соотношений.

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

Для извлечения квадратного корня используем функцию

SQRT

КОРЕНЬ.

Сравнение коэффициентов

Находим разность оценок коэффициента корреляции. Можно видеть, что эта разность практически равна нулю.

Анализ реальных данных

Мы познакомились с основными методами изучения взаимосвязи. Это корреляционный и регрессионный анализ. Далее мы применим рассмотренные методы к реальным данным.

Данные для работы будем загружать из глобальной сети интернет, причём это будут открытые и общедоступные данные. Никаких платных сервисов и закрытых подписок. Мы рассматриваем реальные примеры, в которых ЯВНО просматриваются некоторые закономерности.

Конечно, реальные данные отличаются от идеальных, смоделированных. Здесь появляются более сложные закономерности, распределения отличаются от стандартных, а уравнение связи может изменяться со временем.

Тем не менее, работа с реальными данными — это важный шаг в освоении материала. От студента потребуется способность отличать важные свойства от второстепенных подробностей, а также использовать здравый смысл при формулировке выводов.

Интернет-магазин