Функция ЛИНЕЙН Excel - статистика линейной регрессии

Скачать пример рабочей книги

Загрузите образец книги

В этом руководстве показано, как использовать Функция ЛИНЕЙН в Excel в Excel для расчета статистики по линии тренда.

Обзор функции ЛИНЕЙН

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

Чтобы использовать функцию листа Excel ЛИНЕЙН, выберите ячейку и введите:

(Обратите внимание, как появляются входные данные формулы)

Синтаксис и входные данные функции ЛИНЕЙН

1	= ЛИНЕЙН (известное_ис; известное_x; константа; статистика)

known_y’s - Массив известных значений Y.

known_x’s - Массив известных значений X.

const - ПО ЖЕЛАНИЮ. Логическое значение, указывающее, следует ли вычислять B (точка пересечения в y = mx + b) с использованием метода наименьших квадратов (TRUE или Ommitted) или вручную установить B = 0 (FALSE).

статистика - ПО ЖЕЛАНИЮ. Вернуть дополнительную статистику (ИСТИНА) или вернуть только m (наклон) и b (пересечение) (ЛОЖЬ или пропущено)

Что такое ЛИНЕЙН?

Функция ЛИНЕЙН в Excel - это функция, используемая для создания статистики регрессии для модели линейной регрессии. ЛИНЕЙН - это формула массива, которую можно использовать отдельно или с другими функциями для вычисления конкретной статистики о модели.

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

ЛИНЕЙН похож на ПРОГНОЗ в том смысле, что он дает аналогичный результат, но с гораздо большей информацией о вашей регрессионной модели, а также с возможностью учесть более одной независимой переменной.

Предположим, у меня есть таблица данных с Икс а также у значения, где Икс - независимая переменная и у зависимая переменная:

Я хочу знать, каково уравнение регрессии приведенных выше данных. Используя ЛИНЕЙН:

1	= ЛИНЕЙН (B3: B7; C3: C7; ИСТИНА; ЛОЖЬ)

Значение точки пересечения по оси Y здесь эквивалентно 0 в экспоненциальном представлении.

Уравнение линии y = 2x + 0. Обратите внимание, что функция ЛИНЕЙН возвращает оба наклон и пересечение линии. Чтобы вернуть оба значения, формулу необходимо ввести как формулу массива. Подробнее о формулах массива позже.

Как пользоваться ЛИНЕЙН

Функция ЛИНЕЙН принимает четыре аргумента:

1	= ЛИНЕЙН (известное_y, известное_x, константа, статистика)

Где,

Аргумент	Описание
known_y’s а также known_x’s	Это Икс а также у данные в вашей таблице данных
const	Параметр ИСТИНА / ЛОЖЬ, указывающий, должно ли пересечение оси Y быть принудительно равным 0 или рассчитываться как обычно.
статистика	Параметр TRUE / FALSE, следует ли возвращать дополнительную статистику регрессии

В нашем первом примере функция записывается как:

1	= ЛИНЕЙН (B3: B7; C3: C7; ИСТИНА; ЛОЖЬ)

Когда статистика Если для параметра установлено значение ИСТИНА, регрессионная статистика будет организована следующим образом:

Вам может быть интересно, что означает каждая переменная.

Статистика	Описание
мп	Коэффициенты наклона для переменные x
б	y-перехват
seп	Стандартная ошибка для каждого коэффициента наклона
seб	Стандартная ошибка для точки пересечения по оси Y
р2	Коэффициент детерминации
seу	Стандартная ошибка для у оценивать
F	Статистика F (чтобы определить, возникает ли связь между переменными случайно)
dж	Степени свободы
SSрег	Сумма квадратов регрессии
SSостаток	Остаточная сумма квадратов

Основные статистические данные, которые необходимо понять, - это коэффициенты наклона, точка пересечения по оси y и коэффициент детерминации или r² стоимость модели.

Используя приведенный выше пример и выбрав TRUE для статистика параметр:

Выделенные ячейки показывают наклон = 2, точку пересечения = 0 и r.² = 1.

R² значение - показатель силы корреляции модели. Это можно рассматривать как показатель соответствия. Низкий r² значение будет означать плохую корреляцию между вашими зависимыми и независимыми переменными, и обратное верно для высоких r² значения, с r² = 1 идеально подходит.

В выпусках Excel в Microsoft 365 (ранее Office 365) после января 2022 года динамические массивы изменили способ вычисления формул массива. Больше не требуется использовать CTRL + SHIFT + ENTER или выделять область ячеек, которую займет массив. Просто введите формулу и нажмите Enter, и полученные ячейки «выльются» в массив.

В оставшейся части этой статьи мы будем ссылаться на использование ЛИНЕЙН в отношении динамических массивов в Microsoft 365 Excel.

Прогнозирование с помощью ЛИНЕЙН (простая регрессия)

Комбинирование функций ЛИНЕЙН и СУММ может использоваться для прогнозирования значения зависимой переменной. у, учитывая известные Икс а также у данные. Ниже приведен пример, показывающий, что у значение будет при x = 14.

1	= СУММ (ЛИНЕЙН (C3: C7; B3: B7) * {14,1})

Модель имеет вид y = mx + b. Это то же самое, что и y = a + bx, только другой способ представления уравнения. Совет, о котором следует помнить при работе с линейными уравнениями, - это переменная рядом с Икс - всегда наклон, а переменная, следующая за знаком плюс или минус, всегда является точкой пересечения, независимо от букв, используемых в уравнении.

Использование формулы: = СУММ (ЛИНЕЙН (C3: C7, B3: B7) * {14,1}) возвращает результат 28. Поскольку это единственный результат, нет необходимости вводить его как массив.

В конце приведенной выше формулы * {14,1} указывается независимая переменная, которая будет использоваться для прогнозирования зависимой переменной, в данном случае 14.

Мы можем проверить это, введя x = 14 в уравнение линии, y = 2x + 0.

Прогнозирование с помощью функции ЛИНЕЙН (множественная линейная регрессия)

Следующая таблица данных взята со страницы ЛИНЕЙН на веб-сайте поддержки Microsoft.

В некоторых случаях существует несколько независимых переменных, которые следует учитывать при создании модели линейной регрессии. Это называется множественной линейной регрессией (т. Е. Множественными независимыми переменными). Если я хочу оценить стоимость офисного здания, такие вещи, как площадь пола, количество входов в здание, возраст здания и количество офисов, будут частью уравнения. Давайте посмотрим на пример.

Набрав формулу ЛИНЕЙН в ячейку G29 и выполнив ее, мы получим:

1	= ЛИНЕЙН (E3: E13; A3: D13; ИСТИНА; ИСТИНА)

Модель представлена ​​в следующем виде:

Помните, что массив результатов ЛИНЕЙН находится в порядке, обратном уравнению. В приведенном выше примере 52 317,8 - это наш перехват, b, а 27,6 - это m.₁ или значение уклона для переменной Жилая площадь, Икс₁.

Используя функцию ЛИНЕЙН с предоставленными данными, наша модель регрессии:

С r² значение 0,997, что указывает на сильную или сильно коррелированную модель. Используя модель, теперь вы можете предсказать, какая оценочная стоимость офисного здания будет основана на любой комбинации вышеуказанных независимых переменных.

ЛИНЕЙН советы

1. Убедитесь, что у вас установлена ​​самая последняя версия Microsoft 365 для использования функции ЛИНЕЙН с динамическими массивами. Возможно, вам потребуется включить текущий канал предварительной оценки Office (предварительная версия), чтобы использовать функции динамического массива. На странице учетной записи:
   
2. Если вы используете версию, отличную от Microsoft 365, вам придется использовать устаревший метод CTRL + SHIFT + ENTER (CSE) для оценки формул массива.
3. При использовании устаревшего метода количество столбцов, выделяемых при вводе функции массива ЛИНЕЙН, всегда равно количеству столбцов. Икс переменные в ваших данных плюс 1. Число строк, которые нужно выбрать для массива, равно 5.
4. Если вы будете делиться своей версией Excel с динамическим массивом с кем-то, кто использует версию, отличную от Microsoft 365, используйте устаревший метод CSE, чтобы избежать проблем с совместимостью.

Заинтересованы в большем прогнозировании?

См. Другие наши статьи о прогнозировании с экспоненциальным сглаживанием, функциях ТЕНДЕНЦИЯ, РОСТ и ЛИНЕЙН.

ЛИНЕЙН функция в Google Таблицах

Функция ЛИНЕЙН работает в Google Таблицах точно так же, как и в Excel.

Примеры ЛИНЕЙН в VBA

Вы также можете использовать функцию ЛИНЕЙН в VBA. Тип:
application.worksheetfunction.linest (известные_вы, известные_x, константа, статистика)

Выполнение следующего оператора VBA

1	Диапазон ("D2") = Application.WorksheetFunction.LinEst (Диапазон ("A2: A8"), Диапазон ("B2: B8"))

даст следующие результаты

Для аргументов функции (известные_y и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные, которые будут использоваться вместо них.

Вернуться к списку всех функций в Excel