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

покупка

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

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

Выявление и пометка дубликатов цветом с помощью Kutools for Excel

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

Стратегия Условное форматирование Функция может оказать вам приятную услугу при маркировке дубликатов цветом.

1. Выберите список, из которого вы хотите отметить повторяющиеся значения, щелкните Главная > Условное форматирование > Выделите правила ячеек > Дублирующие значения. Смотрите скриншот:

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

3. Нажмите OK, повторяющиеся значения отмечены цветом.

Пометить дубликаты формулами

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

Скопируйте и вставьте одну из формул ниже в пустую ячейку, например B2,

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

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

Затем перетащите дескриптор автозаполнения вниз, чтобы скопировать формулу в ячейки, если в ячейке отображается ИСТИНА or Дублировать, относительная ячейка дублируется или уникальна. Скриншот:

Выявление и пометка дубликатов цветом с помощью Kutools for Excel

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

После установки Kutools for Excel, пожалуйста, сделайте, как показано ниже 🙁 Скачать бесплатно Kutools for Excel сейчас!)

1. Выберите список, в котором вы хотите найти дубликаты, и отметьте его, нажмите Кутулс > Выберите > Выберите повторяющиеся и уникальные ячейки. Смотрите скриншот:

2. Тогда Выберите повторяющиеся и уникальные ячейки диалог, проверьте Дубликаты (кроме 1-го) or Все дубликаты (включая 1-й) вариант, как вам нужно, затем перейдите к Обработка результатов раздел, чтобы указать цвет фона или шрифта по своему усмотрению. Смотрите скриншот:

3. Нажмите Ok, и появится диалоговое окно, напоминающее, сколько дубликатов было выбрано.

4. Нажмите OK закрыть его.

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

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

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

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

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

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



1. Выделите диапазон ячеек A2:C19 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся диалоговом окне выделите опцию: «Использовать формулу для определения форматируемых ячеек», а в поле ввода введите следующую формулу: $C3:$C20)+0);2)’ >
3. Нажмите на кнопку «Формат» и на закладке заливка укажите зеленый цвет. И нажмите ОК на всех открытых окнах.

В результате мы выделили целые строки условным форматированием и получаем эффект как изображено на рисунке:

Теперь работать с такой читабельна таблицей намного удобнее. Можно комфортно проводить визуальный анализ всех показателей.

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

Как выделить повторяющиеся значения в 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 существует несколько способов фильтрации уникальных значений или удаления повторяющихся значений:

• Чтобы отфильтровать уникальные значения, щелкните Данные >Сортировка & Фильтр >Дополнительно.
• Чтобы удалить повторяющиеся значения, щелкните Data > Data Tools>Remove Duplicates (Удалить дубликаты).
• Чтобы выделить уникальные или повторяющиеся значения, используйте команду Условное форматирование в группе Стиль на вкладке Главная .

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

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

Повторяющееся значение — это значение, в котором все значения по крайней мере в одной строке идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не от базового значения, хранящегося в ячейке. Например, если у вас одно и то же значение даты в разных ячейках, одна из которых имеет формат «08.03.2006», а вторая — «8 марта 2006 г.», значения будут уникальными.

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

Фильтрация уникальных значений

1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
2. Щелкните Данные > Дополнительно (в группе Фильтр & сортировки ).

Важно: Вставьте текст оповещения здесь.

Во всплывающем окне Расширенный фильтр выполните одно из следующих действий.

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

• Щелкните Фильтровать список на месте.

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

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

Уникальные значения из диапазона будут скопированы в новое расположение.

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

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

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

1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
2. На вкладке Данные щелкните Удалить дубликаты (в группе Инструменты для работы с данными ).
3. Выполните одно или несколько из указанных ниже действий.
4. В разделе Столбцы выберите один или несколько столбцов.
5. Чтобы быстро выбрать все столбцы, нажмите кнопку Выбрать все.
6. Чтобы быстро очистить все столбцы, щелкните Отменить выбор всех. Если диапазон ячеек или таблицы содержит много столбцов и требуется выбрать только несколько столбцов, может оказаться проще нажать кнопку Отменить выбор всех, а затем в разделе Столбцы выберите эти столбцы.

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. Если в этих столбцах найден дубликат, будет удалена вся строка, включая другие столбцы в таблице или диапазоне.

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

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

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

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

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стиль щелкните небольшую стрелку для параметра Условное форматирование, а затем выберите пункт Правила выделения ячеек и выберите Повторяющиеся значения.

Важно: Вставьте текст оповещения здесь.

Введите нужные значения, а затем выберите формат.

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку условного форматирования, а затем щелкните Управление правилами , чтобы открыть всплывающее окно Диспетчер правил условного форматирования .

Важно: Вставьте текст оповещения здесь.

Выполните одно из следующих действий:

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

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

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

Щелкните Формат , чтобы открыть всплывающее окно Формат ячеек .

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

В Excel в Интернете можно удалить повторяющиеся значения.

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

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

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

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

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. При обнаружении дубликата в столбцах Column1 и Column2 будет удалена вся строка, включая данные из Столбца 3.

Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.