Как найти и создать уникальный список из таблицы в Excel?
В процессе ежедневной обработки данных в Excel часто встречаются таблицы, содержащие повторяющуюся информацию. Например, у вас может быть таблица с перечислением классов и имен учеников, где некоторые имена учеников появляются несколько раз в одном и том же классе. Предположим, вам нужно извлечь список уникальных имен для «Класса A» только, как показано на скриншоте ниже. Какой лучший способ достичь этого? Это руководство исследует несколько практических методов, начиная с решения, основанного на VLOOKUP.
- Поиск и создание уникального списка с помощью формулы
- Используйте функцию UNIQUE для получения отфильтрованных уникальных значений (Excel 365, Excel 2021 или новее)
- Другие встроенные методы Excel - используйте Удаление дубликатов с фильтрацией
Поиск и создание уникального списка с помощью формулы
Чтобы применить этот метод, сначала выберите ячейку прямо под вашим критерием — например, если ваша ячейка критерия («Класс A») находится в D1, выберите D2. Затем введите следующую формулу массива:
=IFERROR(INDEX(B$1:B$13, MATCH(0, COUNTIF(D$1:D1, IF(A$1:A$13=D$1,B$1:B$13,D$1)),0)),"")После ввода формулы вместо простого нажатия Enter вы должны нажать Ctrl + Shift + Enter вместе, чтобы завершить её как формулу массива. После того, как первое уникальное значение появится, заполните формулу вниз, перетаскивая маркер заполнения до тех пор, пока не достигнете пустой ячейки, что будет означать, что больше нет уникальных имен для отображения.
Примечания и советы:
- B$1:B$13: Столбец, который содержит имена, которые вы хотите извлечь.
- A$1:A$13: Столбец, который содержит ваши критерии фильтрации (например, названия классов).
- D$1: Ячейка, которая содержит значение вашего фильтра, например «Класс A».
- Эта формула требует ввода как формулы массива (Ctrl + Shift + Enter). В Excel 365 и более поздних версиях формулы массива могут работать динамически только с Enter, но для совместимости со старыми версиями всегда используйте сочетание клавиш для формул массива.
- Если диапазон ваших данных изменяется, соответственно скорректируйте ссылки, чтобы избежать ошибок.
Этот метод подходит для пользователей, которым нужна совместимость со старыми версиями Excel. Однако формула довольно сложна для установки и обслуживания, особенно для новых пользователей Excel.
Для более простых решений или для обработки больших объемов данных с большей эффективностью рассмотрите также методы ниже.
Формула Excel - Используйте функцию UNIQUE для получения отфильтрованных уникальных значений
Если вы используете Excel 365, Excel 2021 или более поздние версии, вы можете воспользоваться новыми динамическими функциями массива для легкого создания уникального, мгновенно обновляемого списка на основе критериев. Здесь используются вместе функции UNIQUE и FILTER для извлечения уникальных имен для «Класса A».
- Введите следующую формулу в целевую ячейку (например, в D2):
=UNIQUE(FILTER(B2:B13, A2:A13="Class A")) - Нажмите Enter, и уникальные имена, соответствующие «Классу A», будут автоматически перечислены ниже ячейки с формулой. Список будет автоматически расширяться или сужаться при изменении ваших данных.
- В приведенной выше формуле:
- B2:B13: Диапазон имен учеников.
- A2:A13: Диапазон, содержащий названия классов.
- Вы можете заменить «Класс A» другим критерием по мере необходимости. Если вы ссылаетесь на ячейку (например, E1), используйте ="Класс A" или
=E1соответственно в формуле. - Эта функция является динамической — если вы обновите свою таблицу, уникальный список будет автоматически обновлен.
Преимущества: Автоматически обновляется вместе с вашими исходными данными, очень легко настраивается, идеально подходит для регулярно меняющихся таблиц.
Ограничения: Доступна только в Excel 365, Excel 2021 и более поздних версиях; не поддерживается в старых версиях Excel.
Другие встроенные методы Excel - используйте Удаление дубликатов с фильтрацией
Помимо подходов, основанных на формулах, вы можете воспользоваться встроенной функцией Excel Удаление дубликатов в сочетании с фильтрацией для генерации уникального списка для указанной категории. Этот метод интуитивно понятен и не требует написания каких-либо формул.
Вот как вы можете использовать этот метод для извлечения уникальных имен учеников под «Классом A»:
- Отфильтруйте данные для «Класса A»:
Выберите диапазон ваших данных (например, A1:B13). Перейдите на вкладку Данные и нажмите Фильтр. Используйте выпадающий список фильтра в столбце «Класс», чтобы показать только строки, где класс равен «Класс A». - Скопируйте отфильтрованные результаты:
Выберите только имена учеников, отображаемые после фильтрации. Скопируйте это выделение (Ctrl+C) и вставьте его в другой столбец или лист. - Удалите дубликаты:
С выделенным уникальным диапазоном, вставленным ранее, на вкладке Данные нажмите Удалить дубликаты. В появившемся диалоговом окне убедитесь, что отмечен только соответствующий столбец («Имя студента»), затем подтвердите.
Это оставит вас со списком уникальных имен студентов, соответствующих «Классу A».
- Преимущества: Не требуется знание формул, быстро и просто; подходит для одноразового извлечения данных или отчетности.
- Ограничения: Процесс ручной и требует повторного выполнения каждый раз, когда меняются ваши данные. Не подходит для автоматически обновляемых отчетов.
Понимая каждый метод и выбирая тот, который соответствует вашей версии Excel и рабочему процессу, вы сможете эффективно создавать уникальные списки, отфильтрованные по определенным критериям. Если вы столкнетесь с ошибками формул или проблемами во время операции, дважды проверьте ссылки на ячейки, убедитесь, что все данные находятся в непрерывном диапазоне, и подтвердите, что вы используете версию Excel, поддерживающую необходимые функции. Для пакетной обработки или повторяющихся задач использование встроенных функций, таких как Удаление дубликатов или динамические формулы, может значительно сэкономить время по сравнению с ручными методами.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек