Как убрать повторение. Поиск и удаление дубликатов в Microsoft Excel
В данном материале мы очень, очень быстро удалим дубликаты или оставим уникальные записи из списка, кому как больше нравится, отсортируем список, а также, сделаем так, чтобы список с уникальными записями можно было быстро обновлять, если в основном перечне происходят изменения.
Супербыстрый способ удаления дубликатов записей
Если необходимо просто быстро удалить дубликаты из списка или определенного столбца таблицы, то сделать это можно чрезвычайно просто.
Для этого достаточно:
а) отформатировать диапазон как таблицу. Выделяем диапазон, выбираем вкладу «Главная», группу «Стили», «Форматировать как таблицу» и желаемый стиль оформления таблицы, внизу команда, где можно создать свой собственный вариант
б) удалить дубликаты с помощью команды «Удалить дубликаты» в группе «Инструменты» на дополнительной вкладке «Конструктор», которая появится после преобразования диапазона в таблицу
Диалоговое окно удаления дубликатов предельно простое.
После удаления дубликатов, если в списке находились пустые строки, их можно будет убрать с помощью фильтра. Все диапазоны, отформатированные как таблицы у заголовка имеют возможности фильтрации значений.
Для фильтрации, равно как и сортировки списка достаточно воспользоваться выпадающим меню у заголовка нужного столбца.
Результат проделанной работы:
Такой способ подойдет, если нужно быстро убрать дубликаты записей на листе Excel, а сами изменения запечатлеть в исходном диапазоне. Если же исходный диапазон может обновляться, то каждый раз проделывать подобные действия не самый лучший способ избавиться от дубликатов и здесь на помощь придет второй способ удаления дубликатов на листе Excel.
Удаление дубликатов с помощью запросов (Power Query)
В 2016й версии Excel Power Query или создание запросов изначально входит в базовую поставку, а для версий 2013, 2010 нужно будет самостоятельно установить это бесплатную надстройку.
Первый этап удаления дубликатов с помощью Power Query такой же, как и в предыдущем способе – необходимо диапазон отформатировать как таблицу. Дальше на вкладке «Данные» в группе «Скачать и преобразовать» выбрать команду «Из таблицы». Появится окно редактора запросов, в котором, с помощью контекстного меню по заголовку столбца с нумерацией его можно удалить, а по заголовку столбца с ФИО выбрать команду «Удалить повторения», это же можно сделать и с помощью вкладки «Главная» редактора запросов.
С помощью фильтра можно скрыть пустые строки (они здесь помечены как «null») и отсортировать, при необходимости, отобранные значения, потом выбрать команду «Закрыть и загрузить/ Закрыть и загрузить в…»
Выбор именно второй опции позволит выбрать место, когда выгрузить модифицированный список, первая же команда сделает это на новый лист. Конечный результат работы в торого способа
Преимуществом способа удаления дубликатов с помощью Power Query, является не только то, что исходный диапазон остается нетронутым, но и то, что, добавляя новые записи в исходный диапазон, они будут появляться в обработанном, естественно, если эти записи уникальны. Для обновления обработанного списка, нужно вызвать контекстное меню на нем и выбрать «Обновить», либо нажать «Обновить» с ленты интерфейса.
Поиск и удаление вручную повторяющихся значений в таблице Excel, в особенности в больших документах, - весьма рискованное и нецелесообразное занятие. Не исключено, что вы, проверяя зрительно ячейки, можете пропустить дубли. И потом, тратится колоссальное количество времени на удаление каждой ячейки.
В этой статье мы рассмотрим, как можно удалить дубликаты в Excel, используя различные автоматизированные штатные функции.
Совет! Для удобства ознакомления со способами удаления повторяющихся строк, столбцов, значений ячеек, откройте в редакторе черновой шаблон или копию проекта, над которым работаете, чтобы по ошибке в ходе редактирования не потерять ценные данные и не нарушить формат оригинала.
Способ №1
1. Удерживая левую кнопку мышки, обозначьте курсором границы области таблицы (отдельные строки, столбцы), где необходимо убрать повторы. Либо выделите весь проект.
2. В меню редактора перейдите на вкладку «Данные».
3. В блоке «Работа с данными» нажмите кнопку «Удалить дубликаты».
4. В появившейся панели выберите столбцы, в которых нужно убрать одинаковые значения посредством установки меток. Если элементов много, используйте для быстрой настройки удаления опции «Выделить всё» и «Снять выделение». Нажмите «OK».
5. По завершении очистки строк и столбцов таблицы от дублей появится сообщение о том, сколько осталось уникальных значений.
Совет! Повторы сразу после удаления можно восстановить, щёлкнув в левом верхнем углу Excel значок «стрелочку влево».
Способ №2
1. Кликните по редактируемой таблице.
2. На вкладке «Данные», в блоке «Сортировка и фильтр», щёлкните подраздел «Дополнительно».
Если необходимо создать новую таблицу, содержащую только уникальные ячейки исходника:
1. В панели «Расширенный фильтр» клацните радиокнопку «Скопировать результат в другое место».
2. Нажмите кнопку, расположенную в правой части поля «Поместить результат в диапазон».
3. Клацните на свободном рабочем пространстве Excel, куда необходимо будет поместить отфильтрованную таблицу. После щелчка в поле появится код ячейки. Закройте его и перейдите в опции фильтра.
4. Кликните окошко «Только уникальные записи» и нажмите «OK».
5. После выполнения фильтрации в указанном месте появится версия исходной таблицы без повторов.
Чтобы отредактировать документ без создания копий:
- в панели «Расширенный фильтр» установите режим обработки «Фильтровать список на месте»;
- кликом мышки включите надстройку «Только уникальные записи»;
- клацните «OK».
Привет всем! Некоторые сейчас меня поймут, а некоторые нет (кто не так часто работает с текстом). Вообщем, я часто работаю с документами и у меня все-время возникает вопрос, как удалить дубликаты строк…
Дубли бывают везде, например для , очень много программ. А, для того чтобы удалить одинаковые текстовые строки, программ мало. Да вообще их нет! Сколько не искал сейчас покажу 2 программы, через которые можно это делать. И то одна похоже самописная, от автора, у которого тоже походу кончилось терпение их искать)))
1 способ. Как в Эксель удалить дубликаты строк
Этот способ будет первый, т.к. эта программа установлена у большинства пользователей. Она входит в пакет Майкрософта Офиса. Запускаем Эксель и вставляем туда текст, который нужно отсортировать.
Теперь ваш столбец с текстом должен быть выделен. Когда вы его выделите, нажимаем удалить дубликаты.
Появится окно, в котором выбираем нужный столбец. И нажимаем ОК.
Появится окошко с сообщением об удалении дубликатов.
Все, видим что дубли были удалены.
2 способ удаление дубликатов строк
На помощь придет программа, которую я увидел чисто случайно. В неё кстати реализован не только этот функционал. Возможно ей кто-то будет пользоваться и по другому назначению. Скачиваем…
Я специально, написал несколько одинаковых и дублирующихся строк.
Запускаем программу. Выбираем удалить дубликаты и кнопку выбрать и удалить дубли. Выбираем ваш файл.
В Excel есть несколько способов фильтрации уникальных значений, а также удаления повторяющихся значений.
Сведения о фильтрации уникальных значений и удалении повторяющихся значений
Фильтрация уникальных значений и - это две похожие задачи, так как целью является предоставление списка уникальных значений. Тем не менее, при фильтрации уникальных значений вы должны временно скрыть повторяющиеся значения. Однако удаление повторяющихся значений означает безвозвратное удаление повторяющихся значений.
Повторяющееся значение - это одно из значений, для которого все значения по крайней мере одной строки идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не на основе значения, хранящегося в ячейке. Например, если у вас есть одинаковое значение даты в разных ячейках, например "3/8/2006", а другое - "Мар 8, 2006", значения будут уникальными.
Проверка перед удалением дубликатов: Прежде чем удалять повторяющиеся значения, рекомендуется сначала попытаться отфильтровать (или условно отформатировать по - уникальные значения), чтобы подтвердить достижение ожидаемых результатов.
Фильтрация уникальных значений
Чтобы отфильтровать диапазон ячеек или таблицы по месту, выполните указанные ниже действия.
Нажмите кнопку Фильтровать список на месте .
Чтобы скопировать результаты фильтра в другое место, выполните указанные ниже действия.
Уникальные значения из диапазона будут скопированы в новое место.
Удаление повторяющихся значений
Так как вы удаляете данные безвозвратно, рекомендуется скопировать исходный диапазон ячеек или таблицы на другой лист или в другую книгу, прежде чем удалять повторяющиеся значения.
Выполните указанные ниже действия.
Проблемы, возникающие при удалении дубликатов из структурированных или промежуточных данных
Невозможно удалить повторяющиеся значения из структурированных данных структуры или с промежуточными итогами. Для удаления дубликатов необходимо удалить структуру и промежуточные итоги. Дополнительные сведения можно найти в разделе Структурирование списка данных на листе и Удаление промежуточных итогов .
Условное форматирование уникальных и повторяющихся значений
Примечание: Вы не можете условно форматировать поля в области значений отчета сводной таблицы с помощью уникальных или повторяющихся значений.
Быстрое форматирование
Выполните указанные ниже действия.
Расширенное форматирование
Выполните указанные ниже действия.
В Excel в Интернете вы можете удалить повторяющиеся значения.
Удаление повторяющихся значений
При удалении повторяющихся значений единственным эффектом будут значения в диапазоне ячеек или таблице. Другие значения, находящиеся за пределами диапазона ячеек или таблицы, не изменяются и не перемещаются. При удалении дубликатов сохраняется первое вхождение значения в списке, а также удаляются другие идентичные значения.
Важно: Вы всегда можете нажать кнопку отменить , чтобы вернуть данные после удаления дубликатов. С другой стороны, перед удалением повторяющихся значений рекомендуется скопировать исходный диапазон ячеек или таблицы на лист или в другую книгу.
Выполните указанные ниже действия.
Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
На вкладке данные нажмите кнопку Удалить дубликаты .
В диалоговом окне снимите флажок для столбцов, в которых не нужно удалять повторяющиеся значения.
Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то "Key", использованный для поиска дубликатов, - это значение обоих Столбец1 & Столбец2. При обнаружении дубликата в Столбец1 и Столбец2 вся строка будет удалена, включая данные из Столбец3.
Электронные таблицы MS Excel используются для анализа содержащихся в них данных. Информация анализируется в исходном виде, но чаще она требует предварительного редактирования. В статье мы рассмотрим, как избавиться от лишних, повторяющихся данных, если они затрудняют обработку файла.
Поиск повторяющихся значений
Рассмотрим пример. Магазин, занимающийся продажей косметики, регулярно получает прайс-листы от компаний-поставщиков в формате Excel. Предположим для удобства, что наименования товаров во всех компаниях одинаковые. Для того чтобы сделать заказ, мы хотим прежде всего определить, в какой из фирм какую продукцию выгоднее заказывать.
Для этого нужно найти одинаковые записи и сравнить цены в них. Пока мы не будем удалять дубликаты в Excel, а только найдем одни и те же товары для анализа стоимости. Для этого воспользуемся форматированием ячеек по условию. Выделяем столбец с наименованием товаров и открываем панель инструментов «Стили» вкладки «Главная». По кнопке «Условное форматирование» открывается выпадающий список команд, из которых нас интересует пункт «Правила выделения ячеек».
Нужное нам правило - «Повторяющиеся значения». В открывшемся окне указываем цвет, которым будут отмечены повторяющиеся элементы диапазона. После закрашивания ячеек с помощью фильтра можно отобрать только их, отсортировать по наименованию продукции, произвести анализ цен и удалить дубликаты в Excel.
Поиск уникальных значений
Описанный выше способ подходит также и для отбора элементов, встречающихся в единственном числе. Для такого выбора сделайте те же действия, что и в предыдущем пункте. Откройте панель команд «Стили» вкладки «Главная» и нажмите кнопку «Условное форматирование». В списке операций выберите «Правила выделения ячеек».
Но теперь в окне настроек фильтра нужно выбрать из выпадающего списка значение не «Повторяющиеся», а «Уникальные». Программа разметит выбранным цветом только те элементы столбца, которые встречаются в единственном экземпляре.
После слияния нескольких прайс-листов в один файл в таблице осталось много повторяющихся записей, которые нужно удалить. Так как список продукции очень длинный, его обработка вручную займет очень много времени и потребует значительных усилий. Гораздо удобнее воспользоваться опцией, которую предлагает программа.
Чтобы удалить дубликаты в Excel, существует соответствующая команда меню. Она находится на вкладке «Данные» в блоке операций «Работа с данными». При нажатии на кнопку открывается диалоговое окно. Если перед вызовом команды на листе не было выделено группы ячеек, программа сразу предлагает выделить столбцы, по которым нужно найти и убрать повторения.
Если же предварительно выделить поля одной колонки, Excel выведет уточняющее сообщение, в котором необходимо будет выбрать, использовать ли в последующей операции только выделенный диапазон или расширить его.
Затем отмечаете столбцы для поиска дубликатов. Функция удобна тем, что позволяет найти как полные соответствия и удалить дубликаты строк Excel, так и совпадения только по отдельным полям.
Например, в нашем случае мы можем искать повторения лишь по наименованиям, кодам, типу и производителю товаров, зная, что цены и поставщики будут разными. Такая обработка позволит оставить в списке уникальные элементы продукции для составления собственного прайс-листа или каталога.
Еще один способ удалить дубликаты в Excel - отбор только уникальных значений выделенного диапазона таблицы. Покажем это на примере. Выделяем группу ячеек, по которым нужно убрать повторяющиеся значения, и открываем в программе вкладку «Данные». Здесь находим блок команд «Сортировка и фильтр» и выбираем одну из них - «Дополнительно».
В открывшемся окне вводим параметры выбора полей. Если нет необходимости сохранять исходную таблицу, выбирайте вариант «Фильтровать список на месте». Но если работа с ним не закончена, лучше перенести результаты фильтра в другое место.
Выбираем группу полей для обработки и отмечаем ячейку, в которую будут помещены отфильтрованные данные. Для того чтобы получить в результате только уникальные вхождения, устанавливаем флажок «Только уникальные записи».
Получаем в итоге из начальных 27 записей только 19 без повторений. Этим способом можно удалить дубликаты в Excel 2003, тогда как предыдущий появился только в версии программы 2007.
Обратите внимание на некоторые советы перед удалением информации из таблиц. Прежде всего, до выполнения таких действий сделайте копию таблицы и проводите операции с ней. Либо оставьте ее как резервную и выполняйте изменения в оригинале. В противном случае вы можете потерять данные вашего файла или изменить формат листа.
Если исходная таблица содержит группировку, суммирование или необходимо убрать их перед тем, как удалять дубликаты в столбце Excel.
Нахождение одинаковых элементов не работает в отчетах сводной таблицы.
Кроме вышеперечисленных, есть возможность убрать повторяющиеся элементы столбцов с помощью формул, но этот метод достаточно трудоемкий и не имеет смысла в использовании в современных версиях программы.