Как найти дубликаты в Excel?
Часто при работе с большими данными в Excel это, как правило, непростая задача, особенно при выполнении определенных задач, таких как определение дубликатов в диапазоне данных или по столбцу. Что обычно включает поиск и удаление дубликатов или любую комбинацию, в которой необходимо встретить повторяющиеся ячейки. Excel предоставляет идеальный пакет для поиска или удаления дубликатов разными способами, который поможет пользователю смешивать данные по мере необходимости.
Давайте рассмотрим некоторые из методологий и формул с примерами, которые используются для поиска, выделения и удаления дубликатов в Excel.
Вы можете скачать этот шаблон Excel для поиска дубликатов здесь - шаблон Excel для поиска дубликатовПоиск дубликатов в Excel с использованием условного форматирования
Рассмотрим приведенную ниже таблицу, в которой мы хотели бы идентифицировать и выделить дубликаты, если таковые имеются. В этом примере условное форматирование в Excel используется для поиска и выделения ячеек для дубликатов в Excel. Эта функция доступна в версии Excel 2007 и новее.
Шаг 1: - Теперь мы хотели бы найти и выделить дубликаты в позициях Excel по столбцам. Выберите диапазон данных, чтобы найти дубликаты в Excel.
Шаг 2: - Затем перейдите на главную, выберите «Условное форматирование» и перейдите в раздел «Выделить правила ячеек», и мы найдем повторяющиеся значения.
Шаг 3: - Когда появится всплывающее окно, выберите «Дублировать» значения и необходимые цвета для заливки из раскрывающегося списка, чтобы выделить ячейки. Затем нажмите ОК.
Шаг 4: - После того, как выбор сделан, ниже будет выделен результат для повторяющихся ячеек в таблице данных.
Шаг 5: - Мы также можем фильтровать по любому столбцу, чтобы найти дубликаты в Excel. Это делается щелчком правой кнопкой мыши в нужном столбце для фильтрации дубликатов.
Шаг 6: - Затем перейдите к фильтрам и выберите «Фильтровать по цвету выбранной ячейки». Это позволит вам фильтровать только дубликаты.
Шаг 7: - Ниже приведен результат после применения фильтра к столбцу «Канцелярские товары».
Поиск определенных чисел и дубликатов в Excel
Рассмотрим следующий пример, если вы хотите найти и выделить единственное определенное количество дубликатов в Excel, например, содержимое с тремя числами дубликатов.
Шаг 1: - Выберите диапазон A2: C8 из приведенной выше таблицы данных.
Шаг 2: - Теперь перейдите на вкладку «Главная» и в группе стиля выберите условное форматирование и нажмите новые правила.
Шаг 3: - Как только вы щелкнете по новым правилам, появится всплывающее окно. Где вам нужно будет выбрать «Использовать формулу, чтобы определить, какие ячейки нужно форматировать». Затем введите формулу для = СЧЁТЕСЛИ (диапазон ячеек для таблицы данных, критерии ячеек), чтобы определить, какие ячейки необходимо идентифицировать и выделить для нужного количества повторяющихся ячеек.
В этом случае я выделил только это содержимое ячеек для трех повторений, это также может быть изменено на более трех повторений или любые другие условия, если необходимо.
Шаг 4: - После того, как формула введена, перейдите в Формат. Появится еще одно всплывающее окно, в котором необходимо выбрать вкладку шрифта и цвета, чтобы выделить повторяющиеся ячейки в Excel.
На вкладке Шрифт мы выбрали Обычный. В то время как на вкладке заливки мы выбрали синий оттенок, который будет выделен для желаемых повторяющихся ячеек.
Шаг 5: - После того, как выбор сделан в ячейках формата. Щелкните ОК.
Кроме того, нажмите OK, чтобы появилось новое окно правил форматирования, как показано на шаге 3.
Шаг 6: - Ниже показан желаемый результат для трехкратного подсчета дубликатов для текущего примера.
Шаг 7: - Очистить правила: теперь, если мы снова хотим изменить правила или формулу из таблицы данных. Затем вам нужно сначала очистить правила для всего листа или выбранных ячеек.
Теперь перейдите на вкладку «Главная», выберите условное форматирование в группе стилей. Затем перейдите к очистке правил и выберите одно из следующих: -
Очистить правила для выбранных ячеек: - это сбросит правила для выбранного диапазона только для таблицы данных, это также требует выбора таблицы данных перед очисткой правил.
Четкие правила для всего листа: - Это очистит правила для всего листа.
Найти и удалить дубликаты в Excel
В приведенном ниже примере мы найдем и удалим любые дубликаты в выбранном диапазоне в Excel. Таким образом, рекомендуется сохранить копию таблицы данных или книги, поскольку дубликаты будут удалены без возможности восстановления.
Теперь рассмотрим приведенный ниже пример для понимания подхода.
Шаг 1: - Теперь выберите диапазон для таблицы данных, дубликаты которой необходимо удалить. Затем перейдите в раздел «Данные», выберите «Инструменты для работы с данными» и удалите дубликаты.
Шаг 2: - Рядом с всплывающим окном появится, затем по умолчанию выбраны оба заголовка, дубликаты которых необходимо удалить. Функция удалит дубликаты вместе с соответствующими им строками.
Теперь, чтобы выбрать все столбцы, установите флажок «Выбрать все», нажмите «Мои данные имеют заголовки», если первая таблица данных состоит из заголовков столбцов, и если не нужно выбирать столбцы или меньшее количество столбцов, нажмите « Отменить выбор всех », затем выберите необходимые столбцы, в которых необходимо удалить дубликаты. Затем нажмите ОК, чтобы выполнить.
Шаг 3: - Ниже желаемый результат для таблицы данных. Нажмите OK, чтобы отобразить подсказку, в которой приведены сведения о количестве обнаруженных дубликатов и уникальных значениях, оставшихся в таблице данных после удаления дубликатов.
Поиск повторяющихся значений в Excel с помощью «= СЧЁТЕСЛИ»
Рассмотрим следующую таблицу. Для функции = СЧЁТЕСЛИ требуется диапазон таблицы данных для соответствующего столбца и критерии для ячейки, дубликаты которой вы обнаруживаете в Excel.
Шаг 1: - Альтернативный подход - применить = СЧЁТЕСЛИ (диапазон столбцов, критерии ячеек). Эта функция помогает определить количество дубликатов для соответствующих ячеек, что позволит пользователю получить количество дубликатов для любого дальнейшего анализа и выводов.
Шаг 2: - Введите формулу и нажмите Enter, формулу необходимо перетащить до конца таблицы данных. Помните, что диапазон таблицы данных должен быть зафиксирован знаком доллара «$», иначе диапазон изменится на одну ячейку вниз при перетаскивании формулы вниз.
Если таблица данных очень велика по строкам, то лучший способ - удерживать курсор (выделен красной стрелкой) и дважды щелкнуть выемку в правом нижнем углу ячейки, где применяется формула в качестве альтернативы перетаскиванию формулы до конец.
Ниже приведен полный список количества дубликатов для общего набора данных.
После применения формулы вы можете применить фильтр к заголовку столбца и выбрать число больше 1, чтобы просмотреть множественное количество повторений.
То, что нужно запомнить
- Используйте условное форматирование, чтобы найти и выделить дубликаты в Excel. Новые правила в этом выборе позволят пользователю идентифицировать и выделять только определенное количество дубликатов с помощью формулы СЧЁТЕСЛИ.
- Удаление дубликатов на вкладке ДАННЫЕ помогает удалить любые дубликаты в таблице данных и сохранить только уникальное содержимое ячеек.
- Формула СЧЁТЕСЛИ в Excel используется для выделения дубликатов поиска, соответствующих ячейке соответствующего столбца. Это дополнительно помогает фильтровать любое конкретное событие в соответствии с необходимостью.