Функция 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.
Вид уравнения регрессии
Получаем довольно прилично оформленный график. На нём есть исходные данные в виде точек. Три линии регрессии имеют разный цвет. Обозначения (легенда) приводятся справа от графика. На осях имеются заголовки и масштаб. У графика тоже есть заголовок. При таком оформлении можно понять, что тут нарисовано и как оно обозначено.
Линии регрессии