Как в excel выделить строки с одинаковым текстом - Word и Excel

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

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

Чтобы найти объединить и выделить одинаковые строки в Excel следует выполнить несколько шагов простых действий:

Выделите весь диапазон данных табличной части A2:F18. Начинайте выделять значения из ячейки A2, так чтобы после выделения она оставалась активной как показано ниже на рисунке. И выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулу для определения форматированных ячеек».

В поле ввода введите формулу:

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

В результате выделились все строки, которые повторяются в таблице хотя-бы 1 раз.

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

Форматирование для строки будет применено только в том случаи если формула возвращает значения ИСТИНА. Принцип действия формулы следующий:

Первая функция =СЦЕПИТЬ() складывает в один ряд все символы из только одной строки таблицы. При определении условия форматирования все ссылки указываем на первую строку таблицы.

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

Вторая функция =СЦЕПИТЬ() по очереди сложить значение ячеек со всех выделенных строк.

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

Как только при сравнении совпадают одинаковые значения (находятся две и более одинаковых строк) это приводит к суммированию с помощью функции =СУММ() числа 1 указанного во втором аргументе функции =ЕСЛИ(). Функция СУММ позволяет сложить одинаковые строки в Excel.

Если строка встречается в таблице только один раз, то функция =СУММ() вернет значение 1, а целая формула возвращает – ЛОЖЬ (ведь 1 не является больше чем 1).

Если строка встречается в таблице 2 и более раза формула будет возвращать значение ИСТИНА и для проверяемой строки присвоится новый формат, указанный пользователем в параметрах правила (заливка ячеек зеленым цветом).

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

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

Выделите диапазон данных в таблице A2:B11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулу для определения форматированных ячеек».

В поле ввода введите формулу:

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

Все транзакции, проводимые во вторник или в среду выделены цветом.

Источник

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

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

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

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

Как подсветить дубликаты, используя встроенный инструмент
Как выделить дубликаты без учета 1-го вхождения
Как показать 3-й, 4-й и все последующие дубликаты?
Выделяем повторяющиеся значения в диапазоне
Выделение строки целиком на основе дубликатов в определённой колонке
Выделение дублирующихся строк
Подсветка последовательных дубликатов ячеек
Как выделить дубликаты в Excel с помощью специального инструмента и без формул

Далее в этом руководстве вы найдете несколько способов выделения дубликатов записей в зависимости от конкретной задачи. Эти методы работают во всех версиях Excel 2016, Excel 2013, Excel 2010 и ниже.

Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)

Для начала, во всех версиях Excel есть заготовка для выделения дублирующих ячеек. Чтобы использовать это её в своих таблицах, выполните следующие действия:

Выберите данные, которые вы хотите проверить. Это может быть колонка, строка или диапазон ячеек.
На вкладке « Главная » выберите « Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения».

Откроется диалоговое окно с форматом «Светло-красная заливка» и «Темно-красный текст», выбранными по умолчанию. Чтобы применить формат по умолчанию, просто нажмите ОК.

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

И вот что мы получили в итоге:

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

При использовании этого инструмента имейте в виду следующие две вещи:

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

Как выделить повторы без 1-го вхождения

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

Выберите «Условное форматирование» > « Новое правило»> «Использовать формулу», чтобы определить ячейки для форматирования .
В поле « Форматировать значения», где эта формула возвращает ИСТИНА , введите:

=СЧЕТЕСЛИ($B$2:$B2;$B2)>1

Где B2 — самая верхняя ячейка выбранного диапазона.

Нажмите кнопку «Формат» выберите нужный цвет заливки и/или шрифта.
Наконец, нажмите кнопку ОК, чтобы сохранить и применить созданное.

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

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

Как показать третий, четвертый и все последующие дубликаты?

Чтобы просмотреть повторяющиеся значения, начинающиеся с N-го вхождения, действуйте как в предыдущем примере. С той лишь разницей, что вы заменяете > 1 в конце формулы на требуемое число. Например:

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

=СЧЕТЕСЛИ($B$2:$B2;$B2)>=3

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

=СЧЕТЕСЛИ($B$2:$B2;$B2)>=4

Чтобы выделить только определенные вхождения, используйте оператор равенства (=).

Например, чтобы изменить фон только 2-х экземпляров, вы должны использовать следующую формулу:

=СЧЕТЕСЛИ($B$2:$B2;$B2)=2

Как выделить дубликаты в диапазоне

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

Выделите дубликаты в таблице, включая 1-е вхождение.

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

Или создайте своё условное форматирование:

=СЧЕТЕСЛИ(диапазон, первая_ячейка)> 1

Например, чтобы отметить цветом совпадения в диапазоне A2: C8, сделайте это следующим образом:

=СЧЕТЕСЛИ($A$2:$C$8, A2)>1

Обратите внимание на использование абсолютных ссылок на ячейки для диапазона ($A$2:$C$8) и относительных ссылок для верхней ячейки (A2).

Выделите повторы в таблице без 1-го вхождения.

Решение для этого сценария намного сложнее. Не удивительно, что в Excel нет для него готового решения

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

Правило 1. Относится к первому столбцу.

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

В этом примере мы применяем для A2: A11 условие:

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

В результате дублирующие элементы без 1- ^го вхождения выделяются в крайнем левом столбике диапазона (в нашем случае есть только два таких элемента):

Правило 2. Относится ко всем последующим колонкам

Чтобы выделить дубликаты в оставшихся данных (B2: C11), используйте следующую формулу:

=СЧЁТЕСЛИ(A$2:$A$11;B2)+СЧЁТЕСЛИ(B$2:B2;B2)>1

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

В результате все дублированные элементы, за исключением их 1- ^го вхождения, будут найдены и выделены:

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

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

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

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

=СЧЁТЕСЛИ($B$2:$B2;$B2)>1

Выделяем строки с одинаковыми наименованиями товаров (столбец B)

Чтобы выделить повторяющиеся строки, включая все вхождения:

= СЧЁТЕСЛИ ($B$2:$B$17;$B2)>1

Здесь B2 — первая ячейка, а B17 — последняя, которую вы хотите проверить на наличие дублей.

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

Как выделить дублирующиеся строки.

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

Для этого используйте функцию СЧЁТЕСЛИМН, которая позволяет сравнивать по нескольким критериям. Например, чтобы выделить строки с одинаковыми значениями в B и C, то есть найти заказы одного и того же товара одним заказчиком, используйте одну из следующих формул:

Чтобы выделить совпадающие, кроме 1-го вхождения :

=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1

Обратите внимание, что форматирование мы применяем ко всей таблице.

Чтобы выделить все неуникальные:

=СЧЁТЕСЛИМН($B$2:$B$17; $B2; $C$2:$C$17; $C2)>1

Как вы понимаете, приведенный выше пример только для демонстрационных целей. При выделении дублирующихся строк в ваших реальных таблицах вы, естественно, не ограничены сравнением значений только в 2 столбцах. Функция СЧЁТЕСЛИМН может обрабатывать до 127 пар диапазон / критерий.

Подсветка последовательных дубликатов ячеек.

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

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

Чтобы выделить два идущих друг за другом заказа одного и того же менеджера:

=ИЛИ($G1=$G2;$G2=$G3)

Чтобы выделить второй заказ менеджера, если у него 2 заказа идут подряд:
=$G1=$G2

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

при выделении только второго повтора

=И($G2<>»»; $G1=$G2)

при выделении обоих дублей:

=И($G2<>»»; ИЛИ($G1=$G2;$G2=$G3))

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

Как выделить дубликаты в Excel с помощью инструмента Duplicate Remover.

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

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

Средство удаления дубликатов добавляет к вашей ленте Excel три новых функции:

Quick Dedupe (быстрое удаление дубликатов) – быстро в пару кликов найти и выделить дубликаты в таблице.
Duplicate Remover (удаление дубликатов) – пошаговый мастер с расширенными параметрами для выявления и выделения дубликатов или уникальных значений в таблице.
Compare Two Sheets (сравнить таблицы) – найти и выделить дубликаты, сравнивая два столбца или две отдельные таблицы.

После установки Ultimate Suite for Excel вы найдете эти инструменты на вкладке Ablebits Data.

Как выделить дубликаты в Excel за пару кликов

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

Вы не поверите, но желаемый результат можно получить всего за 2 клика мыши

Выделив любую ячейку в таблице, используйте кнопку Quick Dedupe на ленте Excel.
Откроется диалоговое окно, в котором все столбцы для поиска дублей будут выбраны автоматически.

В правом нижнем углу в выпадающем списке нужно выбрать Выделить цветом (Highlight with color). Теперь все, что вам нужно сделать, это нажать ОК Готово!

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

Как вы видите на изображении выше, инструмент выделил повторяющиеся строки без первых экземпляров.

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

Как выделить дубликаты в Excel с помощью расширенного пошагового мастера.

По сравнению с быстрым инструментом, который мы только что рассмотрели, мастеру Duplicate Remover требуется несколько дополнительных щелчков мыши. Но он компенсирует это рядом дополнительных опций. Посмотрим это в действии:

Выберите любую ячейку в таблице, в которой вы хотите выделить дубликаты, и нажмите кнопку «Удаление дубликатов» на ленте. Мастер запустится, и будет выделена вся таблица. Также на всякий случай надстройка предложит создать резервную копию вашей таблицы. Если он вам не нужен, снимите этот флажок.

Убедитесь, что таблица выбрана правильно, и нажмите Далее .

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

В этом примере давайте найдем Дубликаты + 1-е вхождение :

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

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

Наконец, выберите действие, которое нужно выполнить с дубликатами. У вас есть несколько вариантов – выбор , удаление , копирование, перемещение дубликатов или добавление столбца статуса для идентификации дубликатов .

Поскольку сегодня мы изучаем различные способы выделения дубликатов в Excel, наш выбор очевиден Итак, выберите «Заливка цветом» (Fill with color) и укажите один из стандартных цветов или нажмите « Другие цвета» и выберите любой собственный цвет RGB.

Нажимаем кнопку Finish и наслаждаемся результатом

Вот как вы можно выделить дубликаты в Excel с помощью надстройки Duplicate Remover. Если вам интересно попробовать этот инструмент на своих рабочих таблицах, вы можете загрузить полнофункциональную пробную версию Ultimate Suite, которая включает в себя более 60 инструментов для экономии времени при работе в Excel. Будем очень благодарны за ваши отзывы в комментариях!

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

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

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

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

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

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

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

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

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

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

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

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

Нажать
«ОК».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

Смотрите такжеили кнопку ОК на всех командой сайта office-guru.ru в поле останется только одна за исключением одной. качестве примера возьмем с заголовками», если во внимание, что Активная ячейка вВыберите номер, шрифт, границы, чтобы открыть сообщение.Удалить повторенияВыберите только временно. ТемПримечание:Visual Basic открытых окнах.Источник: http://www.howtogeek.com/198052/how-to-remove-duplicate-rows-in-excel/Replace with

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

если установлены все выделенном диапазоне – и заливка формат,  всплывающее окноU тменить отменить изменения,(в группе
фильтровать список на месте не менее удалениеМы стараемся какна вкладке В результате мы выделили Перевел: Антон Андронов(Заменить на). В
Этот метод удобно применять, удалении отобразится во данными: итогу нажмите кнопку галочки, то дубликатом белая и ее который нужно применять,Диспетчер правил условного форматирования

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

щелкните (или нажатьРабота с данными. повторяющихся значений означает, можно оперативнее обеспечиватьРазработчик (Developer) целые строки условнымАвтор: Антон Андронов данном примере мы когда необходимо найти всплывающем диалоговом окне.Microsoft Excel располагает встроенным «ОК». будут считаться строки, адрес отображается в

если значение в. клавиши Ctrl +).Чтобы скопировать в другое что вы окончательное вас актуальными справочными, вставьте новый пустой форматированием и получаемСписок с выделенным цветом воспользуемся цифрой повторяющиеся строки вВторым инструментом Excel, с инструментом, который позволяет»Умная таблица» создана, но которые полностью повторяются поле Имя. ячейке удовлетворяет условиюВыполните одно из действий, Z на клавиатуре).Выполните одно или несколько

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

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

выберите нужное форматирование;

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

 Таким образом, еслинажмите ОК.ОК

Чтобы добавить условное форматирование, из структуры данных,В разделе

Копировать в другое место котором все значения

переведена автоматически, поэтомуи скопируйте тудаТеперь работать с такой
чем белые ячейки нажмитеFind and Replace дубликаты, является
с поиска повторяющихся необходимо выделить любую вы хотите удалитьСняв Флажок «Выделить дубликаты». Вы можете выбрать нажмите кнопку структурированный или, в столбцы
. в по крайней ее текст может код этого макроса: читабельна таблицей намного

в столбцах сReplace All(Поиск и замена),

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

Расширенный фильтр строк. Для этого ячейку таблицы, чтобы дубликаты с одного выделение повторяющихся значений более одного формата.Создать правило котором содержится промежуточныевыберите один или несколькоВ поле мере одна строка содержать неточности иSub DuplicatesColoring() Dim удобнее. Можно комфортно

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

исчезнет.

Форматы, которые можнодля отображения во итоги. Чтобы удалить столбцов.
Копировать идентичны всех значений грамматические ошибки. Для Dupes() ‘объявляем массив проводить визуальный анализ Даже элементарное выделениеМожно заметить, что все
все продукты Microsoft применим к Excel
- в таблице, а пункт «Конструктор» и оставить лишь однуКогда работаешь с огромным
- выбрать, отображаются на всплывающем окне дубликаты, необходимо удалитьЧтобы быстро выделить все
- введите ссылку на в другую строку. нас важно, чтобы для хранения дубликатов
  
  всех показателей. цветом каждой второй значения Office. Для начала 2003. Чтобы применить затем выделите всю непосредственно перейти в галочку рядом с объемом данных в панели
  
  Создание правила форматирования структуры и промежуточные столбцы, нажмите кнопку ячейку. Сравнение повторяющихся значений эта статья была ReDim Dupes(1 ToСреди стандартных средств Microsoft строки существенно облегчаетduplicate необходимо открыть таблицу фильтр, необходимо выделить таблицу, нажав эту вкладку. его наименованием. программе «Эксель», топредварительного просмотра. итоги. Для получения
Выделить всеКроме того нажмите кнопку зависит от того, вам полезна. Просим Selection.Cells.Count, 1 To Excel есть много визуальный анализ данныхв ячейках таблицы, Excel, с которой всю таблицу, какCtrl+A
В ленте инструментов нужноНажать «ОК». ненароком очень просто.

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

Убедитесь, что выбран соответствующий дополнительных сведений отображается.Свернуть диалоговое окно что отображается в вас уделить пару 2) Selection.Interior.ColorIndex = разных способов выделить таблицы. Для реализации будут заменены на планируется работать. и прежде, воспользовавшись

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

. нажать кнопку «УдалитьКак только вы нажмете можно совершить ошибкуНастроим Условное форматирование для лист или таблица

 Структура списка данных

Чтобы быстро удалить все

временно скрыть всплывающее ячейке, не базового секунд и сообщить, -4142 ‘убираем заливку
дубликаты цветом. Самый данной задачи в1Открыв таблицу, выберите ячейку, сочетанием клавишПерейдите на вкладку дубликаты». на кнопку, начнется и вписать одни выделения только повторяющихся в списке
на листе «и» столбцы, нажмите кнопку окно, выберите ячейку

 значения, хранящегося в

помогла ли она

если была i простой и быстрый Excel применяется универсальный. Мы использовали значение
содержимое которой требуетсяCtrl+ADateПосле этого появится окно поиск дубликатов выделенной и те же значений в списке.Показать правила форматирования для удалить промежуточные итоги.Снять выделение на листе и ячейке. Например, если
вам, с помощью = 3 For
- — с помощью инструмент – условное1 найти и заменить,.(Данные), а затем удаления повторяющихся строк.
- области, а затем данные повторно. ТакНеобходимо выделить ячейки, содержащиеизменения условного форматирования,Примечание:. нажмите кнопку у вас есть кнопок внизу страницы. Each cell In условного форматирования. Для форматирование., так как оно и скопируйте ее.Затем перейдите на вкладку нажмите команду Оно будет аналогично и их удаление. возникают дубликаты. Это, значения, которые повторяются начинается. При необходимости Условное форматирование полей вЕсли диапазон ячеек илиРазвернуть
то же значение Для удобства также Selection If WorksheetFunction.CountIf(Selection, этого достаточно выделитьИногда можно столкнуться со небольшое и выделяется
Для этого выделитеDataRemove Duplicates тому, что было По итогу появится конечно же, повышает в определенном диапазоне. выберите другой диапазон
области «Значения» отчета таблица содержит много. даты в разных приводим ссылку на cell.Value) > 1
диапазон ячеек и ситуацией, когда нужно в тексте. Теперь нужную ячейку и(Данные), в группе(Удалить дубликаты), как представлено в первом окно, в котором объем всей суммарной Выделять повторяющиеся значения ячеек, нажав кнопку сводной таблицы по столбцов, чтобы выбратьУстановите флажок

support.office.com

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

ячейках, один в оригинал (на английском Then For k

Задача

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

Решение

будет продемонстрирован отчет. информации, а в будем с помощьюСвернуть
уникальным или повторяющимся несколько столбцов толькотолько уникальные записи формате «3/8/2006», а
языке) .

= LBound(Dupes) ToГлавная — Условное форматирование данных, но из-за определить строки, которыеCtrl+CSort & Filter

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

В Excel существует несколько UBound(Dupes) ‘если ячейка — Правила выделения сложной структуры нельзя имеют повторяющиеся значения..(Сортировка и фильтр)Remove Duplicates в соответствии с как в Excel формулы, которые должны Файл примера).относится к

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

кнопку ОК «8 мар «2006 способов фильтр уникальных

excel2.ru

Как в Excel удалить повторяющиеся строки: два способа

уже есть в ячеек — Повторяющиеся четко определить иЧтобы оставить один изСкопировав слово, которое необходимо нажмите команду(Удалить дубликаты). Можно первой инструкцией. удалить повторяющиеся строки, подводить итоги ивыделите диапазон содержащий списоквременно скрыть ее.Выполните следующие действия.Снять выделение всех. г. значения должны значений — или массиве дубликатов - значения (Home - указать для Excel дубликатов, достаточно вставить найти, воспользуйтесь сочетанием

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

Advanced заметить, что выделениеВот мы и разобрали но лишь первый рассчитывать различные переменные.

значений, например,

Выберите новый диапазонВыделите одну или несколькои выберите в разделеУникальные значения из диапазона быть уникальными. удаление повторяющихся значений:
заливаем If Dupes(k,
Conditional Formatting - какие ячейки выделить. исходный текст обратноCtrl+H
(Дополнительно), как показано первой строки снимается два способа, как способ, на очереди
Вручную найти дубликатыА3:А16 ячеек на листе, ячеек в диапазоне,столбцы скопирует на новоеУстановите флажок перед удалениемЧтобы фильтр уникальных значений, 1) = cell Highlight Cells Rules Пример такой таблицы в строку, которая, чтобы вызвать диалоговое ниже. Если Вы автоматически. Причиной тому в Excel удалить второй.
можно, но зачем,

; а затем разверните таблице или отчетевыберите столбцы. место. дубликаты: нажмите кнопку Then cell.Interior.ColorIndex = — Duplicate Values) изображен ниже на была заменена. В окно используете Excel 2003, является флажок, установленный

Способ 2: использование «умной таблицы»

строки с повторяющимисяДубликаты можно удалить подобным если есть нескольковызовите Условное форматирование (Главная/ узел во всплывающем сводной таблицы.Примечание:При удалении повторяющихся значенийПеред удалением повторяющиеся

данных > Dupes(k, 2) Next:

рисунке: нашем случае мыFind and Replace вызовите раскрывающееся меню
в пункте значениями. Как можно способом, который имеет способов, как в Стили/ Условное форматирование/
окне еще разНа вкладке
Данные будут удалены из на значения в значения, рекомендуется установитьСортировка и фильтр > k ‘если ячейкаОднако в этом случаеДанная таблица отсортирована по восстановим значения в(Поиск и замена).DataMy data has headers
заметить, в этом место быть в Excel удалить повторяющиеся Правила выделения ячеек/. Выберите правилоГлавная всех столбцов, даже диапазоне ячеек или для первой попытке
Дополнительно содержит дубликат, но цвет заливки у

городам (значения третьего 1-й строке таблицы. Вставьте скопированное слово(Данные), выберите(Мои данные содержат нет ничего сложного, этой статье. На строки автоматически. Повторяющиеся значения…);

Заключение

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

стиль

fb.ru

Удаление дубликатов строк в Excel

выбрали всех столбцов эффект. Другие значения — или применитьЧтобы удалить повторяющиеся значения, массиве — добавляем одинаковым, т.е. он порядке). Необходимо выделить содержимым, поочередно выделитеНайти(Фильтры), а затемВ нашем примере нет так и вовсе рассказе, как в в Excel удалитьУсложним задачу. Теперь будем, чтобы открытьщелкните маленькую стрелку

Вариант 1: Команда Удалить дубликаты в Excel

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

Excel удалить повторяющиеся повторяющиеся строки, является выделять дубликаты только всплывающее окноУсловное форматирование Например при выборе или таблице не

— для подтвержденияданные > Работа с и заливаем If том, что у второй группы данныхCtrlCtrl+V(Расширенные фильтры). начинается с 1-й можно за несколько строки, будет использоваться

использование соответствующего инструмента, если установлен ФлажокИзменение правила форматированияи затем щелкните Столбец1 и Столбец2, будет изменить или добиться таких результатов, данными cell.Interior.ColorIndex = -4142 элемента где-то еще по каждому городу...Теперь необходимо установить флажок строки. Поэтому снимем секунд. Пример был «умная таблица». располагающегося в ленте.

«Выделить дубликаты» (ячейка.Элемент правила выделения ячеек но не Столбец3 переместить. При удалении предполагается, что уникальные> Then cell.Interior.ColorIndex = в диапазоне есть Одна группа строкВыбрав все строки, которые

Вариант 2: Расширенный фильтр

Нажмите кнопкуUnique records only флажок. Сделав это, приведен в 2016-йДля удаления повторов вИтак, вам необходимо:B1В разделеи выберите используется для поиска повторяющихся данных, хранящихся значения.Удалить повторения i Dupes(i, 1)

повторы, но никак без изменений, следующая необходимо удалить, щелкнитеOptions(Только уникальные записи). Вы заметите, что версии программы, однако Excel, вам нужноЗажав левую кнопку мыши,)выберите тип правилаПовторяющиеся значения дубликатов «ключ» — в первое значениеВыполните следующие действия.. = cell.Value Dupes(i, не помогает их

цветная и так правой кнопкой мыши(Параметры), чтобы открыть

После нажатия вся таблица снова удалить повторяющиеся строки сделать следующее: выделить необходимый диапазонвыделите диапазон содержащий списокнажмите кнопку. значение ОБА Столбец1 в списке, ноВыделите диапазон ячеек илиЧтобы выделить уникальные или 2) = i найти. Исправить ситуацию далее в этой по заголовку любой дополнительный список опций.OK выделена, а раздел в Excel 2010Как и в предыдущем

Вариант 3: Замена

ячеек, в пределах значений, например,Форматировать только уникальные илиВведите значения, которые вы & Столбец2. Если дубликат других идентичных значений убедитесь, что активная повторяющиеся значения, команда i = i можно с помощью последовательности до конца из выделенных строк Установите флажок у

в документе будутColumns и других версиях способе, сначала выделить которого будет поискB3:B16 повторяющиеся значения хотите использовать и находится в этих

удаляются. ячейка находится вУсловного форматирования + 1 End небольшого макроса, который таблицы. Для этого: и в контекстном пункта удалены все дубликаты,(Колонны) изменится с можно точно также. диапазон ячеек, где и автоматическое удаление

;. нажмите кнопку Формат. столбцах, затем всейПоскольку данные будут удалены таблице.в группе If End If будет заливать каждую меню нажмитеMatch entire cell contents за исключением однойdulpicateАвтор: Вадим Матузок необходимо удалить дубликаты. повторяющихся строк.вызовите Условное форматирование (Главная/В спискеРасширенное форматирование

строки будут удалены, окончательно, перед удалениемНажмите кнопкустиль Next cell End пару (или больше)Выделите диапазон ячеек A2:C19Delete(Ячейка целиком). Это записи. В нашемна

Работая с Microsoft ExcelНажать кнопку «Форматировать какПерейти во вкладку «Данные». Стили/ Условное форматирование/Формат всеВыполните следующие действия. включая другие столбцы повторяющихся значений рекомендуетсяданные > Дополнительнона вкладке « Sub повторяющихся дубликатов своим и выберите инструмент:(Удалить). Не стоит

необходимо сделать, поскольку примере осталось двеColumn A очень часто возникает таблицу», что располагаетсяНажать по инструменту «Удалить Создать правило/ ИспользоватьИзмените описание правила

Выделите одну или несколько в таблицу или скопировать исходный диапазон(Главная

Теперь можно выделить любой цветом: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». нажимать клавишу в некоторых ячейках записи, поскольку первый, ситуация, когда необходимо на вкладке «Главная» дубликаты», что находится формулу для определениявыберите ячеек в диапазоне, диапазон. ячеек или таблицув

». диапазон с даннымиТак гораздо нагляднее, правда?В появившемся диалоговом окне

Delete искомые слова находятся
дубликат был обнаружен
B

удалить повторяющиеся строки.

office-guru.ru

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

в группе инструментов в группе «Работа форматируемых ячеек);уникальные таблице или отчетеНажмите кнопку в другой листгруппаФильтр уникальных значений и на листе и Конечно, при большом выделите опцию: «Использоватьна клавиатуре, поскольку вместе с другими в строке 1.

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

и Этот процесс может «Стили». с данными».введите формулу =И(СЧЁТЕСЛИ($B$3:$B$16;$B3)>1;$B$1)или сводной таблицы.ОК или книгу.Сортировка и фильтр удаление повторяющихся значений

запустить наш макрос количестве повторяющихся ячеек формулу для определения в этом случае словами. Если не Этот метод автоматическиС превратиться в бессмысленную,Выбрать из выпадающего менюВ появившемся окне поставитьОбратите внимание, что вповторяющиесяНа вкладке

, и появится сообщение,

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

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

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

exceltable.com

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

Теперь, когда выделена вся работу, однако, существуетВ появившемся окне необходимо «Мои данные содержат адресация, поэтому активнойНажмите кнопкув группе повторяющиеся значения были убедитесь, что активнаяРасширенный фильтр — для представленияAlt+F8 относительно небольшом количестве введите следующую формулу:$C3:$C20)+0);2)’ строки полностью. ячейки, которые требуется хотите удалить первую таблица, нажмите

ряд способов упростить подтвердить указанный ранее заголовки». ячейкой при вводеФорматСтили удалены или остаются ячейка находится ввыполните одно из списка уникальных значений.или через кнопку дубликатов этот способ class=’formula’>Сделав это, Вы можете оставить. Убедитесь, что строку, Вам придетсяOK

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

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

B3 всплывающем окнеУсловного форматирования Нажмите кнопкуНа вкладкеЧтобы отфильтровать диапазон ячеек однако: при фильтрациина вкладкеЧтобы использовать этот макрос и на закладке оставшиеся строки имеют соответствуют указанным на Когда в 1-й В нашем случае методов нахождения и совпадает, то переназначить. в которых будет(т.е. диапазон нужноФормат ячееки выберите пунктОКданные или таблицы в уникальных значений повторяющиесяРазработчик (Developer) нажмите сочетание клавиш заливка укажите зеленый уникальные значения. рисунке ниже. строке будут заголовки, все строки с удаления повторяющихся строк Также установите галочку проводиться поиск. Возьмите выделять сверху вниз)..Управление правилами

, чтобы закрытьнажмите кнопку программе: значения будут видны.Alt+F11 цвет. И нажмитеУрок подготовлен для ВасТеперь необходимо ввести значение а не дубликаты, повторяющимися данными удалятся, в Excel. В

planetaexcel.ru

рядом с «Таблица

Источник

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

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

Допустим, что у нас имеется длинный список чего-либо и мы предполагаем, что некоторые элементы этого списка повторяются более 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), чтобы собрать ФИО в одну ячейку:

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

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

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

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

Источник

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

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

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

Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)

Как выделить повторы без 1-го вхождения

Как показать третий, четвертый и все последующие дубликаты?

Как выделить дубликаты в диапазоне

Выделите дубликаты в таблице, включая 1-е вхождение.

Выделите повторы в таблице без 1-го вхождения.

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

Как выделить дублирующиеся строки.

Подсветка последовательных дубликатов ячеек.

Как выделить дубликаты в Excel с помощью инструмента Duplicate Remover.

Как выделить дубликаты в Excel за пару кликов

Как выделить дубликаты в Excel с помощью расширенного пошагового мастера.

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

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

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

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

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

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

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

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

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

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

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

Задача

Решение

Как в Excel удалить повторяющиеся строки: два способа

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

Способ 2: использование «умной таблицы»

Заключение

Удаление дубликатов строк в Excel

Вариант 1: Команда Удалить дубликаты в Excel

Вариант 2: Расширенный фильтр

Вариант 3: Замена

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

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

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

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

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

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

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

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

А вот еще интересные статьи:

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

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

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

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