В этой статье описывается синтаксис формулы и использование функции LINEST в 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.
Const Дополнительные. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.
- Если параметр const имеет значение TRUE или опущен, b вычисляется нормально.
- Если параметр const имеет значение FALSE, значение b равно 0, а значения m корректируются в соответствии с y = mx.
Статистика Дополнительные. Логическое значение, которое указывает, требуется ли вернуть дополнительную регрессионную статистику.
- Если stats имеет значение TRUE, функция LINEST возвращает дополнительную статистику регрессии; в результате возвращается массив {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r2,sey; F, df; ssreg,ssresid}.
- Если статистика имеет значение FALSE или опущена, функция LINEST возвращает только коэффициенты 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-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 = AVERAGE(известные x) и y = AVERAGE(known_y).Функции выравнивания линий и кривых LINEST и LOGEST могут вычислить оптимальную прямую или экспоненциальную кривую, которая соответствует вашим данным. Однако вы должны решить, какой из двух результатов лучше всего подходит для ваших данных. Вы можете вычислить TREND(known_y,known_x) для прямой линии или GROWTH(known_y, known_x) для экспоненциальной кривой. Эти функции без аргумента 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. Функция 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 столбцов 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. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.
- 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-статистику, в то время как ФТЕСТ возвращает вероятность.
Примеры
Пример 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 ₽ |
| Формула | Результат |
| =SUM(LINEST(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 в выходных данных для LINEST), что указывает на сильную связь между независимыми переменными и ценой продажи. Вы можете использовать статистику F, чтобы определить, произошли ли эти результаты с таким высоким значением r2 случайно.
Предположим, что на самом деле взаимосвязи между переменными не существует, просто статистический анализ вывел сильную взаимозависимость по взятой равномерной выборке 11 зданий. Величина "Альфа" используется для обозначения вероятности ошибочного вывода о существовании сильная взаимозависимости.
Значения F и df в выходных данных функции LINEST можно использовать для оценки вероятности случайного возникновения более высокого значения F. F можно сравнить с критическими значениями в опубликованных таблицах распределения F или функцию ПИИСТ в Excel можно использовать для вычисления вероятности случайного возникновения большего значения F. Соответствующее распределение F имеет степени свободы v1 и v2. Если n — количество точек данных и const = TRUE или опущено, то v1 = n – df – 1 и v2 = df. (Если const = FALSE, то v1 = n – df и v2 = df.) Функция ПИИСТ с синтаксисом ПИИСТ(F,v1,v2) вернет вероятность случайного возникновения более высокого значения F. В этом примере df = 6 (ячейка B18) и F = 459,753674 (ячейка A18).
Если значение Альфа равно 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.) Вы можете использовать функцию ПИИСТ в Excel, чтобы получить вероятность случайного возникновения значения F. Например, ПИИСТ(459,753674, 4, 6) = 1,37E-7, крайне малая вероятность. Вы можете сделать вывод, найдя критический уровень F в таблице или с помощью функции ПИИСТ , что уравнение регрессии полезно для прогнозирования оценочной стоимости офисных зданий в этой области. Помните, что очень важно использовать правильные значения версий 1 и 2, которые были вычислены в предыдущем абзаце.
Пример 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. Следовательно, все переменные, использованные в уравнении регрессии, полезны для предсказания оценочной стоимости здания под офис в данном районе.