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

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

  • автор:

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

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

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

Вот как это сделать:

Окно

  1. Выделите диапазон ячеек, которые нужно отформатировать.
  2. Перейдите в раздел Главная >Форматировать как таблицу.
  3. Выберите стиль таблицы, в котором применяется заливка с чередованием строк.
  4. Чтобы изменить заливку со строк на столбцы, выберите таблицу в разделе Конструктор таблиц, а затем снимите флажок Чередующиеся строки и проверка поле Столбцы с полосами.

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

Применение полос к строкам или столбцам с помощью условного форматирования

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

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

Вот как это сделать:

Кнопка

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

Кнопка

  • Перейдите на главную страницу >условное форматирование >новое правило.
  • В поле Выбор типа правила выберите Использовать формулу, чтобы определить, какие ячейки следует форматировать.

    параметр очистки формата

    Советы:

    • Чтобы изменить правило условного форматирования, щелкните одну из ячеек, к которым применено правило, перейдите в раздел Главная >Условное форматирование >Управление правилами >Изменить правило, а затем внесите изменения.
    • Чтобы удалить условное форматирование из ячеек, выделите их, перейдите в раздел Главная >Очистить и выберите Очистить форматы.

    Кнопка

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

    Как выделить повторяющиеся значения в 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
    • Скачать примеры

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

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

    Как известно, в последних версиях 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
    • 83597 просмотров

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

    Среди стандартных средств Microsoft Excel есть много разных способов выделить дубликаты цветом. Самый простой и быстрый — с помощью условного форматирования. Для этого достаточно выделить диапазон ячеек и выбрать на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional Formatting — Highlight Cells Rules — Duplicate Values) : duplicates-coloring1.pngОднако в этом случае цвет заливки у всех ячеек будет одинаковым, т.е. он просто сигнализирует о том, что у элемента где-то еще в диапазоне есть повторы, но никак не помогает их найти. Исправить ситуацию можно с помощью небольшого макроса, который будет заливать каждую пару (или больше) повторяющихся дубликатов своим цветом: duplicates-coloring2.pngТак гораздо нагляднее, правда? Конечно, при большом количестве повторяющихся ячеек оттенки различить будет трудно, но при относительно небольшом количестве дубликатов этот способ сработает отлично.
    Чтобы использовать этот макрос нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставьте новый пустой модуль через меню Insert — Module и скопируйте туда код этого макроса:

    Sub DuplicatesColoring() Dim Dupes() 'объявляем массив для хранения дубликатов ReDim Dupes(1 To Selection.Cells.Count, 1 To 2) Selection.Interior.ColorIndex = -4142 'убираем заливку если была i = 3 For Each cell In Selection If WorksheetFunction.CountIf(Selection, cell.Value) > 1 Then For k = LBound(Dupes) To UBound(Dupes) 'если ячейка уже есть в массиве дубликатов - заливаем If Dupes(k, 1) = cell Then cell.Interior.ColorIndex = Dupes(k, 2) Next k 'если ячейка содержит дубликат, но еще не в массиве - добавляем ее в массив и заливаем If cell.Interior.ColorIndex = -4142 Then cell.Interior.ColorIndex = i Dupes(i, 1) = cell.Value Dupes(i, 2) = i i = i + 1 End If End If Next cell End Sub

    Теперь можно выделить любой диапазон с данными на листе и запустить наш макрос с помощью сочетания клавиш Alt+F8 или через кнопку Макросы (Macros) на вкладке Разработчик (Developer) .

    Ссылки по теме

    • Выделение дубликатов цветом
    • Что такое макросы, куда вставлять код макроса на Visual Basic, как их запускать
    • Как подсчитать количество уникальных значений в заданном диапазоне ячеек
  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *