Эксель выделение повторяющихся значений

Вам также могут быть интересны следующие статьи

Как найти повторяющиеся значения в таблице Excel

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

Работая с большими наборами данных в Excel или объединяя несколько небольших электронных таблиц в более крупные, вы можете столкнуться с большим числом одинаковых строк.

И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в одном списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и ниже.

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

Поиск повторяющихся значений включая первые вхождения.

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

=СЧЁТЕСЛИ(A:A; A2)>1

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

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

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

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

=СЧЕТЕСЛИ($A$2:$A$8, A2)>1

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

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;"Дубликат";"Уникальное")

Если же вам нужно, чтобы формула указывала только на дубли, замените "Уникальное" на пустоту (""):

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

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

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

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

Если вам нужно указать только совпадения, давайте немного изменим:

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

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

Нетрудно заметить, что она не обозначает первое появление слова, а начинает отсчет со второго.

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

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

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

{=ЕСЛИ(СУММ((--СОВПАД($A$2:$A$17;A2)))<=1;"";"Дубликат")}

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

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

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

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

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

Обнаружение повторяющихся строк

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

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

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

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

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

=A2&B2&C2

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

=СЧЁТЕСЛИ(D:D;D2)

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

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

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

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

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

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

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

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

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

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

Используем условное форматирование.

Выделение цветом по условию – весьма важный инструмент Excel, о котором достаточно подробно мы рассказывали.

Сейчас я покажу, как можно в Экселе найти дубли ячеек, просто их выделив цветом.

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

Но здесь мы не можем исключить первые появления – подсвечивается всё.

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

=СЧЁТЕСЛИ($B$2:$B2; B2)>1

Результат работы формулы выденения повторяющихся значений вы видите выше. Они выделены зелёным цветом.

Чтобы освежить память, можете руководствоваться нашим материалом «Как изменить цвет ячейки в зависимости от значения».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Еще о дубликатах:

Выделение повторяющихся значений в EXCEL. Примеры и описание

Настроим Условное форматирование для выделения только повторяющихся значений в списке.

Задача

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

Решение

Усложним задачу. Теперь будем выделять дубликаты только если установлен Флажок « Выделить дубликаты» (ячейка B 1 )

• выделите диапазон содержащий список значений, например, B 3: B 16 ;
• вызовите Условное форматирование ( ) ;
• введите формулу =И(СЧЁТЕСЛИ($B$3:$B$16;$B3)>1;$B$1)

Обратите внимание, что в формуле использована относительная адресация, поэтому активной ячейкой при вводе формулы должна быть B3 (т.е. диапазон нужно выделять сверху вниз). Активная ячейка в выделенном диапазоне – белая и ее адрес отображается в поле Имя .

• выберите нужное форматирование;
• нажмите ОК.

Сняв Флажок « Выделить дубликаты» выделение повторяющихся значений исчезнет.

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

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

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

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

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

Как выделить дубликаты в Excel с помощью встроенного правила (с 1 ^st вхождений)

Во-первых, во всех версиях Excel есть предопределенное правило для выделения повторяющихся ячеек. Чтобы использовать это правило в ваших таблицах, выполните следующие действия:

1. Выберите данные, которые вы хотите проверить на дублирование. Это может быть столбец, строка или диапазон ячеек.
2. На вкладке Home в группе Styles щелкните Conditional Formatting > Highlight Cells Rules > Duplicate Values…
   
3. Откроется диалоговое окно Duplicate Values ​​, в котором по умолчанию выбран формат «Светло-красная заливка» и «Темно-красный текст».Чтобы применить формат по умолчанию, просто нажмите OK .

Помимо красной заливки и форматирования текста, в раскрывающемся списке доступно несколько других предопределенных форматов. Чтобы выделить дубликаты другим цветом, щелкните Custom Format… (последний элемент в раскрывающемся списке) и выберите цвет заливки и / или шрифта по своему вкусу.

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

Используя встроенное правило, вы можете выделить дубликаты в одном или нескольких столбцах, как показано на следующем снимке экрана:

При использовании встроенного правила Excel для выделения повторяющихся значений имейте в виду следующие две вещи:

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

Как выделить дубликаты без 1 ^st вхождений

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

1. На вкладке Домашняя страница в группе Стили щелкните Условное форматирование > Новое правило> Используйте формулу, чтобы определить, какие ячейки нужно форматировать .
2. В поле «Значения формата , где эта формула верна» введите формулу, подобную этой:
   = СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1

   Где A2 - самая верхняя ячейка выбранного диапазона.

3. Нажмите кнопку Format… и выберите желаемый цвет заливки и / или шрифта.
4. Наконец, нажмите ОК , чтобы сохранить и применить правило.

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

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

Как показать 3 ^rd , 4 ^th и все последующие повторяющиеся записи

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

Для выделения 3 ^rd и всех последующих повторяющихся экземпляров создайте правило условного форматирования на основе этой формулы:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> = 3

Для выделения 4 ^-го и всех последующих повторяющихся записей используйте эту формулу:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> = 4

Чтобы выделить только определенные вхождения, используйте оператор , равный (=).Например, чтобы выделить только 2 ^и экземпляров , вы должны использовать следующую формулу:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2) = 2

Как выделить дубликаты в диапазоне (несколько столбцов)

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

Выделить дубликаты в нескольких столбцах, включая 1 ^st вхождений

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

Или создайте правило условного форматирования с помощью этой формулы:

СЧЁТЕСЛИ ( диапазон , верхняя_ячейка )> 1

Например, чтобы выделить дубликаты в диапазоне A2: C8, формула выглядит следующим образом:

= СЧЁТЕСЛИ ($ A $ 2: $ C $ 8; A2)> 1

Обратите внимание на использование абсолютных ссылок на ячейки для диапазона ($ A $ 2: $ C $ 8) и относительных ссылок для верхней ячейки (A2).

Выделить дубликаты в нескольких столбцах без 1 ^st вхождений

Решение для этого сценария намного сложнее, неудивительно, что в Excel нет встроенного правила для этого :)

Чтобы выделить повторяющиеся записи в нескольких столбцах, игнорируя вхождения 1 ^st , вам потребуется создать 2 правила со следующими формулами:

Правило 1.Применяется к первому столбцу

Здесь вы используете ту же формулу, что и мы, чтобы выделить дубликаты без 1 ^st вхождений в одном столбце (подробные инструкции можно найти здесь).

В этом примере мы создаем правило для A2: A8 с этой формулой:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1

В результате повторяющиеся элементы без 1 ^st вхождений выделяются в крайнем левом столбце диапазона (в нашем случае такой элемент только один):

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

Чтобы выделить дубликаты в оставшихся столбцах (B2: C8), используйте эту формулу:

= СЧЁТЕСЛИ (A $ 2: $ A $ 8; B2) + СЧЁТЕСЛИ (B $ 2: B2, B2)> 1

В приведенной выше формуле первая функция СЧЁТЕСЛИ подсчитывает количество вхождений данного элемента в первом столбце, а вторая СЧЁТЕСЛИ делает то же самое для всех последующих столбцов. Затем вы складываете эти числа и проверяете, больше ли сумма 1.

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

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

Другое возможное решение - добавить пустой столбец слева от набора данных и объединить приведенные выше формулы в одну формулу, например:

= ЕСЛИ (СТОЛБЦЫ ($ B2: B2)> 1, СЧЁТЕСЛИ (A $ 2: $ B $ 8, B2), 0) + СЧЁТЕСЛИ (B $ 2: B2, B2)> 1

Где B2 - верхняя ячейка с данными в столбце 2 ^nd целевого диапазона.

Чтобы лучше понять формулу, давайте разделим ее на 2 основные части:

• Для первого столбца (B) условие ЕСЛИ никогда не выполняется, поэтому применяется только вторая функция СЧЁТЕСЛИ, которая является обычной формулой, которую мы уже использовали для поиска дубликатов без вхождений 1 ^st в одном столбце.
• Для всех последующих столбцов (C2: D8) ключевым моментом является разумное использование абсолютных и относительных ссылок в двух функциях СЧЁТЕСЛИ. Чтобы упростить понимание, я скопировал его в столбец G, чтобы вы могли видеть, как формула изменяется при применении к другим ячейкам:
  

Поскольку условие ЕСЛИ всегда ИСТИНА для всех столбцов, кроме первого (количество столбцов больше 1), формула действует следующим образом:

• Подсчитывает количество появлений данного элемента (D5 на скриншоте выше) во всех столбцах слева от данного столбца: COUNTIF (B $ 2: $ C $ 8, D5)
• Подсчитывает количество вхождений данного элемента в столбце элемента до ячейки элемента: COUNTIF (D $ 2: D5, D5)
• Наконец, формула складывает результаты обеих функций СЧЁТЕСЛИ.Если общее количество больше 1, то есть если существует более одного экземпляра элемента, применяется правило и элемент выделяется.

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

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

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

• Чтобы выделить повторяющиеся строки , исключая 1 ^st вхождений :
  = СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1
• Чтобы выделить повторяющиеся строки , включая 1 ^st вхождений :
  = COUNTIF ($ A $ 2: $ A $ 15, $ A2)> 1

Где A2 - первая ячейка, а A15 - последняя использованная ячейка в столбце, который вы хотите проверить на наличие дубликатов.Как видите, главное отличие - это умное использование абсолютных и относительных ссылок на ячейки.
На следующем снимке экрана показаны оба правила в действии:

Как выделить повторяющиеся строки в Excel

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

Для этого используйте функцию СЧЁТЕСЛИМН, которая позволяет сравнивать ячейки по нескольким критериям.Например, чтобы выделить повторяющиеся строки с одинаковыми значениями в столбцах A и B, используйте одну из следующих формул:

• Чтобы выделить повторяющиеся строки , кроме 1 ^st вхождений :
  = COUNTIFS ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2)> 1
• Чтобы выделить повторяющиеся строки с 1 ^st вхождений :
  = COUNTIFS ($ A $ 2: $ A $ 15, $ A2, $ B $ 2: $ B $ 15, $ B2)> 1

Результат показан на следующем снимке экрана:

Как вы понимаете, приведенный выше пример предназначен только для демонстрационных целей.При выделении повторяющихся строк в реальных листах вы, естественно, не ограничены сравнением значений только в 2 столбцах, функция СЧЁТЕСЛИМН может обрабатывать до 127 пар диапазон / критерий.

Выделение последовательных повторяющихся ячеек в Excel

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

• Для выделения последовательных дубликатов без 1 ^st вхождений :
  = $ A1 = $ A2
• Чтобы выделить последовательные дубликаты с 1 ^st вхождений :
  = ИЛИ ($ A1 = $ A2, $ A2 = $ A3)

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

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

• Чтобы выделить последовательные повторяющиеся ячейки без 1 ^st вхождений и игнорировать пустые ячейки :
  = И ($ A2 <> "", $ A1 = $ A2)
• Для выделения последовательных повторяющихся ячеек с 1 ^st вхождений и игнорировать пустые ячейки :
  = И ($ A2 <> "", ИЛИ ($ A1 = $ A2, $ A2 = $ A3))

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

Как выделить дубликаты в Excel с помощью Duplicate Remover

Надстройка Duplicate Remover - это комплексное решение для работы с повторяющимися записями в Excel. Он может находить, выделять, выбирать, копировать или перемещать повторяющиеся ячейки или целые повторяющиеся строки.

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

Средство удаления дубликатов добавляет 3 новые функции к вашей ленте Excel:

• Dedupe Table - для немедленного поиска и выделения дубликатов в одной таблице.
• Duplicate Remover - пошаговый мастер с расширенными параметрами для выявления и выделения дубликатов или уникальных значений в 1 таблице.
• Сравните 2 таблицы - найдите и выделите дубликаты путем сравнения двух столбцов или двух отдельных таблиц.

После установки Ultimate Suite for Excel вы найдете эти инструменты на вкладке Ablebits Data в группе Dedupe :

Выделение дубликатов в Excel за пару кликов

Для этого примера я создал следующую таблицу с несколькими сотнями строк.И наша цель - выделить повторяющиеся строки, которые имеют равные значения во всех трех столбцах:

Вы не поверите, но желаемый результат можно получить всего за 2 клика мыши :)

1. Выделив любую ячейку в таблице, нажмите кнопку Dedupe Table , и умная надстройка выберет всю таблицу.
2. Диалоговое окно Dedupe Table откроется со всеми столбцами, выбранными автоматически, а параметр Color дублирует опцию , выбранную по умолчанию.Итак, все, что вам нужно сделать, это нажать ОК :) Готово!

Результат будет примерно таким:

Как вы видите на изображении выше, инструмент Dupe Table выделил повторяющиеся строки без первых экземпляров .

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

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

По сравнению с быстрым инструментом Dedupe Table , мастеру Duplicate Remover требуется несколько дополнительных щелчков мышью, но он компенсирует это рядом дополнительных опций. Позвольте показать вам это в действии:

1. Выберите любую ячейку в таблице, в которой нужно выделить дубликаты, и нажмите кнопку Duplicate Remover на ленте. Мастер запустится, и будет выделена вся таблица.Надстройка также предложит на всякий случай создать резервную копию вашей таблицы. Если он вам не нужен, снимите этот флажок.

   Убедитесь, что таблица выбрана правильно, и нажмите Далее .

   

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

   Для этого примера давайте найдем дубликатов + 1-е вхождение :
   

3. Теперь выберите столбцы для проверки дубликатов.Поскольку мы хотим выделить полные повторяющиеся строки, я выбрал все 3 столбца.
   

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

4. Наконец, выберите действие для дубликатов. У вас есть несколько вариантов, таких как выбор , удаление , копирование, перемещение дубликатов или добавление столбца статуса в , чтобы идентифицировать дубликатов.

Так как сегодня мы изучаем различные способы выделения дубликатов в Excel, наш выбор очевиден :) Итак, выберите Заливка цветом и выберите один из стандартных цветов темы или нажмите Больше цветов… и выберите любой настраиваемый цвет RGB или HSL.

Нажмите кнопку Finish и наслаждайтесь результатом :)

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

Если вас устраивает производительность Duplicate Remover, мы рады предоставить вам эту особую возможность:

Вас также может заинтересовать

Найдите и удалите дубликаты - Excel

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

1. Выберите ячейки, которые нужно проверить на наличие дубликатов.

   Примечание. Excel не может выделять дубликаты в области значений отчета сводной таблицы.

2. Щелкните Домой > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения .

3. В поле рядом с значениями с выберите форматирование, которое вы хотите применить к повторяющимся значениям, а затем нажмите ОК .

Удалить повторяющиеся значения

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

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

2. Щелкните Data > Remove Duplicates , а затем в разделе Columns установите или снимите отметку со столбцами, в которых вы хотите удалить дубликаты.

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

   

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

   

3. Щелкните ОК .

Найдите и выделите дубликаты - Easy Excel Tutorial

повторяющихся значений | В трех экземплярах | Повторяющиеся строки

В этом примере показано, как найти повторяющиеся значения (или трижды) и как найти повторяющиеся строки в Excel .

Повторяющиеся значения

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

1. Выберите диапазон A1: C10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните Выделить правила ячеек, повторяющиеся значения.

4. Выберите стиль форматирования и нажмите OK.

Результат. Excel выделяет повторяющиеся имена.

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

в трех экземплярах

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

1. Сначала удалите предыдущее правило условного форматирования.

2. Выберите диапазон A1: C10.

3. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

4. Щелкните Новое правило.

5. Выберите «Использовать формулу для определения ячеек для форматирования».

6. Введите формулу = СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A1) = 3

7. Выберите стиль форматирования и нажмите OK.

Результат. Excel выделяет тройные имена.

Объяснение: = COUNTIF ($ A $ 1: $ C $ 10, A1) подсчитывает количество имен в диапазоне A1: C10, равных имени в ячейке A1. Если СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A1) = 3, Excel форматирует ячейку A1. Всегда записывайте формулу для верхней левой ячейки в выбранном диапазоне (A1: C10). Excel автоматически копирует формулу в другие ячейки. Таким образом, ячейка A2 содержит формулу = СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A2) = 3, ячейка A3 = СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A3) = 3 и т. Д. Обратите внимание, как мы создали абсолютную ссылку ($ A $ 1: $ C $ 10), чтобы исправить эту ссылку.

Примечание: вы можете использовать любую формулу, которая вам нравится. Например, используйте эту формулу = СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A1)> 3, чтобы выделить имена, встречающиеся более 3 раз.

Повторяющиеся строки

Чтобы найти и выделить повторяющиеся строки в Excel, используйте СЧЁТЕСЛИ (с буквой S в конце) вместо СЧЁТЕСЛИ.

1. Выберите диапазон A1: C10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните Новое правило.

4. Выберите «Использовать формулу для определения ячеек для форматирования».

5. Введите формулу = СЧЁТЕСЛИ (Животные, $ A1, континенты, $ B1, страны, $ C1)> 1

6. Выберите стиль форматирования и нажмите OK.

Примечание: именованный ареал Животные относится к диапазону A1: A10, именованный диапазон «Континенты» относится к диапазону B1: B10, а именованный диапазон «Страны» относится к диапазону C1: C10. = СЧЁТЕСЛИМН (Животные, $ A1, Континенты, $ B1, Страны, $ C1) подсчитывает количество строк на основе нескольких критериев (Леопард, Африка, Замбия).

Результат. Excel выделяет повторяющиеся строки.

Объяснение: если СЧЁТЕСЛИ (Животные, $ A1, Континенты, $ B1, Страны, $ C1)> 1, другими словами, если имеется несколько строк (Леопард, Африка, Замбия), Excel форматирует ячейку A1. Всегда записывайте формулу для верхней левой ячейки в выбранном диапазоне (A1: C10). Excel автоматически копирует формулу в другие ячейки. Мы зафиксировали ссылку на каждый столбец, поместив символ $ перед буквой столбца ($ A1, $ B1 и $ C1).В результате ячейки A1, B1 и C1 содержат одну и ту же формулу, ячейки A2, B2 и C2 содержат формулу = COUNTIFS (Животные, $ A2, Континенты, $ B2, Страны, $ C2)> 1 и т. Д.

Наконец, вы можете использовать инструмент «Удалить дубликаты» в Excel, чтобы быстро удалить повторяющиеся строки. На вкладке «Данные» в группе «Работа с данными» щелкните Удалить дубликаты.

В приведенном ниже примере Excel удаляет все идентичные строки (синие), кроме первой найденной идентичной строки (желтая).

Примечание: посетите нашу страницу об удалении дубликатов, чтобы узнать больше об этом замечательном инструменте Excel.

найти, выделить, подсчитать, отфильтровать и т. Д.

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

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

И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в едином списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, Excel 2010 и ниже.

Как определить дубликаты в Excel

Самый простой способ обнаружить дубликаты в Excel - использовать функцию СЧЁТЕСЛИ. В зависимости от того, хотите ли вы найти повторяющиеся значения с первыми вхождениями или без них, в формуле будет небольшое изменение, как показано в следующих примерах.

Как найти повторяющиеся записи, включая 1 ^st вхождений

Предположим, у вас есть список элементов в столбце A, которые вы хотите проверить на наличие дубликатов. Это могут быть счета-фактуры, идентификаторы продукта, имена или любые другие данные.

Вот формула для поиска дубликатов в Excel, включая первые вхождения (где A2 - самая верхняя ячейка):

= СЧЁТЕСЛИ (A: A, A2)> 1

Введите указанную выше формулу в B2, затем выберите B2 и перетащите маркер заполнения, чтобы скопировать формулу в другие ячейки:

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

Примечание. Если вам нужно найти дубликаты в диапазоне ячеек , а не во всем столбце, не забудьте исправить этот диапазон с помощью $. Например, для поиска дубликатов в ячейках A2: A8 используйте формулу = СЧЁТЕСЛИ ( $ A $ 2: $ A $ 8 , A2)> 1.

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

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, $ A2)> 1, «Дублировать», «Уникальный»)

Если вы хотите, чтобы формула Excel находила только дубликаты, замените «Уникальный» пустой строкой («»), например:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, $ A2)> 1, "Дублировать", "")

Формула вернет «Дубликаты» для повторяющихся записей и пустую ячейку для уникальных записей:

Как искать дубликаты в Excel без вхождений 1 ^st

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

Итак, давайте изменим нашу формулу дублирования Excel, используя абсолютные и относительные ссылки на ячейки, где это необходимо:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1, "Дублировать", "")

Как видно на следующем снимке экрана, эта формула не идентифицирует первое вхождение " Apple " как дубликат:

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

В ситуациях, когда вам нужно идентифицировать точные дубликаты, включая текстовый регистр, используйте эту общую формулу массива (вводимую нажатием Ctrl + Shift + Enter):

IF (SUM ((--EXACT ( диапазон , верхний _cell ))) <= 1, "", "Duplicate")

В основе формулы вы используете функцию EXACT для точного сравнения целевой ячейки с каждой ячейкой в ​​указанном диапазоне.Результатом этой операции является массив ИСТИНА (совпадение) и ЛОЖЬ (не совпадение), который приводится к массиву из единиц и нулей унарным оператором (-). После этого функция СУММ складывает числа, и если сумма больше 1, функция ЕСЛИ сообщает «Дубликат».

Для нашего образца набора данных формула выглядит следующим образом:

= ЕСЛИ (СУММ ((- ТОЧНО ($ A $ 2: $ A $ 8, A2))) <= 1, "", "Дублировать")

Как показано на скриншоте ниже, строчные и прописные буквы рассматриваются как разные символы (ЯБЛОКИ не идентифицируются как дубликаты):

Как найти повторяющиеся строки в Excel

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

Рассмотрим следующий пример. Предположим, у вас есть порядковые номера в столбце A, даты в столбце B и заказанные элементы в столбце C, и вы хотите найти повторяющиеся строки с тем же номером заказа, датой и элементом. Для этого мы собираемся создать повторяющуюся формулу на основе функции СЧЁТЕСЛИМН, которая позволяет проверять несколько критериев одновременно:

Чтобы найти повторяющихся строк с 1 ^st вхождений , используйте эту формулу:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, $ A2, $ B $ 2: $ B $ 8, $ B2, $ C $ 2: $ C $ 8, $ C2)> 1, "Повторяющаяся строка", "" )

На следующем снимке экрана показано, что формула действительно находит только те строки, которые имеют одинаковые значения во всех 3 столбцах.Например, строка 8 имеет тот же номер заказа и дату, что и строки 2 и 5, но другой элемент в столбце C, и поэтому она не помечена как повторяющаяся строка:

Чтобы показать повторяющихся строк без 1 ^st вхождений , внесите небольшую корректировку в приведенную выше формулу:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2, $ B $ 2: $ B2, $ B2, $ C $ 2: $ C2, $ C2,)> 1 , "Повторяющаяся строка", "")

Как подсчитать дубликаты в Excel

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

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

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

Чтобы узнать, сколько раз та или иная запись встречается в вашем листе Excel, используйте простую формулу СЧЁТЕСЛИ, где A2 - первый, а A8 - последний элемент списка:

= СЧЁТЕСЛИ (2 доллара: 8 австралийских долларов, A2)

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

Если вы хотите идентифицировать 1 ^st , 2 ^nd , 3 ^rd и т. Д. Вхождения каждого элемента, используйте следующую формулу:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)

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

= COUNTIFS ($ A $ 2: $ A $ 8, $ A2, $ B $ 2: $ B $ 8, $ B2)

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

Подсчитать общее количество дубликатов в столбце (ах)

Самый простой способ подсчета дубликатов в столбце - использовать любую из формул, которые мы использовали для определения дубликатов в Excel (с первыми вхождениями или без них). А затем вы можете подсчитать повторяющиеся значения, используя следующую формулу СЧЁТЕСЛИ:

= СЧЁТЕСЛИ (диапазон; "дубликат")

Где « дубликат » - это метка, которую вы использовали в формуле для поиска дубликатов.

В этом примере повторяющаяся формула принимает следующую форму:

= СЧЁТЕСЛИ (B2: B8, «дубликат»)

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

= СТРОКИ ($ A $ 2: $ A $ 8) -SUM (IF (COUNTIF ($ A $ 2: $ A $ 8, $ A $ 2: $ A $ 8) = 1,1,0))

Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ее.Также имейте в виду, что эта формула учитывает все повторяющиеся записи, , включая первые вхождения :

Чтобы найти общее количество повторяющихся строк , вставьте функцию СЧЁТЕСЛИ вместо СЧЁТЕСЛИ в приведенную выше формулу и укажите все столбцы, которые вы хотите проверить на наличие дубликатов. Например, чтобы подсчитать повторяющиеся строки на основе столбцов A и B, введите в лист Excel следующую формулу:

= СТРОКИ ($ A $ 2: $ A $ 8) -SUM (IF (COUNTIFS ($ A $ 2: $ A $ 8, $ A $ 2: $ A $ 8, $ B $ 2: $ B $ 8, $ B $ 2: $ B 8 $) = 1,1,0))

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

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

Как показать и скрыть дубликаты в Excel

Если вы хотите сразу увидеть все дубликаты, воспользуйтесь одной из формул, чтобы найти дубликаты в Excel, которые лучше подходят для ваших нужд. Затем выберите свою таблицу, перейдите на вкладку Data и нажмите кнопку Filter . Также можно щелкнуть Сортировка и фильтр > Фильтр на вкладке Домашняя страница в группе Редактирование .

Наконечник. Чтобы включить автоматическую фильтрацию, преобразуйте данные в полнофункциональную таблицу Excel. Просто выделите все данные и нажмите сочетание клавиш Ctrl + T.

После этого щелкните стрелку в заголовке столбца Дублировать и установите флажок « Дубликат строки », чтобы отображать дубликаты . Если вы хотите отфильтровать, например, скрыть дубликаты , выберите « Unique », чтобы просмотреть только уникальные записи:

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

Как отфильтровать дубликаты по их появлению

Если вы хотите показать 2 ^nd , 3 ^rd или N-е вхождения повторяющихся значений, используйте формулу для подсчета повторяющихся экземпляров, которые мы обсуждали ранее:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)

Затем примените фильтрацию к своей таблице и выберите только вхождения, которые вы хотите просмотреть. Например, вы можете отфильтровать 2 ^и вхождений, как на следующем снимке экрана:

Чтобы отобразить все повторяющиеся записи, i.е. вхождений больше 1 , щелкните стрелку фильтра в заголовке столбца вхождений (столбец с формулой), а затем щелкните Number Filters > Greater Than .

Выберите « больше, чем » в первом поле, введите 1 в поле рядом с ним и нажмите кнопку OK :

Аналогичным образом вы можете показать 2 ^nd , 3 ^rd и все последующие повторяющиеся вхождения.Просто введите необходимое число в поле рядом с « больше ».

Выделение, выбор, очистка, удаление, копирование или перемещение дубликатов

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

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

Чтобы выбрать дубликаты, , включая заголовки столбцов , отфильтровать их, щелкнуть любую отфильтрованную ячейку, чтобы выбрать ее, а затем нажмите Ctrl + A.

Чтобы выбрать повторяющиеся записи без заголовков столбцов , выберите первую (верхнюю левую) ячейку и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.

Наконечник. В большинстве случаев указанные выше ярлыки работают нормально и выбирают только отфильтрованные (видимые) строки. В некоторых редких случаях, в основном в очень больших книгах, могут быть выбраны как видимые, так и невидимые ячейки. Чтобы исправить это, сначала используйте один из указанных выше сочетаний клавиш, а затем нажмите Alt +; до выберите только видимые ячейки , игнорируя скрытые строки.

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

Чтобы очистить дубликаты в Excel, выберите их, щелкните правой кнопкой мыши, а затем щелкните Очистить содержимое (или нажмите кнопку Очистить > Очистить содержимое на вкладке Home в группе Редактирование ).Это удалит только содержимое ячейки, и в результате у вас будут пустые ячейки. Выбор отфильтрованных повторяющихся ячеек и нажатие клавиши Delete будет иметь тот же эффект.

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

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

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

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

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

Чтобы скопировать дубликатов, выберите их, нажмите Ctrl + C, затем откройте другой лист (новый или существующий), выберите верхнюю левую ячейку диапазона, в который вы хотите скопировать дубликаты, и нажмите Ctrl + V, чтобы наклеить их.

Чтобы переместить дубликатов на другой лист, выполните те же действия с той лишь разницей, что вы нажимаете Ctrl + X (вырезать) вместо Ctrl + C (копировать).

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

Duplicate Remover - быстрый и эффективный способ найти дубликаты в Excel

Теперь, когда вы знаете, как использовать повторяющиеся формулы в Excel, позвольте мне продемонстрировать вам еще один быстрый, эффективный и свободный от формул способ - Duplicate Remover для Excel.

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

Как найти повторяющиеся строки в Excel за 2 быстрых шага

Чтобы проверить возможности нашей надстройки Duplicate Remover, я создал таблицу с несколькими сотнями строк, которая выглядит следующим образом:

Как видите, таблица состоит из нескольких столбцов.Первые 3 столбца содержат наиболее важную информацию, поэтому мы собираемся искать повторяющиеся строки исключительно на основе данных в столбцах A - C. Чтобы найти повторяющиеся записи в этих столбцах, просто выполните следующие действия:

1. Выберите любую ячейку в таблице и нажмите кнопку Dedupe Table на ленте Excel. После установки нашего Ultimate Suite for Excel вы найдете его на вкладке Ablebits Data в группе Dedupe .
   
2. Интеллектуальная надстройка возьмет всю таблицу и попросит вас указать следующие две вещи:
   • Выберите столбцы для проверки дубликатов (в этом примере это номер заказа ., Дата заказа и графа позиции ).
   • Выберите действие для выполнения с дубликатами . Поскольку наша цель - выявить повторяющиеся строки, я выбрал Добавить столбец статуса
     

   Помимо добавления столбца состояния, вам доступен ряд других опций:

   • Удалить дубликаты
   • Цвет (выделение) дубликатов
   • Выбрать дубликаты
   • Копировать дубликаты на новый лист
   • Перенести дубликаты на новый лист

   Щелкните кнопку OK и подождите несколько секунд.Выполнено!

Как вы можете видеть на снимке экрана ниже, все строки с одинаковыми значениями в первых трех столбцах были обнаружены (первые вхождения не идентифицируются как дубликаты).

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

Мастер удаления дубликатов - больше возможностей для поиска дубликатов в Excel

В зависимости от конкретного листа, с которым вы работаете, вы можете или не захотите рассматривать первые экземпляры идентичных записей как дубликаты.Одно из возможных решений - использовать разные формулы для каждого сценария, как мы обсуждали в разделе «Как идентифицировать дубликаты в Excel». Если вы ищете быстрый, точный метод без формул, попробуйте мастер Duplicate Remover :

1. Выберите любую ячейку в таблице и нажмите кнопку Duplicate Remover на вкладке Ablebits Data . Мастер запустится, и будет выделена вся таблица.
   
2. На следующем шаге вам будут предложены 4 варианта проверки дубликатов на листе Excel:
   • Дубликаты без первых вхождений
   • Дубликаты с 1-м вхождением
   • Уникальные значения
   • Уникальные значения и первые повторяющиеся вхождения

   Для этого примера давайте выберем второй вариант, т.е.е. Дубликаты + 1-е вхождение :
   

3. Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы выбираем первые 3 столбца:
   
4. Наконец, выберите действие, которое вы хотите выполнить с дубликатами. Как и в случае с инструментом Dedupe Table, мастер Duplicate Remover может идентифицировать , выбрать , выделить , удалить , скопировать или переместить дубликатов.

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

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

Без формул, без стресса, без ошибок - всегда быстрый и безупречный результат :)

Если вы хотите попробовать эти инструменты для поиска дубликатов в таблицах Excel, вы можете скачать ознакомительную версию ниже.Будем признательны за ваши отзывы в комментариях!

А если вам нравятся инструменты и вы хотите получить лицензию, мы рады предложить вам эту особую возможность:

Доступные загрузки

Определить дубликаты - примеры формул (файл .xlsx)
Ultimate Suite - пробная версия (файл .zip)

Вас также может заинтересовать

Excel: выделите повторяющиеся значения

СЧЁТЕСЛИ просто подсчитывает, сколько раз каждое значение встречается в диапазоне. Когда счетчик больше 1, формула возвращает ИСТИНА и запускает правило.

При использовании формулы для применения условного форматирования формула вычисляется относительно активной ячейки в выделенном фрагменте во время создания правила. В этом случае диапазон, который мы используем в COUNTIF, заблокирован абсолютным адресом, но B4 полностью относительный. Итак, правило оценивается для каждой ячейки в диапазоне, при этом B4 изменяется, а $ B $ 4: $ G $ 11 остается неизменным.

Переменное количество дубликатов + именованные диапазоны

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

Вы можете расширить эту идею и упростить чтение формулы, используя именованные диапазоны. Например, если вы назовете G2 «дубликаты», а диапазон B4: G11 «данные», вы можете переписать формулу следующим образом:

Затем вы можете изменить значение в G2 на любое, что вам нравится, и правило условного форматирования отреагирует мгновенно, выделив ячейку, которая содержит значения, большие или равные числу, которое вы поместили в именованный диапазон «дубли».

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

1. Около
2. Товары
3. Для команд

1. Переполнение стека Общественные вопросы и ответы
2. Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
3. Вакансии Программирование и связанные с ним технические возможности карьерного роста

Смотрите также

• 
  Выделения кровавые на ранних сроках беременности
• 
  Выделения при болезни почек у женщин
• 
  Желтые выделения при беременности без запаха во втором триместре
• 
  Выделения зеленоватого цвета с неприятным запахом у женщин
• 
  Выделения на шестой неделе беременности
• 
  Кровянистые выделения не во время месячных
• 
  После мифегина нет выделений
• 
  Выделения после менопаузы
• 
  Процедура реорганизации в форме выделения
• 
  Порошенко сенат сша одобрил выделение всу 500 миллионов долларов
• 
  Коричневые выделения на 24 день