Как найти уникальные записи в Excel: формулы УНИК / UNIQUE, SORT и ВПР / VLOOKUP
В этом руководстве мы разберем, как эффективно использовать функции УНИК / UNIQUE, SORT и ВПР / VLOOKUP для анализа данных и извлечения записей, которые встречаются в вашем наборе данных только один раз. Этот метод особенно полезен для выявления уникальных значений и работы с большими таблицами.
Столбец B содержит исполнителей, C — названия песен, A:D — полные записи. Отсортированный список одиночных исполнителей поместите в I5, ВПР — в J5. Предполагаются непустые имена без ошибок и подстановочных символов * и ?. ВПР ищет по B и возвращает C. Если одиночных исполнителей нет, УНИК(...;;ИСТИНА) вернёт #ВЫЧИСЛ!; вариант с ФИЛЬТР ниже вместо этого выводит «Не найдено».
Примеры с разливом предназначены для Microsoft 365, Excel 2021/2024 для Windows и Mac и Excel для Интернета. В Excel 2016/2019 функций УНИК / UNIQUE и ФИЛЬТР / FILTER нет. Формулы результата вводите в свободную область вне таблицы Excel.
Если вы разбираете формулы в Excel с нуля или хотите ускорить расчеты, здесь показано, как использовать функции в экселе на понятных и рабочих примерах.
Мы покажем несколько подходов к решению этой задачи.
Пошаговая инструкция
- Для начала создайте список уникальных значений, которые встречаются в вашем диапазоне только один раз. Используйте формулу:
Где третий аргументРусская версия:
=УНИК(B4:B6132; ЛОЖЬ; ИСТИНА)English version:=UNIQUE(B4:B6132, FALSE, TRUE)ИСТИНАуказывает функции вернуть только те значения, которые появляются один раз. - Чтобы отсортировать полученный список по алфавиту, оберните функцию
УНИК / UNIQUEвSORT:Русская версия:=СОРТ(УНИК(B4:B6132; ЛОЖЬ; ИСТИНА))English version:=SORT(UNIQUE(B4:B6132, FALSE, TRUE))
Использование аргумента Occurs_Once в функции УНИК / UNIQUE для поиска артистов, которые появились в базе данных только один раз. Сортировка по алфавиту с помощью =СОРТ(УНИК(B4:B6132;ЛОЖЬ;ИСТИНА)) / =SORT(UNIQUE(B4:B6132,FALSE,TRUE)). - Для получения дополнительных данных, связанных с найденными уникальными записями (например, названия песни для артиста), используйте
ВПР / VLOOKUPс массивом в качестве первого аргумента. Одна формула может выполнить множество поисков:Русская версия:=ВПР(I5#; B4:C6132; 2; ЛОЖЬ)English version:=VLOOKUP(I5#, B4:C6132, 2, FALSE)
Чтобы получить название рядом с артистом, используйте =ВПР(I5#;B4:C6132;2;ЛОЖЬ) / =VLOOKUP(I5#,B4:C6132,2,FALSE). Поскольку эти артисты появляются в базе данных только один раз, ВПР / VLOOKUP работает корректно. - Альтернативный подход — комбинация
ФИЛЬТР / FILTERиСЧЁТЕСЛИ / COUNTIFдля возврата всех столбцов исходной таблицы, соответствующих уникальным записям:Русская версия:=ФИЛЬТР(A4:D6132;СЧЁТЕСЛИ(B4:B6132;B4:B6132)=1;"Не найдено")English version:=FILTER(A4:D6132,COUNTIF(B4:B6132,B4:B6132)=1,"Не найдено")
Возврат всех четырёх столбцов для исполнителей с одним вхождением. Формула на иллюстрации использует MATCH; ниже дан вариант с подсчётом вхождений.
Советы и рекомендации
- Убедитесь, что ваш диапазон данных не содержит пустых строк или ошибок, чтобы формулы работали корректно.
- Для ссылки на весь разлив используйте
I5#. Оператор@выполняет неявное пересечение и сводит массив к одному значению; в таблице он указывает текущую строку. - Для больших наборов данных рассмотрите возможность использования
Power Queryдля более эффективной обработки.
Заключение
Сочетание функций УНИК / UNIQUE, SORT и ВПР / VLOOKUP (или ФИЛЬТР / FILTER) предоставляет мощный инструмент для анализа данных в Excel. Эти методы позволяют не только находить уникальные записи, но и извлекать связанную с ними информацию, что значительно упрощает работу с большими таблицами и повышает эффективность анализа.