Поиск дублей в столбце

Поиск дубликатов в Excel осложняется множеством факторов. Это могут быть лишние пробелы, знаки препинания, перестановки слов. К тому же, часто хочется ограничиться только поиском дублей без их удаления. Если же вы спокойны и готовы избавиться от дублей в 1 шаг, не проверяя их, вам в раздел «удалить дубликаты«.

Иногда дубли хочется найти внутри колонки, другой раз интересует поиск значений, повторяющихся в другой колонке.

Ситуаций множество, но ключевых моментов всего три:
1. Какие ячейки мы готовы считать дубликатами — все кроме первого или включая его?
2. Считаем ли дублями строки, отличающиеся только пробелами до, после слов или лишними пробелами между словами?
3. Где мы будем искать дубли — внутри текущего диапазона, или производим сравнение с другим диапазоном?

Итак, обо всем по порядку

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

​ выделения повторяющихся значений,​: . )Ок, файл​ выделим цветом все​В результате выделились все​ lookat:=xlWhole).Interior.ColorIndex​ событие onChange на​ трех столбцов в​Excel.​ мышкой на зеленый​Урок подготовлен для Вас​(Duplicate) пункт​ последние значения​Удалить дубликаты​ с элементом​ полезно, когда в​ без возможности восстановления,​ если в ячейке​ которые повторяются 3​ant6729​ даты этих дней​ строки, которые повторяются​Else​ листе «Данные».​ одной ячейке. В​Нам нужно не​ квадратик в правом​ командой сайта office-guru.ru​Уникальные​выберите пункт​.​Фильтр​ данных содержится несколько​ перед удалением повторяющихся​ A1 содержится формула​ или 4 раза.​: Допроясню тогда​ недели (вторник, среда).​ в таблице хотя-бы​.Cells(Счетчик).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex​Макрос выглядит следующим образом:​ ячейке D15 пишем​ только выделить повторы,​

​ нижнем углу ячейки​​Источник: http://www.excel-easy.com/examples/find-duplicates.html​(Unique), то Excel​Форматировать только уникальные или​Появится либо сообщение о​и выберите пункт​ наборов повторяющихся значений.​​ записей рекомендуется скопировать​​=2-1​Выделим с помощью Условного​​Вместо 34 может​​ Для этого будем​ 1 раз.​СчетчикЦветов = СчетчикЦветов​Option Explicit​ формулу, используя функцию​ но и вести​ (на картинке обведен​Перевел: Антон Андронов​ выделит только уникальные​ повторяющиеся значения​​ том, сколько повторяющихся​​Расширенный фильтр​Выделите одну или несколько​​ исходный диапазон ячеек​​, а в ячейке​ форматирования только дубликаты,​

​ быть другое значение,​ использовать условное форматирование.​

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

​​ + 1​Private Sub Worksheet_Change(ByVal​ «СЦЕПИТЬ» в Excel.​

​ их подсчет, написать​​ красным цветом). Слово​​Автор: Антон Андронов​​ имена.​​.​​ значений было удалено​​.​

Кнопка

​ ячеек в диапазоне,​ или таблицу на​

​ A2 — формула​

​ которые встречаются 3​

​ любое.​Выделите диапазон данных в​

​Форматирование для строки будет​If СчетчикЦветов >​​ Target As Range)​​ =СЦЕПИТЬ(A15;» «;B15;» «;C15)​

​ в ячейке их​ скопируется вниз по​

​Рассмотрим,​​Как видите, Excel выделяет​В меню​​ и сколько уникальных​Выполните одно из следующих​​ таблице или отчете​​ другой лист или​=3-2​

​ или 4 раза​​Я тоже пробовал​ таблице A2:B11 и​ применено только в​ rngЦвета.Count Then СчетчикЦветов​Dim rngЦвета As​Про функцию «СЦЕПИТЬ»​ количество.​

​ столбцу до последней​​как найти повторяющиеся значения​​ дубликаты (Juliet, Delta),​​значения в выбранном диапазоне​​ осталось, либо сообщение​

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

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

​ действий:​ сводной таблицы.​ в другую книгу.​и к ячейкам​ (см. Файл примера).​ через Ваш вариант,​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​ том случаи если​ = 1​ Range​ читайте в статье​В ячейке G5​ заполненной ячейки таблицы.​ в​ значения, встречающиеся трижды​выберите​ о том, что​Задача​

​На вкладке​​Примечание:​ применено одинаковое форматирование,​Выделите диапазон содержащий список​ чтобы подобраться к​ форматирование»-«Создать правило».​ формула возвращает значения​End If​Dim rngК_Покраске As​

​ «Функция «СЦЕПИТЬ» в​ пишем такую формулу.​Теперь в столбце​Excel​

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

Кнопка

​ значений, например,​ решению. Но не​В появившемся окне «Создание​ ИСТИНА. Принцип действия​​End If​​ Range​

​ Excel».​​ =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Копируем по​ A отфильтруем данные​,​ есть) и т.д.​или​ значения не было​​Фильтрация диапазона ячеек или​​в группе​ если выделенные фрагмент​

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

​ повторяющимися. Одинаковые значения,​A3:A16​ догнал, как передать​ правила форматирования» выберите​ формулы следующий:​Next Счетчик​Dim СчетчикЦветов As​Копируем формулу по​ столбцу. Получился счетчик​

​ – «Фильтр по​как выделить одинаковые значения​ Следуйте инструкции ниже,​повторяющиеся​

​ удалено.​​ таблицы на месте​​Стили​​ содержит структурированные данные​​ к которым применены​​;​​ вместо 34 любое​ опцию: «Использовать формулу​​Первая функция =СЦЕПИТЬ() складывает​​End If​​ Integer​​ столбцу. Теперь выделяем​

​ повторов.​​ цвету ячейки». Можно​​ словами, знакамипосчитать количество​ чтобы выделить только​​.​​Совет:​

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

​Выделите диапазон ячеек и​щелкните​ или промежуточные итоги.​ различные числовые форматы,​Вызовите Условное форматирование (Главная/ Стили/​ другое значение​ для определения форматированных​ в один ряд​

​End With​Dim Счетчик As​ дубли любым способом.​Изменим данные в столбце​

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

​ Условное форматирование/ Создать​​Нужно, чтобы вместо​​ ячеек».​​ все символы из​​Application.ScreenUpdating = True​ Integer​​ Как посчитать в​ А для проверки.​​ зависит от того,​​, узнаем​ встречающиеся трижды:​​Форматировать с помощью​

​ или таблице содержится​​Фильтровать список на месте​​и выберите пункт​​ значений нужно удалить​​ Например, если значение​​ правило/ Использовать формулу​​ 34 передавалось каждое​

​В поле ввода введите​​ только одной строки​​End If​Dim rngСтолбец As​ Excel рабочие дни,​

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

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

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

​ в ячейке A1​​ для определения форматируемых​​ значение из диапазона.​​ формулу:​​ таблицы. При определении​​End Sub​​ Range​​ прибавить к дате​​Ещё один способ подсчета​

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

​ нужно выбрать только​Копирование результатов фильтрации в​​.​​ итоги.​

​ имеет формат​ ячеек);​​ И сравнивалось с​​Нажмите на кнопку формат,​

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

​ условия форматирования все​Ниже, как обычно, файл​Dim rngЗаполненДанные As​ дни, т.д., смотрите​

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

​Введите формулу =СЧЁТЕСЛИ($A$3:$A$16;$A3)=4​ каждым. Наверное, так. И​

​ чтобы задать цвет​

​ ссылки указываем на​

​ с примером для​ Range​

​ в статье «Как​ статье «Как удалить​​ строки с дублями.​​В Excel можно​

​A1:C10​Вы можете отредактировать существующее​

​ снимите флажок​​Выделите диапазон ячеек, щелкните​Стиль​​ убедитесь в том,​, а в ячейке​​Выберите нужное форматирование;​​ если оно равно​ заливки для ячеек,​

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

​ правило, чтобы изменить​​Выделить все​​Скопировать результат в другое​​выберите пункт​​ что активная ячейка​

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

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

​ A2 — формат​Нажмите ОК​ другому в ячейке,​ например – зеленый.​Абсолютные и относительные адреса​Скачать выделение повторяющихся значений​ с цветами​ в Excel».​ Excel».​ отфильтрованного столбца B​ и удалять дублирующие​На вкладке​ условное форматирование, применяемое​и выделите только​ место​Классический​ находится в таблице.​1​

​Затем создадим еще одно​​ в том числе​ И нажмите на​ ссылок в аргументах​ ячеек разными цветами​Set rngЦвета =​Пример настраиваемого макроса для​Как посчитать данные​ пишем слово «Да».​

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

​, а затем в​​, а затем в​​На вкладке​​, эти значения не​​ правило:​​ и указанному через​​ всех открытых окнах​

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

​ работать с ними​(Home) выберите команду​ повторяющимся данным.​Для наглядного отображения уникальных​ поле​ списке​Данные​ являются повторяющимися.​Вызовите Условное форматирование (Главная/ Стили/​

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

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

​Выделите одну или несколько​ или повторяющихся значений​Копировать в​Форматировать только первые или​в разделе​Более новые версии​ Условное форматирование/ Создать​ эта ячейка и​Все транзакции, проводимые во​

​ все строки таблицы.​ простых случаях оно​ данными для заливки​ ячеек Excel.​

​ удалить их, смотрите​​ в таблице. Получилось​​ перед удалением, обозначить​​>​​ ячеек в диапазоне,​ к ним можно​​введите ссылку на​​ последние значения​​Работа с данными​​ Office 2011 ​ правило/ Использовать формулу​​ та, закрашивались.​​ вторник или в​

​Вторая функция =СЦЕПИТЬ() по​ выполняет свою функцию.​​ цветом​​Нам нужно чтобы макрос​

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

​ в статье «Как​ так.​ дубли словами, числами,​Создать правило​ таблице или отчете​ применить условное форматирование.​ ячейку.​выберите пункт​

​нажмите кнопку​Выделите диапазон ячеек или​ для определения форматируемых​buchlotnik​

​ среду выделены цветом.​​ очереди сложить значение​​ Кроме того, оно​​Set rngК_Покраске =​​ VBA, при помощи​ сложить и удалить​​Мы подсветили ячейки со​​ знаками, найти повторяющиеся​​(Conditional Formatting >​​ сводной таблицы.​

​ Например, выделение повторяющихся​​Примечание:​​Форматировать только уникальные или​​Удалить дубликаты​​ убедитесь в том,​ ячеек);​​: не-а, сначала прочитайте​ant6729​​ ячеек со всех​​ может послужить вдохновением​ wksДанные.Range(Range(«rngDataStart»), Cells(65535, Range(«rngDataStart»).Column).End(xlUp))​​ разных цветов отмечал​

​ ячейки с дублями​​ словом «Да» условным​​ строки, состоящие из​​ New Rule).​​На вкладке​​ данных определенным цветом​​ При копировании результатов фильтрации​

​ повторяющиеся значения​​.​​ что активная ячейка​Введите формулу =СЧЁТЕСЛИ($A$3:$A$16;$A3)=3​ Правила форума​

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

​: Добрый вечер!​ выделенных строк.​ и отправной точкой​’ столбец с​ в столбце повторяющиеся​

​ в Excel» здесь.​ форматированием. Вместо слов,​ нескольких ячеек, т.д.​Нажмите на​

​Главная​​ помогает найти и​​ в другое место​​.​​Установите один или несколько​ находится в таблице.​​Выберите другое форматирование;​​Цитата​​Если есть повторения​​Обе выше описанные функции​

​ для более комплексных​​ данными​​ значения. Более или​Четвертый способ.​

​ можно поставить числа.​ Как выделить цветом​​Использовать формулу для определения​​в разделе​

​ (при необходимости) удалить​ будут скопированы уникальные​​В списке​​ флажков, соответствующих столбцам​

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

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

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

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

Генерация дубликатов строк

27653 27.08.2017 Скачать пример

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

Читайте также:  Программа для расчета распила бревна в Excel скачать бесплатно

Допустим, что у нас есть вот такая таблица с именами людей, заказавших билет в кино и количеством билетов для каждого:

Как в excel сделать дублирующую строку?

Каждому билету нужно присвоить уникальный 6-значный номер, который формируется здесь простой функцией СЛУЧМЕЖДУ (RANDBETWEEN), генерирующей целое случайное число в заданном диапазоне 100000-999999.

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

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

Как в excel сделать дублирующую строку?

Руками такое делать — тоскливо, формулами — сложно. Так что остаются два наиболее удобных варианта — макросы и Power Query.

Способ 1. Создание дубликатов строк макросом

Откроем редактор макросов кнопкой Visual Basic на вкладке Разработчик (Developer) или сочетанием клавиш Alt+F11. Вставим новый модуль через меню Insert — Module и скопируем туда текст нашего макроса:

Sub Duplicate_Rows()
Dim cell As Range

Set cell = Range(«B2») 'первая ячейка в столбце с кол-вом билетов
Do While Not IsEmpty(cell)
If cell > 1 Then
cell.Offset(1, 0).Resize(cell.Value — 1, 1).EntireRow.Insert 'вставляем N пустых строк
cell.Resize(cell.Value, 1).EntireRow.FillDown 'заполняем вниз из первых ячеек
End If
Set cell = cell.Offset(cell.Value, 0)
Loop
End Sub

Принцип тут не самый сложный:

  1. проходим сверху-вниз по столбцу начиная с B2 до первой пустой ячейки
  2. если число в ячейке >1, то вставляем пустых строк под ячейкой на одну меньше, чем число билетов
  3. заполняем пустые ячейки (метод FillDown — аналог «протягивания за черный крестик» в правом нижнем углу ячейки)
  4. переходим к следующей ячейке и т.д.

Способ 2. Создание дубликатов строк в Power Query

Тем, кто хотя бы немного сталкивался с Power Query, рекламировать его мощь не нужно 🙂 Для тех, кто не знаком (если коротко), то Power Query — это бесплатная надстройка для Excel от Microsoft, умеющая делать с данными практически все, что только можно себе представить: загрузку из любых источников, очистку, трансформацию, анализ данных и т.д. Для Excel 2010-2013 ее можно скачать с сайта Microsoft (появится отдельная вкладка Power Query после установки), а в Excel 2016 она уже встроена по-умолчанию (группа Получить внешние данные на вкладке Данные).

Power Query может легко и красиво решить нашу проблему с генерацией дубликатов.

Для начала, выделим нашу таблицу и загрузим ее в Power Query кнопкой Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или Power Query:

После окна подтверждения увидим редактор запросов и нашу таблицу. Добавим пользовательский столбец на вкладке Добавить столбец (Add Column — Custom Column):

Как в excel сделать дублирующую строку?

В появившемся окне введем имя столбца и формулу, которая создает список чисел от 1 до количества билетов в каждой строке:

Как в excel сделать дублирующую строку?

После нажатия на ОК появится новый столбец со списками, элементы которых можно развернуть в строки, используя кнопку в шапке таблицы:

Как в excel сделать дублирующую строку?

В итоге, получаем практически то, что хотелось:

Как в excel сделать дублирующую строку?

Осталось удалить ненужный больше столбец Список (правой кнопкой мыши по заголовку — Удалить столбец) и выгрузить данные обратно на лист на вкладке Главная (Home) с помощью кнопки Закрыть и загрузить — Закрыть и загрузить в… (Close&Load — Close&Load to…) и указать подходящее место для результирующей таблицы:

Как в excel сделать дублирующую строку?

И останется совсем простая часть — добавить к таблице столбец с формулой СЛУЧМЕЖДУ (RANDBETWEEN) для генерации случайных номеров билетов:

Как в excel сделать дублирующую строку?

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

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

Также можно, для наглядности, склеивать через дефис номер билета и порядковый номер из столбца Список прямо в Power Query, используя команду Объединить столбцы на вкладке Преобразование (Transform).

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

Выделение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как в Excel найти и выделить цветом одинаковые значения ячеек?

На практике часто требуется найти в данных Excel повторяющиеся (одинаковые) значения, чтобы использовать их для дальнейшей обработки, например, для удаления дублей. Удобно, если такие значения будут как-то обозначены на листе, в частности, выделены цветом.

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

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

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

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

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

​Выберите один из вариантов​ читайте статьи Выпадающий​ новые и новые​ Стили/ Условное форматирование/​ в том числе​ant6729​Пример дневного журнала заказов​.Cells(Счетчик).Value) > 1​ ячейку, макрос обрабатывает​ в которых нужно​Показать правила форматирования для​, а затем —​ без возможности восстановления,​Условное форматирование​Стили​и нажмите кнопку​ убедиться в том,​ форматирования в списке​ список в Excel​ данные, и вам​ Правила выделения ячеек/​ и указанному через​: Добрый вечер!​ на товары:​ Then​ событие onChange на​ удалить повторения.​выбран соответствующий лист​ пункт​ перед удалением повторяющихся​и выберите пункт​щелкните​ОК​ что будет получен​Values with​ и Как создать​ снова и снова​ Повторяющиеся значения…);​ запятую, то и​Если есть повторения​Чтобы проверить содержит ли​If Application.WorksheetFunction.CountIf(Range(«rngDataStart»).Resize(Счетчик -​

​ листе «Данные».​​Например, на данном листе​ или таблица.​Повторяющиеся значения​ записей рекомендуется скопировать​Управление правилами​Условное форматирование​​.​​ ожидаемый результат.​(Значения с) диалогового​​ связанные выпадающие списки​​ приходится возвращаться к​нажмите ОК.​ эта ячейка и​ отдельного значения (​ журнал заказов возможные​ 1), .Cells(Счетчик).Value) >​Макрос выглядит следующим образом:​ в столбце «Январь»​Выберите правило и нажмите​.​​ исходный диапазон ячеек​​.​, наведите указатель на​​При удалении повторяющихся значений​​Примечание:​ окна​

​ в Excel.​ проверке на наличие​

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

​Усложним задачу. Теперь будем​ та, закрашивались.​ в данном случае​ дубликаты, будем анализировать​

​ 0 Then​​Option Explicit​​ содержатся сведения о​​ кнопку​​Выберите нужные параметры и​​ или таблицу на​​Убедитесь, что в списке​

Кнопка

​ пункт​ данные удаляются только​

​ Если формулы в ячейках​

​Duplicate Values​

​ выделять дубликаты только​buchlotnik​​ 34), чтобы ячейки,​​ по наименованиям клиентов​

​.Cells(Счетчик).Interior.ColorIndex = _​Private Sub Worksheet_Change(ByVal​

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

​(Повторяющиеся значения) и​​Если Вам нужно​Хотите ли Вы, чтобы​ если установлен Флажок​: не-а, сначала прочитайте​ где 34 закрашивалась.​ – столбец B:​rngЗаполненДанные.Find(what:=.Cells(Счетчик).Value, after:=.Cells(Счетчик), SearchDirection:=xlPrevious,​

​ Target As Range)​​ сохранить.​​.​​ОК​​ в другую книгу.​

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

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

​выбран соответствующий лист​и выберите​ ячеек или таблицы.​ одинаковые, такие значения​ нажмите​ автоматически сравнить введенные​ данные автоматически проверялись​ «Выделить дубликаты» (ячейка​ Правила форума​В ячейке А1​Выделите диапазон B2:B9 и​ lookat:=xlWhole).Interior.ColorIndex​Dim rngЦвета As​Поэтому флажок​Выберите нужные параметры и​.​Примечание:​ или таблица.​

​Повторяющиеся значения​​ Любые другие значения,​ считаются повторяющимися. Например,​ОК​ данные со столбцом​ на наличие дубликатов​B1​Цитата​ 34 (закрашивалась)​

​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​Else​ Range​Январь​

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

Кнопка

​, чтобы применить форматирование.​ из другой таблицы​ прямо в момент​)​​Я тоже пробовал​​В ячейке А2​

​ форматирование»-«Создать правило».​​.Cells(Счетчик).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex​Dim rngК_Покраске As​в поле​ОК​ для выделения уникальных​ если выделенные фрагмент​​ кнопку​​В диалоговом окне​ пределами этого диапазона​

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

​ A1 содержится формула​Кроме этого, в раскрывающемся​ (например, телефонные номера​ их ввода?​выделите диапазон содержащий список​ через Ваш вариант​ 67 (не закрашивалась)​Вберете «Использовать формулу для​СчетчикЦветов = СчетчикЦветов​

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

​ содержит структурированные данные​​Изменить правило​​Создать правило форматирования​​ ячеек или таблицы,​​=2-1​​ списке​​ черного списка), прочтите​Как только Вы​​ значений, например,​​ даа?! и где​​В ячейке А5​​ определения форматируемых ячеек».​

​ + 1​​Dim СчетчикЦветов As​​нужно снять.​В некоторых случаях повторяющиеся​​ на листе определенным​​ или промежуточные итоги.​

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

​.​выберите нужные параметры​ не изменяются и​, а в ячейке​Values with​ статью Как сравнить​ введете какие-либо повторяющиеся​B3:B16​

​ же ваши попытки​ 34. 35, 36​Чтобы найти повторяющиеся значения​If СчетчикЦветов >​

​ Integer​​Нажмите кнопку​​ данные могут быть​​ цветом. Это особенно​​ Перед удалением повторяющихся​​Выберите нужные параметры и​​ и нажмите кнопку​​ не перемещаются. Так​​ A2 — формула​

Читайте также:  Как найти среднее арифметическое число в Excel

​(Значения с) Вы​​ два столбца в​​ значения и нажмете​​;​​ в файле?​ (закрашивалась)​​ в столбце Excel,​ rngЦвета.Count Then СчетчикЦветов​​Dim Счетчик As​​ОК​ полезны, но иногда​​ полезно, когда в​

​ значений нужно удалить​​ нажмите кнопку​​ОК​​ как данные удаляются​​=3-2​​ можете выбрать пункт​​ Excel и выделить​

​ клавишу​​вызовите Условное форматирование (Главная/​​InExSu​Подскажите, пожалуйста, как​ в поле ввода​

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

​ = 1​ Integer​.​ они усложняют понимание​ данных содержится несколько​

​ структуру и промежуточные​ОК​.​ без возможности восстановления,​

​и к ячейкам​​Custom format​​ дубликаты.​​Enter​​ Стили/ Условное форматирование/​​: Привет!​​ это сделать. Не​​ введите формулу: =СЧЁТЕСЛИ($B$2:$B$9;​​End If​

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

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

​ применено одинаковое форматирование,​(Пользовательский формат) и​​Если Ваш список –​​, редактируемое поле сразу​

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

​ Создать правило/ Использовать​Sub yell()​ могу дойти. Решал​ B2)>1.​

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

​ настроить по своему​ это обычный диапазон,​

​ же выделится (например,​

​ формулу для определения​

​ActiveSheet.UsedRange.Interior.Pattern = xlNone​ когда-то с Target​

​Нажмите на кнопку «Формат»​Next Счетчик​​Dim rngЗаполненДанные As​​ разным цветом заливки​

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

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

​ форматируемых ячеек);​​ЛюбоеЗначение = «34»​ и потом с​ и выберите желаемую​End If​ Range​ ячеек Excel.​ данных. Это позволит​

​ таблице или отчете​​ что активная ячейка​​ что активная ячейка​​ на листе определенным​​ или таблицу на​

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

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

​ к которым применены​ размер шрифта, заливку​ увидите вкладку​ или цвет текста),​введите формулу =И(СЧЁТЕСЛИ($B$3:$B$16;$B3)>1;$B$1)​For i =​ методом Find. Но​ заливку ячеек, чтобы​End With​’ диапазон ячеек​Нам нужно чтобы макрос​ вам просматривать повторения​ сводной таблицы.​ находится в таблице.​ находится в таблице.​ цветом. Это особенно​ другой лист или​ различные числовые форматы,​

​ или границы. Например,​​Table Tools​ таким образом извещая​Обратите внимание, что в​ 1 To [a1000000].End(xlUp).Row​ здесь не могу​ выделить дубликаты цветом.​Application.ScreenUpdating = True​ с цветами​

​ VBA, при помощи​ и удалять их​На вкладке​На вкладке​

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

​ формуле использована относительная​If Cells(i, 1)​ понять, как «давать​ Например, зеленый. И​​End If​​Set rngЦвета =​

​ разных цветов отмечал​ по мере необходимости.​Главная​Данные​Данные​ данных содержится несколько​Примечание:​ Например, если значение​ ярко-желтая заливка.​

​ после нажатия на​​ данном столбце уже​ адресация, поэтому активной​ Like «*» &​ целеуказание» на каждую​ нажмите ОК на​End Sub​​ wksВспомогательный.Range(«rngColorStart»).Resize(wksВспомогательный.Range(«settIleColors»).Value, 1)​​ в столбце повторяющиеся​Выберите ячейки, которые нужно​

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

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

​ ячейкой при вводе​ ЛюбоеЗначение & «*»​ i в столбце​ всех открытых окнах.​

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

​ формулы должна быть​ Then Cells(i, 1).Interior.Color​​ A.​​Скачать пример поиска одинаковых​

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

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

​ содержит структурированные данные​1,00​ в списке, обе​ таблицу: выберите любую​

​ показано на скриншоте​​B3​​ = 65535​​InExSu​​ значений в столбце.​ скачивания:​​ цветом​​Макрос отмечает повторяющиеся значения​​Примечание:​​ с кнопкой​

​Удалить дубликаты​​ с элементом​​ таблице или отчете​​ или промежуточные итоги.​​, а в ячейке​ ячейки будут выделяться​​ ячейку с данными​ ниже). Продолжайте читать​​(т.е. диапазон нужно​​Next i​: Добрый вечер!​​Как видно на рисунке​

​Скачать выделение повторяющихся значений​​Set rngК_Покраске =​​ более или менее​​ В Excel не поддерживается​​Условное форматирование​​.​​Фильтр​

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

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

​ эту статью, чтобы​ выделять сверху вниз).​End Sub​План макроса есть,​ с условным форматированием​

​ ячеек разными цветами​ wksДанные.Range(Range(«rngDataStart»), Cells(65535, Range(«rngDataStart»).Column).End(xlUp))​ таким образом: Если​ выделение повторяющихся значений​

​и выберите пункт​​Установите один или несколько​​и выберите пункт​​На вкладке​​ значений нужно удалить​1​​ привлекать к себе​​Ctrl+T​​ узнать, как проделать​​ Активная ячейка в​

​iMrTidy​​ жду файл . ​​ нам удалось легко​Конечно, это не идеальное​

​’ столбец с​ бы речь шла​​ в области «Значения»​​Создать правило​

​ флажков, соответствующих столбцам​Расширенный фильтр​​Главная​​ структуру и промежуточные​

Нужно выделить повторяющиеся значения в столбце? Надо выбрать первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро и просто. За выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем дальше:

Excel выделение цветом

Основные возможности я описал в начале статьи, но на самом деле их масса. Подробнее о самых полезных

Условное форматирование, где найти?

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

Условное 1

При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите, возможностей здесь действительно много.

Excel выделение цветом

Теперь подробнее о самых полезных:

Excel выделение цветом ячеек по условиям. Простые условия

Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:

Excel выделение цветом 0

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

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

Чтобы выделить все повторяющиеся значения выберите соответствующее меню Повторяющиеся значения.

Excel выделение цветом

Далее снова появиться окошко с форматированием. Настройте как вам удобно. Можно выделить, например, только уникальные. Значения и курсивом (пользовательский формат)

Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой =СЦЕПИТЬ() , т.е. в отдельной ячейке у вас будет написано ИвановИванИваныч. По такому столбцу вы уже легко сможете выделить повторяющиеся значения. Важно понимать, что если порядок слов будет различаться, то Excel сочтет такие строки неповторяющимися (например, ИванИванычИванов).

Excel. значения повторяются

Выделение цветом первых/последних значений. Опять же условное форматирование

Excel выделение цветом 1

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

Excel выделение цветом 2

Построение термальной диаграммы и гистограммы

Классная функция для визуализации данных — термальная/температурная диаграмма. Суть в том, что в зависимости от величины значения в столбце или строке, ячейка подсвечивается определенным оттенком цвета, чем больше, тем краснее, например. Таблицы воспринимаются гораздо лучше на глаз, а принимать решение становится проще. Ведь один из лучших анализаторов зачастую — это наш глаз, соответственно, мозг, а не машина!

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

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

Excel выделение цветом 3

Выделение цветом ячеек, содержащих определенный текст

Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = ПОИСК() , но проще и быстрее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит

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

Excel выделение цветом. Фильтр по цвету

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

Подробнее о фильтрах в этой статье .

Excel выделение цветом 4

Проверка условий форматирования

Чтобы проверить какие условные форматирования у Вас заданы, пройдите Главная — Условное форматирование — Управление правилами. Здесь вы сможете отредактировать уже заданные условия, диапазон применения, а также выбрать приоритет заданного форматирования (кто выше, тот главнее, изменить можно кнопками — стрелками).

Excel выделение цветом 5

Неверный диапазон условного форматирования

Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных тормозов Excel . Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас появится множество условий с цветом. Я сам видел более 3 тысяч условий — тормозил файл безобразно. Также файл может тормозить, когда задан диапазон как на картинке выше, лучше, указывать A:A — для всего диапазона.

Подробнее о тормозах Excel и их причинах читайте здесь . Эта статья помогла не одной сотне людей 😉

Как найти одинаковые строки в Excel и выделить их цветом

​Вы можете использовать​ условного форматирования.​Поэтому флажок​ В Excel не поддерживается​ и нажмите кнопку​щелкните маленькую стрелку​ Столбец1 и Столбец2,​ повторяющихся данных, хранящихся​Выделите диапазон ячеек или​Условного форматирования​ формулы должна быть​Application.ScreenUpdating = False​ показать какая часть​ формулу:​Как только при сравнении​ ниже на рисунке.​В появившемся диалоговом окне​ любую формулу, которая​Выделите диапазон​Январь​ выделение повторяющихся значений​

Как объединить одинаковые строки одним цветом?

​Изменить правило​Условное форматирование​ но не Столбец3​ в первое значение​ убедитесь, что активная​

  1. ​в группе​B3​Run «DuplicatesColoring»​ кода отвечает за​Нажмите на кнопку формат,​ совпадают одинаковые значения​ И выберите инструмент:​ выделите опцию: «Использовать​ вам нравится. Например,​A1:C10​Создать правило1.
  2. ​в поле​ в области «Значения»​, чтобы открыть​и затем щелкните​ используется для поиска​СЦЕПИТЬ.
  3. ​ в списке, но​ ячейка находится в​Зеленая заливка.
  4. ​стиль​(т.е. диапазон нужно​Run «ВыделитьДубликатыРазнымиЦветами»​ определенный выделенный диапозон!​ чтобы задать цвет​ (находятся две и​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».​

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

​ отчета сводной таблицы.​

Как выбрать строки по условию?

​ всплывающее окно​Элемент правила выделения ячеек​ дубликатов «ключ» —​ других идентичных значений​ таблице.​на вкладке «​

​ выделять сверху вниз).​Application.ScreenUpdating = True​И какая часть​ заливки для ячеек,​ более одинаковых строк)​В появившемся окне «Создание​ форматируемых ячеек», а​ встречающиеся более 3-х​

Читайте также:  Пример формулы для расчета точки безубыточности BEP в Excel

​На вкладке​нужно снять.​На вкладке​Изменение правила форматирования​и выберите​

​ значение ОБА Столбец1​ удаляются.​Нажмите кнопку​Главная​

​ Активная ячейка в​End Sub​ кода отвечает за​ например – зеленый.​ это приводит к​ правила форматирования» выберите​ в поле ввода​ раз, используйте эту​Главная​Нажмите кнопку​Главная​

​.​Повторяющиеся значения​ & Столбец2. Если дубликат​Поскольку данные будут удалены​данные > Дополнительно​».​ выделенном диапазоне –​В модуль листа​ наступление события (если​ И нажмите на​ суммированию с помощью​ опцию: «Использовать формулу​

​ введите следующую формулу:$C3:$C20)+0);2)’​ формулу:​(Home) выберите команду​ОК​выберите​В разделе​.​ находится в этих​ окончательно, перед удалением​

​(​Фильтр уникальных значений и​ белая и ее​.​ значения в одной​ всех открытых окнах​ функции =СУММ() числа​ для определения форматированных​ >​=COUNTIF($A$1:$C$10,A1)>3​

Как найти и выделить дни недели в датах?

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

  1. ​Нажмите на кнопку «Формат»​=СЧЕТЕСЛИ($A$1:$C$10;A1)>3​>​Этот пример научит вас​Создать правило2.
  2. ​>​нажмите кнопку​ хотите использовать и​ строки будут удалены,​ скопировать исходный диапазон​Использовать формулу.
  3. ​группа​ являются две сходные​Зеленый фон.
  4. ​ поле Имя.​зы​ диапозоне поменяется то​Все транзакции, проводимые во​ втором аргументе функции​В поле ввода введите​ и на закладке​

​Урок подготовлен для Вас​Создать правило​ находить дубликаты в​

Excel выделить дубликаты разными цветами в таблице 100х100

Помощь в написании контрольных, курсовых и дипломных работ здесь.

Выделить дубликаты разными цветами
Здравствуйте! Создал 2 таблицы с рандомными числами от 0 до 1000 Sub Task() Sheets.Add.

Выделить разными цветами границы таблицы
Темный красный цвет рамки снизу и справа, светлый красный цвет рамки сверху и слева Сделал.

MonthCalendar1 Delphi выделить дни разными цветами
как в MonthCalendar1 Delphi выделить все субботы и воскресенья красным цветом, а все остальные дни.

В консольном выводе выделить разными цветами элементы матрицы
Доброй ночи всем. Я делаю задачу в Visual C++ 2008 Express. У меня вот какое дело. Выводится.

Сообщение от toiai

Ввел размер квадратной матрицы 100 и все-равно всё так же..

Необходимо, чтобы каждой цифре был присвоен свой цвет, т.е. все 273 — красные, все 953 — синие и т.п.

Добавлено через 9 минут

Сообщение от aequit

Проверял на цифре 905, она разных цветов..
Необходимо чтобы у каждой цифры был свой цвет

Добавлено через 15 минут

Сообщение от aequit
Сообщение от anvz0r

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

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

С популяризацией компьютеров за последние 10 лет — происходит и популяризация создания отчетов (документов) в программе Excel.

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

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

Примечание : все примеры ниже будут представлены в Office 2016/2019 (актуально также для Office 2013, 2010, 2007). Рекомендую всегда использовать относительно новые версии Office: в них и быстрее работать, и проще.

Поиск повторяющихся значений

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

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

Результат работы данной функции представлен на рисунке ниже.

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

Фильтр и сортировка ячеек по цвету в Excel

  • Рассмотрим основные способы фильтрации и сортировки данных по цвету (как по заливке ячейки, так и по заливке текста) в Excel.
  • Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?

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

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

В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию или убыванию (для чисел), однако давайте познакомимся с еще одним вариантом сортировки — по цвету, и рассмотрим 2 способа, позволяющие сортировать и применять фильтр к данным:

  • Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
  • Применение пользовательских функций.

Стандартный фильтр и сортировка по цвету в Excel

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

Как сделать цветные строки в excel?

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

Как сделать цветные строки в excel?

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

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

Как сделать цветные строки в excel?

На втором шаге повторно произведем сортирование данных, но на этот раз выберем в качестве критерия сортировки красную заливку:

Как сделать цветные строки в excel?

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Как сделать цветные строки в excel?

Сортировка и фильтр по цвету с помощью функций

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

Функция цвета заливки ячейки на VBA

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

Public Function ColorFill(MyCell As Range)
ColorFill = MyCell.Interior.ColorIndex
End Function

Public Function ColorFill(MyCell As Range) ColorFill = MyCell.Interior.ColorIndex

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

Вернемся в Excel и применим новую функцию ColorFill — либо непосредственно введем формулу в ячейку, либо вызовем ее с помощью мастера функций (выбрав из категории Определенные пользователем).
В дополнительном столбце прописываем код заливки ячейки:

Как сделать цветные строки в excel?

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

Как сделать цветные строки в excel?

Функция цвета текста ячейки на VBA

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

Public Function ColorFont(MyCell As Range)
ColorFont = MyCell.Font.ColorIndex
End Function

Public Function ColorFont(MyCell As Range) ColorFont = MyCell.Font.ColorIndex

Функция ColorFont в качестве значения возвращает числовой код цвета шрифта ячейки и принцип ее применения аналогичен примеру рассмотренному выше.

Замечания

К сожалению, при работе с пользовательскими функциями ColorFill и ColorFont есть несколько подводных камней:

  • Они не работают с ячейками, в которых заливка определяется условным форматированием;
  • При изменении раскраски ячейки в Excel формулы автоматически не пересчитываются, в связи с этим пересчет нужно сделать самостоятельно (Shift + F9 для пересчета формул только на активном листе, F9 — для всей книги).

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

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

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

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

Как сделать чтобы excel выделял повторы?

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

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

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

Как сделать чтобы excel выделял повторы?

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

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

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

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

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

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

Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)Dim rngЦвета As RangeDim rngК_Покраске As RangeDim СчетчикЦветов As IntegerDim Счетчик As IntegerDim rngСтолбец As RangeDim 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 WithIf Not Intersect(Target, rngСтолбец) Is Nothing Then ' если изменение в столбце с даннымиApplication.ScreenUpdating = False ' выключаю «мигание» экрана' Очищаем всю область данных (устанавливаем везьде цвет фона по умолчанию)rngЗаполненДанные.Resize(rngЗаполненДанные.Count + 1).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

.Cells(Счетчик).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex СчетчикЦветов = СчетчикЦветов + 1

If СчетчикЦветов > rngЦвета.Count Then СчетчикЦветов = 1

End If End If Next Счетчик End IfEnd WithApplication.ScreenUpdating = TrueEnd IfEnd Sub

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

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

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

Ссылка на основную публикацию