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

Задайте диапазоны исходных данных и укажите, что они расположены по столбцам:

Grouped By — Columns

Группирование — по столбцам.

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

Опишите в отчёте полученные результаты.

Функция CORREL

Вычислите коэффициент корреляции с помощью следующей функции:

CORREL (array1, array2)

КОРРЕЛ (диапазон_x; диапазон_y).

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

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

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