По роду своей деятельности большинство специалистов, задействованных в области инженерных изысканий, и инженеры-геологи в частности, сталкиваются с большим количеством данных. Такие данные для удобства иногда располагаются в таблицах, которые можно наблюдать, к примеру, в нормативной документации. Но порой массива данных бывает недостаточно для получения необходимых значений и здесь на помощь в некоторых случаях может прийти такой математический инструмент, как интерполяция.
В процессе взаимодействия с некоторыми сторонними организациями, выполняющими инженерно-геологические и другие виды изысканий, отмечено, что большое количество расчетов интерполяции производится в ручном режиме. В лучшем случае на листе бумаги, в худшем - что называется, «на глаз». И то, и другое не очень хорошо. Эти рутинные действия отнимают большое количество времени, сил, а так же не исключают допущения ошибок в полученном результате.
Поэтому возникла идея подготовки и публикации данного материала. В нем рассмотрена одна из возможностей использования программы Excel для нахождения промежуточных значений, которая входит в стандартный пакет MS Office, установленный в большинстве компаний.
Вы научитесь делать это самостоятельно, создадите свой файл с расчетами, оцените все его преимущества и сведете к минимуму так называемый человеческий фактор. Сам материал изложен общедоступным языком, а формулы упрощены для лучшего их понимания.
Все примеры приводятся в русскоязычной версии MS Office Excel 2013, однако и в более ранних большинство формул должно работать, возможно, за исключением тех, в которых будет задано большое количество условий.
Интерполяция – в вычислительной математике способ нахождения промежуточных значений величины по имеющемуся дискретному набору известных значений. И в университете, на парах математики вы с ней скорее всего знакомились.
Вариантов интерполяции существует некоторое множество, но мы будем рассматривать именно линейную интерполяцию, которую в Excel можно выполнять с помощью функции ПРЕДСКАЗ. Стоит отметить, что сама эта функция имеет более широкие возможности.
Для наглядности построим простую точечную диаграмму с прямыми отрезками и маркерами. А значения для нее возьмем из таблицы 5.1 СП 22.13330.2016 «Основания зданий и сооружений» Актуализированная редакция СНиП 2.02.01-83* (с изменениями №1, 2, 3) 2019 г. Возможно, это один из самых частых примеров использования интерполяции, с помощью которой получают значение коэффициента moed, применяемого для корректировки одометрического модуля деформации.
Сначала мы будем использовать не все данные из этой таблицы, а только часть. Например – значения moed для супесей при коэффициенте пористости e 0,65-0,75. Создайте такую же таблицу в Excel. Обратите внимание, что содержимое должно соответствовать тем же строкам и столбцам, что и в примере.
На оси X в данном случае будут располагаться значения коэффициента пористости e, а на оси Y – коэффициента moed, соответственно. Посаженные по координатам точки будут соединены отрезком, который мы условно обозначим ab (рис.1).
Рис. 1. Точечная диаграмма по двум значениям
Давайте представим, что нам необходимо найти moed для супеси с коэффициентом пористости 0,7. Для этого от числа 0,7 на оси X мы проведем параллельную оси Y линию fc до нашего отрезка. Затем от точки пересечения проведем к оси Y уже параллельно оси X линию cd. И получим значение moed – 2,3 графику (рис. 2).
Рис. 2. Пример графического метода интерполяции
Это графический способ. Математически формула линейной интерполяции в данном случае выглядит так:
Рис. 3. Точечная диаграмма с обозначениями для примера математического метода интерполяции
Подставив все эти значения в формулу, получаем:
Рис. 4. Окончательный результат интерполяции с помощью функции ПРЕДСКАЗ
Теперь полностью автоматизируем расчеты и разберем на примерах различные варианты. Для каждого из них создадим отдельный лист в книге Excel.
Важно! Все значения должны быть размещены в тех же строках и столбцах, что и в примерах.
Построим таблицу 5.1 из пункта 5.3.7, СП 22.13330.2016 «Основания зданий и сооружений» Актуализированная редакция СНиП 2.02.01-83* (с изменениями №1, 2, 3) 2019 г. и автоматизируем получение коэффициента moed в зависимости от коэффициента пористости 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 для глин
Поэтому сначала выберем по два значения, внутри диапазона которых будет проведена интерполяция. А затем сделаем это для всех данных из таблицы по очереди.
Далее будет указано, в какую ячейку, какое содержимое вписывать. Формат ячеек может быть, как общий, так и числовой.
Рис. 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, вы получите искомое значение коэффициента moed в ячейке B6. В случае отсутствия значений будет выводиться сообщение об ошибке.
Стоит отметить, что добиться подобного можно и другими способами. Например, используя связку функций Excel: ГПР, ИНДЕКС и ПОИСКПОЗ. При этом не потребуется интерполировать всю таблицу, а условных операторов будет меньше. Рассмотрим этот вариант ниже.
Построим таблицу Б3 из приложения Б СП 22.13330.2016 «Основания зданий и сооружений» Актуализированная редакция СНиП 2.02.01-83* (с изменениями №1, 2, 3) 2019 г. и автоматизируем получение R0 в зависимости от коэффициента пористости e, показателя текучести IL и выбранного типа грунта (рис. 12). Для удобства в ячейках H2 и I2 оставим только цифры.
Рис. 12. Таблица Б3 из СП 22.13330.2016
Далее будет указано, в какую ячейку, какое содержимое вписывать. Формат ячеек может быть как общий, так и числовой.
Рис. 13. Конечный вид примера 2 на листе Excel
Как можно заметить, в этом примере для того чтобы не выполнять поочередную интерполяцию всех значений таблицы, были использованы функции ВПР, ИНДЕКС и ПОИСКПОЗ, что значительно упростило задачу. Изменяя тип грунта, а так же значения в ячейках B3 и B4, вы неизменно получите значение расчетного сопротивления в ячейке B5.
Широко известно, что отобрать образцы песка действительно ненарушенной структуры из инженерно-геологических скважин даже на сегодняшний день чрезвычайно затруднительно, особенно на значительных глубинах и в случаях, когда такие грунты обводнены. Это подтверждал и Лев Геннадьевич Мариупольский в книге «Исследования грунтов для проектирования и строительства свайных фундаментов», Москва, Стройиздат 1989.
В качестве альтернативы лабораторному методу определения плотности природного сложения аллювиальных и флювиогляциальных песков, залегающих на глубине до 6 м, Л.Г. Мариупольским была предложена возможность определения p с помощью результатов статического зондирования, природной влажности (W) и плотности частиц грунта (ps). Сопоставив 171 определение коэффициента пористости е, полученного в лабораторных условиях из ненарушенных образцов проб песка с сопротивлением конусу (qc) зонда II типа этих же грунтов, выведена следующая корреляционная зависимость:
Рис. 14. Таблица 1 на листе Excel
Далее ход действий такой же, как и в предыдущих примерах. Формат ячеек может быть, как общий, так и числовой.
Рис. 15. Окончательный вид примера 3 на листе Excel
Изменяя значения в ячейках B1, B2 и B3, вы будете получать значения как по результатам интерполяции таблицы, так и по результатам расчета. Как вы можете заметить, они немного отличаются. Расчетные более точные, в том числе и потому, что указывается реальная плотность частиц, полученная в результате лабораторных исследований.
Примечание. Вы могли заметить, что таблица заполнена не полностью. И даже если указать в ячейке B1, например 0,25, а в ячейке B2 – 40, интерполяция все равно будет выполнена. Только это, конечно уже будет экстраполяция. Чтобы этого избежать, можно не использовать условные операторы, как в предыдущих примерах, а заполнить пустые ячейки, например, словом «Ошибка» (рис. 16).
Рис. 16. Вариант запрета экстраполяции без использования условных операторов
В результате, в ячейке B7, вы получите «Ошибка» или #Н/Д.
В статье были предложены некоторые варианты для поиска и интерполяции значений. Конечно, их гораздо больше. Но уже с помощью этой информации вы самостоятельно сможете интерполировать любые таблицы или значения, которые найдете в нормативной документации или полученные в результате исследований. Без использования специального программного обеспечения, созданного для целей обработки геологической информации, можно вполне обойтись MS Office Excel.
наверх
Поделиться
Поделиться
Скопировать ссылку
Оформить подписку