искать
Рубрикатор материалов

Сейчас в информационной базе:
рубрик - 113 , авторов - 407 ,
всего информационных продуктов - 4198 , из них
статей журнала - 887 , статей базы знаний - 90 , новостей - 2962 , конференций - 4 ,
блогов - 10 , постов и видео - 193 , технических решений - 7

© 2016-2020 ГеоИнфо

Разработка и сопровождение: InfoDesigner.ru
Теория и практика изысканий 

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

Арапов Алексей Григорьевич
21 апреля 2021 года

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

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

Поэтому возникла идея подготовки и публикации данного материала. В нем рассмотрена одна из возможностей использования программы 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. Точечная диаграмма по двум значениям
Рис. 1. Точечная диаграмма по двум значениям

 

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

 

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

 

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

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

 

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

 

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

 

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

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

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

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

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

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

 

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

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

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

 

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

 

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

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

 

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

Построим таблицу 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 от коэффициента пористости для некоторых грунтов
Рис. 5. Таблица зависимости moed от коэффициента пористости для некоторых грунтов

 

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

 

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

 

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

 

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

 

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

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

 

 

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

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

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

 

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

 

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

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

=$F$4:$F$6

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

 

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

 

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

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

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

 

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

 

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

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

 

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

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

 

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

 

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

 

 

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

Супеси;Суглинки;Глины

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

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

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

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

 

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

 

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

 

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

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

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

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

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

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

ps- плотность частиц грунта;

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

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

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

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

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

 

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

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

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

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

 

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

 

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

 

 

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

 

 

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

 

 

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

 

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

 

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

 

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

 

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

 

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

 

Выводы

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

 

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


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

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

 

Отправить сообщение, заявку, вопрос

Отправить заявку на посещение мероприятия

Отправить заявку на участие как экспонент

Запросить консультацию специалистов по данному техническому решению