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

Введение

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

Вариантов интерполяции существует некоторое множество, но мы будем рассматривать именно линейную интерполяцию, которую в 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

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

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

=$F$4:$F$6

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

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

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

=ЕСЛИ(B2="Супеси";F8;ЕСЛИ(B2="Суглинки";F9;ЕСЛИ(B2="Глины";F10)))

Если вы все сделали правильно, то ваш лист 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:

=ЕСЛИ(B2="Супеси";J14;ЕСЛИ(B2="Суглинки";J18;ЕСЛИ(B2="Глины";J23)))

В результате лист 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 г/см³.

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

Теперь перенесем ее на новый лист 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.

СКАЧАЙТЕ ПРИМЕРЫ В EXCEL

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

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