Интерполяция в Excel
Бывает ситуация, когда в массиве известных значений нужно найти промежуточные результаты. В математике это называется интерполяцией. В Excel данный метод можно применять как для табличных данных, так и для построения графиков. Разберем каждый из этих способов.
Использование интерполяции
Главное условие, при котором можно применять интерполяцию – это то, что искомое значение должно быть внутри массива данных, а не выходить за его предел. Например, если мы имеем набор аргументов 15, 21 и 29, то при нахождении функции для аргумента 25 мы можем использовать интерполяцию. А для поиска соответствующего значения для аргумента 30 – уже нет. В этом и является главное отличие этой процедуры от экстраполяции.
Способ 1: интерполяция для табличных данных
Прежде всего, рассмотрим применения интерполяции для данных, которые расположены в таблице. Для примера возьмем массив аргументов и соответствующих им значений функции, соотношение которых можно описать линейным уравнением. Эти данные размещены в таблице ниже. Нам нужно найти соответствующую функцию для аргумента
- Выделяем любую пустую ячейку на листе, куда пользователь планирует выводить результат от проведенных действий. Далее следует щелкнуть по кнопке «Вставить функцию», которая размещена слева от строки формул.
- Активируется окошко Мастера функций. В категории «Математические» или «Полный алфавитный перечень» ищем наименование «ПРЕДСКАЗ». После того, как соответствующее значение найдено, выделяем его и щелкаем по кнопке «OK».
- Запускается окно аргументов функции ПРЕДСКАЗ. В нем имеется три поля:
- X;
- Известные значения y;
- Известные значения x.
В первое поле нам просто нужно вручную с клавиатуры вбить значения аргумента, функцию которого следует отыскать. В нашем случае это 28.
В поле «Известные значения y» нужно указать координаты диапазона таблицы, в котором содержатся значения функции. Это можно сделать вручную, но гораздо проще и удобнее установить курсор в поле и выделить соответствующую область на листе.
Аналогичным образом устанавливаем в поле «Известные значения x» координаты диапазона с аргументами.
После того, как все нужные данные введены, жмем на кнопку «OK».
- Искомое значение функции будет отображено в той ячейке, которую мы выделили ещё в первом шаге данного способа. В результате получилось число 176. Именно оно и будет итогом проведения процедуры интерполяции.
Урок: Мастер функций в Экселе
Способ 2: интерполяция графика с помощью его настроек
Процедуру интерполяции можно применять и при построении графиков функции. Актуальна она в том случае, если в таблице, на основе которой построен график, к одному из аргументов не указано соответствующее значение функции, как на изображении ниже.
- Выполняем построение графика обычным методом. То есть, находясь во вкладке «Вставка», выделяем табличный диапазон, на основе которого будет проводиться построение. Щелкаем по значку «График», размещенному в блоке инструментов «Диаграммы». Из появившегося списка графиков выбираем тот, который считаем более уместным в данной ситуации.
- Как видим, график построен, но не совсем в таком виде, как нам нужно. Во-первых, он разорван, так как для одного аргумента не нашлась соответствующая функция. Во вторых, на нем присутствует дополнительная линия X, которая в данном случае не нужна, а также на горизонтальной оси указаны просто пункты по порядку, а не значения аргумента. Попробуем исправить все это.
Для начала выделяем сплошную синюю линию, которую нужно удалить и жмем на кнопку Delete на клавиатуре.
- Выделяем всю плоскость, на которой размещен график. В появившемся контекстном меню щелкаем по кнопке «Выбрать данные…».
- Запускается окно выбора источника данных. В правом блоке «Подписи горизонтальной оси» жмем на кнопку «Изменить».
- Открывается небольшое окошко, где нужно указать координаты диапазона, значения из которого будут отображаться на шкале горизонтальной оси. Устанавливаем курсор в поле
- Теперь нам осталось выполнить основную задачу: с помощью интерполяции устранить разрыв. Вернувшись в окно выбора диапазона данных жмем на кнопку «Скрытые и пустые ячейки», расположенную в нижнем левом углу.
- Открывается окно настройки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» выставляем переключатель в позицию «Линию». Жмем на кнопку «OK».
- После возвращения в окно выбора источника подтверждаем все сделанные изменения, щелкнув по кнопке «OK».
Как видим, график скорректирован, а разрыв с помощью интерполяции удален.
Урок: Как построить график в Excel
Способ 3: интерполяция графика с помощью функции
Произвести интерполяцию графика можно также с помощью специальной функции НД. Она возвращает неопределенные значения в указанную ячейку.
- После того, как график построен и отредактирован, так как вам нужно, включая правильную расстановку подписи шкалы, остается только ликвидировать разрыв. Выделяем пустую ячейку в таблице, из которой подтягиваются данные. Жмем на уже знакомый нам значок «Вставить функцию».
- Открывается Мастер функций. В категории «Проверка свойств и значений» или «Полный алфавитный перечень» находим и выделяем запись «НД». Жмем на кнопку «OK».
- У данной функции нет аргумента, о чем и сообщает появившееся информационное окошко. Чтобы закрыть его просто жмем на кнопку «OK».
- После этого действия в выбранной ячейке появилось значение ошибки «#Н/Д», но зато, как можно наблюдать, обрыв графика был автоматически устранен.
Можно сделать даже проще, не запуская Мастер функций, а просто с клавиатуры вбить в пустую ячейку значение «#Н/Д» без кавычек. Но это уже зависит от того, как какому пользователю удобнее.
Как видим, в программе Эксель можно выполнить интерполяцию, как табличных данных, используя функцию ПРЕДСКАЗ, так и графика. В последнем случае это осуществимо с помощью настроек графика или применения функции НД, вызывающей ошибку «#Н/Д». Выбор того, какой именно метод использовать, зависит от постановки задачи, а также от личных предпочтений пользователя.
Мы рады, что смогли помочь Вам в решении проблемы.Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТlumpics.ru
Интерполяция графика и табличных данных в Excel
Интерполяция – это своего рода «латание» графиков в тех местах, где возникают обрывы линий из-за отсутствия данных по отдельным показателям. Термин интерполяция подразумевает «латание» внутренних обрывов на графике. А если бы «латались» внешние обрывы, то это была-бы уже экстраполяция графика.
Как построить график с интерполяцией в Excel
При работе в Excel приходится сталкиваться с интерполяцией графиков различной сложности. Но для первого знакомства с ней рассмотрим сначала самый простой пример.
Заполните таблицу как показано на рисунке:
Выделите диапазон A1:B4 и выберите инструмент: «Вставка»-«Диаграммы»-«График»-«График с маркерами».
Чтобы устранить обрывы на графике, то есть выполнить интерполяцию в Excel, можем использовать 2 решения для данной задачи:
- Изменить параметры в настройках графика выбрав соответствующую опцию.
- Использовать функцию: =НД() – возвращает значение ошибки #Н/Д.
Оба эти способа рассмотрим далее на конкретных примерах.
Способ 1:
- Сделайте график активным щелкнув по нему левой кнопкой мышки и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».
- В появившемся диалоговом окне «Выбор источника данных» кликните на кнопку «Скрытые и пустые ячейки»
- В появившемся диалоговом окне «Настройка скрытых и пустых ячеек» выберите опцию «линию». И нажмите ОК во всех открытых диалоговых окнах.
Как видно на рисунках сразу отображены 2 варианта опций «линию» и «нулевые значения». Обратите внимание, как ведет себя график при выборе каждой из них.
Методы интерполяции табличных данных в Excel
Теперь выполним интерполяцию данных в таблице с помощью функции: =НД(). Для этого нужно предварительно сбросить выше описанные настройки графика, чтобы увидеть как работает данный способ.
Способ 2. В ячейку B3 введите функцию =НД(). Это автоматически приведет к интерполяции графика как показано на рисунке:
Примечание. Вместо функции =НД() в ячейку можно ввести просто значение: #Н/Д!, результат будет тот же.
exceltable.com
Интерполяция промежуточных значений для произвольных данных в Excel
у меня есть таблицы данных, такие как этот пример, девять записей в A1: B9 в этом случае:
A B -- --- 1 2.9 2 5.06 3 7 4 8.84 5 10.87 6 13.24 7 16.22 8 20.25 9 36.7
выше представляет девять измерений нелинейной возрастающей физической переменной в, Например, напряжение, а а представляет собой ровно каждую из девяти круглых минут измерения.
Я хочу создать вторую таблицу, столбцы E и F, с количеством строк, которое является «следующим целым числом» для самого высокого значения в столбце B. В этом случае B9=36.7, то есть 37 рядов. Столбец F1: F37 будет содержать целые числа от 1 до 37, столбец E должен иметь числовые значения, соответствующие F, в той же связи, что и между столбцами A до B. другими словами, интерполируйте значения столбца E, соответствующие значениям столбца F.
например, A3=3 и B3=7. В этом случае F7=7 и E7=3, потому что B уже включил целое число 7 и имеет соответствующее значение в столбце A. Однако F8=8, которое является промежуточным значением, не содержащимся в столбце B. Таким образом, E8 будет лежать между 3 и 4, на основе исходных данных и должны быть интерполированы.
идея заключается в том, что при построении графика A1:B9 будет иметь ту же форму, что и E1: F37. В этом примере я расширю таблицу данных до 37 целочисленных результатов, которые могли бы произойти в ходе исходных измерений, и посмотрю, в какое время (в столбце E с десятичными знаками) эти значения могли бы произойти.
что я пробовал
пытаясь решить эту проблему самостоятельно, я смог найдите трудоемкую формулу (обратите внимание, что в моей попытке мои столбцы E и F перевернуты от того, что я описал выше).
- я создал столбец (K), содержащий разницу между элементами столбца B. K5 = B5-B4. Это смещение Y для каждого приращения X.
- столбец E будет содержать столько последовательных целых чисел (37), начиная с 1, Сколько следующее целое значение самого большого элемента в B. В этом случае B9 содержит 36.7, поэтому 37.
- на F1: F37 я ввожу следующую формулу.
ячейка F1 содержит:
=IF(E1>$B,$A+(E1-$B)/$K,IF(E1>$B,$A+(E1-$B)
/$K,IF(E1>$B,$A+(E1-$B)/$K,IF(E1>$B,$A+(E1-$B)
/$K,IF(E1>$B,$A+(E1-$B)/$K,IF(E1>$B,$A+
(E1-$B)/$K,IF(E1>$B,$A+(E1-$B)/$K,IF(E1>$B,$A+
(E1-$B)/$K,IF(E1>$B,$A+(E1-$B)/$K,E1/$K)))))))))
Он работает довольно хорошо. Но это не автоматическая формула; необходимо ввести столько «IFs», сколько элементов в Столбцах A+B (X+Y). Я тестировал точечные диаграммы с линиями из A1:B9 и E1: F37 (перевернутые для правильной последовательности X/Y), и они генерировали точно такую же форму кривой, поэтому она работает.
но нет эффективного решения потому что оно требует нудного, пользовательские, ручной процесс для каждого набора данных. Я ищу способ сделать это более автоматизированным способом со встроенными в Excel функциями или, по крайней мере, более общим подходом с использованием формул.
kompsekret.ru
Практическая работа № 2 Интерполяция в среде excel, Линия тренда. Задача №1
Цель работы– получить аналитическую зависимость функции от аргумента (задано таблично) в средеExcel.
Последовательность выполнения работы
В Excelесть ряд встроенных утилит, которые можно использовать для решения задач по интерполяции и по аппроксимации зависимостей.
Анализ возможностей Excelв данной области начнем с графических утилит. В частности, исследуем вопрос о добавлении линии тренда, которая строится на основе экспериментальных данных и является аппроксимирующей или интерполяционной функцией, в зависимости от выбора типа кривой. Последовательность выполнения:
1. Отображение анализируемых данных в графическом виде.
2. Построение кривой для рассматриваемой зависимости.
3. Анализ полученной кривой для рассматриваемой зависимости.
4. Выводы о проделанной работе.
Выполнение работы
Р,МПа | ,кг/м3 |
0,392 | 1,925 |
0,412 | 2,024 |
0,431 | 2,123 |
0,450 | 2,222 |
0,470 | 2,321 |
0,490 | 2,421 |
0,539 | 2,673 |
0,588 | 2,926 |
0,637 | 3,182 |
0,686 | 3,440 |
Из этих данных строим график:
«Мастер диаграмм – Точечная – Готово». Выбираем значения «Y» и «X», соответственно для «ρ, кг/м3» и «p, МПа».
Вывод: При выполнении работы по интерполяции в средеEXCELмы по заданным значениям зависимости плотности пара, (кг/м3) от давления р, (МПа) получили аналитическое выражение с помощью линии тренда и построили график линии тренда.
Практическая работа № 3 Интерполяция в среде excel, Линия тренда. Задача №2
Цель работы– получить аналитическую зависимость функции от аргумента (задано графически) в средеExcel.
Последовательность выполнения работы
В Excelесть ряд встроенных утилит, которые можно использовать для решения задач по интерполяции и по аппроксимации зависимостей.
Анализ возможностей Excelв данной области начнем с графических утилит. В частности, исследуем вопрос о добавлении линии тренда, которая строится на основе экспериментальных данных и является аппроксимирующей или интерполяционной функцией, в зависимости от выбора типа кривой. Последовательность выполнения:
1. Отображение анализируемых данных в графическом виде.
2. Построение кривой для рассматриваемой зависимости.
3. Анализ полученной кривой для рассматриваемой зависимости.
4. Выводы о проделанной работе.
Выполнение работы
Для решения поставленной задачи прежде всего следует отобразить анализируемые данные в виде таблицы.
Данные полученные с графика
t, мин | 0 | 0,97 | 1,8 | 2,63 | 3,29 | 3,65 | 4 | 4,48 | 5,27 | 6,42 | … | 16,21 | |||||||||||||
T,˚C | 25,5 | 29,2 | 36,4 | 46,4 | 54,5 | 66,5 | 75,6 | 80 | 88,2 | 91,5 | … | 103,7 | |||||||||||||
Из этих данных строим график:
«Мастер диаграмм – Точечная – Готово». Выбираем значения XиY.
Данные представлены на диаграмме в виде отдельных точек – кривая для рассматриваемой зависимости не проведена. Эти данные в области диаграммы нужно выделить, затем щелкнуть правой кнопкой мыши. В раскрывшемся контекстном меню выбрать команду «Добавить линию тренда».
2. В открывшемся диалоговом окне можно задать тип линии. Выбираем «Полиномиальную», и задаем «Порядок», равный шести. Затем переходим на вкладку «Параметры» и ставим флажки «Отображать уравнение», а также «Поместить на диаграмму величину достоверности аппроксимации (R^2)».
\
3. После нажатия кнопки ОК получаем результат. На рисунке не видно выпадающих экспериментальных точек на линии тренда и высока степень достоверности.
Для построения интерполяционного полинома, проходящего через все экспериментальные точки, необходимо, чтобы степень полинома была на единицу меньше количества точек. Чем выше степень полинома, тем больше аппроксимирующая кривая приближается к интерполяционному полиному. Однако положительный результат может быть получен и при меньшем количестве точек.
Вывод: При построении линии тренда мы получили аналитическое выражение функции от аргумента. В данном случае степень полинома равна шести, а количество точек – восемнадцати. Достоверность аппроксимацииR2= 0,9987 (величина достоверности вполне удовлетворяет).
studfiles.net