Функция линейн в excel как пользоваться
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью линейной функции y = a x + b .
Метод наименьших квадратов (англ. Ordinary Least Squares , OLS ) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным.
Рассмотрим приближение функциями, зависящими только от одной переменной:
- Линейная: y=ax+b (эта статья)
- Логарифмическая : y=a*Ln(x)+b
- Степенная : y=a*x m
- Экспоненциальная : y=a*EXP(b*x)+с
- Квадратичная : y=ax 2 +bx+c
Примечание : Случаи приближения полиномом с 3-й до 6-й степени рассмотрены в этой статье. Приближение тригонометрическим полиномом рассмотрено здесь.
Линейная зависимость
Нас интересует связь 2-х переменных х и y . Имеется предположение, что y зависит от х по линейному закону y = ax + b . Чтобы определить параметры этой взаимосвязи исследователь провел наблюдения: для каждого значения х i произведено измерение y i (см. файл примера ). Соответственно, пусть имеется 20 пар значений (х i ; y i ).
Для наглядности рекомендуется построить диаграмму рассеяния.
Примечание: Если шаг изменения по х постоянен, то для построения диаграммы рассеяния можно использовать тип График , если нет, то необходимо использовать тип диаграммы Точечная .
Из диаграммы очевидно, что связь между переменными близка к линейной. Чтобы понять какая из множества прямых линий наиболее «правильно» описывает зависимость между переменными, необходимо определить критерий, по которому будут сравниваться линии.
В качестве такого критерия используем выражение:
Вышеуказанное выражение представляет собой сумму квадратов расстояний между наблюденными значениями y i и ŷ i и часто обозначается как SSE ( Sum of Squared Errors ( Residuals ), сумма квадратов ошибок (остатков) ) .
Метод наименьших квадратов заключается в подборе такой линии ŷ = ax + b , для которой вышеуказанное выражение принимает минимальное значение.
Примечание: Любая линия в двухмерном пространстве однозначно определяется значениями 2-х параметров: a (наклон) и b (сдвиг).
Считается, что чем меньше сумма квадратов расстояний, тем соответствующая линия лучше аппроксимирует имеющиеся данные и может быть в дальнейшем использована для прогнозирования значений y от переменной х. Понятно, что даже если в действительности никакой взаимосвязи между переменными нет или связь нелинейная, то МНК все равно подберет «наилучшую» линию. Таким образом, МНК ничего не говорит о наличии реальной взаимосвязи переменных, метод просто позволяет подобрать такие параметры функции a и b , для которых вышеуказанное выражение минимально.
Проделав не очень сложные математические операции (подробнее см. статью про квадратичную зависимость ), можно вычислить параметры a и b :
Как видно из формулы, параметр a представляет собой отношение ковариации и дисперсии , поэтому в MS EXCEL для вычисления параметра а можно использовать следующие формулы (см. файл примера лист Линейная ):
= КОВАР(B26:B45;C26:C45)/ ДИСП.Г(B26:B45) или
Также для вычисления параметра а можно использовать формулу = НАКЛОН(C26:C45;B26:B45) . Для параметра b используйте формулу = ОТРЕЗОК(C26:C45;B26:B45) .
И наконец, функция ЛИНЕЙН() позволяет вычислить сразу оба параметра. Для ввода формулы ЛИНЕЙН(C26:C45;B26:B45) необходимо выделить в строке 2 ячейки и нажать CTRL + SHIFT + ENTER (см. статью про формулы массива, возвращающими несколько значений ). В левой ячейке будет возвращено значение а , в правой – b .
Примечание : Чтобы не связываться с вводом формул массива потребуется дополнительно использовать функцию ИНДЕКС() . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);1) или просто = ЛИНЕЙН(C26:C45;B26:B45) вернет параметр, отвечающий за наклон линии, т.е. а . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);2) вернет параметр, отвечающий за пересечение линии с осью Y, т.е. b .
Вычислив параметры, на диаграмме рассеяния можно построить соответствующую линию.
Инструмент диаграммы Линия тренда
Еще одним способом построения прямой линии по методу наименьших квадратов является инструмент диаграммы Линия тренда . Для этого выделите диаграмму, в меню выберите вкладку Макет , в группе Анализ нажмите Линия тренда , затем Линейное приближение .
Поставив в диалоговом окне галочку в поле «показывать уравнение на диаграмме» можно убедиться, что найденные выше параметры совпадают со значениями на диаграмме.
Примечание : Для того, чтобы параметры совпадали необходимо, чтобы тип у диаграммы был Точечная, а не График . Дело в том, что при построении диаграммы График значения по оси Х не могут быть заданы пользователем (пользователь может указать только подписи, которые не влияют на расположение точек). Вместо значений Х используется последовательность 1; 2; 3; … (для нумерации категорий). Поэтому, если строить линию тренда на диаграмме типа График , то вместо фактических значений Х будут использованы значения этой последовательности, что приведет к неверному результату (если, конечно, фактические значения Х не совпадают с последовательностью 1; 2; 3; …).
СОВЕТ : Подробнее о построении диаграмм см. статьи Основы построения диаграмм и Основные типы диаграмм .
В этой статье описаны синтаксис формулы и использование функции LINEST в Microsoft Excel. Ссылки на дополнительные сведения о диаграммах и выполнении регрессионного анализа можно найти в разделе См. также.
Описание
Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Функцию ЛИНЕЙН также можно объединять с другими функциями для вычисления других видов моделей, являющихся линейными по неизвестным параметрам, включая полиномиальные, логарифмические, экспоненциальные и степенные ряды. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Инструкции приведены в данной статье после примеров.
Уравнение для прямой линии имеет следующий вид:
y = m1x1 + m2x2 +. + b
если существует несколько диапазонов значений x, где зависимые значения y — функции независимых значений x. Значения m — коэффициенты, соответствующие каждому значению x, а b — постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив . Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.
Синтаксис
ЛИНЕЙН(известные_значения_y; [известные_значения_x]; [конст]; [статистика])
Аргументы функции ЛИНЕЙН описаны ниже.
Синтаксис
Известные_значения_y. Обязательный аргумент. Множество значений y, которые уже известны для соотношения y = mx + b.
Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.
Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.
Известные_значения_x. Необязательный аргумент. Множество значений x, которые уже известны для соотношения y = mx + b.
Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то массивы известные_значения_y и известные_значения_x могут иметь любую форму — при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (т. е. интервалом высотой в одну строку или шириной в один столбец).
Если массив известные_значения_x опущен, то предполагается, что это массив , имеющий такой же размер, что и массив известные_значения_y.
Конст. Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.
Если аргумент конст имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.
Если аргумент конст имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.
Статистика. Необязательный аргумент. Логическое значение, которое указывает, требуется ли вернуть дополнительную регрессионную статистику.
Если статистика имеет true, то LINEST возвращает дополнительную регрессию; в результате возвращается массив .
Если аргумент статистика имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.
Дополнительная регрессионная статистика.
Стандартные значения ошибок для коэффициентов m1,m2. mn.
Коэффициент определения. Сравнивает предполагаемые и фактические значения y и диапазоны значений от 0 до 1. Если значение 1, то в выборке будет отличная корреляция— разница между предполагаемым значением y и фактическим значением y не существует. С другой стороны, если коэффициент определения — 0, уравнение регрессии не помогает предсказать значение y. Сведения о том, как вычисляется 2, см. в разделе "Замечания" далее в этой теме.
Стандартная ошибка для оценки y.
F-статистика или F-наблюдаемое значение. F-статистика используется для определения того, является ли случайной наблюдаемая взаимосвязь между зависимой и независимой переменными.
Степени свободы. Степени свободы используются для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Дополнительные сведения о вычислении величины df см. ниже в разделе "Замечания". Далее в примере 4 показано использование величин F и df.
Регрессионная сумма квадратов.
Остаточная сумма квадратов. Дополнительные сведения о расчете величин ssreg и ssresid см. в подразделе "Замечания" в конце данного раздела.
На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика.
Замечания
Любую прямую можно описать ее наклоном и пересечением с осью y:
Наклон (m):
Чтобы найти наклон линии, обычно записанной как m, возьмите две точки на строке (x1;y1) и (x2;y2); наклон равен (y2 - y1)/(x2 - x1).
Y-перехват (b):
Y-пересечение строки, обычно записанное как b, — это значение y в точке, в которой линия пересекает ось y.
Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.
Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:
Наклон:
=ИНДЕКС( LINEST(known_y,known_x's);1)
Y-перехват:
=ИНДЕКС( LINEST(known_y,known_x),2)
Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель ЛИНЕЙН. Функция ЛИНЕЙН использует для определения наилучшей аппроксимации данных метод наименьших квадратов. Когда имеется только одна независимая переменная x, значения m и b вычисляются по следующим формулам:
где x и y — выборочные средние значения, например x = СРЗНАЧ(известные_значения_x), а y = СРЗНАЧ(известные_значения_y).
Функции ЛИННЕСТРОЙ и ЛОГЪЕСТ могут вычислять наилучшие прямые или экспоненциальное кривой, которые подходят для ваших данных. Однако необходимо решить, какой из двух результатов лучше всего подходит для ваших данных. Вы можетевычислить known_y(known_x) для прямой линии или РОСТ(known_y, known_x в) для экспоненциальной кривой. Эти функции без аргумента new_x возвращают массив значений y, спрогнозируемых вдоль этой линии или кривой в фактических точках данных. Затем можно сравнить спрогнозируемые значения с фактическими значениями. Для наглядного сравнения можно отобразить оба этих диаграммы.
Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений y (без вычитания среднего значения y из частного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента определения r 2 — индикатор того, насколько хорошо уравнение, выданное в результате регрессионного анализа, объясняет связь между переменными. Значение r 2 равно ssreg/sstotal.
В некоторых случаях один или несколько столбцов X (предполагается, что значения Y и X — в столбцах) могут не иметь дополнительного прогнозируемого значения при наличии других столбцов X. Другими словами, удаление одного или более столбцов X может привести к одинаковой точности предсказания значений Y. В этом случае эти избыточные столбцы X следует не использовать в модели регрессии. Этот вариант называется "коллинеарность", так как любой избыточный X-столбец может быть выражен как сумма многих не избыточных X-столбцов. Функция ЛИНЕЙН проверяет коллинеарность и удаляет все избыточные X-столбцы из модели регрессии при их идентификации. Удалены столбцы X распознаются в результатах LINEST как имеющие коэффициенты 0 в дополнение к значениям 0 se. Если один или несколько столбцов будут удалены как избыточные, это влияет на df, поскольку df зависит от числа X столбцов, фактически используемых для прогнозирования. Подробные сведения о вычислении df см. в примере 4. Если значение df изменилось из-за удаления избыточных X-столбцов, это также влияет на значения Sey и F. Коллинеарность должна быть относительно редкой на практике. Однако чаще всего возникают ситуации, когда некоторые столбцы X содержат только значения 0 и 1 в качестве индикаторов того, является ли тема в эксперименте участником определенной группы или не является ее участником. Если конст = ИСТИНА или опущен, функция LYST фактически вставляет дополнительный столбец 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. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.
Помимо вычисления статистики для других типов регрессии с помощью функции ЛГРФПРИБЛ, для вычисления диапазонов некоторых других типов регрессий можно использовать функцию ЛИНЕЙН, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:
работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:
y = m1*x + m2*x^2 + m3*x^3 + b
Формула может быть изменена для расчетов других типов регрессии, но в отдельных случаях требуется корректировка выходных значений и других статистических данных.
Значение F-теста, возвращаемое функцией ЛИНЕЙН, отличается от значения, возвращаемого функцией ФТЕСТ. Функция ЛИНЕЙН возвращает F-статистику, в то время как ФТЕСТ возвращает вероятность.
Примеры
Пример 1. Наклон и Y-пересечение
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
При использовании функции ЛИНЕЙН на листе в Microsoft Excel результаты статистического вывода могут возвращать неверные значения. Средство регрессия в окне "пакет анализа" может также возвращать неверные значения.
Причина
Результат, возвращаемый функцией ЛИНЕЙН, может быть неправильным, если выполняется одно или несколько из указанных ниже условий.
Диапазон значений x перекрывает диапазон значений y.
Количество строк в диапазоне входных данных меньше числа столбцов в общем диапазоне (x-value + y-Value).
Вы задаете нулевую константу (для третьего аргумента функции ЛИНЕЙН установите значение истина).
Обходное решение
Случай 1: диапазоны x-value и y перекрываются
Если диапазоны x-value и y перекрываются, функция ЛИНЕЙН возвращает неверные значения во всех ячейках результата. Нормальная статистическая вероятность запрещает значения в диапазонах x и y для перекрытия (повторяющиеся друг друга). Не перекрывают диапазоны x и y при ссылке на ячейки в формуле.Примечание. Средство регрессия предупреждает об этой проблеме и не продолжает работу. Вы можете использовать средство регрессия вместо функции ЛИНЕЙН. В Microsoft Office Excel 2007 вы можете найти инструмент регрессия, щелкнув анализ данных в группе анализ на вкладке данные . В Microsoft Office Excel 2003 и более ранних версиях Excel можно найти инструмент регрессия, выбрав пункт анализ данных в меню Сервис .
Случай 2: количество строк меньше числа столбцов x-Columns.
Статистические функции не действительны, так как количество строк должно быть меньше числа столбцов x (переменных). Количество строк данных должно быть больше количества столбцов данных (столбцов x и y).
Случай 3: указывается нулевая константа
Не указывайте нулевые константы (b = 0) в функции.
Дополнительная информация
Средство регрессия входит в пакет анализа. Пакет анализа — это программа надстройки Excel. Оно доступно при установке Microsoft Office или Excel. Прежде чем использовать средство регрессия в Excel, вы должны загрузить анализ ToolPak.To в Excel 2007, выполнив указанные ниже действия.
Выберите пункт надстройки, а затем в поле Управление выберите пункт надстройки Excel .
В окне Доступные надстройки установите флажок Пакет анализа , а затем нажмите кнопку ОК.Примечание. Если в списке Доступные надстройки не указан Пакет анализа , нажмите кнопку Обзор , чтобы найти его.
Чтобы сделать это в Excel 2003 и более ранних версиях Excel, выполните указанные ниже действия.
В меню Сервисвыберите пунктнадстройки.
В диалоговом окне надстройки выберите Пакет анализаи нажмите кнопку ОК,Обратите внимание на то, что Пакет анализа не указан в поле Доступные надстройки, нажмите кнопку Обзор , чтобы найти его.
Ссылки
Статистические вычисления на цифровом компьютере. Уильям J. Hemmerle. Blaisdell компания публикации: 1967. Глава 3, "вычисления с несколькими регрессиями" и раздел 3.2.1, "теория для предварительной регрессии".
Функция ЛИНЕЙН() может использоваться для простой регрессии (в этом случае прогнозируемая переменная Y зависит от одной контролируемой переменной Х) и для множественной регрессии (Y зависит от нескольких Х).
Рассмотрим функцию на примере простой регрессии (оценивается наклон и сдвиг линии регрессии). Использование функции в случае множественной регрессии рассмотрено в соответствующей статье про множественную регрессию .
Функция ЛИНЕЙН() возвращает несколько значений, поэтому для вывода результатов потребуется несколько ячеек. Часто функцию вводят как формулу массива : нажатием клавиш CTRL + SHIFT + ENTER , но, как будет показано ниже, для вывода результатов вычислений это не обязательно.
Функция работает в 2-х режимах. В простейшем случае, когда 4-й аргумент функции опущен или установлен ЛОЖЬ, функция возвращает только 2 значения - это оценки параметров модели: наклона a и сдвига b.
Для того, чтобы вычислить оценки:
- выделите 2 ячейки в одной строке,
- в Строке формул введите, например, = ЛИНЕЙН(C23:C83;B23:B83)
- нажмите CTRL+SHIFT+ENTER.
В левой ячейке будет рассчитано значение наклона , в правой – сдвига .
Примечание : В справке MS EXCEL результат функции ЛИНЕЙН() соответствующий наклону обозначается буквой m, а сдвиг – буквой b.
Примечание : Без формул массива можно обойтись. Для этого нужно использовать функцию ИНДЕКС() , которая выведет нужное значение. Например, чтобы вывести величину сдвига линии регрессии введите формулу = ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83);1;2) . Если 4-й аргумент функции опущен или установлен ЛОЖЬ, то функция ЛИНЕЙН() в возвращает массив значений вида 1х2 (т.е. 2 ячейки, расположенные в одной строке). Поэтому, для вывода величины сдвига прямой линии регрессии, первый аргумент функции ИНДЕКС() , который является номером строки, должен быть равен 1, а второй аргумент, номер столбца, должен быть равен 2. Чтобы вывести значение наклона линии регрессии формулу =ЛИНЕЙН(C23:C83;B23:B83) достаточно ввести просто как обычную формулу и нажать ENTER . Конечно, можно использовать и формулу =ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83);1;1) .
Теперь о втором, более сложном режиме функции. Этот режим нужно использовать, если требуется вывести дополнительную статистику (4-й аргумент функции должен быть установлен ИСТИНА). В этом случае функция ЛИНЕЙН() возвращает 10 значений в диапазоне 5х2 ячеек (5 строк и 2 столбца). Как и в более простом режиме, в первой строке возвращаются оценки параметров модели: наклона и сдвига .
Чтобы ввести функцию как формулу массива выполните следующие действия:
- выделите диапазон 5х2 ячеек (2 столбца и 5 строк),
- в Строке формул введите формулу ЛИНЕЙН($C$23:$C$83;$B$23:$B$83;;ИСТИНА)
- чтобы ввести формулу нажмите одновременно комбинацию клавиш CTRL + SHIFT + ENTER
Примечание : Чтобы обойтись без формул массива нужно использовать функцию ИНДЕКС() , которая выведет нужное значение. Например, чтобы вывести коэффициент детерминации R 2 введите формулу = ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83;;ИСТИНА);3;1) . 3 – это номер строки диапазона 5х2, а 1 – это номер столбца. В файле примера на листе Линейный в диапазоне Q 26: R 30 показано как вывести все значения, возвращаемые функцией ЛИНЕЙН() без формул массива .
Итак, установив 4-й аргумент равным ИСТИНА и введя функцию тем или иным способом, функция выведет:
- в строке 1: оценки параметров модели (наклон и сдвиг).
- в строке 2: Стандартные ошибки для наклона и сдвига . Ошибки обозначаются se и seb;
- в строке 3: коэффициент детерминации и стандартную ошибку регрессии . Обозначаются R 2 и SEy;
- в строке 4: значение F-статистики и число степеней свободы . Обозначаются F и df;
- в строке 5: Суммы квадратов SSR, SSE определяющие изменчивость объясненную и необъясненную моделью (см. в статье Простая линейная регрессия разделы про коэффициент детерминации и статью про F-тест ). В справке MS EXCEL SSR, SSE обозначаются как ssreg (Regression Sum of Squares) и ssresid (Residuals Sum of Squares) соответственно.
Примечание : Разобраться в значениях, возвращаемых функцией ЛИНЕЙН() , можно лишь разобравшись в теории линейной регрессии.
В файле примера также приведены формулы, позволяющие сделать расчеты без функции ЛИНЕЙН() – см. диапазон Q 34: R 38 . Альтернативные формулы помогают разобраться в алгоритме расчета вышеуказанных статистических показателей.
Читайте также: