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

В этой статье описаны синтаксис формулы и использование функции ЛИНЕЙН в 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 и фактическим значением 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, прогнозируемых вдоль данной линии или кривой на реальных точках данных. Затем вы можете сравнить прогнозируемые значения с фактическими значениями. Вы можете попытаться создать диаграмму для визуального сравнения.

  • Проводя регрессионный анализ, 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 из модели регрессии при их идентификации. Удаленные столбцы X могут быть распознаны в выходных данных ЛИНЕЙН , так как они имеют нулевые коэффициенты в дополнение к значениям 0 SE. Если один или несколько столбцов удалены как избыточные, значение DF будет затронуто, так как DF зависит от количества столбцов X, которые фактически используются для целей прогнозирования. Подробнее о вычислении DF можно найти в разделе Пример 4. Если значение DF изменилось из-за того, что удаляются столбцы с избыточными X, также повлияют значения Сэй и F. Коллинеарность на практике должен быть сравнительно редкой. Тем не менее, если некоторые из столбцов X содержат только значения 0 и 1, в том числе индикаторов того, является ли тема в эксперименте или не входит в состав определенной группы. Если аргумент " Конст " имеет значение истина или опущен, функция ЛИНЕЙН фактически вставляет дополнительный столбец X для всех значений 1, чтобы смоделировать функцию "конст". Если у вас есть столбец с 1 для каждой темы, или 0, если нет, а также есть столбец с 1 для каждой темы, или 0 (если нет), то этот последний столбец является избыточным, так как записи в нем можно получить путем вычитания записи в поле "индикатор" м ". столбец из записи в дополнительном столбце всех значений 1, добавленных функцией ЛИНЕЙН .

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

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

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

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

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

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

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

    • Наклон и конст возвращают #DIV/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РАСП в Excel, чтобы вычислить вероятность увеличения значения f, возникшего в результате шанса. Соответствующее F Distribution имеет степенную свободу v1 и v2. Если n — количество точек данных и const = истина или опущено, то v1 = n – df – 1 и v2 = df. (Если константа = ложь, то v1 = n – df и v2 = df.) Функция FРАСП — с синтаксисом FРАСП(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, то гипотеза о том, что отсутствует связь между известные_значения_y и известные_значения_x , будет отклонена, если F превышает критический уровень, 4,53.) Вы можете использовать функцию FРАСП в Excel, чтобы получить вероятность того, что значение F, которое возникло с высокой степенью вероятности. Например, FРАСП(459,753674, 4, 6) = 1.37 e-7 — очень маленькая вероятность. Для этого можно либо найти критический уровень F в таблице, либо воспользоваться функцией FРАСП , в которой уравнение регрессии используется для прогнозирования оцененной стоимости зданий Office в этой области. Помните о том, что необходимо использовать правильные значения 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

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

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

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

×