Интерполяция графика в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выделяем всю плоскость, на которой размещен график. В появившемся контекстном меню щелкаем по кнопке «Выбрать данные…».

Запускается окно выбора источника данных. В правом блоке «Подписи горизонтальной оси» жмем на кнопку «Изменить».

Открывается небольшое окошко, где нужно указать координаты диапазона, значения из которого будут отображаться на шкале горизонтальной оси. Устанавливаем курсор в поле «Диапазон подписей осей» и просто выделяем соответствующую область на листе, в которой содержаться аргументы функции. Жмем на кнопку «OK».

Теперь нам осталось выполнить основную задачу: с помощью интерполяции устранить разрыв. Вернувшись в окно выбора диапазона данных жмем на кнопку «Скрытые и пустые ячейки», расположенную в нижнем левом углу.

Открывается окно настройки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» выставляем переключатель в позицию «Линию». Жмем на кнопку «OK».

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

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

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

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

Открывается Мастер функций. В категории «Проверка свойств и значений» или «Полный алфавитный перечень» находим и выделяем запись «НД». Жмем на кнопку «OK».

У данной функции нет аргумента, о чем и сообщает появившееся информационное окошко. Чтобы закрыть его просто жмем на кнопку «OK».

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

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

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

Интерполяция графика и табличных данных в Excel

Интерполяция – это своего рода «латание» графиков в тех местах, где возникают обрывы линий из-за отсутствия данных по отдельным показателям. Термин интерполяция подразумевает «латание» внутренних обрывов на графике. А если бы «латались» внешние обрывы, то это была-бы уже экстраполяция графика.

Как построить график с интерполяцией в Excel

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

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

Заполните таблицу как показано на рисунке:

Выделите диапазон A1:B4 и выберите инструмент: «Вставка»-«Диаграммы»-«График»-«График с маркерами».

Чтобы устранить обрывы на графике, то есть выполнить интерполяцию в Excel, можем использовать 2 решения для данной задачи:

1. Изменить параметры в настройках графика выбрав соответствующую опцию.
2. Использовать функцию: =НД() – возвращает значение ошибки #Н/Д.

Оба эти способа рассмотрим далее на конкретных примерах.

1. Сделайте график активным щелкнув по нему левой кнопкой мышки и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».
2. В появившемся диалоговом окне «Выбор источника данных» кликните на кнопку «Скрытые и пустые ячейки»
3. В появившемся диалоговом окне «Настройка скрытых и пустых ячеек» выберите опцию «линию». И нажмите ОК во всех открытых диалоговых окнах.

Как видно на рисунках сразу отображены 2 варианта опций «линию» и «нулевые значения». Обратите внимание, как ведет себя график при выборе каждой из них.

Методы интерполяции табличных данных в Excel

Теперь выполним интерполяцию данных в таблице с помощью функции: =НД(). Для этого нужно предварительно сбросить выше описанные настройки графика, чтобы увидеть как работает данный способ.

Способ 2 . В ячейку B3 введите функцию =НД(). Это автоматически приведет к интерполяции графика как показано на рисунке:

Примечание. Вместо функции =НД() в ячейку можно ввести просто значение: #Н/Д!, результат будет тот же.

Как сделать интерполяцию в Excel (Эксель)

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

В отличии от экстраполяции, в интерполяции число, которое нужно найти, должно быть в пределах рабочих данных. Как пример, если есть числа 10, 15, 25, то для определения числа 11 или 16, можно использовать интерполяцию, если же значение например 26, то нет.

Метод первый. Использование интерполяции для значений в таблице

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

Нужно выбрать ячейку без значения, в которой в конечном итоге мы получим результат. Далее кликнуть в строке формул слева на кнопку «Вставка функции».

В окне мастера функций нужно выбрать категорию «Математические» и в списке функций выбрать ПРЕДСКАЗ, а после этого подтвердить действие.

Откроется окно, в котором нужно задать параметры выбранной нами функции. В поле с названием «Х» нужно ввести искомое число (28). В поле где надо ввести известные значения у вводим диапазон ячеек, где введены значения в таблице. Аналогично делается и для поля значений аргументов Х. После произведенных манипуляций — подтверждаем действие.

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

Метод второй. Интерполяция с помощью настроек графика

Определение с помощью графика функции, если одно из значений не относится к значению функции f(x).
Чтобы сделать график, надо перейти в раздел «Вставка», и выбрать кнопку «Диаграммы» и кликнуть на пункт «График», в котором уже пользователю надо выбрать наиболее оптимальный для него вариант, предварительно выделив таблицу, а точнее табличные значения, на основе которых и будет построен график.

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

Далее нужно выделить область где находится график.А потом нажать в списке на «Выбрать данные».

3 способа расчета полинома в Excel.

Есть 3 способа расчета значений полинома в Excel:

• 1-й способ с помощью графика;
• 2-й способ с помощью функции Excel =ЛИНЕЙН();
• 3-й способ с помощью Forecast4AC PRO;

Подробнее о полиноме и способе его расчета в Excel далее в нашей статье.

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

Что такое полином? Полином — это степенная функция y=ax 2 +bx+c (полином второй степени) и y=ax 3 +bx 2 +cx+d (полином третей степени) и т.д. Степень полинома определяет количество экстремумов (пиков), т.е. максимальных и минимальных значений на анализируемом промежутке времени.

У полинома второй степени y=ax 2 +bx+c один экстремум (на графике ниже 1 максимум).

У Полинома третьей степени y=ax 3 +bx 2 +cx+d может быть один или два экстремума.

Один экстремум

Два экстремума

У Полинома четвертой степени не более трех экстремумов и т.д.

Как рассчитать значения полинома в Excel?

Есть 3 способа расчета значений полинома в Excel:

• 1-й способ с помощью графика;
• 2-й способ с помощью функции Excel =ЛИНЕЙН;
• 3-й способ с помощью Forecast4AC PRO;

1-й способ расчета полинома — с помощью графика

Выделяем ряд со значениями и строим график временного ряда.

На график добавляем полином 6-й степени.

Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»

После этого уравнение выводится на график y = 3,7066x 6 — 234,94x 5 + 4973,6x 4 — 35930x 3 — 7576,8x 2 + 645515x + 5E+06 . Для того чтобы последний коэффициент сделать читаемым, мы зажимаем левую кнопку мыши и выделяем уравнение полинома

Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»

В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».

Получаем уравнение полинома в читаемом формате:

y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35

Из этого уравнения берем коэффициенты a, b, c, d, g, m, v, и вводим в соответствующие ячейки Excel

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение вместо X.

Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

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

2-й способ расчета полинома в Excel — функция ЛИНЕЙН()

Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel =ЛИНЕЙН()

Для расчета коэффициентов в формулу =ЛИНЕЙН(известные значения y, известные значения x, константа, статистика) вводим:

• «известные значения y» (объёмы продаж за периоды),
• «известные значения x» (порядковый номер временного ряда),
• в константу ставим «1»,
• в статистику «0»

Получаем следующего вида формулу:

Теперь, чтобы формула Линейн() рассчитала коэффициенты полинома, нам в неё надо дописать степень полинома, коэффициенты которого мы хотим рассчитать.

Для этого в часть формулы с «известными значениями x» вписываем степень полинома:

• ^ <1:2:3:4:5:6>— для расчета коэффициентов полинома 6-й степени
• ^ <1:2:3:4:5>— для расчета коэффициентов полинома 5-й степени
• ^ <1_2>— для расчета коэффициентов полинома 2-й степени

Получаем формулу следующего вида:

Вводим формулу в ячейку, получаем 3,71 —- значение (a) для полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v

Для того, чтобы Excel рассчитал все 7 коэффициентов полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v, необходимо:

1. Установить курсор в ячейку с формулой и выделить 7 соседних ячеек справа, как на рисунке:

2. Нажать на клавишу F2

3. Затем одновременно — клавиши CTRL + SHIFT + ВВОД (т.е. ввести формулу массива, как это сделать читайте подробно в статье «Как ввести формулу массива»)

Получаем 7 коэффициентов полиномиального тренда 6-й степени.

Рассчитаем значения полиномиального тренда с помощью полученных коэффициентов. Подставляем в уравнение y=3,7* x ^ 6 -234,9* x ^ 5 +4973,5* x ^ 4 -35929,9 * x^3 -7576,7 * x^2 +645514,7* x +4693169,3 номера периодов X, для которых хотим рассчитать значения полинома.

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение полинома вместо X.

Рассчитаем значения полиномиального тренда для каждого периода. Для этого вводим формулу полинома в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

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

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

3-й способ расчета значений полиномиальных трендов — Forecast4AC PRO

Устанавливаем курсор в начало временного ряда

Заходим в настройки Forecast4AC PRO, выбираем «Прогноз с ростом и сезонностью», «Полином 6-й степени», нажимаем кнопку «Рассчитать».

Заходим в лист с пошаговым расчетом «ForPol6», находим строку «Сложившийся тренд»:

Копируем значения в наш лист.

Получаем значения полинома 6-й степени, рассчитанные 3 способами с помощью:

1. Коэффициентов полиномиального тренда выведенных на график;
2. Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
3. и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel .
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.