Поиск ЧИСЛОвых значений и вывод соответствующих значений в отдельный список в EXCEL

Найдем числовые значения, равные заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.


Пусть имеется перечень учеников класса. Каждому ученику в случайном порядке достался вариант контрольной работы (от 1 до 5) (см. файл примера ), диапазон A7:B25 .

Задача

Необходимо в отдельном столбце вывести фамилии тех учеников, кому достался, например, вариант 5.

Решение



Для удобства написания формул создадим именованные диапазоны Вариант ( A7:A25 ) и Ученик ( B 7: B 25) .

По аналогии с решением из статьи Поиск текстовых значений в списках. Часть1. Обычный поиск напишем формулу массива для вывода интересующих нас значений:

=ЕСЛИОШИБКА(ИНДЕКС(Ученик; НАИМЕНЬШИЙ(ЕСЛИ(D7=Вариант;СТРОКА(Вариант)-СТРОКА($A$6);9999); СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Вариант)))));"")

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


Комментарии
Только для авторизованных пользователей

Аноним, 16 ноября 2016 г.
Добрый день! У меня тоже есть задача которую пока не могу решить. Попробую кратко описать: столбец А (нумерация позиций от 1 до 10000), Столбец B (идентификатор товара и буквы и цифры разной длины). Для некоторых товаров значения идентификаторы совпадают. Нужно в столбце С напротив каждой позиции получить перечень позиций через запятую в которых совпали идентификаторы: А B C 1 Товар1 1 2 Тор2 2, 4, 5 3 ТОРТ4 3 4 Тор2 2, 4, 5 5 Тор2 2, 4, 5 Надеюсь, понятно описал.
Михаил, 17 ноября 2016 г.
Добрый вечер, простого решения нет. Либо писать на VBA либо использовать формулы массива. Основная формула =ТРАНСП(СТРОКА($B$1:$B$5)*($B1=$B$1:$B$5)) Эту формулу нужно ввести в ячейку С1, предварительно выделив несколько ячеек в этой строке вправо. Затем нужно все ненулевые значения в 1 ячейку. При этом нужно контролировать, чтобы число повторов не было больше чем количество выделенных ячеек. Совет: попросите кого-нибудь на VBA написать макрос. Может кто и за бесплатно согласится.
Аноним, 17 ноября 2016 г.
Спасибо Михаил, скрипт у меня есть мне его создал товарищ, но расчет занимает 30 минут, я думал через формулы будет быстрее.
Михаил, 17 ноября 2016 г.
Формулы массива будут тормозить. VBA должен работать быстрее. Отключите обновление экрана в начале выполнения макроса: Application.ScreenUpdating = False
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved