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