Как применить интерполяцию в Microsoft Excel

Интерполяция — это способ вычислить промежуточное значение функции, которое не указано в таблице, но должно находиться внутри имеющегося ряда данных. Например, если известны показания температуры в 9:00 и в 12:00, то с помощью интерполяции можно оценить, сколько градусов было в 10:00. Главное условие: искомое значение по оси X должно лежать между крайними точками набора данных, а не выходить за его пределы. Если нужно найти значение за пределами диапазона, применяется другая процедура: экстраполяция.

Для примеров во всех способах ниже используется одна и та же таблица. В столбце A находятся значения времени суток в часах, а в столбце B хранятся показания температуры в градусах Цельсия:

• A2:A6: часы: 6, 9, 12, 15, 18.
• B2:B6: температура: 8, 14, 22, 19, 12.

Задача во всех способах одна: найти температуру в 10:00, то есть вычислить значение Y при X = 10. Соответственно, можете отталкиваться от подобного примера, чтобы лучше понимать то, как работают те или иные разобранные методы.

Способ 1: Формула линейной интерполяции

Линейная интерполяция предполагает, что между двумя соседними точками зависимость изменяется равномерно, по прямой линии. Именно этот способ дает наиболее точный результат, когда искомое значение расположено между двумя конкретными записями в таблице, и он не требует никаких специальных функций, только обычную формулу с арифметическими операторами.

Математически это выглядит так: y = y1 + (x - x1) * (y2 - y1) / (x2 - x1), где x1 и x2 — соседние известные аргументы, между которыми расположен искомый x, а y1 и y2 — соответствующие им значения функции.

1. Определите, между какими двумя строками таблицы расположен ваш аргумент. В нашем примере x = 10 находится между значениями 9 (ячейка A3) и 12 (ячейка A4). Значит, y1 = 14 хранится в B3, а y2 = 22 хранится в B4.

2. Выделите свободную ячейку для результата, например D2, и введите формулу:

   =B3+(10-A3)*(B4-B3)/(A4-A3)

   Вместо числа 10 можно сослаться на ячейку с искомым значением аргумента, и тогда формула будет пересчитываться автоматически при его изменении.


3. Нажмите Enter. В ячейке отобразится результат: примерно 16,67 градуса. Это означает, что по линейной интерполяции в 10:00 температура составляла около 16,7°C.

Этот способ работает строго между двумя соседними точками. Если в таблице несколько десятков строк и нужно автоматически находить ближайшие x1 и x2 без ручного выбора, формулу дополняют функциями ПОИСКПОЗ и ИНДЕКС, об этом подробнее написано в блоке дополнительной информации в конце статьи.

Способ 2: Функция ПРЕДСКАЗ.ЛИНЕЙН

Функция ПРЕДСКАЗ.ЛИНЕЙН рассчитывает прогнозное значение на основе линейного тренда, построенного по всему набору данных сразу. В отличие от способа выше, она не берет две соседних точки, а анализирует весь диапазон целиком и вычисляет значение, которое лучше всего вписывается в общую линейную зависимость. Если данные в таблице близки к прямолинейному росту или убыванию, функция даст хороший результат. При нелинейных зависимостях ее точность снижается, и лучше использовать ручную формулу из Способа 1.

В версиях Excel 2016 и новее используется именно ПРЕДСКАЗ.ЛИНЕЙН. В более ранних версиях программы аналогичную роль выполняет функция ПРЕДСКАЗ с идентичным синтаксисом.

1. Выделите ячейку для результата, например D2.

2. Введите формулу:

   =ПРЕДСКАЗ.ЛИНЕЙН(10;B2:B6;A2:A6)

   Первый аргумент — искомое значение по оси X, второй — диапазон известных значений Y, третий — диапазон известных значений X.


3. Нажмите Enter. Функция вернет прогнозное значение, рассчитанное по линейному тренду всей таблицы. В нашем примере результат составит около 14,1 градуса, что заметно меньше результата ручной формулы из Способа 1 (16,67). Расхождение объясняется нелинейностью данных: температура сначала растет, затем падает, и общий линейный тренд не описывает этот профиль точно. Чем сильнее данные отклоняются от прямолинейной зависимости, тем заметнее разница между двумя подходами.

Способ 3: Функция ТЕНДЕНЦИЯ

Функция ТЕНДЕНЦИЯ решает ту же задачу, что и ПРЕДСКАЗ.ЛИНЕЙН, но позволяет за один вызов вычислить сразу несколько промежуточных значений, что удобно, когда нужно восстановить целый ряд пропущенных данных, а не одно число. Принцип тот же: функция строит линейный тренд по всему массиву и проецирует на него указанные аргументы.

1. Если нужно найти одно значение, выделите ячейку D2 и введите:

   =ТЕНДЕНЦИЯ(B2:B6;A2:A6;10)

   Третий аргумент — это новое значение X, для которого нужно вычислить Y. Нажмите Enter, и функция вернет результат, аналогичный ПРЕДСКАЗ.ЛИНЕЙН.


2. Чтобы найти значения сразу для нескольких аргументов, например для X = 10 и X = 11, запишите их в ячейки D2 и D3, затем выделите пустые ячейки E2:E3 и введите формулу массива:

   =ТЕНДЕНЦИЯ(B2:B6;A2:A6;D2:D3)

   В Excel 365 и Excel 2021 нажмите Enter, и формула автоматически заполнит весь выделенный диапазон. В более ранних версиях используйте Ctrl + Shift + Enter.

Способ 4: Устранение разрыва на графике через настройки

Когда таблица содержит пустую ячейку в столбце Y, Excel при построении графика отображает разрыв в линии на соответствующем участке. Убрать этот разрыв без заполнения ячейки данными позволяет встроенная настройка, при которой программа самостоятельно интерполирует недостающую точку и отображает непрерывную кривую.

Для примера предположим, что в таблице ячейка B4 (температура в 12:00) пустая, и на графике в этом месте видна разорванная линия.

1. Постройте график на основе диапазона A1:B6: выделите его, перейдите на вкладку «Вставка» и в группе «Диаграммы» нажмите на значок линейного графика, выбрав подходящий вид.

2. Кликните правой кнопкой мыши по области диаграммы и выберите «Выбрать данные».

3. В открывшемся окне нажмите кнопку «Скрытые и пустые ячейки» в нижнем левом углу.

4. В появившемся окне найдите параметр «Показывать пустые ячейки как» и переключите его в положение «Линию». Нажмите «ОК», затем еще раз «ОК» в окне выбора данных.

5. Разрыв на графике исчезнет: Excel соединит соседние точки прямой линией, интерполировав пропущенное значение визуально. Сама ячейка B4 при этом остается пустой.

Способ 5: Функция НД() для устранения разрыва на графике

Альтернативный способ убрать разрыв на графике: заполнить пустую ячейку специальным значением ошибки #Н/Д с помощью функции НД(). Excel воспринимает это значение особым образом: в таблице ячейка выглядит как ошибка, однако на графике программа автоматически интерполирует ее положение и строит непрерывную линию. В отличие от Способа 4, настройки диаграммы менять не нужно, достаточно правильно заполнить ячейку.

1. Выделите пустую ячейку в столбце Y, для которой отсутствует значение, в нашем примере это B4.

2. Введите формулу =НД() и нажмите Enter. В ячейке появится значение ошибки #Н/Д.

3. Если график уже был построен на основе этого диапазона, линия автоматически замкнется без разрыва. Если диаграмма еще не создана, постройте ее стандартным образом и разрыва изначально не будет.

Обратите внимание, что вводить текст #Н/Д прямо с клавиатуры не стоит: Excel сохранит его как обычную текстовую строку, и на графике ничего не изменится. Для корректного результата используйте именно формулу =НД().

Дополнительная информация

Ниже собраны нюансы, которые не вошли в основные способы, но часто влияют на корректность результата. Особое внимание стоит уделить первому пункту, если таблица содержит много строк и выбирать соседние точки вручную неудобно.

• Автоматический поиск соседних точек для линейной интерполяции. Если таблица большая и соседние строки нужно находить автоматически, формулу из Способа 1 можно расширить с помощью функций ПОИСКПОЗ и ИНДЕКС. Предположим, искомый x хранится в ячейке D2, аргументы размещены в A2:A6, значения в B2:B6. Тогда формула примет вид: =ИНДЕКС(B2:B6;ПОИСКПОЗ(D2;A2:A6;1))+(D2-ИНДЕКС(A2:A6;ПОИСКПОЗ(D2;A2:A6;1)))*(ИНДЕКС(B2:B6;ПОИСКПОЗ(D2;A2:A6;1)+1)-ИНДЕКС(B2:B6;ПОИСКПОЗ(D2;A2:A6;1)))/(ИНДЕКС(A2:A6;ПОИСКПОЗ(D2;A2:A6;1)+1)-ИНДЕКС(A2:A6;ПОИСКПОЗ(D2;A2:A6;1))). Функция ПОИСКПОЗ с третьим аргументом 1 находит позицию наибольшего значения, не превышающего x, а ИНДЕКС извлекает соответствующие y1, y2, x1 и x2 из массивов. Для корректной работы столбец X должен быть отсортирован по возрастанию.

• Отличие интерполяции от экстраполяции. Интерполяция работает только внутри диапазона известных данных. Если нужно найти значение за пределами крайних точек, например температуру в 20:00 при наличии данных только до 18:00, потребуется экстраполяция. ПРЕДСКАЗ.ЛИНЕЙН и ТЕНДЕНЦИЯ формально позволяют задавать x за пределами диапазона, однако точность такого прогноза при нелинейных данных будет низкой.

• Когда ПРЕДСКАЗ.ЛИНЕЙН и ручная формула дают разные результаты. Это нормальная ситуация. Ручная формула считает строго между двумя соседними точками, а ПРЕДСКАЗ.ЛИНЕЙН строит линейную регрессию по всему массиву целиком. При нелинейных данных эти результаты могут заметно расходиться, и в таких случаях ручная формула точнее, поскольку учитывает только ближайшие соседние значения.
• Ошибка деления на ноль в формуле. Если при вводе ручной формулы Excel возвращает #ДЕЛ/0!, это означает, что значения x1 и x2 в знаменателе совпадают, то есть в таблице есть дублирующиеся аргументы. Проверьте столбец X на наличие повторяющихся значений и удалите их.