Как оставить только повторяющиеся значения в excel
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
В сегодняшних Excel файлах дубликаты встречаются повсеместно. К примеру, когда вы создаете составную таблицу из других таблиц, вы можете обнаружить в ней повторяющиеся значения, или в файле с общим доступом внесли одинаковые данные два разных пользователя, что привело к задвоению и т.д. Дубликаты могут возникнуть в одном столбце, в нескольких столбцах или даже во всем листе. В Microsoft Excel реализовано несколько инструментов поиска, выделения и, при необходимости, удаления повторяющихся значений. Ниже описаны основные методики определения дубликатов в Excel.
1. Удаление повторяющихся значений в Excel (2007+)
Предположим, у вас имеется таблица, состоящая из трех столбцов, в которой присутствуют одинаковые записи и вам необходимо избавится от них. Выделяем область таблицы, в которой хотите удалить повторяющиеся значения. Вы можете выделить один или несколько столбцов, или всю таблицу целиком. Переходим по вкладке Данные в группу Работа с данными, щелкаем по кнопке Удалить дубликаты.
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.
Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.
Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице. Если вы выделили не все столбцы для определения дубликатов, строки с повторяющимися значениями также будут удалены.
2. Использование расширенного фильтра для удаления дубликатов
Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.
В появившемся диалоговом окне Расширенный фильтр, необходимо установить переключатель в положение скопировать результат в другое место, в поле Исходный диапазон указать диапазон, в котором находится таблица, в поле Поместить результат в диапазон указать верхнюю левую ячейку будущей отфильтрованной таблицы и установить маркер Только уникальные значения. Щелкаем ОК.
На месте, указанном для размещения результатов работы расширенного фильтра, будет создана еще одна таблица, но уже с отфильтрованными, по уникальным значениям, данными.
3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)
Выделяем таблицу, в которой необходимо обнаружить повторяющиеся значения. Переходим по вкладке Главная в группу Стили, выбираем Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.
В появившемся диалоговом окне Повторяющиеся значения, необходимо выбрать формат выделения дубликатов. У меня по умолчанию установлено светло-красная заливка и темно-красный цвет текста. Обратите внимание, в данном случае Excel будет сравнивать на уникальность не всю строку таблицы, а лишь ячейку столбца, поэтому если у вас имеются повторяющиеся значения только в одном столбце, Excel отформатирует их тоже. На примере вы можете увидеть, как Excel залил некоторые ячейки третьего столбца с именами, хотя вся строка данной ячейки таблицы уникальна.
4. Использование сводных таблиц для определения повторяющихся значений
Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названием Счетчик, и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке Вставка в группу Таблицы, щелкаем по кнопке Сводная таблица.
Создаем сводную таблицу. В поле Название строк помещаем три первых столбца, в поле Значения помещаем столбец со счетчиком. В созданной сводной таблице, записи со значением больше единицы будут дубликатами, само значение будет означать количество повторяющихся значений. Для большей наглядности, можно отсортировать таблицу по столбцу Счетчик, чтобы сгруппировать дубликаты.
В Excel есть несколько способов отфильтровать уникальные значения или удалить повторяющиеся значения:
Чтобы выделить уникальные или повторяющиеся значения, используйте команду Условное форматирование в группе Стиль на вкладке Главная.
Узнайте, как фильтровать уникальные значения или удалять повторяющиеся значения.Фильтрация уникальных значений и удаление повторяюющихся значений — две похожие задачи, так как их цель — представить список уникальных значений. Однако существует критическое различие: при фильтрации уникальных значений повторяющиеся значения скрываются только временно. Однако удаление повторяюющихся значений означает, что повторяющиеся значения удаляются окончательно.
Повторяютая строка — это значение, в котором все значения хотя бы в одной строке совпадают со всеми значениями в другой строке. Сравнение повторяюющихся значений зависит от того, что отображается в ячейке, а не от значения, хранимого в ячейке. Например, если в разных ячейках есть одно и то же значение даты в формате "08.03.2006", а в другом — "8 марта 2006 г.", значения будут уникальными.
Прежде чем удалять дубликаты, проверьте: Прежде чем удалять повторяющиеся значения, сначала попробуйте отфильтровать уникальные значения (или отформатировать их с условием), чтобы достичь нужного результата.
Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
Щелкните > дополнительные данные (в группе Фильтр & сортировки).
Во всплывающее окно Расширенный фильтр сделайте следующее:
Чтобы отфильтровать диапазон ячеек или таблицу на месте:
Щелкните Фильтровать список на месте.
Чтобы скопировать результаты фильтра в другое место:
В поле Копировать в введите ссылку на ячейку.
Вы также можете нажать кнопку Свернуть , чтобы временно скрыть всплывающее окно, выбрать ячейку на этом сайте и нажать кнопку Развернуть .
Проверьте только уникальные записии нажмите кнопку ОК.
Уникальные значения из диапазона копируются в новое место.
При удалите повторяющиеся значения, только на значения в диапазоне ячеек или таблице. Другие значения за пределами диапазона ячеек или таблицы не изменяются и не перемещаются. При удалении дубликатов первое вхождение значения в списке будет сохранено, а другие одинаковые значения будут удалены.
Так как данные удаляются окончательно, перед удалением повторяюющихся значений лучше скопировать исходный диапазон ячеек или таблицу на другой.
Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
На вкладке Данные нажмите кнопку Удалить дубликаты (в группе Инструменты для работы с данными).
Выполните одно или несколько из указанных ниже действий.
В области Столбцывыберите один или несколько столбцов.
Чтобы быстро выбрать все столбцы, нажмите кнопку Выбрать все.
Чтобы быстро очистить все столбцы, нажмите кнопку Отклоните все.
Если диапазон ячеек или таблицы содержит много столбцов и нужно выбрать только несколько столбцов, вам может быть проще нажать кнопку Отобрать все,а затем в столбцах выберите эти столбцы.
Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключом, используемым для поиска дубликатов, будет значение BOTH Column1 & Column2. Если в этих столбцах найдено повторяющиеся записи, удаляется вся строка, включая другие столбцы в таблице или диапазоне.
Чтобы отменить изменение, нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).
Проблемы с удалением дубликатов из структурных или вычитающихся данныхПовторяющиеся значения невозможно удалить из структурных данных или с суммами. Чтобы удалить дубликаты, необходимо удалить структуру и подытогов. Дополнительные сведения см. в таблицах Структурная схема данных на листе и Удаление подытогов.
Условное форматирование уникальных или повторяюных значенийПримечание: Нельзя условно отформатировать поля в области значений отчета отчетов данных по уникальным или повторяемым значениям.
Быстрое форматирование
Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стиль щелкните маленькую стрелку для условного форматирования ,а затем выберите правила выделения ячеек ищелкните Повторяющиеся значения.
Введите нужные значения и выберите формат.
Расширенное форматирование
Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стили щелкните стрелку для команды Условное форматирование ивыберите управление правилами, чтобы отобразить всплывающее окно Диспетчер правил условного форматирования.
Выполните одно из следующих действий:
Чтобы добавить условное форматирование, нажмите кнопку Новое правило, чтобы отобразить всплывающее окно Новое правило форматирования.
Чтобы изменить условное форматирование, начните с того, что в списке Показать правила форматирования для выбран соответствующий лист или таблица. При необходимости выберите другой диапазон ячеек, нажав кнопку Свернуть во всплывающее окно Применяется к временно скрыть его. Выберите новый диапазон ячеек на этом сайте, а затем снова разширив всплывающее окно, . Выберите правило и нажмите кнопку Изменить правило, чтобы отобразить всплывающее окно Изменение правила форматирования.
В группе Выберите тип правила выберите параметр Форматировать только уникальные или повторяющиеся значения.
В списке Форматировать все выберите изменить описание правила, выберите уникальный или дубликат.
Выберите формат числа, шрифта, границы или заливки, который вы хотите применить, когда значение ячейки удовлетворяет условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы отображаются на панели предварительного просмотра.
В Excel в Интернете можно удалить повторяющиеся значения.
Удаление повторяющихся значений
При удалите повторяющиеся значения, только на значения в диапазоне ячеек или таблице. Другие значения за пределами диапазона ячеек или таблицы не изменяются и не перемещаются. При удалении дубликатов первое вхождение значения в списке будет сохранено, а другие одинаковые значения будут удалены.
Важно: Вы всегда можете нажать кнопку Отменить, чтобы вернуть данные после удаления дубликатов. При этом перед удалением повторяюющихся значений лучше скопировать исходный диапазон ячеек или таблицу на другой рабочий или другой.
Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
На вкладке Данные нажмите кнопку Удалить дубликаты.
В диалоговом окне Удаление дубликатов снимите с нее все столбцы, в которых не нужно удалять повторяющиеся значения.
Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключом, используемым для поиска дубликатов, будет значение BOTH Column1 & Column2. Если в столбцах "Столбец1" и "Столбец2" найдено повторяющиеся данные, удаляется вся строка, включая данные из столбца "Столбец3".
Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Из исходной таблицы с повторяющимися значениями отберем только те значения, которые имеют повторы. Теперь при добавлении новых значений в исходный список, новый список будет автоматически содержать только те значения, которые повторяются.
Пусть в столбце А имеется список с повторяющимися значениями, например список с названиями компаний. В некоторых ячейках исходного списка имеются повторы.
Задача
Создадим новый список, который содержит только те значения, которые повторяются. Дополнительное условие: при добавлении новых значений в исходный список, новый список должен автоматически включать только повторяющиеся значения.
Решение
Список значений, которые повторяются, создадим в столбце B с помощью формулы массива . (см. файл примера ).
Введем в ячейку B5 формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0) );"")
Вместо ENTER нужно нажать CTRL + SHIFT + ENTER .
ИсхСписок - это Динамический диапазон (ссылка на исходный список в столбце А ).
Скопируйте формулу вниз с помощью Маркера заполнения (размерность списка значений имеющих повторы должна совпадать с размерностью исходного списка).
В файле примера также приведены перечни, содержащие неповторяющиеся значения и уникальные значения.
С помощью Условного форматирования в исходном списке можно выделить повторяющиеся значения.
Тестируем
1. Добавьте в исходный список название новой компании (в ячейку А20 введите ООО Кристалл)
2. Список неповторяющихся значений (столбец С) автоматически будет обновлен, чтобы включить новое название
3. Добавьте в исходный список название новой компании еще раз (в ячейку А21 снова введите ООО Кристалл)
4. Список неповторяющихся значений автоматически будет обновлен, новое название будет исключено
5. Список повторяющихся значений (столбец B) автоматически будет обновлен, чтобы включить новое название.
СОВЕТ:
Созданный список повторяющихся значений является динамическим, т.е. при добавлении новых значений в исходный список, новый список будет автоматически обновляться. Это очень ресурсоемкая задача и годится для небольших списков 50-100 значений. Если динамический список не нужен, то можно пойти другим путем: см. статью Отбор повторяющихся значений с помощью фильтра .
В этом руководстве представлены трюки, позволяющие удалить все повторяющиеся значения из списка, но оставить только одно в Excel, прочтите подробности:
Удалите все дубликаты, но оставьте только один с помощью Kutools for Excel
Объедините все повторяющиеся строки и сохраните данные с помощью Kutools for Excel
Удалите все дубликаты, но оставьте только один с помощью функции удаления дубликатов
В Excel функция «Удалить дубликат» может помочь вам удалить все, кроме одного повторяющегося значения.
1. Выберите нужный список данных и нажмите Данные > Удалить дубликаты. Смотрите скриншот:
2. Затем в Удалить дубликаты диалоговое окно, проверьте имя столбца, из которого вы хотите удалить дубликаты, и если ваши данные имеют заголовок, отметьте У моих данных есть заголовки вариант тоже. Смотрите скриншот:
3. Нажмите OK. Появится диалоговое окно, в котором сообщается, сколько дубликатов было удалено, просто закройте его. И теперь все дубликаты удалены, но в списке остался только один.
Удалите все дубликаты, но оставьте только один с VBA
Если вас интересует VBA, здесь я также могу представить код VBA для решения этой задачи.
1. Нажмите Alt + F11 ключи одновременно, чтобы открыть Microsoft Visual Basic для приложенийокно s.
2. Нажмите Вставить > Модульи вставьте ниже VBA в новый Модуль окно.
VBA: удалите все дубликаты, но оставьте один.
3. Нажмите F5 ключ или щелкните Запустите для выполнения VBA, а дубликаты в списке в столбце A были удалены, но оставьте один.
Наконечник: В приведенном выше VBA A указывает столбец A, а A1: A указывает диапазон списка, вы можете изменить их по своему усмотрению.
Удалите все дубликаты, но оставьте только один с помощью Kutools for Excel
Есть мощная утилита в Kutools for Excel названный Выберите повторяющиеся и уникальные ячейки можно быстро выбрать или выделить все дубликаты, кроме первого, а затем удалить их.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон данных и нажмите Kutools > Выберите > Выберите повторяющиеся и уникальные ячейки. Смотрите скриншот:
2. В открывшемся диалоговом окне отметьте Дубликаты (кроме 1-го) и нажмите Ok. Смотрите скриншот:
3. Затем появится диалоговое окно, напоминающее количество выбранных ячеек. И просто нажмите OK чтобы закрыть это. Смотрите скриншот:
4. И все дубликаты, кроме первого, выбраны, нажмите Удалить ключ, чтобы очистить их.
Наконечник: Вы также можете удалить все дубликаты, но сохранить один с Выберите повторяющиеся и уникальные ячейки как это:
Выберите все уникальные значения, включая первые дубликаты, а затем скопируйте их, нажав Ctrl + C и вставьте в другое место с помощью Ctrl + V.
Удалите все дубликаты, но оставьте только один
Объедините все повторяющиеся строки и сохраните данные с помощью Kutools for Excel
Если у вас есть данные и вы хотите объединить повторяющиеся строки, сохраните соответствующие данные, как показано на скриншоте ниже, вы можете использовать Kutools for ExcelАвтора Расширенные ряды комбинирования функции.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите данные и нажмите Kutools > Слияние и разделение > Расширенные ряды комбинирования. Смотрите скриншот:
2. в Объединить строки на основе столбца выберите столбец, из которого вы хотите удалить дубликаты, и нажмите Основной ключ чтобы сделать его столбцом первичного ключа. Смотрите скриншот:
3. Затем выберите столбец, в котором вы просто хотите сохранить данные, и нажмите Сочетать, и выберите один разделитель из списка. Смотрите скриншот:
4. Нажмите Ok, а повторяющиеся строки объединяются и сохраняют соответствующие данные.
Очень часто при работе c таблицами Excel возникают ситуации, в которых необходимо сравнить несколько списков, найти в них повторяющиеся значения и что-то с ними сделать. Оптимальный способ поиска и обработки дубликатов должен быть выбран в зависимости от типа исходных данных и желаемого результата. Цель данной статьи - разобрать все возможные варианты обработки дубликатов в Excel в одной статье для того, чтобы читатель мог выбрать оптимальный вариант для любой ситуации.
Оглавление:
Функции в каждом разделе описаны в порядке возрастания их сложности и трудоемкости использования.
Выделение
Поиск и выделение повторяющихся значений ячеек в одном списке - условное форматирование
1. Выделить все значения в списке
2. Вкладка "Главная" -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения
3. Выбрать необходимый формат (в данном случае выбран красный шрифт на светло-красном фоне)
Если применить данное условное форматирование ко всему столбцу A, то все новые дубликаты, добавленные после строки 10 также будут отформатированы по заданному правилу.
Поиск и выделение повторяющихся значений ячеек в нескольких списках - условное форматирование
Сначала необходимо выделить столбцы (диапазоны ячеек) с дубликатами. Далее необходимо проделать действия, описанные в предидушем разделе начиная с шага 2.
Недостаток данного способа выделения дубликатов - визуально не определить, продублированы ли значения внутри каждого из списков, или между списками. В данном примере "малина" дублируется внутри списка 1, а "банан" и "груша" выделены потому что они продублированы между списками.
Для того, чтобы дублирование внутри списков воспринималось отдельно от дублирования между списков можно повторно использовать другое условное форматирование для каждого из столбцов по отдельности.
1. Выделяем первый столбец:
2. Вкладка "Главная" -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения
3. Пользовательский формат
5. Повторяем операцию с шага 2 для столбца B и получаем:
Поиск и выделение повторяющихся значений ячеек - макрос Excel-VBA
То же самое, но через форматирование ячеек:
Поиск и выделение повторяющегося текста внутри ячеек - макрос Excel VBA
Возможно существуют и другие более эффективные способы выделения копий слов внутри ячеек, но здесь будет описан только простейший способ, на который натолкнёт запись действий макрорекордером. Если мы напишем длинный текст в ячейке, включим запись макроса и отформатируем часть текста в ячейке, то получим примерно следующее:
Таким образом, для выделения отдельных слов в ячейке нам нужно предварительно найти, где расположен нужный нам набор символов, а также его длину для каждого повторения.
Замена
Замена дублирующихся значений ячеек с помощью макроса Excel-VBA
Подстановка в другие таблицы
Функция ВПР (VLOOKUP)
Комбинация функций ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH)+СЧЁТ()+ЕСЛИ()
Если таблица, из которой мы хотим доставать значения для подстановки содержит дубликаты, то использование функции ВПР может не дать нужного результата, так как ВПР использует первое попавшееся совпадение и результат будет зависит от сортировки.
Читайте также: