Линейн excel

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

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

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

Источник: excel2.ru

Глава 22. Функция массива ЛИНЕЙН

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

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

Рис. 22.1. Четыре аргумента функции ЛИНЕЙН

Скачать заметку в формате Word или pdf, примеры в формате Excel

Линейная регрессия

На рис. 22.2 показан набор данных (он уже анализировался в главе 9, когда мы обсуждали функции НАКЛОН, ОТРЕЗОК, ПРЕДСКАЗ и ТЕНДЕНЦИЯ). Поскольку ЛИНЕЙН является функцией массива и вы хотите, чтобы она вернула два значения, выполните следующие действия:

  1. Выделите диапазон D2:Е2. Функция ЛИНЕЙН возвращает массив из двух значений, расположенных по горизонтали, но не по вертикали.
  2. Введите известные значения y. Это – баллы, которые студенты заработали на последнем тестировании.
  3. Введите известные значения х. Это количество часов, которые студенты потратили на подготовку к тестам.
  4. Опустите аргумент [конст].
  5. Опустите аргумент [статистика].
  6. Введите формулу с помощью Ctrl+Shift+Enter.

Рис. 22.2. Функция ЛИНЕЙН возвращает наклон и отрезок, если массив расположен в горизонтальном диапазоне

Рис. 22.3. Функция массива ЛИНЕЙН заменяет две отдельные функции – НАКЛОН и ОТРЕЗОК

Если вам всё же нужно вывести результаты функции ЛИНЕЙН в вертикальный массив, воспользуйтесь ухищрением (рис. 22.4).

Рис. 22.4. Формулы для вывода результатов в вертикальный массив

Если вы хотите отобразить не только наклон и отрезок, но и дополнительные статистики, выделите диапазон на один столбец больше, чем столбцов с переменными х, и высотой 5 строк. Как показано на рис. 22.5, поскольку у вас лишь одна переменная х, выделите диапазон Е2:F6 (2 столбца по 5 строк). Третьему и четвертому аргументам присвойте значения ИСТИНА: вы хотите, чтобы b считалось обычным образом, и хотите вывести дополнительные статистики. После ввода формулы нажатием Ctrl+Shift+Enter, результат должен соответствовать рис. 22.6 (подробнее о десяти статистиках см. Простая линейная регрессия).

Рис. 22.5. Когда требуется дополнительная статистика для одной переменной, выделите диапазон 2*5; функция ЛИНЕЙН вернет 10 значений; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Рис. 22.6. Функция ЛИНЕЙН возвращает 10 статистик

В главе 8 было показано, как с помощью формулы преобразовать таблицу в столбец. На рис. 22.7 приведена формула, позволяющая представить результаты работы функции ЛИНЕЙН (которые она возвращает в диапазон 2*5) в вертикальном столбце.

Следующие элементы являются аргументами функции ИНДЕКС:

  • аргумент массив: функция ЛИНЕЙН($B$2:$B$12;$A$2:$A$12;ИСТИНА;ИСТИНА) возвращает диапазон из пяти строк и двух столбцов.
  • аргумент номер_строки: ОСТАТ(ЧСТРОК(E$1:E1)-1;5)+1 возвращает следующие значения 1,2,3,4,5,1,2,3,4,5 при копировании формулы вдоль столбца от Е1 до Е10.
  • аргумент номер_столбца: ЦЕЛОЕ((ЧСТРОК(E$1:E1)-1)/5)+1 возвращает 1,1,1,1,1,2,2,2,2,2 при копировании формулы вдоль столбца от Е1 до Е10.

Рис. 22.7. Преобразование диапазона вывода формулы ЛИНЕЙН из 2*5 в вертикальный

Читайте также:  Как ворд вставить в эксель

Формула в Е1 не требует ввода с помощью Ctrl+Shift+Enter.

Множественная регрессия

В случае множественной регресии, когда значения y зависят от двух переменных х1 и х2, функция ЛИНЕЙН возвращает 12 статистик (подробнее см. Введение в множественную регрессию и Построение модели множественной регрессии). На рис. 22.8 используются следующие обозначения:

  • y = зависимая переменная
  • x1 = независимая переменная 1 = баллы за домашнее задание
  • x2 = независимая переменная 2 = часов изучал последний столбец тест = гр.

Чтобы выполнить множественную регрессию:

  • Выделите диапазон В3:D7 (число столобцов = число переменных +1; число строк всегда равно 5).
  • Наберите формулу <=ЛИНЕЙН(D13:D23;B13:C23;ИСТИНА;ИСТИНА)>. Для аргумента известные_значения_х, выделите оба столбца значений x из диапазона В13:С23.
  • Введите функцию с помощью клавиш Ctrl+Shift+Enter.
  • Обратите внимание, что несмотря на то, что значения х1 указаны в диапазоне В13:С23 до значений х2, наклон сначала указан для х2.

Рис. 22.8. Для двух переменных x1 и х2 функция ЛИНЕЙН выполняет множественную регрессию

Если вас раздражают знаяения ошибки #Н/Д дополните вашу формулу функцией ЕСЛИОШИБКА (рис. 22.9).

Рис. 22.9. Вы можете избавиться от ошибок #Н/Д «обернув» ЛИНЕЙН функцией ЕСЛИОШИБКА

Пример с тремя переменными не должен вызвать затруднений (рис. 22.10).

Рис. 22.10. Множественная регрессия для трех независимых переменных

2 комментария для “Глава 22. Функция массива ЛИНЕЙН”

Добрый день!
У меня следующая ситуация: значения двух независимых переменных x1 и x2 содержаться на разных листах. Перенести их на один лист не получается, потому что наборов данных несколько сотен и делать для каждого набора отдельную вкладку — не вариант. Можно ли как-то обойти требование что x1 и x2 должны содержаться в едином диапазоне?

Источник: baguzin.ru

Примеры как пользоваться функцией ЛИНЕЙН в Excel

Задача отыскания функциональной зависимости очень важна, поэтому для ее решения в MS Excel введен набор функций, основанных на методе наименьших квадратов. В качестве результата выдаются не только коэффициенты функции, приближающей данные, но и статистические характеристики полученных результатов.

Смысл выходной статистической информации функции ЛИНЕЙН

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

Общий синтаксис вызова функции ЛИНЕЙН имеет следующий вид:

Для работы с функцией необходимо заполнить как минимум 1 обязательный и при необходимости 3 необязательных аргумента:

  1. Известные_значения_y − это множество значений y , которые уже известны для соотношения y=mx+b.
  2. Известные_значения_x − это множество известных значений x . Если этот аргумент опущен, то предполагается, что это массив <1; 2; 3; . >такого же размера, как и известные_значения_y.
  3. Конст − это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если в функции ЛИНЕЙН аргумент константа имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx.
  4. Статистика − это логическое значение, которое указывает, требуется ли выдать дополнительную статистику по регрессии.



Примеры использования функции ЛИНЕЙН в Excel

Для решения первой задачи – о соотношении часов подготовки студентов к тесту и результатов теста, как х и у соответственно, – необходимо применить следующий порядок действий (в связи с тем, что ЛИНЕЙН является функцией, которая возвращает массив):

  1. Выделите диапазон D2:Е2, так как функция ЛИНЕЙН возвращает массив из двух значений, расположенных по горизонтали, но не по вертикали.
  2. Введите известные значения y – баллы, которые студенты заработали на последнем тестировании (диапазон ячеек В2:В12).
  3. Затем введите известные значения х – количество часов, которые студенты потратили на подготовку к тестам (диапазон А2:А12).
  4. Опустите аргумент [конст].
  5. Опустите аргумент [статистика].
  6. Введите формулу с помощью Ctrl+Shift+Enter.

Результатом применения функции становится:

Теперь, на примере решения второй задачи, разберем необходимость в отображении не только наклона и отрезка, но и дополнительной статистики. Для примера, на диапазоне А1:В6 выстроим таблицу с соотношением у и х соответствующих сумме заработка студентом денежных средств за период в 5 месяцев. Так как мы имеем лишь одну переменную х, то необходимо выделить диапазон состоящий из двух столбцов и пяти строк. Важно отметить, что в том случае, если переменных х будет больше, то количество столбцов может изменяться соответственно их количеству, однако строк будет всегда 5.

Применительно к решаемой нами задаче, выделим диапазон Е2:F6, затем введем формулу аналогично предыдущей задаче, но в данном случае третьему и четвертому аргументу присвоим значение 1 соответствующее ИСТИНЕ. Для вывода параметров статистики функции ЛИНЕЙН необходимо нажат Ctrl+Shift+Enter, результат должен соответствовать следующему рисунку, на котором представлено обозначение дополнительных статистик:

Вернемся к примеру № 1, касающемуся зависимости между часами подготовки студентов к тесту и баллов за тест. Добавим к условию задачи данные о баллах за домашнее задание – представляющие дополнительную переменную х, что свидетельствует о необходимости применения множественной регрессии.

В случае множественной регрессии, когда значения « y » зависят от двух переменных « х », функция ЛИНЕЙН возвращает 12 статистик. На рисунке с модифицированной таблицей от 1 примера, представленном ниже используются следующие обозначения:

  • y = зависимая переменная;
  • x1 = независимая переменная 1 = баллы за домашнее задание;
  • x2 = независимая переменная 2 = часы подготовки к тесту.

Чтобы выполнить множественную регрессию:

  1. Выделите диапазон В3:D7 (число столбцов = число переменных +1; число строк всегда равно 5).
  2. Наберите формулу =ЛИНЕЙН(D14:D24;B14:C24;1;1). Для аргумента известные_значения_х, выделите оба столбца значений x из диапазона В14:С24.
  3. Введите функцию с помощью клавиш Ctrl+Shift+Enter.
  4. Обратите внимание, что несмотря на то, что значения х1 указаны в диапазоне В14:С24 до значений х2, наклон сначала указан для х2.

Диапазон D5:D7 содержит ошибку #Н/Д – значащую, что формула не может обнаружить значения для данных ячеек. Визуально наличие ошибки отвлекает от сути решения, поэтому далее предложим вариант избавления от нее. Так, если дополнить формулу содержащую функцию ЛИНЕЙН функцией ЕСЛИОШИБКА, то можно значительно улучшить вид таблицы, результат которой представлен ниже:

Распределение статистик в таблице их значение представлено на следующем рисунке:

В результате мы получили всю необходимую выходную статистическую информацию, которая нас интересует.

Источник: exceltable.com

Функция Линейн

Функция Линейн, встроенная в Excel, позволяет строить различного вида аппроксимирующие формулы для одного и более входных параметров. В этом разделе вы найдете множество разного рода примеров аппроксимации, а в этой статье я привожу официальный текст по этой функции без корректировки. Не обязательно сразу разбираться во всех деталях использования этой функции. Для начал достаточно узнать как она записывается, где находятся значения коэффициентов аппроксимирующего уравнения и где находится значение погрешности аппроксимации. Аналогичную приведенной ниже информацию вы также можете найти непосредственно в Exel.

Расчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.

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

Массив x может содержать одно или несколько множеств переменных.

Конст – это логическое значение, которое указывает, требуется ли,

чтобы свободный член a был равен 0.

Если аргумент конст имеет значение ИСТИНА, 1 или опущено, то a вычисляется обычным образом. Если аргумент конст имеет значение ЛОЖЬ или 0, то a полагается равным 0.

Статистика – это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент статистика имеет значение ИСТИНА или 1, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ, 0 или опущена, то функция ЛИНЕЙН возвращает только коэффициенты и свободный член.

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

se1,se2. sen – стандартные значения ошибок для коэффициентов b1,b2. bn.

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

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

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

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

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

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

ssresid – остаточная сумма квадратов.

Выборочную информацию из функции можно получить через функцию

Y-пересечение (свободный член):

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

Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов. Затем Microsoft Excel подсчитывает сумму квадратов разностей между фактическими значениями y и средним значением y, которая называется общей суммой квадратов (регрессионая сумма квадратов + остаточная сумма квадратов). Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности r2, который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными.

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

Пример 1 Наклон и Y-пересечение

ЛИНЕЙН(<1;9;5;7>;<0;4;2;3>) равняется <2;1>, наклон = 2 и y-пересечение = 1.

Использование статистик F и R2

Можно использовать F-статистику, чтобы определить, является ли результат с высоким значение r2 случайным. Если F-наблюдаемое больше, чем F-критическое, то взаимосвязь между переменными имеется. F-критическое можно получить из таблицы F-критических значений в любом справочнике по математической статистике. Для того, чтобы найти это значение, используя односторонний тест, положим величину Альфа (величина Альфа используется для обозначения вероятности ошибочного вывода о том, что имеется сильная взаимозависимость) равной 0,05, а для числа степеней свободы (обозначаемых обычно v1 и v2), положим v1 = k = 4 и v2 = n – (k + 1) = 11 – (4 + 1) = 6, где k – это число переменных, а n – число точек данных. Из таблицы справочника F-критическое равно 4,53. Наблюдаемое F-значение равно 459,753674 (это значение получено в опущенном нами примере), что заметно больше чем F-критическое значение 4,53. Следовательно, полученное регрессионное уравнение полезно для предсказания искомого результата.

Источник: www.algoritmist.ru

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

Параметры линейной регрессии можно определить с помощью встроенной статистической функции ЛИНЕЙН. Порядок вычисления следующий:

– Ввод исходных данных;

– Выделите область пустых ячеек 5х2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1х2 – для получения только оценок коэффициентов регрессии;

– Активизируйте Мастер функций – щелкните fx на панели инструментов или в главном меню выберите Вставка – Функция;

– В окне Категория выберите Статистические, в окне Функция – ЛИНЕЙН. Щелкните ОК.

– Заполните аргументы функции:

q Известные значения у – диапазон, содержащий данные результативного признака;

q Известные значения_х – диапазон, содержащий данные факторов независимого признака;

q Константа – логическое значение, которое указывает на наличие или отсутствие свободного члена в уравнении: если Константа = 1, то свободный член рассчитывается обычным способом, если Константа = 0, то свободный член равен 0;

q Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу ( = 1) или нет (=0);

q Нажмите комбинацию клавиш CTRL – SHIFT – ENTER. Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей таблице:

Коэффициент b Коэффициент a
Среднеквадратическое отклонение b Среднеквадратическое отклонение a
Индекс детерминации R 2 Среднеквадратическое отклонение остатков
F – статистика Число степеней свободы остатков
Регрессионная сумма квадратов S(Y^ – Y^средн.) 2 Сумма квадратов остатков S(Y – Y^) 2
1,7818 -4,2727
0,2451 3,7578
0,8544 2,5710
52,833
349,23 59,490

Если случайно щёлкнули ОК, нажмите на клавишу F2, а затем – на комбинацию клавиш CTRL – SHIFT – ENTER.

Для вычисления параметров показательной функции Y = ab x в Excel применяется встроенная статистическая функция ЛГФПРИБЛ. Порядок вычислений аналогичен применению функции ЛИНЕЙН.

Как видите, полученные коэффициенты a, b и индекс детерминации R 2 совпадают с результатами их оценки с помощью диаграммы. Кроме того, получены погрешности коэффициентов a, b, стандартное отклонение Y, число степеней свободы остатков (n-2 = 9), сумма квадратов остатков, регрессионная сумма квадратов = S(Y^ – Y^средн.) 2 и статистика Фишера.

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Сдача сессии и защита диплома – страшная бессонница, которая потом кажется страшным сном. 9260 – | 7451 – или читать все.

Источник: studopedia.ru

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

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

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

  • Если аргумент конст имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.
  • Если аргумент конст имеет значение ЛОЖЬ , то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx .
  • Если аргумент статистика имеет значение ИСТИНА , функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Возвращаемый массив будет иметь следующий вид: .
  • Если аргумент статистика имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

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

Величина Описание
se1,se2,…,sen Стандартные значения ошибок для коэффициентов m1,m2,…,mn.
seb Стандартное значение ошибки для постоянной b (seb = #Н/Д, если аргумент конст имеет значение ЛОЖЬ).
r2 Коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т. е. различий между фактическим и оценочным значениями y нет. В противоположном случае, если коэффициент детерминированности равен 0, использовать уравнение регрессии для предсказания значений y не имеет смысла.
sey Стандартная ошибка для оценки y.
F F-статистика или F-наблюдаемое значение. F-статистика используется для определения того, является ли случайной наблюдаемая взаимосвязь между зависимой и независимой переменными.
df Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН.
ssreg Регрессионная сумма квадратов.
ssresid Остаточная сумма квадратов. Дополнительные сведения о расчете величин ssreg и ssresid см. в подразделе «Замечания» в конце данного раздела.

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

  • Любую прямую можно описать ее наклоном и пересечением с осью y:
    Наклон (m):
    чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x1,y1) и (x2,y2); наклон будет равен (y2 — y1)/(x2 — x1).

Y-пересечение (b):
Y-пересечением прямой, обычно обозначаемым через b, является значение y для точки, в которой прямая пересекает ось y.

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

Y-пересечение:

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

    где: — выборочные средние значения, например:
    а
  • Функции аппроксимации ЛИНЕЙН и ЛГРФПРИБЛ позволяют вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую данные. Однако они не дают ответа на вопрос, какой из двух результатов больше подходит для решения поставленной задачи. Можно также вычислить функцию
    для прямой или функцию
    для экспоненциальной кривой. Эти функции, если не задавать аргумент новые_значения_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 вследствие удаления избыточных столбцов значения sey и F также изменяются. Часто использовать коллинеарность не рекомендуется. Однако ее следует применять, если некоторые столбцы X содержат 0 или 1 в качестве индикатора, указывающего, входит ли предмет эксперимента в отдельную группу. Если конст = ИСТИНА или значение этого аргумента не указано, функция ЛИНЕЙН вставляет дополнительный столбец X для моделирования точки пересечения.Если имеется столбец со значениями 1 для указания мужчин и 0 — для женщин, а также имеется столбец со значениями 1 для указания женщин и 0 — для мужчин, то последний столбец удаляется, поскольку его значения можно получить из столбца с «индикатором пола».

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

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

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

    • Функция ЛИНЕЙН возвращает значение, равное 0. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.
    • Функции НАКЛОН и ОТРЕЗОК возвращают ошибку #ДЕЛ/0!. Алгоритм функций НАКЛОН и ОТРЕЗОК используется для поиска только одного ответа, а в данном случае их может быть несколько.
  • Помимо вычисления статистики для других типов регрессии с помощью функции ЛГРФПРИБЛ, для вычисления диапазонов некоторых других типов регрессий можно использовать функцию ЛИНЕЙН, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:

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

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

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