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

0,3

0,5

0,7

1,0.

Постройте диаграммы разброса. Укажите на них значения коэффициента корреляции и степень тесноты связи.

Формулы

Вычислите значение коэффициенты линейной корреляции с помощью формул. Используйте соотношение, приведённое ниже.

Коэффициент корреляции

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

Для вычисления коэффициента корреляции используйте функцию извлечения корня:

SQRT (number)

КОРЕНЬ (число).

Сравнение результатов

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

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

Регрессионный анализ

Регрессионный анализ сводится к построению линии, которая проходит в среднем по исходным точкам, а также к нахождению уравнения этой линии.

Уравнение линейной регрессии приводится ниже.

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

Регрессионный анализ в Excel проводится следующими способами:

— встроенный элемент диаграммы;

— модуль Регрессия надстройки Анализ данных;

— готовая функция LINEST;

— решение системы нормальных уравнений с помощью формул.

Регрессия на диаграмме

Вызовите встроенную функцию графика, включив Линию тренда как элемент диаграммы разброса, как показано на рисунке:

Trendline — Linear

Линия тренда — Линейный.

Линия тренда

Включите вывод уравнения регрессии на поле графика:

Design — Add Chart Element — Trendline — More Trendline options — Format Trendline — Trendline options — Display Equation on chart

Конструктор — Добавить элемент диаграммы — Линия тренда — Дополнительные параметры линии тренда — Формат линии тренда — Параметры линии тренда — показывать уравнение на диаграмме.

Расположите уравнение на свободном месте графика.

Условное среднее

Рассчитайте условное среднее и нанесите его на диаграмму разброса. Это среднее для значений Y, для которых X попадает в заданный интервал.

Постройте таблицу для группировки данных:

— нижние границы интервалов по X;

— верхние границы интервалов по Х;

— условные средние значения X и Y.

Процедура группировки подробно обсуждается в предыдущей работе [6].

Используйте функцию нахождения условной суммы:

SUMIF (range, criteria, [sum_range])

СУММЕСЛИ (диапазон; критерий; [диапазон_суммирования]).

Первый и второй аргументы относятся к факторному признаку, третий аргумент — к результативному.

Примеры нахождения условных сумм приведены на рисунке.

Условные суммы

Для упрощения выражений можно проверять условие только по верхней границе интервала, а затем находить разность результатов для соседних интервалов.

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

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

COUNTIF

СЧЕТЕСЛИ.

Примеры нахождения условных (групповых) средних для факторного признака приведены ниже.

Групповое среднее Х

Пример определения условного среднего для результативного признака показан на рисунке.

Условное среднее Y (X)

Постройте график условного среднего на диаграмме разброса в виде ломаной линии:

Scatter with Straight Lines

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

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

Условное среднее на диаграмме разброса

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

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

Укажите расположение исходных данных — столбцов Y и Х. Снимите выбор следующего пункта:

Constant is Zero

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

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

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

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

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

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

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

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

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

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

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

Labels

Метки.

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

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

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

Функция LINEST

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

LINEST (range_y, range_x)

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

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

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

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

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

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

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

Функция