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

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

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

Я сохранил диапазоны в столбцах 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, которые я могу использовать для получения такого типа вывода. Я предпочитаю использовать формулы, а не какое-либо кодирование. Спасибо за ваше время, чтобы направить меня в этом отношении.

С наилучшими пожеланиями, Шридхар

решение1

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

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

| 0        | 0        | #N/A     |

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

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

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

, со значением, которое нужно проверитьD1

Проходя через формулу сД1содержащий 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вД1:

=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"

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

Связанный контент