Поиск и удаление дубликатов в Microsoft Excel

|

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

Поиск и удаление

Найдите и удалите значения таблицы, которые дублируются, возможно, по-разному. В каждом из этих вариантов поиск и устранение дубликатов ссылок представляет собой единый процесс.

Способ 1: простое удаление повторяющихся строк

Самый простой способ удалить дубликаты — использовать специальную кнопку на ленте, предназначенную для этой цели.

1. Выберите весь диапазон таблицы. Перейдите на вкладку «Данные». Нажмите кнопку Удалить дубликаты. Он находится на ленте в панели инструментов Data Tools».



2. Откроется окно Удалить дубликаты. Если у вас есть таблица с заголовком (а у подавляющего большинства они всегда есть), должна стоять галочка рядом с параметром «Мои данные содержат заголовки». Основное поле окна содержит список столбцов, которые будут проверяться. Строка считается дубликатом только в том случае, если данные во всех столбцах, отмеченных галочкой, совпадают. То есть, если вы снимаете галочку с имени столбца, вы тем самым увеличиваете вероятность признания записи дубликатом. После выполнения всех необходимых настроек нажмите кнопку «ОК».



3. Excel выполняет процедуру поиска и удаления дубликатов. После ее завершения появляется информационное окно, сообщающее, сколько было удалено повторяющихся значений и сколько осталось уникальных записей. Чтобы закрыть это окно, нажмите кнопку «ОК».

Способ 2: удаление дубликатов в «умной таблице»

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

1. Выберите весь диапазон таблицы.



2. На вкладке «Главная» нажмите кнопку «Форматировать как таблицу» на ленте панели инструментов «Стили». В появившемся списке выберите любой понравившийся стиль.



3. Затем откроется небольшое окно, в котором вы должны подтвердить выбранный диапазон, чтобы сформировать «умную таблицу». Если вы выбрали все правильно, вы можете подтвердить, что если вы допустили ошибку, вам следует исправить ее в этом окне. Также важно отметить, что рядом с параметром «Таблица заголовков» стоит галочка. Если его нет, то он должен быть. После завершения всех настроек нажмите кнопку «ОК». Создан «Умный стол.



4. Но создание «умной таблицы» — это только один шаг к решению нашей главной задачи — удалению дубликатов. Щелкните любую ячейку в области таблицы. При этом появляется дополнительная группа вкладок «Работа с таблицами». Находясь во вкладке «Конструктор», нажмите на кнопку «Удалить дубликаты», которая находится на ленте в блоке инструментов «Сервис».



5. После этого откроется окно удаления дубликатов, работа над ним была подробно описана, когда вы описывали первый способ. Все дальнейшие действия выполняются точно в таком же порядке.

Этот способ является самым универсальным и функциональным из всех описанных в этой статье.

Урок: Как создать таблицу в Excel

Способ 3: применение сортировки

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

1. Выберите таблицу. Перейдите на вкладку «Данные». Нажмите на кнопку «Фильтр» в блоке настроек «Сортировка и фильтрация».



2. Фильтр включен, о чем свидетельствуют значки в виде перевернутых треугольников в названиях столбцов. Теперь мы должны настроить его. Нажмите кнопку «Дополнительно» рядом со всем в той же группе инструментов «Сортировка и фильтрация».



3. Откроется окно расширенного фильтра. Установите флажок рядом с параметром «Только уникальные записи». Все остальные настройки оставляем по умолчанию. Затем нажмите на кнопку «ОК».

После этого повторяющиеся записи будут скрыты. Но их отображение можно включить в любой момент повторным нажатием кнопки «Фильтр».

Урок: Расширенный фильтр в Excel

Способ 4: условное форматирование

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

1. Выберите область на столе. На вкладке «Главная» нажмите кнопку «Условное форматирование», расположенную в блоке настроек «Стили». В появившемся меню последовательно проходим пункты «Правила выделения» и «Повторяющиеся значения…».



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

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

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

Урок: Условное форматирование в Excel

Способ 5: применение формулы

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

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;СРАВНИТЬ(0;СЧЁТЕСЛИ(дубликат_заголовка_столбца: дубликат_адрес_заголовка_столбца (абсолютный); адрес столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес столбца;)>1;0;1);0));»»)

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



2. Вводим формулу по шаблону выше в первую свободную ячейку нового столбца. В нашем конкретном случае формула будет выглядеть так:

   =ОШИБКА(ИНДЕКС(A8:A15;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E7:$E$7;A8:A15)+ЕСЛИ(СЧЁТЕСЛИ(A8:A15;A8:A15)>1,0,1),0));»»)



3. Выберите весь столбец для дубликатов, кроме заголовка. Поместите курсор в конец строки формул. Нажмите кнопку F2 на клавиатуре. Затем набираем комбинацию клавиш Ctrl+Shift+Enter. Это связано с особенностями использования формул на массивах.

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

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

Как видите, в Excel есть множество инструментов, предназначенных для поиска и удаления дубликатов. Каждый из них имеет свои особенности. Например, условное форматирование предполагает поиск дубликатов только для каждой ячейки в отдельности. Кроме того, не все инструменты умеют не только искать, но и удалять повторяющиеся значения. Самый универсальный вариант — создать «умный стол». С помощью этого метода можно настроить поиск дубликатов максимально точно и удобно. Кроме того, их удаление является немедленным.