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

Выделение повторяющихся значений в MS EXCEL

​Смотрите также​ какую вкладку добавлять​Insert — Module​

Задача

​.Cells(Счетчик).Interior.ColorIndex = _​ с начала.​ ячейкой при вводе​Во всплывающем меню​На вкладке​Изменить правило​Стили​

Решение

• ​ ячейку.​ Значение считается повторяющимся,​​Выберите ячейки, которые нужно​​и выберите пункт​
• ​Примечание:​фильтровать список на месте​.​Настроим Условное форматирование для​
• ​ кнопки?​

​и скопируйте туда​rngЗаполненДанные.Find(what:=.Cells(Счетчик).Value, after:=.Cells(Счетчик), SearchDirection:=xlPrevious,​Поскольку лучше сделать так,​ формулы должна быть​​Стиль​​Данные​

• ​.​щелкните​​Примечание:​​ если все значения​
• ​ проверить на наличие​Управление правилами​ Данные будут удалены из​.​Чтобы удалить повторяющиеся значения,​
• ​ выделения только повторяющихся​

​и можно ли​ код этого макроса:​ lookat:=xlWhole).Interior.ColorIndex​ чтобы ячейки обновлялись​A3​​выберите пункт​​в разделе​Выберите нужные параметры и​Условное форматирование​ При копировании результатов фильтрации​ в одной строке​ повторений.​, чтобы открыть​

• ​ всех столбцов, даже​
• ​Чтобы скопировать в другое​

​ нажмите кнопку​ значений в списке.​ немного изменить макрос​

excel2.ru

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

​Sub DuplicatesColoring() Dim​​Else​ каждый раз, когда​(т.е. диапазон нужно​Классический​Сервис​ нажмите кнопку​, наведите указатель на​ в другое место​ полностью совпадают со​Примечание:​ всплывающее окно​ если вы не​ место результаты фильтрации:​данные > Работа с​Необходимо выделить ячейки, содержащие​ чтобы он охватывал​ Dupes() ‘объявляем массив​.Cells(Счетчик).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex​ что-то вводится в​ выделять сверху вниз).​, а затем во​

​нажмите кнопку​ОК​ пункт​ будут скопированы уникальные​

• ​ значениями в другой.​ В Excel не поддерживается​​Диспетчер правил условного форматирования​ ​ выбрали всех столбцов​Нажмите кнопку​​ данными​
  

• ​ значения, которые повторяются​ конкретный заданный диапозон​​ для хранения дубликатов​СчетчикЦветов = СчетчикЦветов​ ​ ячейку, макрос обрабатывает​ ​ Активная ячейка в​​ всплывающем меню​
  

• ​Удалить дубликаты​.​​Правила выделения ячеек​​ значения из выбранного​​ Повторяющиеся значения определяются​​ выделение повторяющихся значений​​.​​ на этом этапе.​

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

​Копировать в другое место​>​ в определенном диапазоне.​ и поставить событие​ ReDim Dupes(1 To​ + 1​ событие onChange на​ выделенном диапазоне –​Форматировать только первые или​.​Выделите диапазон ячеек или​и выберите​ диапазона. Исходные данные​ значением, которое отображается​ в области «Значения»​

​Выполните одно из действий,​ Например при выборе​.​Удалить повторения​ Выделять повторяющиеся значения​ при изменении содержимого​ Selection.Cells.Count, 1 To​If СчетчикЦветов >​ листе «Данные».​ белая, ее адрес​ последние значения​Установите один или несколько​ убедитесь в том,​Повторяющиеся значения​ при этом не​ в ячейке, а​ отчета сводной таблицы.​ указанных ниже.​ Столбец1 и Столбец2,​В поле​.​

​ будем с помощью​ любой ячейки в​​ 2) Selection.Interior.ColorIndex =​ rngЦвета.Count Then СчетчикЦветов​Макрос выглядит следующим образом:​ отображается в поле​выберите пункт​ флажков, соответствующих столбцам​ что активная ячейка​.​ изменятся.​ не тем, которое​

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

​На вкладке​

1. ​Чтобы добавить условное форматирование,​ но не Столбец3​Копировать​Чтобы выделить уникальные или​

2. ​ Условного форматирования (см.​​ данном диапозоне подсвечивание​​ -4142 ‘убираем заливку​​ = 1​​Option Explicit​​ Имя.​​Форматировать только уникальные или​

   

3. ​ таблицы, и нажмите​​ находится в таблице.​​В диалоговом окне​Установите флажок​

​ в ней хранится.​Главная​ нажмите кнопку​

• ​ используется для поиска​​введите ссылку на​​ повторяющиеся значения, команда​

​ Файл примера).​ автоматом перерисовыволось?​

• ​ если была i​​End If​​Private Sub Worksheet_Change(ByVal​

• ​Выберите нужное форматирование;​​ повторяющиеся значения​​ кнопку​На вкладке​

• ​Создать правило форматирования​​Только уникальные записи​ ​ Например, если в​выберите​Создать правило​ дубликатов «ключ» —​​ ячейку.​ ​Условного форматирования​

• ​выделите диапазон содержащий список​​БИТ​​ = 3 For​​End If​​ Target As Range)​

​Нажмите ОК​.​Удалить дубликаты​

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

​Данные​выберите нужные параметры​и нажмите кнопку​ различных ячейках содержатся​Условное форматирование​для отображения во​ значение ОБА Столбец1​Кроме того нажмите кнопку​в группе​ значений, например,​: И если можно​ Each cell In​Next Счетчик​Dim rngЦвета As​

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

​>​

1. ​ всплывающем окне​ & Столбец2.  Если дубликат​Свернуть диалоговое окно​стиль​

2. ​А3:А16​​ показать какая часть​​ Selection If WorksheetFunction.CountIf(Selection,​​End If​​ Range​​ выделение неповторяющихся значений​​значения в выбранном диапазоне​

   

3. ​Появится либо сообщение о​Сортировка и фильтр​

   • ​ОК​​.​​ в разных форматах​Правила выделения ячеек​

   • ​Создание правила форматирования​ находится в этих​​временно скрыть всплывающее​​на вкладке «​

   • ​;​ кода отвечает за​​ cell.Value) > 1​​End With​

     ​Dim rngК_Покраске As​ исчезнет.​выберите​ том, сколько повторяющихся​щелкните стрелку рядом​.​​При удалении повторяющихся значений​​ («08.12.2010» и «8​​>​​.​

     ​ столбцах, затем всей​​ окно, выберите ячейку​Главная​вызовите Условное форматирование (Главная/​ определенный выделенный диапозон!​ Then For k​Application.ScreenUpdating = True​ Range​Того же результата можно​уникальные​ значений было удалено​ с элементом​Вы можете создать правило​ данные удаляются только​ дек 2010″), они​Повторяющиеся значения​Убедитесь, что выбран соответствующий​ строки будут удалены,​ на листе и​

4. ​».​​ Стили/ Условное форматирование/​​И какая часть​ = LBound(Dupes) To​End If​Dim СчетчикЦветов As​ добиться вызвав команду​или​​ и сколько уникальных​​Фильтр​ для выделения уникальных​

5. ​ из выделенного диапазона​ считаются уникальными. Рекомендуется​.​ лист или таблица​

Удаление дубликатов с промежуточными итогами или структурированных данных проблем

​ включая другие столбцы​ нажмите кнопку​Фильтр уникальных значений и​ Правила выделения ячеек/​ кода отвечает за​ UBound(Dupes) ‘если ячейка​End Sub​ Integer​ меню Главная/ Стили/​повторяющиеся​ осталось, либо сообщение​и выберите пункт​

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

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

​Развернуть​

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

1. ​ Повторяющиеся значения…);​ наступление события (если​ уже есть в​Ниже, как обычно, файл​

2. ​Dim Счетчик As​​ Условное форматирование/ Правила​​.​​ о том, что​​Расширенный фильтр​​ на листе определенным​​ Любые другие значения,​​ значения или применить​​ оператором​​Показать правила форматирования для​​ диапазон.​
   

3. ​.​ являются две сходные​нажмите ОК.​

​ значения в одной​

​ массиве дубликатов -​

1. ​ с примером для​ Integer​ выделения ячеек/ Повторяющиеся​В меню​

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

3. ​ заливаем If Dupes(k,​ скачивания:​

   • ​Dim rngСтолбец As​ значения. В диалоговом​​Форматировать с помощью​​ значения не было​Выполните одно из следующих​​ полезно, когда в​​ пределами этого диапазона​

   • ​ форматирование, чтобы перед​выберите форматирование для​ начинается. При необходимости​​ОК​​только уникальные записи​ — для представления​ выделять дубликаты только​ диапозоне поменяется то​​ 1) = cell​ ​Скачать выделение повторяющихся значений​​ Range​​ окне выбрать уникальные​выберите нужный вариант​ удалено.​ действий:​ данных содержится несколько​ ячеек или таблицы,​​ удалением повторяющихся значений​ применения к повторяющимся​​ выберите другой диапазон​​, и появится сообщение,​, а затем нажмите​​ списка уникальных значений.​​ если установлен Флажок​

4. ​ заново запускается макрос​​ Then cell.Interior.ColorIndex =​​ ячеек разными цветами​​Dim rngЗаполненДанные As​ (о несоответствии терминологии​​ форматирования уникальных или​

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

6. ​кнопку ОК​​ Есть важные различия,​​ «Выделить дубликаты» (ячейка​ и снова подсвечиваются​​ Dupes(k, 2) Next​​Конечно, это не идеальное​

7. ​ Range​ повторяющихся значений читайте​ повторяющихся значений.​ Если в диапазоне ячеек​Необходимые действия​Выделите одну или несколько​​ не перемещаются. Так​​ что будет получен​ кнопку​Свернуть​ повторяющиеся значения были​.​​ однако: при фильтрации​​B1​

support.office.com

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

​ соответствующие значения)?​ k ‘если ячейка​ решение, но в​’ диапазон ячеек​ в статье Классификация​Вы можете отредактировать существующее​ или таблице содержится​Фильтрация диапазона ячеек или​ ячеек в диапазоне,​ как данные удаляются​ ожидаемый результат.​

1. ​ОК​во всплывающем окне​ удалены или остаются​

   ​Уникальные значения из диапазона​​ уникальных значений повторяющиеся​)​SLAVICK​ содержит дубликат, но​

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

   

3. ​.​относится к​​ количества уникальных значений.​​ скопирует на новое​ значения будут видны​выделите диапазон содержащий список​: Можно — для​​ еще не в​​ выполняет свою функцию.​

   

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

​Set rngЦвета =​​ В этом случае,​​ условное форматирование, применяемое​ нужно выбрать только​Выделите диапазон ячеек и​ сводной таблицы.​ перед удалением повторяющихся​ Если формулы в ячейках​При использовании функции​

1. ​временно скрыть ее.​ Нажмите кнопку​ место.​

   ​ только временно. Тем​​ значений, например,​ этого замените :​ массиве — добавляем​ Кроме того, оно​ wksВспомогательный.Range(«rngColorStart»).Resize(wksВспомогательный.Range(«settIleColors»).Value, 1)​

2. ​ отменить выделение неповторяющихся​​ к уникальным или​​ несколько из них,​​ щелкните​​На вкладке​​ записей рекомендуется скопировать​​ разные, а значения​Удаление дубликатов​ Выберите новый диапазон​ОК​

   

   ​При удалении повторяющихся значений​ не менее удаление​B3:B16​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Selection​ ее в массив​

   

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

   

3. ​Фильтровать список на месте​​Главная​​ исходный диапазон ячеек​

support.office.com

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

​ одинаковые, такие значения​повторяющиеся данные удаляются​ ячеек на листе,​, чтобы закрыть​ на значения в​ повторяющихся значений означает,​;​на​ и заливаем If​ и отправной точкой​ данными для заливки​ Флажка будет невозможно.​Выделите одну или несколько​Выделить все​.​в группе​ или таблицу на​ считаются повторяющимися. Например,​ безвозвратно. Чтобы случайно​ а затем разверните​ сообщение.​ диапазоне ячеек или​ что вы окончательное​вызовите Условное форматирование (Главная/​Range(«a1:a100»)​ cell.Interior.ColorIndex = -4142​ для более комплексных​ цветом​Пример настраиваемого макроса для​ ячеек в диапазоне,​и выделите только​Копирование результатов фильтрации в​Стили​ другой лист или​ если в ячейке​ не потерять необходимые​ узел во всплывающем​U тменить отменить изменения,​ таблице — единственный​ удаление повторяющихся значений.​ Стили/ Условное форматирование/​Цитата​

​ Then cell.Interior.ColorIndex =​​ решений.​Set rngК_Покраске =​ выделения повторяющихся значений​ таблице или отчете​ нужные столбцы.​ другое место​​щелкните​​ в другую книгу.​ A1 содержится формула​​ сведения, перед удалением​​ окне еще раз​ щелкните (или нажать​ эффект. Другие значения​Повторяющееся значение входит в​ Создать правило/ Использовать​БИТ, 14.11.2015 в​ i Dupes(i, 1)​Среди стандартных средств Microsoft​ wksДанные.Range(Range(«rngDataStart»), Cells(65535, Range(«rngDataStart»).Column).End(xlUp))​ разным цветом заливки​​ сводной таблицы.​​Для наглядного отображения уникальных​Выделите диапазон ячеек, щелкните​​Условное форматирование​​Примечание:​=2-1​

​ повторяющихся данных рекомендуется​. Выберите правило​

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

1. ​ клавиши Ctrl +​ вне диапазона ячеек​ котором все значения​ формулу для определения​

2. ​ 15:26, в сообщении​​ = cell.Value Dupes(i,​​ Excel есть много​​’ столбец с​​ ячеек Excel.​​На вкладке​​ или повторяющихся значений​

   

3. ​Скопировать результат в другое​и выберите пункт​

   ​ Нельзя удалить повторяющиеся значения,​	​, а в ячейке​
   ​ скопировать исходные данные​ и нажмите кнопку​	​ Z на клавиатуре).​ или таблице не​​ в по крайней​​ форматируемых ячеек);​
   ​ № 4200?’200px’:»+(this.scrollHeight+5)+’px’);»>И какая​ 2) = i​	​ разных способов выделить​​ данными​Нам нужно чтобы макрос​​Главная​ к ним можно​​ место​​Создать правило​ если выделенные фрагмент​ ​ A2 — формула​​ на другой лист.​Изменить правило​Нельзя удалить повторяющиеся значения​ будет изменить или​ мере одна строка​введите формулу =И(СЧЁТЕСЛИ($B$3:$B$16;$B3)>1;$B$1)​ часть кода отвечает​

4. ​ i = i​​ дубликаты цветом. Самый​​Set rngСтолбец =​​ VBA, при помощи​​в разделе​

Дополнительные параметры

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

​ применить условное форматирование.​, а затем в​.​ содержит структурированные данные​=3-2​Выделите диапазон ячеек с​, чтобы открыть​ из структуры данных,​ переместить.  При удалении​ идентичны всех значений​Обратите внимание, что в​ за наступление события​ + 1 End​ простой и быстрый​ Columns(«B»)​ разных цветов отмечал​Формат​ Например, выделение повторяющихся​

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

1. ​ формуле использована относительная​Добавил в модуль​ If End If​ — с помощью​

2. ​With wksДанные​​ в столбце повторяющиеся​​щелкните стрелку рядом​​ данных определенным цветом​​Копировать в​​Стиль​​ Перед удалением повторяющихся​

   

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

   ​ адресация, поэтому активной​​ листа на изменение.​ Next cell End​ условного форматирования. Для​Set rngЗаполненДанные =​ значения. Более или​ с кнопкой​​ помогает найти и​​введите ссылку на​выберите пункт​

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

​ значений нужно удалить​ такие значения считаются​Совет:​.​ итоги. Чтобы удалить​ в списке, но​ зависит от того,​ ячейкой при вводе​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Sub Worksheet_Change(ByVal​

1. ​ Sub​ этого достаточно выделить​ .Range(.Range(«rngDataStart»), .Range(«rngDataStart»).Offset(10000).End(xlUp))​ менее так:​

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

3. ​ Target As Range)​​Теперь можно выделить любой​​ диапазон ячеек и​End With​​Макрос отмечает повторяющиеся значения​​и выберите пункт​

Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям

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

1. ​ удаляются.​ ячейке, не базового​B3​On Error Resume​

2. ​ диапазон с данными​​ выбрать на вкладке​​If Not Intersect(Target,​​ более или менее​​Управление правилами​​Выделите одну или несколько​​ При копировании результатов фильтрации​​ списке​​Выделите диапазон ячеек или​

3. ​ различные числовые форматы,​​ структуры и промежуточные​​нажмите кнопку​​ итоги. Для получения​​Поскольку данные будут удалены​ значения, хранящегося в​​(т.е. диапазон нужно​ Next​​ на листе и​​Главная — Условное форматирование​ rngСтолбец) Is Nothing​​ таким образом: Если​

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

5. ​Форматировать только уникальные или​​ дополнительных сведений отображается​​ окончательно, перед удалением​ ячейке. Например, если​ выделять сверху вниз).​

Изменение правил расширенного условного форматирования

​Set changeCell =​ запустить наш макрос​ — Правила выделения​ Then ‘ если​ бы речь шла​

1. ​Убедитесь, что в меню​ таблице или отчете​ будут скопированы уникальные​ последние значения​

2. ​ что активная ячейка​​ Например, если значение​​ данных.​​ повторяющиеся значения​​ Структура списка данных​​ повторяющихся значений рекомендуется​​ у вас есть​​ Активная ячейка в​​ Target​

3. ​ с помощью сочетания​​ ячеек — Повторяющиеся​​ изменение в столбце​ только о выборе​

4. ​Показать правила форматирования для​ сводной таблицы.​​ значения из выбранного​​выберите пункт​

5. ​ находится в таблице.​ в ячейке A1​​На вкладке​​.​

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

1. ​ на листе «и»​ скопировать исходный диапазон​ то же значение​ выделенном диапазоне –​

2. ​Application.ScreenUpdating = False​​ клавиш​​ значения (Home -​​ с данными​​ ячеек, данные которых​выбран соответствующий лист​​На вкладке​​ диапазона. Исходные данные​​Форматировать только уникальные или​​На вкладке​

   

3. ​ имеет формат​Данные​

   ​В списке​	​ удалить промежуточные итоги.​
   ​ ячеек или таблицу​ даты в разных​	​ белая и ее​Run «DuplicatesColoring»​​Alt+F8​​ Conditional Formatting -​
   ​Application.ScreenUpdating = False​ просто повторяются, тогда​	​ или таблица.​​Главная​ при этом не​​ повторяющиеся значения​Данные​​1,00​​нажмите кнопку​Формат все​ ​Примечание:​​ в другой лист​ ячейках, один в​ адрес отображается в​Run «ВыделитьДубликатыРазнымиЦветами»​или через кнопку​ Highlight Cells Rules​ ‘ выключаю «мигание»​

4. ​ можно просто использовать​​Выберите правило и нажмите​​в разделе​​ изменятся.​​.​

Дополнительные параметры

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

​в разделе​, а в ячейке​Удалить дубликаты​Измените описание правила​ Условное форматирование полей в​ или книгу.​ формате «3/8/2006», а​ поле Имя.​Application.ScreenUpdating = True​Макросы (Macros)​ — Duplicate Values)​ экрана​ условное форматирование. Однако​ кнопку​Формат​Установите флажок​В списке​Работа с данными​

​ A2 — формат​​и в разделе​выберите​ области «Значения» отчета​Выполните следующие действия.​ другой — как​выберите нужное форматирование;​End Sub​на вкладке​

1. ​:​’ Очищаем всю​ в обсуждаемом примере​Изменить правило​

2. ​щелкните стрелку рядом​​Только уникальные записи​​значения в выбранном диапазоне​​нажмите кнопку​​1​​Столбцы​​уникальные​

   

3. ​ сводной таблицы по​Выделите диапазон ячеек или​ «8 мар «2006​нажмите ОК.​​БИТ​​Разработчик (Developer)​

   ​Однако в этом случае​ область данных (устанавливаем​ дело обстоит немного​.​ с элементом​и нажмите кнопку​выберите пункт​Удалить дубликаты​, эти значения не​

   ​установите или снимите​​или​ уникальным или повторяющимся​ убедитесь, что активная​ г. значения должны​Сняв Флажок «Выделить дубликаты»​: СПАСИБО!!!​​.​​ цвет заливки у​ везьде цвет фона​

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

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

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

2. ​АЛЕКСАНДР1986​​БИТ​​ всех ячеек будет​​ по умолчанию)​​ необходимо сделать так,​ нажмите кнопку​​, выберите пункт​​.​​или​​Установите один или несколько​Более новые версии​​ в которых нужно​​.​

   

3. ​Быстрое форматирование​ таблице.​​Установите флажок перед удалением​​ исчезнет.​

Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям

​: Не подскажите я​: не подскажите как​ одинаковым, т.е. он​rngЗаполненДанные.Resize(rngЗаполненДанные.Count + 1).Interior.ColorIndex​ чтобы разные значения​ОК​Правила выделения ячеек​При удалении повторяющихся значений​

1. ​повторяющиеся​ флажков, соответствующих столбцам​ Office 2011 ​ удалить повторения.​

2. ​Нажмите кнопку​​Выполните следующие действия.​​На вкладке​​ дубликаты:​​Примечание:​ также использую эти​​ сделать если в​​ просто сигнализирует о​​ = _​​ были отмечены разными​

   

3. ​.​​, а затем —​​ данные удаляются только​​.​​ таблицы, и нажмите​Выделите диапазон ячеек или​​Например, на данном листе​Формат​​Выделите одну или несколько​​данные​Перед удалением повторяющиеся​​Мы стараемся как​

4. ​ макросы но они​​ таблице​​ том, что у​​wksВспомогательный.Range(«rngFonStandart»).Interior.ColorIndex​​ цветами. Кроме того,​​Настроим Условное форматирование для​​ пункт​

5. ​ из выделенного диапазона​​В списке​​ кнопку​ убедитесь в том,​ в столбце «Январь»​

Изменение правил расширенного условного форматирования

​для отображения во​ ячеек в диапазоне,​нажмите кнопку​ значения, рекомендуется установить​ можно оперативнее обеспечивать​

1. ​ почему-то у меня​два или более​ элемента где-то еще​СчетчикЦветов = 1​

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

   

3. ​Удалить повторения​​ для первой попытке​​ вас актуальными справочными​ не работают не​

4. ​ значения повторяются выделять​ в диапазоне есть​​ ‘ сброс счётчика​​ легко выбирать эти​

5. ​ значений в списке.​.​​ Любые другие значения,​​выберите нужный вариант​

support.office.com

Выделение неповторяющихся значений в MS EXCEL

​.​ находится в таблице.​ ценах, которые нужно​

Задача

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

Решение

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

• ​ форматирования уникальных или​Совет:​​На вкладке​​ сохранить.​
• ​.​На вкладке​Работа с данными​ — или применить​ языке. Эта страница​
• ​ делаю не так?​

​Если другие два​ не помогает их​With rngК_Покраске​Вспомогательный лист (с цветами)​ значения, которые не​​ нажмите кнопку​​ пределами этого диапазона​ повторяющихся значений.​ Если в диапазоне ячеек​Данные​Поэтому флажок​Выберите номер, шрифт, границы​Главная​

• ​).​
• ​ условное форматирование на​

​ переведена автоматически, поэтому​SLAVICK​ или более значения​

​ найти. Исправить ситуацию​’ первая ячейка​ выглядит примерно так:​ повторяются в данном​ОК​ ячеек или таблицы,​Вы можете отредактировать существующее​ или таблице содержится​в группе​Январь​ и заливка формат,​в группе​Выполните одно или несколько​ — для подтверждения​ ее текст может​

excel2.ru

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

​: Вы не поместили​ повторяются выделять их​ можно с помощью​If Application.WorksheetFunction.CountIf(rngК_Покраске, .Cells(1).Value)​

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

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

​Сортировка и фильтр​в поле​ который нужно применять,​стиль​ следующих действий.​ добиться таких результатов,​ содержать неточности и​ макрос :​ другим цветом!​ небольшого макроса, который​ > 1 Then​ свои желаемые пользовательские​ значения будем с​Вы можете создать правило​ не перемещаются. Так​ условное форматирование, применяемое​ нужно выбрать только​нажмите кнопку​Удаление дубликатов​

​ если значение в​щелкните маленькую стрелку​

​В разделе​ предполагается, что уникальные​ грамматические ошибки. Для​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Sub Worksheet_Change(ByVal Target​

​и т.д.​

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

​ будет заливать каждую​.Cells(1).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex​ цвета для подсветки​ помощью Условного форматирования​ для выделения уникальных​ как данные удаляются​ к уникальным или​ несколько из них,​Дополнительно​нужно снять.​ ячейке удовлетворяет условию​Условное форматирование​столбцы​ значения.​ нас важно, чтобы​ As Range)​для каждой группы​ пару (или больше)​СчетчикЦветов = СчетчикЦветов​

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

​Нажмите кнопку​

​ и нажмите кнопку​
​и затем щелкните​выберите один или несколько​
​Выполните следующие действия.​ эта статья была​
​On Error Resume​ значений свой цвет!​
​ повторяющихся дубликатов своим​ + 1​
​​Будем выделять неповторяющиеся значения​
​ на листе определенным​ перед удалением повторяющихся​
​Выделите одну или несколько​Выделить все​
​Выполните одно из указанных​ОК​
​ОК​Элемент правила выделения ячеек​
​ столбцов.​Выделите диапазон ячеек или​ вам полезна. Просим​
​ Next​SLAVICK​
​ цветом:​If СчетчикЦветов >​
​Раскрашивая соответствующие ячейки на​ только если установлен​
​ цветом. Это особенно​
​ записей рекомендуется скопировать​ ячеек в диапазоне,​
​и выделите только​
​ ниже действий.​.​. Вы можете выбрать​и выберите​Чтобы быстро выделить все​
​ убедитесь, что активная​ вас уделить пару​Set changeCell =​
​: Макросом отсюда, или​Так гораздо нагляднее, правда?​ rngЦвета.Count Then СчетчикЦветов​ вспомогательном листе, мы​
​ Флажок Выделить неповторяющиеся​ полезно, когда в​
​ исходный диапазон ячеек​
​ таблице или отчете​ нужные столбцы.​Задача​
​Фильтрация уникальных значений и​
​ более одного формата.​
​Повторяющиеся значения​ столбцы, нажмите кнопку​
​ ячейка находится в​
​ секунд и сообщить,​ Target​
​ отсюда​ Конечно, при большом​ = 1​
​ тем самым обозначаем,​
​ (ячейка​ данных содержится несколько​
​ или таблицу на​ сводной таблицы.​
​Для наглядного отображения уникальных​Необходимые действия​
​ удаление повторяющихся —​ Форматы, которые можно​
​.​
​Выделить все​ таблице.​
​ помогла ли она​Application.ScreenUpdating = False​Выделите ячейки и​
​ количестве повторяющихся ячеек​
​End If​ какими цветами будут​
​А1​
​ наборов повторяющихся значений.​
​ другой лист или​На вкладке​
​ или повторяющихся значений​Фильтрация диапазона ячеек или​ это две тесно​
​ выбрать, отображаются на​
​Введите значения, которые вы​
​.​
​Нажмите кнопку​
​ вам, с помощью​
​Run "DuplicatesColoring"​
​ нажмите кнопку.​
​ оттенки различить будет​


​’Если имеется более​ отмечены повторяющиеся значения​):​

​Выделите одну или несколько​ в другую книгу.​

​Главная​ к ним можно​ таблицы на месте​ связанные друг с​ панели​ хотите использовать и​Чтобы быстро удалить все​данные > Дополнительно​ кнопок внизу страницы.​

exceltable.com

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

​Run «ВыделитьДубликатыРазнымиЦветами»​В примере оба​ трудно, но при​ чем одна ячейка​ в столбце с​выделите диапазон содержащий список​ ячеек в диапазоне,​Примечание:​в группе​ применить условное форматирование.​​Выделите диапазон ячеек и​ другом задачи, поскольку​предварительного просмотра​ нажмите кнопку Формат.​ столбцы, нажмите кнопку​(​ Для удобства также​​Application.ScreenUpdating = True​

​ варианта.​ относительно небольшом количестве​If rngЗаполненДанные.Count >​ данными. Макрос берет​ значений, например,​ таблице или отчете​ Нельзя удалить повторяющиеся значения,​Стили​ Например, выделение повторяющихся​ щелкните​ в результате их​.​Расширенное форматирование​Снять выделение​в​ приводим ссылку на​End Sub​

​В первом больше​ дубликатов этот способ​ 1 Then​ поочередно цвета из​А3:А16​ сводной таблицы.​ если выделенные фрагмент​щелкните​

​ данных определенным цветом​Фильтровать список на месте​​ выполнения отображается список​​В некоторых случаях повторяющиеся​​Выполните следующие действия.​​.​​группа​​ оригинал (на английском​В модуль листа​​ цветов- НО максимум​​ сработает отлично.​’ это для​

​ указанных ячеек, и​;​На вкладке​ содержит структурированные данные​Условное форматирование​ помогает найти и​.​ уникальных значений. Однако​ данные могут быть​Выделите одну или несколько​Если диапазон ячеек или​Сортировка и фильтр​ языке) .​.​ 55.​Чтобы использовать этот макрос​ следующих ячеек​ устанавливает их как​вызовите Условное форматирование (Главная/​Главная​ или промежуточные итоги.​и выберите пункт​ (при необходимости) удалить​Копирование результатов фильтрации в​ между этими двумя​ полезны, но иногда​ ячеек в диапазоне,​ таблица содержит много​).​В Excel существует несколько​Вот держите.​Во втором цветов​ нажмите сочетание клавиш​For Счетчик =​ цвет фона для​ Стили/ Условное форматирование/​в разделе​

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

planetaexcel.ru

ВЫДЕЛЯТЬ ПОВТОРЯЮЩИЕСЯ ЗНАЧЕНИЯ (Формулы/Formulas)

​Alt+F11​​ 2 To .Count​ каждой из ячеек,​ Создать правило/ Использовать​
​Формат​ значений нужно удалить​.​
​Выделите одну или несколько​Выделите диапазон ячеек, щелкните​ различие. При фильтрации​ данных. Используйте условное​
​ сводной таблицы.​
​ несколько столбцов только​Расширенный фильтр​

​ значений — или​​А что это​ но можно добавлять​
​или кнопку​If Application.WorksheetFunction.CountIf(rngК_Покраске, _​
​ имеющих повторяющиеся значения.​ формулу для определения​
​щелкните стрелку рядом​ структуру и промежуточные​Убедитесь, что в списке​
​ ячеек в диапазоне,​Скопировать результат в другое​ уникальных значений повторяющиеся​ форматирование для поиска​На вкладке​

​ может проще нажмите​​выполните одно из​ удаление повторяющихся значений:​ у вас за​ свои цвета и​
​Visual Basic​.Cells(Счетчик).Value) > 1​ Если «не хватит»​ форматируемых ячеек);​ с кнопкой​ итоги.​Показать правила форматирования для​ таблице или отчете​ место​

​ значения временно скрываются,​​ и выделения повторяющихся​Главная​ кнопку​ указанных ниже действий.​
​Чтобы фильтр уникальных значений,​ диапазон такой: Range(«am532:b0781»)​ задавать их.​на вкладке​ Then​ цветов (повторяющихся значений​введите формулу =И(СЧЁТЕСЛИ($A$3:$A$16;A3)=1;$A$1)​Условное форматирование​Выделите диапазон ячеек или​

​выбран соответствующий лист​​ сводной таблицы.​, а затем в​
​ тогда как в​
​ данных. Это позволит​
​в группе​
​Снять выделение всех​​Чтобы отфильтровать диапазон ячеек​ нажмите кнопку​ ?​БИТ​Разработчик (Developer)​
​If Application.WorksheetFunction.CountIf(Range(«rngDataStart»).Resize(Счетчик -​ больше, чем определенных​
​Обратите внимание, что в​и выберите пункт​
​ убедитесь в том,​ или таблица.​
​На вкладке​ поле​
​ ходе удаления повторяющихся​
​ вам просматривать повторения​
​Стили​
​и выберите в разделе​
​ или таблицы в​

​данных >​​(два раза верно​

​: отлично спасибки единственное​​, вставьте новый пустой​ 1), .Cells(Счетчик).Value) >​ цветов), они (цвета)​ формуле использована относительная​Создать правило​ что активная ячейка​Выберите правило и нажмите​

​Главная​​Копировать в​ значений они удаляются​​ и удалять их​щелкните стрелку для​
​столбцы​ программе:​
​Сортировка и фильтр >​ :Range("am532:bo781"), а один​
​ не подскажите через​
​ модуль через меню​
​ 0 Then​
​ будут просто взяты​
​ адресация, поэтому активной​
​.​​ находится в таблице.​
​ кнопку​
​в группе​
​введите ссылку на​ без возможности восстановления.​ по мере необходимости.​Условного форматирования​
​выберите столбцы.​Выберите​ Дополнительно​

excelworld.ru

​ раз Range(«am532:b0781»))​

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

1.     Удаление повторяющихся значений в Excel (2007+)

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

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

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

2.     Использование расширенного фильтра для удаления дубликатов

Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

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

На месте, указанном для размещения результатов работы расширенного фильтра, будет создана еще одна таблица, но уже с отфильтрованными, по уникальным значениям, данными.

3.     Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

4.     Использование сводных таблиц для определения повторяющихся значений

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

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

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

Поиск
одинаковых значений в Excel

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

На
рисунке – списки писателей. Алгоритм
действий следующий:

• Выбрать
  ячейку I3
  с записью «С. А. Есенин».
• Поставить
  задачу – выделить цветом ячейки с
  такими же записями.
• Выделить
  область поисков.
• Нажать
  вкладку «Главная».
• Далее
  группа «Стили».
• Затем
  «Условное форматирование»;
• Нажать
  команду «Равно».

• Появится
  диалоговое окно:

• В
  левом поле указать ячейку с I2,
  в которой записано «С. А. Есенин».
• В
  правом поле можно выбрать цвет шрифта.
• Нажать
  «ОК».

В
таблицах отмечены цветом ячейки, значение
которых равно заданному.

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

Ищем в таблицах Excel
все повторяющиеся значения

Отметим
все неуникальные записи в выделенной
области. Для этого нужно:

• Зайти
  в группу «Стили».
• Далее
  «Условное форматирование».
• Теперь
  в выпадающем меню выбрать «Правила
  выделения ячеек».
• Затем
  «Повторяющиеся значения».

• Появится
  диалоговое окно:

• Нажать
  «ОК».

Программа
ищет повторения во всех столбцах.

Если
в таблице много неуникальных записей,
то информативность такого поиска
сомнительна.

Удаление одинаковых значений
из таблицы Excel

Способ
удаления неуникальных записей:

1. Зайти
   во вкладку «Данные».
2. Выделить
   столбец, в котором следует искать
   дублирующиеся строки.
3. Опция
   «Удалить дубликаты».

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

Список
с уникальными значениями:

Расширенный фильтр: оставляем
только уникальные записи

Расширенный
фильтр – это инструмент для получения
упорядоченного списка с уникальными
записями.

• Выбрать
  вкладку «Данные».
• Перейти
  в раздел «Сортировка и фильтр».
• Нажать
  команду «Дополнительно»:

• В
  появившемся диалоговом окне ставим
  флажок «Только уникальные записи».
• Нажать
  «OK»
  – уникальный список готов.

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

Составим
список уникальных строк, не теряя данные
из других столбцов и не меняя исходную
таблицу. Для этого используем инструмент
Сводная таблица:

Вкладка
«Вставка».

Пункт
«Сводная таблица».

В
диалоговом окне выбрать размещение
сводной таблицы на новом листе.

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

Получаем
упорядоченный список уникальных строк.

Мы все знаем, что большие наборы данных лучше всего сохранять в электронных таблицах, т.к тогда мы обеспечены замечательным инструментом обработки информации. Рассмотрим, как можно исключить повторяющуюся в строках информацию. В файлах MS Excel дубликаты встречаются тогда, когда мы создаем составную таблицу из других таблиц, или когда разные пользователи работают с одним файлом, наполняя таблицу однотипной информацией. MS Excel предлагает несколько вариантов исключения или выявления повторяющейся информации, а именно: поиск, выделение и, при необходимости, удаление повторяющихся значений. Рассмотрим подробно каждое из действий на примере MS Excel 2007.

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

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

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

Далее подтверждаем выбор, нажимая на командную кнопку «ОК». Строки, содержащие дубликаты, будут удалены, и на экране появится сообщение о количестве удаленных записей и количестве уникальных записей.

ИТОГ: Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице.

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

2. Расширенный (дополнительный) фильтр для удаления дубликатов

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

В появившемся диалоговом окне Расширенный фильтр необходимо установить переключатель в строке Скопировать результат в другое место и указать необходимый диапазон (в нашем случае столбец) в поле Исходный диапазон, в поле Поместить результат укажите диапазон, куда будет помещен результат фильтрации, и установите маркер Только уникальные значения. Подтверждаем установленные команды командной кнопкой  ОК.

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

3. Условное форматирование в Excel

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

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

ИТОГ: Для больших массивов информации данный метод не дает четкой картины. Все фамилии авторов книг, у которых есть повторения, выделены одинаковым цветом.

4. Сводные таблицы для определения повторяющихся значений

В таблице со списком авторов и наименованием произведений данных авторов и добавьте столбец Порядковый номер перед фамилиями авторов и столбец Счетчик после наименования произведений. Заполните столбец Счетчик единицами (1), а столбец Порядковый номер – порядковыми номерами авторов и их произведениями. Выделите всю таблицу и перейдите на вкладку Вставка в группу Таблицы. Используя левую клавишу мыши, щелкните по кнопке Сводная таблица. В открывшемся окне установите флажки так, как указано на рисунке, т.е. сводную таблицу мы размещаем на новый лист.

Не забудьте нажать на командную кнопку ОК и продолжить формирование сводной таблицы на новом листе. В окне Список полей сводной таблицы установите флажки во всех полях, так как указано на рисунке. При этом выбранные наименования полей появятся в окнах Название строк и å Значения. Перетащите поле п/п в столбец Названия строк.

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

Материал подготовлен Л.А. Шутилиной, методистом ГМЦ ДОгМ

Дубли в Excel — одна из самых сложных проблем для людей, которые взаимодействуют с большими данными.

Что же можно делать с дублями?

Удаление дублей

Например, мы можем удалить дубли. Для этого необходимо зайти в поле «Данные», нажать «Удалить дубликаты».

Удалим дубликаты из столбца B:

Получаем:

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

Также мы можем выделить повторные значения цветом.

Переходим на вкладку «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения».

Получаем:

Фильтрация дублей

Также мы можем отфильтровать дубли. Для этого выделяем названия столбцов, заходим в «Данные», нажимаем «Фильтр».

Затем выделяем необходимую категорию:

Получаем:

Научитесь использовать весь функционал Excel для работы с данными, их визуализации и автоматизации рабочих задачна курсе «Excel Academy» от SF Education!

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

Оглавление:

• Выделение
  • Поиск и выделение повторяющихся значений ячеек в одном списке — условное форматирование
  • Поиск и выделение повторяющихся значений ячеек в нескольких списках — условное форматирование
  • Поиск и выделение повторяющихся значений ячеек — макрос Excel-VBA
  • Поиск и выделение повторяющегося текста внутри ячеек — макрос Excel VBA
• Замена
  • Замена дублирующихся значений ячеек с помощью макроса Excel-VBA
  • Подстановка в другие таблицы
  • Функция ВПР (VLOOKUP)
  • Комбинация функций ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH)+СЧЁТ()+ЕСЛИ()
• Подсчёт
  • Посчитать количество повторений в одном списке
  • Сравнение двух списков используя формулу подсчёта повторений
  • Подсчёт количества повторений значений в строках с помощью макросов Excel-VBA
  • Функция СЧЁТЕСЛИ (COUNTIF)
• Поиск
  • Поиск повторений значений в ячейках с помощью макроса Excel-VBA
  • Скрытие
  • Сортировка и фильтр
  • Скрытие строк с помощью макроса Excel-VBA
• Удаление
  • Данные -> удалить дубликаты
  • Умные таблицы. Форматировать как таблицу -> удалить дубликаты

Функции в каждом разделе описаны в порядке возрастания их сложности и трудоемкости использования.

Выделение

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

1. Выделить все значения в списке 

2. Вкладка «Главная» -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения

3. Выбрать необходимый формат (в данном случае выбран красный шрифт на светло-красном фоне)

Результат:

Если применить данное условное форматирование ко всему столбцу A, то все новые дубликаты, добавленные после строки 10 также будут отформатированы по заданному правилу.

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

Сначала необходимо выделить столбцы (диапазоны ячеек) с дубликатами. Далее необходимо проделать действия, описанные в предидушем разделе начиная с шага 2.

Недостаток данного способа выделения дубликатов — визуально не определить, продублированы ли значения внутри каждого из списков, или между списками. В данном примере «малина» дублируется внутри списка 1, а «банан» и «груша» выделены потому что они продублированы между списками.

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

1. Выделяем первый столбец:

2. Вкладка «Главная» -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения

3. Пользовательский формат

4. Выбираем, например, одинарное подчеркивание, жирный шрифт и фиолетовый цвет.

5. Повторяем операцию с шага 2 для столбца B и получаем:

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

Скачать пример в Excel

Sub search_highlight_duplicates()

Dim Arr(16, 1) As String 'сравниваем значения как текст
'массив двухмерный
'16 на 2
'элементы 1-16,0 содержат значения ячеек
'элементы 1-16,1 - является ли соответсвующее значение дубликатом


For i = 1 To 16
Arr(i, 0) = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value 'записываем в массив
Next i


For k = 1 To UBound(Arr, 1)
CurrentItem = Arr(k, 0) 'достаём по одному элементы из массива 1-16,0
    For i = 1 To UBound(Arr, 1)
    If CurrentItem = Arr(i, 0) And i <> k Then Arr(i, 1) = "COPY"
    'сравниваем с другими элементами массива (за исключением себя самого)
    'для копий записываем в 1-16,0 "COPY"
    Next i
Next k


For i = 1 To UBound(Arr, 1)
    ThisWorkbook.Sheets("Sheet1").Cells(i, 2).Value = Arr(i, 1)
    'запишем результат обратно в таблицу в колонку 2
    'либо здесь можно прописать особенное форматирование для каждого элемента исходного массива
Next i

End Sub

То же самое, но через форматирование ячеек:

Скачать пример в Excel

Sub search_highlight_duplicates()

Dim Arr(16, 1) As String

For i = 1 To 16
Arr(i, 0) = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
Next i

For k = 1 To UBound(Arr, 1)
CurrentItem = Arr(k, 0)
    For i = 1 To UBound(Arr, 1)
    If CurrentItem = Arr(i, 0) And i <> k Then Arr(i, 1) = "COPY"
    Next i
Next k


For i = 1 To UBound(Arr, 1)
If Arr(i, 1) = "COPY" Then
    With ThisWorkbook.Sheets("Sheet1").Cells(i, 1)
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
End If
    
Next i
End Sub

Поиск и выделение повторяющегося текста внутри ячеек — макрос Excel VBA

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

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

Замена

Замена дублирующихся значений ячеек с помощью макроса Excel-VBA

Подстановка в другие таблицы

Функция ВПР (VLOOKUP)

Об использовании функции ВПР пошагово.

Комбинация функций ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH)+СЧЁТ()+ЕСЛИ()

Если таблица, из которой мы хотим доставать значения для подстановки содержит дубликаты, то использование функции ВПР может не дать нужного результата, так как ВПР использует первое попавшееся совпадение и результат будет зависит от сортировки.

Подсчёт

Посчитать количество повторений в одном списке

Сравнение двух списков используя формулу подсчёта повторений

Подсчёт количества повторений значений в строках с помощью макросов Excel-VBA

Функция СЧЁТЕСЛИ (COUNTIF)

Поиск

Поиск повторений значений в ячейках с помощью макроса Excel-VBA

Скрытие

Сортировка и фильтр

Скрытие строк с помощью макроса Excel-VBA

Удаление

Данные -> удалить дубликаты

Умные таблицы. Форматировать как таблицу -> удалить дубликаты

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

— Простите, вы не видели тут моего близнеца?
— Вы уже спрашивали.

Допустим, что у нас имеется длинный список чего-либо и мы предполагаем, что некоторые элементы этого списка повторяются более 1 раза. Хотелось бы видеть эти повторы явно, т.е. подсветить дубликаты цветом. Сделать это в Excel можно несколькими разными способами.

Способ 1. Повторяющиеся ячейки

Выделяем все ячейки с данными и на вкладке Главная (Home) жмем кнопку Условное форматирование (Conditional Formatting), затем выбираем Правила выделения ячеек — Повторяющиеся значения (Highlight Cell Rules — Duplicate Values):

В появившемся затем окне можно задать желаемое форматирование (заливку, цвет шрифта и т.д.)

Способ 2. Выделение всей строки

Если хочется выделить цветом не одиночные ячейки, а сразу строки целиком, то придется создавать правило условного форматирования с формулой. Для этого выделяем все данные в таблице и выбираем Главная — Условное форматирование — Создать правило — Использовать формулу для выделения форматируемых ячеек (Home — Conditional formatting — Create rule — Use a formula to determine which cells to format), а затем вводим формулу:

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

=COUNTIF($A$2:$A$20;$A2)>1

где

• $A$2:$A$20 — столбец в данных, в котором мы проверяем уникальность
• $A2 — ссылка на первую ячейку столбца

Способ 3. Нет ключевого столбца

Усложним задачу. Допустим, нам нужно искать и подсвечивать повторы не по одному столбцу, а по нескольким. Например, имеется вот такая таблица с ФИО в трех колонках:

Задача все та же — подсветить совпадающие ФИО, имея ввиду совпадение сразу по всем трем столбцам — имени, фамилии и отчества одновременно.

Самым простым решением будет, конечно, добавить дополнительный служебный столбец (его потом можно скрыть) с текстовой функцией СЦЕПИТЬ (CONCATENATE), чтобы собрать ФИО в одну ячейку:

Имея такой столбец мы, фактически, сводим задачу к предыдущему способу.

Если же хочется всё решить без дополнительного столбца, то формула для условного форматирования будет посложнее:

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

• Сравнение двух диапазонов данных, поиск различий и совпадений
• Извлечение уникальных элементов из диапазона