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

Вклад х: 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