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

Суть методу
Маємо пари значень (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 порахує множинну лінійну регресію.
Лінія тренду на діаграмі
- Виділіть A1:B6 і вставте точкову діаграму: «Вставлення» → «Точкова».
- Натисніть правою кнопкою на точках → «Додати лінію тренду».
- У панелі параметрів виберіть «Лінійна» і позначте «Показувати рівняння на діаграмі» та «Показувати значення R² на діаграмі».
Лінія тренду теж будується методом найменших квадратів, і її рівняння збігається з результатом SLOPE / INTERCEPT. Там само є експоненційна, логарифмічна, поліноміальна і степенева моделі.
«Пошук рішення» для нестандартних моделей
Якщо залежність не зводиться до лінійної (або потрібні обмеження на коефіцієнти), МНК можна виконати надбудовою «Пошук рішення» (Solver):
- Увімкніть її: «Файл» → «Параметри» → «Надбудови» → «Керування: Надбудови Excel» → «Перейти» → позначте «Пошук рішення».
- Заведіть клітинки з коефіцієнтами (наприклад, E2 і E3), стовпець прогнозу (наприклад,
=$E$2*A2+$E$3) і клітинку з сумою квадратів відхилень:=SUMXMY2(B2:B6;C2:C6). - «Дані» → «Пошук рішення»: цільова клітинка — сума квадратів, «Мінімум», змінювані клітинки — коефіцієнти, метод розв'язання — GRG Nonlinear (нелінійний).
Докладно про інструмент — у довідці Microsoft «Пошук рішення».
Часті питання
У чому полягає сутність методу найменших квадратів?
Підібрати параметри моделі так, щоб сума квадратів різниць між фактичними і розрахунковими значеннями була мінімальною. Квадрат робить усі відхилення додатними і сильніше «штрафує» великі помилки.
Як знайти коефіцієнти МНК в Excel? (метод наименьших квадратов excel
)
Функціями SLOPE і INTERCEPT або однією LINEST; наочно — лінією тренду з рівнянням на точковій діаграмі.
Чи працює це в Google Таблицях і LibreOffice Calc?
Так, функції SLOPE, INTERCEPT, RSQ і LINEST є в обох програмах, лінія тренду — теж.