Поиск числа в коллекции диапазонов в Excel

Question

Как и в примере ниже, у меня есть таблица, содержащая коллекцию диапазонов.

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

Например: найдите описание, связанное со значением 208 в таблице ниже.

--------------------------------------
| Item | Location Description        |
|------------------------------------|  
| 1    | 100 to 103 agenda           |
| 2    | 106 to 111 Notes of Meeting |  
| 3    | 112 to 123 revision         |
| 4    | 200 to 210 factor           |
--------------------------------------

Есть ли способ сделать это?

Answer 1

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

Вместо того чтобы показывать диапазоны в форме в вашем вопросе, используйте таблицу, которая имеет начальное значение для каждого диапазона. Я добавил значения, чтобы заполнить пробелы, где у вас есть разрывы, и назначил описание как "ошибка" в случае, если кто-то пытается использовать неопределенное число.

В столбце C я ввел несколько чисел, чтобы показать, как значения будут работать относительно диапазонов. Формула для результата в столбце D. В D1:

=VLOOKUP(C1,A$1:B$7,2,1)

Это ищет значение из столбца C в первом столбце таблицы, ища самое высокое значение, которое не превышает значение поиска. Получает результат из второго столбца таблицы. Последний 1 параметр указывает, что значения находятся в порядке возрастания.

Answer 2

Подход

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

Этот пример основан на предположении, что все диапазоны содержат трехзначные числа. Если нет, нам нужно будет проанализировать конечные точки диапазона другим способом, например, найти одно из пробелов в фразе диапазона и затем вычислить, где находятся значения диапазона.

Я выбрал B7 для ввода значения поиска и C7 для отображения результата поиска. Вспомогательные столбцы E и F.

Как это устроено

В столбце E мы смотрим, находится ли целевое значение в диапазоне этой строки. Если это так, то отображается описание этой строки. В противном случае ячейка остается пустой. Таким образом, в столбце E только строка, в которой находится цель, будет отображать описание.

Столбец F последовательно объединяет результаты столбца E. Поскольку это объединяет пробелы для несовпадающих диапазонов с любым отображаемым описанием, последняя ячейка в столбце F будет отображать описание, откуда бы оно ни появлялось. C7 просто отображает последнюю ячейку в столбце F.

Формулы

Мои данные начинаются со строки 2, поэтому E2 содержит:

=IF(AND(B$7>=--LEFT(B2,3),B$7<=--RIGHT(B2,3)),C2,"")

Скопируйте это вниз по строке по мере необходимости. AND сравнивает целевое значение (B7) с обоими концами диапазона строки. Он использует функции ВЛЕВО и ВПРАВО для извлечения трех цифр с каждого конца строки. Двойные отрицания заставляют Excel обрабатывать эти числовые строки как фактические числа. Пустые двойные кавычки в конце - это нулевое значение, которое оставит ячейку пустой, если тест ложен (кавычки не должны иметь ничего между ними).

F2 содержит:

= Ф1 & Е2

Скопируйте это вниз по строке по мере необходимости. Обратите внимание, что это зависит от того, F1 будет пустым. Это берет все, что находится в ячейке выше, и объединяет все, что является результатом в столбце E для строки.

Ячейка результата C7 содержит:

= F5

В этом примере F5 - результат в последней строке данных.

Как упоминалось ранее, столбцы E и F могут быть скрыты, поэтому я показал действие в двух столбцах, чтобы упростить визуализацию. Тем не менее, E и F могут быть объединены, и действие выполняется с одним вспомогательным столбцом. Например, сделайте F вспомогательным столбцом и замените ссылку на ячейку столбца E формулой столбца E. Поэтому оставьте столбец E, и F2 будет:

=F1&IF(AND(B$7>=--LEFT(B2,3),B$7<=--RIGHT(B2,3)),C2,"")

Заметка

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