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

Метод вычисления значения шага интерполяции в Excel

В следующей формуле Microsoft Excel выполняется линейная интерполяция путем вычисления значения шага интерполяции:

=(end-start)/(ROW(end)-ROW(start))

где конечным является адрес ячейки большего числа, а начальным — адрес ячейки меньшего числа.

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

Дополнительная информация

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

Введите в рабочую книгу следующие данные:

A1: 9 B1: =(A7-A1)/(ROW(A7)-ROW(A1)) A2: =A1+$B$1 A3: A4: A5: A6: A7: 11 

A2: 9.33333 A3: 9.66667 A4: 10. A5: 10.33333 A6: 10.66667 

Необходимо ввести ссылку на значение шага в ячейке B1 в качестве абсолютной ссылки (со значками доллара).

Линейная интерполяция в Excel: пошаговый пример

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

Имея два известных значения (x 1 , y 1 ) и (x 2 , y 2 ), мы можем оценить значение y для некоторой точки x, используя следующую формулу:

у = у 1 + (хх 1 )(у 2 -у 1 )/(х 2 -х 1 )

В этом руководстве объясняется, как использовать линейную интерполяцию для поиска неизвестного значения y на основе значения x в Excel.

Пример: линейная интерполяция в Excel

Предположим, у нас есть следующий набор данных в Excel:

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

Теперь предположим, что мы хотим найти значение y, связанное с новым значением x, равным 13.Мы видим, что мы измерили значения y для значений x 12 и 14, но не для значения x 13.

Мы можем использовать следующую формулу для выполнения линейной интерполяции в Excel, чтобы найти расчетное значение y:

= FORECAST (NewX, OFFSET (KnownY, MATCH (NewX,KnownX,1)-1,0,2), OFFSET (KnownX, MATCH (NewX,KnownX,1)-1,0,2)) 

Вот как использовать эту функцию для оценки значений y, связанных со значением x, равным 13:

Расчетное значение y оказывается равным 33,5 .

Если мы добавим точку (13, 33,5) к нашему графику, она, похоже, будет хорошо соответствовать функции:

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

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

Вы можете найти больше учебников по Excel здесь .

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

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

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

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

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

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

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

• X;
• Известные значения y;
• Известные значения x.

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

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

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

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

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

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

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

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

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

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

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

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

Максим Тютюшев Вам помогли мои советы?

Задайте вопрос или оставьте свое мнение Отменить комментарий

15 комментария
Алексей Капля :

Есть 3 значения у для трех значений х.
Монтажное натяжение T, т.
t=-40⁰C t=-0⁰C t=-+40⁰C
24,60 19,50 14,75
20,50 17,17 14,65
19,00 17,17 15,57
16,50 14,88 13,70
16,75 15,42 14,69
Необходимо определить значения монтажных натяжений для температуры с шагом 1 градус.

Есть ли возможность заполнить эту таблиу автоматически, а не вычислять каждое значение в отдельности?

Это уже двойная интерполяция.

Способы нахождения неизвестных значений посредством интерполяции в Excel

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

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

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

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

Все примеры приводятся в русскоязычной версии MS Office Excel 2013, однако и в более ранних большинство формул должно работать, возможно, за исключением тех, в которых будет задано большое количество условий.

Введение

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

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

Для наглядности построим простую точечную диаграмму с прямыми отрезками и маркерами. А значения для нее возьмем из таблицы 5.1 СП 22.13330.2016 «Основания зданий и сооружений» Актуализированная редакция СНиП 2.02.01-83* (с изменениями №1, 2, 3) 2019 г. Возможно, это один из самых частых примеров использования интерполяции, с помощью которой получают значение коэффициента m_oed , применяемого для корректировки одометрического модуля деформации.

Сначала мы будем использовать не все данные из этой таблицы, а только часть. Например – значения m_oed для супесей при коэффициенте пористости e 0,65-0,75. Создайте такую же таблицу в Excel. Обратите внимание, что содержимое должно соответствовать тем же строкам и столбцам, что и в примере.

На оси X в данном случае будут располагаться значения коэффициента пористости e , а на оси Y – коэффициента m_oed , соответственно. Посаженные по координатам точки будут соединены отрезком, который мы условно обозначим ab (рис.1).

Рис. 1. Точечная диаграмма по двум значениям

Давайте представим, что нам необходимо найти m_oed для супеси с коэффициентом пористости 0,7. Для этого от числа 0,7 на оси X мы проведем параллельную оси Y линию f_c до нашего отрезка. Затем от точки пересечения проведем к оси Y уже параллельно оси X линию cd . И получим значение m_oed – 2,3 графику (рис. 2).

Рис. 2. Пример графического метода интерполяции

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

где Y0=2,1; Y1=2,5; X=0,7; X0=0,75; X1=0,65

На рисунке 3 приведена диаграмма с соответствующими обозначениями.

Рис. 3. Точечная диаграмма с обозначениями для примера математического метода интерполяции

Подставив все эти значения в формулу, получаем:

Y – есть наше искомое значение m_oed для коэффициента пористости 0,70, которое для супеси равно 2,3.

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

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

Сама функция в Excel имеет следующий вид:

ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)

Возвращаемся к нашему примеру и в ячейку M5 запишем известное значение коэффициента пористости – 0,70, а в ячейку N5 впишем следующую формулу:

=ПРЕДСКАЗ(M5;M3:N3;M2:N2)

Нажмем Enter. В результате в ячейке N5 получим значение 2,3, которое соответствует нашему искомому коэффициенту m_oed . Изменяя значение в ячейке M5 от 0,65 до 0,75, вы будете получать в соответствии с ним новые значения в ячейке N5 (рис. 4).

Рис. 4. Окончательный результат интерполяции с помощью функции ПРЕДСКАЗ

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

Важно! Все значения должны быть размещены в тех же строках и столбцах, что и в примерах.

Пример 1. Получение коэффициента m_oed

Построим таблицу 5.1 из пункта 5.3.7, СП 22.13330.2016 «Основания зданий и сооружений» Актуализированная редакция СНиП 2.02.01-83* (с изменениями №1, 2, 3) 2019 г. и автоматизируем получение коэффициента m_oed в зависимости от коэффициента пористости e и выбранного типа грунта. Для удобства столбец «0,45-0,55» разобьем на два. Там, где значений нет, поставим 0 (рис. 5).

Рис. 5. Таблица зависимости moed от коэффициента пористости для некоторых грунтов

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

Рис. 6. Точечная диаграмма и линейная аппроксимация по значениям из таблицы 5.1, СП 22.13330.2016 для супесей

Рис. 7. Точечная диаграмма и линейная аппроксимация по значениям из таблицы 5.1, СП 22.13330.2016 для суглинков

Рис. 8. Точечная диаграмма и линейная аппроксимация по значениям из таблицы 5.1, СП 22.13330.2016 для глин

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

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

Затем для большего удобства сделаем выбор типа грунта из выпадающего списка. Для этого:

Выберите ячейку B2

На вкладке «Данные», в разделе «Работа с данными», выберите «Проверка данных» (рис. 9)

Рис. 9. Расположение пункта «Проверка данных» в Excel 2013

В появившемся окне в разделе «Условия проверки» в качестве типа данных установите «Список»

В строку «Источник» впишите:

Нажмите «Ок» (рис. 10)

Рис. 10. Меню «Проверка вводимых значений» в Excel 2013

Остается только получить нужный результат. Впишите формулу в ячейку B6:

Если вы все сделали правильно, то ваш лист Excel должен иметь следующий вид (рис. 11):

Рис. 11. Конечный вид примера 1 на листе Excel

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

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

Пример 2. Получение расчетного сопротивления глинистых непросадочных грунтов

Построим таблицу Б3 из приложения Б СП 22.13330.2016 «Основания зданий и сооружений» Актуализированная редакция СНиП 2.02.01-83* (с изменениями №1, 2, 3) 2019 г. и автоматизируем получение R₀ в зависимости от коэффициента пористости e , показателя текучести I_L и выбранного типа грунта (рис. 12). Для удобства в ячейках H2 и I2 оставим только цифры.

Рис. 12. Таблица Б3 из СП 22.13330.2016

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

Теперь выберем ячейку B2 и создадим выпадающий список грунтов, как в первом примере. Только в поле «Источник» (см. рис. 10) впишем уже такое содержимое:

Подтвердите все кнопкой «Ок» и нажмите на ячейку B2 . Появится выпадающий список, в котором выберите, например, суглинки.

Остается только получить R₀ . Для этого впишите такую формулу в ячейку B5 :

В результате лист Excel должен иметь следующий вид (рис. 13).

Рис. 13. Конечный вид примера 2 на листе Excel

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

Пример 3. Косвенный метод определения плотности p песков по результатам статического зондирования

Широко известно, что отобрать образцы песка действительно ненарушенной структуры из инженерно-геологических скважин даже на сегодняшний день чрезвычайно затруднительно, особенно на значительных глубинах и в случаях, когда такие грунты обводнены. Это подтверждал и Лев Геннадьевич Мариупольский в книге «Исследования грунтов для проектирования и строительства свайных фундаментов», Москва, Стройиздат 1989.

В качестве альтернативы лабораторному методу определения плотности природного сложения аллювиальных и флювиогляциальных песков, залегающих на глубине до 6 м, Л.Г. Мариупольским была предложена возможность определения p с помощью результатов статического зондирования, природной влажности ( W ) и плотности частиц грунта ( p_s ). Сопоставив 171 определение коэффициента пористости е , полученного в лабораторных условиях из ненарушенных образцов проб песка с сопротивлением конусу ( q_c ) зонда II типа этих же грунтов, выведена следующая корреляционная зависимость:

где 1 – это 1 МПа.

По утверждениям автора, коэффициент корреляции для такой зависимости составил 0,74, а среднее квадратическое отклонение – 0,09. Причем гранулометрический состав в таком случае практически не влияет на точность определения e .

Исходя из вышесказанного, получена формула количественного определения плотности p таких песков:

p_s — плотность частиц грунта;

W — влажность грунта;

e – коэффициент пористости.

Если подставить в эту формулу коэффициент пористости, то получится следующее:

Т.к. для определения W и p_s нет необходимости отбирать образцы ненарушенной структуры, то, по утверждениям автора, точности результатов p по данной формуле достаточно для расчетов оснований свайных фундаментов.

В следствии этого была представлена таблица, в которой приведены значения плотности p песков в зависимости от их q_c и W .

Таблица 1. Значения плотности песков p в зависимости от сопротивления конусу зонда q_c и влажности W по Мариупольскому

Для расчета значений в этой таблице, плотность частиц песков принималась, как средняя, p_s =2,65 г/см 3 .

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

Теперь перенесем ее на новый лист Excel. Чтобы еще немного усложнить задачу, расположим значения в строке q_c от большего к меньшему. Соответственно, значения p тоже перенесем (рис. 14).

Рис. 14. Таблица 1 на листе Excel

Далее ход действий такой же, как и в предыдущих примерах. Формат ячеек может быть, как общий, так и числовой.

Теперь обработаем результаты, а заодно рассчитаем такие показатели, как плотность сухого грунта p_d , коэффициент пористости e и коэффициент водонасыщения S_r .

Дополнительно, помимо простой интерполяции, выполним расчет p по тем формулам, которые указывались выше:

Теперь, лист Excel у вас должен иметь следующий вид (рис. 15)

Рис. 15. Окончательный вид примера 3 на листе Excel

Изменяя значения в ячейках B1 , B2 и B3 , вы будете получать значения как по результатам интерполяции таблицы, так и по результатам расчета. Как вы можете заметить, они немного отличаются. Расчетные более точные, в том числе и потому, что указывается реальная плотность частиц, полученная в результате лабораторных исследований.

Примечание. Вы могли заметить, что таблица заполнена не полностью. И даже если указать в ячейке B1 , например 0,25, а в ячейке B2 – 40, интерполяция все равно будет выполнена. Только это, конечно уже будет экстраполяция. Чтобы этого избежать, можно не использовать условные операторы, как в предыдущих примерах, а заполнить пустые ячейки, например, словом «Ошибка» (рис. 16).

Рис. 16. Вариант запрета экстраполяции без использования условных операторов

В результате, в ячейке B7 , вы получите «Ошибка» или #Н/Д.

Выводы

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

Список литературы

1. Л.Г. Мариупольский. Исследования грунтов для проектирования и строительства свайных фундаментов. Москва, Стройиздат, 1989 г.
2. СП 22.13330.2016 «Основания зданий и сооружений» Актуализированная редакция СНиП 2.02.01-83* (с изменениями №1, 2, 3). 2019 г.

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

Поддержите нас один раз за год

Поддерживайте нас каждый месяц