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

Содержание

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

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

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

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

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

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

Прежде всего, применим метод экстраполяции к содержимому табличного диапазона. Для примера возьмем таблицу, в которой имеется ряд аргументов (X) от 5 до 50 и ряд соответствующих им значений функции (f(x)). Нам нужно найти значение функции для аргумента 55, который находится за пределом указанного массива данных. Для этих целей используем функцию ПРЕДСКАЗ.

Выделяем ячейку, в которой будет отображаться результат проведенных вычислений. Кликаем по значку «Вставить функцию», который размещен у строки формул.
Запускается окно Мастера функций. Выполняем переход в категорию «Статистические» или «Полный алфавитный перечень». В открывшемся списке производим поиск наименования «ПРЕДСКАЗ». Найдя его, выделяем, а затем щелкаем по кнопке «OK» в нижней части окна.
Мы перемещаемся к окну аргументов вышеуказанной функции. Она имеет всего три аргумента и соответствующее количество полей для их внесения.
В поле «X» следует указать значение аргумента, функцию от которого нам следует вычислить. Можно просто вбить с клавиатуры нужное число, а можно указать координаты ячейки, если аргумент записан на листе. Второй вариант даже предпочтительнее.
Если мы произведем внесение именно таким способом, то для того, чтобы просмотреть значение функции для другого аргумента нам не придется менять формулу, а достаточно будет изменить вводную в соответствующей ячейке.
Для того, чтобы указать координаты этой ячейки, если был выбран все-таки второй вариант, достаточно установить курсор в соответствующее поле и выделить эту ячейку. Её адрес тут же отобразится в окне аргументов.
В поле «Известные значения y» следует указать весь имеющийся у нас диапазон значений функции. Он отображается в колонке «f(x)». Следовательно, устанавливаем курсор в соответствующее поле и выделяем всю эту колонку без её наименования.

В поле «Известные значения x» следует указать все значения аргумента, которым соответствуют внесенные нами выше значения функции. Эти данные находятся в столбце «x». Точно так же, как и в предыдущий раз выделяем нужную нам колонку, предварительно установив курсор в поле окна аргументов.
После того, как все данные внесены, жмем на кнопку «OK».
После этих действий результат вычисления путем экстраполяции будет выведен в ячейку, которая была выделена в первом пункте данной инструкции перед запуском Мастера функций. В данном случае значение функции для аргумента 55 равно 338.
Если все-таки был выбран вариант с добавлением ссылки на ячейку, в которой содержится искомый аргумент, то мы легко сможем его поменять и просмотреть значение функции для любого другого числа. Например, искомое значение для аргумента 85 буде равно 518.

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

Способ 2: экстраполяция для графика

Выполнить процедуру экстраполяции для графика можно путем построения линии тренда.

Прежде всего, строим сам график. Для этого курсором при зажатой левой кнопке мыши выделяем всю область таблицы, включая аргументы и соответствующие значения функции. Затем, переместившись во вкладку «Вставка», кликаем по кнопке «График». Этот значок расположен в блоке «Диаграммы» на ленте инструментов. Появляется перечень доступных вариантов графиков. Выбираем наиболее подходящий из них на свое усмотрение.
После того, как график построен, удаляем из него дополнительную линию аргумента, выделив её и нажав на кнопку Delete на клавиатуре компьютера.
Далее нам нужно поменять деления горизонтальной шкалы, так как в ней отображаются не значения аргументов, как нам того нужно. Для этого, кликаем правой кнопкой мыши по диаграмме и в появившемся списке останавливаемся на значении «Выбрать данные».
В запустившемся окне выбора источника данных кликаем по кнопке «Изменить» в блоке редактирования подписи горизонтальной оси.
Открывается окно установки подписи оси. Ставим курсор в поле данного окна, а затем выделяем все данные столбца «X» без его наименования. Затем жмем на кнопку «OK».
После возврата к окну выбора источника данных повторяем ту же процедуру, то есть, жмем на кнопку «OK».
Теперь наш график подготовлен и можно, непосредственно, приступать к построению линии тренда. Кликаем по графику, после чего на ленте активируется дополнительный набор вкладок – «Работа с диаграммами». Перемещаемся во вкладку «Макет» и жмем на кнопку «Линия тренда» в блоке «Анализ». Кликаем по пункту «Линейное приближение» или «Экспоненциальное приближение».
Линия тренда добавлена, но она полностью находится под линией самого графика, так как мы не указали значение аргумента, к которому она должна стремиться. Чтобы это сделать опять последовательно кликаем по кнопке «Линия тренда», но теперь выбираем пункт «Дополнительные параметры линии тренда».
Запускается окно формата линии тренда. В разделе «Параметры линии тренда» есть блок настроек «Прогноз». Как и в предыдущем способе, давайте для экстраполяции возьмем аргумент 55. Как видим, пока что график имеет длину до аргумента 50 включительно. Получается, нам нужно будет его продлить ещё на 5 единиц. На горизонтальной оси видно, что 5 единиц равно одному делению. Значит это один период. В поле «Вперед на» вписываем значение «1». Жмем на кнопку «Закрыть» в нижнем правом углу окна.
Как видим, график был продлен на указанную длину с помощью линии тренда.

Урок: Как построить линию тренда в Excel

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

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

Источник: //word-office.ru/ekstrapolyaciya-v-excel-kak-sdelat.html

Интерполяция в Excel: особенности, порядок действий и примеры

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

Табличный процeссор Excel позволяeт нe только быстро производить различныe вычислeния, но и рeшать достаточно сложныe задачи.

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

В Excel для этого прeдусмотрeны различныe инструмeнты, пользоваться которыми научит эта статья.

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

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

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

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

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

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

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

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

f(x)

128

158

188

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

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

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

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

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

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

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

В частности, можeт быть примeнeн графичeский мeтод. Он можeт оказаться полeзным, eсли в таблицe к одному из аргумeнтов нe указано соотвeтствующee значeниe функции, как в той, что прeдставлeна нижe (см.

ячeйку с адрeсом B9).

f(x)

128

158

188

218

278

308

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

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

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

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

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

Затeм:

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

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

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

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

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

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

Послe этого в ячeйкe B9 появляeтся значeниe ошибки «#Н/Д». Однако обрыв графика автоматичeски устраняeтся.

Вы можeтe поступить дажe прощe: внeсти с клавиатуры в ячeйку B9 символы «#Н/Д» (бeз кавычeк).

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

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

200

400

600

800

1000

Пролeт

160

210

260

190

240

290

130

180

230

280

330

180

230

280

330

380

240

290

340

390

440

310

360

410

460

510

390

440

490

540

590

750

800

850

900

950

Высота

278

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

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

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

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

копируют тeкст формулы из ячeйки с адрeсом J17 в ячeйку J19;
замeняют в формулe ссылку на J15 значeниeм в ячeйкe J15: J7+(J8-J7)*J11/J13;
повторяют эти дeйствия до получeния нeобходимой формулы.

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

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

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

Однако они должны создаваться под конкрeтную задачу, поэтому их изучeниe в общeм видe нe имeeт смысла.

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

Источник: //xroom.su/1727-interpoliaciia-v-excel-osobennosti-poriadok-deistvii-i-primery

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

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

Скачать последнюю версию Excel

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

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

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

В этом и является главное отличие этой процедуры от экстраполяции.

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

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

Выделяем любую пустую ячейку на листе, куда пользователь планирует выводить результат от проведенных действий. Далее следует щелкнуть по кнопке «Вставить функцию», которая размещена слева от строки формул.
Активируется окошко Мастера функций. В категории «Математические» или «Полный алфавитный перечень» ищем наименование «ПРЕДСКАЗ». После того, как соответствующее значение найдено, выделяем его и щелкаем по кнопке «OK».
Запускается окно аргументов функции ПРЕДСКАЗ. В нем имеется три поля:
- X;
- Известные значения y;
- Известные значения x.
В первое поле нам просто нужно вручную с клавиатуры вбить значения аргумента, функцию которого следует отыскать. В нашем случае это 28.
В поле «Известные значения y» нужно указать координаты диапазона таблицы, в котором содержатся значения функции. Это можно сделать вручную, но гораздо проще и удобнее установить курсор в поле и выделить соответствующую область на листе.
Аналогичным образом устанавливаем в поле «Известные значения x» координаты диапазона с аргументами.
После того, как все нужные данные введены, жмем на кнопку «OK».
Искомое значение функции будет отображено в той ячейке, которую мы выделили ещё в первом шаге данного способа. В результате получилось число 176. Именно оно и будет итогом проведения процедуры интерполяции.

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

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

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

Выполняем построение графика обычным методом. То есть, находясь во вкладке «Вставка», выделяем табличный диапазон, на основе которого будет проводиться построение. Щелкаем по значку «График», размещенному в блоке инструментов «Диаграммы». Из появившегося списка графиков выбираем тот, который считаем более уместным в данной ситуации.
Как видим, график построен, но не совсем в таком виде, как нам нужно. Во-первых, он разорван, так как для одного аргумента не нашлась соответствующая функция. Во вторых, на нем присутствует дополнительная линия X, которая в данном случае не нужна, а также на горизонтальной оси указаны просто пункты по порядку, а не значения аргумента. Попробуем исправить все это.
Для начала выделяем сплошную синюю линию, которую нужно удалить и жмем на кнопку Delete на клавиатуре.
Выделяем всю плоскость, на которой размещен график. В появившемся контекстном меню щелкаем по кнопке «Выбрать данные…».
Запускается окно выбора источника данных. В правом блоке «Подписи горизонтальной оси» жмем на кнопку «Изменить».
Открывается небольшое окошко, где нужно указать координаты диапазона, значения из которого будут отображаться на шкале горизонтальной оси. Устанавливаем курсор в поле «Диапазон подписей осей» и просто выделяем соответствующую область на листе, в которой содержаться аргументы функции. Жмем на кнопку «OK».
Теперь нам осталось выполнить основную задачу: с помощью интерполяции устранить разрыв. Вернувшись в окно выбора диапазона данных жмем на кнопку «Скрытые и пустые ячейки», расположенную в нижнем левом углу.
Открывается окно настройки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» выставляем переключатель в позицию «Линию». Жмем на кнопку «OK».
После возвращения в окно выбора источника подтверждаем все сделанные изменения, щелкнув по кнопке «OK».

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

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

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

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

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

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

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

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

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

Помогла ли вам эта статья?

ДА НЕТ

Источник: //lumpics.ru/interpolation-in-excel/

Интерполяция данных: соединяем точки так, чтобы было красиво

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

Как построить график по n точкам? Самое простое — отметить их маркерами на координатной сетке. Однако для наглядности их хочется соединить, чтобы получить легко читаемую линию. Соединять точки проще всего отрезками прямых.

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

Однако тут нужно быть осторожным, чтобы не получилось вот такого:

Немного матчасти

Восстановление промежуточных значений функции, которая в данном случае задана таблично в виде точек P1 … Pn, называется интерполяцией. Есть множество способов интерполяции, но все они могут быть сведены к тому, что надо найти n – 1 функцию для расчёта промежуточных точек на соответствующих сегментах.

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

Функции fi могут быть самыми разными, но чаще всего используют полиномы некоторой степени.

В этом случае итоговая интерполирующая функция (кусочно заданная на промежутках, ограниченных точками Pi) называется сплайном.

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

Ставим опыты

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

Давайте добавим немного конкретики. Вот набор точек (взяты почти с потолка):

0 020 045 -4753 33557 2662 38774 10489 095 100100 0 Результат линейной интерполяции этих точек выглядит так: Однако, как отмечалось выше, иногда хочется получить в итоге гладкую кривую.

Что есть гладкость? Бытовой ответ: отсутствие острых углов. Математический: непрерывность производных.

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

То есть, если функция имеет гладкость порядка 1 на отрезке [a; b], это означает, что на [a; b] она имеет непрерывную первую производную, а вот вторая производная уже терпит разрыв в каких-то точках.
У сплайна в контексте гладкости есть понятие дефекта. Дефект сплайна — это разность между его степенью и его гладкостью. Степень сплайна — это максимальная степень использованных в нём полиномов.

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

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

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

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

Другое традиционное решение, кроме кубических сплайнов дефекта 1 — полиномы Лагранжа. Это полиномы степени n – 1, принимающие заданные значения в заданных точках. То есть членения на сегменты здесь не происходит, вся последовательность описывается одним полиномом.

Но вот что получается: Гладкость, конечно, присутствует, но наглядность пострадала так сильно, что… пожалуй, стоит поискать другие методы. На некоторых наборах данных результат выходит нормальный, но в общем случае ошибка относительно линейной интерполяции (и, соответственно, ложные экстремумы) может получаться слишком большой — из-за того, что тут всего один полином на все сегменты.

В компьютерной графике очень широко применяются кривые Безье, представленные полиномами k-й степени.

Они не являются интерполирующими, так как из k + 1 точек, участвующих в построении, итоговая кривая проходит лишь через первую и последнюю. Остальные k – 1 точек играют роль своего рода «гравитационных центров», притягивающих к себе кривую. Вот пример кубической кривой Безье:

Как это можно использовать для интерполяции? На основе этих кривых тоже можно построить сплайн. То есть на каждом сегменте сплайна будет своя кривая Безье k-й степени (кстати, k = 1 даёт линейную интерполяцию). И вопрос только в том, какое k взять и как найти k – 1 промежуточную точку.

Здесь бесконечно много вариантов (поскольку k ничем не ограничено), однако мы рассмотрим классический: k = 3.
Чтобы итоговая кривая была гладкой, нужно добиться дефекта 1 для составляемого сплайна, то есть сохранения непрерывности первой и второй производных в точках сочленения сегментов (Pi), как это делается в классическом варианте кубического сплайна.
Решение этой задачи подробно (с исходным кодом) рассмотрено здесь. Вот что получится на нашем тестовом наборе: Стало лучше: ложные экстремумы всё ещё есть, но хотя бы не так сильно отличаются от реальных.

Думаем и экспериментируем

Можно попробовать ослабить условие гладкости: потребовать дефект 2, а не 1, то есть сохранить непрерывность одной только первой производной.

Достаточное условие достижения дефекта 2 в том, что промежуточные контрольные точки кубической кривой Безье, смежные с заданной точкой интерполируемой последовательности, лежат с этой точкой на одной прямой и на одинаковом расстоянии:

В качестве прямых, на которых лежат точки Ci – 1(2), Pi и Ci(1), целесообразно взять касательные к графику интерполируемой функции в точках Pi. Это гарантирует отсутствие ложных экстремумов, так как кривая Безье оказывается ограниченной ломаной, построенной на её контрольных точках (если эта ломаная не имеет самопересечений).

Методом проб и ошибок эвристика для расчёта расстояния от точки интерполируемой последовательности до промежуточной контрольной получилась такой:Эвристика 1

Первая и последняя промежуточные контрольные точки равны первой и последней точке графика соответственно (точки C1(1) и Cn – 1(2) совпадают с точками P1 и Pn соответственно). В этом случае получается вот такая кривая: Как видно, ложных экстремумов уже нет. Однако если сравнивать с линейной интерполяцией, местами ошибка очень большая. Можно сделать её ещё меньше, но тут в ход пойдут ещё более хитрые эвристики. К текущему варианту мы пришли, уменьшив гладкость на один порядок. Можно сделать это ещё раз: пусть сплайн будет иметь дефект 3. По факту, тем самым формально функция не будет гладкой вообще: даже первая производная может терпеть разрывы. Но если рвать её аккуратно, визуально ничего страшного не произойдёт.

Отказываемся от требования равенства расстояний от точки Pi до точек Ci – 1(2) и Ci(1), но при этом сохраняем их все лежащими на одной прямой:

Эвристика для вычисления расстояний будет такой:Эвристика 2Расчёт l1 и l2 такой же, как в «эвристике 1».
При этом, однако, стоит ещё проверять, не совпали ли точки Pi и Pi + 1 по ординате, и, если совпали, полагать l1 = l2 = 0. Это защитит от «вспухания» графика на плоских отрезках (что тоже немаловажно с точки зрения правдивого отображения данных).
Результат получается такой: В результате на шестом сегменте ошибка уменьшилась, а на седьмом — увеличилась: кривизна у Безье на нём оказалась больше, чем хотелось бы. Исправить ситуацию можно, принудительно уменьшив кривизну и тем самым «прижав» Безье ближе к отрезку прямой, которая соединяет граничные точки сегмента. Для этого используется следующая эвристика:Эвристика 3Если абсцисса точки пересечения касательных в точках Pi(xi, yi) и Pi + 1(xi + 1, yi + 1) лежит в отрезке [xi; xi + 1], то l1 либо l2 полагаем равным нулю. В том случае, если касательная в точке Pi направлена вверх, нулю полагаем максимальное из l1 и l2, если вниз — минимальное.
Результат следующий: На этом было принято решение признать цель достигнутой.

Может быть, кому-то пригодится код.

А как люди-то делают?

Обещанный обзор. Конечно, перед решением задачи мы посмотрели, кто чем может похвастаться, а уже потом начали разбираться, как сделать самим и по возможности лучше. Но вот как только сделали, не без удовольствия ещё раз прошлись по доступным инструментам и сравнили их результаты с плодами наших экспериментов. Итак, поехали.

MS Excel

Это очень похоже на рассмотренный выше сплайн дефекта 1, основанный на кривых Безье. Правда, в отличие от него в чистом виде, тут всего два ложных экстремума — первый и второй сегменты (у нас было четыре). Видимо, к классическому поиску промежуточных контрольных точек тут добавляются ещё какие-то эвристики. Но ото всех ложных экстремумов они не спасли.

LibreOffice Calc

В настройках это названо кубическим сплайном. Очевидно, он тоже основан на Безье, и вот тут уже точная копия нашего результата: все четыре ложных экстремума на месте. Есть там ещё один тип интерполяции, который мы тут не рассматривали: B-сплайн. Но для нашей задачи он явно не подходит, так как даёт вот такой результат 🙂

Highcharts, одна из самых популярных JS-библиотек для построения диаграмм

Тут налицо «метод касательных» в варианте равенства расстояний от точки интерполируемой последовательности до промежуточных контрольных. Ложных экстремумов нет, зато есть сравнительно большая ошибка относительно линейной интерполяции (седьмой сегмент).

amCharts, ещё одна популярная JS-библиотека

Картина очень похожа на экселевскую, те же два ложных экстремума в тех же местах.

Coreplot, самая популярная библиотека построения графиков для iOS и OS X

Есть ложные экстремумы и видно, что используется сплайн дефекта 1 на основе Безье. Библиотека открытая, так что можно посмотреть в код и убедиться в этом.

aChartEngine, вроде как самая популярная библиотека построения графиков для Android

Больше всего похоже на кривую Безье степени n – 1, хотя в самой библиотеке график называется «cubic line». Странно! Как бы то ни было, тут не только присутствуют ложные экстремумы, но и в принципе не выполняются условия интерполяции.

Вместо заключения

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

Вообще, заняться этим пришлось по просьбе покупателей, которые зарепортили нам «острые углы» в качестве бага в нашем движке диаграмм. Будем рады, если описанный опыт кому-то пригодится.

графики и диаграммы
визуализация данных
интерполяция

Источник: //habr.com/post/264191/

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

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

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

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

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

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

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

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

A	B	C	D	E
1	x	f(x)
2	5	38
3	10	68
4	15	98
5	20	128
6	25	158
7	30	188

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

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

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

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

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

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

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

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

ячейку с адресом B9).

A	B	C	D	E
1	x	f(x)
2	5	38
3	10	68
4	15	98
5	20	128
6	25	158
7	30	188
8	35	218
9	40
10	45	278
11	50	308

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

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

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

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

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

Затем:

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

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

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

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

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

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

После этого в ячейке B9 появляется значение ошибки «#Н/Д». Однако обрыв графика автоматически устраняется.

Вы можете поступить даже проще: внести с клавиатуры в ячейку B9 символы «#Н/Д» (без кавычек).

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

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

A	B	C	D	E	F	G
1	200	400	600	800	1000	Пролет
2	20	10	20	160	210	260
3	30	40	60	190	240	290
4	40	130	180	230	280	330
5	50	180	230	280	330	380
6	60	240	290	340	390	440
7	70	310	360	410	460	510
8	80	390	440	490	540	590
9	90	750	800	850	900	950
10	Высота	278

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

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

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

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

копируют текст формулы из ячейки с адресом J17 в ячейку J19;
заменяют в формуле ссылку на J15 значением в ячейке J15: J7+(J8-J7)*J11/J13;
повторяют эти действия до получения необходимой формулы.

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

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

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

Однако они должны создаваться под конкретную задачу, поэтому их изучение в общем виде не имеет смысла.

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

Источник: //fb.ru/article/321859/interpolyatsiya-v-excel-osobennosti-poryadok-deystviy-i-primeryi