Excel – Разное выделение для разных пар дублирующих значений

0

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

Пример:

1. Thelegend28 | [email protected]
2. timmyhs | [email protected]
3. l33tu53r | [email protected]
4. Thelegend28 | [email protected]
5. 2l33t4u | [email protected]
6. timmyhs | [email protected]

Как видно, у меня есть пользователи, зарегистрированные дважды с одним и тем же именем пользователя и адресом электронной почты (2. и 6.), но у меня также иногда разные уникальные имена пользователей, связанные с одной электронной почтой (3. и 5.) и имена пользователей, которые связаны с несколькими адресами (1. и 4.).

Что мне нужно сделать, если это возможно, так это отформатировать эти три случая по-разному.

Конечно, любая помощь будет очень ценна. Я всего лишь новичок, но стараюсь учиться. Заранее благодарю всех.

Попробуйте добавить 2 колонки с формулой COUNTIF:

Колонка C: =COUNTIF($A$2:$A$7,A2)

Колонка D: =COUNTIF($B$2:$B$7,B2)

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

Затем вы можете использовать Условное форматирование с этими формульными правилами:

=AND($C2=1,$D2>1)

=AND($C2>1,$D2=1)

=AND($C2>1,$D2>1)

Как всегда, есть много способов использовать Excel.

1. Используйте сводные таблицы и считайте количество вхождений в трех отдельных таблицах (pivot tables легче, чем кажется – просто посмотрите видео на YouTube об этом – это изменит ваш взгляд на жизнь, эээ, я имею в виду Excel). Одна таблица для дублированных имен пользователей, одна для дублированных адресов электронной почты и одна для обоих (объедините A1 & B1 в новую колонку C с =A1&”;”&B1). Это одна сводная таблица на колонку. Это может хорошо работать для ручной обработки (например, массовая рассылка пользователям, обновление базы данных веб-сайта и т. д.), но не так хорошо для удаления или редактирования дублированных строк в исходной электронной таблице. P.S. Не забывайте, что вы можете “углубляться” из сводной таблицы, дважды щелкнув на ячейку.

2. Для цветового форматирования на исходных данных условное форматирование сделает вашу работу. Сохраните новую колонку C, как указано выше. Выбирайте одну колонку за раз, затем нажмите “Главная” > “Условное форматирование” > “Правила выделения ячеек” > “Дублирующиеся значения…” и установите желаемое форматирование для каждой колонки. Этот метод может дать разное цветовое форматирование в одной строке, в отличие от того, чтобы одна строка была помечена как принадлежащая одной категории.

3. Если вы хотите быть более конкретным, используйте функцию COUNTIF для категоризации каждой строки следующим образом. Сохраните новую колонку C, как указано выше. Скажем, ваши данные находятся в A1:C10, тогда в D1 введите

   =IF(COUNTIF($A$1:$A$10, A1)>1,1,0) + IF(COUNTIF($B$1:$B$10, B1)>1,2,0) + IF(COUNTIF($C$1:$C$10, C1)>1,1,0)

   Это даст вам 0 для уникальных, 1 для дублированных имен пользователей, 2 для дублированных адресов электронной почты, 4 для полных дубликатов и 3 для специального класса, который имеет как дублированные адреса электронной почты, так и имена пользователей, но отдельно (например, john [email protected]; john [email protected]; johnny [email protected]). Вы можете затем условно отформатировать колонку D, используя индивидуальный “набор значков” с 5 различными значками для (0,1,2,3,4).