У Кеваля за столом более 5000 студентов, распределенных по 22 курсам обучения. Напротив имени каждого студента в каждой строке этой таблицы указывается курс, на который он зарегистрирован. Кеваль хотел бы на другом листе поместить название курса в ячейку A1, а затем отобразить в Excel, начиная со строки 3, всех студентов этого курса, как они извлечены из списка. Он задается вопросом, есть ли способ сделать это с помощью формулы.

Лучшее решение этой проблемы во многом будет зависеть от того, как организованы ваши исходные данные. Если у вас есть только два столбца (имя студента и название курса), возможно, вам даже не нужно использовать второй рабочий лист. Вместо этого вы можете использовать возможности фильтрации Excel. Просто отфильтруйте содержимое столбца, содержащего название курса, и вы можете легко ограничить то, что отображается, только для учащихся курса.

Если вам необходимо, чтобы информация отображалась на втором листе, может быть лучше использовать формулу массива для извлечения учащихся. Поместите желаемый курс в ячейку A1, а затем следующую формулу в ячейку A3:

=IF(COUNTIF(Sheet1!$B$1:$B$5000,$A$1)<ROW()-2,"", INDEX(Sheet1!$A$1:$A$5000,SMALL(IF(Sheet1!$B$1:$B$5000=$A$1, ROW(Sheet1!$B$1:$B$5000)),ROW()-2)))

Помните — это единственная формула, и вам нужно ввести ее в ячейку, нажав Ctrl + Shift + Enter. Скопируйте формулу вниз в достаточное количество ячеек, чтобы она могла вместить самый большой класс. Формула также предполагает, что исходные данные находятся в строках с 1 по 5000; если это не так, вы захотите изменить формулу, чтобы отразить соответствующий диапазон.

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

Если с вашими исходными данными связана дополнительная информация (например, оценки, адреса, назначения книг и т. Д.), Вы можете рассмотреть возможность работы с реальной программой базы данных, такой как Access. Вы сможете манипулировать данными и извлекать их с помощью инструментов, которые превосходят инструменты Excel.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12346) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Compiling_a_List_of_Students_in_a_Course [Составление списка студентов в курсе].