Excel: Как найти диапазон из списка диапазонов, в котором находится значение?

Question

Я надеюсь, что кто-то может ответить на мой вопрос!

У меня много значений, и мне нужно знать, какие из них находятся в списке диапазонов, и если да, то в каком диапазоне они лежат.

Например: рабочий лист 1 содержит мои интересующие значения в столбце A, а рабочий лист 2 содержит начальное значение диапазонов в столбце B и конечное значение диапазонов в столбце C, а также имя для каждого из этих диапазонов в столбце D. Как бы я выяснить, находится ли значение, указанное в столбце A рабочей таблицы 1, между любыми диапазонами, описанными в столбце B рабочей таблицы 2, в столбце C и вернуть соответствующую информацию из столбца D. По сути, выяснить, какой диапазон охватывает интересующее значение.

Спасибо за любую помощь, которую вы можете оказать!

ps найти диапазон в пределах диапазона было бы еще лучше, если бы кто-нибудь знал, как это сделать, но я отказался от этого некоторое время назад!

Answer 1

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

Допущения:2 листа

• Лист 1 = данные
• Колонна А = скважина
• Колонка B = Глубина
• Столбец C = значение, рассчитанное по листу 2
• Лист 2 = диапазоны
• скважинный
• Глубина от
• Глубина до
• Значение (используется для заполнения листа 1, столбец C)

формула - это формула массива, которая означает, что ее нужно вводить с помощью Ctrl + Shift + Enter

=INDEX(ranges!$D$2:$D$5,MATCH(data!A2,IF(data!B2>ranges!$B$2:$B$5,IF(data!B2<ranges!$C$2:$C$5,ranges!$A$2:$A$5,""),""),0))

Я не буду вдаваться в то, как работает индексирование и сопоставление, так как это происходит во всей сети, но для внутренней части формулы сопоставления это то, что происходит.

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

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

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