В работе с данными периодически возникает необходимость извлечения уникальных записей. Для этого пользователю Excel доступно множество способов удаления повторяющихся значений и фильтрации с возможностью скрытия или отображения. Каждый метод можно применять в повседневной работе или при обработке сложных статистических данных. При этом специальных знаний не требуется, достаточно минимальных знаний табличного процессора Excel.
Как удалить дубликаты в Excel
Для быстрого решения этой задачи пользователю доступна стандартная функция «Удаление дубликатов». В этом случае операцию можно провести только по выбранным полям.
Для того чтобы удалит дубликаты строк, необходимо:
- Выделить ячейки с нужной информацией.
- Перейти на закладку «Данные».
- Нажать «Удалить дубликаты». Появится информационное окно с указанием выделенного столбца.
- После выполнения, появится информация о количестве найденных и оставшихся элементов. В результате останутся только не повторяющиеся элементы.
Удаление дубликатов в умной таблице
В этом методе используется конструктор Excel (расширенный режим с дополнительными возможностями). Конструктор активируется при выделении любой ячейки табличной области. Как и в первом случае, после применения производится удаление дублированных значений.
Для тех, кто не знает: умная таблица – это вид форматирования, после использования которого все ячейки таблицы принимаю определенные свойства. При этом Excel рассматривает подобную таблицу, как единое целое, а не как набор ячеек и элементов.
Чтобы удалить повторяющиеся строки в такой таблице, следует:
- Установить курсор на любую ячейку области с записями.
- Перейти в «Конструктор».
- Выполнить операцию «Удалить дубликаты» (при необходимости откорректировать столбцы).
- Появится сообщение о количестве найденных и оставшихся дубликатов.
Используем фильтрацию
С помощью фильтрации, можно скрыть дублированные данные, при этом значения не удаляются — их можно вернуть в любой момент.
Чтобы провести форматирование и найти повторяющиеся значения таким образом следует:
- Определить необходимую область.
- На закладке «Данные» установить фильтр.
- В том же блоке сортировки и фильтрации нажать «Дополнительно» для запуска расширенного фильтра. Проверить выбранный диапазон и установить галку «Только уникальные записи».
- После выполнения процедуры, дубли будут скрыты, а фильтрации уже не будет.
- Для возврата скрытых значений, нужно нажать кнопку «Фильтр».
Условное форматирование
Этим способом можно воспользоваться для поиска одинаковых записей в ячейках. Для удобства можно настроить цвет выделения. Для реализации нужно применить предустановленное правило выделения — «Повторяющиеся значения…».
Чтобы найти дубликаты в Excel, необходимо:
- Задать область поиска.
- На главной закладке, в разделе «Условное форматирование», выбрать правило.
- Задать цвет и тип данных — уникальные или повторяющиеся.
- В результате будут выделены все повторы.
Использование формулы
С помощью встроенной функции «ПОИСКПОЗ» можно найти повторяющиеся элементы в настраиваемом диапазоне. «ПОИСКПОЗ» — возвращает относительную позицию в массиве элемента, соответствующего указанному значению с учетом указанного порядка.
Чтобы выделить повторяющиеся строки в Excel необходимо:
- Выбрать нужные ячейки.
- В разделе «Условное форматирование» создать новое правило.
- Выбрать «Использовать формулу для определения форматируемых ячеек».
- Вставить следующую формулу «=ПОИСКПОЗ(B3;$B:$B;0)<>СТРОКА(B3)» и задать формат (цвет, шрифт и т.д.).
Пояснение: В формуле, задается функция «ПОИСКПОЗ» которая ищет повторы по столбцу «B», начиная с ячейки «B3».
- Результатом будет выделение дублированных элементов в заданном формате. В будущем при изменении записей в столбце «B» (например добавление новых) заданная функция будет проверять эту запись по формуле каждый раз.
Копирование уникальных строк в новое расположение
Если требуется сохранить текущую табличную область без изменений, тогда можно применить этот способ копирования.
Для выполнения, потребуется:
- Указать необходимый диапазон.
- На закладке «Данные», в разделе «Сортировка и фильтр», выбрать «Дополнительно». Установить – «скопировать результат в другое место», «только уникальные записи» и задать адреса ячеек нового места.
- Не повторяющиеся элементы будут скопированы в новое месторасположение.
Используем сводные таблицы
Для группировки дублированных элементов можно использовать сводную таблицу (более простое понятие — обобщенная).
Для работы этого способа потребуется:
- Создать новый столбец со значением счетчика «1». Выбрать всю область.
- Используя закладку «Вставка», создать новую сводную таблицу.
- Перейти на новый лист и заполнить поля «Названия строк» и «Значения». Дубли будут подсчитаны мгновенно и отображаться будут на новом листе.
Кроме этого можно ознакомиться с видео по удалению дублей в Excel.
Удаление дубликатов в Google таблицах
В Google доступен только поиск уникальных записей в ячейках (методов удаления дублей нет):
- используя сводные таблицы (подсчет дублированных значений);
- с помощью функции unique (вывод результата из массива);
- используя сторонние расширения;
Пример использования функции «unique»:
Для вывода уникальных записей, требуется применить формулу «=unique(диапазон проверки)»: