Корреляционный и регрессионный анализ в 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
Константа — ноль.
Изучите таблицу с полученными коэффициентами. Запишите уравнение регрессии.
Пользуясь полученным уравнением, нанесите линию регрессии на диаграмму разброса. Для этого определите координаты двух точек в соответствии с уравнением. Настройте оформление графика.
Линейная регрессия
Надстройка — нелинейная регрессия
С помощью надстройки постройте нелинейные уравнения регрессии второго и третьего порядков.
Уравнения регрессии
Постройте дополнительные столбцы для возведения значений факторного признака во вторую и третью степень.
Вызовите надстройку Анализ данных и выберите модуль Регрессия.
В качестве факторного признака укажите два столбца Х — Х>2 для параболы и три столбца Х — Х>2 — Х>3 для кубической параболы.
Для дальнейшего анализа используйте заголовки столбцов, выбрав следующий пункт
Labels
Метки.
Изучите полученные таблицы коэффициентов и запишите уравнения регрессии.
Для построения линии регрессии создайте вспомогательную таблицу и найдите координаты нескольких точек, чтобы получить гладкую линию. Нанесите линии регрессии на диаграмму разброса. Включите легенду на диаграмме.
Оцените относительный вклад (в процентах) каждой степени факторного признака Х в прогнозные значения результативного признака Y по уравнениям регрессии.
Функция LINEST
Получите оценки коэффициентов уравнений регрессии с помощью следующей функции:
LINEST (range_y, range_x)
ЛИНЕЙН (диапазон_y; диапазон_x).
При вводе функции массива используйте следующие шаги:
— введите формулу в одну ячейку;
— выделите диапазон ячеек;
— нажмите клавишу [F2];
— нажмите комбинацию клавиш [Ctrl + Shift + Enter].
Пример вызова функции LINEST показан на рисунке.
Функция
