Надеюсь, кто-нибудь сможет ответить на мой вопрос!
У меня много значений, и мне нужно знать, какие из них попадают в список диапазонов, и если да, то в какой именно диапазон они попадают.
Например, рабочий лист 1 содержит интересующие меня значения в столбце A, в то время как рабочий лист 2 содержит начальное значение диапазонов в столбце B и конечное значение диапазонов в столбце C, а также имя для каждого из этих диапазонов в столбце D. Как мне узнать, находится ли значение, указанное в столбце A рабочего листа 1, между любым диапазоном, описанным в столбце B рабочего листа 2, и столбцом C, и вернуть соответствующую информацию из столбца D? По сути, выяснить, какой диапазон охватывает интересующее меня значение.
P.S. было бы еще лучше найти диапазон внутри диапазона, если бы кто-то знал, как это сделать, но я уже давно забросил это дело!
решение1
Существует множество веб-сайтов, на которых более подробно обсуждаются двойные совпадения и подобные формулы, а также более эффективные способы их решения, но вот один из примеров.
Предположения: 2 листа
- Лист 1 = данные
- Столбец A = Скважина
- Столбец B = Глубина
- Столбец C = Значение, рассчитанное по листу 2
- Лист 2 = диапазоны
- Скважина
- Глубина от
- Глубина до
- Значение (используется для заполнения столбца C листа 1)
формула представляет собой формулу массива, что означает, что ее нужно вводить с помощью 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))
Я не буду вдаваться в подробности работы индекса и сопоставления, поскольку это распространено во всем Интернете, но вот что происходит внутри формулы сопоставления.
Дваеслифункции проверяют, находится ли заданная глубина между каждым значением глубины от и глубины до. Если она не соответствует обоим этим критериям, она возвращает пробел, если она соответствует обоим критериям, она возвращает идентификатор скважины из столбца A.
Затем функция сопоставления пытается сопоставить ваш желаемый идентификатор скважины с массивом, который включает несколько хороших идентификаторов и кучу пробелов. Если он совпадает с идентификатором скважины, это и есть та строка, которая вам нужна, и она возвращает это значение индексу, который выбирает соответствующую ячейку значения.
Для сопоставляемых диапазонов это зависит от того, совпадают ли диапазоны или какие из них длиннее, поскольку вы можете попытаться заполнить значением диапазон, который пересекает несколько ячеек.