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

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

Выделение дубликатов цветом

Допустим, что у нас имеется длинный список чего-либо (например, товаров), и мы предполагаем, что некоторые элементы этого списка повторяются более 1 раза.

Как быстро выделить все дубликаты, если они есть?

Выделяем весь список, начиная в нашем примере с ячейки А2 и идем в меню Формат — Условное форматирование (Format — Conditional Formatting) . Выбираем из выпадающего списка вариант условия Формула и вводим такую проверку:

в английском Excel это будет соответственно =COUNTIF($A:$A;A2)>1

Эта простая функция ищет сколько раз содержимое текущей ячейки встречается в столбце А. Если это количество повторений больше 1, т.е. у элемента есть дубликаты, то срабатывает заливка ячейки. Для выбора цвета выделения в окне Условное форматирование нажмите кнопку Формат. и перейдите на вкладку Вид.

Знаете ли Вы, что такое «Большой Взрыв»?
Согласно рупору релятивистской идеологии Википедии «Большой взрыв (англ. Big Bang) — это космологическая модель, описывающая раннее развитие Вселенной, а именно — начало расширения Вселенной, перед которым Вселенная находилась в сингулярном состоянии. Обычно сейчас автоматически сочетают теорию Большого взрыва и модель горячей Вселенной, но эти концепции независимы и исторически существовало также представление о холодной начальной Вселенной вблизи Большого взрыва. Именно сочетание теории Большого взрыва с теорией горячей Вселенной, подкрепляемое существованием реликтового излучения. «
В этой тираде количество нонсенсов (бессмыслиц) больше, чем количество предложений, иначе просто трудно запутать сознание обывателя до такой степени, чтобы он поверил в эту ахинею.
На самом деле взорваться что-либо может только в уже имеющемся пространстве.
Без этого никакого взрыва в принципе быть не может, так как «взрыв» — понятие, применимое только внутри уже имеющегося пространства. А раз так, то есть, если пространство вселенной уже было до БВ, то БВ не может быть началом Вселенной в принципе. Это во-первых.
Во-вторых, Вселенная — это не обычный конечный объект с границами, это сама бесконечность во времени и пространстве. У нее нет начала и конца, а также пространственных границ уже по ее определению: она есть всё (потому и называется Вселенной).
В третьих, фраза «представление о холодной начальной Вселенной вблизи Большого взрыва» тоже есть сплошной нонсенс.
Что могло быть «вблизи Большого взрыва», если самой Вселенной там еще не было? Подробнее читайте в FAQ по эфирной физике.

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

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

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

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

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

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

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

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

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

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

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

Нажмите кнопку ОК.

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

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

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

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

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

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

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

Как выделить повторяющиеся значения в Excel разными цветами?

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

Отмечаем разными цветами ячейки с повторяющимися значениями

Нам нужно чтобы макрос VBA, при помощи разных цветов отмечал в столбце повторяющиеся значения. Более или менее так:

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

Вспомогательный лист (с цветами) выглядит примерно так:

Здесь пользователь может указать свои желаемые пользовательские цвета для подсветки дубликатов.

Установка цветов для подсветки ячеек с дубликатами

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

Поскольку лучше сделать так, чтобы ячейки обновлялись каждый раз, когда что-то вводится в ячейку, макрос обрабатывает событие onChange на листе «Данные».

Макрос выглядит следующим образом:

Option Explicit
Private Sub Worksheet_Change( ByVal Target As Range)
Dim rngЦвета As Range
Dim rngК_Покраске As Range
Dim СчетчикЦветов As Integer
Dim Счетчик As Integer
Dim rngСтолбец As Range
Dim rngЗаполненДанные As Range
‘ диапазон ячеек с цветами
Set rngЦвета = wksВспомогательный.Range( «rngColorStart» ).Resize(wksВспомогательный.Range( «settIleColors» ).Value, 1)
‘ диапазон с данными для заливки цветом
Set rngК_Покраске = wksДанные.Range(Range( «rngDataStart» ), Cells(65535, Range( «rngDataStart» ).Column). End (xlUp))
‘ столбец с данными
Set rngСтолбец = Columns( «B» )
With wksДанные
Set rngЗаполненДанные = .Range(.Range( «rngDataStart» ), .Range( «rngDataStart» ).Offset(10000). End (xlUp))
End With
If Not Intersect(Target, rngСтолбец) Is Nothing Then ‘ если изменение в столбце с данными
Application.ScreenUpdating = False ‘ выключаю «мигание» экрана
‘ Очищаем всю область данных (устанавливаем везьде цвет фона по умолчанию)
rngЗаполненДанные.Resize(rngЗаполненДанные.Count + 1).Interior.ColorIndex = _
wksВспомогательный.Range( «rngFonStandart» ).Interior.ColorIndex
СчетчикЦветов = 1 ‘ сброс счётчика цветов
With rngК_Покраске
‘ первая ячейка
If Application.WorksheetFunction.CountIf(rngК_Покраске, .Cells(1).Value) > 1 Then
.Cells(1).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex
СчетчикЦветов = СчетчикЦветов + 1
If СчетчикЦветов > rngЦвета.Count Then СчетчикЦветов = 1
End If
‘Если имеется более чем одна ячейка
If rngЗаполненДанные.Count > 1 Then
‘ это для следующих ячеек
For Счетчик = 2 To .Count
If Application.WorksheetFunction.CountIf(rngК_Покраске, _
.Cells(Счетчик).Value) > 1 Then
If Application.WorksheetFunction.CountIf(Range( «rngDataStart» ).Resize(Счетчик — 1), .Cells(Счетчик).Value) > 0 Then
.Cells(Счетчик).Interior.ColorIndex = _
rngЗаполненДанные.Find(what:=.Cells(Счетчик).Value, after:=.Cells(Счетчик), SearchDirection:=xlPrevious, lookat:=xlWhole).Interior.ColorIndex
Else
.Cells(Счетчик).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex
СчетчикЦветов = СчетчикЦветов + 1
If СчетчикЦветов > rngЦвета.Count Then СчетчикЦветов = 1
End If
End If
Next Счетчик
End If
End With
Application.ScreenUpdating = True
End If
End Sub

Ниже, как обычно, файл с примером для скачивания:

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры