Как удалить уникальные значения в excel

Обновлено: 08.07.2024

Фильтрация уникальных значений и удаление повторяющихся — это две тесно связанные друг с другом задачи, поскольку в результате их выполнения отображается список уникальных значений. Однако между этими двумя задачами существует важное различие. При фильтрации уникальных значений повторяющиеся значения временно скрываются, тогда как в ходе удаления повторяющихся значений они удаляются без возможности восстановления. Значение считается повторяющимся, если все значения в одной строке полностью совпадают со значениями в другой. Повторяющиеся значения определяются значением, которое отображается в ячейке, а не тем, которое в ней хранится. Например, если в различных ячейках содержатся одинаковые значения даты в разных форматах ("08.12.2010" и "8 дек 2010"), они считаются уникальными. Рекомендуется сначала отфильтровать уникальные значения или применить к ним условное форматирование, чтобы перед удалением повторяющихся значений убедиться в том, что будет получен ожидаемый результат.

Примечание: Если формулы в ячейках разные, а значения одинаковые, такие значения считаются повторяющимися. Например, если в ячейке A1 содержится формула =2-1, а в ячейке A2 — формула =3-2 и к ячейкам применено одинаковое форматирование, такие значения считаются повторяющимися. Одинаковые значения, к которым применены различные числовые форматы, не считаются повторяющимися. Например, если значение в ячейке A1 имеет формат 1,00, а в ячейке A2 — формат 1, эти значения не являются повторяющимися.

Фильтрация уникальных значений

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

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из указанных ниже действий.

Необходимые действия

Фильтрация диапазона ячеек или таблицы на месте

Выделите диапазон ячеек и щелкните Фильтровать список на месте.

Копирование результатов фильтрации в другое место

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

Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.

Установите флажок Только уникальные записи и нажмите кнопку ОК.

Дополнительные параметры

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

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

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

На вкладке Данные в разделе Работа с данными нажмите кнопку Удалить дубликаты.

Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите кнопку Удалить дубликаты.

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

Применение условного форматирования к уникальным или повторяющимся значениям

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

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

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

В диалоговом окне Создать правило форматирования выберите нужные параметры и нажмите кнопку ОК.

Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям

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

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

На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Создать правило.

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

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

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

Изменение правил расширенного условного форматирования

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

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

На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Управление правилами.

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

Выберите правило и нажмите кнопку Изменить правило.

Выберите нужные параметры и нажмите кнопку ОК.

Фильтрация уникальных значений

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

На вкладке Данные в разделе Сортировка и фильтр щелкните стрелку рядом с элементом Фильтр и выберите пункт Расширенный фильтр.

Выполните одно из указанных ниже действий.

Необходимые действия

Фильтрация диапазона ячеек или таблицы на месте

Выделите диапазон ячеек и щелкните Фильтровать список на месте.

Копирование результатов фильтрации в другое место

Установите флажок Только уникальные записи и нажмите кнопку ОК.

Дополнительные параметры

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

На вкладке Данные в разделе Сервис нажмите кнопку Удалить дубликаты.

Применение условного форматирования к уникальным или повторяющимся значениям

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

На вкладке Главная в разделе Формат щелкните стрелку рядом с элементом Условное форматирование, выберите пункт Правила выделения ячеек, а затем — пункт Повторяющиеся значения.

Выберите нужные параметры и нажмите кнопку ОК.

Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям

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

На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Создать правило.

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

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

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

Изменение правил расширенного условного форматирования

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

На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.

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

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

Удалите все, кроме повторяющихся строк, с помощью вспомогательного столбца

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

1. Введите эту формулу = СЧЁТЕСЛИ ($ A $ 2: $ A $ 15; A2) = 1 в пустую ячейку помимо ваших данных, например ячейку B2, см. снимок экрана:

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

2. Затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, Ложь указывает на повторяющиеся значения и Правда обозначает уникальные значения, см. снимок экрана:

3. Затем выберите диапазон данных и нажмите Данные > Фильтр, см. снимок экрана:

4. Щелкните раскрывающийся список фильтров в новом вспомогательном столбце B и снимите флажок НЕПРАВДА вариант, чтобы просто показать уникальные значения столбца A, а затем щелкните OK кнопку, см. снимок экрана:

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

документ удалить все, кроме дубликатов 5

документ удалить все, кроме дубликатов 6

6. Затем нажмите OK чтобы закрыть диалоговое окно, и были выбраны только видимые строки, как показано на следующем снимке экрана:

7. Затем вам нужно удалить только эти видимые строки, навести курсор на выбранные строки, щелкнуть правой кнопкой мыши, чтобы выбрать Удалить строку из контекстного меню см. снимок экрана:

8. И сразу удаляются только видимые строки, тогда нужно отменить фильтр, нажав Данные > Фильтр снова, чтобы отключить Фильтр функция, и вы можете увидеть, что все строки с уникальными значениями были удалены, и оставлены только повторяющиеся записи, см. снимок экрана:

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

Удалите все, кроме повторяющихся строк, с помощью Kutools for Excel

При использовании вышеуказанного метода необходимо выполнить так много шагов, если у вас есть удобный инструмент - Kutools for Excel, это может сэкономить вам много времени.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите диапазон данных, который вы хотите использовать.

2. Затем нажмите Kutools > Выберите > Выберите повторяющиеся и уникальные ячейки, см. снимок экрана:

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

4. Затем нажмите OK Кнопка, и появится окно подсказки, чтобы напомнить вам, сколько строк было выбрано:

5, Затем нажмите OK чтобы закрыть эти диалоги и выбрать все уникальные строки значений, см. снимок экрана:

6. Затем вам просто нужно удалить эти выбранные строки, нажмите Главная > Удалить > Удалить строки листов, см. снимок экрана:

7. И все выбранные уникальные строки были немедленно удалены.

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

В этом руководстве представлены трюки, позволяющие удалить все повторяющиеся значения из списка, но оставить только одно в 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, а повторяющиеся строки объединяются и сохраняют соответствующие данные.

Начиная с 2007-й версии функция удаления дубликатов является стандартной - найти ее можно на вкладке Данные - Удаление дубликатов (Data - Remove Duplicates) :

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

Способ 2. Расширенный фильтр

Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data) .

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

Выбираем в меню Данные - Фильтр - Расширенный фильтр (Data - Filter - Advanced Filter) . Получаем окно:

Выделяем наш список компаний в Исходный диапазон (List Range) .
Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.

Получите список без дубликатов:

Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE) :

Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.

Способ 3. Выборка уникальных записей формулой

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

Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:

Первая задача - пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем затем вниз до упора следующую формулу:

В английской версии это будет:

Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз - дает ему последовательно возрастающий номер.

Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы - Диспетчер имен (Formulas - Name manager) или в старых версиях - через меню Вставка - Имя - Присвоить (Insert - Name - Define) :

диапазону номеров (A1:A100) - имя NameCount
всему списку с номерами (A1:B100) - имя NameList

Теперь осталось выбрать из списка NameList все элементы имеющие номер - это и будут наши уникальные представители. Сделать это можно в любой пустой ячейке соседних столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:

=ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1);NameList;2))

или в английской версии Excel:

=IF(MAX(NameCount)

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

Читайте также: