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