Как сделать интерполяцию в excel

Обновлено: 06.07.2024

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

Использование интерполяции

Главное условие, при котором можно применять интерполяцию – это то, что искомое значение должно быть внутри массива данных, а не выходить за его предел. Например, если мы имеем набор аргументов 15, 21 и 29, то при нахождении функции для аргумента 25 мы можем использовать интерполяцию. А для поиска соответствующего значения для аргумента 30 – уже нет. В этом и является главное отличие этой процедуры от экстраполяции.

Способ 1: интерполяция для табличных данных

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

«Вставить функцию»

X;
Известные значения y;
Известные значения x.

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

В поле «Известные значения y» нужно указать координаты диапазона таблицы, в котором содержатся значения функции. Это можно сделать вручную, но гораздо проще и удобнее установить курсор в поле и выделить соответствующую область на листе.

Аналогичным образом устанавливаем в поле «Известные значения x» координаты диапазона с аргументами.

Способ 2: интерполяция графика с помощью его настроек

Процедуру интерполяции можно применять и при построении графиков функции. Актуальна она в том случае, если в таблице, на основе которой построен график, к одному из аргументов не указано соответствующее значение функции, как на изображении ниже.

«Вставка»

«График»

«Диаграммы»

Как видим, график скорректирован, а разрыв с помощью интерполяции удален.

Способ 3: интерполяция графика с помощью функции

Произвести интерполяцию графика можно также с помощью специальной функции НД. Она возвращает неопределенные значения в указанную ячейку.

«Вставить функцию»

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Интерполяция - это метод, который используется для оценки или определения значения между двумя известными значениями на линии или кривой. Этот вид прогнозирования используется во многих видах анализа, таких как инвестиции в рост, прогнозирование чисел, установление стратегий, страховые решения, движения цен, акции и рынки акций и т. Д.

Линейная интерполяция означает оценку будущего значения определенной переменной на основе текущих данных. В MS-Excel создается прямая линия, которая соединяет два известных значения, и, таким образом, будущее значение рассчитывается с использованием простой математической формулы или функции FORECAST.

Примеры для интерполяции в Excel

Давайте разберемся, как интерполировать в Excel с некоторыми примерами.

Вы можете скачать этот шаблон Интерполировать Excel здесь - Шаблон Интерполировать Excel

Пример № 1 - Использование простой математической формулы

Допустим, у нас есть простой набор данных из двух известных значений x и y, и мы хотим интерполировать значение (т.е. найти соответствующее значение y для значения x) следующим образом:

Итак, простая формула, которая используется для интерполяции этого значения:

Поэтому, когда мы применяем эту формулу к данному набору данных, мы получаем интерполированное значение y как:

Таким образом, мы можем видеть на скриншоте выше, что мы интерполировали значение с двумя известными значениями x и y. Могут быть моменты, когда становится трудно запомнить формулу. Таким образом, функция ПРОГНОЗ может быть использована в таких случаях.

Пример №2 - Использование функции FORECAST

Теперь допустим, что мы хотим интерполировать то же значение в примере 1 с помощью функции FORECAST.

Функция ПРОГНОЗ оценивает значение на основе существующих значений вместе с линейным трендом. Он имеет следующий синтаксис:

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

Таким образом, мы можем видеть на скриншоте выше, что функция FORECAST также хорошо работает для этого.

Пример № 3 - Использование функции прогноза

Теперь допустим, что у нас есть набор данных о розничной фирме, с указанием количества дней и соответствующих продаж фирмы в те дни (т. Е. Количества единиц, проданных в те дни), как показано ниже:

Давайте сначала посмотрим синтаксис функции OFFSET и функции MATCH:

Функция OFFSET возвращает ячейку или диапазон ячеек с указанным количеством строк и столбцов, в зависимости от высоты и ширины в указанных строках и столбцах. Он имеет следующий синтаксис:

OFFSET (ссылка, строки, столбцы, (высота), (ширина))

ссылка: это отправная точка, откуда начинается отсчет строк и столбцов.
row: это число строк, смещаемых ниже начальной ячейки ссылки.
cols: это число столбцов, которые должны быть смещены вправо от начальной ссылочной ячейки.
высота: опционально; Из возвращенной ссылки это высота строк.
ширина: необязательно; Из возвращенной ссылки это ширина столбцов.

Функция MATCH возвращает относительное положение искомого значения в строке, столбце или таблице, которое соответствует указанному значению в указанном порядке. Он имеет следующий синтаксис:

MATCH (lookup_value, lookup_array, (match_type))

lookup_value: это значение, которое необходимо сопоставить или просмотреть из lookup_array.
lookup_array: это массив или диапазон ячеек, в которых нужно искать lookup_value.
match_type: необязательно; это может принимать значения 1, 0, -1.

Значение по умолчанию для этого match_type равно 1. Для значения 1 функция MATCH найдет наибольшее значение, которое меньше или равно lookup_value, и значение должно быть в порядке возрастания. Для значения 0 функция MATCH находит первое значение, которое точно равно lookup_value. Для значения -1 функция найдет наименьшее значение, которое больше или равно lookup_value, и значение должно быть в порядке убывания.

Теперь, если мы хотим оценить продажи, скажем, на 28 дней, мы используем эти функции следующим образом:

Вторая функция OFFSET, используемая в качестве третьего параметра в функции FORECAST, используется для выбора известных_х (независимых значений, то есть количества дней).

Функция MATCH, используемая в качестве параметра в функции OFFSET, используется для генерации позиции значения, которая должна быть спрогнозирована, и, таким образом, для вычисления количества строк. Столбцы в функции MATCH, т. Е. Второй параметр в ней должен быть 0, так как зависимое значение требуется для того же выбранного столбца.

Таким образом, в течение 28 дней мы оценили или прогнозировали продажи фирмы как 1120. Аналогично, мы можем оценить продажи фирмы за другое количество дней, используя эту функцию ПРОГНОЗ.

Что нужно помнить о интерполяции в Excel

Процесс извлечения простой функции из набора данных дискретных значений, так что функция проходит через все заданные значения и, таким образом, может использоваться для прогнозирования значений между заданными значениями, называется интерполяцией.
Он используется для определения того, какие данные могут существовать вне собранных данных.
Линейная интерполяция не является точным методом в MS Excel, однако, это экономит время и быстро.
Линейная интерполяция может даже использоваться для прогнозирования значений осадков, географических точек данных и т. Д.
Если данные не являются линейными, то для таких интерполяций в таких случаях могут использоваться другие методы: полиномиальная интерполяция, сплайн-интерполяция и т. Д.
Функция FORECAST может даже использоваться для экстраполяции или прогнозирования будущих значений.

Интерполяция с функцией прогноза в Excel

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

Синтаксис функции прогноза

Аргументы функции прогноза:

Теперь, рассмотрев тот же пример, приведенный выше, попробуем использовать функцию Forecast.

В нашем примере

x - ячейка B4 (так как мы хотим узнать время, затрачиваемое при скорости в 30 км / час).

Итак, окончательная формула в ячейке B4 будет такой:

= ПРОГНОЗ (А4, В2: В3, А2: А3)

И, как вы можете видеть, конечный результат такой же, то есть 30 минут .

Для создания диаграммы перейдите в меню «Вставка», нажмите «Разброс», а затем выберите «Разброс с гладкими линиями и маркерами».

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

Примеры линейной интерполяции в Excel

Давайте разберемся с линейной интерполяцией в Excel на нескольких примерах.

Вы можете скачать этот шаблон Excel с линейной интерполяцией здесь - Шаблон Excel с линейной интерполяцией

Пример № 1

Предположим, у вас есть данные о продажах и прибыли за предыдущие годы, и вы хотите узнать прибыль за текущий год, если достигнете определенного уровня продаж.

Посмотрите на таблицу ниже. У вас есть данные о продажах с 2016 по 2018 год, и вы хотите знать, какой должна быть прибыль, если ваши продажи составляют рупии. 40 000 000 в 2019 году.

Таким образом, с помощью функции прогнозирования мы можем интерполировать прибыль 2019 года, когда продажи составляют рупии. 4000000

Формула в ячейке C5 будет выглядеть следующим образом:

= ПРОГНОЗ (B5, C2: C4, B2: B4)

После использования формулы прогноза ответ показан ниже.

Результат для функции Прогноз будет Rs. 875 000 на основе данных о продажах и прибыли, доступных с 2016 по 2018 год.

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

Пример № 2

У нас есть данные о последних 9 матчах команды по крикету, в которой пропали калитки и забиты команды.

Мы хотим выяснить, сколько пробежек принесет команда, если выпадет 8 калиток.

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

После применения формулы мы получаем результат 302 пробежек, если у нас выпало 8 калиток на основе линейной регрессии последних 9 матчей, сыгранных командой.

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

Что нужно помнить о линейной интерполяции в Excel

Метод интерполяции: что это такое?

В вычислительной математике так называют способ нахождения промежуточных неизвестных значений функции Y(X) по дискретному набору уже известных.

Интерполяция функции Y(X) может осуществляться только для тех ее аргументов, которые находятся внутри интервала [X₀, X_n], такого, что известны значения Y(X₀) и Y(X_n).

Если X не принадлежит [X₀, X_n], то можно использовать метод экстраполяции.

В классической постановке интерполяционной задачи требуется найти приближенную аналитическую функцию φ(X), у которой значения в узловых точках X_i совпадают со значениями Y(X_i) исходной таблицы, т. е. соблюдается условие φ (X_i)=Y_i (i = 0,1,2. n).

Линейная интерполяция в Excel

В самом известном табличном процессоре от Microsoft присутствует крайне полезный оператор «ПРЕДСКАЗ».

Рассмотрим данные, размещенные в в таблице, представленной ниже.

В первом столбце находятся аргументы x, а во втором — соответствующие им значения некоторой линейной функции f(x). Предположим, что нам нужно узнать значение для аргумента x=28. Для этого:

выделяют любую пустую ячейку на листе табличного процессора, куда будет выводиться результат от осуществленных действий, например C1;
кликают по значку «fx» («Вставить функцию»), размещенному слева от строки формул;
в окошке «Мастера функций» заходят в категорию «Математические»;
находят оператор «ПРЕДСКАЗ» и нажимают на «OK».

В окне аргументов есть 3 поля. В первое вводят с клавиатуры значение аргумента (в конкретной задаче это 28). Для того чтобы заполнить поле «Известные значения _ y», нажимают на иконку с красной стрелкой слева от соответствующего окошка и выделяют соответствующую область на листе. В конкретном случае это часть столбца В с адресами из диапазона B2:B7.

Точно так же заполняют поле «Известные значения _ x» и нажимают на кнопку «Ок».

В результате в выделенной ячейке C1 отображается значение 176, являющееся итогом процедуры интерполяции.

Графический метод: подготовка

Интерполяция в Excel, пример которой представлен выше, далеко не единственный способ, позволяющий выяснить промежуточные неизвестные значения функции Y(X) по дискретному набору уже известных. В частности, может быть применен графический метод. Он может оказаться полезным, если в таблице к одному из аргументов не указано соответствующее значение функции, как в той, что представлена ниже (см. ячейку с адресом B9).

Интерполяция в Excel в таком случае начинается с построения графика. Для этого:

во вкладке «Вставка» выделяют табличный диапазон;
в блоке инструментов «Диаграммы» выбирают значок «График»;
в появившемся списке выбирают тот, который лучше подходит для решения конкретной задачи.

Так как в ячейке B9 пусто, график получился разорванный. Кроме того, на нем присутствует дополнительная линия X, в которой нет необходимости, а на горизонтальной оси вместо значений аргумента указаны пункты по порядку.

Интерполяция в Excel: решение графическим методом

Займемся обработкой графика. Для этого выделяют сплошную синюю линию и удаляют ее нажатием кнопки Delete, которая находится на клавиатуре.

выделяют плоскость, на которой находится график;
в контекстном меню выбирают кнопку «Выбрать данные…»;
в окне «Выбор источника данных» в правом блоке нажимают «Изменить»;
нажимают на иконку с красной стрелкой справа от поля «Диапазон подписей осей»;
выделяют диапазон А2:А11;
нажимают на кнопку «OK»;
вновь вызывают окно «Выбор источника данных»;
нажимают на кнопку «Скрытые и пустые ячейки» в нижнем левом углу;
в строке «Показывать пустые ячейки» переключатель переставляют в позицию «Линия» и нажимают «OK»;
подтверждают эти действия тем же способом.

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

Использование специальной функции НД

Теперь, когда вы знаете, как сделать интерполяцию в Excel графическим методом или посредством оператора «ПРЕДСКАЗ», решение многих практических задач для вас не составит большого труда. Однако это еще не все. Табличный процессор от Microsoft представляет возможность найти неизвестное значение функции с помощью функции НД.

Предположим, что график уже построен, на нем уже установлены корректные подписи шкалы. Попробуем ликвидировать разрыв. Для этого:

выделяют в таблице ячейку, в которой отсутствует значение функции;
выбирают значок «Вставить функцию»;
в «Мастере функций» в окошке «Категории» находят строку «Полный алфавитный перечень» (в некоторых версиях процессора «Проверка свойств и значений»);
нажимают на запись «НД» и жмут на кнопку «OK».

Билинейная интерполяция

Круг задач, для решения которых можно использовать моделирование посредством функций одной переменной, достаточно ограничен. Поэтому имеет смысл рассмотреть, как используется формула двойной интерполяции в Excel. Примеры могут быть самыми разными. Например: имеется таблица (см. ниже).

Требуется вычислить давление ветра при величине пролета 300 м на высоте 25 м.

В таблицу добавляют новые записи так, как представлено на рисунке (см. ниже).

Как видно, в нее добавлены ячейки для высоты и пролета в J1 и J2.

Путем обратной последовательной подстановки «собирают» мегаформулу, необходимую для нахождения давления ветра при конкретных параметрах. Для этого:

Использование spline

Предыдущий метод достаточно громоздкий, поэтому в некоторых случаях предпочтительнее интерполяция сплайнами. В Excel ее суть заключается в нахождении интерполирующей функции φ(Х) по формулам одного и того же типа для различных подмножеств аргумента. Далее осуществляется стыковка значений φ(Х) и ее производных на граничных значениях аргументов каждого из подмножеств. В Excel для этих целей предусмотрены специальные функции, а также возможно написание макросов на VBA. Однако они должны создаваться под конкретную задачу, поэтому их изучение в общем виде не имеет смысла.

Теперь вы знаете, как написать формулу двойной интерполяции в Excel корректно или найти неизвестное значение линейной функции посредством встроенных операторов или графика. Надеемся, что эта информация поможет вам в решении множества практических задач.

Читайте также: