Метод найменших квадратів в Excel

Як застосувати метод найменших квадратів в Excel: суть і формули, приклад з функціями SLOPE, INTERCEPT, RSQ і LINEST, лінія тренду на діаграмі і «Пошук рішення» для нестандартних моделей.

Метод найменших квадратів (МНК) підбирає пряму y = a·x + b, для якої сума квадратів відхилень точок від прямої мінімальна. В Excel не потрібно нічого рахувати вручну: коефіцієнти дають функції SLOPE і INTERCEPT або LINEST, а на діаграмі — лінія тренду з рівнянням. Надбудова «Пошук рішення» потрібна лише для нестандартних моделей.

Метод найменших квадратів в Excel

Суть методу

Маємо пари значень (x, y) — наприклад, місяць і обсяг продажів. Шукаємо коефіцієнти a (нахил) і b (зсув), щоб сума Σ(yᵢ − (a·xᵢ + b))² була найменшою. Для лінійної залежності існують готові формули:

  • a = (n·Σxy − Σx·Σy) / (n·Σx² − (Σx)²)
  • b = (Σy − a·Σx) / n

де n — кількість точок. Саме за цими формулами працюють функції Excel нижче. Якщо пряма має проходити через початок координат (b = 0), то a = Σxy / Σx².

Приклад: функції SLOPE, INTERCEPT і RSQ

Введіть дані: у стовпець A (A2:A6) — x: 1, 2, 3, 4, 5; у стовпець B (B2:B6) — y: 2,1; 3,9; 6,2; 7,8; 10,1.

Що рахуємоФормулаРезультат
Нахил a=SLOPE(B2:B6;A2:A6)1,99
Зсув b=INTERCEPT(B2:B6;A2:A6)0,05
Коефіцієнт детермінації R²=RSQ(B2:B6;A2:A6)≈ 0,9973
Прогноз для x = 6=FORECAST.LINEAR(6;B2:B6;A2:A6)11,99

Отже, рівняння прямої — y = 1,99x + 0,05. R², близький до 1, означає, що пряма добре описує дані. Перевірка вручну: Σx = 15, Σy = 30,1, Σxy = 110,2, Σx² = 55, тож a = (5·110,2 − 15·30,1) / (5·55 − 15²) = 99,5 / 50 = 1,99.

Розділювач аргументів (; чи ,) залежить від регіональних налаштувань Windows. В українській локалізації Excel назви функцій можуть відображатися перекладеними, але англійські назви працюють у Excel для вебу і в англійському інтерфейсі.

Функція LINEST: усе одразу

LINEST повертає масив коефіцієнтів і статистик. У Microsoft 365 та Excel 2021+ введіть у клітинку:

=LINEST(B2:B6;A2:A6;TRUE;TRUE)

Результат «розіллється» на діапазон 5 × 2: у першому рядку — a і b, нижче — їх стандартні похибки, R², F-статистика тощо (опис — у довідці Microsoft до LINEST). Для прямої через початок координат третій аргумент ставлять FALSE: =LINEST(B2:B6;A2:A6;FALSE) дасть a ≈ 2,0036 (110,2 / 55).

Для кількох факторів (x₁, x₂ …) передайте в другому аргументі кілька стовпців — LINEST порахує множинну лінійну регресію.

Лінія тренду на діаграмі

  1. Виділіть A1:B6 і вставте точкову діаграму: «Вставлення» → «Точкова».
  2. Натисніть правою кнопкою на точках → «Додати лінію тренду».
  3. У панелі параметрів виберіть «Лінійна» і позначте «Показувати рівняння на діаграмі» та «Показувати значення R² на діаграмі».

Лінія тренду теж будується методом найменших квадратів, і її рівняння збігається з результатом SLOPE / INTERCEPT. Там само є експоненційна, логарифмічна, поліноміальна і степенева моделі.

«Пошук рішення» для нестандартних моделей

Якщо залежність не зводиться до лінійної (або потрібні обмеження на коефіцієнти), МНК можна виконати надбудовою «Пошук рішення» (Solver):

  1. Увімкніть її: «Файл» → «Параметри» → «Надбудови» → «Керування: Надбудови Excel» → «Перейти» → позначте «Пошук рішення».
  2. Заведіть клітинки з коефіцієнтами (наприклад, E2 і E3), стовпець прогнозу (наприклад, =$E$2*A2+$E$3) і клітинку з сумою квадратів відхилень: =SUMXMY2(B2:B6;C2:C6).
  3. «Дані» → «Пошук рішення»: цільова клітинка — сума квадратів, «Мінімум», змінювані клітинки — коефіцієнти, метод розв'язання — GRG Nonlinear (нелінійний).

Докладно про інструмент — у довідці Microsoft «Пошук рішення».

Часті питання

У чому полягає сутність методу найменших квадратів?

Підібрати параметри моделі так, щоб сума квадратів різниць між фактичними і розрахунковими значеннями була мінімальною. Квадрат робить усі відхилення додатними і сильніше «штрафує» великі помилки.

Як знайти коефіцієнти МНК в Excel? (метод наименьших квадратов excel)

Функціями SLOPE і INTERCEPT або однією LINEST; наочно — лінією тренду з рівнянням на точковій діаграмі.

Чи працює це в Google Таблицях і LibreOffice Calc?

Так, функції SLOPE, INTERCEPT, RSQ і LINEST є в обох програмах, лінія тренду — теж.

Схожі інструкції