Расчет чисел методом интерполяции. Применение экстраполяции в Microsoft Excel

Инструкция

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

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

Интерполяция через алгебраический двучлен, или линейная интерполяция
В общем виде: происходит интерполирование некоторой заданной функции f(х), принимающей значение в точках x0 и x1 отрезка алгебраическим двучленом P1(x) = ax + b. Если же задается более чем два значения функции, то искомая линейная функция заменяется линейно-кусочной функцией, каждая часть функции заключается между двумя заданными значениями функции в этих точках на интерполируемом отрезке.

Интерполирование методом конечных разностей
Данный метод один из простейших и широко распространенных методов осуществления интерполяции. Его суть в замене дифференциальных коэффициентов уравнения на разностные коэффициенты. Это действие позволит перейти к решению дифференциального уравнения путем его разностного аналога, иначе говоря, построить его конечно-разностную схему

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

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

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

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

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

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

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

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

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

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


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

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


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

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

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


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

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

Это глава из книги Билла Джелена .

Задача: некоторые инженерные проблемы проектирования требуют использования таблиц для вычисления значений параметров. Поскольку таблицы являются дискретными, дизайнер использует линейную интерполяцию для получения промежуточного значения параметра. Таблица (рис. 1) включает высоту над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр). Например, если надо найти скорость ветра, соответствующую высоте 47 метров, то следует применить формулу: 130 + (180 – 130) * 7 / (50 – 40) = 165 м/сек.

Скачать заметку в формате или , примеры в формате

Как быть, если существует два управляющих параметра? Можно ли выполнить вычисления с помощью одной формулы? В таблице (рис. 2) показаны значения давления ветра для различных высот и величин пролета конструкций. Требуется вычислить давление ветра на высоте 25 метров и величине пролета 300 метров.

Решение: проблему решаем путем расширения метода, используемого для случая с одним управляющим параметром. Выполните следующие действия.

Начните с таблицы, изображенной на рис. 2. Добавьте исходные ячейки для высоты и пролета в J1 и J2 соответственно (рис. 3).

Рис. 3. Формулы в ячейках J3:J17 объясняют работу мегаформулы

Для удобства использования формул определите имена (рис. 4).

Проследите за работой формулы последовательно переходя от ячейки J3 к ячейке J17.

Путем обратной последовательной подстановки соберите мегаформулу. Скопируйте текст формулы из ячейки J17 в J19. Замените в формуле ссылку на J15 на значение в ячейке J15: J7+(J8-J7)*J11/J13. И так далее. Получится формула, состоящая из 984 символов, которую невозможно воспринять в таком виде. Вы можете посмотреть на нее в приложенном Excel-файле. Не уверен, что такого рода мегаформулы полезны в использовании.

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

Интерполяция. Введение. Общая постановка задачи

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

Таблично заданные в математических моделях функции обычно записываются в таблицы вида:

Y1 (X)

Y(Х0 )

Y(Х1 )

Y(Хn )

Ym (X)

Y(Х0 )

Y(Х1 )

Y(Хn )

Ограниченность информации, представленной такими таблицами, в ряде случаев требует получить значения функций Y j (X) (j=1,2,…,m) в точкахХ , не совпадающих с узловыми точками таблицыХ i (i=0,1,2,…,n) . В таких случаях необходимо определить некоторое аналитическое выражениеφ j (Х) для вычисления приближенных значений исследуемой функцииY j (X) в произвольно задаваемых точкахХ . Функцияφ j (Х) используемая для определения приближенных значений функцииY j (X) называется аппроксимирующей функцией (от латинскогоapproximo - приближаюсь). Близость аппроксимирующей функцииφ j (Х) к аппроксимируемой функцииY j (X) обеспечивается выбором соответствующего алгоритма аппроксимации.

Все дальнейшие рассмотрения и выводы мы будем делать для таблиц, содержащих исходные данные одной исследуемой функции (т. е. для таблиц с m=1 ).

1. Методы интерполяции

1.1 Постановка задачи интерполяции

Наиболее часто для определения функции φ(Х) используется постановка, называемая постановкой задачи интерполяции.

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

ϕ (X i )= Y i (i = 0,1,2,...,n )

Построенная таким образом аппроксимирующая функция φ(Х) позволяет получить достаточно близкое приближение к интерполируемой функцииY(X) в пределах интервала значений аргумента [Х 0 ; Х n ], определяемого таблицей. При задании значений аргументаХ ,не принадлежащих этому интервалу, задача интерполяции преобразуется в задачуэкстраполяции . В этих случаях точность

значений, получаемых при вычислении значений функции φ(Х), зависит от расстояния значения аргументаХ отХ 0 , еслиХ <Х 0 , или отХ n , еслиХ >Х n .

При математическом моделировании интерполирующая функция может быть использована для вычисления приближенных значений исследуемой функции в промежуточных точках подынтервалов [Х i ; Х i+1 ]. Такая процедура называетсяуплотнением таблицы .

Алгоритм интерполяции определяется способом вычисления значений функции φ(Х). Наиболее простым и очевидным вариантом реализации интерполирующей функции является замена исследуемой функцииY(Х) на интервале [Х i ; Х i+1 ] отрезком прямой, соединяющим точкиY i , Y i+1 . Этот метод называется методом линейной интерполяции.

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

При линейной интерполяции значение функции в точке Х , находящейся между узламиХ i иХ i+1 , определяется по формуле прямой, соединяющей две соседние точки таблицы

Y(X) = Y(Xi )+

Y(Xi + 1 )− Y(Xi )

(X − Xi ) (i= 0,1,2, ...,n),

X i+ 1− X i

На рис. 1 приведен пример таблицы, полученной в результате измерений некоторой величины Y(X) . Строки, исходной таблицы выделены заливкой. Справа от таблицы построена точечная диаграмма, соответствующая этой таблице. Уплотнение таблицы выполнено благодаря вычислению по формуле

(3) значений аппроксимируемой функции в точках Х , соответствующих серединам подынтервалов (i=0, 1, 2, … , n ).

Рис.1. Уплотненная таблица функции Y(X) и соответствующая ей диаграмма

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

интерполяции, существенно зависит от характера интерполируемой функции и от расстояния между узлами таблицы X i, , X i+1 .

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

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

1.3 Интерполяция каноническим полиномом

Метод интерполяции функции каноническим полиномом основывается на построении интерполирующей функции как полинома в виде [ 1 ]

ϕ (x) = Pn (x) = c0 + c1 x+ c2 x2 + ... + cn xn

Коэффициенты с i полинома (4) являются свободными параметрами интерполяции, которые определяются из условий Лагранжа:

Pn (xi )= Yi , (i= 0 , 1 , ... , n)

Используя (4) и (5) запишем систему уравнений

C x+ c x2

C xn = Y

C x+ c x2

C xn

C x2

C xn = Y

Вектор решения с i (i = 0, 1, 2, …, n ) системы линейных алгебраических уравнений (6) существует и может быть найден, если среди узловх i нет совпадающих. Определитель системы (6) называется определителем Вандермонда1 и имеет аналитическое выражение [ 2 ].

1 Определителем Вандермонданазывается определитель

Он равен нулю тогда и только тогда, когда xi = xj для некоторых. (Материал из Википедии - свободной энциклопедии)

Для определения значений коэффициентов с i (i = 0, 1, 2, … , n)

уравнений (5) можно записать в векторно-матричной форме

A* C= Y,

где А, матрица коэффициентов, определяемых таблицей степеней вектора аргументовX= (x i 0 , x i , x i 2 , … , x i n ) T (i = 0, 1, 2, … , n)

x0 2

x0 n

xn 2

xn n

С - вектор-столбец коэффициентовс i (i = 0, 1, 2, … , n), аY - вектор-столбец значенийY i (i = 0, 1, 2, … , n) интерполируемой функции в узлах интерполяции.

Решение этой системы линейных алгебраических уравнений может быть получено одним из методов, описанных в [ 3 ]. Например, по формуле

С = A− 1 Y,

где А -1 - матрица обратная матрицеА . Для получения обратной матрицы А -1 можно воспользоваться функциейМОБР() , входящей в набор стандартных функций программы Microsoft Excel.

После того, как будут определены значения коэффициентов с i , используя функцию (4), могут быть вычислены значения интерполируемой функции для любого значения аргументах .

Запишем матрицу А для таблицы, приведенной на рис.1, без учёта строк уплотняющих таблицу.

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

Используя функцию МОБР() , получим матрицу А -1 обратную матрицеА (рис. 3). После чего, по формуле (9) получим вектор коэффициентовС={c 0 , c 1 , c 2 , …, c n } T , приведенный на рис. 4.

Для вычисления значений канонического полинома в ячейку столбца Y канонич , соответствующую значениюх 0 , введем преобразованную к следующему виду формулу, соответствующую нулевой строке системы (6)

=((((c 5

* х 0 +c 4 )*х 0 +c 3 )*х 0 +c 2 )*х 0 +c 1 )*х 0 +c 0

C0 +x *(c1 + x *(c2 + x*(c3 + x*(c4 + x* c5 ))))

Вместо записи " c i " в формуле, вводимой в ячейку таблицы Excel, должна стоять абсолютная ссылка на соответствующую ячейку, содержащую этот коэффициент (см. рис. 4). Вместо "х 0 " - относительная ссылка на ячейку столбцаХ (см. рис. 5).

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

таблицы (см. рис.5).

Рис. 5. Диаграммы, построенные по таблицам линейной и канонической интерполяции

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