Описать задачу
Формулник › Для учёбы › Расчёты по шагам › Метод наименьших квадратов (МНК)

Метод наименьших квадратов (МНК) в Excel — по шагам, с графиком и отчётом

Редакция Формулника · обновлено · как мы проверяем

Метод наименьших квадратов подбирает прямую y = b·x + a так, чтобы сумма квадратов отклонений точек от неё была наименьшей. В книге — суммы Σx, Σy, Σx², Σxy, формулы наклона и свободного члена и проверка функциями НАКЛОН, ОТРЕЗОК и ЛИНЕЙН.

Функции Excel: НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, КВПИРСОН. В примере — учебные данные; замените их своими.

Считаем и оформляем — работу не пишем. Вывод в отчёте — заготовка: допишите его своими словами.

Другие расчёты

Линия тренда и прогноз (ЛИНЕЙН)Коэффициент корреляцииМатрица корреляцийПарная линейная регрессияОписательная статистикаДисперсия и стандартное отклонениеДоверительный интервалt-критерий СтьюдентаПогрешности измеренийГрафик функцииСистема линейных уравнений (матрицы)Подбор параметраИнтервальный ряд и гистограмма

Вопросы

Это готовая лабораторная?

Нет. Формулник считает и оформляет: книга 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.

По шагам

  1. Введите x в столбец A, y в столбец B, без пустых строк между измерениями.
  2. Посчитайте наклон и свободный член функциями НАКЛОН и ОТРЕЗОК. Порядок аргументов — сначала y, потом x; перепутать их — самая частая ошибка.
  3. Для погрешностей коэффициентов выделите диапазон 5 строк × 2 столбца, введите =ЛИНЕЙН(B2:B10;A2:A10;ИСТИНА;ИСТИНА) и нажмите Ctrl+Shift+Enter (в Excel 365 — просто Enter).
  4. Отдельные элементы без массива достаёт ИНДЕКС: R² — =ИНДЕКС(ЛИНЕЙН(B2:B10;A2:A10;ИСТИНА;ИСТИНА);3;1).
  5. Постройте точечную диаграмму (не «График»: он ставит точки через равные промежутки и игнорирует значения x).
  6. Правой кнопкой по точкам → «Добавить линию тренда» → «Линейная», отметьте «показывать уравнение» и «величину достоверности аппроксимации R²». Уравнение на графике должно совпасть с вашими НАКЛОН и ОТРЕЗОК.
  7. Прогноз в новой точке из 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
3R²стандартная ошибка y
4F-статистикастепени свободы n − 2
5регрессионная сумма квадратовостаточная сумма квадратов

Частые ошибки

Вопросы и ответы

Как найти коэффициенты 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 степенях свободы.