Как найти повторяющиеся значения в столбце в excel

Повторяющиеся значения в Excel: находим и выделяем быстро

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

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

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

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

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

  Найти и выделить цветом дубликаты в Excel

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

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

  

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

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

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

  

  Второй шаг процедуры – выбор цветов заливки и текста

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

  

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

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

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

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

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

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

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

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

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

  Формула возвращает ИСТИНА, если в адресованном диапазоне появляется дубликат

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Как найти дубли ячеек, не учитывая лишние пробелы

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

  Как найти одинаковые значения в столбце Excel

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

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

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

  

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

  Пример дневного журнала заказов на товары:

  Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

  1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
  2. Вберете «Использовать формулу для определения форматируемых ячеек».
  3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
  4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

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

  Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

  Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

  Самые быстрые и простые способы: найти дубликаты в ячейках.

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

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

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

  

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

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

  

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

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

  

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

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

  

  

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

  

  

  

  Как найти повторяющиеся ячейки в excel

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

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

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

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

  

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

  

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

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

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

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

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

  

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

  

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

  

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

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

  

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

  

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

  Отбор повторяющихся значений в 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) пункт​ удалить дубликаты.​ будет обновлен, чтобы​ в исходном списке​=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок;​А​ и другом читайте​ ячеек» выбрать функцию​

  Поиск дубликатов в 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. ​ компаний. В некоторых​

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

  

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

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

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

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

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

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

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

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

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

  Похожие публикации:

  1. Как скопировать формулу в excel в другую ячейку без изменения
  2. Как собрать листы из разных книг excel в одну книгу excel
  3. Как сохранить макрос в excel для всех книг
  4. Как считать градусы в excel