Как найти и создать уникальный список на основе таблицы в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек