Функция LINEST / ЛИНЕЙН

Второй способ регрессионного анализа — готовая функция 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 — •

Формат ряда данных — Параметры ряда — Заливка и границы — Маркер — Параметры маркера — Встроенный — Тип — •.

Устанавливаем чёрный цвет для заливки маркеров:

Format Data Series — Series Options — Fill & Line — Marker — Fill — Solid Fill — Color — Black

Формат ряда данных — Параметры ряда — Заливка и границы — Маркер — Заливка — Сплошная заливка — Цвет — Чёрный.

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

Чтобы нанести на график линию регрессию, сделаем вспомогательную таблицу. Первый столбец — десять значений «икс» от минимального до максимального. В соответствии с нашим вариантом задания, диапазон от 1000 до 2000. Для получения десяти значений берём шаг 100 единиц.

Вводим числа 1000 и 1100. Выделяем диапазон из этих двух ячеек и тянем вниз маркер заполнения. Вводим формулы для квадратов и кубов. Вычисляем прогнозы по уравнениям регрессии. Не забываем зафиксировать значения коэффициентов, нажав клавишу F4.

Данные для графиков

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

Вид нашего уравнения регрессии указываем при выборе данных для графика в окне Edit Series в строке Series name.

Вид уравнения регрессии

Получаем довольно прилично оформленный график. На нём есть исходные данные в виде точек. Три линии регрессии имеют разный цвет. Обозначения (легенда) приводятся справа от графика. На осях имеются заголовки и масштаб. У графика тоже есть заголовок. При таком оформлении можно понять, что тут нарисовано и как оно обозначено.

Линии регрессии