Excel удалить дубликаты значений простые способы


Как удалить дубли (повторяющиеся записи) в Excel

Сегодня поговорим о том, как удалить дублирующие записи (дубликаты) в Excel.

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

Простой способ. Кнопка «Удалить дубликаты».

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

Продвинутый способ. Сводная таблица.

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

  1. Добавляем столбцу заголовок.
  2. Выделяем интересующий нас диапазон.
  3. На вкладке «Вставка» нажимаем кнопку «Сводная таблица».
  4. В открывшемся диалоговом окне нажимаем «Ок». Должен создаться новый лист со сводной таблицей.
  5. Переносим заголовок интересующего нас столбца в строки.
  6. Вы получили перечень уникальных записей по выбранному столбцу.

Сегодня мы узнали как удалить дубли в Excel, либо как составить список уникальных записей из массива данных при помощи кнопки «Удалить дубликаты» или Сводной таблицы.

Если у Вас появились вопросы — задавайте их в комментариях к этой статье.

Поиск дублей в Excel

Как в excel сделать поиск одинаковых значений в столбцах?

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

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

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

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

  • Как в excel сделать поиск одинаковых значений в столбцах?

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

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

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

Как в excel сделать поиск одинаковых значений в столбцах?

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

Как в excel сделать поиск одинаковых значений в столбцах?

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

Как в excel сделать поиск одинаковых значений в столбцах?

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

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

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

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

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

Как в excel сделать поиск одинаковых значений в столбцах?

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

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

Как в excel сделать поиск одинаковых значений в столбцах?

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

Как в excel сделать поиск одинаковых значений в столбцах?

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

Как в excel сделать поиск одинаковых значений в столбцах?

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

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

  1. Как в excel сделать поиск одинаковых значений в столбцах?

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

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

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

Заключение

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

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

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

Кликни на звездочку, чтобы оценить его!

Средний рейтинг 3.6 / 5. Итог: 5

Источник: https://kovalev-copyright.ru/metodologicheskie-osnovy-dlya-kopirajterov/poisk-dublej-v-excel.html

Поиск и удаление дубликатов в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке
    Вставка
    в группу
    Таблицы,
    щелкаем по кнопке
    Сводная таблица.

    Создаем сводную таблицу. В поле Название строк

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

    Удаление дубликатов в Excel

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

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

    1. Поиск уникальных значений/Поиск дубликатов.
    2. Получение уникальных значений.
    3. Получение значений, где есть дублирующие записи.

    Метод первый — для Excel 2007 и выше.

    Слава богу, для обладателей версии Excel 2007 и выше можно ни о чем не думать. Начиная с этой версии, появилось штатное средство — «Удалить дубликаты» на вкладке Данные.

    Пользоваться им просто:

    • Встаете на вашу таблицу с дубликатами, нажимаете «Удалить дубликаты«.
    • Появляется окно, где вам нужно выделить те столбцы, по которым у вас могут быть идти совпадения.

    • Результатом будет вот такое окно

    Плюс этого метода — скорость и простота. Надо получить только уникальные значения — решения в два клика, получите и распишитесь.

    Минус в том, что если мы более глубоко работаем над таблицей, то мы не сможем понять, какие данные были дублированы (а соответственно, не определим причину дубляжа), а это порой не менее важнее получения нормального списка.

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

    Организовав данные в виде списка, Вы можете применять к ним различные фильтры. В зависимости от набора данных, который у Вас есть, Вы можете отфильтровать список по одному или нескольким столбцам. Поскольку я использую Office 2010, то мне достаточно выделить верхнюю строку, в которой находятся заголовки, затем перейти на вкладку Data (Данные) и нажать команду Filter (Фильтр). Возле каждого из заголовков появятся направленные вниз треугольные стрелки (иконки выпадающих меню), как на рисунке ниже.

    Если нажать одну из этих стрелок, откроется выпадающее меню фильтра, которое содержит всю информацию по данному столбцу. Выберите любой элемент из этого списка, и Excel отобразит данные в соответствии с Вашим выбором. Это быстрый способ подвести итог или увидеть объём выбранных данных. Вы можете убрать галочку с пункта Select All (Выделить все), а затем выбрать один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные Вами пункты. Так гораздо проще найти дубликаты, если они есть.

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

    В результате работы фильтра, Excel отображает только те строки, в которых содержатся выбранные мной элементы (т.е. людей на чьём компьютере установлены XP и XP Pro). Можно выбрать любую другую комбинацию данных, а если нужно, то даже настроить фильтры сразу в нескольких столбцах.

    Метод второй — расширенный фильтр.

    Этот метод доступен уже и для версии Excel 2003, ниже не проверял, но насколько помню эта версия не сильно прогрессировала по сравнению с 2000й версией.

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

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

    • После этого нажимаете «Ок» и ваши данные отфильтруются или скопируются, в зависимости от того, что вы выбрали. Я буду рассматривать случай фильтрации как видите, данные отфильтрованы — это можно видеть по синим номерам строк и нарушенной нумерации (после 19 идет 21, 20я строка скрыта).
    • Теперь уникальные значения можно выделить цветом или забить в отдельном столбце какой-то признак (я ставлю единицу, так проще потом анализировать).
    • Теперь выбираем команду «Очистить» и у вас сразу видно, где дубликаты — они или не отмечены никаким цветом либо у них пустые поля. Теперь можно с помощью автофильтра спокойно получить список дубликатов или уникальных значений.

    Плюс этого метода в том, что мы имеем выбор — удалять или не удалять дубликаты, а значит, можем работать и анализировать данные.

    И в конце еще один метод (шутка). Просто сделайте сводную таблицу и нужные столбцы закиньте в поле строк. Повторы там не появятся

    Использование расширенного фильтра для удаления дубликатов

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

    в группу
    Сортировка и фильтр
    , щелкните по кнопке
    Дополнительно.

    В появившемся диалоговом окне Расширенный фильтр,

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

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

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

    Ниже на рисунке изображена таблица с дублирующими значениями. Строка 3 содержит тоже значение, что и строка 6. А значение строки 4 = строке 7. Ячейки с числами в дублирующихся строках имеют одинаковые значения и разные форматы. У них отличается количество разрядов после запятой. Выполним 2 разные операции для удаления дубликатов.

    Устранение дубликатов на основе значений колонки с текстом:

    1. Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке:
    2. Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».
    3. В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».

    Строки 6 и 7 распознаны как дублирующие и удалены из таблицы. Если в пункте 2 не отключить проверку по столбцу ни одна строка не будет удалена, так как для Excel все числа в колонке «Цена» считаются разными.

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

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

    Следуйте этой последовательности, чтобы найти и выделить повторяющиеся значения:

    1. Выделите те ячейки, в которых будете искать дубли (статья о том, как выделять – здесь)
    2. Нажмите на ленте Главная – Условное Форматирование – Правила выделения ячеек – Повторяющиеся значения

      Как в excel сделать поиск одинаковых значений в столбцах?

    3. В открывшемся меню выберите в списке, как выделить дубли, или «Пользовательский формат…», чтобы самостоятельно сделать форматирование ячеек

      Как в excel сделать поиск одинаковых значений в столбцах?

    4. Нажмите Ок и принимайте результат

    Мы воспользовались инструментом условного форматирования. Если Вы с ним не знакомы – настоятельно рекомендую прочесть эту статью. Форматирование по условию – это то, что делает за вас огромное количество работы!

    Но как в Excel найти одинаковые значения в столбцах, когда их несколько? То есть, сравнить построчно несколько колонок и выделить абсолютно идентичные наборы. Способ, рассмотренный выше, не подойдет. Можно поступить так:

    1. В свободной колонке с помощью функции СЦЕПИТЬ объединить значения в каждой строке

      Как в excel сделать поиск одинаковых значений в столбцах?

    2. Повторить поиск дубликатов, как было показано ранее

    А у меня всё. С радостью отвечу на ваши вопросы!

    Источник: https://officelegko.com/2019/09/19/kak-najti-povtoryayushhiesya-znacheniya-v-stolbcze-v-excel/

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

    В Excel существуют и другие средства для работы с дублированными значениями. Например:

    1. «Данные»-«Удалить дубликаты» — Инструмент удаляет повторяющиеся строки на листе Excel.
    2. «Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа.

    3. «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом.

    4. Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A.
    5. Формула в массиве: 1;0;1);0));»»)’ class=’formula’>. Формула ищет одинаковые наименования в диапазоне A2:A13 и выводит их в отдельный список столбца B (формулу нужно скопировать в диапазон B2:B13). Обратите внимание, что формула отображается в фигурных скобках , а значит она выполняется в массиве. Поэтому ее нужно вводить комбинацией горячих клавиш CTRL+SHIFT+Enter.

    Каждый инструмент обладает своими преимуществами и недостатками. Но эффективнее всех использовать для удаления дубликатов – таблицу (как описано выше). Там весь процесс происходит поэтапно с максимальным контролем данных. Это дает возможность получить качественный результат.

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

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

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

    Как в excel сделать поиск одинаковых значений в столбцах?

    Задача

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

    Решение

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

    Введем в ячейку B5 формулу массива:

    =ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0)

    );»»)

    • Вместо ENTER нужно нажать CTRL + SHIFT + ENTER.
    • ИсхСписок- это Динамический диапазон (ссылка на исходный список в столбце А).

    Как в excel сделать поиск одинаковых значений в столбцах?

    Скопируйте формулу вниз с помощью Маркера заполнения (размерность списка значений имеющих повторы должна совпадать с размерностью исходного списка).

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

    Как в excel сделать поиск одинаковых значений в столбцах?

    С помощью Условного форматирования в исходном списке можно выделить повторяющиеся значения.

    Тестируем

    1. 1. Добавьте в исходный список название новой компании (в ячейку А20 введите ООО Кристалл)
    2. 2. Список неповторяющихся значений (столбец С) автоматически будет обновлен, чтобы включить новое название
    3. 3.
      Добавьте в исходный список название новой компании еще раз (в ячейку А21 снова введите ООО Кристалл)
    4. 4. Список неповторяющихся значений автоматически будет обновлен, новое название будет исключено

    5.

    Список повторяющихся значений (столбец B) автоматически будет обновлен, чтобы включить новое название.

    Как в excel сделать поиск одинаковых значений в столбцах?

    СОВЕТ:

    Созданный список повторяющихся значений является динамическим, т.е. при добавлении новых значений в исходный список, новый список будет автоматически обновляться. Это очень ресурсоемкая задача и годится для небольших списков 50-100 значений. Если динамический список не нужен, то можно пойти другим путем: см. статью Отбор повторяющихся значений с помощью фильтра.

    Источник: https://excel2.ru/articles/otbor-povtoryayushchihsya-znacheniy-v-ms-excel

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

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

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

    Примечание:

    В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.

    На вкладке Главная

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

    В поле рядом с оператором значения с

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

    Выборочная подсветка или удаление одинаковых значений в Excel

    Способ, может быть, банальный, но действенный. Воспользуемся функцией «Поиск».

    Открывает вкладку «Главная» — раздел «Редактирование» — «Найти и выделить» (CTRL+F).

    В окне в поле «Найти» набираем, что мы ищем. Затем жмем по кнопке «Найти все», нажимаем сочетание клавиш CTRL+A, чтобы выбрать все результаты поиска, и выделяю их цветом. Так же их можно удалить, а не выделять.

    Рейтинг
    ( 1 оценка, среднее 4 из 5 )
    Понравилась статья? Поделиться с друзьями: