У меня возникли некоторые проблемы с проблемой, как указано: у меня есть рабочий лист, содержащий столбец A с разными названиями скважин. Во втором и третьем столбце указаны верхняя и нижняя глубины геологического слоя, а также название слоя. Пример: Well_XYZ --- 40.02 --- 40.55 --- Layer_NAME
На другом рабочем листе у меня есть список образцов, взятых из/из этих различных скважин, как указано в другом рабочем листе в столбце A, с определенной глубиной образца. Пример: Образец-XYZ --- 40.34
Теперь я хочу узнать, какую формулу (написанную в ячейке EXCEL) я мог бы использовать для добавления Layer_NAME в лист sample-ID при проверке глубины образца интервала глубины каждой скважины. Я попробовал несколько подходов (используя INDEX/MATCH, VLOOKUP), но ни один из них не работает правильно (или формула не принимается с "отсутствующими аргументами" согласно моей "логике" программирования R).
Поскольку я не хочу передавать эти задачи на аутсорсинг R (пока), а вместо этого хочу улучшить свои знания Excel (в котором используются другие «схемы» и подходы, как в Python или R), я был бы очень рад, если бы вы могли мне помочь в этом и познакомить меня с миром «мышления в Excel». :)
Заранее большое спасибо!
решение1
Было бы очень полезно разместить в сети настоящий файл XL для просмотра, но я попробовал с этими данными:
О, я этого не ожидал. SU превратил мою вставленную таблицу в изображение. Хорошо, давайте это используем.
А вот ваш другой лист выглядит так:
Формула такова:
=LOOKUP(B2,Sheet1!B:B, Sheet1!D:D)
Я взял это отсюда:https://exceljet.net/formula/lookup-value-between-two-numbers
Но есть несколько БОЛЬШИХ проблем с этим! Во-первых, поскольку Well — это имя 1-го столбца на 1-м листе, мы должны предположить, что на листе может быть больше одного колодца. Это само по себе не проблема. Проблема в том, что данные в верхнем столбце должны быть отсортированы (по возрастанию). Так что если у вас есть еще один колодец, и ваши данные выглядят так:
Тогда у вас есть перекрывающиеся диапазоны (40.34 попадает в 2 диапазона) и вы можете получить неправильный результат (вы получите последнее совпадение). Если вы можете изменить страницу образца, чтобы сохранить имя скважины в качестве отдельного столбца, вы можете использовать это, чтобы «отфильтровать» 1-й лист, а затем выполнить этот Lookup выше для результата. Это намного сложнее, но это определенно выполнимо, см.здесьиздесь.
решение2
Я думаю, что в вашем вопросе не хватает некоторых деталей, но я предполагаю, что у вас должно быть название скважины на листе образцов. Так что, расширяя другой ответ, вы можете использовать XLOOKUPпротив фильтра глубины скважины.
=XLOOKUP(H2,FILTER($B$2:$B$5,$A$2:$A$5=$G2),FILTER($D$2:$D$5,$A$2:$A$5=$G2),"",-1,1)
Для простоты я разместил данные своих образцов на одном листе.
Первый FILTERпросто возвращает значения из столбца B, где столбец A соответствует имени скважины в текущей строке (которая находится в столбце G). Это просто массив из двух элементов {40.02,40.55}. Это значения, которые XLOOKUPбудут искаться.
Второй FILTERвозвращает значения из столбца D, где столбец A соответствует имени скважины текущей строки (снова столбец G). Это два значения {XYZ_1,XYZ_2}. Теперь они соответствуют двум числовым значениям, перечисленным выше. Если мы сопоставляем с первым из искомых значений, мы вернем имя первого слоя, если мы сопоставляем со вторым из искомых значений, мы вернем имя второго слоя.
Четвертый параметр XLOOKUP— что возвращать, если совпадений не найдено. В нашем случае — пустая строка.
Пятый параметр важен для этой проблемы. -1используется для «Точного совпадения или следующего меньшего элемента». Когда мы пытаемся сопоставить любое значение из выборки с этим списком, мы ищем в массиве поиска и находим ближайшее совпадение, которое не больше искомого значения. Последний параметр сообщает функции, в каком направлении искать. Его можно опустить, так как по умолчанию используется значение 1.
Итак, мы ищем в массиве из двух чисел выше 40.34. Поскольку мы получим ближайшее совпадение, которое не больше этого значения, мы получим 40.02, что является первым элементом массива. Таким образом, мы вернем первый элемент массива из второго массива, содержащего имена слоев - XYZ_1.
Подводя итог, можно сказать, что фильтр помогает нам избежать проблемы, при которой разные скважины могут иметь одинаковую глубину, а приблизительный поиск помогает нам найти «ближайшее» совпадение из отфильтрованного списка.