3 метода как отыскивать дубликаты в Excel

Поиск дубликатов в Excel может оказаться не обычной задачей, но если Вы вооружены некими базисными познаниями, то найдёте несколько методов совладать с ней. Когда я в первый раз задумался о данной нам дилемме, то довольно стремительно вымыслил пару методов как найти дубликаты, а незначительно поразмыслив, нашел ещё несколько методов. Итак, давайте для начала разглядим пару обычных, а потом перейдём к наиболее сложным способам.

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

Поиск дубликатов с помощью интегрированных фильтров Excel

Организовав данные в виде перечня, Вы сможете использовать к ним разные фильтры. В зависимости от набора данных, который у Вас есть, Вы сможете отфильтровать перечень по одному либо нескольким столбцам. Так как я использую Office 2010, то мне довольно выделить верхнюю строчку, в которой находятся заглавия, потом перейти на вкладку Data (Данные) и надавить команду Filter (Фильтр). Около всякого из заголовков покажутся направленные вниз треугольные стрелки (иконки выпадающих меню), как на рисунке ниже.

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

Опосля опции фильтра Вы сможете удалить дубликаты строк, подвести промежные итоги либо добавочно отфильтровать данные по другому столбцу. Вы сможете редактировать данные в таблице так, как Для вас необходимо. На примере ниже у меня выбраны элементы XP и XP Pro.

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

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

На вкладке Data (Данные) справа от команды Filter (Фильтр) есть клавиша для опций фильтра – Advanced (Добавочно). Сиим инвентарем воспользоваться чуток труднее, и его необходимо незначительно настроить, до этого чем употреблять. Ваши данные должны быть организованы так, как было описано ранее, т.е. как база данных.

Перед тем как употреблять расширенный фильтр, Вы должны настроить для него аспект. Поглядите на набросок ниже, на нем виден перечень с данными, а справа в столбце L указан аспект. Я записал заголовок столбца и аспект под одним заголовком. На рисунке представлена таблица футбольных матчей. Требуется, чтоб она демонстрировала лишь домашние встречи. Конкретно потому я скопировал заголовок столбца, в котором желаю выполнить фильтрацию, а ниже расположил аспект (H), который нужно употреблять.

Сейчас, когда аспект настроен, выделяем всякую ячейку наших данных и жмем команду Advanced (Добавочно). Excel изберет весь перечень с данными и откроет вот такое диалоговое окно:

Видите ли, Excel выделил всю таблицу и ждёт, когда мы укажем спектр с аспектом. Изберите в диалоговом окне поле Criteria Range (Спектр критерий), потом выделите мышью ячейки L1 и L2 (или те, в которых находится Ваш аспект) и нажмите ОК. Таблица покажет лишь те строки, где в столбце Home / Visitor стоит значение H, а другие скроет. Таковым образом, мы отыскали дубликаты данных (по одному столбцу), показав лишь домашние встречи:

Это довольно обычной путь для нахождения дубликатов, который может посодействовать сохранить время и получить нужную информацию довольно стремительно. Необходимо держать в голове, что аспект должен быть размещён в ячейке раздельно от перечня данных, чтоб Вы могли найти его и употреблять. Вы сможете поменять фильтр, изменив аспект (у меня он находится в ячейке L2). Не считая этого, Вы сможете отключить фильтр, нажав клавишу Clear (Очистить) на вкладке Data (Данные) в группе Sort & Filter (Сортировка и фильтр).

Интегрированный инструмент для удаления дубликатов в Excel

В Excel есть интегрированная функция Remove Duplicates (Удалить дубликаты). Вы сможете избрать столбец с данными и с помощью данной нам команды удалить все дубликаты, оставив лишь неповторимые значения. Пользоваться инвентарем Remove Duplicates (Удалить дубликаты) можно с помощью одноименной клавиши, которую Вы найдёте на вкладке Data (Данные).

Не забудьте избрать, в каком столбце нужно бросить лишь неповторимые значения. Если данные не содержат заголовков, то в диалоговом окне будут показаны Column A, Column B (столбец A, столбец B) и так дальше, потому с заголовками работать еще удобнее.

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

Поиск дубликатов с помощью команды Найти

Если Для вас необходимо найти в Excel маленькое количество дублирующихся значений, Вы сможете создать это с помощью поиска. Зайдите на вкладку Home (Основная) и кликните Find & Select (Найти и выделить). Раскроется диалоговое окно, в котором можно ввести хоть какое значение для поиска в Вашей таблице. Чтоб избежать опечаток, Вы сможете скопировать значение прямо из перечня данных.

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

Интересно почитать: Пузырьковая диаграмма в excel как построить

Если необходимо выполнить поиск по всем имеющимся данным, может быть, клавиша Find All (Найти все) окажется для Вас наиболее полезной.

В заключение

Все три способа ординарны в использовании и посодействуют Для вас с поиском дубликатов:

Фильтр – совершенно подступает, когда в данных находятся несколько категорий, которые, может быть, Для вас пригодится поделить, просуммировать либо удалить. Создание подразделов – самое наилучшее применение для расширенного фильтра.
Удаление дубликатов уменьшит объём данных до минимума. Я пользуюсь сиим методом, когда мне необходимо создать перечень всех неповторимых значений 1-го из столбцов, которые в предстоящем использую для вертикального поиска при помощи функции ВПР.
Я пользуюсь командой Find (Найти) лишь если необходимо найти маленькое количество значений, а инструмент Find and Replace (Найти и поменять), когда нахожу ошибки и желаю разом поправить их.

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

Повторяющиеся значения в Excel — найти, выделить либо удалить дубликаты в Excel

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

1. Удаление циклических значений в Excel (2007+)

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

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

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

2. Внедрение расширенного фильтра для удаления дубликатов

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

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

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

3. Выделение циклических значений при помощи условного форматирования в Excel (2007+)

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

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

4. Внедрение сводных таблиц для определения циклических значений

Воспользуемся уже знакомой нам таблицей с 3-мя столбцами и добавим 4-ый, под заглавием Счетчик, и заполним его единицами (1). Выделяем всю таблицу и перебегаем по вкладке Вставка в группу Таблицы, щелкаем по кнопочке Сводная таблица.

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

Как найти повторяющиеся значения в excel?

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

Работа с цветом

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

Опосля открытия вкладки «Основная»,
зайдя в раздел под заглавием «Стили»,
следует избрать «Условное форматирование»,
потом – «Правила обозначения ячеек»,
и, в конце концов, «Повторяемые значения».

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

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

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

Выборочное подсвечивание или удаление схожих данных

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

Интересно почитать: Excel настройка автосохранения

В окошке, в поле поиска пропечатываем то, что отыскиваем. Опосля этого кликаем на клавишу «Найти все», жмем комбинацию Ctrl и А для выделения всех разыскиваемых результатов, и выделяем их средством цвета. К тому же, их может быть не выделять, а удалять.

Избавляемся от схожих данных средством расширенного фильтра

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

Сейчас следует настроить в данном окне, как конкретно будет осуществлена фильтровка. Можно переместить результаты фильтровки в другое пространство (проставляем галочку и указываем пространство, куда будут скопированы результаты), либо же оставляем его на прежнем месте. И обязательно выставляем галочку «Только неповторимые данные».

Дальше мои результаты использования в таблице расширенного фильтра. Итак, Эксель отыскал и удалил повторения.

Найти повторяющиеся значения в excel

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

Сейчас перейдите ко вкладке «Данные», к разделу под заглавием «работа с данными», а потом – «Удалить повторения».

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

На этом метод исчерпал себя. Вот результаты выполненной работы:

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

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

Фильтр неповторимых значений и удаление повторений из листа.

Поиск циклических значений включая 1-ые вхождения.

Представим, что у вас в колонке А находится набор каких-либо характеристик, посреди которых, возможно, есть одинаковые. Это могут быть номера заказов, наименования продуктов, имена клиентов и остальные данные. Если ваша задачка – найти их, то последующая формула вам:

Где А2 – 1-ая ячейка из области для поиска.

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

Как вы могли увидеть на снимке экрана выше, формула возвращает ИСТИНА, если имеются совпадения. А для встречающихся лишь 1 раз значений она указывает ЛОЖЬ.

Подсказка! Если вы отыскиваете повторы в определенной области, а не во всей колонке, обозначьте подходящий спектр и “зафиксируйте” его знаками $. Это существенно убыстрит вычисления. К примеру, если вы ищете в A2:A8, используйте

Если вас путает ИСТИНА и ЛОЖЬ в статусной колонке и вы не желаете держать в уме, что из их значит циклическое, а что – неповторимое, заверните свою СЧЕТЕСЛИ в функцию ЕСЛИ и укажите хоть какое слово, которое обязано соответствовать дубликатам и неповторимым:

Если же для вас необходимо, чтоб формула указывала лишь на дубли, поменяйте “Неповторимое” на пустоту (“”):

В этом случае Эксель отметит лишь неуникальные записи, оставляя пустую ячейку напротив неповторимых.

Поиск неуникальных значений без учета первых вхождений

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

Если для вас необходимо указать лишь совпадения, давайте незначительно изменим:

На снимке экрана ниже вы видите эту формулу в деле.

Несложно увидеть, что она не обозначает 1-ое возникновение слова, а начинает отсчет со второго.

Чувствительный к регистру поиск дубликатов

Желаю направить ваше внимание на то, что хоть формулы выше и находят 100%-дубликаты, есть один узкий момент – они не чувствительны к регистру. Может быть, вам это не принципно. Но если в ваших данных абв, Абв и АБВ – это три различных параметра – то этот пример вам.

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

Не запамятовывайте, что формулы массива вводятся комбиинацией Ctrl + Shift + Enter.

Если возвратиться к содержанию, то тут употребляется функция СОВПАД для сопоставления мотивированной ячейки со всеми остальными ячейками с избранной области. Итог ворачивается в виде ИСТИНА (совпадение) либо ЛОЖЬ (не совпадение), которые потом преобразуются в массив из 1 и 0 с помощью оператора (–).

Опосля этого, функция СУММ складывает эти числа. И если приобретенный итог больше 1, функция ЕСЛИ докладывает о отысканном дубликате.

Если вы взглянете на последующий снимок экрана, вы удостоверьтесь, что поиск вправду учитывает регистр при обнаружении дубликатов:

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

Найти и удалить повторяющиеся значения в столбце в Excel

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

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

Сейчас избираем столбцы в которых будем создавать операцию.

Система без помощи других отыщет одинаковые значения и приобретенный итог отсортирует по возрастанию.

Поиск схожих значений в Excel

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

На рисунке – списки писателей. Метод действий последующий:

Избрать ячейку I3 с записью «С. А. Есенин».
Поставить задачку – выделить цветом ячейки с таковыми же записями.
Выделить область поисков.
Надавить вкладку «Основная».
Дальше группа «Стили».
Потом «Условное форматирование»;
Надавить команду «Равно».

Покажется диалоговое окно:

В левом поле указать ячейку с I2, в которой записано «С. А. Есенин».
В правом поле можно избрать цвет шрифта.
Надавить «ОК».

В таблицах отмечены цветом ячейки, значение которых равно данному.

Интересно почитать: Автоподбор высоты строк в excel

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

Обнаружение циклических строк

Мы разглядели, как найти одинаковые данные в отдельных ячейках. А если необходимо находить дубликаты-строки?

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

Итак, имеются данные о товарах и заказчиках.

Сделаем справа от наших данных формулу, объединяющую содержание всех расположенных слева от нее ячеек.

Представим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:

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

Скопируем вниз для всех строк данных.

В столбце E отображается количество возникновений данной нам строки в столбце D. Неповторяющимся строчкам будет соответствовать значение 1. Повторам строчкам соответствует значение больше 1, указывающее на то, сколько раз таковая строчка была найдена.

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

Как найти одинаковые значения в столбце Excel

Поиск дублей в Excel – это одна из самых всераспространенных задач для хоть какого офисного сотрудника. Для ее решения существует несколько различных методов. Но как стремительно как найти дубликаты в Excel и выделить их цветом? Для ответа на этот нередко задаваемый вопросец разглядим определенный пример.

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

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

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

В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Основная – Форматировать как таблицу.

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

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

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

Поиск совпадений с помощью команды «Найти».

Очередной обычной, но не очень технологичный метод – внедрение встроенного поиска.

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

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

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

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

Как применить сводную таблицу для поиска дубликатов.

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

Для наиболее опытнейших – сходу перебегаем к сущности вопросца.

Создаем новейший макет сводной таблицы. А потом в качестве строк и значений используем одно и то же поле. В нашем случае – «Продукт». Так как заглавие продукта – это текст, то для подсчета таковых значений Excel по дефлоту употребляет функцию СЧЕТ, другими словами подсчитывает количество. А нам это и необходимо. Если будет больше 1, означает, имеются дубликаты.

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

Например, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и лицезреем такую картину:

Думаю, этот способ полностью можно употреблять. Что приятно – никаких формул не требуется.

Поиск и удаление дубликатов в Microsoft Excel

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