Здравствуйте, друзья. Известные нам функции поиска данных, такие, как ВПР и ПОИСКПОЗ, ищут в массиве первое совпадение и возвращают его. Во многих случаях это и требуется, но что делать, когда следует получить все результаты, а не только первый, и искать их по нескольким критериям, а не по одному?

В этой статье мы решим такую задачу. Описываю пример. Есть ведомость школьных оценок 1-3 классов. Мы хотим выбрать класс, оценку, и увидеть учеников класса, которые получили этот балл:

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

Создание вспомогательной колонки

Сейчас необходимо добавить справа от ведомости еще один столбец. Назовём его «технический», т.к. данные в нем будут неинформативными, вспомогательными.

В этой колонке пронумеруем строки так, чтобы напротив каждой строчки, удовлетворяющей условию поиска, стоял её порядковый номер в таблице-выборке.

создаем дополнительный столбец

Смотрите на рисунке, чего мы хотим добиться. Ищем всех учеников класса, которые получили 5. Первый из списка, кто получил такую оценку – Соколов. Напротив него нужно поставить единичку. Второй в списке – Козлов, даём ему второй номер по порядку, третий – Волков, ему ставим третий номер и т.д. Под этими номерами они потом попадут в табличку с выборкой.

Формулу укажу на примере ячейки D3:

=СУММ(D2;И(A3=$L$2;C3=$L$3))

Функция СУММ для каждой строки сложит значение из предыдущей ячейки, а так же, результат сравнения класса в ведомости с искомым, оценки в ведомости с искомыми. Два уточнения:

  • В ячейке D2 – текст, и функция СУММ его проигнорирует. В остальных строчках будет браться число из ячейки сверху;
  • ФОРМУЛА И(A3=$L$2;C3=$L$3) вернёт ИСТИНУ (единицу), когда оба условия внутри неё выполнятся, или ЛОЖЬ (ноль), если хотя бы одно из условий не выполнится. Таким образом, когда функция И найдет совпадение класса и оценки, СУММ увеличит порядковый номер на единицу, что нам и требовалось

дополнительный столбец заполнен

Смотрите на картинке результат. Все найденные совпадения я выделил красным цветом с помощью условного форматирования.

Вывод отобранных строк в таблицу

Теперь осталось вывести на экран выборку учеников класса с нужной оценкой. Для этого сопоставим номера в столбце F с номерами в технической колонке D. Удобнее всего это сделать с помощью комбинации функций ИНДЕКС и ПОИСКПОЗ:

=ЕСЛИОШИБКА(ИНДЕКС(A$3:A$182;ПОИСКПОЗ($F3;технический;0));»»)

список сформирован

Алгоритм работы на примере ячейки G3:

  1. Функция ПОИСКПОЗ получает числовой индекс строки из ячейки F3 и ищет такое же значение в техническом столбце D. Возвращает либо порядковый номер найденной ячейки в массиве, либо ошибку #Н/Д
  2. Функция ИНДЕКС получает из ведомости класс, находящийся в ячейке, порядковый номер которой мы получили в первом пункте
  3. Далее функция ЕСЛИОШИБКА выводит на экран класс, если он найден, или пустую строку, если ПОИСКПОЗ вернула ошибку

Такой порядок работает и для остальных столбцов: «Фамилия», «Баллы». Теперь если изменить искомый класс или оценку, формулы сами всё пересчитают и переформируют список. Можно скрыть дополнительный столбец, чтобы не мешал.

Для удобного выбора оценки и класса, я создал раскрывающийся список. В этой статье вы можете прочитать, как это делается.

Мы получили полностью автоматизированный интерактивный проект, способный формировать списки «налету», сразу после того, как вы сделаете свой выбор. На этом всё, и я готов отвечать на ваши вопросы!

Скачать пример