Как в Excel скрыть повторяющиеся записи в столбцах?

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

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

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

На рисунке ниже показана таблица с дублирующимися значениями. Строка 3 содержит то же значение, что и строка 6. А значение в строке 4 = строке 7. Ячейки с числами в дублированных строках имеют одинаковые значения и разные форматы. У них разное количество знаков после запятой. Выполните 2 различные операции для удаления дубликатов.

Удаление дубликатов на основе значений в текстовой колонке:

1. Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке:
2. Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».
3. В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».

Определяется, что строки 6 и 7 являются дубликатами, поэтому они удаляются из таблицы. Если вы не выключите проверку столбцов на шаге 2, вы не сможете удалить ни одного столбца, так как все числа в столбце Цена будут считаться Excel разными.

Альтернативные способы удаления дубликатов

В Excel есть и другие инструменты для работы с дублирующимися значениями. Например:

1. «Данные»-«Удалить дубликаты» — Инструмент удаляет повторяющиеся строки на листе Excel.
2. «Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа.
3. «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом.
4. Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A.
5. Формула в массиве: 1;0;1);0));»»)’ >. Формула ищет одинаковые наименования в диапазоне A2:A13 и выводит их в отдельный список столбца B (формулу нужно скопировать в диапазон B2:B13). Обратите внимание, что формула отображается в фигурных скобках <>, а значит она выполняется в массиве. Поэтому ее нужно вводить комбинацией горячих клавиш CTRL+SHIFT+Enter.

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

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

В этой статье мы рассмотрим, как удалить дубликаты в Excel 2007, Excel 2010, Excel 2013 и Excel 2016. Будет рассмотрено несколько различных методов обнаружения и удаления дубликатов в Excel, включая поиск и удаление дубликатов строк, поиск и удаление дубликатов столбцов, а также обнаружение абсолютных и частичных совпадений. Среди тем, затронутых в статье, следующие:

Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»

Если вы используете последними версиями Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов – функцию Удалить дубликаты.

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

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

1. Для начала выберите диапазон, в котором вы хотите удалить дубликаты. Чтобы выбрать всю таблицу, нажмите Ctrl+A .
2. Далее перейдите на вкладку « ДАННЫЕ » —> группа « Работа с данными » и нажмите кнопку «Удалить дубликаты».

Удалить дубликаты в Excel – Функция Удалить дубликаты в Excel

1. Откроется диалоговое окно «Удалить дубликаты». Выберите столбцы для проверки дубликатов и нажмите « ОК ».

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

Удалить дубликаты в Excel – Выбор столбца(ов), который вы хотите проверить на наличие дубликатов

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

Удалить дубликаты в Excel – Сообщение о том, сколько было удалено дубликатов

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

Удалить дубликаты, скопировав уникальные записи в другое место

Другой способ удаления дубликатов в Excel — это выделение уникальных значений и копирование их на другой рабочий лист или в выделенный диапазон на текущем рабочем листе. Давайте разберем этот метод.

1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
2. Перейдите во вкладку « ДАННЫЕ » —> группа « Сортировка и фильтр » и нажмите кнопку « Дополнительно ».

Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов

1. В диалоговом окне « Расширенный фильтр » выполните следующие действия:

• Выберите пункт « скопировать результат в другое место ».
• Проверьте, отображается ли правильный диапазон в Исходном диапазоне . Это должен быть диапазон, выбранный на шаге 1.
• В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
• Выберите Только уникальные записи

Удалить дубликаты в Excel – Фильтр дубликатов

1. Наконец, нажмите « ОК ». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:

Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места

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

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

Удалить дубликаты строк в Excel с помощью формул и фильтра

Excel может определить дубликаты с помощью формулы, отфильтровать их, а затем удалить дубликаты строк.

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

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

Формулы для поиска дубликатов в 1 столбце

• Дубликат, кроме первого появления:

= ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; $A2)>1; «Дубликат»; «»)

• Дубликат 1-го появления:

( Счет($A$2:$A$10, $A2)>1; «Дублированный»; «Уникальный»)

Где A2 — первая ячейка, а A10 — последняя ячейка диапазона, в котором будет производиться поиск дубликатов.

Формулы для поиска дубликатов строк

• Дублирование строк, за исключением первого появления. Это означает, что если в результате будут найдены две или более одинаковых строк, первая будет помечена как уникальная, а все последующие — как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;»Дубликат строки»; «Уникальное»)

• Дублирование строк с 1-го появления. В этом случае, если в результатах поиска будут найдены две или более одинаковые строки, они будут помечены как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)

Где A, B и C — столбцы, которые необходимо проверить на наличие дубликатов.

Например, так можно определить дубликаты строк, за исключением 1-го вхождения:

Удалить дубликаты в Excel – Формула для идентификации дубликатов строк за исключением первых случаев

1. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку « Фильтр » на вкладке « ДАННЫЕ », или « Сортировка и фильтр » —> « Фильтр » на вкладке « ГЛАВНАЯ ».

Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)

1. Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок « Дубликат строки ».

Удалить дубликаты в Excel – Фильтр дубликатов строки

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

Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк

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