Как удалить дубликаты в Excel?

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

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

В диапазоне данных

1. Выделить весь диапазон данных, состоящий из заполненных ячеек столбцов A, B и C.
2. Перейти на вкладку «Данные», и открыть выпадающий список инструмента «Работа с данными».
3. Щёлкнуть инструмент «Удалить дубликаты».

4. В появившемся одноимённом окне пользователь может уточнить условия такого удаления. В данном случае показываемые по умолчанию параметры менять не надо: столбцы содержат заголовки, и поиск должен производиться во всех них. Щёлкнуть OK.

5. В появившемся информационном окошке программа Excel представляет пользователю результаты поиска и удаления дубликатов. Три строки оказались полностью идентичными, и были удалены. Нажать OK.

Обратите внимание на подчёркнутую красной чертой частично повторяющуюся строку (старший специалист Иванова Тамара). Она не была удалена из-за отличающихся данных в столбце «Зарплата». Очевидно, что, если бы поиск производился по первым двум столбцам, то осталась бы только одна строка Тамары Ивановой.

В «умной» таблице

С версии 2007 в Excel появился усовершенствованный объект с особой структурой, свойствами и расширенной функциональностью, называемый «умной» таблицей. Инструмент удаления дубликатов есть, естественно, и в ней. Чтобы им воспользоваться, следует предварительно создать такую таблицу.

1. Выделить весь диапазон данных.
2. На вкладке «Главная» открыть выпадающий список инструмента «Форматировать как таблицу» и щёлкнуть на желаемом варианте формата.

3. В окошке «Форматирование таблицы» ничего менять не нужно: щёлкнуть OK.

4. Щёлкнуть любую ячейку созданной умной таблицы.
5. Перейти на появившуюся вкладку «Работа с таблицами»/»Конструктор», и щёлкнуть инструмент «Удалить дубликаты».

6. Повторить шаги 4 и 5 первого способа.

Полученный результат выглядит аналогично.

С помощью фильтрации

Для обратного преобразования умной таблицы в диапазон данных:

• выделить её;
• на вкладке «Конструктор» щёлкнуть инструмент «Преобразовать в диапазон»;

• подтвердить действие.

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

1. Выделить сканируемый диапазон данных;
2. Перейти на вкладку «Данные», и в разделе «Сортировка и фильтр» щёлкнуть инструмент «Фильтр».

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

3. Щёлкнуть инструмент «Дополнительно».
4. В появившемся окошке «Расширенный фильтр» активировать параметр «Только уникальные записи», и нажать OK.

После этого неуникальные строки скрываются. Они, однако, вновь станут видимыми, если снова щёлкнуть инструмент «Фильтр».

С помощью условного форматирования

Этот способ также нельзя назвать полноценным. С его помощью повторяющиеся строки всего лишь выделяются. После этого их придётся отдельно удалять.

1. Выделить диапазон данных.
2. На вкладке «Главная» последовательно открыть выпадающие списки «Условное форматирование», затем – «Правило выделения ячеек».
3. Щёлкнуть пункт «Повторяющиеся значения».

По умолчанию дубликаты выделяются красным цветом на розовом фоне. Но в окошке «Повторяющиеся значения» можно выбрать и другие цвета.

На мой взгляд, лучше выглядят оранжевые буквы на жёлтом фоне.

С помощью формулы

Последний сложный способ можно рекомендовать только продвинутым пользователям, имеющим опыт работы с функциями Excel. В предлагаемой формуле используются пять и них – логические ЕСЛИ и ЕСЛИОШИБКА, статистическая СЧЁТЕСЛИ, и две функции работы с массивами – ИНДЕКС и ПОИСКПОЗ.

Формула для обнаружения (но не удаления!) имеющихся дубликатов выглядит следующим образом:

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов (абсолютный; адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));"")

Последовательность действий такова.

1. Справа от диапазона данных создать пока пустой столбец с заголовком «Дубликаты».
2. Щёлкнуть верхнюю пустую ячейку (D2) этого столбца.
3. Ввести в неё формулу, взятую в красную рамку в строке формул.

4. Выделить пустые ячейки D2:D14 столбца «Дубликаты».
5. Установить курсор в конец формулы.

Следующие два шага – следствие специфики работы с массивами.

6. На клавиатуре нажать клавишу F2.
7. На клавиатуре нажать комбинацию клавиш Ctrl + Shift + Enter.

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

После её удаления можно аналогично выявить и удалить и другие дубликаты.