Определите, в каком из приведенных диапазонов есть случайное число

Question

У меня есть эта таблица с первым столбцом, являющимся просто Идентификационным кодом, а второй - диапазонами.

Например, если число находится в диапазоне от 0 до 1134, оно соответствует первой строке «1310101». Если оно находится между 1135 и 1311236, оно соответствует второму ряду и так далее, и так далее.

Я хочу сгенерировать случайное число в Excel с помощью RANDBETWEEN(1,6551654) а затем определить, в каком диапазоне столбца «Диапазон» находится число, и вернуть код идентификатора. Поэтому, если я получу, например, 1000, мне понадобится некоторая функция, которая возвращает 1310101, потому что я не хочу указывать условия вручную (в моей таблице более 2000 строк).

Answer 1

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

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

    =RANDBETWEEN(1,6551654)

Формула в D2 будет иметь вид:

    =INDEX(A$2:A$7,IF(ISERROR(MATCH(C2,B$2:B$7)),0,MATCH(C2,B$2:B$7))+IF(ISERROR((MATCH(C2,B$2:B$7,0))),1,0))

Скопируйте этот столбец D столько, сколько вам нужно. Я написал формулу таким образом, чтобы люди, использующие электронную таблицу, например LibreOffice Calc, не имели функции IFERROR. Для Excel вы можете упростить первое выражение ISERROR:

    IF(ISERROR(MATCH(C2,B$2:B$7)),0,MATCH(C2,B$2:B$7))   would become:
    IFERROR(MATCH(C2,B$2:B$7),0)

INDEX выбирает значение из первого столбца (я предполагаю столбец A), основываясь на результатах MATCH. Первый результат MATCH - это обычный поиск с коррекцией в случае, если значение меньше границы первого интервала. Второй результат MATCH увеличивает его до следующего интервала, если он не точно соответствует границе.