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

Дубликаты в Excel могут доставить массу проблем. Независимо от того, импортируете ли вы данные из базы данных, получаете их от коллеги либо сопоставляете сами, дубликаты данных постоянно могут закрасться. А если данные, с которыми вы работаете, громадны, тогда становится вправду трудно отыскать и удалить эти дубликаты в Excel. .

В этом руководстве я покажу для вас, как отыскать и удалить дубликаты в Excel.

СОДЕРЖАНИЕ:

НАЙДИТЕ и ВЫДЕЛЯЙТЕ дубликаты в Excel.
- Найдите и выделите дубликаты в одном столбце.
- Найдите и выделите дубликаты в нескольких столбцах.
- Найдите и выделите повторяющиеся строчки.
УДАЛИТЬ Дубликаты в Excel.
- Удалите дубликаты из 1-го столбца.
- Удалите дубликаты из нескольких столбцов.
- Удалите повторяющиеся строчки.

Найдите и выделите дубликаты в Excel

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

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

Условное форматирование упрощает выделение дубликатов в Excel.

Ах так это создать:

Изберите данные, в каких вы желаете выделить дубликаты.

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

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

Это выделит все значения, у каких есть дубликаты.

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

Поиск и выделение дубликатов в нескольких столбцах в Excel

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

Ах так это создать:

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

Поиск и выделение циклических строк в Excel

Поиск циклических данных и поиск циклических строк данных — это две различные вещи. Посмотри:

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

В примыкающем столбце используйте последующую формулу:
= A2, B2, C2 и D2
Перетащите его вниз для всех строк. Эта формула соединяет воединыжды все значения ячеек в одну строчку. (Вы также сможете употреблять функцию СЦЕПИТЬ для объединения текстовых строк)

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

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

Вот шаги, чтоб выделить повторяющиеся строчки:

Изберите спектр, содержащий объединенные строчки (в этом примере E2: E16).
Перейдите на главную страничку -> Условное форматирование -> Выделите правила ячеек -> Повторяющиеся значения.
В диалоговом окне «Повторяющиеся значения» удостоверьтесь, что избран параметр «Дублировать», а потом укажите цвет, которым вы желаете выделить повторяющиеся значения.

Это выделит повторяющиеся значения в столбце E.

В описанной чуть повыше подходе мы выделили лишь те строчки, которые мы сделали.

Но что, если вы желаете выделить все повторяющиеся строчки (заместо выделения ячеек в одном столбце)?

Интересно почитать: Как научиться работать в excel

Вот шаги, чтоб выделить повторяющиеся строчки:

В примыкающем столбце используйте последующую формулу:
= A2, B2, C2 и D2
Перетащите его вниз для всех строк. Эта формула соединяет воединыжды все значения ячеек в одну строчку.

Изберите данные A2: D16.
Выбрав данные, перейдите в раздел «Основная» -> «Условное форматирование» -> «Новое правило».

В диалоговом окне «Новое правило форматирования» нажмите «Употреблять формулу, чтоб найти, какие ячейки необходимо форматировать».
В поле ниже используйте последующую функцию СЧЁТЕСЛИ:
= СЧЁТЕСЛИ ($ E $ 2: $ E $ 16, $ E2)> 1

Изберите формат и нажмите ОК.

Эта формула выделит все строчки, у каких есть дубликаты.

Удалить дубликаты в Excel

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

Удаление дубликатов из 1-го столбца в Excel

Если у вас есть данные в одном столбце и вы желаете удалить все дубликаты, сделайте последующие деяния:

Изберите данные.
Перейдите в Данные -> Инструменты для работы с данными -> Удалить дубликаты.
В диалоговом окне «Удалить дубликаты»:
- Если у ваших данных есть заглавия, удостоверьтесь, что установлен флаг «Мои данные содержат заглавия».
- Удостоверьтесь, что столбец избран (в этом случае есть лишь один столбец).
- Щелкните ОК.
Это удалит все повторяющиеся значения из столбца, и вы получите лишь неповторимые значения.

ОСТОРОЖНОСТЬ: Это изменяет ваш набор данных, удаляя дубликаты. Удостоверьтесь, что у вас есть запасная копия начального набора данных. Если вы желаете извлечь неповторимые значения в каком-то другом месте, скопируйте этот набор данных в это пространство, а потом сделайте вышеупомянутые шаги. Не считая того, вы также сможете употреблять расширенный фильтр для извлечения неповторимых значений в другое пространство.

Удалить дубликаты из нескольких столбцов в Excel

Представим, у вас есть данные, как показано ниже:

В приведенных выше данных строчки №2 и №16 содержат схожие данные для торгового представителя, региона и суммы, но различные даты (так же обстоит дело со строчками №10 и №13). Это быть может ошибка ввода, когда одна и та же запись была записана два раза с различными датами.

Чтоб удалить повторяющуюся строчку в этом случае:
- Изберите данные.
- Перейдите в Данные -> Инструменты для работы с данными -> Удалить дубликаты.
- В диалоговом окне «Удалить дубликаты»:
  - Если у ваших данных есть заглавия, удостоверьтесь, что установлен флаг «Мои данные содержат заглавия».
  - Изберите все столбцы, не считая столбца Дата.
  - Щелкните ОК.
  Это приведет к удалению 2 циклических записей.
  
  ПРИМЕЧАНИЕ. При всем этом сохраняется 1-ое вхождение и удаляются все оставшиеся повторяющиеся вхождения.
  
  Удалить повторяющиеся строчки в Excel
  
  Чтоб удалить повторяющиеся строчки, сделайте последующие деяния:
  - Выделите все данные.
  - Перейдите в Данные -> Инструменты для работы с данными -> Удалить дубликаты.
  - В диалоговом окне «Удалить дубликаты»:
    - Если у ваших данных есть заглавия, удостоверьтесь, что установлен флаг «Мои данные содержат заглавия».
    - Выделите все столбцы.
    - Щелкните ОК.
    Используйте вышеупомянутые способы, чтоб очистить свои данные и избавиться от дубликатов.
    
    Для вас также могут приглянуться последующие управления по Excel:
    
    Выделите дубликаты в Excel
    
    Как выделить повторяющиеся значения в Excel?
    
    Ниже приведены аннотации по поиску либо выделению циклических значений в Excel.
    - Шаг 1: Изберите спектр перечня, который содержит повторяющиеся значения. (Изберите лишь спектр данных, а не весь лист)
    - Шаг 2: Перейдите на главную> Условное форматирование> Правила выделения ячеек> Повторяющиеся значения.
    Сочетания кнопок Excel для выделения циклических значений Excel:
    - Шаг 3: Опосля того, как вы нажмете на «Повторяющиеся значения», раскроется диалоговое окно, показанное ниже. Тут мы можем избрать хотимый тип форматирования в Excel. Мы можем выделить и ячейку, и текст 3-мя различными цветами, мы можем лишь заполнить ячейку, мы можем выделить лишь текстовые значения, либо мы можем выделить границы циклических ячеек.
    - Шаг 4: Опосля выбора типа форматирования будут выделены все повторяющиеся значения Excel в спектре. Тут я избрал красную заливку с красным текстом.
    Выделите повторяющиеся значения на всем листе
    
    Мы можем не только лишь выделить повторяющиеся значения Excel в одном столбце; быстрее, мы можем выполнить эту задачку на всем листе.
    
    У меня есть данные в первых 2-ух столбцах. Я желаю употреблять условное форматирование, чтоб в дальнейшем, если я введу какие-либо повторяющиеся значения, оно обязано выделять циклическое значение для меня.
    - Шаг 1: Выделите весь столбец номера счета-фактуры. Причина, по которой нам необходимо избрать все, поэтому что тут я создаю проверку, чтоб, если в дальнейшем будут изготовлены какие-либо повторяющиеся записи, она обязана автоматом уведомить меня о разработке циклического значения.
    - Шаг 2: Перейдите на главную> Условное форматирование> Правила выделения ячеек> Повторяющиеся значения.
    - Шаг 3: Изберите цвет, который желаете выделить. Тут я избрал зеленоватый для заливки темным текстом.
    - Шаг 4: Тут я уже отыскал четыре циклических значения.
    - Шаг 5: Сейчас попытайтесь ввести хоть какой из циклических номеров счетов-фактур, которые он отыщет, и выделите повторяющуюся запись в Excel.
    Удалить повторяющиеся значения в Excel
    
    Поиск и выделение циклических значений — точно нужный навык сейчас, и вы узнали о этом в прошлом примере. В этом разделе статьи я покажу для вас, как удалить эти дубликаты из выделения.
    - Шаг 1: Изберите циклический спектр данных.
    - Шаг 2: Перейдите в Данные> Удалить дубликаты.
    Сочетание кнопок для удаления дубликатов в Excel:
    - Шаг 3: Опосля того, как вы избрали опцию «Удалить повторяющиеся значения», раскроется диалоговое окно, показанное ниже. Так как у меня лишь один столбец, тут отображается этот единственный заголовок столбца.
    Примечание. Удостоверьтесь, что в поле «Мои данные есть заглавия» установлен флаг.
    - Шаг 4: Щелкните по кнопочке ОК. Это одномоментно удалит все повторяющиеся значения из выбора. Он также покажет для вас подсказку о том, сколько дубликатов удалено и сколько неповторимых значений осталось.
    Поглядите на изображение выше; возникло диалоговое окно, в каком указаны 3 циклических значения, отысканных и удаленных; Осталось 16 неповторимых ценностей.
    
    Как выделить дубликаты в excel различными цветами
    
    Как выделить повторяющиеся значения в Excel различными цветами?
    
    Пример настраиваемого макроса для выделения циклических значений различным цветом заливки ячеек Excel.
    
    Отмечаем различными цветами ячейки с циклическими значениями
    
    Нам необходимо чтоб макрос VBA, с помощью различных цветов отмечал в столбце повторяющиеся значения. Наиболее либо наименее так:
    
    Макрос отмечает повторяющиеся значения наиболее либо наименее таковым образом: Если б речь шла лишь о выборе ячеек, данные которых просто повторяются, тогда можно просто употреблять условное форматирование. Но в обсуждаемом примере дело обстоит мало труднее, поэтому что нужно создать так, чтоб различные значения были отмечены различными цветами. Не считая того, необходимо придумать как просто выбирать эти цвета.
    
    Вспомогательный лист (с цветами) смотрится приблизительно так:
    
    Тут юзер может указать свои желаемые пользовательские цвета для подсветки дубликатов.
    
    Установка цветов для подсветки ячеек с дубликатами
    
    Раскрашивая надлежащие ячейки на вспомогательном листе, мы тем обозначаем, какими цветами будут отмечены повторяющиеся значения в столбце с данными. Макрос берет попеременно цвета из обозначенных ячеек, и устанавливает их как цвет фона для каждой из ячеек, имеющих повторяющиеся значения. Если "не хватит" цветов (циклических значений больше, чем определенных цветов), они (цвета) будут просто взяты с начала.
    
    Так как лучше создать так, чтоб ячейки обновлялись всякий раз, когда что-то вводится в ячейку, макрос обрабатывает событие onChange на листе «Данные».
    
    Макрос смотрится последующим образом:
    
    Ниже, как обычно, файл с примером для скачки:
    
    Естественно, это не безупречное решение, но в обычных вариантах оно делает свою функцию. Не считая того, оно может послужить вдохновением и отправной точкой для наиболее всеохватывающих решений.
    
    Макрос для выделения дубликатов различными цветами
    
    Как понятно, в крайних версиях Excel просто выделить дубликаты цветом, — для этого есть особая функция в «условном форматировании».
    Довольно выделить спектр, задать цвет заливки, — и все повторяющиеся (либо, напротив, неповторимые) значения будут выделены.
    
    Но время от времени требуется, чтоб разные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.
    В этом случае, без макросов не обойтись.
    
    Ниже приведён макрос, который как раз и решает эту задачку
    (довольно выделить спектр ячеек, запустить макрос, — и повторяющиеся непустые ячейки получат однообразный цвет заливки)
    - 67949 просмотров
    Комменты
    
    Имеется поле суммы столбика В /=СУММ(B1:B23)/, можно создать так, что эту сумму врубались лишь поля, которые имеют определённый цвет другого поля? К примеру: при всем этом столбик А имеет цвет полей от А1 до А10 желтоватый, а с А11 до А23 красноватый.Т.Е. что бы автосумм учитывала лишь поля красноватого цвета, и при изменении его она пересчитывала итог.
    Заблаговременно спасибо!
    
    Евгений, там много в коде переделывать нужно. Это лишь если под заказ (платно)
    
    Хороший инструмент, спасибо!
    Подскажите, что необходимо поменять в этом макросе, чтобы закрашивались лишь те ячейки, которые повторяются не меньше 4-х (5-и, . 8-и) раз? Готов всякий раз лазить в макрос и поменять на необходимое кол-во, лишь подскажите что и где? (я не спец по макросам, к огорчению).
    
    Хороший денек .Есть большенный лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Любая ячейка содержит 10 цифр в порядке возрастания. Смотрятся ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. некие ячейки из их с циклическими цифрами к примеру: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99
    
    4-5 цифр повторяются, другие различные. Ячейки в каких совпадают все 10 цифр можно автоматом выделить при помощи условного форматирования.
    А ах так создать так, чтоб схожим образом автоматом выделялись цветом ячейки в каторых совпадают 4 числа и наиболее?
    За преждевременное спасибо!
    
    Александр, здесь макрос необходимо писать.
    Можем создать под заказ
    
    Хороший денек . Есть большенный лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Любая ячейка содержит 10 цифр в порядке возрастания. Смотрятся ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. Но некие ячейки из их с циклическими цифрами к примеру: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99
    
    4-5 цифр повторяются, другие различные. Ячейки в каких совпадают все 10 цифр можно автоматом выделить при помощи условного форматирования.
    А ах так создать так, чтоб схожим образом автоматом выделялись цветом ячейки в каких совпадают 4 числа и наиболее?
    За преждевременное спасибо!
    
    а где макрос? не могу скачать
    
    Всё можно
    Но это другом макрос нужен. Можем создать под заказ
    
    Подскажите, а можно ли создать? Есть 2 столбца (1-ый составлен из второго с удалением дублей), мне необходимо отыскать все дубли во 2-м и лишь то что дублируется окрасить в цвет и в первом и и во 2-м в один цвет?
    
    Спасибо, супер макрос, весьма посодействовал
    
    Де здесь лайк ставить !? спасибо . основное что не разрезает глаза . единственное необходимо убрать сероватый фон либо создать его потемнее . соединяется с белоснежным .