Страницы: 1
RSS
Поиск значения по двум критериям, формулы для Excel 2003
 
Здравствуйте.
Помогите с формулами (Excel 2003) для поиска значения по двум критериям.
Формулы пробовал разные (вектор и массив), но полностью решить поставленную задачу не получилось. Дело в том, что условия находятся в объединенных ячеках и в них поиск работает не стабильно, а необходимо, чтобы формула растягивалась как по вертикали, так и по горизонтали.
Пример таблицы с несколькими вариантами решения прикрепил к сообщению. Подскажите, что не так?
 
Если нет пропусков чисел в данных, то
=VLOOKUP(B136;INDEX(B:B;MATCH(LOOKUP(2;1/(A$136:A136<>"");A$136:A136);A:A;)):INDEX(C:C;MATCH(LOOKUP(2;1/(A$136:A136<>"");A$136:A136);A:A;)+COUNT($B$3:$B$132)+1);2;)
или
=SUMPRODUCT((INDEX(A:A;N(INDEX(LOOKUP(ROW($A$3:$A$123);ROW($A$3:$A$123)/($A$3:$A$123<>""));)))=LOOKUP(2;1/(A$136:A136<>"");A$136:A136))*($B$3:$B$123=B136)*$C$3:$C$123)
По вопросам из тем форума, личку не читаю.
 
Как вариант.
Код
=INDEX(C$3:C$123;MATCH(1;INDEX(LOOKUP(ROW(A$3:A$123);ROW(A$3:A$123)/(A$3:A$123>0);A$3:A$123=LOOKUP(2;1/(A$136:A136>0);A$136:A136))/(B$3:B$123=B136););))
 
БМВ и memo, Спасибо. Приятно видеть работу профессионалов.
Вопрос решён.
 
БМВ, Подскажите, как усовершенствовать формулу, чтобы она работала при растягивании по горизонтали? Закрепил столбцы A  и В, но необходимо, чтобы увеличивались последние два числовых значения в формуле:
=ВПР($B136;ИНДЕКС($B:$B;ПОИСКПОЗ(ПРОСМОТР(2;1/($A$136:$A136<>"");$A$136:$A136);$A:$A;)):ИНДЕКС(C:C;ПОИСКПОЗ(ПРОСМОТР(2;1/($A$136:$A136<>"");$A$136:$A136);$A:$A;)+СЧЁТ($B$3:$B$132)+1);2;)
чтобы получалось:
=ВПР($B136;ИНДЕКС($B:$B;ПОИСКПОЗ(ПРОСМОТР(2;1/($A$136:$A136<>"");$A$136:$A136);$A:$A;)):ИНДЕКС(D:D;ПОИСКПОЗ(ПРОСМОТР(2;1/($A$136:$A136<>"");$A$136:$A136);$A:$A;)+СЧЁТ($B$3:$B$132)+2);3;)

=ВПР($B136;ИНДЕКС($B:$B;ПОИСКПОЗ(ПРОСМОТР(2;1/($A$136:$A136<>"");$A$136:$A136);$A:$A;)):ИНДЕКС(E:E;ПОИСКПОЗ(ПРОСМОТР(2;1/($A$136:$A136<>"");$A$136:$A136);$A:$A;)+СЧЁТ($B$3:$B$132)+3);4;)


С остальными формулами: Вашей и memo разобрался:
=СУММПРОИЗВ((ИНДЕКС($A:$A;Ч(ИНДЕКС(ПРОСМОТР(СТРОКА($A$3:$A$123);СТРОКА($A$3:$A$123)/($A$3:$A$123<>""));)))=ПРОСМОТР(2;1/($A$136:$A136<>"");$A$136:$A136))*($B$3:$B$123=$B136)*C$3:C$123)

=ИНДЕКС(C$3:C$123;ПОИСКПОЗ(1;ИНДЕКС(ПРОСМОТР(СТРОКА($A$3:$A$123);СТРОКА($A$3:$A$123)/($A$3:$A$123>0);$A$3:$A$123=ПРОСМОТР(2;1/($A$136:$A136>0);$A$136:$A136))/($B$3:$B$123=$B136););))
 
Максим Для протягивания по горизонтали используйте например COLUMN(B$1) или COLUMNS($A1:B$1) это даст нужный результат: 2...3...4 и т.д.
 
Максим, уже memo, ответил. Все верно, вместо индекса столбца в ВПР использовать COLUMN или COLUMNS c соответствующим диапазоном. Обычно, чтоб избежать казусов с удалением строк или столбцов перед таблицей, что приводит к сбою формул, используют диапазон той же строки и COLUMNS, который ссылается на первый столбец и на текущую ячейку например.   Кстати вариант с ВПР еще и самый быстрый, хотя и не самый короткий.
По вопросам из тем форума, личку не читаю.
 
БМВ, memo, Спасибо за помощь и подробные разъяснения.
Вставил функцию ЧИСЛСТОЛБ - всё работает.
Страницы: 1
Наверх