Связь уравнений 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

КОРЕНЬ.

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

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