Эксель повторяющиеся ячейки как найти

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

1. Выберите ячейки, которые нужно проверить на наличие повторений.

   Примечание: В Excel не поддерживается выделение повторяющихся значений в области “Значения” отчета сводной таблицы.

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

   

3. В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК.

   

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

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

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

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

   

   Например, на данном листе в столбце “Январь” содержатся сведения о ценах, которые нужно сохранить.

   

   Поэтому флажок Январь в поле Удаление дубликатов нужно снять.

   

3. Нажмите кнопку ОК.

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

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

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

​Смотрите также​ одинаковые значения, например:​A3​Выберите стиль форматирования и​ Следуйте инструкции ниже,​(Conditional Formatting >​ Это очень ресурсоемкая​ (столбец С) автоматически​ИсхСписок- это Динамический диапазон​ значения, которые повторяются.​ Как убрать повторяющиеся​

1. ​ способов найти одинаковые​ нам нужно выделить:​Первый способ.​

   ​Нажмите кнопку​​Перед попыткой удаления​>​В некоторых случаях повторяющиеся​12​

2. ​:​​ нажмите​​ чтобы выделить только​​ Highlight Cells Rules)​​ задача и годится​​ будет обновлен, чтобы​​ (ссылка на исходный​​ Дополнительное условие: при​​ значения в Excel,​

   

3. ​ значения в Excel​ повторяющиеся или уникальные​​Как найти одинаковые значения​​ОК​ повторений удалите все​Повторяющиеся значения​ данные могут быть​​45​​=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.​

   

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

​ОК​​ те значения, которые​​ и выберите​ для небольших списков​ включить новое название​ список в столбце​ добавлении новых значений​ смотрите в статье​ и выделить их​

1. ​ значения. Выбираем цвет​ в Excel​.​

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

2. ​ встречающиеся трижды:​​Повторяющиеся значения​​ 50-100 значений. Если​​3. Добавьте в исходный​​А​​ в исходный список,​​ «Как удалить дубли​ не только цветом,​ заливки ячейки или​.​

   

   ​Рассмотрим,​ итоги из своих​В поле рядом с​ они усложняют понимание​6​

   

   ​ создали абсолютную ссылку​​Результат: Excel выделил значения,​​Сперва удалите предыдущее правило​​(Duplicate Values).​​ динамический список не​

   

3. ​ список название новой​​).​​ новый список должен​

support.office.com

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

​ в Excel».​​ но и словами,​​ цвет шрифта.​Например, число, фамилию,​к​​ данных.​ оператором​ данных. Используйте условное​8​ –​ встречающиеся трижды.​ условного форматирования.​Определите стиль форматирования и​ нужен, то можно​ компании еще раз​Скопируйте формулу вниз с​ автоматически включать только​Из исходной таблицы с​ числами, знаками. Можно​Подробнее смотрите в​ т.д. Как это​ак найти и выделить​
​На вкладке​
​значения с​ форматирование для поиска​​12​
​$A$1:$C$10​Пояснение:​Выделите диапазон​ нажмите​ пойти другим путем:​
​ (в ячейку​
​ помощью Маркера заполнения​ повторяющиеся значения.​​ повторяющимися значениями отберем​ настроить таблицу так,​ статье «Выделить дату,​

​ сделать, смотрите в​ одинаковые значения в​Данные​выберите форматирование для​ и выделения повторяющихся​6​.​Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество​
​A1:C10​ОК​ см. статью Отбор​А21​ (размерность списка значений​Список значений, которые повторяются,​ только те значения,​
​ что дубли будут​ день недели в​ статье «Как выделить​ Excel.​нажмите кнопку​​ применения к повторяющимся​ данных. Это позволит​589​

​Примечание:​ значений в диапазоне​.​.​ повторяющихся значений с​снова введите ООО​ имеющих повторы должна​ создадим в столбце​ которые имеют повторы.​ не только выделяться,​ Excel при условии»​​ ячейки в Excel».​Нам поможет условное​Удалить дубликаты​ значениям и нажмите​ вам просматривать повторения​Можно ли как-нибудь​Вы можете использовать​A1:C10​На вкладке​Результат: Excel выделил повторяющиеся​ помощью фильтра. ​ Кристалл)​ совпадать с размерностью​B​ Теперь при добавлении​ но и считаться.​ тут.​Второй способ.​ форматирование.Что такое условное​и в разделе​ кнопку​ и удалять их​ обозначить эти совпадения​ любую формулу, которая​, которые равны значению​Главная​ имена.​Этот пример научит вас​4. Список неповторяющихся значений​ исходного списка).​с помощью формулы​

excel-office.ru

Отбор повторяющихся значений в MS EXCEL

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

​ в ячейке A1.​​(Home) выберите команду​​Примечание:​ находить дубликаты в​ автоматически будет обновлен,​В файле примера также​ массива. (см. файл​ исходный список, новый​

Задача

​ значения с первого​ D выделились все​ в Excel​ с ним работать,​установите или снимите​.​Выберите ячейки, которые нужно​ форматирования? Или просто​ чтобы выделить значения,​

Решение

​Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует​Условное форматирование​​Если в первом​​ Excel с помощью​ новое название будет​ приведены перечни, содержащие​

​ примера).​​ список будет автоматически​​ слова, а можно​
​ года – 1960.​
​. В этой таблице​
​ смотрите в статье​

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

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

​Введем в ячейку​ содержать только те​ выделять дубли со​Можно в условном​ нам нужно выделить​ “Условное форматирование в​

​ в которых нужно​Удаление дубликатов​ повторений.​ и выделить в​

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

Тестируем

​ по этой ссылке,​5. Список повторяющихся значений​ уникальные значения.​​B5​​ значения, которые повторяются.​

​ второго и далее.​ форматировании тоже в​ год рождения 1960.​ Excel” здесь. Выделить​

​ удалить повторения.​повторяющиеся данные удаляются​Примечание:​ доп столбце?​​ формулу:​​ кнопку​(Conditional Formatting >​

​Повторяющиеся​ чтобы узнать, как​ (столбец B) автоматически​С помощью Условного форматирования​

​формулу массива:​Пусть в столбце​ Обо всем этом​ разделе «Правила выделенных​

​Выделяем столбец «Год​

​ повторяющиеся значения в​Например, на данном листе​ безвозвратно. Чтобы случайно​ В Excel не поддерживается​_Boroda_​=COUNTIF($A$1:$C$10,A1)>3​Условное форматирование​ New Rule).​(Duplicate) пункт​ удалить дубликаты.​ будет обновлен, чтобы​ в исходном списке​=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок;​А​ и другом читайте​ ячеек» выбрать функцию​

excel2.ru

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

​ рождения». На закладке​ Excel можно как​ в столбце “Январь”​ не потерять необходимые​ выделение повторяющихся значений​: Выделяем диапазон,​=СЧЕТЕСЛИ($A$1:$C$10;A1)>3​

1. ​(Conditional Formatting), мы​​Нажмите на​​Уникальные​
2. ​Выделите диапазон​​ включить новое название.​​ можно выделить повторяющиеся​​ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0)​​имеется список с​​ в статье “Как​​ «Содержит текст». Написать​ «Главная» в разделе​ во всей таблицы,​​ содержатся сведения о​​ сведения, перед удалением​
3. ​ в области “Значения”​Главная – условное​​Урок подготовлен для Вас​​ выбрали диапазон​​Использовать формулу для определения​(Unique), то Excel​

   

​A1:C10​​СОВЕТ:​ значения.​);””)​​ повторяющимися значениями, например​​ найти повторяющиеся значения​​ этот текст (например,​​ «Стили» нажимаем кнопку​ так и в​ ценах, которые нужно​

​ повторяющихся данных рекомендуется​ отчета сводной таблицы.​ форматирование – Правила​ командой сайта office-guru.ru​A1:C10​ форматируемых ячеек​ выделит только уникальные​.​Созданный список повторяющихся значений​

1. ​1. Добавьте в исходный​Вместо​
2. ​ список с названиями​​ в Excel”. В​​ фамилию, цифру, др.),​
3. ​ «Условное форматирование». Затем​​ определенном диапазоне (строке,​​ сохранить.​​ скопировать исходные данные​​На вкладке​​ выделения ячеек -​​Источник: http://www.excel-easy.com/examples/find-duplicates.html​, Excel автоматически скопирует​
4. ​(Use a formula​​ имена.​На вкладке​​ является динамическим, т.е.​ список название новой​ENTER​
5. ​ компаний. В некоторых​

   ​ таблице можно удалять​
​ и все ячейки​

6. ​ в разделе «Правила​ столбце). А функция​​Поэтому флажок​​ на другой лист.​​Главная​ Повторяющиеся значения​

   

   ​Перевел: Антон Андронов​

   • ​ формулы в остальные​ to determine which​​Как видите, Excel выделяет​​Главная​ при добавлении новых​
   • ​ компании (в ячейку​нужно нажать​
   • ​ ячейках исходного списка​ дубли по-разному. Удалить​​ с этим текстом​​ выделенных ячеек» выбираем​ “Фильтр в Excel”​​Январь​​Выделите диапазон ячеек с​выберите​Или в соседнем​Автор: Антон Андронов​​ ячейки. Таким образом,​​ cells to format).​​ дубликаты (Juliet, Delta),​​(Home) нажмите​

     ​ значений в исходный​

   • ​А20​CTRL + SHIFT +​ имеются повторы.​​ строки по полному​​ выделятся цветом. Мы​

​ «Повторяющиеся значения».​​ поможет их скрыть,​в поле​ повторяющимися значениями, который​Условное форматирование​ столбце (или тоже​hatter​ ячейка​

​Введите следующую формулу:​
​ значения, встречающиеся трижды​

​Условное форматирование​ список, новый список​
​введите ООО Кристалл)​
​ ENTER​

​Создадим новый список, который​

office-guru.ru

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

​ совпадению, удалить ячейки​​ написали фамилию «Иванов».​В появившемся диалоговом​ если нужно. Рассмотрим​
​Удаление дубликатов​
​ нужно удалить.​
​>​
​ в условном форматировании)​
​: имеется столбец в​
​A2​
​=COUNTIF($A$1:$C$10,A1)=3​
​ (Sierra), четырежды (если​
​>​ будет автоматически обновляться.​2. Список неповторяющихся значений​.​ содержит только те​ в столбце, т.д.​Есть еще много​

​ окне выбираем, что​​ несколько способов.​
​нужно снять.​Совет:​Правила выделения ячеек​200?’200px’:”+(this.scrollHeight+5)+’px’);”>=СЧЁТЕСЛИ(Диапазон;Ссылка_на_ячейку)>1​
​ котором могут находиться​содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка​=СЧЕТЕСЛИ($A$1:$C$10;A1)=3​
​ есть) и т.д.​

excelworld.ru

​Правила выделения ячеек​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Найти и выделить цветом дубликаты в Excel
• Формула проверки наличия дублей в диапазонах
  • Внутри диапазона
• !SEMTools, поиск дублей внутри диапазона
  • Найти дубли ячеек в столбце, кроме первого
  • Найти в столбце дубли ячеек, включая первый
  • Найти дубли в столбце без учета лишних пробелов

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

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

Ключевых моментов несколько:

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

Сначала рассмотрим простые примеры.

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

Найти инструмент можно на вкладке программы “Главная”:

Процедура интуитивно понятна:

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

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

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

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

Но есть и другие решения. О них дальше.

Формула проверки наличия дублей в диапазонах

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

Внутри диапазона

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

=СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон;тот-же-диапазон)-1)>0

Так выглядит на практике применение формулы:

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

А дело все в том, что формулу несложно видоизменить и улучшить.

Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ .Это позволит находить дубликаты, отличающиеся незаметными лишними пробелами:

=СУММПРОИЗВ(--(СЖПРОБЕЛЫ(ячейка)=СЖПРОБЕЛЫ(диапазон)))>1

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

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

Обратите внимание на один момент в этой демонстрации: диапазон закреплен ($A$1:$B$4), а искомая ячейка (A1) нет. Именно это позволяет условному форматированию находить все дубликаты в диапазоне.

!SEMTools, поиск дублей внутри диапазона

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

Давайте покажу, как они работают.

Найти дубли ячеек в столбце, кроме первого

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

Найти в столбце дубли ячеек, включая первый

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

Найти дубли в столбце без учета лишних пробелов

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

Для первой операции есть отдельный инструмент «Удалить лишние пробелы»:

Найти повторяющиеся значения в Excel и решить сотни других задач поможет надстройка !SEMTools.

Скачайте прямо сейчас и убедитесь сами!

---

Смотрите также:

• Удалить дубли без смещения строк;
• Удалить неявные дубли;
• Найти повторяющиеся слова в Excel;
• Удалить повторяющиеся слова внутри ячеек.