В этой статье описаны синтаксис формулы и использование функции ЛИНЕЙН в Microsoft Excel.
Описание
Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Функцию ЛИНЕЙН также можно объединять с другими функциями для вычисления других видов моделей, являющихся линейными по неизвестным параметрам, включая полиномиальные, логарифмические, экспоненциальные и степенные ряды. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Инструкции приведены в данной статье после примеров.
Уравнение для прямой линии имеет следующий вид:
y = mx + b
или
y = m1x1 + m2x2 +... + b
если существует несколько диапазонов значений x, где зависимые значения y — функции независимых значений x. Значения m — коэффициенты, соответствующие каждому значению x, а b — постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn;mn-1;...;m1;b}. Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.
Синтаксис
ЛИНЕЙН(известные_значения_y; [известные_значения_x]; [конст]; [статистика])
Аргументы функции ЛИНЕЙН описаны ниже.
known_y Обязательно. Множество значений y, которые уже известны для соотношения y = mx + b.
- Если диапазон known_y находится в одном столбце, каждый столбец known_x интерпретируется как отдельная переменная.
- Если диапазон known_y содержится в одной строке, каждая строка known_x интерпретируется как отдельная переменная.
known_x Необязательно. Множество значений x, которые уже известны для соотношения y = mx + b.
- Диапазон known_x может включать один или несколько наборов переменных. Если используется только одна переменная, known_y и known_x могут быть диапазонами любой формы, если у них одинаковые размерности. Если используется более одной переменной, known_y должна быть вектором (т. е. диапазоном с высотой в одну строку или шириной в один столбец).
- Если аргумент "known_x" опущен, предполагается, что массив {1,2,3,...} имеет тот же размер, что и массив known_y.
Продолжить Необязательно. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.
- Если функция const имеет значение ИСТИНА или опущена, b вычисляется нормально.
- Если const имеет значение FALSE, b устанавливается равным 0, а m-значения корректируются по размеру y = mx.
статистика Необязательно. Логическое значение, которое указывает, требуется ли вернуть дополнительную регрессионную статистику.
- Если stats имеет значение TRUE, LINET возвращает дополнительную статистику регрессии; В результате возвращается массив {mn,mn-1,...,M1,B; SEN,SEN-1,...,SE1,SEB; r2,sey; F,df; ssreg,ssresid}.
- Если значение stats равно ЛОЖЬ или опущено, функция ЛИНЕЙН возвращает только m-коэффициенты и константу b.
Дополнительная регрессионная статистика.
| Величина | Описание |
|---|---|
| se1,se2,...,sen | Стандартные значения ошибок для коэффициентов m1,m2,...,mn. |
| seb | Стандартное значение ошибки для константы b (seb = #N/A, если const имеет значение FALSE). |
| R2 | Коэффициент детерминации. Сравнивает предполагаемые и фактические значения y в интервалах от 0 до 1. Если оно равно 1, в выборке имеется идеальная корреляция — разницы между расчетным значением y и фактическим значением y нет. С другой стороны, если коэффициент детерминации равен 0, уравнение регрессии не помогает предсказывать значение y. Сведения о том, как вычисляется2 , см. в разделе "Замечания" далее в этой статье. |
| sey | Стандартная ошибка для оценки y. |
| F | F-статистика или F-наблюдаемое значение. F-статистика используется для определения того, является ли случайной наблюдаемая взаимосвязь между зависимой и независимой переменными. |
| df | Степени свободы. Степени свободы используются для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Дополнительные сведения о вычислении величины df см. ниже в разделе "Замечания". Далее в примере 4 показано использование величин F и df. |
| ssreg | Регрессионная сумма квадратов. |
| ssresid | Остаточная сумма квадратов. Дополнительные сведения о расчете величин ssreg и ssresid см. в подразделе "Замечания" в конце данного раздела. |
На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика.
Замечания
Любую прямую можно описать ее наклоном и пересечением с осью y:
Уклон (м):
Чтобы найти наклон линии, часто записываемой как m, возьмите две точки на прямой, (x1,y1) и (x2,y2); Наклон равен (Y2 - Y1)/(X2 - X1).
Y-перехват (b):
Пересечение с осью Y линии, часто записываемое как b, — это значение y в точке, где линия пересекает ось Y.
Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:
Склон:
=ИНДЕКС(ЛИНЕЙН(known_y;known_x;1)
Y-образный перехват:
=ИНДЕКС(ЛИНЕЙН(known_y;known_x;2)Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель ЛИНЕЙН. Функция ЛИНЕЙН использует для определения наилучшей аппроксимации данных метод наименьших квадратов. Когда имеется только одна независимая переменная x, значения m и b вычисляются по следующим формулам:
где x и y являются выборочными средними; то есть, x = СРЗНАЧ(известные значения, связанные с known_y , и y = СРЗНАЧ('s).Функции подгонки линий и кривых ЛИНЕЙН и ЛОГИСТ могут вычислить наилучшую прямую или экспоненциальную кривую, соответствующую вашим данным. Однако необходимо решить, какой из двух результатов лучше всего соответствует вашим данным. Вы можете вычислить TREND(known_y's,known_x's) для прямой линии или GROWTH(known_y's, known_x's) для экспоненциальной кривой. Эти функции без аргумента new_x возвращают массив значений y, спрогнозированных вдоль этой линии или кривой в фактических точках данных. Затем можно сравнить предсказанные значения с фактическими. Для визуального сравнения можно составить диаграммы для их обоих.
Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если аргумент const = TRUE или опущен, общая сумма квадратов является суммой возведенных в квадраты разностей между фактическими значениями y и средним значением y. Если аргумент const = FALSE, общая сумма квадратов является суммой квадратов фактических значений y (без вычитания среднего значения y из каждого отдельного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминации r2, который является показателем того, насколько хорошо уравнение, полученное в результате регрессионного анализа, объясняет связь между переменными. Значение r2 равно ssreg/sstotal.
В некоторых случаях один или несколько столбцов X (предполагая, что столбцы Y и X находятся в столбцах) могут не иметь дополнительной прогностической ценности при наличии других столбцов X. Другими словами, удаление одного или нескольких столбцов X может привести к предсказанию значений Y, которые будут одинаково точными. В этом случае эти избыточные столбцы X следует исключить из модели регрессии. Это явление называется «коллинеарностью», потому что любой избыточный столбец X может быть выражен как сумма кратных неизбыточных столбцов X. Функция ЛИНЕЙН проверяет коллинеарность и удаляет все избыточные столбцы X из модели регрессии, когда она их обнаруживает. Удаленные столбцы X могут распознаваться в выходных данных линейны как имеющие 0 коэффициентов в дополнение к 0 значениям se. Если один или несколько столбцов удаляются как избыточные, это влияет на функцию df, поскольку она зависит от количества столбцов X, фактически используемых в целях прогнозирования. Дополнительные сведения о вычислении df см. в примере 4. Если df изменяется из-за удаления избыточных столбцов X, значения sey и F также меняются. На практике коллинеарность должна быть относительно редкой. Однако один из случаев, когда он более вероятен, — это когда некоторые столбцы X содержат только значения 0 и 1 в качестве индикаторов того, является ли испытуемый в эксперименте членом определенной группы или нет. Если const = TRUE или опущен, функция ЛИНЕЙН вставляет дополнительный столбец X из всех значений 1 для моделирования отсекаемого отрезка. Если у вас есть столбец с 1 для каждого субъекта, если мужчина, или 0, если нет, а также у вас есть столбец с 1 для каждого субъекта, если женщина, или 0 если нет, этот последний столбец является избыточным, потому что записи в нем могут быть получены путем вычитания записи в столбце «мужской индикатор» из записи в дополнительном столбце всех значений 1, добавленных функцией ЛИНЕЙН .
Значение df вычисляется следующим образом, когда из модели не удаляются X столбцов из-за коллинеарности: если есть k столбцов known_x и const = TRUE или опущен, df = n – k – 1. Если const = FALSE, df = n - k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.
При вводе константы массива (например, known_x) в качестве аргумента используйте точку с запятой для разделения значений, содержащихся в одной строке, и точку с запятой — в разных строках. Знаки-разделители могут быть другими в зависимости от региональных параметров.
Следует отметить, что значения y, предсказанные с помощью уравнения регрессии, возможно, не будут правильными, если они располагаются вне интервала значений y, которые использовались для определения уравнения.
Основной алгоритм, используемый в функции ЛИНЕЙН, отличается от основного алгоритма функций НАКЛОН и ОТРЕЗОК. Разница между алгоритмами может привести к различным результатам при неопределенных и коллинеарных данных. Например, если точки данных аргумента known_y равны 0, а точки данных аргумента known_x равны 1:
- Функция ЛИНЕЙН возвращает значение, равное 0. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.
- НАКЛОН и ПЕРЕХВАТ возвращают #DIV/0! . Алгоритм функций НАКЛОН и ПЕРЕХВАТ предназначен для поиска только одного ответа, и в этом случае ответов может быть несколько.
Помимо вычисления статистики для других типов регрессии с помощью функции ЛГРФПРИБЛ, для вычисления диапазонов некоторых других типов регрессий можно использовать функцию ЛИНЕЙН, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:
=ЛИНЕЙН(значения_y, значения_x^СТОЛБЕЦ($A:$C))
работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:
y = m1*x + m2*x^2 + m3*x^3 + b
Формула может быть изменена для расчетов других типов регрессии, но в отдельных случаях требуется корректировка выходных значений и других статистических данных.Значение F-теста, возвращаемое функцией ЛИНЕЙН, отличается от значения, возвращаемого функцией ФТЕСТ. Функция ЛИНЕЙН возвращает F-статистику, в то время как ФТЕСТ возвращает вероятность.
Примеры
Пример 1. Наклон и Y-пересечение
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
| Известные значения y | Известные значения x |
|---|---|
| 1 | 0 |
| 9 | 4 |
| 5 | 2 |
| 7 | 3 |
| Результат (наклон) | Результат (y-пересечение) |
| 2 | 1 |
| Формула (формула массива в ячейках A7:B7) | |
| =ЛИНЕЙН(A2:A5;B2:B5;;ЛОЖЬ) |
Пример 2. Простая линейная регрессия
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
| Месяц | Продажи |
|---|---|
| 1 | 3 100 ₽ |
| 2 | 4 500 ₽ |
| 3 | 4 400 ₽ |
| 4 | 5 400 ₽ |
| 5 | 7 500 ₽ |
| 6 | 8 100 ₽ |
| Формула | Результат |
| =СУММ(ЛИНЕЙН(B1:B6; A1:A6)*{9,1}) | 11 000 ₽ |
| Вычисляет предполагаемый объем продаж в девятом месяце на основе данных о продажах за период с первого по шестой месяцы. |
Пример 3. Множественная линейная регрессия
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
| Общая площадь (x1) | Количество офисов (x2) | Количество входов (x3) | Время эксплуатации (x4) | Оценочная цена (y) |
|---|---|---|---|---|
| 2310 | 2 | 2 | 20 | 142 000 ₽ |
| 2333 | 2 | 2 | 12 | 144 000 ₽ |
| 2356 | 3 | 1,5 | 33 | 151 000 ₽ |
| 2379 | 3 | 2 | 43 | 150 000 ₽ |
| 2402 | 2 | 3 | 53 | 139 000 ₽ |
| 2425 | 4 | 2 | 23 | 169 000 ₽ |
| 2448 | 2 | 1,5 | 99 | 126 000 ₽ |
| 2471 | 2 | 2 | 34 | 142 900 ₽ |
| 2494 | 3 | 3 | 23 | 163 000 ₽ |
| 2517 | 4 | 4 | 55 | 169 000 ₽ |
| 2540 | 2 | 3 | 22 | 149 000 ₽ |
| -234,2371645 | ||||
| 13,26801148 | ||||
| 0,996747993 | ||||
| 459,7536742 | ||||
| 1732393319 | ||||
| Формула (формула динамического массива, введенная в ячейке A19) | ||||
| =ЛИНЕЙН(E2:E12; A2:D12; ИСТИНА; ИСТИНА) |
Пример 4. Использование статистики F и R2
В предыдущем примере коэффициент детерминации, или r2, равен 0,99675 (см. ячейку A17 в выходных данных для функции ЛИНЕЙН), что указывает на сильную связь между независимыми переменными и ценой продажи. Вы можете использовать F-статистику, чтобы определить, были ли эти результаты получены случайно при таком высоком значении r2.
Предположим, что на самом деле взаимосвязи между переменными не существует, просто статистический анализ вывел сильную взаимозависимость по взятой равномерной выборке 11 зданий. Величина "Альфа" используется для обозначения вероятности ошибочного вывода о существовании сильная взаимозависимости.
Значения F и df в выходных данных функции ЛИНЕЙН можно использовать для оценки вероятности случайного появления более высокого значения F. F можно сравнить с критическими значениями в опубликованных таблицах F-распределения или использовать функцию FDIST в Excel для вычисления вероятности случайного появления большего значения F. Соответствующее F-распределение имеет степени свободы v1 и v2. Если n — число точек данных, а const = TRUE или опущен, то v1 = n — df — 1 и v2 = df. (Если const = FALSE, то v1 = n – df и v2 = df.) Функция FDIST с синтаксисом FDIST(F, v1, v2) возвращает вероятность случайного появления более высокого значения F. В этом примере df = 6 (ячейка B18) и F = 459,753674 (ячейка A18).
Если предположить, что значение Alpha равно 0,05, v1 = 11 – 6 – 1 = 4 и v2 = 6, то критический уровень F равен 4,53. Поскольку F = 459,753674 намного выше, чем 4,53, крайне маловероятно, что такое высокое значение F было случайным. (При Альфа = 0,05 гипотеза об отсутствии связи между known_y и known_x должна быть отвергнута, когда F превышает критический уровень, 4,53.) Функцию FDIST в Excel можно использовать для получения вероятности того, что такое высокое значение F было случайным. Например, FDIST(459.753674, 4, 6) = 1.37E-7, крайне малая вероятность. Вы можете сделать вывод, либо найдя критический уровень F в таблице, либо используя функцию FDIST , что уравнение регрессии полезно для прогнозирования оценочной стоимости офисных зданий в этой области. Помните, что очень важно использовать правильные значения v1 и v2, которые были вычислены в предыдущем абзаце.
Пример 5. Вычисление t-статистики
Другой тест позволяет определить, подходит ли каждый коэффициент наклона для оценки стоимости здания под офис в примере 3. Например, чтобы проверить, имеет ли срок эксплуатации здания статистическую значимость, разделим -234,24 (коэффициент наклона для срока эксплуатации здания) на 13,268 (оценка стандартной ошибки для коэффициента времени эксплуатации из ячейки A15). Ниже приводится наблюдаемое t-значение:
t = m4 ÷ se4 = –234,24 ÷ 13,268 = –17,7
Если абсолютное значение t достаточно велико, можно сделать вывод, что коэффициент наклона можно использовать для оценки стоимости здания под офис в примере 3. В таблице ниже приведены абсолютные значения четырех наблюдаемых t-значений.
Если обратиться к справочнику по математической статистике, то окажется, что t-критическое двустороннее с 6 степенями свободы равно 2,447 при Альфа = 0,05. Критическое значение также можно также найти с помощью функции Microsoft Excel СТЬЮДРАСПОБР. СТЬЮДРАСПОБР(0,05; 6) = 2,447. Поскольку абсолютная величина t, равная 17,7, больше, чем 2,447, срок эксплуатации — это важная переменная для оценки стоимости здания под офис. Аналогичным образом можно протестировать все другие переменные на статистическую значимость. Ниже приводятся наблюдаемые t-значения для каждой из независимых переменных.
| Переменная | t-наблюдаемое значение |
|---|---|
| Общая площадь | 5,1 |
| Количество офисов | 31,3 |
| Количество входов | 4,8 |
| Возраст | 17,7 |
Абсолютная величина всех этих значений больше, чем 2,447. Следовательно, все переменные, использованные в уравнении регрессии, полезны для предсказания оценочной стоимости здания под офис в данном районе.