Корреляционный и регрессионный анализ в Excel (Арьков) - страница 4
Вычислите средние значения, поделив суммы на количество единиц в каждой группе.
Размер групп найдите с помощью следующей функции:
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 показан на рисунке.
Функция LINEST
После нажатия комбинации