Как в Excel скрыть повторяющиеся записи в столбцах?
Удаление дубликатов в Excel с помощью таблиц
Функции автоматической таблицы позволяют сравнивать значения и удалять дубликаты. Стоит сразу отметить, что одинаковые числовые значения в разных форматах ячеек воспринимаются в Excel как разные. Смотрите это правило для удаления дубликатов с конкретным примером.
Как удалить дубликаты в Excel
На рисунке ниже показана таблица с дублирующимися значениями. Строка 3 содержит то же значение, что и строка 6. А значение в строке 4 = строке 7. Ячейки с числами в дублированных строках имеют одинаковые значения и разные форматы. У них разное количество знаков после запятой. Выполните 2 различные операции для удаления дубликатов.
Удаление дубликатов на основе значений в текстовой колонке:
- Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке:
- Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».
- В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».
Определяется, что строки 6 и 7 являются дубликатами, поэтому они удаляются из таблицы. Если вы не выключите проверку столбцов на шаге 2, вы не сможете удалить ни одного столбца, так как все числа в столбце Цена будут считаться Excel разными.
Альтернативные способы удаления дубликатов
В Excel есть и другие инструменты для работы с дублирующимися значениями. Например:
- «Данные»-«Удалить дубликаты» — Инструмент удаляет повторяющиеся строки на листе Excel.
- «Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа.
- «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом.
- Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A.
- Формула в массиве: 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.
Примечание В связи с тем, что команда Удалить дубликаты навсегда удаляет идентичные записи, рекомендуется сделать копию исходных данных перед удалением дублирующих строк.
- Для начала выберите диапазон, в котором вы хотите удалить дубликаты. Чтобы выбрать всю таблицу, нажмите Ctrl+A .
- Далее перейдите на вкладку « ДАННЫЕ » —> группа « Работа с данными » и нажмите кнопку «Удалить дубликаты».
Удалить дубликаты в Excel – Функция Удалить дубликаты в Excel
- Откроется диалоговое окно «Удалить дубликаты». Выберите столбцы для проверки дубликатов и нажмите « ОК ».
- Чтобы удалить дубликаты строк, имеющие полностью одинаковые значения во всех столбцах, оставьте флажки рядом со всеми столбцами, как показано на изображении ниже.
- Чтобы удалить частичные дубликаты на основе одного или нескольких ключевых столбцов, выберите только соответствующие столбцы. Если в вашей таблице много столбцов, лучше сперва нажать кнопку « Снять выделение », а затем выбрать столбцы, которые вы хотите проверить на предмет дубликатов.
- Если в вашей таблице нет заголовков , уберите флаг с поля « Мои данные содержат заголовки » в правом верхнем углу диалогового окна, которое обычно выбирается по умолчанию.
Удалить дубликаты в Excel – Выбор столбца(ов), который вы хотите проверить на наличие дубликатов
Готово! После удаления каждого дублирующего ряда из выбранного диапазона отображается объяснение того, сколько дублирующих рядов было удалено и сколько уникальных значений осталось.
Удалить дубликаты в Excel – Сообщение о том, сколько было удалено дубликатов
С помощью функции Удалить дубликаты можно удалить второй и все последующие дубликаты из электронной таблицы Excel, оставив все уникальные строки и первые записи. Первая дублирующая строка включает все первые вхождения, т.е. если вы удалите все дублирующиеся ячейки. Или в другом случае, если есть две или более дублирующиеся строки и вы хотите оставить первую из них и удалить все последующие дубликаты, то используйте одно из следующих решений, описанных в этом разделе.
Удалить дубликаты, скопировав уникальные записи в другое место
Другой способ удаления дубликатов в Excel — это выделение уникальных значений и копирование их на другой рабочий лист или в выделенный диапазон на текущем рабочем листе. Давайте разберем этот метод.
- Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
- Перейдите во вкладку « ДАННЫЕ » —> группа « Сортировка и фильтр » и нажмите кнопку « Дополнительно ».
Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов
- В диалоговом окне « Расширенный фильтр » выполните следующие действия:
- Выберите пункт « скопировать результат в другое место ».
- Проверьте, отображается ли правильный диапазон в Исходном диапазоне . Это должен быть диапазон, выбранный на шаге 1.
- В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
- Выберите Только уникальные записи
Удалить дубликаты в Excel – Фильтр дубликатов
- Наконец, нажмите « ОК ». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:
Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места
При использовании этого метода новые данные получаются из указанных, но дубликаты удаляются.
Обратите внимание, что расширенный фильтр позволяет только копировать отфильтрованные значения в другие места активного рабочего листа.
Удалить дубликаты строк в Excel с помощью формул и фильтра
Excel может определить дубликаты с помощью формулы, отфильтровать их, а затем удалить дубликаты строк.
Этот подход дает вам гибкость в поиске и удалении дубликатов на основе значений в нескольких столбцах или в комбинации столбцов и строк. Недостатком является то, что вам придется запомнить несколько формул для дубликатов.
- В зависимости от вашей задачи используйте одну из следующих формул для поиска дубликатов.
Формулы для поиска дубликатов в 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 – Формула для идентификации дубликатов строк за исключением первых случаев
- Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку « Фильтр » на вкладке « ДАННЫЕ », или « Сортировка и фильтр » —> « Фильтр » на вкладке « ГЛАВНАЯ ».
Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)
- Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок « Дубликат строки ».
Удалить дубликаты в Excel – Фильтр дубликатов строки
- И, наконец, удалите дубликаты строк. Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите « Удалить строку » в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей « Delete » на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:
Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк
Теперь, когда вы знаете несколько способов удаления дубликатов из Excel, давайте их применим. Выбор способа зависит от вашей ситуации.