Інтерполяція — це пошук проміжного значення всередині відомого ряду даних: є значення функції для x = 20 і x = 30, а потрібне для x = 25. В Excel це роблять трьома способами: функцією FORECAST.LINEAR (лінійна залежність по всьому ряду), формулою між двома сусідніми точками (класична лінійна інтерполяція) і налаштуваннями графіка, коли в даних є пропуск.
Інтерполяція і екстраполяція
Інтерполяція працює лише в межах відомих аргументів. Якщо відомі x = 10, 20, 30 і 40, то значення для 25 можна інтерполювати, а для 50 — уже ні: це екстраполяція, тобто прогноз за межами даних, і її точність значно нижча. Функції Excel порахують результат в обох випадках, тому стежте за межами самі.
Приклад для всіх способів нижче: у стовпці A (A2:A5) — аргументи 10, 20, 30, 40, у стовпці B (B2:B5) — значення 100, 140, 190, 230, у клітинці D2 — аргумент 22, для якого шукаємо значення.
Спосіб 1: FORECAST.LINEAR — лінійна залежність по всіх точках
Якщо дані приблизно лежать на прямій, введіть у порожню клітинку:
=FORECAST.LINEAR(D2; B2:B5; A2:A5)
Аргументи: x, для якого шукаємо значення; відомі значення y; відомі значення x. Функція будує пряму лінійної регресії через усі точки і повертає значення на ній (довідка Microsoft). Стара назва FORECAST теж працює для сумісності. Розділювач аргументів залежить від регіональних налаштувань: крапка з комою або кома.
Важливо: це не точна інтерполяція між сусідніми точками. Якщо дані «ламані», результат FORECAST.LINEAR відрізнятиметься від значення на відрізку між сусідами — для нашого прикладу вона дасть 151,8, тоді як лінійна інтерполяція між точками 20 і 30 дає рівно 150.
Спосіб 2: лінійна інтерполяція між двома сусідніми точками
Класична формула: y = y₁ + (x − x₁) · (y₂ − y₁) / (x₂ − x₁), де (x₁; y₁) і (x₂; y₂) — найближчі точки зліва і справа від x. Для нашого прикладу: 140 + (22 − 20) · (190 − 140) / (30 − 20) = 150.
Щоб Excel сам знаходив сусідні точки, аргументи в стовпці A мають бути відсортовані за зростанням. Тоді формула:
=FORECAST.LINEAR(D2; OFFSET(B2; MATCH(D2; A2:A5; 1) - 1; 0; 2); OFFSET(A2; MATCH(D2; A2:A5; 1) - 1; 0; 2))
MATCH з типом 1 знаходить позицію найбільшого аргументу, що не перевищує D2, а OFFSET вирізає дві сусідні точки — через дві точки FORECAST.LINEAR проводить саме відрізок між ними. Обмеження: x має бути строго всередині ряду (для останнього аргументу формула вийде за межі таблиці).
Спосіб 3: пропуск у даних на графіку
Якщо для одного з аргументів значення немає, лінійний графік за замовчуванням розривається. Щоб Excel з'єднав сусідні точки прямою (тобто інтерполював візуально):
- Клацніть діаграму правою кнопкою миші → «Вибрати дані».
- Натисніть «Приховані та пусті клітинки» в лівому нижньому куті.
- Для пустих клітинок виберіть «З'єднати точки даних лінією» і натисніть «OK».
Другий варіант — поставити в пусту клітинку функцію =NA(): вона повертає помилку #N/A, яку лінійна діаграма пропускає й з'єднує сусідні точки (довідка Microsoft). Так зручно позначати відсутні дані в таблицях, з яких будуються графіки. У нових версіях Excel у тому ж вікні «Приховані та пусті клітинки» є перемикач, чи показувати #N/A як пусту клітинку, — перевірте його, якщо лінія все одно розривається.
Як побудувати сам графік — у статті як зробити графік в Excel.
Якщо залежність нелінійна
- Побудуйте точкову діаграму і додайте лінію тренду (поліноміальну, експоненційну, логарифмічну) з параметром «Показувати рівняння на діаграмі» — підставляйте x у отримане рівняння.
- Для поліноміальної залежності можна використати функцію LINEST з масивом степенів x.
- Для точних інженерних розрахунків (сплайни) зручніші LibreOffice Calc з розширеннями, Python (NumPy / SciPy) або R.
У LibreOffice Calc і Google Таблицях
У LibreOffice Calc є ті самі функції FORECAST.LINEAR, OFFSET, MATCH і NA, тож формули працюють без змін. У Google Таблицях — FORECAST, OFFSET, MATCH і NA() з комою як розділювачем.
Часті питання
Як зробити інтерполяцію в Excel? (интерполяция в эксель
)
Для лінійних даних — =FORECAST.LINEAR(x; відомі_y; відомі_x). Для точної інтерполяції між сусідніми точками — формула з OFFSET і MATCH зі способу 2.
Яка функція інтерполяції є в Excel? (функция интерполяции в excel
)
Окремої функції «INTERPOLATE» немає. Використовують FORECAST.LINEAR, TREND або формулу лінійної інтерполяції.
Чому FORECAST.LINEAR дає не те значення, що я рахую вручну?
Функція рахує регресію по всіх точках, а не відрізок між двома сусідніми. Обмежте діапазони двома точками (спосіб 2).
Як прибрати розрив лінії на графіку Excel?
«Вибрати дані» → «Приховані та пусті клітинки» → «З'єднати точки даних лінією» або поставте в пусту клітинку =NA().