Как в экселе убрать дубли строк. Excel — как удалить дубликаты но оставить уникальные значения? Удалить дубликаты в Excel - Функция Удалить дубликаты в Excel

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

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

Можно, причем очень просто. Для этого есть специальная функция. Предварительно выберите диапазон, где нужно удалять дубликаты. На ленте заходим Данные — Удалить дубликаты (смотрите картинку в начале статьи).

Выбираем первый столбец

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

Очень удобно!

2. Как выделить все дубликаты в Excel?

Уже слышали про ? Здесь оно тоже поможет! Выделяете столбец, в котором надо пометить дубликаты, выбираете в меню Главное — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения…

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

Нажимаете ОК, если не хотите изменять форматирование. Теперь все данные по выбранным условиям подкрасятся.

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

Кстати, если нужно увидеть уникальные, то в окне слева выберите — уникальные.

3. Уникальные значения при помощи сводных таблиц

Признаюсь честно, когда-то я не подозревал о существовании возможности «удалить дубликаты» и пользовался сводными таблицами. Как я это делал? Выделяете таблицу, в которой надо найти уникальные значения — Вставка —

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

В этой статье мы подробно рассмотрим эффективные практики по поиску и удалению дубликатов.

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

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

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

Самый простой способ найти и выделить цветом дубликаты в Excel, это использовать условное форматирование.

Как это сделать:

• Выделим область с данными, в которой нам нужно найти дубликаты:

• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”:

• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены дублирующие значения. Нажмите кнопку “ОК”:

• После этого, в выделенной колонке, будут подсвечены цветом дубликаты:

Подсказка : не забудьте проверить данные вашей таблицы на наличие лишних пробелов. Для этого лучше использовать функцию TRIM (СЖПРОБЕЛЫ).

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

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

• Выделите колонки с данными, в которых нужно найти дубликаты;
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены цветом дубликаты:

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

Поиск дубликатов повторяющихся ячеек и целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:

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

Рассмотрим как найти дубликаты строк:

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

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения повторяющихся строк в таблице сделайте следующие шаги:

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого давайте сделаем следующее:

• Также как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:

=A2&B2&C2&D2

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

• Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
• Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

• В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

• Не забудьте задать формат найденных дублированных строк.

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

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

Выше мы изучили как найти дубликаты и как их выделить цветом. Ниже вы узнаете как их удалить.

Как удалить дубликаты в одном столбце Эксель

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

• Выделите данные;
• Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:

• В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки”, если в выделенном вами диапазоне столбца есть заголовок. Также, убедитесь, что в меню “Колонны” выделен необходимый вам столбец:

• Нажмите “ОК”

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

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

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

Представим, что у нас есть данные продаж как в таблице ниже.

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

Поиск повторяющихся значений

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

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

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

Поиск уникальных значений

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

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

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

Чтобы удалить дубликаты в Excel, существует соответствующая команда меню. Она находится на вкладке «Данные» в блоке операций «Работа с данными». При нажатии на кнопку открывается диалоговое окно. Если перед вызовом команды на листе не было выделено группы ячеек, программа сразу предлагает выделить столбцы, по которым нужно найти и убрать повторения.

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

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

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

Еще один способ удалить дубликаты в Excel - отбор только уникальных значений выделенного диапазона таблицы. Покажем это на примере. Выделяем группу ячеек, по которым нужно убрать повторяющиеся значения, и открываем в программе вкладку «Данные». Здесь находим блок команд «Сортировка и фильтр» и выбираем одну из них - «Дополнительно».

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

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

Получаем в итоге из начальных 27 записей только 19 без повторений. Этим способом можно удалить дубликаты в Excel 2003, тогда как предыдущий появился только в версии программы 2007.

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

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

Нахождение одинаковых элементов не работает в отчетах сводной таблицы.

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

Привет всем! Некоторые сейчас меня поймут, а некоторые нет (кто не так часто работает с текстом). Вообщем, я часто работаю с документами и у меня все-время возникает вопрос, как удалить дубликаты строк…

Дубли бывают везде, например для , очень много программ. А, для того чтобы удалить одинаковые текстовые строки, программ мало. Да вообще их нет! Сколько не искал сейчас покажу 2 программы, через которые можно это делать. И то одна похоже самописная, от автора, у которого тоже походу кончилось терпение их искать)))

1 способ. Как в Эксель удалить дубликаты строк

Этот способ будет первый, т.к. эта программа установлена у большинства пользователей. Она входит в пакет Майкрософта Офиса. Запускаем Эксель и вставляем туда текст, который нужно отсортировать.

Теперь ваш столбец с текстом должен быть выделен. Когда вы его выделите, нажимаем удалить дубликаты.

Появится окно, в котором выбираем нужный столбец. И нажимаем ОК.

Появится окошко с сообщением об удалении дубликатов.

Все, видим что дубли были удалены.

2 способ удаление дубликатов строк

На помощь придет программа, которую я увидел чисто случайно. В неё кстати реализован не только этот функционал. Возможно ей кто-то будет пользоваться и по другому назначению. Скачиваем…

Я специально, написал несколько одинаковых и дублирующихся строк.

Запускаем программу. Выбираем удалить дубликаты и кнопку выбрать и удалить дубли. Выбираем ваш файл.

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

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

Как найти и выделить

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

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

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

Как посчитать

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

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

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

В следующем окне уже указаны ячейки диапазона, маркером отмечаем «На новый лист» и нажимаем «ОК» .

Справой стороны перетаскиваем первые три заголовка в область «Названия строк» , а поле «Код» перетаскиваем в область «Значения» .

В результате получим сводную таблицу без дубликатов, а в поле «Код» будут стоять числа, соответствующие повторяющимся значениям в исходной таблице – сколько раз в ней повторялась данная строка.

Для удобства, выделим все значения в столбце «Сумма по полю Код» , и отсортируем их в порядке убывания.

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

Оценить статью: