Как оставить только повторяющиеся значения в Excel

Обновлено: 21.11.2024

Фильтрация уникальных значений и удаление повторяющихся значений — это две тесно связанные задачи, поскольку отображаемые результаты одни и те же — список уникальных значений. Однако разница существенная. При фильтрации уникальных значений вы временно скрываете повторяющиеся значения, но при удалении повторяющихся значений вы удаляете повторяющиеся значения навсегда. Повторяющееся значение — это значение, в котором все значения в строке точно совпадают со всеми значениями в другой строке. Повторяющиеся значения определяются значением, отображаемым в ячейке, и не обязательно значением, хранящимся в ячейке. Например, если у вас есть одно и то же значение даты в разных ячейках, одна из которых имеет формат «8 декабря 2017 г.», а другая — «8 декабря 2017 г.», значения будут уникальными. Перед удалением повторяющихся значений рекомендуется отфильтровать или условно отформатировать уникальные значения, чтобы убедиться, что результаты соответствуют вашим ожиданиям.

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

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

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

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

Выполните одно из следующих действий:

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

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

Скопировать результаты фильтра в другое место

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

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

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

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

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

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

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

На вкладке "Данные" в группе "Инструменты данных" нажмите "Удалить дубликаты".

Установите один или несколько флажков, которые относятся к столбцам в таблице, а затем нажмите "Удалить дубликаты".

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

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

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

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

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

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

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

На вкладке "Главная" в группе "Стили" нажмите "Условное форматирование", а затем нажмите "Новое правило".

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

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

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

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

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

На вкладке "Главная" в группе "Стили" нажмите "Условное форматирование", а затем нажмите "Управление правилами".

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

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

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

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

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

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

Выполните одно из следующих действий:

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

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

Скопировать результаты фильтра в другое место

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

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

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

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

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

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

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

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

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

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

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

На значениях во всплывающем меню выбранного диапазона нажмите «уникальные» или «дубликаты».

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

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

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

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

Как сохранить повторяющиеся строки только в столбце Excel?

В этой статье будут показаны способы сохранения повторяющихся строк только в столбце Excel.

Сохраняйте повторяющиеся строки только с помощью Kutools for Excel

Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

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

<р>1. Выберите пустую ячейку, введите формулу =СЧЁТЕСЛИ(A:A, A2)=1 в строку формул, а затем нажмите клавишу Enter. Смотрите скриншот:

<р>2. Продолжайте выбирать ячейку, перетащите маркер заполнения вниз, пока не дойдете до последней ячейки списка.

Примечания:

<р>1. В формуле A:A — это столбец, содержащий повторяющиеся значения, A2 — это вторая ячейка списка, расположенная под заголовком. Вы можете изменить их в зависимости от ваших потребностей.

<р>2. Результат FALSE означает, что соответствующая ячейка дублируется в списке, а TRUE означает, что она уникальна.

<р>3. Выберите первую ячейку списка результатов и нажмите Данные > Фильтр.

<р>4. Щелкните стрелку раскрывающегося списка, снимите флажок ИСТИНА в раскрывающемся списке и нажмите кнопку ОК.

<р>5. Теперь все ячейки с уникальными значениями немедленно скрываются, и в списке отображаются только повторяющиеся значения.

Примечание. Если вы хотите удалить все скрытые уникальные строки ячеек, вы можете попробовать утилиту Удалить скрытые (видимые) строки и столбцы. Смотрите скриншот ниже.

После бесплатной установки Kutools for Excel с помощью утилиты «Выбрать повторяющиеся и уникальные ячейки» Kutools for Excel вы можете легко выбрать целые строки всех уникальных ячеек, а затем удалить их сразу.
<р>1. Выберите список, в котором вы хотите сохранить только повторяющиеся строки, затем нажмите Kutools > Выбрать > Выбрать повторяющиеся и уникальные ячейки. Смотрите скриншот:

<р>2. В диалоговом окне «Выбор дубликатов и уникальных ячеек» выберите параметр «Только уникальные значения», установите флажок «Выбрать все строки» и, наконец, нажмите кнопку «ОК». Смотрите скриншот:

<р>3. Затем появится диалоговое окно, в котором будет указано, сколько строк было выбрано. Нажмите кнопку "ОК".
<р>4. Щелкните правой кнопкой мыши выбранные строки, а затем нажмите «Удалить» в контекстном меню. Смотрите скриншот:

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

Предварительное условие:

Подготовьте список A. Он содержит повторяющиеся значения. Мы хотим сохранить эти повторяющиеся значения и удалить остальные.

Способ 1. Сохранение повторяющихся значений с помощью условного форматирования

Шаг 1. Выберите список А.

Шаг 2. Нажмите «Главная» на ленте, затем «Условное форматирование» в группе «Стили».

Шаг 3. В раскрывающемся списке «Условное форматирование» выберите «Правила выделения ячеек» -> «Повторяющиеся значения».

Шаг 4. В диалоговом окне «Повторяющиеся значения» выберите «Дублировать» в раскрывающемся списке. Сохранить значение по умолчанию в значениях с раскрывающимся списком. Затем нажмите ОК.

Шаг 5. Убедитесь, что все повторяющиеся значения правильно помечены темно-красным цветом.

Шаг 6. Выберите A1, нажмите «Данные» на ленте, затем нажмите «Фильтр» в группе «Сортировка и фильтр».

Шаг 7. Нажмите стрелку фильтра на A1, чтобы загрузить настройки фильтра. Нажмите Фильтр по цвету, в разделе Фильтр по цвету ячейки выберите ячейку с цветом.

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

Шаг 8. Теперь нам просто нужно скопировать эти значения в новый столбец. Выберите эти ячейки, нажмите F5, нажмите "Специальные" в диалоговом окне "Перейти".

Отметьте Только видимые ячейки и нажмите OK.

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

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

Способ 2. Извлечение повторяющихся значений по формулам

Шаг 1. В поле B2 введите формулу =СЧЁТЕСЛИМН($A$2:$A$14,$A2)>1. Мы получаем ИСТИНА.

Шаг 2. Перетащите маркер заполнения вниз. Убедитесь, что B2:B14 заполнены TRUE или FALSE.

Шаг 3. В B1 перейдите на вкладку «Данные» на ленте и нажмите «Фильтр», чтобы создать фильтр. В фильтре отметьте только TRUE, затем нажмите OK.

Шаг 4. Убедитесь, что отображаются только повторяющиеся значения.

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

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

В этом уроке показано, как использовать функцию ЕСЛИ в Excel для распределения баллов на основе количества прошедшего времени. Если вы намерены следовать этому руководству, вы можете сделать это, загрузив .

Предположим, что у вас есть список дат с разными форматами дат, как показано на прилагаемом рисунке. В этом случае функция сортировки Excel не сможет их правильно отсортировать. Однако вы можете преобразовать все различные форматы даты в определенный файл .

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

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

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

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

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

В Excel существует несколько способов фильтрации уникальных значений или удаления повторяющихся значений:

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

Фильтрация уникальных значений и удаление повторяющихся значений — это две похожие задачи, поскольку цель состоит в том, чтобы представить список уникальных значений. Однако есть важное отличие: при фильтрации уникальных значений повторяющиеся значения лишь временно скрываются. Однако удаление повторяющихся значений означает, что вы удаляете повторяющиеся значения без возможности восстановления.

Повторяющееся значение — это значение, в котором все значения хотя бы в одной строке идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не от базового значения, хранящегося в ячейке. Например, если у вас есть одно и то же значение даты в разных ячейках, одна из которых имеет формат «8 марта 2006 г.», а другая — «8 марта 2006 г.», значения будут уникальными.

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

Выполните следующие действия:

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

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

Во всплывающем окне "Расширенный фильтр" выполните одно из следующих действий:

Чтобы отфильтровать диапазон ячеек или таблицу на месте:

Нажмите "Фильтровать список на месте".

Чтобы скопировать результаты фильтра в другое место:

Нажмите «Копировать в другое место».

В поле "Копировать в" введите ссылку на ячейку.

Можно также нажать «Свернуть диалоговое окно», чтобы временно скрыть всплывающее окно, выбрать ячейку на листе и нажать «Развернуть» .

Проверьте только уникальные записи, затем нажмите OK.

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

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

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

Выполните следующие действия:

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

На вкладке "Данные" нажмите "Удалить дубликаты" (в группе "Инструменты данных").

Выполните одно или несколько из следующих действий:

В разделе "Столбцы" выберите один или несколько столбцов.

Чтобы быстро выбрать все столбцы, нажмите "Выбрать все".

Чтобы быстро очистить все столбцы, нажмите "Отменить выбор всех".

Если диапазон ячеек или таблица содержит много столбцов и вы хотите выбрать только несколько столбцов, вам может быть проще нажать "Отменить выбор всех", а затем в разделе "Столбцы" выбрать эти столбцы.

Примечание. Данные будут удалены из всех столбцов, даже если вы не выберете все столбцы на этом шаге. Например, если вы выберете Column1 и Column2, но не Column3, то «ключ», используемый для поиска дубликатов, будет значением ОБА Column1 и Column2. Если в этих столбцах обнаружен дубликат, вся строка будет удалена, включая другие столбцы в таблице или диапазоне.

Нажмите «ОК», и появится сообщение, указывающее, сколько повторяющихся значений было удалено или сколько уникальных значений осталось. Нажмите OK, чтобы закрыть это сообщение.

Отменить изменение, нажав «Отменить» (или нажав Ctrl+Z на клавиатуре).

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

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

Быстрое форматирование

Выполните следующие действия:

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

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

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

Расширенное форматирование

Выполните следующие действия:

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

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

Выполните одно из следующих действий:

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

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

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

В списке «Форматировать все» в разделе «Редактировать описание правила» выберите «уникальное» или «дубликат».

Нажмите «Формат», чтобы открыть всплывающее окно «Формат ячеек».

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

В Excel для Интернета можно удалить повторяющиеся значения.

Удалить повторяющиеся значения

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

Выполните следующие действия:

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

На вкладке "Данные" нажмите "Удалить дубликаты" .

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

Примечание. Данные будут удалены из всех столбцов, даже если вы не выберете все столбцы на этом шаге. Например, если вы выберете Column1 и Column2, но не Column3, то «ключ», используемый для поиска дубликатов, будет значением ОБА Column1 и Column2. Если в Column1 и Column2 обнаружен дубликат, то будет удалена вся строка, включая данные из Column3.

Нажмите "ОК". Появится сообщение о том, сколько повторяющихся значений было удалено. Нажмите OK, чтобы закрыть это сообщение.

Примечание. Если вы хотите вернуть свои данные, просто нажмите «Отменить» (или нажмите Ctrl+Z на клавиатуре).

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе Answers.

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