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

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

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

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

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

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

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