Дублирование данных в Excel может привести к множеству проблем с данными. Неважно, импортируете ли вы данные из любой базы данных, получаете их от коллеги или друга. Чем больше данных в вашем файле, тем сложнее найти и удалить дубликаты в Excel.
В этой статье мы более подробно рассмотрим передовые методы поиска и удаления дубликатов.
Поиск и выделение дубликатов цветом в Excel
Дубликаты в таблицах могут встречаться в разных формах. Это могут быть повторяющиеся значения в одном столбце и более, а также в одной или нескольких строках.
Поиск и выделение дубликатов цветом в одном столбце в Эксель
Самый простой способ найти и выделить дубликаты в Excel — использовать условное форматирование.
Как это сделать:
- Выделим область с данными, в которой нам нужно найти дубликаты:
- На вкладке «Главная» панели инструментов щелкните пункт меню «Условное форматирование» -> «Правила выбора ячеек» -> «Повторяющиеся значения”:
- Во всплывающем диалоговом окне выберите пункт «Дублировать» в раскрывающемся списке слева, в раскрывающемся списке справа выберите, каким цветом будут выделяться повторяющиеся значения. Нажмите кнопку «ОК”:
- Далее в выбранном столбце дубликаты будут выделены цветом:
Совет: не забудьте проверить данные таблицы на наличие лишних пробелов. Лучше использовать для этого функцию TRIM).
Поиск и выделение дубликатов цветом в нескольких столбцах в Эксель
Если вам нужно вычислить дубликаты в нескольких столбцах, процесс их вычисления такой же, как в предыдущем примере. Единственное отличие в том, что для этого нужно выбрать не один столбец, а несколько:
- Выберите столбцы с данными, в которых вы хотите найти дубликаты;
- На вкладке «Главная» панели инструментов щелкните пункт меню «Условное форматирование» -> «Правила выбора ячеек» -> «Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите пункт «Дублировать» в раскрывающемся списке слева, в раскрывающемся списке справа выберите, каким цветом будут выделяться повторяющиеся значения. Нажмите кнопку «ОК”:
- Далее дубликаты будут выделены в выбранном столбце:
Поиск и выделение цветом дубликатов строк в Excel
Поиск повторяющихся повторяющихся ячеек и целых строк данных — это разные концепции. Обратите внимание на следующие две таблицы:
Таблицы выше содержат те же данные. Их отличие в том, что в примере слева мы искали повторяющиеся ячейки, а справа мы нашли целые повторяющиеся строки с данными.
Давайте посмотрим, как найти повторяющиеся строки:
- Справа от таблицы с данными создаем вспомогательный столбец, в котором перед каждой строкой с данными помещаем формулу, объединяющую все значения строки таблицы в одну ячейку:
= A2, B2, C2 и D2
Во вспомогательном столбце вы увидите данные объединенной таблицы:
Теперь, чтобы определить повторяющиеся строки в таблице, выполните следующие действия:
- Выделите область с данными во вспомогательном столбце (в нашем примере это диапазон ячеек E2: E15);
- На вкладке «Главная» панели инструментов щелкните пункт меню «Условное форматирование» -> «Правила выбора ячеек» -> «Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите «Дублировать» в раскрывающемся списке слева, в раскрывающемся списке справа выберите цвет, которым будут выделяться повторяющиеся значения. Нажмите кнопку «ОК”:
- После этого в выбранном столбце будут выделены повторяющиеся строки:
В приведенном выше примере мы выбрали строки в созданном вспомогательном столбце.
Но что, если нам нужно выделить строки не во вспомогательном столбце, а сами строки в таблице данных?
Для этого поступаем следующим образом:
- Как и в примере выше, мы создадим вспомогательный столбец, в каждую строку которого введем следующую формулу:
= A2, B2, C2 и D2
Таким образом, мы получим собранные данные всей строки таблицы в ячейке:
- Теперь выделим все данные в таблице (кроме вспомогательного столбца). В нашем случае это ячейки диапазона A2: D15;
- Затем на вкладке «Главная» панели инструментов нажмите «Условное форматирование» -> «Создать правило”:
- В диалоговом окне «Новое правило форматирования» щелкните элемент «Использовать формулу для определения форматированных ячеек» и в поле «Значения формата, для которых истинна следующая формула» вставьте формулу:
= СЧЁТЕСЛИ ($ E $ 2: $ E $ 15, $ E2)> 1
- Не забудьте установить формат найденных повторяющихся строк.
Эта формула проверяет диапазон данных в вспомогательном столбце и выделяет повторяющиеся строки в таблице, если есть повторяющиеся строки:
Как убрать дубликаты в Excel
Выше мы узнали, как находить дубликаты и как выделять их цветом. Ниже вы узнаете, как их удалить.
Как удалить дубликаты в одном столбце Эксель
Если ваши данные находятся в столбце, и вы хотите удалить все дубликаты, сделайте следующее:
- Выделите данные;
- Переходим на панель инструментов во вкладке «Данные» -> раздел «Работа с данными» -> «Удалить дубликаты”:
- В диалоговом окне «Удалить дубликаты» установите флажок рядом с «Мои данные содержат заголовки», если выбранный диапазон столбцов имеет заголовок. Также убедитесь, что нужный столбец выделен в меню Столбцы:
- Нажмите «ОК”
Затем система удаляет все дубликаты в столбце, оставляя только уникальные значения.
Совет. Обязательно сделайте резервную копию своих данных перед любыми повторными операциями по удалению. Удаление дубликатов также может выполняться на отдельных листах, чтобы предотвратить случайное удаление данных.
Как удалить дубликаты в нескольких столбцах в Excel
Представим, что у нас есть данные о продажах, как в следующей таблице:
Строки выделены желтым цветом, где названия, регион и сумма продаж совпадают, но даты разные. Скорее всего, это связано с ошибкой при вставке данных в таблицу. Если вам нужно удалить повторяющиеся данные таблицы в отдельных столбцах, сделайте следующее:
- Выберите данные таблицы;
- Переходим на панель инструментов во вкладке «Данные» -> раздел «Работа с данными» -> «Удалить дубликаты»:
- В диалоговом окне «Удалить дубликаты» установите флажок «Мои данные содержат заголовки», если выбранный диапазон содержит заголовки. Чтобы удалить дубликаты во всех столбцах, кроме даты, оставьте поле даты пустым:
- Нажмите «ОК”
Далее система удалит строки с данными, в которых дублируются значения в столбцах «Имя», «Регион», «Продажи”.
Как удалить дублирующие строки с данными в Эксель
Если вам нужно удалить целые повторяющиеся строки в таблице, выполните следующие действия:
- Выберите данные таблицы;
- Переходим на панель инструментов во вкладке «Данные» -> раздел «Работа с данными» -> «Удалить дубликаты”:
- В диалоговом окне «Удалить дубликаты» установите флажок «Мои данные содержат заголовки», если выбранный диапазон содержит заголовки. Чтобы система могла сравнивать строки в таблице, важно убедиться, что выбраны все столбцы в диалоговом окне:
- Нажмите «ОК”
Используйте описанные выше методы для очистки данных и устранения дубликатов.
