Линейная регрессия (функции ЛИНЕЙН, НАКЛОН, ОТРЕЗОК, ПРЕДСКАЗ, ТЕНДЕНЦИЯ)
Так как задача отыскания функциональной зависимости очень важна, в Excel введен набор функций, которые позволяют решать эту задачу. Эти функции основаны на методе наименьших квадратов. В качестве результата выдаются не только коэффициенты аппроксимирующей функции, но и статистические характеристики полученных результатов.Функция ЛИНЕЙН
Функция ЛИНЕЙН вычисляет коэффициенты m и b прямой линии y=mx+b, которая наилучшим образом аппроксимирует имеющиеся данные, а также дополнительную регрессионную статистику. Функция возвращает массив данных, который описывает полученную прямую. Синтаксис функции:
ЛИНЕЙН(известные_y, [известные_x], [константа], [статистика])
Известные_y. Обязательный аргумент. Множество значений y, которые уже известны для соотношения y=mx+b.
Известные_x. Необязательный аргумент. Множество значений x, которые уже известны для соотношения y=mx+b.
Константа. Необязательный аргумент. Логическое значение. Если аргумент Константа = 0, то b принудительно полагается равным нулю, т.е. y=mx.
Статистика. Необязательный аргумент. Логическое значение. Если аргумент Статистика = 0 или опущен, то вычисляются только коэффициенты m и b, а если = 1, то выдаются дополнительные статистические характеристики.
Пример 1
Даны x и y: (0, 3), (1,
1), (2, 6), (3, 3), (4, 7). Найти коэффициенты m и b прямой линии y=mx+b, наилучшим образом
аппроксимирующей эти данные по критерию наименьших квадратов.
Решение
Подготовим таблицу как показано ниже. Ячейки E2:F6 не заполняйте, они будут заполнены автоматически.
- В A2:A6 введены значения x, блоку присвоено имя х.
- В В2:В6 введены значения y, блоку присвоено имя y.
- В E2:F6 введена табличная формула {=ЛИНЕЙН(y;x;1;1)}. Для того чтобы ввести табличную формулу, надо выделить блок ячеек E2:F6, ввести формулу и нажать комбинацию клавиш Ctrl Shift Enter. Фигурные скобки вводить вручную не надо.
Пояснение к блоку статистических результатов функции Линейн.
- В E2 записан коэффициент m, в F2 — коэффициент b.
- В E3:F3 стандартные отклонения для этих коэффициентов.
- В E4 записан так называемый
коэффициент детерминации R2. Этот коэффициент
лежит на отрезке [0; 1]. Считается, что чем ближе этот коэффициент к 1, тем
лучше регрессионное уравнение описывает зависимость. Иногда к
такой интерпретации надо относиться с осторожностью.
- В F4 находится стандартная
ошибка для оценки у.
- В E5 записано значение F-статистики, а в F5 —
количество степеней свободы.
- В E6:F6 записана регрессионная сумма квадратов (10) и остаточная сумма квадратов (14).
Функция НАКЛОН
Функция НАКЛОН вычисляет коэффициент m — тангенс угла наклона прямой регрессии. Например: =НАКЛОН(y;x)
Функция ОТРЕЗОК
Функция ОТРЕЗОК вычисляет коэффициент b — отрезок, отсекаемый прямой на оси ординат. Например: =ОТРЕЗОК(y;x)
Функция ПРЕДСКАЗ
Вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это y-значение, соответствующее заданному x-значению. x- и y-значения — известны; новое значение предсказывается с использованием линейной регрессии. Этой функцией можно воспользоваться для прогнозирования.
ПРЕДСКАЗ(x;известные_y;известные_x)
x — точка данных, для которой предсказывается значение.
Функция ТЕНДЕНЦИЯ
Возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы «известные_y» и «известные_x». Возвращает значения y, соответствующие этой прямой для заданного массива «новые_x».
ТЕНДЕНЦИЯ(известные_y;[известные_x];[новые_x];[константа])
Не забывайте, что функция ТЕНДЕНЦИЯ может возвращать массив результатов, поэтому для неё надо указывать блок ячеек и вводить как табличную формулу.Новые_x — новые значения x, для которых функция ТЕНДЕНЦИЯ возвращает соответствующие значения y.
Пример 2.
Постройте таблицу по образцу. В примечаниях показаны имена ячеек или формулы. В A5:A9 известныеX, в B5:B9 известныеY. Блоку A5:A12 присвоено имя Х. Рассчитайте Предсказ, Тенденцию и прямую mx+b.
Постройте диаграмму по образцу. На диаграмме видно, что прямая пересекает ось ординат в точке 2 (b=2), а наклон прямой равен 45° (m=1). Прямые Предсказ, Тенденция и mx+b слились в одну линию.
