Lumpics lumpics.ru

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

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

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

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

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

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

В таблице нет значения функции в Microsoft Excel
  1. Выделяем любую пустую ячейку на листе, куда пользователь планирует выводить результат от проведенных действий. Далее следует щелкнуть по кнопке «Вставить функцию», которая размещена слева от строки формул.
  2. Переход в Мастер функций в Microsoft Excel
  3. Активируется окошко Мастера функций. В категории «Математические» или «Полный алфавитный перечень» ищем наименование «ПРЕДСКАЗ». После того, как соответствующее значение найдено, выделяем его и щелкаем по кнопке «OK».
  4. Переход к аргументам функции ПРЕДСКАЗ в Microsoft Excel
  5. Запускается окно аргументов функции ПРЕДСКАЗ. В нем имеется три поля:
    • X;
    • Известные значения y;
    • Известные значения x.

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

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

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

    После того, как все нужные данные введены, жмем на кнопку «OK».

  6. Аргументы функции ПРЕДСКАЗ в Microsoft Excel
  7. Искомое значение функции будет отображено в той ячейке, которую мы выделили ещё в первом шаге данного способа. В результате получилось число 176. Именно оно и будет итогом проведения процедуры интерполяции.
Результат вычисления функции ПРЕДСКАЗ в Microsoft Excel

Урок: Мастер функций в Экселе

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

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

  1. Выполняем построение графика обычным методом. То есть, находясь во вкладке «Вставка», выделяем табличный диапазон, на основе которого будет проводиться построение. Щелкаем по значку «График», размещенному в блоке инструментов «Диаграммы». Из появившегося списка графиков выбираем тот, который считаем более уместным в данной ситуации.
  2. Переход к построению графика в Microsoft Excel
  3. Как видим, график построен, но не совсем в таком виде, как нам нужно. Во-первых, он разорван, так как для одного аргумента не нашлась соответствующая функция. Во вторых, на нем присутствует дополнительная линия X, которая в данном случае не нужна, а также на горизонтальной оси указаны просто пункты по порядку, а не значения аргумента. Попробуем исправить все это.

    Для начала выделяем сплошную синюю линию, которую нужно удалить и жмем на кнопку Delete на клавиатуре.

  4. Удаление линии в Microsoft Excel
  5. Выделяем всю плоскость, на которой размещен график. В появившемся контекстном меню щелкаем по кнопке «Выбрать данные…».
  6. Переход к выбору данных в Microsoft Excel
  7. Запускается окно выбора источника данных. В правом блоке «Подписи горизонтальной оси» жмем на кнопку «Изменить».
  8. Окно выбора источника данных в Microsoft Excel
  9. Открывается небольшое окошко, где нужно указать координаты диапазона, значения из которого будут отображаться на шкале горизонтальной оси. Устанавливаем курсор в поле «Диапазон подписей осей» и просто выделяем соответствующую область на листе, в которой содержаться аргументы функции. Жмем на кнопку «OK».
  10. Изменение шкалы оси в Microsoft Excel
  11. Теперь нам осталось выполнить основную задачу: с помощью интерполяции устранить разрыв. Вернувшись в окно выбора диапазона данных жмем на кнопку «Скрытые и пустые ячейки», расположенную в нижнем левом углу.
  12. Переход к скрытым и пустым ячейкам в Microsoft Excel
  13. Открывается окно настройки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» выставляем переключатель в позицию «Линию». Жмем на кнопку «OK».
  14. Настройка скрытых и пустых ячеек в Microsoft Excel
  15. После возвращения в окно выбора источника подтверждаем все сделанные изменения, щелкнув по кнопке «OK».
Подтверждение изменений в Microsoft Excel

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

График скорректирован в Microsoft Excel

Урок: Как построить график в Excel

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

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

  1. После того, как график построен и отредактирован, так как вам нужно, включая правильную расстановку подписи шкалы, остается только ликвидировать разрыв. Выделяем пустую ячейку в таблице, из которой подтягиваются данные. Жмем на уже знакомый нам значок «Вставить функцию».
  2. Перемещение в Мастер функций в программе Microsoft Excel
  3. Открывается Мастер функций. В категории «Проверка свойств и значений» или «Полный алфавитный перечень» находим и выделяем запись «НД». Жмем на кнопку «OK».
  4. Мастер функций в Microsoft Excel
  5. У данной функции нет аргумента, о чем и сообщает появившееся информационное окошко. Чтобы закрыть его просто жмем на кнопку «OK».
  6. Информационное окно в Microsoft Excel
  7. После этого действия в выбранной ячейке появилось значение ошибки «#Н/Д», но зато, как можно наблюдать, обрыв графика был автоматически устранен.
Результат обработки функцией НД в Microsoft Excel

Можно сделать даже проще, не запуская Мастер функций, а просто с клавиатуры вбить в пустую ячейку значение «#Н/Д» без кавычек. Но это уже зависит от того, как какому пользователю удобнее.

НД вставлена как значение в Microsoft Excel

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

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

15 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Сергей
15 декабря 2020 14:48

не работает первый способ! не вводите людей в заблуждение

Аноним
7 марта 2020 17:40

Первый способ не работает!

Аноним
23 февраля 2021 11:01

Первый способ , применим только для линейной зависимости. При любом криволинейном графике — не верный.

Аноним
28 июня 2019 16:56

предсказ не правильно считает, не линейная интерполяция

yo
9 октября 2019 09:20

Фигня написана, не работает корректно.

Алексей Капля
8 мая 2018 14:54

Есть 3 значения у для трех значений х.
Монтажное натяжение T, т.
t=-40⁰C t=-0⁰C t=-+40⁰C
24,60 19,50 14,75
20,50 17,17 14,65
19,00 17,17 15,57
16,50 14,88 13,70
16,75 15,42 14,69
Необходимо определить значения монтажных натяжений для температуры с шагом 1 градус.

Есть ли возможность заполнить эту таблиу автоматически, а не вычислять каждое значение в отдельности?

Костя
9 декабря 2018 15:02
Ответить на  Алексей Капля

Это уже двойная интерполяция.

Проектировщик
11 мая 2020 15:21

Вы такое хотите — в экселе https://yadi.sk/i/5U6KG-3tFAKYRQ в опен офисе — https://yadi.sk/i/SjWv5yDZuyhRyQ
слеплено из 20 формул в одну =)

2020-05-11_15-19-55
Гришин Илья
7 октября 2021 09:35

Вариантов интерполяций великое множество… выбирай на свой вкус и цвет.

comment_image_reloaded_57569760
Аноним
19 января 2022 18:10

Первый способ не подходит для линейной интерполяции.

Екатерина
28 сентября 2019 15:57

Первый способ НЕПРАВИЛЬНЫЙ.
Скажите, как может между 128 и 158 быть 176?
Прежде чем публиковать, проанализировали бы

Аноним
2 мая 2020 13:31

Меня интересует не значение, а интерполяционная функция. Как её получить по данным таблицы?

Аноним
17 августа 2021 08:55

выводит ошибку «деление на ноль», хотя таковых значений аргументов нет в массиве

Аноним
3 августа 2023 11:40

Если бы кто-то в первом примере еще объяснил откуда взялся аргумент 28, было бы вообще чудесно. цифры 28 нет ни на одном скриншоте.

Задать вопрос