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

Обновлено: 06.07.2024

И мне нужно найти значение в (-16, 37). Я пробовал использовать линейную интерполяцию, но могу решить только с парой значений (x, y). Не могли бы вы помочь мне?

Большое спасибо Андреа

Спасибо всем, ваши предложения очень помогли мне лучше понять проблему.

Вначале я неправильно сформулировал проблему, потому что опирался на графическое представление данных, матрицу. Но после некоторого "пробормотания" :) Я понял, что правильный способ представить проблему был:

Так, т.е. f (35, -15) = 15.0

Чтобы получить значение в x1 = 37 и x2 = -13, я могу использовать билинейную интерполяцию. Графическое представление задачи в

Была моя вспышка вдохновения.

Вы можете попробовать подогнать под свои данные самолет:

Затем решите для A, B, C, используя метод наименьших квадратов и 6 баллов (-15,35,15,0), . Excel может найти оптимальные значения для вас.

Изменить

Здесь я вставил ваши данные (зеленые ячейки) и использовал инструмент решателя для построения оптимальной линейной модели z = A x + B y + C. (x равно 35,40 и y равно -15, -10, -5). A, B, C - синие ячейки слева. Тогда розовые ячейки - это A x + B y + C, а красные ячейки - (date-model) ^ 2. Ошибка - это сумма всех этих 6 красных ячеек.

Затем используйте инструмент Solver (его необходимо активировать из параметров Excel). Определите ячейку ошибки как целевую (минимизацию) и A, B, C как ячейки переменных. Он найдет лучшие значения A, B, C.

Эти значения затем применяются к вашему запросу (x = 37 и y = -16), что дает результат 14,5.

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

Чтобы делать что-либо с матрицей в excel, вам нужно использовать формулы массива. Когда вы закончите вводить формулу, не нажимайте Enter. Вместо этого нажмите Ctrl + Shift + Enter. Если вы все сделали правильно, вы увидите формулу, которую вы ввели, обведенную знаком <> в строке формул.

Если вы считаете, что ваши данные линейны, и хотите попробовать подход, обозначенный @ tom10, попробуйте

Что вернет массив новых y для каждого нового предоставленного x.

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

Линейная интерполяция найдет записи в указанных пределах. т.е. -5> = х> = -15. Чтобы найти значение -16, вам нужно будет экстраполировать.

Один из способов - подобрать поверхность к точкам, например, решить Ax = b) или сказать, что это то же самое, что и (-15, 37) - это зависит от ваших потребностей и выбора правильного метода, который на самом деле не является тем, о чем этот сайт.

Если вы знаете метод, мы можем рассказать вам, как его рассчитать, но вы должны показать, что вы пробовали, показав свой код.

Вы можете сделать разумную оценку, рассматривая три независимых линейных задачи:

При постоянном значении x (скажем, 35) используйте линейную аппроксимацию для одномерных данных, чтобы найти значение z для вашего целевого значения y (-16), используя три значения y и три значения z. Назовите это Z ₀ .
То же, что 1., но теперь для другого значения x (40). Назовите это Z ₁ .
Теперь выполните подгонку в другом направлении . при y = -16 используйте (35, Z ₀ ) и (40, Z ₁ ), чтобы найти Z -значение в целевом значении x.

Это приблизительное и не лучшее решение, но я сомневаюсь, что Excel выполняет многомерный анализ, необходимый для правильного решения проблемы - где бы вы поместили поверхность в эти точки. Я плохо знаю Excel и, возможно, сомневаюсь в этом по незнанию.

Андреа, если вы хотите использовать кусочную билинейную интерполяцию в 2D в Excel, могу ли я порекомендовать функцию VBA здесь. В прошлом у меня это хорошо сработало.

Функцию Interp2dTab в xongrid можно использовать для билинейной интерполяции

Здравствуйте! Не получается сделать простую линейную интерполяцию
Готовая формула ПРЕДСКАЗ не помогает, она почему-то выдает вобще странные цифры
Что мне надо. Например, есть две два столбца с цифрами
1 2
2 5
3 8
нужно узнать значение из второго столбца для 2,5,
бывает и так, что искомая цифра равна цифре первого столбца- 2, в таком случае должно выводить 5
Педсказ же и интерполирует не правильно, и даже если ввести уже существующую цифру (ну возьмем снова 2), то выдает не 5,а ерунду.При этом везде в инете пишут именно про эту функцию. Может есть какая-то другая?

Выполнить линейную интерполяцию
Функция yi=f(xi) i=1,n, задана таблично в узлах xi . Выполнить линейную интерполяцию данной.

Как получить промежуточное значение используя линейную интерполяцию?
Программирую ардуино, нужно точно мерять температуру термистором. Есть таблица показаний.

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

Определить значения функции в некоторой промежуточной точке хk, используя линейную интерполяцию
Помогите пожалуйста, уже 2 недели бьюсь с кодом и все никак не выходит: Функция f(x) представлена.

' width='8' height='8' /> Excel for HVAC

Данный раздел создан по просьбам участников Форума для сбора на сайте коллекции таблиц Excel на русском языке для расчётов по разным специальностям.

При присоединении файла, пожалуйста, указывайте следующую информацию:
Автор (Информация об авторе.)
Размер файла (максимальный размер присоединяемого файла 15 Мб)
Описание

Предлагаю лёгкую табличку по интерполяции в Excel.
На разных закладках: Экстраполяция и Интерполяция 2-х и 4-х значений.
Пользование понятно без справки, главное не вводить числа в ячейки формул.
Разработка далёкого 1999 года, ещё при старом режиме. Всего 30К.
Есть ещё таблица, передаёт данные в интерполяцию и забирает результаты.
Если у кого есть нечто подобное или усовершенствуете - сдавайте сюда.

В табличке линейная интерполяция и экстраполяция. Бывает, что для инженера нужна и квадратичная интерполяция.

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

Квадратичная интерполяция подразумевает составление квадратного уравнения для поиска значения в промежуточной точке. Для этого используются значения 3 ближайших точек (пары значений в таблице). В линейной интерполяции, фактически, строится уравнение прямой, в которое и подставляется промежуточное значение. СНиП диктует линейную интерполяцию, например, для нахождения сопротивления теплопередачи по градусо-суткам. Для более точного определения значений следует использовать интерполяцию высоких порядков: 3, 4, 5 и т.д., то есть составлять многочлен Ньютона, находить в нем неизвестные коэффициенты с помощью системы обычных линейных уравнений, а затем подставлять промежуточное значение. Бывает, что этим (нахождением коэффициентов) занимаются ученые при разработке новых методов расчета. Тогда мы можем увидеть в формуле "экспериментальный" коэффициент.

zr84

Многоинтервальная квадратичная интерполяция -аппроксимация функций двух переменных, когда нужно интерполировать табличные функцию двух переменных
Мною этот алгоритм использовался при интерполяции данных калориферов КСК,
делал очень давно в VBasic. Вот страницы алгоритма(алгоритм дан операторами
Basic, можно организовать в Excel )

LordN

Книга Мудрова? Знаю, помню зачитывался в 1998 году, делал по ней на Basice проги для курсовых. Позже обязательно скачаю. Всем советую!

Видел прогу для обычной интерполяции с www.DWG.ru. Вообще, старый Excel лучше новой проги. На сайте www.DWG.ru (поиск "интерполяц") позаимствовал программу (1 ехе) для квадратичной интерполяции 563К.

LordN

Composter

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

pavelP2009

Composter

в таблице (L5:M26) ищется значение теплоотдачи (М5:M26) ,которое соответствует значению температурного напора (F37)

Вот хорошая функция для Excel на VBA для кубической сплайн интерполяции (со слабыми граничными условиями наверное).

Composter

Здравствуйте! У меня вот немного другой вопрос. Я работаю над некоторыми вычислениями в Excel и мне не хватает данных для точности. Я построила график по тем данным которые у меня есть. Получилась ломанная линия. Могу ли я получить дополнителные значения из графика на каждом отрезке, например 7-8 значений, и выводить их в отдельную таблицу? как можно это осуществить через VBA? Наверное тоже нужно использовать интерполяцию.

Медведица

Егор

TarantuL

Chocobear

Здравствуйте. Можете подсказать? Как Найти 3 ближайшие точки от Х ср Raschet.xls ( 42 килобайт ) Кол-во скачиваний: 20
в эксель автоматически?? И не могли бы вы просмотреть решение задачи интерполирования . Правильно??

Chocobear

Подскажите как привязать 3 ближайшие точки к Хар. Чтобы при изменении значений они подбирались автоматически??

Ernestas

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

Gidro38

Composter

так никакая интерполяция не нужна. можно справится штатными методами. файл прикладываю

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

Gidro38

так никакая интерполяция не нужна. можно справится штатными методами. файл прикладываю

Да и сейчас другая проблема нарисовалась. не сохраняет выпадающие списки после закрытия файла. ПОЧЕМУ.

Composter

Gidro38

Composter

видать совсем блондинка. там есть надпись Искомое значение,рядом в ячейке формула =интерпол(F2,F3,A6:Z27) , которая и заупскает интреполяцию.
"макросы" я написал чтобы было понятнее, я имел ввиду макросы, функции, процедуры и прочее.

функции вы не увидете в этом окне
alt+f11 - можно увидеть в файле 3 модуля с кодом на VBA. а в вашем файле все пусто

возможно надо попробовать заменить запятые на точки или наоборот.

Gidro38

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

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

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

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

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

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

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

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

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

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

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

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

Появится новое окно, в нем в правой стороне кликаем на "Изменить".

После этого надо задать диапазон подписи оси, выделив нужные данные в таблице и подтвердить действие.

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

В новом окне нужно произвести настройки. Под надписью "Показ пустых ячеек", кликаем на кружок возле наименования "Линия" и подтверждаем действие. И после проведенных манипуляций не забываем подтвердить действие в окне "Выбор источника данных".

Результатом будет показ правильного графика с проведенной интерполяцией.

Метод третий. Интерполяция графика с использованием функции НД

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

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

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

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

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

2) Надстройка для Excel *.xla с функцией пользователя Interpol(X_n, Y_n, arX, arY, arZ, X, Y)
X_n, Y_n – кол-во строк и столбцов таблицы
arX – столбец аргументов таблицы
arY – строка аргументов таблицы
arZ – двумерным массив значений в таблице
X – аргумент "по столбцу"
Y – аргумент "по строке"
Если не совсем понятно расписал значения аргументов функции Interpol – см. первый файл из архива.

Для работы обоих файлов надо не отключать макросы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: