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

Часто имеется набор значений параметров X и Y, а требуется аппроксимировать зависимость Y от X прямой линией Y=aX+b.

Функция ЛИНЕЙН позволяет определить коэффициенты аппроксимирующей линии методом наименьших квадратов.

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

Пример №12

Имеется экспериментально полученные значения параметра X и соответствующие значения Y. Требуется описать зависимость Y от X.

1. Функция ЛИНЕЙН является функцией массива (возвращает несколько чисел), поэтому перед ее вводом надо выделить необходимый диапазон для возвращаемых данных. Так как дополнительная статистика не требуется, достаточно выделить две смежные ячейки в одной строке (E16: F16).

2. Открыть окно мастера функций для функции ЛИНЕЙН (категория Статистические).

3. Ввести диапазоны значений Y и X.Параметры Конст и Статистика опускаем, так как не требуется прохождение искомой прямой через начало координат и дополнительная статистика.

4. Нажать Ctrl+Shift+Enter.

Массивы значений X и Y могут располагаться как в столбик, так и в строку, но они должны иметь одинаковый размер.

Известные_значения_x — необязательный аргумент. Если он опущен, то предполагается, что это массив {1; 2; 3;...}, который имеет такой же размер, как и массив_известные_значения_y. Поэтому в примере можно было не указывать этот аргумент.

Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент «конст» имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.

Если аргумент «конст» имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения а подбираются таким образом, чтобы выполнялось соотношение y = аx.

Статистика — логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

Если аргумент «статистика» имеет значение ИСТИНА, функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент «статистика» имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты а и постоянную b.

Множественная линейная регрессия

При многомерном регрессионном анализе линия регрессии представляет вклад ряда независимых переменных в ожидаемый результат

Y=a₁X₁+a₂X₂+a₃X₃+…+a_nX_n

Пример №13

Продавец офисного помещения должен оценить его на основе существующих цен. Предполагается, что существует линейная зависимость между общей площадью помещения (Х1), временем эксплуатации (Х2) и ценой данном районе (У).

Продавец наугад выбирает 11 помещений и получает данные, которые приведены ниже.

Скопируйте таблицу в Excel.

Для приведенного примера зависимость должна иметь вид Y=a₁X₁+a₂X₂.

Найдите коэффициенты а1, а2 и вычислите значения У по полученному уравнения линейной регрессии. Получите цену помещения площадью 150 кв. м., построенного 15 лет назад.