Функция ЛИНЕЙН возвращает параметры прямой, которая проходит ближе всего ко всем имеющимся точкам данных, с помощью метода наименьших квадратов.
Эта функция бывает полезна в статистическом анализе и прогнозировании.
Синтаксис
ЛИНЕЙН(известные_данные_y; [известные_данные_x]; [b]; [статистика])
Аргумент |
Описание |
Допустимые значения |
|---|---|---|
известные_данные_y |
Массив или диапазон известных зависимых значений (y) |
Любые числовые значения, диапазоны ячеек с числами. Не допускаются текстовые значения или пустые ячейки без чисел |
[известные_данные_x] |
(необязательный) Значения независимых переменных, которые соответствуют аргументу "известные_данные_y". Если аргумент не указан, используется массив из чисел {1;2;3;...}. |
Любые числовые значения, диапазоны ячеек с числами. Не допускаются текстовые значения или пустые ячейки без чисел |
[b] |
(необязательный) Логическое значение, определяющее переменную, которую необходимо вычислить в линейном уравнении y = m·x+b. Вычисляет параметр b (ИСТИНА) или значение параметра m (ЛОЖЬ). По умолчанию ИСТИНА |
ИСТИНА или ЛОЖЬ |
[статистика] |
(необязательный) Логическое значение, определяющее, нужно ли вычислять дополнительную регрессионную статистику для переменных (ИСТИНА) или только значения переменных b и m (ЛОЖЬ). По умолчанию ЛОЖЬ |
ИСТИНА или ЛОЖЬ |
Структура вывода статистики
Строка |
Столбец 1 |
Столбец 2 |
Описание |
|---|---|---|---|
1 |
m (Xk) |
b |
Коэффициенты регрессии –m = наклон (коэффициент при X) –b = свободный член (константа) Уравнение: y = m·x + b |
2 |
SE(m) |
SE(b) |
Стандартные ошибки коэффициентов –SE(m) = точность оценки наклона –SE(b) = точность оценки константы Если SE > 2, значит коэффициент значим |
3 |
R² |
SE(y) |
Показатели качества модели –R² = коэффициент детерминации (0..1) –SE(y) = стандартная ошибка оценки Y R² = 0,95 означает: 95% изменчивости Y объясняется моделью |
4 |
F-стат |
df |
Дисперсионный анализ (ANOVA) –F = F-статистика (значимость модели) –df = степени свободы остатков = n-k-1 |
5 |
SSreg |
SSres |
Суммы квадратов –SSreg = объясненная сумма квадратов –SSres = остаточная сумма квадратов |
Примеры использования
Простой пример линейной регрессии
Если у вас есть данные о продажах и расходах, и вы хотите найти коэффициенты:
=ЛИНЕЙН(A2:A10; B2:B10)
Здесь A2:A10 — это диапазон значений y (например, продажи), а B2:B10 — диапазон значений x (например, расходы).
Выделите две ячейки и примените сочетание клавиш Ctrl+Shift+Enter после ввода формулы.
Результатом будет массив, где первый элемент — это коэффициент наклона, а второй — свободный член.
Расчет линейной регрессии с выводом статистики
Допустим, массив данных y находится в ячейках A1:A7: [1, 3, 5, 7, 10, 15, 19], массив данных x в ячейках B1:B7: [2, 5, 7, 9, 13, 17, 21].
Для получения данных о регрессии со статистикой необходимо выделить массив ячеек размером 5 строк × 2 столбца, ввести формулу =ЛИНЕЙН(A1:A7; B1:B7; 1; 1) и нажать сочетание клавиш Ctrl+Shift+Enter.
Результат:
Столбец 1 |
Столбец 2 |
Описание |
|---|---|---|
0,9601036269 |
-1,578238342 |
m = наклон (коэффициент при X) b = свободный член (константа) Уравнение: y = 0,9601·x - 1,5782 |
0,0336516431 |
0,413713877 |
SE(m) = точность оценки наклона SE(b) = точность оценки константы Оба коэффициента значимы, так как m/SE(m) = 28,5 > 2 и b/SE(b) = 3,8 > 2 |
0,9938949836 |
0,5587736387 |
R² = коэффициент детерминации (0..1) SE(y) = стандартная ошибка оценки Y R² = 0,994 означает: 99,4% изменчивости Y объясняется моделью |
813,99862501 |
5 |
F = F-статистика (значимость модели) df = здесь указано n (общее наблюдений), а не df остатков F > Fкрит — модель статистически значима |
254,15314582 |
1,5611398964 |
SSreg = объясненная сумма квадратов SSres = остаточная сумма квадратов SSreg/SSres = 162,8 — модель хорошо объясняет данные |
Примечания
–Функция возвращает массив значений, поэтому необходимо выделить диапазон и использовать комбинацию клавиш Ctrl+Shift+Enter.
–Убедитесь, что ваши диапазоны имеют одинаковую длину, иначе функция вернет ошибку.