Ситуация, когда нужно найти промежуточные результаты в ряду известных значений. В математике это называется интерполяцией. В Excel этот метод можно использовать как для табличных данных, так и для построения графиков. Давайте рассмотрим каждый из этих методов.
Использование интерполяции
Главное условие интерполяции — искомое значение должно находиться в пределах матрицы данных, а не выходить за пределы. Например, если у нас есть набор аргументов 15, 21 и 29, мы можем использовать интерполяцию при нахождении функции для аргумента 25. А чтобы найти соответствующее значение для аргумента 30 — не более. В этом основное отличие данной процедуры от экстраполяции.
Способ 1: интерполяция для табличных данных
Прежде всего, рассмотрим использование интерполяции для данных, помещенных в таблицу. Например, возьмем ряд аргументов и соответствующих им значений функций, где отношение может быть описано линейным уравнением. Эти данные размещены в таблице ниже. Нам нужно найти соответствующую функцию для аргумента 28. Проще всего это сделать с помощью оператора FORECAST.
- Активируется окно мастера функций. В категории «Математика» или «Полный алфавитный список» ищем название «ПРОГНОЗ». После того, как соответствующее значение будет найдено, выберите его и нажмите кнопку «ОК».
- Откроется окно Аргументы функции FORECAST. Он имеет три поля:
- ИКС;
- Известные значения y;
- Известные значения x.
В первое поле нам нужно только вручную ввести с клавиатуры значения аргумента, функцию которого нужно найти. В нашем случае это 28.
В поле «Известные y-значения» необходимо указать координаты области таблицы, в которой находятся значения функции. Это можно сделать вручную, но гораздо проще и удобнее поставить курсор в поле и выбрать соответствующую область на листе.
Таким же образом заносим координаты области с аргументами в поле «Известные x-значения.
После того, как вся необходимая информация введена, нажмите кнопку «ОК».
- Искомое значение функции отображается в ячейке, которую мы выбрали на первом шаге этого метода. В результате получится число 176. Это и будет результат процедуры интерполяции.
Урок: Мастер функций в Excel
Способ 2: интерполяция графика с помощью его настроек
Процедуру интерполяции также можно использовать при построении графика функции. Актуально, если в таблице, на которой построен график, не указано соответствующее значение функции для одного из аргументов, как на изображении ниже.
- Как видите, график построен, но не совсем в том виде, который нам нужен. Во-первых, он не работает, потому что не существует эквивалентной функции для одного аргумента. Во-вторых, на нем есть лишняя линия X, которая в данном случае не нужна, а также горизонтальная ось показывает только точки по порядку, а не значения аргументов. Попробуем все исправить.
Сначала выберите сплошную синюю линию, которую хотите удалить, и нажмите кнопку «Удалить» на клавиатуре.
- Выделите всю плоскость, на которой расположен график. В появившемся контекстном меню нажмите на кнопку «Выбрать данные…».
- Откроется окно выбора источника данных. Нажмите кнопку «Изменить» в правом блоке «Метки горизонтальной оси».
- Откроется небольшое окно, где вам предстоит указать координаты местности, значения которых будут отображаться на шкале горизонтальной оси. Ставим курсор в поле «Диапазон меток осей» и просто выбираем соответствующую область листа, содержащую аргументы функции. Нажмите на кнопку «ОК».
- Теперь нам осталось выполнить основную задачу: устранить зазор с помощью интерполяции. Вернитесь в окно выбора диапазона данных, нажмите кнопку «Скрытые и пустые ячейки» в левом нижнем углу.
- Откроется окно для установки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» установите переключатель в положение «Линия». Нажмите на кнопку «ОК».
- Вернувшись в окно выбора источника, подтвердите все сделанные изменения, нажав кнопку «ОК».
Как видите, график был скорректирован, а разрыв устранен с помощью интерполяции.
Урок: Как построить график в Excel
Способ 3: интерполяция графика с помощью функции
Вы также можете интерполировать график, используя специальную функцию ND. Он возвращает нулевые значения в указанной ячейке.
- Откроется мастер функций. В категории «Проверка свойств и значений» или «Полный алфавитный список» находим и выбираем запись «НД». Нажмите на кнопку «ОК».
- Эта функция не имеет аргументов, о чем свидетельствует появившееся информационное окно. Чтобы закрыть его, достаточно нажать на кнопку «ОК».
- После этого действия в выбранной ячейке появилось значение ошибки "#Н/Д", но, как видите, обрыв графика был автоматически устранен.
Можно сделать еще проще, не запуская мастер функций, а просто ввести с клавиатуры значение "#Н/Д" без кавычек в пустую ячейку. Но это уже зависит от того, как кому удобнее.
Как видите, в программе Excel можно интерполировать как табличные данные с помощью функции ПРОГНОЗ, так и графики. В последнем случае это можно сделать с помощью настроек карты или с помощью функции ND, которая вызывает ошибку «#Н/Д». Выбор метода зависит от постановки задачи, а также от личных предпочтений пользователя.
