Метод наименьших квадратов (МНК) в Excel — по шагам, с графиком и отчётом
Метод наименьших квадратов подбирает прямую y = b·x + a так, чтобы сумма квадратов отклонений точек от неё была наименьшей. В книге — суммы Σx, Σy, Σx², Σxy, формулы наклона и свободного члена и проверка функциями НАКЛОН, ОТРЕЗОК и ЛИНЕЙН.
Функции Excel: НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, КВПИРСОН. В примере — учебные данные; замените их своими.
Другие расчёты
Вопросы
Это готовая лабораторная?
Нет. Формулник считает и оформляет: книга Excel с формулами по шагам, график и отчёт с таблицами. Вывод — заготовка: допишите своими словами, что показали расчёты. Работу за вас мы не пишем.
Можно ли верить числам?
Каждую формулу книги пересчитывают два движка — LibreOffice и второй независимый, — и результат сверяется с расчётом по определению. Если хоть одно число не сходится, файл не выдаётся.
Как вставить данные?
Скопируйте столбцы из Excel или Word и вставьте: по столбцу на величину, по строке на измерение. Запятая или точка в дробях — неважно. Первая строка с названиями — по желанию.
Откроется ли в моём Excel?
Да: Excel 2010–365, LibreOffice, Р7-Офис и МойОфис. Формулы обычные, без макросов; в листе «По шагам» каждая формула записана и по-русски.
МНК в Excel: ЛИНЕЙН, НАКЛОН, ОТРЕЗОК и линия тренда
Для прямой y = b·x + a по методу наименьших квадратов в Excel достаточно двух функций: наклон =НАКЛОН(B2:B10;A2:A10) и свободный член =ОТРЕЗОК(B2:B10;A2:A10), где в B — значения y, в A — x. Качество подгонки показывает =КВПИРСОН(B2:B10;A2:A10) — коэффициент детерминации R². Те же числа и их погрешности одной формулой выдаёт ЛИНЕЙН со статистикой.
Проверочный пример: x = 1, 2, 3, 4, 5 и y = 2, 4, 5, 4, 5 дают b = 0,6, a = 2,2, R² = 0,6; прогноз при x = 6 — 5,8.
По шагам
- Введите x в столбец A, y в столбец B, без пустых строк между измерениями.
- Посчитайте наклон и свободный член функциями
НАКЛОНиОТРЕЗОК. Порядок аргументов — сначала y, потом x; перепутать их — самая частая ошибка. - Для погрешностей коэффициентов выделите диапазон 5 строк × 2 столбца, введите
=ЛИНЕЙН(B2:B10;A2:A10;ИСТИНА;ИСТИНА)и нажмите Ctrl+Shift+Enter (в Excel 365 — просто Enter). - Отдельные элементы без массива достаёт
ИНДЕКС: R² —=ИНДЕКС(ЛИНЕЙН(B2:B10;A2:A10;ИСТИНА;ИСТИНА);3;1). - Постройте точечную диаграмму (не «График»: он ставит точки через равные промежутки и игнорирует значения x).
- Правой кнопкой по точкам → «Добавить линию тренда» → «Линейная», отметьте «показывать уравнение» и «величину достоверности аппроксимации R²». Уравнение на графике должно совпасть с вашими
НАКЛОНиОТРЕЗОК. - Прогноз в новой точке из A11 —
=ТЕНДЕНЦИЯ(B2:B10;A2:A10;A11).
Если зависимость не прямая, а парабола
Добавьте столбец с x² (в B), а y перенесите в C. Тогда =ЛИНЕЙН(C2:C10;A2:B10) вернёт три коэффициента в обратном порядке: при x², при x и свободный член. На графике выбирайте линию тренда «Полиномиальная», степень 2.
Если нужна экспонента y = a·e^(bx)
Возьмите логарифм y: наклон =НАКЛОН(LN(B2:B10);A2:A10) даёт b, а a — это экспонента от ОТРЕЗОК с теми же аргументами. Функция ЛГРФПРИБЛ делает то же, но возвращает основание e^b, а не сам показатель — в отчёте это легко перепутать.
Если прямая должна проходить через ноль
В ЛИНЕЙН третий аргумент ставят ЛОЖЬ — свободный член принудительно равен нулю. Так делают, например, для закона Ома или Гука, где физически a = 0. Помните, что R² при этом считается по-другому и бывает завышен.
Что лежит в ячейках ЛИНЕЙН со статистикой (5 × 2)
| Строка | Столбец 1 | Столбец 2 |
|---|---|---|
| 1 | наклон b | свободный член a |
| 2 | стандартная ошибка b | стандартная ошибка a |
| 3 | R² | стандартная ошибка y |
| 4 | F-статистика | степени свободы n − 2 |
| 5 | регрессионная сумма квадратов | остаточная сумма квадратов |
Частые ошибки
- Аргументы перепутаны местами — во всех этих функциях первым идёт y. Если поменять, получится регрессия x по y и другой наклон.
- Диаграмма типа «График» — Excel считает x равными 1, 2, 3…, и уравнение тренда не совпадает с расчётом. Нужна «Точечная».
- ЛИНЕЙН показывает одно число — в Excel 2019 и раньше это формула массива: выделите 5×2 ячеек заранее и завершите ввод Ctrl+Shift+Enter.
- В отчёте только R² — преподаватели спрашивают погрешности коэффициентов: b ± стандартная ошибка, они во второй строке
ЛИНЕЙН.
Вопросы и ответы
Как найти коэффициенты a и b методом наименьших квадратов вручную?
b = (nΣxy − ΣxΣy) / (nΣx² − (Σx)²), a = (Σy − bΣx) / n. Суммы удобно считать отдельными столбцами x², xy и проверять итог функцией НАКЛОН.
Почему уравнение линии тренда отличается от ЛИНЕЙН?
Чаще всего из-за типа диаграммы «График» или из-за округления подписи: в формате подписи тренда увеличьте число знаков после запятой.
Что значит R² = 0,95?
Прямая объясняет 95 % разброса y. Для физической лабораторной с хорошими измерениями ожидают R² выше 0,98.
Чем ТЕНДЕНЦИЯ отличается от ПРЕДСКАЗ?
Результат для прямой одинаковый. ТЕНДЕНЦИЯ умеет работать с несколькими x и сразу с массивом новых точек, ПРЕДСКАЗ.ЛИНЕЙН — только с одной переменной.
Как получить погрешность наклона для отчёта?
Стандартная ошибка — =ИНДЕКС(ЛИНЕЙН(B2:B10;A2:A10;ИСТИНА;ИСТИНА);2;1). Для доверительного интервала умножьте её на t-критическое при n − 2 степенях свободы.