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

Данные для Predicted y

Вместо столбца исходных «иксов» выбираем отсортированные «иксы» из вспомогательной таблицы. Диапазон для прогноза «игреков» оставляем тем же.

Отсортированные Predicted y

Теперь линия регрессии на графике стала похожа на линию. Поскольку мы генерировали исходные данные с помощью уравнения прямой линии, в наших точках особой «кривизны» не наблюдается. Так что мы видим участок параболы с очень небольшой кривизной.

График по отсортированным данным

Перейдём к уравнению регрессии. Надстройка выдаёт нам оценки коэффициентов уравнения. Заголовки строк указывают, к чему относится каждый коэффициент:

Intercept — свободный член уравнения;

x — «иксы»;

x2 — квадраты «иксов».

В исходной таблице мы сделали красивый заголовок для квадратов «икса» x 2 с помощью форматирования. Как видим, при выводе результатов регрессионного анализа форматирование потерялось и осталось только x2. Делаем вывод: заголовки должны быть такими, чтобы они хорошо читались как с форматированием, так и без форматирования.

Оценки коэффициентов

Берём коэффициенты и записываем уравнение регрессии. Нам понадобится несколько ячеек. Используем ссылки на ячейки с оценками коэффициентов.

Уравнение регрессии

Коэффициент при квадрате «икса» небольшой. С учётом величины квадрата «икса» получаем небольшой вклад в общий результат — на фоне остальных членов уравнения. Сравним вклад членов уравнения для среднего значения аргумента. Вычисления округлим до целых:

x = 1 500

x 2= 2 250 000.

Свободный член: 1 086.

Вклад х: 1,0733 * 1500 = 1 610.

Вклад х 2: 0,000 137 * 1 500 * 1 500 = 308.

Относительный вклад х 2:

308 / (1086 +1610 +308) * 100% = 10%.

Получается, что нелинейная часть уравнения даёт всего 10% изменения результативного признака.

Чтобы построить уравнение третьей степени, повторите описанные шаги для диапазона ячеек, дополнительно включающего столбец третьих степеней «иксов».

Второй способ регрессионного анализа — готовая функция Excel. Коэффициенты регрессии можно найти с помощью функции

LINEST

ЛИНЕЙН.

Как и в предыдущем разделе, используем дополнительные столбцы со степенями «иксов». Скопируем исходные данные на новый лист. Значения регрессоров по-прежнему должны располагаться в соседних столбцах. При этом они указываются в виде диапазона как второй аргумент функции. В нашем случае формат вызова функции следующий:

LINEST (range_y, range_x)

ЛИНЕЙН (диапазон_y; диапазон_x).

Функция LINESTвыводит результаты анализа в виде массива в несколько ячеек. Вызов этой функции снова потребует от нас выполнить несколько шагов. Напомним последовательность работы с формулой массива:

— ввести формулу в левую верхнюю ячейку диапазона;

— выделить весь диапазон ячеек;

— нажать клавишу [F2];

— нажать комбинацию клавиш [Ctrl + Shift + Enter].

Вначале вводим функцию LINESTв ячейку и указываем аргументы функции — диапазоны ячеек с исходными данными. Здесь тоже вначале идут «игреки», а потом «иксы». Всплывающая подсказка намекает, что у функции LINESTесть один обязательный аргумент и три необязательных — они указаны в квадратных скобках. Нам нужно указать два аргумента функции, как показано на рисунке.

Параметры функции LINEST

Как видим, при таком вызове функция даёт нам значение всего одного коэффициента. А в линейном уравнении их должно два.

Оценка одного коэффициента

Чтобы получить два коэффициента, проделаем описанные шаги для ввода ФОРМУЛЫ МАССИВА. Выделяем диапазон, состоящий из двух соседних ячеек F2и G2. Вторая ячейка диапазона должна быть справа от первой!

Выделение диапазона из двух ячеек

Нажимаем клавишу [F2]. Обычно её используют для редактирования содержимого одной ячейки. Но теперь у нас было выделено две ячейки. На рисунке можно видеть, что выделение всё ещё охватывает две наши ячейки. Обратите внимание на зелёное обрамление вокруг ячеек F2и G2. Получается что мы «редактируем» выделенный диапазон ячеек.

Результат нажатия [F2]

Наконец, нажимаем комбинацию клавиш:

[Ctrl + Shift + Enter].

Вокруг формулы появились ФИГУРНЫЕ СКОБКИ. Это говорит о том, что это формула массива.

Формула массива

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

Формула массива в каждой ячейке

Excel не позволяет изменить или удалить содержимое ячейки, если она входит в массив. При попытке внести изменения или удалить содержимое выводится сообщение:

You can’t change part of an array

Нельзя изменить часть массива.

Запрет изменений в массиве

При работе с массивом можно удалить только весь массив целиком. Выделим наш массив из двух ячеек и нажмём клавишу Delete. Массив удалён. Вернём результаты на место: нажмём кнопку отката Undoв левой верхней части окна программы. Можно также использовать комбинацию клавиш [Ctrl + Z].

Откат изменений

Повторим описанные шаги для построения уравнения регрессии второго и третьего порядка. Для параболы потребуется три коэффициента, а для кубической параболы — четыре. Соответственно, нужно будет вводить формулу массива на три или на четыре ячейки.

Чтобы можно было сравнить результаты, наведём порядок в представлении. Напомним, как выглядят уравнения регрессии — см. формулы.

Уравнения регрессии

Обратите внимание, что во всех трёх уравнениях номер (индекс) коэффициента — это степень, в которую возводится «икс». Поэтому придётся немного перестроить таблицу для коэффициентов.

Оценки коэффициентов

Получив оценки коэффициентов, можно записать уравнения регрессии.

Уравнения регрессии

Следующий шаг — графики. Нам нужно построить диаграмму разброса и нанести на неё линии регрессии.

Напомним, как построить диаграмму разброса:

Insert — Charts — Insert Scatter (X, Y) or Bubble Chart — Scatter — Scatter

Вставка — Диаграмма — Вставить точечную (X, Y) или пузырьковую диаграмму — Точечная — Точечная.

Добавляем набор данных для построения диаграммы разброса:

Select Data — Add

Выбрать данные — Добавить.

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

Format Axis — Axis options — Bounds — Minimum/Maximum

Формат оси — Параметры оси — Границы — Минимум/Максимум.

Устанавливаем тип маркеров — жирные точки:

Format Data Series — Series Options — Fill & Line — Marker — Marker Options — Built-in — •