Связь уравнений 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
КОРЕНЬ.
Сравнение коэффициентов
Находим разность оценок коэффициента корреляции. Можно видеть, что эта разность практически равна нулю.