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

Спросите у SEO-шника без чего он, как без рук! Он наверняка ответит: без Excel! Эксель – лучший друг и помощник и для специалиста в SEO, и для вебмастера.

Одна из задач, которую тебе точно придётся решать при работе с большими массивами данных – это поиск дублей в Excel. Не вариант проверять тысячи ячеек руками – угробишь на это часы и выйдешь с работы, пошатываясь, будто пьяный. Я предложу тебе 2 способа, как выполнить эту работу в десяток раз быстрее. Они дают немного разные результаты, но в равной степени просты.

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

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

Наша цель – найти повторы в столбцах Excel и выделить их цветом.

Шаг №1. Выделяем весь диапазон.

Шаг №2. Кликаем на раздел «Условное форматирование» в главной вкладке.

Шаг №3. Наводим на пункт «Правила выделения ячеек» и в появившемся списке выбираем «Повторяющиеся значения».

Шаг №4. Возникнет окно. Вам нужно выбрать, хотите ли вы подсветить повторяющиеся или уникальные значения. Также можно установить цвета заливки и текста.

Нажмите «ОК», и вы обнаружите: одинаковые ячейки в двух столбиках теперь выделены! Как видите, это вопрос 30 секунд.

Описанный вариант – самый удобный для пользователей Эксель версий 2013 и 2016.

Как вычислить повторы при помощи сводных таблиц

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

Вернёмся к нашим баранам футболистам. Я оставил один столбик, добавив в него ячейки-дубли, а также дописал заглавную строку (это обязательно).

Далее делаем следующее:

Шаг 1. В ячейках напротив фамилий проставляем единички. Вот так:

Шаг 2. Переходим в раздел «Вставка» главного меню и в блоке «Таблицы» выбираем «Сводная таблица».

Откроется окно «Создание сводной таблицы». Здесь нужно выбрать диапазон данных для анализа (1), указать, куда поместить отчёт (2) и нажать «ОК».

Только не ставьте галку напротив «Добавить эти данные в модель данных». Иначе Эксель начнёт формировать модель, и это парализует ваш комп на пару минут минимум.

Шаг 3. Распределите поля сводной таблицы следующим образом: первое поле (в моём случае «Футболисты») – в область «Строки», второе («Значение2») – в область «Значения». Используйте обычное перетаскивание (drag-and-drop).

Должно получиться так:

А на листе сформируется сама сводка – уже без дублированных ячеек. Зато во втором столбике будет указано, сколько ячеек-дублей с конкретным содержанием было обнаружено в первом столбике (например, Онопко – 2 шт.).

Этот метод «на бумаге» может выглядеть несколько замороченным, но уверяю: попробуете раз-два, набьёте руку, а потом все операции будете выполнять за минуту.

Заключение

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

Хотя на самом деле функционал программы Эксель настолько широк, что можно не только подсветить повторяющиеся значения в столбике, но и автоматически их все удалить. Я знаю, как это делается, но сейчас вам не скажу. Теперь на сайте есть отдельная статья об уд алении повторяющихся строк в Excel – там и смотрите 😉.

Помогли ли тебе мои методы работы с данными? Или ты знаешь лучше? Поделись своим мнением в комментариях!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​Нажимаем «ОК». Все ячейки​Нам нужно не​ повторить процедуру с​

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

​ что и строка​​Форматировать только уникальные или​​Главная​​ клавиши Ctrl +​​.​​кнопку ОК​​ условное форматирование на​​стиль​​ дни, т.д., смотрите​

​ с повторяющимися данными​ только выделить повторы,​​ обозначением дублей.​​ ячеек с уникальными​ работать с ними​ повторяющимися значениями, который​ данные могут быть​​«Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только​​ 6. А значение​

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

​ повторяющиеся значения​​в группе​​ Z на клавиатуре).​Чтобы быстро удалить все​.​ — для подтверждения​на вкладке "​ в статье "Как​ окрасились.​

​ но и вести​Второй способ.​ данными написать слово​

​ – посчитать дубли​​ нужно удалить.​ полезны, но иногда​ уникальные записи». Инструмент​ строки 4 =​.​

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

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

​ строке 7. Ячейки​​В списке​​щелкните стрелку для​​ из структуры данных,​​Снять выделение​

​ скопирует на новое​​ предполагается, что уникальные​​".​

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

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

​ секунд и сообщить,​​ в​Как посчитать данные​ формуле можно писать​ столбцу до последней​ с дублями.​Например, на данном листе​На вкладке​Формула в массиве:1;0;1);0));"")’ >.​Щелкните по таблице и​ который нужно применять,​.​ значениям невозможно.​ выбрали всех столбцов​Поскольку данные будут удалены​выполните одно из​Повторяющееся значение входит в​ помогла ли она​Excel.​ в ячейках с​ любые или числа,​ заполненной ячейки таблицы.​

​Дублирующие данные подкрасили условным​ в столбце "Январь"​Главная​ Формула ищет одинаковые​

​ выберите инструмент «Работа​ если значение в​​Убедитесь, что выбран соответствующий​ ​Быстрое форматирование​ на этом этапе.​​ окончательно, перед удалением​

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

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

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

​ Например при выборе​ повторяющихся значений рекомендуется​Чтобы отфильтровать диапазон ячеек​ в по крайней​ кнопок внизу страницы.​ выделить данные по​ удалить их, смотрите​ столбце E написали​ A отфильтруем данные​Есть два варианта​ ценах, которые нужно​Условное форматирование​ A2:A13 и выводит​ в разделе инструментов​ и нажмите кнопку​

​ в списке​Выделите одну или несколько​ Столбец1 и Столбец2,​ скопировать исходный диапазон​ или таблицы в​ мере одна строка​ Для удобства также​ трем столбцам сразу.​ в статье «Как​ такую формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"Повторно";"Впервые")​ – «Фильтр по​ выделять ячейки с​ сохранить.​>​ их в отдельный​ «Сервис».​ОК​Показать правила форматирования для​ ячеек в диапазоне,​ но не Столбец3​ ячеек или таблицу​

​ программе:​ идентичны всех значений​​ приводим ссылку на​ У нас такая​ сложить и удалить​В столбце F​ цвету ячейки». Можно​ одинаковыми данными. Первый​Поэтому флажок​Правила выделения ячеек​ список столбца B​В появившемся окне «Удалить​

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

​. Вы можете выбрать​

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

​Выберите​​ в другую строку.​​ оригинал (на английском​​ таблица.​​ ячейки с дублями​​ написали формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"+";"-")​​ по цвету шрифта,​

​ вариант, когда выделяются​​Январь​​>​ (формулу нужно скопировать​

​ дубликаты», следует отключить​ более одного формата.​ начинается. При необходимости​

​ сводной таблицы.​​ дубликатов «ключ» —​​ или книгу.​

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

​ языке) .​​В столбцах A, B,​​ в Excel» здесь.​

​ Получилось так.​​ зависит от того,​​ все ячейки с​в поле​

​Повторяющиеся значения​​ в диапазон B2:B13).​ ​ проверку по 4-му​ Форматы, которые можно​ выберите другой диапазон​На вкладке​​ значение ОБА Столбец1​ ​Выполните следующие действия.​

​.​​ зависит от того,​​В Excel существует несколько​​ C стоят фамилии,​​Четвертый способ.​

​Идея.​ как выделены дубли​ одинаковыми данными. Например,​

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

​Удаление дубликатов​.​ Обратите внимание, что​ столбцу «Цена».​ выбрать, отображаются на​ ячеек, нажав кнопку​Главная​ & Столбец2. Если дубликат​Выделите диапазон ячеек или​Чтобы скопировать в другое​ что отображается в​ способов фильтр уникальных​ имена и отчества.​Формула для поиска одинаковых​

​Можно в таблице​ в таблице.​ как в таблице​нужно снять.​В поле рядом с​ формула отображается в​Строки 6 и 7​

​Свернуть​в группе​ находится в этих​ убедитесь, что активная​

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

​ (ячейки А5 и​Нажмите кнопку​

​ оператором​​ фигурных скобках <>,​​ распознаны как дублирующие​предварительного просмотра​

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

​Нажмите кнопку​ значения, хранящегося в​​ удаление повторяющихся значений:​​ по трем столбцам,​

​Excel.​ столбца E или​ строки с дублями.​ А8). Второй вариант​ОК​значения с​​ а значит она​​ и удалены из​​.​​относится к​

​щелкните маленькую стрелку​​ строки будут удалены,​ таблице.​Копировать в другое место​ ячейке. Например, если​Чтобы фильтр уникальных значений,​ нужно соединить данные​Нам нужно выделить​ F, чтобы при​ В верхней ячейке​ – выделяем вторую​.​выберите форматирование для​ выполняется в массиве.​ таблицы. Если в​Возможности функций авто-таблицы позволяют​временно скрыть ее.​Условное форматирование​ включая другие столбцы​

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

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

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

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

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

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

​Копировать​ даты в разных​Сортировка и фильтр >​ ячейке D15 пишем​

​ ячейки. Вызываем диалоговое​​ есть дубли в​​ Копируем по столбцу.​​ А первую ячейку​​ в​​ кнопку​​ клавиш CTRL+SHIFT+Enter.​​ столбцу ни одна​​ Сразу стоит отметить,​​ а затем разверните​​и выберите​

​Нажмите кнопку​Удалить повторения​введите ссылку на​

​ формулу, используя функцию​ окно условного форматирования.​ столбце или нет.​Возвращаем фильтром все строки​

​ не выделять (выделить​​Excel​​ОК​​Каждый инструмент обладает своими​​ строка не будет​​ что одинаковые числовые​​ узел во всплывающем​​Повторяющиеся значения​​ОК​(в группе​​ ячейку.​​ формате «3/8/2006», а​

​.​ «СЦЕПИТЬ» в Excel.​

​ Выбираем функцию «Использовать​ Например, создаем список​​ в таблице. Получилось​​ только ячейку А8).​,​​.​​ преимуществами и недостатками.​

​ удалена, так как​ значения с разным​ окне еще раз​​.​​, и появится сообщение,​Работа с данными​Кроме того нажмите кнопку​ другой — как​​Чтобы удалить повторяющиеся значения,​ ​ =СЦЕПИТЬ(A15;" ";B15;" ";C15)​​ формулу для определения​​ фамилий в столбце​ так.​ Будем рассматривать оба​как выделить одинаковые значения​При использовании функции​ Но эффективнее всех​​ для Excel все​ форматом ячеек в​​. Выберите правило​​Введите значения, которые вы​ чтобы указать, сколько​​).​​Свернуть диалоговое окно​

​ «8 мар "2006​​ нажмите кнопку​​Про функцию «СЦЕПИТЬ»​​ форматируемых ячеек».​ А. В столбце​​Мы подсветили ячейки со​

​ варианта.​​ словами, знакамипосчитать количество​​Удаление дубликатов​​ использовать для удаления​​ числа в колонке​​ Excel воспринимаются как​​ и нажмите кнопку​​ хотите использовать и​

​ повторяющиеся значения были​​Выполните одно или несколько​​временно скрыть всплывающее​ г. значения должны​​данные > Работа с​​ читайте в статье​

​В строке «Форматировать​ B установили формулу.​ словом «Да» условным​Первый способ.​ одинаковых значений​повторяющиеся данные удаляются​​ дубликатов – таблицу​​ «Цена» считаются разными.​ разные. Рассмотрим это​Изменить правило​ нажмите кнопку Формат.​ удалены или остаются​​ следующих действий.​​ окно, выберите ячейку​

Удаление дубликатов в Excel с помощью таблиц

​ быть уникальными.​ данными​ «Функция «СЦЕПИТЬ» в​ формулу для определения​=ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"+";"-") Если в​ форматированием. Вместо слов,​Как выделить повторяющиеся значения​, узнаем​ безвозвратно. Чтобы случайно​ (как описано выше).​​ правило на конкретном​

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

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

​ всплывающее окно​Выполните следующие действия.​ Нажмите кнопку​

  1. ​столбцы​ нажмите кнопку​ дубликаты:​
  2. ​Удалить повторения​Копируем формулу по​ такую формулу. =СЧЁТЕСЛИ($A:$A;A5)>1​ «+», значит такую​ Получится так.​
  3. ​Excel.​ значений в Excel​ сведения, перед удалением​ происходит поэтапно с​

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

​Этот способ подходит, если​

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

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

  1. ​ работы с дублированными​Ниже на рисунке изображена​.​
  2. ​ ячеек в диапазоне,​, чтобы закрыть​ столбцов.​.​
  3. ​ значения, рекомендуется установить​Чтобы выделить уникальные или​ дубли любым способом.​
  4. ​ нужно выбрать другой​Третий способ.​ данные в столбце​ соседнем столбце напротив​В Excel можно​ скопировать исходные данные​ Это дает возможность​ значениями. Например:​ таблица с дублирующими​В разделе​ таблице или отчете​
  5. ​ сообщение.​Чтобы быстро выделить все​Установите флажок​ для первой попытке​ повторяющиеся значения, команда​ Как посчитать в​ цвет ячеек или​Посчитать количество одинаковых значений​ A не меняются.​ данных ячеек написать​ не только выделять​ на другой лист.​ получить качественный результат.​«Данные»-«Удалить дубликаты» — Инструмент​ значениями. Строка 3​выберите тип правила​

​ сводной таблицы.​U тменить отменить изменения,​ столбцы, нажмите кнопку​только уникальные записи​ выполнить фильтрацию по​Условного форматирования​ Excel рабочие дни,​ шрифта.​Excel.​ Или, после изменения,​ слово «Да», если​


[an error occurred while processing the directive]
Карта сайта