Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Попадает ли число в диапазон, и если да в какой?
 
#1
30.09.2011 00:35:47
Столкнулся с задачей, есть очень много массивов с началом и концом... например 1-5 и нужно узнать принадлежит ли число в ячейке указанным массивам. Если да то вытягивать Фамилию из ячейки рядом.  
 
Пример прикрепляю. Помогите как можно решить проблему стандартными средствами Excel2003, а если нет - то как через VBA.  
 
В рабочей задаче количество массивов около 3000, и количество значений около 1000. Для единичного значения делал проверку через если, если вписывается то истина, если нет то ложь и так находился верный диапазон и фамилия... но если прописывать таким же образом под существующую задачу то нужно 3 000 000 ячеек-проверок %)
 
 
 
#2
30.09.2011 01:01:04
Если отсортировать массивы, то и простая ВПР() работает:  
в H4  
=ВПР(G4;$A$4:$C$11;3;1)
 
 
 
#3
30.09.2011 01:25:10
увы, данные забираются с внешнего файла, который редактировать нельзя.
 
 
 
#4
30.09.2011 01:36:28
Формула массива:  
=ИНДЕКС($C$4:$C$11;МАКС(ЕСЛИ((G4>=$A$4:$A$11)*(G4<=$B$4:$B$11);СТРОКА($4:$11)-3)))
 
 
 
#5
30.09.2011 02:40:41
через VBA....
 
 
 
#6
30.09.2011 09:03:03
{quote}{login=vikttur}{date=30.09.2011 01:36}{thema=}{post}Формула массива:  
=ИНДЕКС($C$4:$C$11;МАКС(ЕСЛИ((G4>=$A$4:$A$11)*(G4<=$B$4:$B$11);СТРОКА($4:$11)-3))){/post}{/quote}  
 
=ИНДЕКС($C$4:$C$11;МАКС((G4>=$A$4:$A$11)*(G4<=$B$4:$B$11)*СТРОКА($A$4:$A$11)-3))
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#7
30.09.2011 10:03:31
.. еще вариант.  
 
=ВПР(МИН(ЕСЛИ(((G9<$B$4:$B$11)*(G9<$B$4:$B$11)>0);$B$4:$B$11));$B$4:$C$11;2;0)
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#8
30.09.2011 16:35:48
Увы, не работают формулы когда список не отсортирован.  
=ИНДЕКС($C$4:$C$11;МАКС(ЕСЛИ((G4>=$A$4:$A$11)*(G4<=$B$4:$B$11);СТРОКА($4:$11)-3))) работает как копирование построчно с массива, тоесть не ищет значение в границах, а просто копирует значение. Получается - 36 Сидоров. когда диапазон 26-53 принадлежит Иваненко.  
 
=ИНДЕКС($C$4:$C$11;МАКС((G4>=$A$4:$A$11)*(G4<=$B$4:$B$11)*СТРОКА($A$4:$A$11)-3))  
Не работает, работает только для первого вхождения и то когда список отсортирован.  
 
=ВПР(МИН(ЕСЛИ(((G9<$B$4:$B$11)*(G9<$B$4:$B$11)>0);$B$4:$B$11));$B$4:$C$11;2;0)  
неккоректно работает :(  
 
Получился результат только с VBA, но я так понял что скрипт работает только по вводу, если применить уже к введенным данным - работать не будет.  
 
А цикл формулами в Excel не организовать? что бы одна ячейка проверяла все диапазоны в массиве
 
 
 
#9
30.09.2011 16:41:19
.. там сообщение от vikttur_а видели?  
"ФОРМУЛА МАССИВА"
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#10
30.09.2011 16:47:18
{quote}{login=KKS}{date=30.09.2011 04:35}{thema=}{post}Увы, не работают формулы когда список не отсортирован.{/post}{/quote}  
А у меня рабатают, нужно вводить как формулы массива (нажатием ctrl+shift+enter)  
 
Еще вариант, немассивная:  
=ПРОСМОТР(2;1/(G4>=$A$4:$A$11)/(G4<=$B$4:$B$11);$C$4:$C$11)
 
 
 
#11
30.09.2011 16:50:57
Так если формула массива то везде ж пропишется {=ИНДЕКС($C$4:$C$11;МАКС(ЕСЛИ((G4>=$A$4:$A$11)*(G4<=$B$4:$B$11);СТРОКА($4:$11)-3)))} и будет сравнение по 1му значению G4, хотя должно было размножится 4..11
 
 
 
#12
03.10.2011 23:58:18
Спасибо, разобрался, не думал что массив может состоять из одной формулы, но смысл была массива внутри ячейки. Все заработало!  
Формула vikttur - работает :)
 
 
 
Страницы: 1
Читают тему
Наверх