Как удалить дубликаты в Excel. Повторяющиеся значения в Excel: как удалить дубликаты, или просто найти и выделить Как убрать повторы в эксель
При работе с таблицей или базой данных с большим количеством информации возможна ситуация, когда некоторые строки повторяются. Это ещё больше увеличивает массив данных. К тому же, при наличии дубликатов возможен некорректный подсчет результатов в формулах. Давайте разберемся, как в программе Microsoft Excel отыскать и удалить повторяющиеся строки.
Найти и удалить значения таблицы, которые дублируются, возможно разными способами. В каждом из этих вариантов поиск и ликвидация дубликатов – это звенья одного процесса.
Способ 1: простое удаление повторяющихся строк
Проще всего удалить дубликаты – это воспользоваться специальной кнопкой на ленте, предназначенной для этих целей.
Способ 2: удаление дубликатов в «умной таблице»
Дубликаты можно удалить из диапазона ячеек, создав умную таблицу.
Этот способ является наиболее универсальным и функциональным из всех описанных в данной статье.
Способ 3: применение сортировки
Данный способ является не совсем удалением дубликатов, так как сортировка только скрывает повторные записи в таблице.
После этого, повторяющиеся записи будут скрыты. Но их показ можно в любой момент включить повторным нажатием на кнопку «Фильтр» .
Способ 4: условное форматирование
Найти повторяющиеся ячейки можно также при помощи условного форматирования таблицы. Правда, удалять их придется другим инструментом.
После этого произойдет выделение ячеек с повторяющимися значениями. Эти ячейки вы потом при желании сможете удалить вручную стандартным способом.
Внимание! Поиск дублей с применением условного форматирования производится не по строке в целом, а по каждой ячейке в частности, поэтому не для всех случаев он является подходящим.
Способ 5: применение формулы
Кроме того, найти дубликаты можно применив формулу с использованием сразу нескольких функций. С её помощью можно производить поиск дубликатов по конкретному столбцу. Общий вид данной формулы будет выглядеть следующим образом:
ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));"")
После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.
Но, данный способ все-таки слишком сложен для большинства пользователей. К тому же, он предполагает только поиск дублей, но не их удаление. Поэтому рекомендуется применять более простые и функциональные решения, описанные ранее.
Как видим, в Экселе есть множество инструментов предназначенных для поиска и удаления дублей. У каждого из них есть свои особенности. Например, условное форматирование предполагает поиск дублей только по каждой ячейке в отдельности. К тому же, не все инструменты могут не только искать, но и удалять повторяющиеся значения. Наиболее универсальный вариант – это создание «умной таблицы». При использовании данного способа можно максимально точно и удобно настроить поиск дубликатов. К тому же, их удаление происходит моментально.
Распространенный вопрос: как найти и удалить дубликаты в Excel. Предположим, вы выгрузили месячный отчет из вашей учетной системы, но в итоге вам нужно понять какие контрагенты вообще взаимодействовали с компанией за этот период — составить список контрагентов без повтарений. Как отобрать уникальные значения?
Можно ли удалить задвоеные, затроенные и так далее значения в Excel по нескольким столбцам?
Можно, причем очень просто. Для этого есть специальная функция. Предварительно выберите диапазон, где нужно удалять дубликаты. На ленте заходим Данные — Удалить дубликаты (смотрите картинку в начале статьи).
Выбираем первый столбец
При этом важно понимать, что если вы выберите только первый столбец, то все данные в невыбранных столбцах удалятся в случае неуникальности.
Очень удобно!
2. Как выделить все дубликаты в Excel?
Уже слышали про ? Здесь оно тоже поможет! Выделяете столбец, в котором надо пометить дубликаты, выбираете в меню Главное — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения…
В открывшемся окне Повторяющиеся значения выберите, какие ячейки выделяем (уникальные или повторяющиеся), а так же формат выделения, либо из преложенных, либо создайте Пользовательский формат. Предустановленным форматом будет красная заливка и красный текст.
Нажимаете ОК, если не хотите изменять форматирование. Теперь все данные по выбранным условиям подкрасятся.
Отмечу, что инструмент применяется только для выбранного одного (!) столбца.
Кстати, если нужно увидеть уникальные, то в окне слева выберите — уникальные.
3. Уникальные значения при помощи сводных таблиц
Признаюсь честно, когда-то я не подозревал о существовании возможности «удалить дубликаты» и пользовался сводными таблицами. Как я это делал? Выделяете таблицу, в которой надо найти уникальные значения — Вставка —
Доброго времени суток!
С популяризацией компьютеров за последние 10 лет - происходит и популяризация создания отчетов (документов) в программе Excel.
И в любом относительно большом документе встречаются повторяющиеся строки, особенно, если вы его собрали из несколько других таблиц. Эти дубли могут очень мешать дальнейшему редактированию таблицы, а потому их нужно либо выделить, либо вообще удалить...
Собственно, ко мне ни раз и ни два обращались с подобными вопросами, и я решил оформить работу с дубликатами в отдельную небольшую статью (которую вы сейчас читаете). Приведу самые часто-встречаемые задачи, и покажу их решение.
Примечание : все примеры ниже будут представлены в Office 2016 (актуально также для Office 2013, 2010, 2007). Рекомендую всегда использовать относительно новые версии Office: в них и быстрее работать, и проще ☻.
Простое авто-удаление дублей строк
Представим одну из самых популярных задач: есть список строк с ФИО людей, в строках много совпадений, нужно оставить только уникальные значения (кстати, это просто пример, все совпадения с реальными людьми - случайны).
Задачка проиллюстрирована на скриншоте ниже (это пример: понятно, что 2 строки можно удалить и руками, но в реальности же строк может быть и 2000, и 20000, где "руки" будут бессильны...!).
Чтобы в Excel удалить повторяющиеся строки, необходимо:
Копирование уникальных строк (данных) в новое место
Если вы не хотите трогать и изменять текущие данные (текущую таблицу) - то можно просто скопировать только уникальные строки (значения) из нее в новую таблицу. Это очень удобно, когда вы хотите наглядно сравнить, что было, и что получилось (а также волнуетесь за старые данные, которые еще могут пригодиться).
Как это делается:
Поиск и выделение повторяющихся значений
В тех случаях, когда вам не нужно удалить дубли или копировать уникальные значения в новое место - можно просто найти и подсветить их (причем, выделить можно как уникальные значения, так и повторяющиеся). Обычно, это необходимо делать, когда предстоит дальнейшая корректировка строк (их удобно и быстро можно найти).
Работая с Microsoft Excel очень часто возникает ситуация, когда необходимо удалить повторяющиеся строки. Этот процесс может превратиться в бессмысленную, монотонную и трудоемкую работу, однако, существует ряд способов упростить задачу. Сегодня мы разберем несколько удобных методов нахождения и удаления повторяющихся строк в Excel. В качестве примера возьмем следующую таблицу с данными:
Вариант 1: Команда Удалить дубликаты в Excel
Microsoft Excel располагает встроенным инструментом, который позволяет находить и удалять дубликаты строк. Начнем с поиска повторяющихся строк. Для этого выберите любую ячейку в таблице, а затем выделите всю таблицу, нажав Ctrl+A .
Перейдите на вкладку Date (Данные), а затем нажмите команду Remove Duplicates (Удалить дубликаты), как показано ниже.
Появится небольшое диалоговое окно Remove Duplicates (Удалить дубликаты). Можно заметить, что выделение первой строки снимается автоматически. Причиной тому является флажок, установленный в пункте My data has headers (Мои данные содержат заголовки).
В нашем примере нет заголовков, поскольку таблица начинается с 1-й строки. Поэтому снимем флажок. Сделав это, Вы заметите, что вся таблица снова выделена, а раздел Columns (Колонны) изменится с dulpicate на Column A , B и С .
Теперь, когда выделена вся таблица, нажмите OK , чтобы удалить дубликаты. В нашем случае все строки с повторяющимися данными удалятся, за исключением одной. Вся информация об удалении отобразится во всплывающем диалоговом окне.
Вариант 2: Расширенный фильтр
Вторым инструментом Excel, с помощью которого можно найти и удалить дубликаты, является Расширенный фильтр . Этот метод также применим к Excel 2003. Чтобы применить фильтр, необходимо выделить всю таблицу, как и прежде, воспользовавшись сочетанием клавиш Ctrl+A .
Затем перейдите на вкладку Data (Данные), в группе команд Sort & Filter (Сортировка и фильтр) нажмите команду Advanced (Дополнительно), как показано ниже. Если Вы используете Excel 2003, вызовите раскрывающееся меню Data (Данные), выберите Filters (Фильтры), а затем Advanced Filters (Расширенные фильтры).
Теперь необходимо установить флажок Unique records only (Только уникальные записи).
После нажатия OK в документе будут удалены все дубликаты, за исключением одной записи. В нашем примере осталось две записи, поскольку первый дубликат был обнаружен в строке 1. Этот метод автоматически определяет заголовки в таблице. Если Вы хотите удалить первую строку, Вам придется удалить ее вручную. Когда в 1-й строке будут заголовки, а не дубликаты, останется только одна копия существующих повторов.
Вариант 3: Замена
Этот метод удобно применять, когда необходимо найти повторяющиеся строки в небольших таблицах. Мы будем использовать инструмент Find and Replace (Поиск и замена), который встроен во все продукты Microsoft Office. Для начала необходимо открыть таблицу Excel, с которой планируется работать.
Открыв таблицу, выберите ячейку, содержимое которой требуется найти и заменить, и скопируйте ее. Для этого выделите нужную ячейку и нажмите сочетание клавиш Ctrl+C .
Скопировав слово, которое необходимо найти, воспользуйтесь сочетанием Ctrl+H , чтобы вызвать диалоговое окно Find and Replace (Поиск и замена). Вставьте скопированное слово с поле Найти , нажав Ctrl+V .
Нажмите кнопку Options (Параметры), чтобы открыть дополнительный список опций. Установите флажок у пункта Match entire cell contents (Ячейка целиком). Это необходимо сделать, поскольку в некоторых ячейках искомые слова находятся вместе с другими словами. Если не выбрать эту опцию, можно непреднамеренно удалить ячейки, которые требуется оставить. Убедитесь, что все остальные настройки соответствуют указанным на рисунке ниже.
Теперь необходимо ввести значение в поле Replace with (Заменить на). В данном примере мы воспользуемся цифрой 1 . Введя нужное значение, нажмите Replace All (Заменить все).
Можно заметить, что все значения duplicate в ячейках таблицы, будут заменены на 1 . Мы использовали значение 1 , так как оно небольшое и выделяется в тексте. Теперь Вы можете визуально определить строки, которые имеют повторяющиеся значения.
Чтобы оставить один из дубликатов, достаточно вставить исходный текст обратно в строку, которая была заменена. В нашем случае мы восстановим значения в 1-й строке таблицы.
Определив строки с повторяющимся содержимым, поочередно выделите их, удерживая клавишу Ctrl .
Выбрав все строки, которые необходимо удалить, щелкните правой кнопкой мыши по заголовку любой из выделенных строк и в контекстном меню нажмите Delete (Удалить). Не стоит нажимать клавишу Delete на клавиатуре, поскольку в этом случае удалится только содержимое ячеек, а не строки полностью.
Сделав это, Вы можете заметить, что все оставшиеся строки имеют уникальные значения.
В данном материале мы очень, очень быстро удалим дубликаты или оставим уникальные записи из списка, кому как больше нравится, отсортируем список, а также, сделаем так, чтобы список с уникальными записями можно было быстро обновлять, если в основном перечне происходят изменения.
Супербыстрый способ удаления дубликатов записей
Если необходимо просто быстро удалить дубликаты из списка или определенного столбца таблицы, то сделать это можно чрезвычайно просто.
Для этого достаточно:
а) отформатировать диапазон как таблицу. Выделяем диапазон, выбираем вкладу «Главная», группу «Стили», «Форматировать как таблицу» и желаемый стиль оформления таблицы, внизу команда, где можно создать свой собственный вариант
б) удалить дубликаты с помощью команды «Удалить дубликаты» в группе «Инструменты» на дополнительной вкладке «Конструктор», которая появится после преобразования диапазона в таблицу
Диалоговое окно удаления дубликатов предельно простое.
После удаления дубликатов, если в списке находились пустые строки, их можно будет убрать с помощью фильтра. Все диапазоны, отформатированные как таблицы у заголовка имеют возможности фильтрации значений.
Для фильтрации, равно как и сортировки списка достаточно воспользоваться выпадающим меню у заголовка нужного столбца.
Результат проделанной работы:
Такой способ подойдет, если нужно быстро убрать дубликаты записей на листе Excel, а сами изменения запечатлеть в исходном диапазоне. Если же исходный диапазон может обновляться, то каждый раз проделывать подобные действия не самый лучший способ избавиться от дубликатов и здесь на помощь придет второй способ удаления дубликатов на листе Excel.
Удаление дубликатов с помощью запросов (Power Query)
В 2016й версии Excel Power Query или создание запросов изначально входит в базовую поставку, а для версий 2013, 2010 нужно будет самостоятельно установить это бесплатную надстройку.
Первый этап удаления дубликатов с помощью Power Query такой же, как и в предыдущем способе – необходимо диапазон отформатировать как таблицу. Дальше на вкладке «Данные» в группе «Скачать и преобразовать» выбрать команду «Из таблицы». Появится окно редактора запросов, в котором, с помощью контекстного меню по заголовку столбца с нумерацией его можно удалить, а по заголовку столбца с ФИО выбрать команду «Удалить повторения», это же можно сделать и с помощью вкладки «Главная» редактора запросов.
С помощью фильтра можно скрыть пустые строки (они здесь помечены как «null») и отсортировать, при необходимости, отобранные значения, потом выбрать команду «Закрыть и загрузить/ Закрыть и загрузить в…»
Выбор именно второй опции позволит выбрать место, когда выгрузить модифицированный список, первая же команда сделает это на новый лист. Конечный результат работы в торого способа
Преимуществом способа удаления дубликатов с помощью Power Query, является не только то, что исходный диапазон остается нетронутым, но и то, что, добавляя новые записи в исходный диапазон, они будут появляться в обработанном, естественно, если эти записи уникальны. Для обновления обработанного списка, нужно вызвать контекстное меню на нем и выбрать «Обновить», либо нажать «Обновить» с ленты интерфейса.