Как подсветить дубликаты в эксель

Поиск и удаление повторений

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

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

Примечание: В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.

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

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

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

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

Совет: Перед попыткой удаления повторений удалите все структуры и промежуточные итоги из своих данных.

Выберите Данные >Удалить дубликаты, а затем в разделе Столбцы проверка или снимите флажок столбцов, в которых нужно удалить дубликаты. Например, на данном листе в столбце «Январь» содержатся сведения о ценах, которые нужно сохранить. Поэтому флажок Январь в поле Удаление дубликатов нужно снять.

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

Макрос для выделения дубликатов разными цветами

Как известно, в последних версиях Excel легко выделить дубликаты цветом, — для этого есть специальная опция в «условном форматировании».
Достаточно выделить диапазон, задать цвет заливки, — и все повторяющиеся (или, наоборот, уникальные) значения будут выделены.

Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.
В этом случае, без макросов не обойтись.

Ниже приведён макрос, который как раз и решает эту задачу
(достаточно выделить диапазон ячеек, запустить макрос, — и повторяющиеся непустые ячейки получат одинаковый цвет заливки)

Sub ВыделитьДубликатыРазнымиЦветами() On Error Resume Next ' массив цветов, используемых для заливки ячеек-дубликатов Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _ 9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213) Dim coll As New Collection, dupes As New Collection, _ cols As New Collection, ra As Range, cell As Range, n& Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange) If Err Then Exit Sub ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False For Each cell In ra.Cells ' запонимаем значение дубликатов в коллекции dupes Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value) If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value) Next cell For i& = 1 To dupes.Count ' заполняем коллекцию cols цветами для разных дубликатов n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1 Next For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет cell.Interior.color = cols(CStr(cell.Value)) Next cell Application.ScreenUpdating = True End Sub

• 81798 просмотров

Выделение в EXCEL дубликатов, встречающихся 3 или 4 раза

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

• Выделите диапазон содержащий список значений, например, A3:A16 ;
• Вызовите Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило/ Использовать формулу для определения форматируемых ячеек) ;
• Введите формулу =СЧЁТЕСЛИ($A$3:$A$16;$A3)=4
• Выберите нужное форматирование;
• Нажмите ОК

Затем создадим еще одно правило:

• Вызовите Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило/ Использовать формулу для определения форматируемых ячеек) ;
• Введите формулу =СЧЁТЕСЛИ($A$3:$A$16;$A3)=3
• Выберите другое форматирование;
• Нажмите ОК

Значения, повторяющиеся 3 раза, выделены красным; 4 раза – желтым.

Выделить цветом дубликаты значений во всех листах книги

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

Sub ColorsDoubles() On Error Resume Next ' массив цветов, используемых для заливки ячеек-дубликатов Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _ 9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213) Dim coll As New Collection, dupes As New Collection, _ cols As New Collection, ra As Range, cell As Range, n& Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange) If Err Then Exit Sub ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False For Each cell In ra.Cells ' запонимаем значение дубликатов в коллекции dupes Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value) If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value) Next cell For i& = 1 To dupes.Count ' заполняем коллекцию cols цветами для разных дубликатов n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1 Next For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет cell.Interior.Color = cols(CStr(cell.Value)) ' если надо окрасить всю строку,то cell.EntireRow.Interior.color = cols(CStr(cell.Value)) Next cell Application.ScreenUpdating = True End Sub 

По комментарию ниже сделал — не получилось.

Sub ColorsDoubles() On Error Resume Next ' массив цветов, используемых для заливки ячеек-дубликатов Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _ 9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213) Dim coll As New Collection, dupes As New Collection, _ cols As New Collection, ra As Range, cell As Range, n& For Each oneSheet In ThisWorkbook.Sheets Err.Clear: Set ra = oneSheet.UsedRange Next If Err Then Exit Sub ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False For Each cell In ra.Cells ' запонимаем значение дубликатов в коллекции dupes Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value) If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value) Next cell For i& = 1 To dupes.Count ' заполняем коллекцию cols цветами для разных дубликатов n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1 Next For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет cell.Interior.Color = cols(CStr(cell.Value)) ' если надо окрасить всю строку,то cell.EntireRow.Interior.color = cols(CStr(cell.Value)) Next cell Application.ScreenUpdating = True End Sub