Не могли бы вы помочь мне с приведенным ниже требованием.
Я сохранил диапазоны в столбцах 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"
Это не технически#Н/Д, это массив всех возможных ответов, но поскольку мы не ввели формулу как формулу массива, она дает нам только первое значение