Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
В сегодняшних Excel файлах дубликаты встречаются повсеместно. К примеру, когда вы создаете составную таблицу из других таблиц, вы можете обнаружить в ней повторяющиеся значения, или в файле с общим доступом внесли одинаковые данные два разных пользователя, что привело к задвоению и т.д. Дубликаты могут возникнуть в одном столбце, в нескольких столбцах или даже во всем листе. В Microsoft Excel реализовано несколько инструментов поиска, выделения и, при необходимости, удаления повторяющихся значений. Ниже описаны основные методики определения дубликатов в Excel.
1. Удаление повторяющихся значений в Excel (2007+)
Предположим, у вас имеется таблица, состоящая из трех столбцов, в которой присутствуют одинаковые записи и вам необходимо избавится от них. Выделяем область таблицы, в которой хотите удалить повторяющиеся значения. Вы можете выделить один или несколько столбцов, или всю таблицу целиком. Переходим по вкладке Данные в группу Работа с данными, щелкаем по кнопке Удалить дубликаты.
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.
Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.
Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице. Если вы выделили не все столбцы для определения дубликатов, строки с повторяющимися значениями также будут удалены.
2. Использование расширенного фильтра для удаления дубликатов
Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.
В появившемся диалоговом окне Расширенный фильтр, необходимо установить переключатель в положение скопировать результат в другое место, в поле Исходный диапазон указать диапазон, в котором находится таблица, в поле Поместить результат в диапазон указать верхнюю левую ячейку будущей отфильтрованной таблицы и установить маркер Только уникальные значения. Щелкаем ОК.
На месте, указанном для размещения результатов работы расширенного фильтра, будет создана еще одна таблица, но уже с отфильтрованными, по уникальным значениям, данными.
3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)
Выделяем таблицу, в которой необходимо обнаружить повторяющиеся значения. Переходим по вкладке Главная в группу Стили, выбираем Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.
В появившемся диалоговом окне Повторяющиеся значения, необходимо выбрать формат выделения дубликатов. У меня по умолчанию установлено светло-красная заливка и темно-красный цвет текста. Обратите внимание, в данном случае Excel будет сравнивать на уникальность не всю строку таблицы, а лишь ячейку столбца, поэтому если у вас имеются повторяющиеся значения только в одном столбце, Excel отформатирует их тоже. На примере вы можете увидеть, как Excel залил некоторые ячейки третьего столбца с именами, хотя вся строка данной ячейки таблицы уникальна.
4. Использование сводных таблиц для определения повторяющихся значений
Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названием Счетчик, и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке Вставка в группу Таблицы, щелкаем по кнопке Сводная таблица.
Создаем сводную таблицу. В поле Название строк помещаем три первых столбца, в поле Значения помещаем столбец со счетчиком. В созданной сводной таблице, записи со значением больше единицы будут дубликатами, само значение будет означать количество повторяющихся значений. Для большей наглядности, можно отсортировать таблицу по столбцу Счетчик, чтобы сгруппировать дубликаты.
Удаление повторяющихся строк в Эксель: самые быстрые методы
Чтобы избежать ошибок при работе с таблицами, надо уметь удалять дубликаты в Excel. Для этого существует несколько способов.
Редактор таблиц MS Excel применяют в разных целях — для составления отчетов и планов, хранения данных о товарах и ценах. Если документ одновременно редактируют несколько человек или он создавался из нескольких файлов, не исключена вероятность повторения и отдельных элементов, и целых строк. Из-за такого удвоения могут появляться проблемы — неправильно считаются суммы и количество предметов.
Функция удаления дубликатов
Проще всего избавляться от повторов, доверив удаление автоматической встроенной в программу функции. Этот способ удаления повторяющихся строк в Excel самый быстрый и простой. Хотя не исключена вероятность того, что программа удалит что-то лишнее — или, наоборот, пропустит «неполные» повторения.
Использовать такую методику стоит, если пользователю нужно быстро убрать дублирующиеся данные — или если таких дубликатов слишком много.
Порядок действия для устранения повторов в таблице следующий:
- Выделить область таблицы и открыть вкладку «Данные».
- Перейти к группе команд «Работа с данными».
- Найти иконку функции удаления дубликатов, которая выглядит как два расположенных рядом цветных столбца.
- Кликнуть по ней и, если в столбцах есть заголовки, поставить галочку напротив соответствующего пункта в открывшемся окне.
- Нажать «ОК» и получить в результате файл без дубликатов.
С помощью такой методики можно удалить те строки, которые полностью совпадают друг с другом. Обычно это происходит при копировании информации из 2-3 и более файлов в одну таблицу. Но иногда возникает необходимость удалить дубли в Экселе, где информация совпадает только частично. Это может быть, например, каталог товаров, где есть одни и те же наименования с отличающимися ценами.
Простое удаление не позволит устранить повторы, если стоимость будет другой. Избежать ошибки можно, выбрав при настройке удаления дубликатов только те столбцы, которые будут сравниваться. Например, «Название» и «Марка» — но без «Цены» и «Количества».
Применение расширенного фильтра
Чтобы избежать ошибок, к которым может привести использование фильтра, перед тем, как удалить дубликаты строк в Excel, можно создать копию первоначального документа. Или выбрать использование специального расширенного фильтра, который поместит исправленную таблицу на новый лист без повторов.
Чтобы использовать фильтр, необходимо:
- Выбрать в таблице нужную область.
- Открыть вкладку «Данные» — «Сортировка» — «Дополнительно».
- В окне расширенного фильтра установить переключатель таким способом, чтобы результат его работы был скопирован в другое место.
- Выбрать в строке исходного диапазона всю таблицу или ее часть, а в строке диапазона для перемещения установить место верхнего левого угла.
- Поставить галочку напротив выбора только уникальных значений и нажать «ОК».
Старая таблица с данными остается на месте. А результат использования такого специального фильтра окажется рядом (в том числе, в новом документе или на другом листе, которые необходимо создать заранее). В новой таблице все одинаковые строки будут удалены.
Выделение повторов
Если пользователь не уверен, что программа автоматически удалит нужные строки, или в том, что она найдет все повторы построчно, можно только выделить дубликаты в Excel. А удалить их уже потом — вручную. Для этого нужно выполнить такие действия:
- Выделить таблицу, из которой удаляются повторы.
- Перейти на главной вкладке в группу стилей и выбрать условное форматирование.
- Перейти к правилам выделения ячеек и выбрать пункт «Повторяющиеся значения».
- Указать цвет текста и заливки, которыми будут выделены повторяющиеся сведения. Лучше всего сделать выделение заметно отличающимся от основного текста — так будет проще не пропустить дубликаты для удаления вручную.
После запуска этой функции программа выделит указанным способом все повторяющиеся значения в выделенной области. Причем сделает это не только для целых строк, но и для отдельных слов или фраз. А уже удалять повторы в Эксель придется самому пользователю.
Создание сводной таблицы
Избавиться от повторяющихся значений можно и с помощью сводной таблицы. Способ этот сложнее, чем две первых методики — зато подходит даже для старых версий Excel с меньшим количеством функций.
О том, как создать сводную таблицу в Excel, мы писали в отдельной статье. В кратце, для ее формирования нужно сделать следующее:
- Добавить дополнительный столбец, назвав его, например, «Счетчик», и заполнить его единицами до конца таблицы.
- На вкладке «Вставка» в группе таблиц выбрать «Сводную таблицу» и установить галочки так, чтобы она оказалась на новом листе.
- Перетащить мышкой названия всех столбцов, кроме последнего, в поле названий.
- Перетащить «Счетчик» в поле значений.
Теперь у пользователя получится сводная таблица, в которой все дубликаты будут сгруппированы вместе. Узнать о том, что среди информации есть повторы, можно по цифрам в последнем столбце. Если там будет не единица — значит, есть дубликаты.
Как найти и удалить повторяющиеся значения в Excel
Найти повторяющиеся значения в одном или двух столбцах поможет избавиться от дублей или найти совпадения для их дальнейшего анализа.
Найти и удалить повторяющиеся значения в столбце в Excel
В этом случае потребуется выделить нужный диапазон, после чего в верхнем меню выбрать «Данные»-«Удалить дубликаты».
Система предложит провести операцию только с выделенным диапазоном или же захватить рядом стоящие ячейки.
Теперь выбираем столбцы в которых будем производить операцию.
Система самостоятельно найдет одинаковые значения и полученный результат отсортирует по возрастанию.
Одинаковые значения в разных столбцах
Выполнить это действие можно при помощи функции ВПР, в которой прописываем:
=ВПР(Искомый объект; Список поиска ; номер столбца который нужно вернуть ;
Пример для работы Вы сможете скачать ниже.
В итоге получает третий столбец, показывающий, где находятся повторяющиеся значения из столбца Б в столбце А.
Важно! #Н/Д значит объект не найдет.
Найти дубли без операций
Для этого сортируем весь диапазон по возрастанию.
После создаем формулу напротив второго значения, в которой через равно проверяем на соответствие предыдущей ячейки со следующей. Пример:
Растягиваем формулу до конца проверяемого ряда.
В итоге получаем, там, где указано «Истина» — это повтор, а «Ложь» — уникальное значение.
Повторяющиеся значения в Excel: как удалить дубликаты, или просто найти и выделить
С популяризацией компьютеров за последние 10 лет — происходит и популяризация создания отчетов (документов) в программе Excel.
И в любом относительно большом документе встречаются повторяющиеся строки, особенно, если вы его собрали из несколько других таблиц. Эти дубли могут очень мешать дальнейшему редактированию таблицы, а потому их нужно либо выделить, либо вообще удалить.
Собственно, ко мне ни раз и ни два обращались с подобными вопросами, и я решил оформить работу с дубликатами в отдельную небольшую статью (которую вы сейчас читаете). Приведу самые часто-встречаемые задачи, и покажу их решение.
Примечание : все примеры ниже будут представлены в Office 2016/2019 (актуально также для Office 2013, 2010, 2007). Рекомендую всегда использовать относительно новые версии Office: в них и быстрее работать, и проще.
Простое авто-удаление дублей строк
Представим одну из самых популярных задач: есть список строк с ФИО людей, в строках много совпадений, нужно оставить только уникальные значения (кстати, это просто пример, все совпадения с реальными людьми — случайны) .
Задачка проиллюстрирована на скриншоте ниже (это пример: понятно, что 2 строки можно удалить и руками, но в реальности же строк может быть и 2000, и 20000, где «руки» будут бессильны. ).
Пример задачи в Excel
Чтобы в Excel удалить повторяющиеся строки, необходимо:
-
выделить свою табличку (строки и столбцы) с данными;
Выделение таблицы в Excel
Данные — удалить дубликаты // Excel
Удаление (выделение заголовков)
Лишние строки удалены!
Копирование уникальных строк (данных) в новое место
Если вы не хотите трогать и изменять текущие данные (текущую таблицу) — то можно просто скопировать только уникальные строки (значения) из нее в новую таблицу. Это очень удобно, когда вы хотите наглядно сравнить, что было, и что получилось (а также волнуетесь за старые данные, которые еще могут пригодиться).
Как это делается:
-
сначала необходимо выделить табличку, затем перейти в раздел «Данные» и нажать на кнопку «Дополнительно» (подраздел «Сортировка и фильтр», см. скриншот ниже);
Раздел «Данные» — дополнительно (Excel)
Скопировать только уникальные значения в другое место
Поиск и выделение повторяющихся значений
В тех случаях, когда вам не нужно удалить дубли или копировать уникальные значения в новое место — можно просто найти и подсветить их (причем, выделить можно как уникальные значения, так и повторяющиеся). Обычно, это необходимо делать, когда предстоит дальнейшая корректировка строк (их удобно и быстро можно найти).
Как выделить повторяющийся строки:
-
сначала также выделяете все свои строки (пример ниже);
Повторяющиеся значения // Раздел «Главная» в Excel
Выбираем цвет, как выделим строки
Надеюсь, информация будет полезна для работы с большими таблицами.