Корреляционный и регрессионный анализ в Excel (Арьков) - страница 5

Формула массива

Постройте уравнения регрессии первого, второго и третьего порядка.

Постройте диаграмму разброса и нанесите на неё все три линии регрессии. Для построения графиков создайте вспомогательную таблицу. Количество точек должно быть достаточно, чтобы получить графики, гладкие на вид.

Включите легенду на диаграмме и сделайте информативные названия линий.

Линии регрессии

Постройте уравнения регрессии с помощью формул, решив систему уравнений.

Система нормальных уравнений

Сформируйте вспомогательную таблицу для подсчёта сумм.

Для обращения матрицы используйте следующую функцию:

MINVERSE (array)

МОБР (массив).

Для умножения матриц используйте функцию:

MMULT (array1, array2)

МУМНОЖ (матрица1;матрица2).

Пример поиска решения системы уравнений:

=MMULT (MINVERSE (C127:D128),C130:C131)

Для работы с матрицами используйте формулы массива, как описано выше.

Изучите полученные коэффициенты и запишите уравнения регрессии.

Постройте диаграмму разброса и нанесите линии регрессии.

Пример оформления страницы отчёта приводится на рисунке.

Оформление отчёта

Сравните уравнения регрессии, полученные разными способами.

Определите разницу значений коэффициентов в процентах.

С помощью надстройки постройте два уравнения линейной регрессии «Y на X» и «X на Y» — см. уравнения.

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

Запишите полученные уравнения регрессии.

Постройте диаграмму разброса и нанесите на неё обе линии регрессии. Используйте общий масштаб для всех наборов данных на комбинированном графике.

Проверьте выполнение двух свойств:

— точка пересечения линий регрессии;

— связь с коэффициентом корреляции.

Первое свойство: прямые Y (X) и X (Y) должны пересекаться в точке {Хср, Yср}.

Вычислите средние значения Х и Y и нанесите эту точку на график.

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

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

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