Быстрый способ удаления дубликатов в Excel с обновлением и сортировкой списка

Продолжаем тему работы со списками данных в Excel. Ранее мы сравнивали два файла в Excel с помощью функции ВПР (VLOOKUP) и с помощью СУБД Access, находили и подсвечивали условным форматированием уникальные записи в списке, наконец, подсчитывали уникальные записи диапазона.

В данном материале мы очень, очень быстро удалим дубликаты или оставим уникальные записи из списка, кому как больше нравится, отсортируем список, а также, сделаем так, чтобы список с уникальными записями можно было быстро обновлять, если в основном перечне происходят изменения.

Супербыстрый способ удаления дубликатов записей

Если необходимо просто быстро удалить дубликаты из списка или определенного столбца таблицы, то сделать это можно чрезвычайно просто.

Для этого достаточно:

а) отформатировать диапазон как таблицу. Выделяем диапазон, выбираем вкладу "Главная", группу "Стили", "Форматировать как таблицу" и желаемый стиль оформления таблицы, внизу команда, где можно создать свой собственный вариант

Диапазон с повторяющимися записями

Диапазон с повторяющимися записями

б) удалить дубликаты с помощью команды "Удалить дубликаты" в группе "Инструменты" на дополнительной вкладке "Конструктор", которая появится после преобразования диапазона в таблицу

Команды дополнительной владки Конструктор

Команды дополнительной владки Конструктор

Диалоговое окно удаления дубликатов предельно простое.

Диалоговое окно удаления дубликатов

Диалоговое окно удаления дубликатов

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

Результат удаления дубликатов

Результат удаления дубликатов

Для фильтрации, равно как и сортировки списка достаточно воспользоваться выпадающим меню у заголовка нужного столбца.

Окно для фильтрации и сортировки данных

Окно для фильтрации и сортировки данных

Результат проделанной работы:

Конечный результат первого способа

Конечный результат первого способа

Такой способ подойдет, если нужно быстро убрать дубликаты записей на листе Excel, а сами изменения запечатлеть в исходном диапазоне. Если же исходный диапазон может обновляться, то каждый раз проделывать подобные действия не самый лучший способ избавиться от дубликатов и здесь на помощь придет второй способ удаления дубликатов на листе Excel.

Удаление дубликатов с помощью запросов (Power Query)

В 2016й версии Excel Power Query или создание запросов изначально входит в базовую поставку, а для версий 2013, 2010 нужно будет самостоятельно установить это бесплатную надстройку.

Первый этап удаления дубликатов с помощью Power Query такой же, как и в предыдущем способе – необходимо диапазон отформатировать как таблицу. Дальше на вкладке "Данные" в группе "Скачать и преобразовать" выбрать команду "Из таблицы". Появится окно редактора запросов, в котором, с помощью контекстного меню по заголовку столбца с нумерацией его можно удалить, а по заголовку столбца с ФИО выбрать команду "Удалить повторения", это же можно сделать и с помощью вкладки "Главная" редактора запросов.

Работа в редакторе Query Editor

Работа в редакторе Query Editor

С помощью фильтра можно скрыть пустые строки (они здесь помечены как "null") и отсортировать, при необходимости, отобранные значения, потом выбрать команду "Закрыть и загрузить/ Закрыть и загрузить в…"

Фильтрация пустых строк и сортировка в редакторе Query Editor

Фильтрация пустых строк и сортировка в редакторе Query Editor

Выбор именно второй опции позволит выбрать место, когда выгрузить модифицированный список, первая же команда сделает это на новый лист.

Выбор места для обработаного списка

Выбор места для обработаного списка

При указании диапазон, достаточно просто обозначить первую ячейку, в нашем случае это ячейка D1.

Конечный результат работы в торого способа

Конечный результат работы в торого способа

Преимуществом способа удаления дубликатов с помощью Power Query, является не только то, что исходный диапазон остается нетронутым, но и то, что, добавляя новые записи в исходный диапазон, они будут появляться в обработанном, естественно, если эти записи уникальны. Для обновления обработанного списка, нужно вызвать контекстное меню на нем и выбрать "Обновить", либо нажать "Обновить" с ленты интерфейса.