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

В этой статье описаны синтаксис формулы и использование функции ЛИНЕЙН в 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]; [конст]; [статистика])

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

Синтаксис

  • Известные_значения_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.

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

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

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

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

Величина

Описание

se1,se2,...,sen

Стандартные значения ошибок для коэффициентов m1,m2,...,mn.

seb

Стандартное значение ошибки для постоянной b (seb = #Н/Д, если аргумент конст имеет значение ЛОЖЬ).

r2

Коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т. е. различий между фактическим и оценочным значениями y нет. В противоположном случае, если коэффициент детерминированности равен 0, использовать уравнение регрессии для предсказания значений y не имеет смысла. Дополнительные сведения о способах вычисления r2, см. в подразделе "Замечания" в конце данного раздела.

sey

Стандартная ошибка для оценки y.

F

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

df

Степени свободы. Степени свободы используются для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Дополнительные сведения о вычислении величины df см. ниже в разделе "Замечания". Далее в примере 4 показано использование величин F и df.

ssreg

Регрессионная сумма квадратов.

ssresid

Остаточная сумма квадратов. Дополнительные сведения о расчете величин 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-пересечение непосредственно, воспользовавшись следующими формулами:

    Наклон:
    =ИНДЕКС(ЛИНЕЙН(известные_значения_y;известные_значения_x);1)

    Y-пересечение:
    =ИНДЕКС(ЛИНЕЙН(известные_значения_y;известные_значения_x);2)

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

    Уравнение

    Уравнение

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

  • Функции аппроксимации ЛИНЕЙН и ЛГРФПРИБЛ позволяют вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую данные. Однако они не дают ответа на вопрос, какой из двух результатов больше подходит для решения поставленной задачи. Можно также вычислить функцию ТЕНДЕНЦИЯ(известные_значения_y; известные_значения_x) для прямой или функцию РОСТ(известные_значения_y; известные_значения_x) для экспоненциальной кривой. Эти функции, если не задавать аргумент новые_значения_x, возвращают массив вычисленных значений y для фактических значений x в соответствии с прямой или кривой. После этого можно сравнить вычисленные значения с фактическими значениями. Можно также построить диаграммы для визуального сравнения.

  • Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений 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 см. ниже в примере 4. При изменении df вследствие удаления избыточных столбцов значения sey и F также изменяются. Часто использовать коллинеарность не рекомендуется. Однако ее следует применять, если некоторые столбцы X содержат 0 или 1 в качестве индикатора, указывающего, входит ли предмет эксперимента в отдельную группу. Если конст = ИСТИНА или значение этого аргумента не указано, функция ЛИНЕЙН вставляет дополнительный столбец X для моделирования точки пересечения. Если имеется столбец со значениями 1 для указания мужчин и 0 — для женщин, а также имеется столбец со значениями 1 для указания женщин и 0 — для мужчин, то последний столбец удаляется, поскольку его значения можно получить из столбца с "индикатором пола", добавленного функцией ЛИНЕЙН.

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

  • Формулы, возвращающие массивы, необходимо вводить как формулы массива.

    Примечание :  В Excel Online невозможно создать формулы массива.

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

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

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

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

    • Функции НАКЛОН и ОТРЕЗОК возвращают ошибку #ДЕЛ/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; A2:A7)*{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

1 732 393 319

Формула (формула массива, указанная в ячейках A14:A18)

=ЛИНЕЙН(E2:E12; A2:D12; ИСТИНА; ИСТИНА)

Пример 4. Использование F-статистики и r2-статистики

В предыдущем примере коэффициент детерминированности r2 равен 0,99675 (см. ячейку A17 в результатах функции ЛИНЕЙН), что указывает на сильную зависимость между независимыми переменными и продажной ценой. Можно использовать F-статистику, чтобы определить, является ли этот результат (с таким высоким значением r2) случайным.

Предположим, что на самом деле взаимосвязи между переменными не существует, просто статистический анализ вывел сильную взаимозависимость по взятой равномерной выборке 11 зданий. Величина "Альфа" используется для обозначения вероятности ошибочного вывода о существовании сильная взаимозависимости.

В выходных данных функции ЛИНЕЙН величины F и df используются для оценки вероятности случайного получения наибольшего значения F. Величину F можно сравнить с критическими значениями в публикуемых таблицах F-распределения, либо для вычисления возможности случайного получения наибольшего значения F можно использовать функцию Microsoft Excel FРАСП. Соответствующее F-распределение имеет степени свободы v1 и v2. Если величина n представляет количество точек данных и аргумент конст имеет значение ИСТИНА или опущен, то v1 = n – df – 1 и v2 = df. (При конст = ЛОЖЬ v1 = n – df и v2 = df). Функция FРАСП — с синтаксисом FРАСП(F,v1,v2) — возвращает вероятность случайного получения наибольшего значения F. В примере 4 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 гипотеза об отсутствии связи между аргументами известные_значения_y и известные_значения_x отвергается, если значение F превышает критический уровень 4,53). Использование функции Microsoft Excel FРАСП дает возможность вычислять вероятность случайного получения больших значений F. Значение вероятности FРАСП(459,753674; 4; 6) = 1,37E-7 чрезвычайно мало. Из этого можно заключить через нахождение критического уровня F в таблице или использование функции Microsoft Excel FРАСП, что уравнением регрессии можно воспользоваться для предсказания оценочной стоимости зданий под офис в данном районе. Следует учесть, что использование правильных значений 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. Следовательно, все переменные, использованные в уравнении регрессии, полезны для предсказания оценочной стоимости здания под офис в данном районе.

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×