Корреляционный и регрессионный анализ в Excel

Константа — ноль.

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

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

Линейная регрессия

Надстройка — нелинейная регрессия

С помощью надстройки постройте нелинейные уравнения регрессии второго и третьего порядков.

Уравнения регрессии

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

Вызовите надстройку Анализ данных и выберите модуль Регрессия.

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

Для дальнейшего анализа используйте заголовки столбцов, выбрав следующий пункт

Labels

Метки.

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

Для построения линии регрессии создайте вспомогательную таблицу и найдите координаты нескольких точек, чтобы получить гладкую линию. Нанесите линии регрессии на диаграмму разброса. Включите легенду на диаграмме.

Оцените относительный вклад (в процентах) каждой степени факторного признака Х в прогнозные значения результативного признака Y по уравнениям регрессии.

Функция LINEST

Получите оценки коэффициентов уравнений регрессии с помощью следующей функции:

LINEST (range_y, range_x)

ЛИНЕЙН (диапазон_y; диапазон_x).

При вводе функции массива используйте следующие шаги:

— введите формулу в одну ячейку;

— выделите диапазон ячеек;

— нажмите клавишу [F2];

— нажмите комбинацию клавиш [Ctrl + Shift + Enter].

Пример вызова функции LINEST показан на рисунке.

Функция LINEST

После нажатия комбинации [Ctrl + Shift + Enter] вокруг формулы массива выводятся фигурные скобки — см. рисунок.

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

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

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

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

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

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

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

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

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

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

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 (X) и X (Y) должны пересекаться в точке {Хср, Yср}.

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

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

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

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

Анализ данных интернет-магазина

Загрузите данные о компьютерных компонентах на сайте:

www.nix.ru.

Выберите не менее десяти однотипных изделий.

Проведите корреляционный и регрессионный анализ связи двух наиболее информативных признаков, например, объёма диска и его цены.

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

Анализ биржевых котировок

Проведите анализ взаимосвязи между котировками на Московской бирже.

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

Уточните базу расчёта индекса на сайте:

www.moex.com.

Загрузите исторические данные дневной периодичности за последние 10 лет на сайте:

investing.com.