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

Описание

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

Уравнение для прямой линии имеет следующий вид:

y = mx + b

или

y = m1x1 + m2x2 +... + b

если существует несколько диапазонов значений x, где зависимые значения y — функции независимых значений x. Значения m — коэффициенты, соответствующие каждому значению x, а b — постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn;mn-1;...;m1;b}. Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис

ЛИНЕЙН(известные_значения_y; [известные_значения_x]; [конст]; [статистика])

Аргументы функции ЛИНЕЙН описаны ниже.

Синтаксис

• Известные_значения_y.    Обязательный аргумент. Множество значений y, которые уже известны для соотношения y = mx + b.

  • Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

  • Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

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

  • Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то массивы известные_значения_y и известные_значения_x могут иметь любую форму — при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (т. е. интервалом высотой в одну строку или шириной в один столбец).

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

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

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

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

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

  • Если stats имеет значение TRUE, функция LINEST возвращает дополнительную статистику регрессии; в результате возвращается массив {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r^2,sey; F, df; ssreg,ssresid}.

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

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

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

Замечания

• Любую прямую можно описать ее наклоном и пересечением с осью y:

  Наклон (м):
  Чтобы найти наклон линии, часто написанной как m, возьмите две точки на линии( x1,y1) и (x2,y2); наклон равен (y2 – y1)/(x2 – x1).

  Y-intercept (b):
  Y-перехват линии, часто записываемый как b, — это значение y в точке пересечения линии y-оси.

  Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.

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

  Наклон:
  =INDEX(LINEST(known_y,known_x),1)

  Y-intercept:
  =INDEX(LINEST(known_y,known_x),2)

• Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель ЛИНЕЙН. Функция ЛИНЕЙН использует для определения наилучшей аппроксимации данных метод наименьших квадратов. Когда имеется только одна независимая переменная x, значения m и b вычисляются по следующим формулам:

  

  

  где x и y — выборочные средние значения, например x = СРЗНАЧ(известные_значения_x), а y = СРЗНАЧ(известные_значения_y).

• Функции выравнивания линий и кривых LINEST и LOGEST могут вычислить оптимальную прямую или экспоненциальную кривую, которая соответствует вашим данным. Однако вы должны решить, какой из двух результатов лучше всего подходит для ваших данных. Вы можете вычислить TREND(known_y,known_x) для прямой линии или GROWTH(known_y, known_x) для экспоненциальной кривой. Эти функции без аргумента new_x возвращают массив значений Y, прогнозируемых вдоль этой линии или кривой в фактических точках данных. Затем можно сравнить прогнозируемые значения с фактическими значениями. Вы можете создать диаграмму для визуального сравнения.

• Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений y (без вычитания среднего значения y из частного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента определения r², что является показателем того, насколько хорошо уравнение, полученное в результате регрессии анализа, объясняет связь между переменными. Значение r² равно ssreg/sstotal.

• В некоторых случаях один или несколько столбцов X (предполагается, что Y и X находятся в столбцах) могут не иметь дополнительного прогнозного значения в присутствии других столбцов X. Иными словами, исключение одного или нескольких X-столбцов может привести к прогнозируемым значениям Y с одинаковой точностью. В этом случае эти избыточные столбцы X следует исключить из модели регрессии. Это явление называется "коллинеарностью", так как любой избыточный столбец X может быть выражен в виде суммы кратных столбцов X. Функция LINEST проверяет наличие коллинеарности и удаляет все избыточные столбцы X из модели регрессии при их идентификации. Удаленные столбцы X можно распознать в выходных данных LINEST как имеющие 0 коэффициентов в дополнение к значениям 0 se. Если один или несколько столбцов удаляются как избыточные, df затрагивается, так как df зависит от количества столбцов X, фактически используемых для прогнозирования. Дополнительные сведения о вычислении df см. в примере 4. Если df изменяется из-за удаления избыточных столбцов X, также затрагиваются значения sey и F. Коллинеарность должна быть относительно редкой на практике. Однако один из случаев, когда это более вероятно, заключается в том, что некоторые столбцы X содержат только значения 0 и 1 в качестве индикаторов того, является ли субъект в эксперименте членом определенной группы. Если const = TRUE или опущен, функция LINEST эффективно вставляет дополнительный столбец X со всеми 1 значениями для моделирования перехвата. Если у вас есть столбец с 1 для каждого субъекта , если мужчина, или 0, если нет, и у вас также есть столбец с 1 для каждой темы, если женщина, или 0, если нет, этот столбец является избыточным, так как записи в нем могут быть получены из вычитания записи в столбце "индикатор мужчины" из записи в дополнительном столбце всех 1 значений, добавленных функцией LINEST .

• Вычисление значения df для случаев, когда столбцы X удаляются из модели вследствие коллинеарности происходит следующим образом: если существует k столбцов известных_значений_x и значение конст = ИСТИНА или не указано, то df = n – k – 1. Если конст = ЛОЖЬ, то df = n - k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.

• При вводе константы массива (например, в качестве аргумента известные_значения_x) следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть другими в зависимости от региональных параметров.

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

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

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

  • SLOPE и INTERCEPT возвращают #DIV/0! . Алгоритм функций SLOPE и INTERCEPT предназначен для поиска только одного ответа, и в этом случае может быть несколько ответов.

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

  =ЛИНЕЙН(значения_y, значения_x^СТОЛБЕЦ($A:$C))

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

  y = m1*x + m2*x^2 + m3*x^3 + b

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

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

Примеры