Функция LINE вычисляет статистику для ряда, используя метод наименьших квадратов для расчета прямой линии, которая лучше всего аппроксимирует имеющиеся данные, а затем возвращает массив, описывающий полученную прямую линию.
Описание функции ЛИНЕЙН
Функция LINEINE рассчитывает статистику для ряда, используя метод наименьших квадратов для вычисления прямой линии, которая наилучшим образом аппроксимирует имеющиеся данные, а затем возвращает массив, описывающий полученную прямую линию.
Функция LINE также может быть объединена с другими функциями для расчета других типов моделей, линейных по неизвестным параметрам, включая полиномиальные, логарифмические, экспоненциальные и степенной ряд. Поскольку она возвращает массив значений, функция должна быть указана как формула массива.
Уравнение прямой линии выглядит следующим образом:
Синтаксис
=LINE(известные_значения_y; [известные_значения_x]; [const]; [stat])
Аргументы
известные_значения_y; [известные_значения_x; [const]; [stat] Обязательный аргумент. Набор значений y, которые уже известны для соотношения y = mx + b
- Если массив известных_значений_y имеет один столбец, то каждый столбец массива известных_значений_x интерпретируется как отдельная переменная.
- Если известные_значения_y имеют одну строку, то каждая строка известных_значений_x интерпретируется как отдельная переменная.
Необязательный аргумент. Набор значений x, которые уже известны для отношения y = mx + b
- Массив known_values_x может содержать один или несколько наборов переменных. Если используется только одна переменная, массивы известных_значений_y и известных_значений_x могут быть любой формы - лишь бы они имели одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (т.е. интервалом высотой в одну строку или шириной в один столбец).
- Если известные_значения_x опущены, то предполагается, что это массив {1;2;3;...} того же размера, что и известные_значения_y.
Необязательный аргумент. Булево значение, указывающее, должна ли константа b быть равна 0.
- Если аргумент const равен TRUE или опущен, то константа b вычисляется обычным способом.
- Если аргумент constant равен FALSE, b принимается равным 0, а m корректируется так, чтобы y = mx было выполнено.
Необязательный аргумент. Булево значение, указывающее, следует ли возвращать дополнительную статистику регрессии.
- Если аргумент statistic равен TRUE, функция LINE вернет дополнительную статистику регрессии. Возвращаемый массив будет иметь следующий вид: {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid}.
- Если аргумент statistics равен FALSE или опущен, функция LINE возвращает только коэффициенты m и константу b.
Дополнительная статистика регрессии:
| Значение | Описание |
|---|---|
| se1,se2,...,sen | Значения стандартной ошибки для коэффициентов m1,m2,...,mn. |
| себ | Значение стандартной ошибки для константы b (seb = #H/A, если аргумент const равен FALSE). |
| r2 | Коэффициент детерминации. Сравниваются фактические значения y и значения, полученные из уравнения симплекса; по результатам сравнения рассчитывается коэффициент детерминации, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различий между фактическими и расчетными значениями y. В противном случае, когда коэффициент детерминации равен 0, нет смысла использовать уравнение регрессии для прогнозирования значений y. |
| сей | Стандартная ошибка для оценки y. |
| F | F-статистика или F-наблюдаемое значение. F-статистика используется для определения того, является ли наблюдаемая связь между зависимой и независимой переменными случайной. |
| df | Степени свободы. Степени свободы полезны для нахождения критических значений F в статистической таблице. Чтобы определить уровень надежности модели, сравните значения в таблице с F-статистикой, возвращаемой функцией LINE. |
| ssreg | Сумма квадратов регрессии. |
| ssresid | Остаточная сумма квадратов. Более подробную информацию о вычислении ssreg и ssresid см. в разделе "Примечания" в конце этой главы. |
На рисунке ниже показан порядок возврата дополнительной статистики регрессии:
Замечания
- Каждая прямая линия может быть описана ее наклоном и пересечением с осью y:
Уклон (м):
Чтобы определить наклон прямой, обычно обозначаемый m, возьмите две точки на прямой (x1,y1) и (x2,y2); наклон будет равен (y2 - y1)/(x2 - x1).Пересечение Y (b):
Y-пересечение прямой линии, обычно обозначаемое b, - это значение y для точки, где прямая линия пересекает ось y.Уравнение этой прямой имеет вид
y = mx + b Если известны значения m и b, каждую точку на прямой можно вычислить, подставив в уравнение значения y или x. Также можно использовать функцию TENDENCY. - Если имеется только одна независимая переменная x, наклон и y-интерцепт могут быть получены непосредственно с помощью следующих формул:
Склон:
= INDEX(LINE(известные_значения_y;известные_значения_x);1)Y-интерцепт:
=INDEX(LINEIN(известные_значения_y;известные_значения_x);2) - Точность аппроксимации прямой линии, рассчитанной функцией LINE, зависит от дисперсии данных. Чем ближе данные к прямой линии, тем точнее модель, используемая функцией. Функция LINE использует метод наименьших квадратов для определения наилучшего приближения к данным. Когда имеется только одна независимая переменная x, значения m и b вычисляются по следующим формулам:
где: $${overline{x}, $$overline{y}$$ - средние значения по выборке, напр:
x = Известные_значения_x и
y = Известные_значения_y - Аппроксимирующие функции LINEINE и LGRFPRIBLE позволяют рассчитать прямую или экспоненциальную кривую, которая лучше всего описывает данные. Однако они не дают ответа на вопрос, какой из этих двух результатов более подходит для конкретной задачи. Также можно вычислить функцию
TENDENCE(известные_значения_y; известные_значения_x) для прямой линии или функции
REST(known_values_y; known_values_x) для экспоненциальной кривой. Эти функции, если не указан аргумент new_values_x, возвращают массив вычисленных значений y для фактических значений x по прямой или кривой. Затем рассчитанные значения можно сравнить с фактическими. Для визуального сравнения можно также построить графики. - При проведении регрессионного анализа Microsoft Excel вычисляет для каждой точки квадрат разницы между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов называется остаточной суммой квадратов (ssresid).
Затем Microsoft Excel вычисляет общую сумму квадратов (sstotal). Если const = TRUE, или если для этого аргумента не указано значение, ssstotal будет суммой квадратов разностей фактических значений y и среднего значения y. Если const = FALSE, то ssstotal будет суммой квадратов фактических значений y (без вычитания среднего значения y из частичного значения y). Сумма квадратов регрессии может быть рассчитана следующим образом: ssreg = sstotal - ssresid. Чем меньше сумма квадратов остатков, тем выше значение коэффициента детерминации r2, который показывает, насколько хорошо уравнение, полученное в результате регрессионного анализа, объясняет связь между переменными. Коэффициент r2 равен отношению ssreg/sstotal. - В некоторых случаях один или несколько столбцов X (пусть значения Y и X находятся в столбцах) не повлияют на результаты при наличии других столбцов X. Другими словами, удаление одного или нескольких столбцов X может привести к вычислению значений Y с той же точностью. В этом случае избыточные столбцы X будут исключены из регрессионной модели. Это явление называется коллинеарностью, поскольку избыточные столбцы X могут быть представлены как сумма нескольких нередуцированных столбцов.
Функция LINEINE проверяет наличие коллинеарности и удаляет все избыточные столбцы X из регрессионной модели, если обнаруживает их. Удаленные столбцы X можно определить в выводе LINEINE по коэффициенту, равному 0, и значению se, равному 0. Удаление одного или нескольких столбцов как избыточных изменяет значение df, поскольку оно зависит от количества столбцов X, фактически используемых для предсказания.
Когда df изменяется из-за удаления лишних столбцов, значения sey и F также изменяются. Использование коллинеарности часто не рекомендуется. Однако его следует использовать, если некоторые столбцы X содержат 0 или 1 в качестве индикатора принадлежности участника эксперимента к отдельной группе. Если const = TRUE или значение не указано, функция LINE вставляет дополнительный столбец X для имитации пересечения. Если есть столбец со значениями 1 для мужчин и 0 для женщин, и есть столбец со значениями 1 для женщин и 0 для мужчин, последний столбец удаляется, поскольку его значения могут быть получены из столбца "индикатор пола".
- Расчет значения df для случаев, когда столбцы X удаляются из модели из-за коллинеарности, производится следующим образом: если имеется k столбцов известных_значений_x и const = TRUE или не указано, то df = n - k - 1. Если const = FALSE, то df = n - k. В обоих случаях удаление столбцов X из-за коллинеарности увеличивает значение df на 1.
- Формулы, возвращающие массивы, должны быть введены как формулы массивов.
- При вводе константы массива (например, known_values_x в качестве аргумента) используйте точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Сепараторы могут быть разными, в зависимости от региональных параметров.
Обратите внимание, что значения y, предсказанные уравнением регрессии, могут быть неверными, если они лежат вне диапазона значений y, которые использовались для определения уравнения.
Основной алгоритм, используемый в функции LINE, отличается от основного алгоритма функций TILED и RETURN. Разница между алгоритмами может привести к различным результатам при использовании неопределенных и коллинеарных данных. Например, если точки данных аргумента известные_значения_y равны 0, а точки данных аргумента известные_значения_x равны 1, то:
- Функция LINEINE возвращает значение, равное 0. Алгоритм функции LINEINE используется для возврата соответствующих значений для коллинеарных данных, и в этом случае может быть найден хотя бы один ответ.
- Функции NEXT и LINE возвращают ошибку #DEL/0! Алгоритм функций NEXT и RETURN используется для поиска только одного ответа, но в данном случае ответов может быть несколько.
- В дополнение к расчету статистики для других типов регрессии с помощью функции LGRFPRIBLE, функцию LINEINE можно использовать для расчета интервалов для некоторых других типов регрессии, введя функции переменных x и y для LINE в качестве строк. Например, следующая формула:
=LINEINE(values_y, values_x^Column($A:$C))работает, если есть один столбец значений Y и один столбец значений X для вычисления кубической аппроксимации (полинома степени 3) следующего вида:
y = m1*x + m2*x^2 + m3*x^3 + bФормулу можно модифицировать для расчета других типов регрессии, но в некоторых случаях необходимо скорректировать выходные значения и другие статистические данные.
- Значение F-теста, возвращаемое функцией LINEINE, отличается от значения, возвращаемого функцией FTEST. Функция LINE возвращает F-статистику, а FTEST - вероятность.
