Найти дубликаты в Excel и выделить

Обновлено: 21.11.2024

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

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

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

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

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

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

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

Давайте рассмотрим несколько примеров.

Действия по поиску и выделению дубликатов в Excel с использованием условного форматирования перечислены ниже:

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

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

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

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

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

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

Используйте функцию условного форматирования Excel.

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

Шаг 1. Выберите столбец «Номер счета» (столбец A). Благодаря этому выбору в будущем можно будет выделить новое повторяющееся значение, введенное в существующем списке (столбец A).

Шаг 2. На вкладке «Главная» щелкните раскрывающийся список условного форматирования в группе «Стили». Выберите «повторяющиеся значения» в «правилах выделения ячеек», как показано на следующем рисунке.

Шаг 3. Выберите цвет, которым должны быть выделены повторяющиеся значения ячеек. Мы выбираем «зеленая заливка с темно-зеленым текстом». Нажмите «ОК».

Шаг 4. Четыре повторяющихся значения выделены зеленым цветом. Это те, которые встречаются более одного раза.

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

Примечание 1. Либо выберите весь рабочий лист (на шаге 1) и выполните шаги 2 и 3.Результаты будут такими же, как и на шаге 4.

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

Действия по удалению дубликатов из выбранного диапазона перечислены ниже:

Шаг 1. Выберите диапазон данных, содержащий дубликаты.

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

Можно поочередно нажимать сочетания клавиш «Alt+A+M».

Установите флажок «Мои данные содержат заголовки». Нажмите «ОК».

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

Нажмите "ОК", чтобы увидеть результаты.

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

Предостережения относительно повторяющихся значений

Предостережения, которые необходимо соблюдать при работе с дубликатами, перечислены ниже:

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

Часто задаваемые вопросы

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

Действия по поиску и выделению дубликатов в Excel перечислены ниже:
a. Выберите диапазон, в котором дубликаты должны быть найдены и выделены.
б. Щелкните раскрывающийся список «условное форматирование» на вкладке «Главная» в Excel. Выберите «повторяющиеся значения» из «правил выделения ячеек».
в. Откроется окно «Повторяющиеся значения». Выберите необходимое форматирование из различных вариантов, доступных в поле «значения с». Нажмите «ОК».

Дубликаты в выбранном диапазоне будут найдены и выделены цветом, выбранным на шаге c.

Давайте выделим дубликаты в следующих столбцах:

• Столбец A состоит из розы, лилии, подсолнуха, лилии и лотоса в диапазоне A1:A5.
• Столбец B состоит из тюльпанов, роз, орхидей, лотосов и роз в диапазоне B1:B5.
• В столбец C входят подсолнечник, нарцисс, бархатцы, тюльпан и гибискус в диапазоне C1:C5.

Шаги по выделению дубликатов в столбцах Excel A, B и C перечислены ниже:

а. Выберите диапазон A1:C5.
б. Щелкните раскрывающийся список «условное форматирование» на вкладке «Главная». Выберите «Управление правилами».
в. Откроется диалоговое окно «Менеджер правил условного форматирования». Нажмите «Новое правило».
г. В разделе «выберите тип правила» нажмите «форматировать только уникальные или повторяющиеся значения». В разделе «Форматировать все» выберите «Дублировать».
э. Нажмите «формат» и во вкладке «заливка» выберите нужный цвет. Нажмите «ОК».
ж. Нажмите «ОК» еще раз в окне «новое правило форматирования».
г. Правило, примененное к текущему выделению, отображается в диалоговом окне «Менеджер правил условного форматирования». Нажмите «ОК».

Все названия цветов, встречающиеся более одного раза в выбранном диапазоне (A1:C5), выделяются. Уникальные названия цветов — орхидея, нарцисс, бархатцы и гибискус. Остальные имена цветов являются повторяющимися значениями.

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

Этапы выделения дубликатов в нескольких столбцах с помощью формулы СЧЁТЕСЛИ перечислены ниже:

а. Выберите весь диапазон, в котором должны быть найдены дубликаты.
б. Щелкните раскрывающийся список «условное форматирование» на вкладке «Главная». Выберите «новое правило».
в. Откроется окно «новое правило форматирования».Выберите параметр «использовать формулу, чтобы определить, какие ячейки форматировать» в разделе «выберите тип правила».
д. В разделе «изменить описание правила» введите следующую формулу.
“=СЧЁТЕСЛИ(диапазон,верхняя_ячейка)>1”

Диапазон — это диапазон, выбранный на шаге а. «top_cell» — это первая, самая левая ячейка текущего выделения. Например, если «диапазон» равен A1:C5, формула принимает вид «=СЧЁТЕСЛИ($A$1:$C$5,A1)>1».

<р>т.е. Нажмите «Формат» и выберите нужный цвет (на вкладке «Заливка») для выделения дубликатов. Нажмите «ОК».
ж. Нажмите «ОК» еще раз в окне «новое правило форматирования».

Дубликаты в выбранном диапазоне будут выделены.

Примечание. В формуле, введенной на шаге d, «диапазон» ($A$1:$C$5) записывается с использованием абсолютных ссылок. «top_cell» (A1) записывается с использованием относительной ссылки.

Рекомендуемые статьи

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

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

Видеоинструкции

Условное форматирование — выделение дубликатов

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

  1. Выделите весь раздел, в котором вы хотите найти дубликаты. Обычно это столбец или массив.
  2. На вкладке "Главная" нажмите "Условное форматирование".
  3. Выберите правила выделения
  4. Выбрать повторяющиеся значения

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

Функция СЧЁТЕСЛИ

Функция СЧЕТЕСЛИ делает именно то, о чем говорит. Он подсчитывает значения в ячейках, если выполняется условие. Итак, в этом случае мы хотим подсчитать случаи, когда значение дублируется. Давайте посмотрим на элементы функции СЧЁТЕСЛИ. COUNT(диапазон, критерии). Диапазон — это столбец или длина данных, в которых живут ваши дубликаты, а ваши критерии — это то, что он использует, чтобы решить, когда считать. Следуйте приведенным ниже инструкциям, чтобы использовать функцию СЧЁТЕСЛИ для поиска дубликатов. Вот полная функция. Но следуйте приведенной ниже разбивке, чтобы увидеть это в деталях.

СЧЁТЕСЛИ(Диапазон, Критерий)

СЧЁТЕСЛИ(Там, где дубликаты, значение, которое нужно проверить)

  1. Запишите функцию СЧЁТЕСЛИ рядом с первым значением в диапазоне данных =СЧЁТЕСЛИ
  2. Введите часть диапазона формулы =СЧЁТЕСЛИ(A2:A11
  3. Введите вторую часть формулы, которую вы хотите оценить, в этом случае мы хотим увидеть, равны ли критерии первой ячейке в наших данных = СЧЁТЕСЛИ(A2:A11,A2).
  4. После этого вам нужно заблокировать диапазон, чтобы гарантировать, что он не изменится. Вы можете сделать это, просто нажав F4. Итоговая формула будет выглядеть так: СЧЁТЕСЛИ($A$2:$A$11,A2).
  5. Скопируйте формулу вниз, и она покажет, сколько раз повторяется конкретное значение.

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

СВОДНАЯ ТАБЛИЦА

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

  1. Нажмите на свои данные и выберите «Вставить сводную таблицу» на вкладке «Вставка». Появится окно «Поля сводной таблицы».
  2. Перетащите значение, которое нужно оценить, в раздел ROWS сводной таблицы.
  3. Перетащите нужное значение в VALUES.
  4. По умолчанию значения суммируются так, как нам нужно. Просто нажмите морковку вниз в настройках поля значения и выберите вариант подсчета.

Нам по-прежнему нужно изменить агрегацию на подсчет, потому что это не значение по умолчанию.

Нажмите стрелку вниз или морковку, чтобы изменить агрегирование с SUM на COUNT.

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


Изображение: Ааджан Getty Images/iStockphoto

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

Я использую Microsoft 365 в 64-разрядной системе Windows 10, но вы можете использовать более раннюю версию. Вы можете работать со своими данными или скачать демонстрационный файл .xlsx. Браузер поддерживает условное форматирование; однако вы не можете использовать браузер для реализации правила формулы.

Как выделить отдельные значения в Excel

Простой набор данных, показанный на рис. A, повторяет несколько значений в столбце D: 1, 2 и 6. Их легко различить визуально, но так будет не всегда. Давайте воспользуемся встроенным правилом, чтобы выделить их:

  1. Выберите значения, которые хотите отформатировать; в данном случае это D3:D16.
  2. Перейдите на вкладку "Главная". Затем откройте раскрывающийся список «Условное форматирование» в группе «Стили».
  3. В раскрывающемся списке выберите «Правила выделения ячеек», а затем выберите «Дублировать значения» в появившемся подменю (рис. A).
  4. Выберите предустановленный формат в раскрывающемся списке справа (рис. Б).
  5. При нажатии кнопки "ОК" Excel выделяет повторяющиеся значения в столбце D, как показано на рисунке C.

Рисунок А

Рисунок Б

Рисунок C

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

Как выделить строки в Excel

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

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

  1. Выберите диапазон данных, B3:E16 — вы хотите выделить всю строку. Если вы используете таблицу, Excel будет обновлять диапазон при добавлении и удалении записей.
  2. Нажмите «Условное форматирование» в группе «Стили» и выберите «Новое правило».
  3. На верхней панели появившегося диалогового окна выберите последний параметр «Использовать формулу для определения форматируемых ячеек».
  4. В нижней панели введите формулу
    =СЧЕТЕСЛИ($D$3:$D$16, $D3)>1
  5. Нажмите «Формат», выберите формат и нажмите «ОК». На этот раз я выбираю цвет шрифта, чтобы вы могли увидеть оба правила в действии. На рисунке D показано правило и предварительный просмотр формата.
  6. Нажмите "ОК".

Рисунок D

Функция СЧЁТЕСЛИМН() сама подсчитывает, сколько раз значение встречается в столбце D. Если это значение больше единицы, то есть значение встречается более одного раза, функция возвращает значение "Истина", и формат применяется. Когда счетчик равен 1 или меньше, функция возвращает значение False, и ничего не происходит.

Теперь у вас есть два правила условного форматирования. Один встроенный и выделяет отдельные значения. Другой представляет собой формулу и выделяет всю запись.

Еженедельный информационный бюллетень Microsoft

Будьте инсайдером Microsoft в своей компании, прочитав эти советы, рекомендации и памятки по Windows и Office.

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

Выделить повторяющиеся строки в одном столбце

Первый — самый простой. Здесь у нас есть только один столбец, поэтому, когда мы выделяем одну ячейку, мы также выделяем одну строку.

Чтобы выделить повторяющиеся строки, сначала выберите ячейки от A1 до A9.

Перейдите на ГЛАВНУЮ >> Стили >> Условное форматирование >> Выделите правила ячеек и выберите Дублировать значения.

Появится новое окно.

Убедитесь, что вы выбрали «Дублировать», и нажмите «ОК».

Все повторяющиеся строки теперь выделяются.

Проверить несколько столбцов

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

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

Давайте создадим еще один столбец, в котором мы объединим оба столбца, и назовем его Объединенным.

Введите следующую формулу в ячейку C2.

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

Теперь, когда мы используем ГЛАВНАЯ >> Стили >> Условное форматирование >> Правила выделения ячеек >> Повторяющиеся значения… в столбце C будет выделен только этот столбец.

Чтобы выделить все столбцы (A, B и C), мы должны использовать правила.

  1. Выберите ячейки от A1 до C10.
  2. Перейдите на ГЛАВНУЮ >> Стили >> Условное форматирование и нажмите «Новое правило…».
  3. Нажмите Использовать формулу, чтобы определить ячейки для форматирования.
  4. Нажмите кнопку «Формат…», чтобы выбрать цвет.
  5. Введите следующую формулу: =СЧЕТЕСЛИ($C$1:$C$10,$C1)>1.
  6. Нажмите "ОК".

Это последний пример.

Анализ формулы:

Функция СЧЁТЕСЛИ принимает два аргумента:

Диапазон:

Критерии:

Как видите, критерий $C1. Перед номером 1 нет знака доллара. Это означает, что номер строки не является абсолютным. Другими словами, мы проверяем каждый критерий (C1, C2, …, C10) и сравниваем его с диапазоном (C1:C10).

Прочитайте этот урок, если хотите узнать больше об абсолютных и относительных ссылках на ячейки.

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