Как оставить только повторяющиеся значения в excel
Из исходной таблицы с повторяющимися значениями отберем только те значения, которые имеют повторы. Теперь при добавлении новых значений в исходный список, новый список будет автоматически содержать только те значения, которые повторяются.
Пусть в столбце А имеется список с повторяющимися значениями, например список с названиями компаний. В некоторых ячейках исходного списка имеются повторы.
Задача
Создадим новый список, который содержит только те значения, которые повторяются. Дополнительное условие: при добавлении новых значений в исходный список, новый список должен автоматически включать только повторяющиеся значения.
Решение
Список значений, которые повторяются, создадим в столбце B с помощью формулы массива . (см. файл примера ).
Введем в ячейку B5 формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0) );"")
Вместо ENTER нужно нажать CTRL + SHIFT + ENTER .
ИсхСписок - это Динамический диапазон (ссылка на исходный список в столбце А ).
Скопируйте формулу вниз с помощью Маркера заполнения (размерность списка значений имеющих повторы должна совпадать с размерностью исходного списка).
В файле примера также приведены перечни, содержащие неповторяющиеся значения и уникальные значения.
С помощью Условного форматирования в исходном списке можно выделить повторяющиеся значения.
Тестируем
1. Добавьте в исходный список название новой компании (в ячейку А20 введите ООО Кристалл)
2. Список неповторяющихся значений (столбец С) автоматически будет обновлен, чтобы включить новое название
3. Добавьте в исходный список название новой компании еще раз (в ячейку А21 снова введите ООО Кристалл)
4. Список неповторяющихся значений автоматически будет обновлен, новое название будет исключено
5. Список повторяющихся значений (столбец B) автоматически будет обновлен, чтобы включить новое название.
СОВЕТ:
Созданный список повторяющихся значений является динамическим, т.е. при добавлении новых значений в исходный список, новый список будет автоматически обновляться. Это очень ресурсоемкая задача и годится для небольших списков 50-100 значений. Если динамический список не нужен, то можно пойти другим путем: см. статью Отбор повторяющихся значений с помощью фильтра .
Имея список с повторяющимися значениями, создадим список, состоящий только из уникальных значений. При добавлении новых значений в исходный список, список уникальных значений должен автоматически обновляться.
Пусть в столбце А имеется список с повторяющимися значениями, например список с названиями компаний.
Задача
В некоторых ячейках исходного списка имеются повторы - новый список уникальных значений не должен их содержать.
Для наглядности уникальные значения в исходном списке выделены цветом с помощью Условного форматирования .
Решение
Для начала создадим Динамический диапазон , представляющий собой исходный список. Если в исходный список будет добавлено новое значение, то оно будет автоматически включено в Динамический диапазон и нижеследующие формулы не придется модифицировать.
Для создания Динамического диапазона :
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Исходный_список ;
- в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))
- нажмите ОК.
Список уникальных значений создадим в столбце B с помощью формулы массива (см. файл примера ). Для этого введите следующую формулу в ячейку B5 :
После ввода формулы вместо ENTER нужно нажать CTRL + SHIFT + ENTER . Затем нужно скопировать формулу вниз, например, с помощью Маркера заполнения . Чтобы все значения исходного списка были гарантировано отображены в списке уникальных значений, необходимо сделать размер списка уникальных значений равным размеру исходного списка (на тот случай, когда все значения исходного списка не повторяются). В случае наличия в исходном списке большого количества повторяющихся значений, список уникальных значений можно сделать меньшего размера, удалив лишние формулы, чтобы исключить ненужные вычисления, тормозящие пересчет листа.
Разберем работу формулу подробнее:
- Здесь использование функции СЧЁТЕСЛИ() не совсем обычно: в качестве критерия (второй аргумент) указано не одно значение, а целый массив Исходный_список , поэтому функция возвращает не одно значение, а целый массив нулей и единиц. Возвращается 0, если значение из исходного списка не найдено в диапазоне B4:B4( B4:B5 и т.д.), и 1 если найдено. Например, в ячейке B5 формулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается массив . Т.е. в исходном списке найдено 4 значения «ООО Рога и копытца» ( B5 ). Массив легко увидеть с помощью клавиши F9 (выделите в Строке формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список) , нажмите F9 : вместо формулы отобразится ее результат);
- ПОИСКПОЗ() – возвращает позицию первого нуля в массиве из предыдущего шага. Первый нуль соответствует значению еще не найденному в исходном списке (т.е. значению "ОАО Уважаемая компания" для формулы в ячейке B5 );
- ИНДЕКС() – восстанавливает значение по его позиции в диапазоне Исходный_список ;
- ЕСЛИОШИБКА() подавляет ошибку, возникающую, когда функция ПОИСКПОЗ() пытается в массиве нулей и единиц, возвращенном СЧЁТЕСЛИ() , найти 0, которого нет (ситуация возникает в ячейке B12 , когда все уникальные значения уже извлечены из исходного списка).
Формула будет работать и в случае если исходный список содержит числовые значения.
Примечание . Функция ЕСЛИОШИБКА() будет работать начиная с версии MS EXCEL 2007, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() . В файле примера имеется лист Для 2003 , где эта функция не используется.
Решение для списков с пустыми ячейками
Если исходная таблица содержит пропуски, то нужно использовать другую формулу массива (см. лист с пропусками файла примера ): =ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19; ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);"";СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0) );"")
Решение без формул массива
Для отбора уникальных значений можно обойтись без использования формул массива . Для этого создайте дополнительный служебный столбец для промежуточных вычислений (см. лист "Без CSE" в файле примера ).
СОВЕТ: Список уникальных значений можно создать разными способами, например, с использованием Расширенного фильтра (см. статью Отбор уникальных строк с помощью Расширенного фильтра ), Сводных таблиц или через меню Данные/ Работа с данными/ Удалить дубликаты . У каждого способа есть свои преимущества и недостатки. Преимущество использования формул состоит в том, чтобы при добавлении новых значений в исходный список, список уникальных значений автоматически обновлялся.
СОВЕТ2 : Для тех, кто создает список уникальных значений для того, чтобы в дальнейшем сформировать на его основе Выпадающий список , необходимо учитывать, что вышеуказанные формулы возвращают значение Пустой текст "" , который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции СЧЕТЗ() нужно использовать СЧЕТЕСЛИ() со специальными аргументами ). Например, см. статью Динамический выпадающий список в MS EXCE L.
Примечание : В статье Восстанавливаем последовательности из списка без повторов в MS EXCEL решена обратная задача: из списка уникальных значений, в котором для каждого значения задано количество повторов, создается список этих значений с повторами.
В этом руководстве представлены трюки, позволяющие удалить все повторяющиеся значения из списка, но оставить только одно в 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 ключ или щелкните Run для выполнения VBA, а дубликаты в списке в столбце A были удалены, но оставьте один.
Наконечник: В приведенном выше VBA A указывает столбец A, а A1: A указывает диапазон списка, вы можете изменить их по своему усмотрению.
Удалите все дубликаты, но оставьте только один с помощью Kutools for Excel
Есть мощная утилита в Kutools for Excel названный Выберите повторяющиеся и уникальные ячейки можно быстро выбрать или выделить все дубликаты, кроме первого, а затем удалить их.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон данных и нажмите Кутулс > Выберите > Выберите повторяющиеся и уникальные ячейки. Смотрите скриншот:
2. В открывшемся диалоговом окне отметьте Дубликаты (кроме 1-го) и нажмите Ok. Смотрите скриншот:
3. Затем появится диалоговое окно, напоминающее количество выбранных ячеек. И просто нажмите OK чтобы закрыть это. Смотрите скриншот:
4. И все дубликаты, кроме первого, выбраны, нажмите Удалить ключ, чтобы очистить их.
Наконечник: Вы также можете удалить все дубликаты, но сохранить один с Выберите повторяющиеся и уникальные ячейки как это:
Выберите все уникальные значения, включая первые дубликаты, а затем скопируйте их, нажав Ctrl + C и вставьте в другое место с помощью Ctrl + V.
Удалите все дубликаты, но оставьте только один
Объедините все повторяющиеся строки и сохраните данные с помощью Kutools for Excel
Если у вас есть данные и вы хотите объединить повторяющиеся строки, сохраните соответствующие данные, как показано на скриншоте ниже, вы можете использовать Kutools for ExcelАвтора Расширенные ряды комбинирования функции.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите данные и нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования. Смотрите скриншот:
2. в Объединить строки на основе столбца выберите столбец, из которого вы хотите удалить дубликаты, и нажмите Основной ключ чтобы сделать его столбцом первичного ключа. Смотрите скриншот:
3. Затем выберите столбец, в котором вы просто хотите сохранить данные, и нажмите Сочетать, и выберите один разделитель из списка. Смотрите скриншот:
4. Нажмите Ok, а повторяющиеся строки объединяются и сохраняют соответствующие данные.
Как отображать только повторяющиеся строки в столбце Excel?
В некоторых случаях, если в столбце есть несколько повторяющихся значений и уникальных значений, вы просто хотите скрыть уникальные значения и показать только дубликаты, как показано ниже. Есть ли быстрый способ показать только дубликаты, кроме скрытия уникальных значений по одному? В этой статье, я думаю, вы сможете найти уловки.
Показать только дубликаты с помощью Kutools for Excel
Показывать только дубликаты с помощью функций условного форматирования и фильтрации
Вы можете использовать функцию условного форматирования, чтобы пометить цвет для повторяющихся значений, а затем отфильтровать цвет.
1. Выберите значения, для которых нужно отображать только дубликаты, и нажмите Главная > Условное форматирование > Выделите правила ячеек > Повторяющееся значениес. Тогда в Дублирующие значения диалоговое окно, выберите Дублировать в раскрывающемся списке слева выберите нужный формат из раскрывающегося списка справа и нажмите OK. Смотрите скриншот:
Затем дубликаты раскрашены.
2. Щелкните заголовок столбца Имя и щелкните Данные > Фильтр для Фильтр функция. Смотрите скриншот:
3. Затем нажмите Фильтр значок и в раскрывающемся списке выберите Фильтр по цветуи выберите цвет, который вы отформатировали для дубликатов на шаге 1 из подсписка. Смотрите скриншот:
4. Нажмите OK. Теперь отфильтрованы только дубликаты.
Показывать только дубликаты с формулами и функциями фильтрации
Более того, вы можете использовать формулу, чтобы сначала отметить дубликаты, а затем применить функцию фильтра только для фильтрации повторяющихся значений.
1. Выберите пустую ячейку рядом с диапазоном данных, например B2, введите эту формулу = СЧЁТЕСЛИМН ($ A $ 2: $ A $ 12, $ A2)> 1 , затем перетащите маркер заполнения в нужные ячейки. В ячейке отображается TURE означает, что соответствующая ячейка дублируется, НЕПРАВДА означает уникальное значение. Смотрите скриншот:
2. Щелкните ячейку B1, ячейку над формулами, и щелкните Данные > Фильтр применять Фильтр функция. Смотрите скриншот:
3. Затем нажмите Фильтр в ячейке B1 и отметьте ИСТИНА из раскрывающегося списка. Нажмите OK, то показаны только дубликаты. Смотрите скриншот:
Внимание: В формуле A2: A12 - это диапазон данных, который вы используете, вы можете изменить его по своему усмотрению.
Показать только дубликаты с помощью Kutools for Excel
Если вы хотите скрыть уникальные значения и показать только повторяющиеся строки, вы можете применить Kutools for ExcelАвтора Выберите повторяющиеся и уникальные ячейки Утилита для выбора уникальных строк, а затем скрытие уникальных строк и отображение только повторяющихся строк.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон данных и нажмите Кутулс > Выберите > Выберите повторяющиеся и уникальные ячейки. Смотрите скриншот:
2. в Выберите повторяющиеся и уникальные ячейки диалог, проверьте Только уникальные значения и Выбрать целые строки параметры, щелкните Ok, появится диалоговое окно, напоминающее количество выбранных строк. Смотрите скриншот:
3. Нажмите OK, теперь выбраны уникальные строки, щелкните правой кнопкой мыши заголовок одной строки выбранных уникальных строк, выберите Спрятать из контекстного меню. Смотрите скриншот:
Теперь все уникальные строки были скрыты, и были показаны только повторяющиеся строки.
Наконечник. Если вы хотите получить бесплатную пробную версию Выберите повторяющиеся и уникальные ячейки Функция, пожалуйста, перейдите к бесплатной загрузке Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с вышеуказанными шагами.
В 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.
Читайте также: