Інтерполяція в Excel

Як зробити інтерполяцію в Excel: функція FORECAST.LINEAR, точна лінійна інтерполяція між сусідніми точками з OFFSET і MATCH, розрив графіка і функція NA(), нелінійні дані.

Інтерполяція — це пошук проміжного значення всередині відомого ряду даних: є значення функції для 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 з'єднав сусідні точки прямою (тобто інтерполював візуально):

  1. Клацніть діаграму правою кнопкою миші → «Вибрати дані».
  2. Натисніть «Приховані та пусті клітинки» в лівому нижньому куті.
  3. Для пустих клітинок виберіть «З'єднати точки даних лінією» і натисніть «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().

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