Поиск и удаление повторений
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Еще…Меньше
В некоторых случаях повторяющиеся данные могут быть полезны, но иногда они усложняют понимание данных. Используйте условное форматирование для поиска и выделения повторяющихся данных. Это позволит вам просматривать повторения и удалять их по мере необходимости.
-
Выберите ячейки, которые нужно проверить на наличие повторений.
Примечание: В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.
-
На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.
-
В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК.
Удаление повторяющихся значений
При использовании функции Удаление дубликатов повторяющиеся данные удаляются безвозвратно. Чтобы случайно не потерять необходимые сведения, перед удалением повторяющихся данных рекомендуется скопировать исходные данные на другой лист.
-
Выделите диапазон ячеек с повторяющимися значениями, который нужно удалить.
-
На вкладке Данные нажмите кнопку Удалить дубликаты и в разделе Столбцы установите или снимите флажки, соответствующие столбцам, в которых нужно удалить повторения.
Например, на данном листе в столбце «Январь» содержатся сведения о ценах, которые нужно сохранить.
Поэтому флажок Январь в поле Удаление дубликатов нужно снять.
-
Нажмите кнопку ОК.
Примечание: Количество повторяющихся и уникальных значений, заданных после удаления, может включать пустые ячейки, пробелы и т. д.
Дополнительные сведения
Нужна дополнительная помощь?
Редактор таблиц MS Excel применяют в разных целях — для составления отчетов и планов, хранения данных о товарах и ценах. Если документ одновременно редактируют несколько человек или он создавался из нескольких файлов, не исключена вероятность повторения и отдельных элементов, и целых строк. Из-за такого удвоения могут появляться проблемы — неправильно считаются суммы и количество предметов.
Функция удаления дубликатов
Проще всего избавляться от повторов, доверив удаление автоматической встроенной в программу функции. Этот способ удаления повторяющихся строк в Excel самый быстрый и простой. Хотя не исключена вероятность того, что программа удалит что-то лишнее — или, наоборот, пропустит «неполные» повторения.
Использовать такую методику стоит, если пользователю нужно быстро убрать дублирующиеся данные — или если таких дубликатов слишком много.
Порядок действия для устранения повторов в таблице следующий:
- Выделить область таблицы и открыть вкладку «Данные».
- Перейти к группе команд «Работа с данными».
- Найти иконку функции удаления дубликатов, которая выглядит как два расположенных рядом цветных столбца.
- Кликнуть по ней и, если в столбцах есть заголовки, поставить галочку напротив соответствующего пункта в открывшемся окне.
- Нажать «ОК» и получить в результате файл без дубликатов.
С помощью такой методики можно удалить те строки, которые полностью совпадают друг с другом. Обычно это происходит при копировании информации из 2-3 и более файлов в одну таблицу. Но иногда возникает необходимость удалить дубли в Экселе, где информация совпадает только частично. Это может быть, например, каталог товаров, где есть одни и те же наименования с отличающимися ценами.
Простое удаление не позволит устранить повторы, если стоимость будет другой. Избежать ошибки можно, выбрав при настройке удаления дубликатов только те столбцы, которые будут сравниваться. Например, «Название» и «Марка» — но без «Цены» и «Количества».
Применение расширенного фильтра
Чтобы избежать ошибок, к которым может привести использование фильтра, перед тем, как удалить дубликаты строк в Excel, можно создать копию первоначального документа. Или выбрать использование специального расширенного фильтра, который поместит исправленную таблицу на новый лист без повторов.
Чтобы использовать фильтр, необходимо:
- Выбрать в таблице нужную область.
- Открыть вкладку «Данные» — «Сортировка» — «Дополнительно».
- В окне расширенного фильтра установить переключатель таким способом, чтобы результат его работы был скопирован в другое место.
- Выбрать в строке исходного диапазона всю таблицу или ее часть, а в строке диапазона для перемещения установить место верхнего левого угла.
- Поставить галочку напротив выбора только уникальных значений и нажать «ОК».
Старая таблица с данными остается на месте. А результат использования такого специального фильтра окажется рядом (в том числе, в новом документе или на другом листе, которые необходимо создать заранее). В новой таблице все одинаковые строки будут удалены.
Выделение повторов
Если пользователь не уверен, что программа автоматически удалит нужные строки, или в том, что она найдет все повторы построчно, можно только выделить дубликаты в Excel. А удалить их уже потом — вручную. Для этого нужно выполнить такие действия:
- Выделить таблицу, из которой удаляются повторы.
- Перейти на главной вкладке в группу стилей и выбрать условное форматирование.
- Перейти к правилам выделения ячеек и выбрать пункт «Повторяющиеся значения».
- Указать цвет текста и заливки, которыми будут выделены повторяющиеся сведения. Лучше всего сделать выделение заметно отличающимся от основного текста — так будет проще не пропустить дубликаты для удаления вручную.
После запуска этой функции программа выделит указанным способом все повторяющиеся значения в выделенной области. Причем сделает это не только для целых строк, но и для отдельных слов или фраз. А уже удалять повторы в Эксель придется самому пользователю.
Создание сводной таблицы
Избавиться от повторяющихся значений можно и с помощью сводной таблицы. Способ этот сложнее, чем две первых методики — зато подходит даже для старых версий Excel с меньшим количеством функций.
О том, как создать сводную таблицу в Excel, мы писали в отдельной статье. В кратце, для ее формирования нужно сделать следующее:
- Добавить дополнительный столбец, назвав его, например, «Счетчик», и заполнить его единицами до конца таблицы.
- На вкладке «Вставка» в группе таблиц выбрать «Сводную таблицу» и установить галочки так, чтобы она оказалась на новом листе.
- Перетащить мышкой названия всех столбцов, кроме последнего, в поле названий.
- Перетащить «Счетчик» в поле значений.
Теперь у пользователя получится сводная таблица, в которой все дубликаты будут сгруппированы вместе. Узнать о том, что среди информации есть повторы, можно по цифрам в последнем столбце. Если там будет не единица — значит, есть дубликаты.
Читайте также:
- Как сделать рамку в Ворде: 2 простых способа
- Как отключить спящий режим и гибернацию в Windows 10
Редактор направлений «Мобильные устройства» и «Техника для дома»
Skip to content
В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения.
Хотя Microsoft Excel является в первую очередь инструментом для расчетов, его таблицы часто используются в качестве баз данных для отслеживания запасов, составления отчетов о продажах или ведения списков рассылки.
Распространенная проблема, возникающая при увеличении размера базы данных, заключается в том, что в ней появляется много повторов. И даже если ваш огромный файл содержит всего несколько идентичных записей, эти несколько повторов могут вызвать массу проблем. Например, вряд ли порадует отправка нескольких копий одного и того же документа одному человеку или появление одних и тех же данных в отчете несколько раз.
Поэтому, прежде чем использовать базу данных, имеет смысл проверить ее на наличие дублирующих записей, чтобы убедиться, что вы не будете потом тратить время на исправление ошибок.
- Как вручную удалить повторяющиеся строки
- Удаление дубликатов в «умной» таблице
- Убираем повторы, копируя уникальные записи в другое место
- Формулы для удаления дубликатов
- Формулы для поиска дубликатов в столбце
- Удаление дублирующихся строк при помощи формул
- Универсальный инструмент для поиска и удаления дубликатов в Excel
В нескольких наших недавних статьях мы обсуждали различные способы выявления дубликатов в Excel и выделения неуникальных ячеек или строк (см.ссылки в конце статьи). Однако могут возникнуть ситуации, когда вы захотите в конечном счете устранить дубли в ваших таблицах. И это как раз тема этого руководства.
Удаление повторяющихся строк вручную
Если вы используете последнюю версию Microsoft Excel с 2007 по 2019, у вас есть небольшое преимущество. Эти версии содержат встроенную функцию для поиска и удаления повторяющихся значений.
Этот инструмент позволяет находить и удалять абсолютные совпадения (ячейки или целые строки), а также частично совпадающие записи (имеющие одинаковые значения в столбце или диапазоне).
Важно! Поскольку инструмент «Удалить дубликаты» навсегда удаляет идентичные записи, рекомендуется создать копию исходных данных, прежде чем удалять что-либо.
Для этого выполните следующие действия.
- Для начала выберите диапазон, в котором вы хотите работать. Чтобы выделить всю таблицу, нажмите
Ctrl + A, - Указав диапазон, перейдите на вкладку «Данные» > и нажмите кнопку «Удалить дубликаты» .
- Откроется диалоговое окно. Выберите столбцы для проверки на наличие дублей и нажмите кнопку «ОК».
- Чтобы удалить повторяющиеся строки, которые имеют абсолютно одинаковые данные во всех колонках, оставьте флажки рядом со всеми столбцами, как на скриншоте ниже.
- Чтобы удалить частичные совпадения на основе одного или нескольких ключевых столбцов, выберите только их. Если в вашей таблице много колонок, самый быстрый способ — нажать кнопку «Снять выделение». А затем отметить те, которые вы хотите проверить.
- Ежели в вашей таблице нет заголовков, снимите флажок Мои данные в верхнем правом углу диалогового окна, который обычно включается по умолчанию.
- Если указать в диалоговом окне все столбцы, строка будет удалена только в том случае, если повторяются значения есть во всех них. Но в некоторых ситуациях не нужно учитывать данные, находящиеся в определенных колонках. Поэтому для них снимите флажки. К примеру, если каждая строчка содержит уникальный идентификационный код, программа никогда не найдет ни одной повторяющейся. Поэтому флажок рядом с колонкой с такими кодами следует снять.
Выполнено! Все повторяющиеся строки в нашем диапазоне удаляются, и отображается сообщение, указывающее, сколько повторяющихся записей было удалено и сколько осталось уникальных.
Важное замечание. Повторяющиеся значения определяются по тому, что отображается в ячейке, а не по тому, что в ней записано на самом деле. Представим, что в A1 и A2 содержится одна и та же дата. Одна из них представлена в формате 15.05.2020, а другая отформатирована в формате 15 май 2020. При поиске повторяющихся значений Excel считает, что это не одно и то же. Аналогично значения, которые отформатированы по-разному, считаются разными, поэтому $1 209,32 — это совсем не одно и то же, что 1209,32.
Поэтому, для того чтобы обеспечить успешный поиск и удаление повторов в таблице или диапазоне данных, рекомендуется применить один формат ко всему столбцу.
Примечание. Функция удаления дублей убирает 2-е и все последующие совпадения, оставляя все уникальные и первые экземпляры идентичных записей.
Удаление дубликатов в «умной таблице».
Думаю, вы знаете, что, если преобразовать диапазон ячеек в таблицу, в нашем распоряжении появляется множество интересных дополнительных возможностей по работе с этими данными. Именно по этой причине такую таблицу Excel называют «умной».
Выделите нужную нам область, затем на вкладке «Главная» выберите «Форматировать как таблицу». Далее вам будет предложено указать желаемый вариант оформления. Когда закончите, автоматически откроется вкладка «Конструктор».
Выбираем на ленте нужную кнопку, как показано на скриншоте. Затем отмечаем те столбцы, в которых будем искать повторы. Ну а далее произойдет то же самое, что было описано в предыдущем разделе.
Но, в отличие от ранее рассмотренного инструмента удаления, операцию можно отменить, если что-то пошло не так.
Избавьтесь от повторов, скопировав уникальные записи в другое место.
Еще один способ удалить повторы — это выбрать все уникальные записи и скопировать их на другой лист или в другую книгу. Подробные шаги следуют ниже.
- Выберите диапазон или всю таблицу, которую вы хотите обработать (1).
- Перейдите на вкладку «Данные» (2) и нажмите кнопку «Фильтр — Дополнительно» (3-4).
- В диалоговом окне «Расширенный фильтр» (5) выполните следующие действия:
- Выберите переключатель скопировать в другое место (6).
- Убедитесь, что в списке диапазонов указан правильный диапазон. Это должен быть диапазон из шага 1.
- В поле «Поместить результат в…» (7) введите диапазон, в который вы хотите скопировать уникальные записи (на самом деле достаточно указать его верхнюю левую ячейку).
- Выберите только уникальные записи (8).
- Наконец, нажмите кнопку ОК, и уникальные значения будут скопированы в новое место:
Замечание. Расширенный фильтр позволяет копировать отфильтрованные данные в другое место только на активном листе. Например, выберите место внизу под вашими исходными данными.
Я думаю, вы понимаете, что можно обойтись и без копирования. Просто выберите опцию «Фильтровать список на месте», и дублирующиеся записи будут на время скрыты при помощи фильтра. Они не удаляются, но и мешать вам при этом не будут.
Как убрать дубликаты строк с помощью формул.
Еще один способ удалить неуникальные данные — идентифицировать их с помощью формулы, затем отфильтровать, а затем после этого удалить лишнее.
Преимущество этого подхода заключается в универсальности: он позволяет вам:
- находить и удалять повторы в одном столбце,
- находить дубликаты строк на основе значений в нескольких столбиках данных,
- оставлять первые вхождения повторяющихся записей.
Недостатком является то, что вам нужно будет запомнить несколько формул.
В зависимости от вашей задачи используйте одну из следующих формул для обнаружения повторов.
Формулы для поиска повторяющихся значений в одном столбце
Добавляем еще одну колонку, в которой запишем формулу.
Повторы наименований товаров, без учета первого вхождения:
=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B2; $B2)>1; «Дубликат»; «»)
Как видите, когда значение встречается впервые (к примеру, в B4), оно рассматривается как вполне обычное. А вот второе его появление (в B7) уже считается повтором.
Отмечаем все повторы вместе с первым появлением:
=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$17; $B2)>1; «Дубликат»; «Уникальный»)
Где A2 — первая, а A10 — последняя ячейка диапазона, в котором нужно найти совпадения.
Ну а теперь, чтобы убрать ненужное, устанавливаем фильтр и в столбце H и оставляем только «Дубликат». После чего строки, оставшиеся на экране, просто удаляем.
Вот небольшая пошаговая инструкция.
- Выберите любую ячейку и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «Данные».
- Отфильтруйте повторяющиеся строки, щелкнув стрелку в заголовке нужного столбца.
- И, наконец, удалите повторы. Для этого выберите отфильтрованные строки, перетаскивая указатель мыши по их номерам, щелкните правой кнопкой мыши и выберите «Удалить строку» в контекстном меню. Причина, по которой вам нужно сделать это вместо простого нажатия кнопки «Удалить» на клавиатуре, заключается в том, что это действие будет удалять целые строки, а не только содержимое ячейки.
Формулы для поиска повторяющихся строк.
В случае, если нам нужно найти и удалить повторяющиеся строки (либо часть их), действуем таким же образом, как для отдельных ячеек. Только формулу немного меняем.
Отмечаем при помощи формулы неуникальные строчки, кроме 1- го вхождения:
=ЕСЛИ(СЧЁТЕСЛИМН($B$2:$B2; $B2;$A$2:A2;$A2;$C$2:C2;$C2)>1; «Дубликат»; «»)
В результате видим 2 повтора.
Теперь самый простой вариант действий – устанавливаем фильтр по столбцу H и слову «Дубликат». После этого просто удаляем сразу все отфильтрованные строки.
Если нам нужно исключить все повторяющиеся строки вместе с их первым появлением:
=ЕСЛИ(СЧЁТЕСЛИМН($B$2:$B$17; $B2;$A$2:$A$17;$A2;$C$2:$C$17;$C2)>1; «Дубликат»; «»)
Далее вновь устанавливаем фильтр и действуем аналогично описанному выше.
Насколько удобен этот метод – судить вам.
Duplicate Remover — универсальный инструмент для поиска и удаления дубликатов в Excel.
В отличие от встроенной функции Excel для удаления дубликатов, о которой мы рассказывали выше, надстройка Ablebits Duplicate Remover не ограничивается только удалением повторяющихся записей. Подобно швейцарскому ножу, этот многофункциональный инструмент сочетает в себе все основные варианты использования и позволяет определять, выбирать, выделять, удалять, копировать и перемещать уникальные или повторяющиеся значения, с первыми вхождениями или без них, целиком повторяющиеся или частично совпадающие строки в одной таблице или путем сравнения двух таблиц.
Он безупречно работает во всех операционных системах и во всех версиях Microsoft Excel 2019 — 2003.
Как избавиться от дубликатов в Excel в 2 клика мышки.
Предполагая, что в вашем Excel установлен Ultimate Suite, выполните следующие простые шаги, чтобы удалить повторяющиеся строки или ячейки:
- Выберите любую ячейку в таблице, c которой вы хотите работать, и нажмите Quick Dedupe на вкладке Ablebits Data.
- Откроется диалоговое окно, и все столбцы будут выбраны по умолчанию. Выберите те, которые вам нужны, а также в выпадающем списке в правом нижнем углу укажите желаемое действие.
Поскольку моя цель – просто выделить повторяющиеся данные, я выбрал «Закрасить цветом».
Помимо выделения цветом, вам доступны и другие операции:
- Удалить дубликаты
- Выбрать дубликаты
- Указать их в столбце статуса
- Копировать дубликаты на новый лист
- Переместить на новый лист
- Нажимаем кнопку OK и оцениваем получившийся результат:
Как вы можете видеть на скриншоте выше, строки с повторяющимися значениями в первых 3 столбцах были обнаружены (первые вхождения здесь по умолчанию не считаются как дубликаты).
Совет. Если вы хотите определить повторяющиеся строки на основе значений в ключевом столбце, оставьте выбранным только этот столбец (столбцы) и снимите флажки со всех остальных неактуальных столбцов.
И если вы хотите выполнить какое-то другое действие, например, удалить повторяющиеся строки, или скопировать повторяющиеся значения в другое место, выберите соответствующий вариант из раскрывающегося списка.
Больше возможностей для поиска дубликатов при помощи Duplicate Remover.
Если вам нужны дополнительные параметры, такие как удаление повторяющихся строк, включая первые вхождения, или поиск уникальных значений, используйте мастер Duplicate Remover, который предоставляет эти и некоторые другие возможности. Рассмотрим на примере, как найти повторяющиеся значения с первым вхождением или без него.
Удаление дубликатов в Excel — обычная операция. Однако в каждом конкретном случае может быть ряд особенностей. В то время как инструмент Quick Dedupe фокусируется на скорости, Duplicate Remover предлагает ряд дополнительных опций для работы с дубликатами и уникальными значениями.
- Выберите любую ячейку в таблице, где вы хотите удалить дубликаты, переключитесь на вкладку Ablebits Data и нажмите кнопку Duplicate Remover.
- Вам предложены 4 варианта проверки дубликатов в вашем листе Excel:
- Дубликаты без первых вхождений повторяющихся записей.
- Дубликаты с 1-м вхождением.
- Уникальные записи.
- Уникальные значения и 1-е повторяющиеся вхождения.
- В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:
- Все ваши данные будут автоматически выделены.
- Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы выбираем первые 3 столбца:
- Наконец, выберите действие, которое вы хотите выполнить с дубликатами. Как и в случае с инструментом быстрого поиска дубликатов, мастер Duplicate Remover может идентифицировать, выбирать, выделять, удалять, копировать или перемещать повторяющиеся данные.
Чтобы более наглядно увидеть результат, отметим параметр «Закрасить цветом» (Fill with color) и нажимаем Готово.
Мастеру Duplicate Remover требуется совсем немного времени, чтобы проанализировать вашу таблицу и показать результат:
Как видите, результат аналогичен тому, что мы наблюдали выше. Но здесь мы выделили дубликаты, включая и первое появление этих повторяющихся записей. Если вы выберете опцию удаления, то эти 4 записи будут стерты из вашей таблицы.
Надстройка также создает резервную копию рабочего листа, чтобы случайно не потерять нужные данные: вдруг вы хотели оставить первые вхождения данных, но случайно выбрали не тот пункт.
Мы рассмотрели различные способы, которыми вы можете убрать дубликаты из ваших таблиц — при помощи формул и без них. Я надеюсь, что хотя бы одно из решений, упомянутых в этом обзоре, вам подойдет.
Все мощные инструменты очистки дублей, описанные выше, включены в надстройку Ultimate Suite для Excel. Если вы хотите попробовать их, я рекомендую вам загрузить полнофункциональную пробную версию и сообщить нам свой отзыв в комментариях.
Что ж, как вы только что видели, есть несколько способов найти повторяющиеся значения в Excel и затем удалить их. И каждый из них имеет свои сильные стороны и ограничения.
Еще на эту же тему:
Таблицы Excel – это не обычные диапазоны. Ими служит набор данных, с которым можно осуществлять самые разные действия, в том числе, и удаление дубликатов. Важно понимать, что ячейки, которые содержат одинаковые на первый взгляд значения, могут на деле оказаться разных форматов. Очевидно, что Excel может подумать, что они разные, хотя по факту они являются одинаковыми.
Чтобы убрать дубликаты ячеек в таблицах Excel, необходимо знать, как это делается. А проще всего в этом разобраться на конкретных кейсах, что мы сейчас и будем делать.
Содержание
- Удаление дубликатов с помощью таблиц
- Удаление повторяющихся значений в Excel (версии от 2007 года)
- Расширенный фильтр для удаления дубликатов
- Как выделить дубликаты с помощью условного форматирования (2007+)
- Сводные таблицы для обнаружения повторяющихся значений
- Поиск и выделение дубликатов цветом
- В одном столбце
- В нескольких столбцах
- Дубликаты строк
- Иные способы удаления дубликатов в Excel
- Удаление дубликатов в одном столбце
- Удаление дубликатов в нескольких столбцах
- Удаление дублирующих строк с данными
- Выводы
Удаление дубликатов с помощью таблиц
Для начала надо научиться создавать умные таблицы. Давайте предположим, что мы создали документ, в котором есть диапазон с дублирующимися значениями. Причем форматы этих ячеек отличаются друг от друга количеством знаков после запятой.
Устранять дубликаты можно несколькими способами.
Чтобы сделать это, базируясь на содержимом колонки с текстом, необходимо выполнить следующие действия:
- Превратить диапазон в умную таблицу. Чтобы это сделать, достаточно просто нажать комбинацию клавиш CTRL + T.
1 - Далее достаточно нажать по пункту «Удалить дубликаты», который находится на вкладке «Конструктор».
2 - Чтобы удалить дубликаты, нужно предварительно выключить проверку по четвертому столбцу (или, естественно, тому, который соответствует вашему случаю).
3 - Теперь строки с одинаковыми значениями были убраны из таблицы.
Проверку по столбцу очень важно не забыть выключить, поскольку в ином случае данные не будут удалены.
Удаление повторяющихся значений в Excel (версии от 2007 года)
Допустим, нами была создана таблица, где имеется три колонки. Они содержат ячейки с одинаковыми показаниями, которые нужно убрать. Первый шаг – выделение той области, где есть повторяющиеся ячейки. Есть возможность удалить выделить самые разные диапазоны, включая отдельные столбцы или целую таблицу.
Затем делаем левый клик мышью по вкладке «Данные», и на ней находится кнопка «Удалить дубликаты».
Может быть так, что в таблице есть строка с заголовками. В этом случае нужно поставить соответствующий флажок в открывшемся окне. В нем также нужно выбрать те столбцы, в которых надо искать повторяющиеся значения.
После того, как клавиша ОК будет нажата, все строчки, содержащие дубликаты, убираются.
С помощью этой функции можно удалять ячейки, дублирующие ряды, в полной мере. Впрочем, если была выделена только часть колонок, то все равно уберутся строки, в которых записаны повторяющиеся значения.
Расширенный фильтр для удаления дубликатов
Есть еще один метод удаления ячеек, содержащих одинаковую информацию – расширенный фильтр. Чтобы его применить, нужно выбрать какую-угодно ячейку, находящуюся внутри требуемого диапазона, после чего открыть вкладку «Данные». Нас там интересует кнопка «Дополнительно».
После того, как она будет нажата, откроется окно, где следует выставить опцию «Скопировать результат в другое место», а там, где нужно записать исходный диапазон, надо его указать. Также в этом окошке есть поле «Поместить результат в диапазон», с помощью которого можно задать первую ячейку для будущего диапазона. После этого нужно включить отображение лишь уникальных значений, после чего подтвердить свои действия.
Теперь перед нами появляется таблица с отфильтрованными данными. В ней будут отображаться лишь те значения, которые являются уникальными. Мы так сделали с помощью соответствующей кнопки в предыдущем шаге.
Важно! Этот способ не убирает повторы полностью, а просто их скрывает.
Как выделить дубликаты с помощью условного форматирования (2007+)
Можно также осуществить выделение всех имеющихся дубликатов и убрать их вручную. Для этого можно воспользоваться функцией, которая называется условным форматированием. В качестве первого шага служит выделение таблицы, где нужно осуществить поиск повторяющихся значений. Для этого необходимо найти группу «Стили» на вкладке «Главная», и там найти «Условное форматирование».
Далее следует выбрать непосредственно критерии, по которым будет выделение осуществляться. Нас интересует правило «Повторяющиеся значения».
После этого появится окошко, где можно осуществить более гибкую настройку.
Важно учитывать, что в этом случае форматироваться будет исключительно определенная ячейка колонки, а не строки. Поэтому может быть ситуация, когда повторяющиеся значения одного столбца будут залиты Excel, хотя они не являются уникальными.
Наглядно можно этот эффект увидеть на этом скриншоте.
Сводные таблицы для обнаружения повторяющихся значений
Сводная таблица – это еще один инструмент Excel, использование которого возможно для удаления дублей. Давайте откроем таблицу, которая находится выше (ту, которая с тремя столбцами, и сделаем еще один). Назовем его «Счетчик». В качестве содержимого ячеек будем использовать единицу. После этого вся таблица выделяется. Далее ищем кнопку «Сводная таблица». Найти ее можно на вкладке «Вставка».
Далее нужно настроить таблицу. Сперва открывается поле «Название строк», где размещается три столбца. Затем в качестве содержимого поля «Значения» выбираем колонку, в которой будет располагаться счетчик.
После этого будет сгенерирована таблица, где те записи, которые содержат число, превышающее единицу, будут обозначать дубликаты. При этом сама цифра будет показывать, сколько значений повторяется. Чтобы было более понятно, давайте сделаем сортировку значений по колонке «Счетчик», чтобы все дубликаты находились вместе.
Поиск и выделение дубликатов цветом
Чтобы выделить дубликаты на фоне других ячеек каким-то цветом, надо использовать условное форматирование. Этот инструмент имеет множество функций, в том числе, и возможность выставлять цвет для обнаруженных дубликатов.
В одном столбце
Условное форматирование – это наиболее простой способ определить, где находятся дубликаты в Excel и выделить их. Что нужно сделать для этого?
- Найти ту область поиска дубликатов и выделить ее.
13 - Переключить свой взор на Панель инструментов, и там развернуть вкладку «Главная». После нажатия на эту кнопку появляется набор пунктов, и нас, как уже было понятно исходя из информации выше, интересует пункт «Повторяющиеся значения».
14 - Далее появляется окно, в котором нужно выбрать пункт «Повторяющиеся» и нажать на клавишу ОК.
15
Теперь дубликаты подсвечены красным цветом. После этого нужно их просто удалить, если в этом есть необходимость.
В нескольких столбцах
Если стоит задача определить дубликаты, расположенные больше, чем в одной колонке, то принципиальных отличий от стандартного использования условного форматирования нет. Единственная разница заключается в том, что необходимо выделить несколько столбцов.
Последовательность действий, в целом, следующая:
- Выделить колонки, в которых будет осуществляться поиск дубликатов.
- Развернуть вкладку «Главная». После этого находим пункт «Условное форматирование» и выставляем правило «Повторяющиеся значения» так, как это было описано выше.
- Далее снова выбираем пункт «Повторяющиеся» в появившемся окошке, а в списке справа выбираем цвет заливки. После этого кликаем по «ОК» и радуемся жизни.
16
Дубликаты строк
Важно понимать, что между поиском дублей ячеек и строк есть огромная разница. Давайте ее рассмотрим более подробно. Посмотрите на эти две таблицы.
Характерная особенность тех таблиц, которые были приведены выше, заключается в том, что в них приводятся одни и те же значения. Все потому, что в первом примере осуществлялся поиск дубликатов ячеек, а во втором видим уже повторение строк с информацией.
Итак, что нужно сделать для поиска повторяющихся значений в рядах?
- Создаем еще одну колонку в правой части по отношению к таблице с исходной информацией. В нем записывается формула, которая выводит объединенную информацию со всех ячеек, входящих в состав строки. =A2&B2&C2&D2
- После этого мы увидим информацию, которая была объединена.
19 - После этого следует выбрать дополнительную колонку (а именно, те ячейки, которые содержат объединенные данные).
- Далее переходим на «Главная», а затем снова выбираем пункт «Повторяющиеся значения» аналогично описанному выше.
- Далее появится диалоговое окно, где снова выбираем пункт «Повторяющиеся», а в правом перечне находим цвет, с использованием которого будет осуществляться выделение.
После того, как будет нажата кнопка «ОК», повторы будут обозначены тем цветом, который пользователь выбрал на предыдущем этапе.
Хорошо, предположим, перед нами стоит задача выбрать те строки, которые располагаются в исходном диапазоне, а не по вспомогательной колонке? Чтобы это сделать, нужно предпринять следующие действия:
- Аналогично предыдущему примеру, делаем вспомогательную колонну, где записываем формулу объединения предыдущих столбцов. =A2&B2&C2&D2
- Далее мы получаем все содержащиеся в строке значения, указанные в соответствующих ячейках каждой из строк.
20 - После этого осуществляем выделение всей содержащиеся информации, не включая дополнительный столбец. В случае с нами это такой диапазон: A2:D15. После этого переходим на вкладку «Главная» и выбираем пункт «Условное форматирование» – создать правило (видим, что последовательность немного другая).
21 - Далее нас интересует пункт «Использовать формулу для определения форматируемых ячеек», после чего вставляем в поле «Форматировать значения, для которых следующая формула является истинной», такую формулу. =СЧЁТЕСЛИ($E$2:$E$15;$E2)>1
22
Для дублированных строк обязательно установить правильный формат. С помощью приведенной выше формулы можно осуществить проверку диапазона на предмет наличия повторов и выделить их определенным пользователем цветом в таблице.
Иные способы удаления дубликатов в Excel
Итак, давайте подведем некоторые итоги касаемо того, как удалять повторы в Excel в разных ситуациях. Начем с повторений в одной колонке.
Удаление дубликатов в одном столбце
Если вся нужная информация находится в одной колонке, а требуется удаление повторяющихся данных, то нужно выполнить такие шаги:
- Выделить нужный диапазон.
- Развернуть вкладку «Данные» на Панели инструментов и найти кнопку «Удалить дубликаты».
24 - После этого появится окно, где возле пункта «Мои данные содержат заголовки» ставится флажок, если в соответствующем диапазоне заголовок имеется. Кроме этого, надо посмотреть на меню «Колонны», чтобы убедиться, что галочка стоит возле нужного столбца.
25
После того, как будет нажата кнопка «Ок», системой будут удалены все копии в столбце.
Удаление дубликатов в нескольких столбцах
Представим, что у нас есть таблица, в которой имена, сумма продаж и регион совпадают, но при этом отличаются даты.
Это может произойти, если при вводе информации в таблицу была допущена ошибка. Если необходимо удалить такие повторения, то нужно осуществить следующие действия:
- Выделить информацию в таблице.
- Развернуть вкладку «Данные», там отыскать часть инструментов, обозначенную заголовком «Работа с данными», после чего кликнуть на знакомую нам кнопку «Удалить дубликаты». Дальше действия осуществляются аналогично, но при нужно не ставить флажок возле столбца с датой.
27
После этого все дубли будут убраны системой.
Удаление дублирующих строк с данными
Чтобы автоматически убрать все повторения строк, нужно выполнить такие действия:
- Выделить часть ячеек, где нужно убрать дубли.
- После этого найти функцию «Удалить дубликаты» и выполнять те же действия, что описаны выше. Но в этом случае надо убедиться в том, что все флажки стоят. Тогда Эксель автоматически проверит всю информацию на предмет наличия в ней повторяющейся.
28
После нажатия ОК мы получаем нужный нам результат.
Выводы
Таким образом, в арсенале Excel есть огромное количество способов, с помощью которых можно скрыть дубли. Не во всех ситуациях нужно удалять их, нередко достаточно просто их спрятать или отметить цветом.
Оцените качество статьи. Нам важно ваше мнение:
Мы все знаем, что большие наборы данных лучше всего сохранять в электронных таблицах, т.к тогда мы обеспечены замечательным инструментом обработки информации. Рассмотрим, как можно исключить повторяющуюся в строках информацию. В файлах MS Excel дубликаты встречаются тогда, когда мы создаем составную таблицу из других таблиц, или когда разные пользователи работают с одним файлом, наполняя таблицу однотипной информацией. MS Excel предлагает несколько вариантов исключения или выявления повторяющейся информации, а именно: поиск, выделение и, при необходимости, удаление повторяющихся значений. Рассмотрим подробно каждое из действий на примере MS Excel 2007.
1. Удаление повторяющихся значений в Excel
У вас таблица, состоящая из двух столбцов, в которой присутствуют одинаковые записи, и вам необходимо избавиться от них. Устанавливаем курсор внутрь области, в которой хотите удалить повторяющиеся значения. Открываем вкладку Данные, в группе Работа с данными, левой клавишей мыши нажимаем на командную кнопку Удалить дубликаты.
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов. На рисунке данные элементы выделены цветной рамочкой.
Далее подтверждаем выбор, нажимая на командную кнопку «ОК». Строки, содержащие дубликаты, будут удалены, и на экране появится сообщение о количестве удаленных записей и количестве уникальных записей.
ИТОГ: Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице.
Теперь рассмотрим возможность сохранить исходный текст и получить текст без информации, которая содержит дубль. Для этого воспользуемся фильтром.
2. Расширенный (дополнительный) фильтр для удаления дубликатов
Выберите столбец таблицы, который содержит повторяющуюся информацию, перейдите на вкладку Данные и далее в группу Сортировка и фильтр, щелкните левой клавишей мыши по кнопке Дополнительно.
В появившемся диалоговом окне Расширенный фильтр необходимо установить переключатель в строке Скопировать результат в другое место и указать необходимый диапазон (в нашем случае столбец) в поле Исходный диапазон, в поле Поместить результат укажите диапазон, куда будет помещен результат фильтрации, и установите маркер Только уникальные значения. Подтверждаем установленные команды командной кнопкой ОК.
На месте, указанном для размещения результатов работы расширенного фильтра, будет создан еще один столбец, но уже с уникальными значениям; в нашем случае столбец с авторами произведений.
3. Условное форматирование в Excel
Выделите таблицу, которая содержит повторяющиеся значения. Во вкладке Главная перейдите в группу Стили, выберите Условное форматирование, далее Правила выделения ячеек и в них – Повторяющиеся значения.
В открывшемся диалоговом окне Повторяющиеся значения выберите формат выделения повторяющихся записей. По умолчанию в MS Excel установлена светло-красная заливка и темно-красный цвет текста. В данном случае Excel будет сравнивать на уникальность не всю строку таблицы, а лишь ячейку столбца, поэтому для нас, отслеживающих повторяющиеся записи только в одном столбце, это удобно. На рисунке вы можете увидеть, как Excel залил некоторые ячейки столбца с фамилиями авторов книг, хотя вся строка данной таблицы уникальна.
ИТОГ: Для больших массивов информации данный метод не дает четкой картины. Все фамилии авторов книг, у которых есть повторения, выделены одинаковым цветом.
4. Сводные таблицы для определения повторяющихся значений
В таблице со списком авторов и наименованием произведений данных авторов и добавьте столбец Порядковый номер перед фамилиями авторов и столбец Счетчик после наименования произведений. Заполните столбец Счетчик единицами (1), а столбец Порядковый номер – порядковыми номерами авторов и их произведениями. Выделите всю таблицу и перейдите на вкладку Вставка в группу Таблицы. Используя левую клавишу мыши, щелкните по кнопке Сводная таблица. В открывшемся окне установите флажки так, как указано на рисунке, т.е. сводную таблицу мы размещаем на новый лист.
Не забудьте нажать на командную кнопку ОК и продолжить формирование сводной таблицы на новом листе. В окне Список полей сводной таблицы установите флажки во всех полях, так как указано на рисунке. При этом выбранные наименования полей появятся в окнах Название строк и å Значения. Перетащите поле п/п в столбец Названия строк.
В поле Значения должен остаться столбец со счетчиком. По мере установки и размещения строк в окне Список полей сводной таблицы материал исходной таблицы будет меняться. В созданной сводной таблице записи со значением больше единицы будут дубликатами, само значение будет означать количество повторяющихся значений. Для большей наглядности можно отсортировать таблицу по столбцу Счетчик, чтобы сгруппировать дубликаты.
Материал подготовлен Л.А. Шутилиной, методистом ГМЦ ДОгМ