ЛИНЕЙН
Функция ЛИНЕЙН возвращает массив статистических данных для лучшей эмпирической прямой по полученным данным, построенной по методу наименьших квадратов.
ЛИНЕЙН(известные значения y; известные значения x; вычисл. ненул. y; доп. статистика)
известные значения y: набор известных значений y. Набор известных значений y должен содержать числовые значения или значения даты/времени. Если имеется только один набор известных значений x, набор известные значения y может быть любого размера. Если наборов известных значений x несколько, набор известные значения y должен представлять собой либо один столбец, либо одну строку со значениями.
известные значения x: необязательный для указания набор, содержащий известные значения x. Набор известных значений x должен содержать числовые значения или значения даты/времени. Если соответствующий аргумент опущен, предполагается, что он имеет тот же размер, что и набор известные значения y, и начинается со значения 1, например: 1; 2; 3, если набор известные значения y состоит из трех значений. Если есть только одно множество известных значений x, то множества известные значения x и известные значения y должны совпадать по размеру. Если множеств известных значений x несколько, то каждая строка (столбец) набора известные значения x считается отдельным множеством, и ее (его) размер должен совпадать с размером строки (столбца) известные значения y.
вычисл. ненул. y: необязательное значение перечислимого типа, указывающее способ вычисления отрезка оси Y (константы b).
нормальный (1, ИСТИНА или пропущено): значение отрезка оси Y (константа b) должно вычисляться стандартно.
принуд. значение 0 (0, ЛОЖЬ): значение отрезка оси Y (константа b) должно быть равно 0.
доп. статистика: необязательное значение перечислимого типа, указывающее, возвращается ли дополнительная статистическая информация.
нет дополнительной статистики (0, ЛОЖЬ или пропущено): не возвращать дополнительную статистику регрессии в массиве результата.
дополнительная статистика (1, ИСТИНА): возвращать дополнительную статистику регрессии в массиве результата.
Примечания
Значения, возвращаемые функцией, оформляются в виде массива. Один из методов считывания элементов массива — функция ИНДЕКС. Функцию ЛИНЕЙН можно обернуть в функцию ИНДЕКС: =ИНДЕКС(ЛИНЕЙН(известные значения y; известные значения х; точка пересечения с осью Y; доп. статистика); y; x), где y и x указывают столбец и строку требуемой ячейки.
Если дополнительные статистические данные не возвращаются (для аргумента доп. статистика задано значение ЛОЖЬ), возвращаемый массив содержит только одну строку. Количество столбцов на единицу больше количества множеств известные значения х. Массив содержит значения углового коэффициента (по одному для каждой строки или столбца значений x) в обратном порядке (первым стоит значение, относящееся к последней строке или столбцу значений x), а в конце — значение константы b (размер отрезка).
Если дополнительные статистические данные возвращаются (для аргумента доп. статистика задано значение ИСТИНА), массив содержит пять строк. См. дополнительную информацию об этом массиве сразу же после примеров.
Примеры |
|---|
В следующей таблице набор известные значения y занимает ячейки A2:A6, а набор известные значения x — ячейки B2:B6. |
A | B | |
|---|---|---|
1 | Y | X |
2 | 0 | -1 |
3 | 8 | 10 |
4 | 9 | 12 |
5 | 4 | 5 |
6 | 1 | 3 |
=ИНДЕКС(ЛИНЕЙН(A2:A6; B2:B6; 1; 0); 1) возвращает приблизительно 0,752707581227437, при данном нормальном (1) значении для вычисл. ненул. y. Это наилучшая подходящая наклонная линия, так как мы указали, что требуется первое значение массива, возвращенное функцией ИНДЕКС, и указали только одно множество значений известные значения x. =ИНДЕКС(ЛИНЕЙН(A2:A6; B2:B6; 1; 0); 2) возвращает приблизительно 0,0342960288808646 — отрезок b, отсекаемый лучшей эмпирической прямой на оси Y. Отсекаемый отрезок возвращается потому, что указано второе значение из массива, возвращенного функцией ИНДЕКС: отсекаемый отрезок является вторым значением, поскольку задано только одно множество известные значения x. |
Содержимое массива дополнительных статистических данных
Массив, возвращаемый функцией ЛИНЕЙН, может содержать дополнительную статистическую информацию. В дальнейшем обсуждении предположим, что имеется пять множеств известных значений x и одно множество известных значений y. Предположим, что известные значения x находятся в пяти строках или в пяти столбцах таблицы. На основе этих данных массив, возвращенный функцией ЛИНЕЙН, должен содержать следующие значения.
1 | 2 | 3 | 4 | 5 | 6 | |
|---|---|---|---|---|---|---|
1 | S5 | S4 | S3 | S2 | S1 | b |
2 | SE5 | SE4 | SE3 | SE2 | SE1 | SEb |
3 | C | SEy | ||||
4 | Ж | DF | ||||
5 | R1 | R2 |
В строке 1 столбца 1 содержится S5 (наклон для пятого множества значений известные значения x) и т. д. до столбца 5, в котором содержится S1 (наклон для первого множества значений известные значения x). Обратите внимание, что коэффициенты угла наклона для каждого из множеств значений известные значения x возвращаются в обратном порядке.
В последней ячейке строки 1 содержится «b» — отрезок по оси Y для известных значений x. В нашем примере это строка 1 столбца 6.
В строке 2 столбца 1 содержится SE5 (стандартная ошибка для множителя, связанного с пятым множеством значений известные значения x) и т. д. до столбца 5, в котором содержится SE1 (стандартная ошибка для множителя, связанного с первым множеством значений известные значения x). Значения возвращаются в обратном порядке, то есть, если есть пять множеств известных значений x, то первым будет стоять значение, относящееся к пятому множеству. Значения углового коэффициента возвращаются аналогичным образом.
В последней ячейке строки 2 содержится SEb — стандартная ошибка, связанная со значением отрезка оси y (b). В нашем примере это строка 2 столбца 6.
В строке 3 столбца 1 содержится C — множитель смешанной корреляции. Эта статистика связана со сравнением оценочных и фактических значений y. Если этот коэффициент равен 1, то разницы между оценочными и фактическими значениями y нет. Такая зависимость называется линейной корреляцией. Если множитель смешанной корреляции равен 0, то корреляция отсутствует, то есть данное уравнение регрессии не может использоваться для прогнозирования значений y.
В строке 3 столбца 2 содержится SEy — стандартная ошибка, связанная с оценочным значением y.
В строке 4 столбца 1 содержится F — наблюдаемое значение F. Наблюдаемое значение F может использоваться для определения случайности или неслучайности наблюдаемой связи между зависимой и независимой переменной.
В строке 4 столбца 2 содержится DF — число степеней свободы. Эта статистика используется для определения уровня достоверности.
В строке 5 столбца 1 содержится R1 — регрессионная сумма квадратов.
В строке 5 столбца 2 содержится R2 — остаточная сумма квадратов.
При работе с массивом дополнительных статистических данных следует учитывать приведенную ниже информацию.
Совершенно не важно, как расположены известные значения x и y, в столбцах или в строках. В любом случае данные в возвращаемом массиве будут упорядочены по строкам (см. таблицу).
В качестве примера мы взяли пять множеств известных значений x. Если бы множеств было больше, число столбцов в возвращаемом массиве изменилось бы соответствующим образом (оно всегда на единицу больше количества множеств известных значений x), а количество строк осталось бы прежним.
Если аргумент доп статистики в функции ЛИНЕЙН не задан, возвращаемый массив содержит только одну первую строку.