No Image

Как проверить дубли в excel

СОДЕРЖАНИЕ
3 просмотров
16 декабря 2019

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

Поиск и удаление

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

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

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

  1. Выделяем весь табличный диапазон. Переходим во вкладку «Данные». Жмем на кнопку «Удалить дубликаты». Она располагается на ленте в блоке инструментов «Работа с данными».

Открывается окно удаление дубликатов. Если у вас таблица с шапкой (а в подавляющем большинстве всегда так и есть), то около параметра «Мои данные содержат заголовки» должна стоять галочка. В основном поле окна расположен список столбцов, по которым будет проводиться проверка. Строка будет считаться дублем только в случае, если данные всех столбцов, выделенных галочкой, совпадут. То есть, если вы снимете галочку с названия какого-то столбца, то тем самым расширяете вероятность признания записи повторной. После того, как все требуемые настройки произведены, жмем на кнопку «OK».

  • Excel выполняет процедуру поиска и удаления дубликатов. После её завершения появляется информационное окно, в котором сообщается, сколько повторных значений было удалено и количество оставшихся уникальных записей. Чтобы закрыть данное окно, жмем кнопку «OK».
  • Способ 2: удаление дубликатов в «умной таблице»

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

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

    Находясь во вкладке «Главная» жмем на кнопку «Форматировать как таблицу», расположенную на ленте в блоке инструментов «Стили». В появившемся списке выбираем любой понравившийся стиль.

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

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

    Способ 3: применение сортировки

    Данный способ является не совсем удалением дубликатов, так как сортировка только скрывает повторные записи в таблице.

      Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».

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

  • Открывается окно расширенного фильтра. Устанавливаем в нем галочку напротив параметра «Только уникальные записи». Все остальные настройки оставляем по умолчанию. После этого кликаем по кнопке «OK».
  • После этого, повторяющиеся записи будут скрыты. Но их показ можно в любой момент включить повторным нажатием на кнопку «Фильтр».

    Способ 4: условное форматирование

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

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

  • Открывается окно настройки форматирования. Первый параметр в нём оставляем без изменения – «Повторяющиеся». А вот в параметре выделения можно, как оставить настройки по умолчанию, так и выбрать любой подходящий для вас цвет, после этого жмем на кнопку «OK».
  • После этого произойдет выделение ячеек с повторяющимися значениями. Эти ячейки вы потом при желании сможете удалить вручную стандартным способом.

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

    Способ 5: применение формулы

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

    =ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));"")

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

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

  • Выделяем весь столбец для дубликатов, кроме шапки. Устанавливаем курсор в конец строки формул. Нажимаем на клавиатуре кнопку F2. Затем набираем комбинацию клавиш Ctrl+Shift+Enter. Это обусловлено особенностями применения формул к массивам.
  • После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.

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

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Спросите у 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 – там и смотрите 😉.

    Читайте также:  Как подключить usb модем к планшету android

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

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

    ​Смотрите также​ там есть группа​ ищет сколько раз​ to format)​ и отчества одновременно.​Выбираем из выпадающего​ имеется длинный список​ первом столбце меняются​ Excel".​В столбце F​ строки с дублями.​

    ​ только ячейку А8).​ значений в Excel​ выбрали диапазон​

    ​ встречающиеся трижды:​​ по этой ссылке,​ не потерять необходимые​В некоторых случаях повторяющиеся​ "Сервис", а в​

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

    ​ Будем рассматривать оба​, т.д.​​A1:C10​​Сперва удалите предыдущее правило​ чтобы узнать, как​ сведения, перед удалением​ данные могут быть​​ ней команда "Удалить​​ встречается в столбце​

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

    ​ количества совпадений и​​ добавить дополнительный служебный​​Формула (Formula)​ и мы предполагаем,​ в зависимости от​ в ячейках с​ Получилось так.​ отфильтрованного столбца B​ варианта.​

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

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

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

    ​Выделите диапазон​Выделите диапазон​ скопировать исходные данные​ они усложняют понимание​ из столбцов и​

    ​ количество повторений больше​​ помощью кнопки​​ можно скрыть) с​​ проверку:​​ этого списка повторяются​

    ​ дубли.​​ удалить их, смотрите​​Можно в таблице​

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

    ​ Копируем по столбцу.​Как выделить повторяющиеся значения​ и удалять дублирующие​ ячейки. Таким образом,​A1:C10​A1:C10​ на другой лист.​

    1. ​ данных. Используйте условное​​ строк. Эта команда​​ 1, т. е.​
    2. ​Формат (Format)​​ текстовой функцией СЦЕПИТЬ​​=СЧЁТЕСЛИ($A:$A;A2)>1​​ более 1 раза.​​Пятый способ.​​ в статье «Как​​ использовать формулу из​Возвращаем фильтром все строки​ в​​ данные, но и​​ ячейка​
    3. ​.​.​​Выделите диапазон ячеек с​​ форматирование для поиска​​ позволяет выбрать те​ у элемента есть​

    ​- все, как​​ (CONCATENATE), чтобы собрать​в английском Excel это​ Хотелось бы видеть​​Как найти повторяющиеся строки​​ сложить и удалить​​ столбца E или​​ в таблице. Получилось​Excel.​ работать с ними​

    ​A2​На вкладке​На вкладке​ повторяющимися значениями, который​ и выделения повторяющихся​ столбцы, в которых​ дубликаты, то срабатывает​ в Способе 2:​ ФИО в одну​

    1. ​ будет соответственно =COUNTIF($A:$A;A2)>1​ эти повторы явно,​
    2. ​ в​​ ячейки с дублями​​ F, чтобы при​
    3. ​ так.​​Нам нужно в​​ – посчитать дубли​​содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка​​Главная​​Главная​​ нужно удалить.​ данных. Это позволит​
    4. ​ есть повторяющиеся данные.​​ заливка ячейки. Для​Starbuck​​ ячейку:​Эта простая функция ищет​ т.е. подсветить дублирующие​
    5. ​Excel.​

    ​ в Excel» здесь.​
    ​ заполнении соседнего столбца​

    ​Мы подсветили ячейки со​ соседнем столбце напротив​​ перед удалением, обозначить​​A3​​(Home) выберите команду​(Home) нажмите​

    • ​ вам просматривать повторения​ Например, у вас​​ выбора цвета выделения​​: Автофильтр по колонкам​Имея такой столбец мы,​
    • ​ сколько раз содержимое​ ячейки цветом, например​
    • ​Нужно сравнить и​Четвертый способ.​​ было сразу видно,​​ словом «Да» условным​ данных ячеек написать​​ дубли словами, числами,​​:​Условное форматирование​Условное форматирование​Перед попыткой удаления​​ и удалять их​​ есть столбец ФИО,​​ в окне Условное​​Зибин​

    ​ фактически, сводим задачу​

  • ​ текущей ячейки встречается​ так: ​ выделить данные по​​Формула для поиска одинаковых​​ есть дубли в​
  • ​ форматированием. Вместо слов,​​ слово «Да», если​ знаками, найти повторяющиеся​=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.​>​>​ повторений удалите все​ по мере необходимости.​

    ​ Должность, Примечание. Вы​
    ​ форматирование нажмите кнопку​

    ​: а версия Excel?​ к предыдущему способу.​
    ​ в столбце А.​
    ​В последних версиях Excel​

    ​ трем столбцам сразу.​

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

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

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

    Читайте также:  Как оформить покупку на алиэкспресс пошагово

    Способ 1. Если у вас Excel 2007 или новее

    ​Если у вас​в Excel 2007 и​ нужно искать и​В появившемся затем окне​

    ​ читайте в статье​ цвет ячеек или​ в ячейке их​​Копируем формулу по​​ (на картинке обведен​​Есть два варианта​​ командой сайта office-guru.ru​​Результат: Excel выделил значения,​Повторяющиеся​ ценах, которые нужно​.​​ записи, а повторы​

    ​ если у вас​ Excel 2003 и​ новее – нажать​ подсвечивать повторы не​

    Способ 2. Если у вас Excel 2003 и старше

    ​ можно задать желаемое​ «Функция «СЦЕПИТЬ» в​ шрифта.​ количество.​ столбцу. Получится так.​ красным цветом). Слово​ выделять ячейки с​​Источник: http://www.excel-easy.com/examples/find-duplicates.html​ ​ встречающиеся трижды.​​(Duplicate) пункт​​ сохранить.​В поле рядом с​​ будут скрыты. Теперь​​ Эксель 2007 и​ старше​

    ​ по одному столбцу,​ форматирование (заливку, цвет​

    ​ Excel».​Нажимаем «ОК». Все ячейки​В ячейке G5​Обратите внимание​ скопируется вниз по​ одинаковыми данными. Первый​Перевел: Антон Андронов​Пояснение:​Уникальные​Поэтому флажок​ оператором​​ надо выделить уникальные​​ выше, то выделите​​Выделяем весь список​​Главная (Home)​ а по нескольким.​​ шрифта и т.д.)​​Копируем формулу по​

    Способ 3. Если много столбцов

    ​ с повторяющимися данными​ пишем такую формулу.​, что такое выделение​ столбцу до последней​ вариант, когда выделяются​Автор: Антон Андронов​Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество​(Unique), то Excel​Январь​

    ​значения с​ записи, как-то их​ ваш диапазон, нажмите​ (для примера -​кнопку​ Например, имеется вот​В более древних версиях​

    ​ столбцу. Теперь выделяем​ окрасились.​ =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Копируем по​ дублей, выделяет словом​ заполненной ячейки таблицы.​ все ячейки с​Рассмотрим,​ значений в диапазоне​

    ​ выделит только уникальные​в поле​выберите форматирование для​ отметить, я обычно​ на вкладке "Вставка"​ диапазон А2:A10), и​Условное форматирование – Создать​ такая таблица с​ Excel придется чуточку​ дубли любым способом.​Идея.​

    • ​ столбцу. Получился счетчик​ «Да» следующие повторы​Теперь в столбце​​ одинаковыми данными. Например,​как найти повторяющиеся значения​A1:C10​ имена.​
    • ​Удаление дубликатов​ применения к повторяющимся​ в пустой столбец​​ команду "Таблица". Так​​ идем в меню​​ правило (Conditional Formatting​ ФИО в трех​ сложнее. Выделяем весь​​ Как посчитать в​Можно в условном​​ повторов.​ в ячейках, кроме​ A отфильтруем данные​ как в таблице​ в​

    ​, которые равны значению​Как видите, Excel выделяет​нужно снять.​ значениям и нажмите​​ ставлю что-нибудь, единицу​​ вы сделаете из​ Формат – Условное​

    Повторы в Excel Как в MS Excel определить повторы в одном столбце? Измучился уже.

    ​ – New Rule)​​ колонках:​

    ​ список (в нашем​​ Excel рабочие дни,​
    ​ форматировании установить белый​Изменим данные в столбце​ первой ячейки.​
    ​ – «Фильтр по​ (ячейки А5 и​Excel​ в ячейке A1.​ дубликаты (Juliet, Delta),​Нажмите кнопку​ кнопку​
    ​ например и продлеваю.​ своих данных "умную"​ форматирование. Выбираем из​и выбрать тип​Задача все та же​
    ​ примере – диапазон​ прибавить к дате​ цвет заливки и​
    ​ А для проверки.​Слова в этой​ цвету ячейки». Можно​ А8). Второй вариант​,​Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует​ значения, встречающиеся трижды​ОК​ОК​
    ​ После отмены фильтра​
    ​ таблицу. А умная​ выпадающего списка вариант​ правила​ – подсветить совпадающие​ А2:A10), и идем​ дни, т.д., смотрите​ шрифта. Получится так.​ Получилось так.​ формуле можно писать​ по цвету шрифта,​ – выделяем вторую​как выделить одинаковые значения​ ячейку.​ (Sierra), четырежды (если​.​

    ​.​​ (команда "Очистить") у​ таблица может сама​ условия Формула и​Использовать формулу для опеределения​ ФИО, имея ввиду​ в меню​
    ​ в статье "Как​Первые ячейки остались видны,​Ещё один способ подсчета​ любые или числа,​ зависит от того,​
    ​ и следующие ячейки​ словами, знакамипосчитать количество​Поскольку прежде, чем нажать​ есть) и т.д.​Этот пример научит вас​При использовании функции​ вас ваши повторы​ удалить ваши дубликаты,​ вводим такую проверку:​ форматируемых ячеек (Use​ совпадение сразу по​Формат – Условное форматирование​ посчитать рабочие дни​ а последующие повторы​ дублей описан в​ знаки. Например, в​ как выделены дубли​ в одинаковыми данными.​ одинаковых значений​ кнопку​ Следуйте инструкции ниже,​ находить дубликаты в​Удаление дубликатов​ будут пустые. Так​ для этого переходите​=СЧЁТЕСЛИ ($A:$A;A2)>1​ a formula to​ всем трем столбцам​(Format – Conditional Formatting)​ в Excel".​ не видны. При​ статье "Как удалить​ столбце E написали​ в таблице.​ А первую ячейку​
    ​, узнаем​Условное форматирование​ чтобы выделить только​ Excel с помощью​повторяющиеся данные удаляются​ вы их и​ на саму таблицу,​Эта простая функция​ determine which cell​ – имени, фамилии​.​Допустим, что у нас​ изменении данных в​ повторяющиеся значения в​ такую формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"Повторно";"Впервые")​В таблице остались две​ не выделять (выделить​формулу для поиска одинаковых​(Conditional Formatting), мы​ те значения, которые​ условного форматирования. Перейдите​ безвозвратно. Чтобы случайно​ найдете.​

    Комментировать
    3 просмотров
    Комментариев нет, будьте первым кто его оставит

    Это интересно
    No Image Компьютеры
    0 комментариев
    No Image Компьютеры
    0 комментариев
    No Image Компьютеры
    0 комментариев
    Adblock detector