Нахождение числа в диапазоне

Question

Не могли бы вы помочь мне с приведенным ниже требованием.

Я сохранил диапазоны в столбцах A и B. Соответствующие значения для каждого диапазона указаны в столбце C. Например, см. Ниже:

| Column A | Column B | Column C |
| 1        | 10       | A        |
| 15       | 20       | B        |
| 21       | 30       | C        |

Теперь у меня есть другой столбец E, где я вставил число, например, 17. Теперь я хотел бы знать, в какой диапазон попадает это число, чтобы можно было ввести соответствующее значение из столбца C. В этом примере 17 находится в диапазоне от 15 до 20, который находится в строке 2 и значение которого в столбце C равно B. Таким образом, для цифры 17 выход должен быть как B. Если я введу 25, выход должен быть C. Но если я введу 12, результат должен быть # N/A, потому что это число не попадает ни в какой диапазон.

Могу ли я узнать, есть ли какие-либо формулы Excel, которые я могу использовать, чтобы получить этот тип вывода. Я предпочитаю использовать формулы, а не кодирование. Спасибо за ваше время, чтобы вести меня в этом отношении.

С уважением, Шридхар

Answer 1

Да, это можно сделать - вам нужна дополнительная строка в таблице, чтобы это работало.

Я добавил строку

| 0        | 0        | #N/A     |

в таблицу в качестве первой строки, поэтому таблица теперь занимает A1:C4.

Я использую функцию INDEX чтобы решить, какую строку мне нужно вернуть.
Чтобы вычислить строку, я использую SUMPRODUCT для вычисления строки, которую будет использовать INDEX.
Полная формула становится:

=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=D1),--(B1:B4>=D1),ROW(C1:C4)))

со значением, подлежащим проверке в D1

Пройдя по формуле с D1, содержащей 17:

=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=17),--(B1:B4>=17),ROW(C1:C4)))
=INDEX(C1:C4,SUMPRODUCT(--({0,1,15,21}<=17),--({0,10,20,30}>=17),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT(--({True,True,True,False}),--({False,False,True,True}),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT({1,1,1,0}),({0,0,1,1}),{1,2,3,4}))
=INDEX(C1:C4,(1*0*1 + 1*0*2 + 1*1*3 + 0*1*4))
=INDEX(C1:C4,3)
="B"

Если число выходит за пределы диапазона, в SUMPRODUCT не будет части, в которой строка значений не равна 0 , поэтому он вернет первое значение в диапазоне, которое будет # N/A. Та же формула, но с 12 в D1:

=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=12),--(B1:B4>=12),ROW(C1:C4)))
=INDEX(C1:C4,SUMPRODUCT(--({0,1,15,21}<=12),--({0,10,20,30}>=12),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT(--({True,True,False,False}),--({False,False,True,True}),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT({1,1,0,0}),({0,0,1,1}),{1,2,3,4}))
=INDEX(C1:C4,(1*0*1 + 1*0*2 + 0*1*3 + 0*1*4))
=INDEX(C1:C4,0)
="#N/A"

Технически это не # N/A , это массив всех возможных ответов, но так как мы не ввели формулу как формулу массива, она дает нам только первое значение