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

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

Описание функции ЛИНЕЙН

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

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

Уравнение прямой:

Синтаксис

= ЛИНЕЙН(известные_y_значения, [известные_x_значения], [константа], [статистика])

Аргументы

known_values_and_known_values_xconststats Обязательный аргумент. Набор уже известных значений y для отношения y = mx + b

• Если в массиве known_y есть один столбец, то каждый столбец массива known_x интерпретируется как отдельная переменная.
• Если в массиве known_y есть одна строка, то каждая строка массива known_x интерпретируется как отдельная переменная.

Необязательный аргумент. Набор значений x, которые уже известны для отношения y = mx + b

• Массив known_x может содержать один или несколько наборов переменных. Если используется только одна переменная, то массивы known_y и known_x могут иметь любую форму, если они имеют одинаковую размерность. Если используется более одной переменной, то известные значения y должны быть вектором (т е высотой в одну строку или шириной в один столбец).
• Если массив known_x опущен, то предполагается, что это массив {1;2;3;…}, который имеет тот же размер, что и массив known_y_values.

Необязательный аргумент. Логическое значение, указывающее, должна ли константа b быть равной 0.

• Если аргумент const равен TRUE или опущен, то константа b вычисляется нормально.
• Если аргумент const равен FALSE, то значение b считается равным 0, а значения m выбираются так, чтобы выполнялось соотношение y = mx.

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

• Если аргумент статистики равен TRUE, функция ЛИНЕЙН возвращает дополнительную статистику регрессии. Возвращаемый массив будет выглядеть следующим образом: {mn;mn-1;…;m1;b:sen;sen-1;…;se1;seb:r2;sey:F;df:ssreg;ssresid}.
• Если статистический аргумент имеет значение FALSE или опущен, функция ЛИНЕЙН возвращает только m коэффициентов и константу b.

Дополнительная статистика регрессии:

На следующем рисунке показан порядок, в котором возвращается дополнительная статистика регрессии:
 

Замечания

• Любая прямая линия может быть описана ее наклоном и точкой пересечения с точкой Y:
  Уклон (м):
  чтобы определить наклон линии, обычно обозначаемый m, нужно взять две точки на линии (x1,y1) и (x2,y2); наклон будет (y2 — y1)/(x2 — x1).

  Y-пересечение (b):
  Точка пересечения линии по оси y, обычно обозначаемая буквой b, представляет собой значение y для точки, в которой линия пересекает ось y.

  Уравнение прямой имеет вид

  y = mx + b Если известны значения m и b, то любую точку на прямой можно найти, подставив в уравнение значения y и x. Вы также можете использовать функцию TREND.

• Если есть только одна независимая переменная x, вы можете получить наклон и точку пересечения y напрямую, используя следующие формулы:

  Наклон:

  =ИНДЕКС(ЛИНЕЙН(известный_y, известный_x),1)

  Y-перехват:

  =ИНДЕКС(ЛИНЕЙН(известный_y, известный_x),2)

• Точность аппроксимации прямой линией, рассчитанной функцией ЛИНЕЙН, зависит от степени дисперсии данных. Чем ближе данные к прямой линии, тем точнее будет модель, используемая функцией. Функция ЛИНЕЙН использует метод наименьших квадратов для определения наилучшего соответствия данным. При наличии только одной независимой переменной x значения m и b рассчитываются по следующим формулам:
   ​
  где: $$\overline{x}, \overline{y}$$ — выборочные средние значения, например:
  x = СРЕДНЕЕ(известные_значения_x) а
  y = СРЕДНЕЕ (известные_y_значения)
• Функции подбора ЛИНЕЙН и LGRPRIBL позволяют вычислить прямую линию или экспоненциальную кривую, которая наилучшим образом соответствует данным. Однако они не отвечают на вопрос, какой из двух результатов больше подходит для решения задачи. Вы также можете вычислить функцию
  TREND(известные значения y; известные значения x) для прямой линии или функции
  РОСТ (известные_y, известные_x) для экспоненциальной кривой. Эти функции, если они не указаны в аргументе new_x_values, возвращают массив вычисленных значений y для фактических значений x вдоль прямой линии или кривой. Затем вы можете сравнить рассчитанные значения с фактическими значениями. Вы также можете создавать диаграммы для визуального сравнения.
• При выполнении регрессионного анализа 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 могут быть идентифицированы в выходных данных ЛИНЕЙН с коэффициентом 0 и значением se, равным 0. Удаление одного или нескольких столбцов как избыточных изменяет значение df, поскольку оно зависит от того, сколько столбцов X фактически используется для прогнозирования.

  Когда df изменяется из-за удаления лишних столбцов, значения sey и F также меняются. Использование коллинеарности часто не рекомендуется. Однако его следует использовать, если какой-либо из столбцов X содержит 0 или 1 в качестве индикатора, указывающего, находится ли субъект эксперимента в отдельной группе. Если const = TRUE или если этот аргумент не указан, ЛИНЕЙН вставляет дополнительный столбец X для моделирования точки пересечения, для мужчин последний столбец удаляется, так как его значения могут быть получены из столбца с «индикатором пола».

• Вычисление df для случаев, когда столбцы X удаляются из модели из-за коллинеарности, выглядит следующим образом: если имеется k столбцов known_x и const = TRUE или unspecified, то df = n — k — 1. Если const = FALSE , то df = п — к. В обоих случаях удаление столбцов X из-за коллинеарности увеличивает значение df на 1.
• Формулы, возвращающие массивы, необходимо вводить как формулы массива.
• При вводе константы массива (например, в качестве аргумента known_x_values) используйте точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Символы-разделители могут различаться в зависимости от региональных настроек.

  Обратите внимание, что значения y, предсказанные уравнением регрессии, могут быть неправильными, если они находятся за пределами диапазона значений y, которые использовались для определения уравнения.

  Основной алгоритм, используемый в функции ЛИНЕЙН, отличается от основного алгоритма, используемого в функциях НАКЛОН и ОТРЕЗОК. Различия между алгоритмами могут привести к различным результатам для неопределенных и коллинеарных данных. Например, если точки данных аргумента known_y равны 0, а точки данных аргумента known_x равны 1, тогда:

  • Функция ЛИНЕЙН возвращает значение 0. Алгоритм ЛИНЕЙН используется для возврата соответствующих значений для коллинеарных данных, и в этом случае можно найти хотя бы один ответ.
  • Функции НАКЛОН и ОТРЕЗОК возвращают ошибку #ДЕЛ/0!. Алгоритм функций НАКЛОН и ОТРЕЗОК используется для нахождения единственного ответа, а в данном случае их может быть несколько.
• Помимо вычисления статистики для других типов регрессий с помощью функции LGRFPRIB, вы можете использовать функцию ЛИНЕЙН для вычисления диапазонов для некоторых других типов регрессий, вводя функции переменных x и y как ряды x и y для ЛИНЕЙН. Например, следующая формула:
  =ЛИНЕЙН(значения y, значения x ^ СТОЛБЦ($A:$C))

  работает со столбцом значений Y и столбцом значений X для вычисления приближения к кубу (многочлен третьей степени) следующим образом:

  у = м1*х + м2*х^2 + м3*х^3 + б

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

• Значение F-теста, возвращаемое функцией ЛИНЕЙН, отличается от значения, возвращаемого функцией ФТЕСТ. ЛИНЕЙН возвращает F-статистику, а ФТЕСТ возвращает вероятность.​

Пример

Простая линейная регрессия

• Применимый к
• Эксель 2003
• Эксель 2007
• Эксель 2010
• Эксель 2013