Валентин Арьков - Статистический анализ взаимосвязи в Excel

Диаграмма разброса

Добавим линию регрессии. Поскольку мы строим прямую линию, нам будет достаточно найти всего две точки. Сделаем вспомогательную табличку. Зададим два крайних значения «икс»: 1000 и 2000. Вычислим прогноз по уравнению регрессии для «игрека».

Вспомогательная таблица

Добавим этот массив как данные для графика. Настроим тип и цвет линии. Отключим маркеры.

Диаграмма разброса и линия регрессии

Рассмотрим построенный график и убедимся в правильности расчётов. Линия регрессии проходит в среднем по исходным точкам. Значит, грубых ошибок у нас нет.

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

Оформление отчёта

Далее самостоятельно постройте нелинейную регрессию второго и третьего порядка.

Уравнение второго порядка — «икс» участвует во второй степени. Система нормальных уравнений для регрессии второго порядка — см. формулы.

Регрессия второго порядка

Уравнение третьего порядка — «икс» участвует в третьей степени. Система нормальных уравнений для регрессии третьего порядка — см. формулы.

Регрессия третьего порядка

Нанесите линии регрессии на общий график.

Сравним уравнения регрессии, полученные разными способами. Обратим внимание, насколько они похожи на исходный вариант задания.

На новом листе разместим свой вариант задания, а также уравнения, полученные разными способами.

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

Все уравнения представим в единой форме, например, в порядке возрастания показателя степени «икс».

Если всё сделано правильно, уравнения одного порядка, полученные разными способами, будут очень похожи друг на друга. Возможна небольшая разница в самых младших разрядах из-за вычислительных погрешностей.

Будет небольшое отличие полученных уравнений регрессии от варианта задания. Это погрешность из-за наличия случайной составляющей, которую мы добавили в исходные данные.

В предыдущих разделах мы рассмотрели уравнение линейной регрессии «Y на X». Существует и второй вариант — обратное уравнение. Это регрессия «X на Y» — см. уравнения.

Уравнения регрессии Y (X)и X (Y)

Построим обратное уравнение с помощью надстройки. В качестве «иксов» указываем «игреки» и наоборот.

Чтобы найти коэффициенты уравнения регрессии X (Y), нам понадобится решить систему нормальных уравнений:

Система нормальных уравнений для X (Y)

Получаем следующее уравнение регрессии — см. формулы.

Оценки уравнений регрессии

Сформируем вспомогательную таблицу для построения прямой линии на графике. Выбираем крайние точки по Y: 2000 и 2700. Можно выбрать любые значения, выходящие за границы поля графика. Позже при настройке масштаба по осям на графике останется только видимая часть линий. Главное — занять нашей линией всё поле графика. Вычисляем значения Xпо уравнению регрессии.

Регрессия Y (X)

Наносим обе линии регрессии на диаграмму разброса.

Настроим тип графика для каждого набора данных. Выбираем в контекстном меню

Change Chart Type

Изменить тип диаграммы.

Устанавливаем комбинированный тип графика:

Combo

Комбинированная.

Выбираем тип графика — диаграмма разброса:

Scatter

Точечная.

Для линий регрессии Y (X)и X (Y)выбираем тип графика — ломаная линия:

Scatter with Straight Lines

Точечная с прямыми отрезками.

Чтобы оси координат были общими для всех графиков, снимаем отметки в колонке

Secondary Axis

Вспомогательная ось.

Выбор типа графиков

Настроим масштаб по осям и цвет линий.

Включаем вывод легенды на графике:

Chart Elements — Legend

Элементы диаграммы — Легенда.

В регрессионном анализе обнаружено одно интересное свойство. Наши прямые линии Y (X)и X (Y)должны пересекаться в точке {Хср, Yср}.

Чтобы продемонстрировать это свойство, возьмём первые уравнения из систем нормальных уравнения для Y (X)и X (Y). Поделим уравнения на n — см. формулы. Если сумму значений поделить на их количество, получится СРЕДНЕЕ ЗНАЧЕНИЕ. В наших формулах среднее обозначено чертой: «икс с чертой» и «игрек с чертой».