Как удалить дубликаты в Excel при помощи таблиц

Старт:14.12.2024

Срок обучения:2 месяца

Excel: базовый курс

Обучение Excel в Нетологии с нуля до профессионала. Освойте сводные таблицы, формулы и функции. Практический курс с сертификацией и примерами из реальной жизни. Запишитесь уже сегодня!

15 750 ₽22 500 ₽

1 312₽/мес рассрочка

Подробнее

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

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

Удаление дубликатов с помощью таблиц

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

Есть несколько способов избавиться от дубликатов.

Для этого на основе содержимого текстового столбца необходимо сделать следующее:

1. Превратите диапазон в умный стол. Для этого достаточно нажать комбинацию клавиш CTRL + T. 
2. Затем нажмите на элемент «Удалить дубликаты», который находится во вкладке «Конструктор».
3. Чтобы удалить дубликаты, вы должны сначала отключить проверку четвертого столбца (или, конечно, ту, которая вам подходит).
4. Теперь из таблицы удалены строки с такими же значениями.

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

Удаление повторяющихся значений в Excel (версии от 2007 года)

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

Затем мы щелкаем левой кнопкой мыши вкладку «Данные», и на ней есть кнопка «Удалить дубликаты».

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

После нажатия кнопки ОК все строки, содержащие дубликаты, удаляются. 

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

Расширенный фильтр для удаления дубликатов

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

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

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

Важно! Этот метод не удаляет полностью повторы, он просто скрывает их.

Как выделить дубликаты с помощью условного форматирования (2007+)

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

Далее следует непосредственно выбрать критерии, по которым будет производиться выбор. Нас интересует правило «Повторяющиеся значения».

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

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

Вы можете ясно увидеть этот эффект на этом скриншоте.

Сводные таблицы для обнаружения повторяющихся значений

Сводная таблица - еще один инструмент Excel, который можно использовать для удаления дубликатов. Откроем таблицу выше (ту, что с тремя столбцами, и сделаем еще одну). Назовем это «Счетчик». Мы будем использовать один как содержимое ячейки. Далее выбирается вся таблица. Далее мы ищем кнопку «Сводная таблица». Вы можете найти его во вкладке «Вставка».

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

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

Поиск и выделение дубликатов цветом

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

В одном столбце

Условное форматирование - это самый простой способ определить дубликаты в Excel и выделить их. Что для этого нужно сделать?

1. Найдите эту повторяющуюся область поиска и выделите ее.
2. Переместите взгляд на панель инструментов и разверните там вкладку «Главная». После нажатия на эту кнопку появляется набор элементов, и, как уже было ясно из приведенной выше информации, нас интересует элемент «Повторяющиеся значения».
3. Затем появляется окно, в котором нужно выбрать пункт «Дублировать» и нажать кнопку ОК.

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

В нескольких столбцах

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

Последовательность действий в целом следующая:

1. Выберите столбцы, в которых будет производиться поиск дубликатов.
2. Разверните вкладку «Главная». Затем мы находим элемент «Условное форматирование» и устанавливаем правило «Повторяющиеся значения», как описано выше. 
3. Затем снова выберите пункт «Дублировать» в появившемся окне и в списке справа выберите цвет заливки. Далее нажимаем «ОК» и наслаждаемся жизнью.

Дубликаты строк

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

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

Итак, что вам нужно сделать, чтобы найти повторяющиеся значения в строках?

1. Создадим еще один столбец справа по отношению к таблице с исходной информацией. Записывает формулу, отображающую объединенную информацию обо всех ячейках, составляющих строку. = A2, B2, C2 и D2
2. Далее мы посмотрим на информацию, которая была объединена.
3. Затем вы должны выбрать дополнительный столбец (т.е те ячейки, которые содержат объединенные данные).
4. Затем перейдите к «Основному», затем снова выберите «Повторяющиеся значения» таким же образом, как описано выше. 
5. Впоследствии появится диалоговое окно, в котором мы снова выбираем пункт «Дублировать», а в списке справа находим цвет, которым будет сделан выбор. 

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

Хорошо, предположим, вам нужно выбрать те строки, которые находятся в исходном диапазоне, а не во вспомогательном столбце? Для этого вам необходимо выполнить следующие действия:

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

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

Иные способы удаления дубликатов в Excel

Итак, давайте подведем итоги того, как удалять дубликаты в Excel в разных ситуациях. Начнем с повторов в один столбик.

Удаление дубликатов в одном столбце

Если вся необходимая информация находится в одном столбце и вам нужно удалить повторяющиеся данные, вам необходимо выполнить следующие действия:

1. Выберите желаемый интервал. 
2. Разверните вкладку «Данные» на панели инструментов и найдите кнопку «Удалить дубликаты». 24
3. Впоследствии появится окно, в котором рядом с пунктом «Мои данные содержат заголовки» стоит галочка, если в соответствующем диапазоне есть заголовок. Также вам нужно посмотреть в меню «Столбцы», чтобы убедиться, что флажок установлен рядом с нужным столбцом.

После нажатия кнопки «Ок» система удалит все копии в столбце.

Удаление дубликатов в нескольких столбцах

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

Это может произойти, если при вводе информации в таблицу была допущена ошибка. Если вам нужно убрать такие повторы, вам нужно сделать следующее:

1. Выделите информацию в таблице. 
2. Разверните вкладку «Данные», найдите там некоторые инструменты, обозначенные заголовком «Работа с данными», затем нажмите знакомую кнопку «Удалить дубликаты». Дальнейшие действия производятся аналогично, но если вам не нужно ставить галочку напротив столбца с датой.

После этого все дубликаты будут удалены из системы. 

Удаление дублирующих строк с данными

Чтобы автоматически удалить все повторы строк, вам необходимо сделать следующее:

1. Выделите часть ячеек, где вы хотите удалить дубликаты. 
2. Затем найдите функцию «Удалить дубликаты» и выполните те же действия, которые описаны выше. Но в этом случае нужно убедиться, что все флажки установлены. Затем Excel автоматически проверит всю информацию на наличие в ней дубликатов.

После нажатия ОК получаем нужный нам результат.

Выводы

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