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

Корреляционный анализ — это изучение степени тесноты связи.

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

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

— надстройка Анализ данных;

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

— формулы, вводимые вручную.

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

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

Grouped By — Columns

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

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

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

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

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])

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

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

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

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

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