• Как убрать выделение повторяющихся значений в эксель


    Повторяющиеся значения в Excel - найти, выделить или удалить дубликаты в Excel

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

    1.     Удаление повторяющихся значений в Excel (2007+)

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

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

    Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

    2.     Использование расширенного фильтра для удаления дубликатов

    Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

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

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

    3.     Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

    4.     Использование сводных таблиц для определения повторяющихся значений

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

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

    Вам также могут быть интересны следующие статьи

    Фильтр уникальных значений или удаление повторяющихся значений

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

    • Чтобы отфильтровать уникальные значения, нажмите кнопку данные > отсортировать & фильтр > Дополнительно.

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

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

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

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

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

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

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

    2. Нажмите кнопку данные > дополнительно (в группе Фильтр сортировки & ).

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

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

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

    • Выберите команду скопировать в другое место.

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

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

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

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

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

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

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

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

    2. На вкладке данные нажмите кнопку Удалить дубликаты (в группе Работа с данными ).

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

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

      • Чтобы быстро выделить все столбцы, нажмите кнопку выделить все.

      • Чтобы быстро удалить все столбцы, нажмите кнопку снять выделение.

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

        Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то "Key", использованный для поиска дубликатов, — это значение обоих Столбец1 & Столбец2.  Если в этих столбцах обнаружена повторяющаяся копия, вся строка удаляется, в том числе другие столбцы в таблице или диапазоне.

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

    5. Чтобы изменить изменения, нажмите кнопку Отменить (или нажмите клавиши CTRL + Z на клавиатуре).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    2. На вкладке данные нажмите кнопку Удалить дубликаты .

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

      Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то "Key", использованный для поиска дубликатов, — это значение обоих Столбец1 & Столбец2.  При обнаружении дубликата в Столбец1 и Столбец2 вся строка будет удалена, включая данные из Столбец3.

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

    Примечание: Если вы хотите вернуться к данным, просто нажмите кнопку отменить (или нажмите клавиши CTRL + Z на клавиатуре).

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

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

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

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

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

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

    Удаление повторяющихся строк вручную

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

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

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

    Для этого выполните следующие действия.

    1. Для начала выберите диапазон, в котором вы хотите работать. Чтобы выделить всю таблицу, нажмите Ctrl + A,
    2. Указав диапазон, перейдите на вкладку «Данные» >  и нажмите кнопку « Удалить дубликаты» .
    1. Откроется диалоговое окно. Выберите столбцы для проверки на наличие дублей и нажмите кнопку «ОК».
      • Чтобы удалить повторяющиеся строки, которые имеют абсолютно одинаковые данные во всех колонках, оставьте флажки рядом со всеми столбцами, как на скриншоте ниже.
      • Чтобы удалить частичные совпадения на основе одного или нескольких ключевых столбцов, выберите только их. Если в вашей таблице много колонок, самый быстрый способ - нажать кнопку «Снять выделение». А затем отметить те, которые вы хотите проверить.
      • Ежели в вашей таблице нет заголовков, снимите флажок Мои данные в верхнем правом углу диалогового окна, который обычно включается по умолчанию.
    2. Если указать в диалоговом окне все столбцы, строка будет удалена только в том случае, если повторяются значения есть во всех них. Но в некоторых ситуациях не нужно учитывать данные, находящиеся в определенных колонках. Поэтому для них снимите флажки. К примеру, если каждая строчка содержит уникальный идентификационный код, программа никогда не найдет ни одной повторяющейся. Поэтому флажок рядом с колонкой с такими кодами следует снять.

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

    Важное замечание. Повторяющиеся значения определяются по тому, что отображается в ячейке, а не по тому, что в ней записано на самом деле. Представим, что в A1  и A2 содержится одна и та же дата. Одна из них представлена в формате 15.05.2020, а другая отформатирована в формате 15 май 2020. При поиске повторяющихся значений Excel считает, что это не одно и то же. Аналогично значения, которые отформатированы по-разному, считаются разными, поэтому $1 209,32 - это совсем не одно и то же, что 1209,32.

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

    Удаление дубликатов в «умной таблице».

    Думаю, вы знаете, что, если преобразовать диапазон ячеек в таблицу, в нашем распоряжении появляется множество интересных дополнительных возможностей по работе с этими данными. Именно по этой причине такую таблицу Excel называют «умной».

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

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

    Но, в отличие от ранее рассмотренного инструмента удаления, операцию можно отменить, если что-то пошло не так.

    Избавьтесь от повторов, скопировав уникальные записи в другое место.

    Еще один способ удалить повторы - это выбрать все уникальные записи и скопировать их на другой лист или в другую книгу. Подробные шаги следуют ниже.

    1. Выберите диапазон или всю таблицу, которую вы хотите обработать (1).
    2. Перейдите на вкладку «Данные» (2) и нажмите кнопку «Фильтр - Дополнительно» (3-4).
    3. В диалоговом окне «Расширенный фильтр» (5) выполните следующие действия:
      • Выберите переключатель скопировать в другое место (6).
      • Убедитесь, что в списке диапазонов указан правильный диапазон. Это должен быть диапазон из шага 1.
      • В поле «Поместить результат в…» (7) введите диапазон, в который вы хотите скопировать уникальные записи  (на самом деле достаточно указать его верхнюю левую ячейку).
      • Выберите только уникальные записи (8).
    4. Наконец, нажмите кнопку ОК, и уникальные значения будут скопированы в новое место:

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

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

    Как убрать дубликаты строк с помощью формул.

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

    Преимущество этого подхода заключается в универсальности: он позволяет вам:

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

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

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

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

    Добавляем еще одну колонку, в которой запишем формулу.

    Повторы наименований товаров, без учета первого вхождения:

    =ЕСЛИ(СЧЁТЕСЛИ($B$2:$B2; $B2)>1; "Дубликат"; "")

    Как видите, когда значение встречается впервые (к примеру, в B4), оно рассматривается как вполне обычное. А вот второе его появление (в B7) уже считается повтором.

    Отмечаем все повторы вместе с первым появлением:

    =ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$17; $B2)>1; "Дубликат"; "Уникальный")

    Где A2 - первая, а A10 - последняя ячейка диапазона, в котором нужно найти совпадения.

    Ну а теперь, чтобы убрать ненужное, устанавливаем фильтр и в столбце H и оставляем только «Дубликат». После чего строки, оставшиеся на экране, просто удаляем.

    Вот небольшая пошаговая инструкция.

    1. Выберите любую ячейку и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «Данные».
    2. Отфильтруйте повторяющиеся строки, щелкнув стрелку в заголовке нужного столбца.
    3. И, наконец, удалите повторы. Для этого выберите отфильтрованные строки, перетаскивая указатель мыши по их номерам, щелкните правой кнопкой мыши и выберите «Удалить строку» в контекстном меню. Причина, по которой вам нужно сделать это вместо простого нажатия кнопки «Удалить» на клавиатуре, заключается в том, что это действие будет удалять целые строки, а не только содержимое ячейки.

    Формулы для поиска повторяющихся строк.

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

    Отмечаем при помощи формулы неуникальные строчки, кроме 1- го вхождения:

    =ЕСЛИ(СЧЁТЕСЛИМН($B$2:$B2; $B2;$A$2:A2;$A2;$C$2:C2;$C2)>1; "Дубликат"; "")

    В результате видим 2 повтора.

    Теперь самый простой вариант действий – устанавливаем фильтр по столбцу H и слову «Дубликат». После этого просто удаляем сразу все отфильтрованные строки.

    Если нам нужно исключить все повторяющиеся строки вместе с их первым появлением:

    =ЕСЛИ(СЧЁТЕСЛИМН($B$2:$B$17; $B2;$A$2:$A$17;$A2;$C$2:$C$17;$C2)>1; "Дубликат"; "")

    Далее вновь устанавливаем фильтр и действуем аналогично описанному выше.

    Насколько удобен этот  метод – судить вам.

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

    Еще на эту же тему:

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

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

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

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

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

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

    Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)

    Для начала, во всех версиях Excel есть заготовка для выделения дублирующих ячеек. Чтобы использовать это её в своих таблицах, выполните следующие действия:

    1. Выберите данные, которые вы хотите проверить. Это может быть колонка, строка или диапазон ячеек.
    2. На вкладке « Главная » выберите « Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения».

    Откроется диалоговое окно с форматом «Светло-красная заливка» и «Темно-красный текст», выбранными по умолчанию. Чтобы применить формат по умолчанию, просто нажмите ОК.

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

    И вот что мы получили в итоге:

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

    При использовании этого инструмента имейте в виду следующие две вещи:

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

    Как выделить повторы без 1-го вхождения

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

    1. Выберите «Условное форматирование» > « Новое правило»> «Использовать формулу», чтобы определить ячейки для форматирования .
    2. В поле « Форматировать значения», где эта формула возвращает ИСТИНА , введите:

    =СЧЕТЕСЛИ($B$2:$B2;$B2)>1

    Где B2 - самая верхняя ячейка выбранного диапазона.

    1. Нажмите кнопку «Формат» выберите нужный цвет заливки и/или шрифта.
    2. Наконец, нажмите кнопку ОК, чтобы сохранить и применить созданное.

    Если у вас нет большого опыта работы с условным форматированием Excel, вы найдете подробные инструкции по созданию правила на основе формул в следующем руководстве:  Как изменить цвет ячейки в зависимости от значения? 

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

    Как показать третий, четвертый и все последующие дубликаты?

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

    Чтобы раскрасить третий и все последующие повторы, примените это:

    =СЧЕТЕСЛИ($B$2:$B2;$B2)>=3

    Чтобы указать на 4-е и все последующие повторяющиеся записи, используйте выражение:

    =СЧЕТЕСЛИ($B$2:$B2;$B2)>=4

    Чтобы выделить только определенные вхождения, используйте оператор равенства (=). 

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

    =СЧЕТЕСЛИ($B$2:$B2;$B2)=2

    Как выделить дубликаты в диапазоне

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

    Выделите дубликаты в таблице, включая 1-е  вхождение.

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

    Или создайте своё условное форматирование:

    =СЧЕТЕСЛИ(диапазонпервая_ячейка)> 1

    Например, чтобы отметить цветом совпадения в диапазоне A2: C8, сделайте это следующим образом:

    =СЧЕТЕСЛИ($A$2:$C$8, A2)>1

    Обратите внимание на использование абсолютных ссылок на ячейки для диапазона ($A$2:$C$8) и относительных ссылок для верхней ячейки (A2).

    Выделите повторы в таблице без 1-го вхождения.

    Решение для этого сценария намного сложнее. Не удивительно, что в Excel нет для него готового решения :)

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

    Правило 1. Относится к первому столбцу.

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

    В этом примере мы применяем для A2: A11 условие:

    =СЧЁТЕСЛИ($A$2:$A2;A2)>1

    В результате дублирующие элементы без 1- го вхождения выделяются в крайнем левом столбике диапазона (в нашем случае есть только два таких элемента):

    Правило 2. Относится ко всем последующим колонкам

    Чтобы выделить дубликаты в оставшихся данных (B2: C11), используйте следующую формулу:

    =СЧЁТЕСЛИ(A$2:$A$11;B2)+СЧЁТЕСЛИ(B$2:B2;B2)>1

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

    В результате все дублированные элементы, за исключением их 1- го вхождения, будут найдены и выделены:

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

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

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

    • Чтобы выделить дублирующиеся строки, исключая первые вхождения:

    =СЧЁТЕСЛИ($B$2:$B2;$B2)>1

    Выделяем строки с одинаковыми наименованиями товаров (столбец B)

    • Чтобы выделить повторяющиеся строки, включая все вхождения:

    = СЧЁТЕСЛИ ($B$2:$B$17;$B2)>1

    Здесь B2 - первая ячейка, а B17 - последняя, которую вы хотите проверить на наличие дублей. 

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

    Как выделить дублирующиеся строки в Excel

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

    Для этого используйте функцию СЧЁТЕСЛИМН, которая позволяет сравнивать по нескольким критериям. Например, чтобы выделить строки с одинаковыми значениями в B и C, то есть найти заказы одного и того же товара одним заказчиком, используйте одну из следующих формул:

    • Чтобы выделить совпадающие, кроме 1-го вхождения :

    =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1

    Обратите внимание, что форматирование мы применяем ко всей таблице.

    • Чтобы выделить все неуникальные:

    =СЧЁТЕСЛИМН($B$2:$B$17; $B2; $C$2:$C$17; $C2)>1

    Как вы понимаете, приведенный выше пример только для демонстрационных целей. При выделении дублирующихся строк в ваших реальных таблицах вы, естественно, не ограничены сравнением значений только в 2 столбцах. Функция СЧЁТЕСЛИМН может обрабатывать до 127 пар диапазон / критерий.

    Подсветка последовательных дубликатов ячеек в Excel

    Иногда вам может не потребоваться выделять все дубликаты в таблице, а отображать только последовательные, то есть те, которые расположены рядом друг с другом. 

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

    • Чтобы выделить два идущих друг за другом заказа одного и того же менеджера:

    =ИЛИ($G1=$G2;$G2=$G3)

    • Чтобы выделить второй заказ менеджера, если у него 2 заказа идут подряд:
      =$G1=$G2

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

    • при выделении только второго повтора

    =И($G2<>""; $G1=$G2)

    • при выделении обоих дублей:

    =И($G2<>""; ИЛИ($G1=$G2;$G2=$G3))

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

    Рекомендуем также:

    Как посчитать количество повторяющихся значений в Excel? - Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если… Как убрать повторяющиеся значения в Excel? - В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения. Хотя…

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

    Выделение повторяющихся значений в MS EXCEL

    ​Смотрите также​ какую вкладку добавлять​Insert - Module​

    Задача

    ​.Cells(Счетчик).Interior.ColorIndex = _​ с начала.​ ячейкой при вводе​Во всплывающем меню​На вкладке​Изменить правило​Стили​

    Решение

    • ​ ячейку.​ Значение считается повторяющимся,​​Выберите ячейки, которые нужно​​и выберите пункт​
    • ​Примечание:​фильтровать список на месте​.​Настроим Условное форматирование для​
    • ​ кнопки?​

    ​и скопируйте туда​rngЗаполненДанные.Find(what:=.Cells(Счетчик).Value, after:=.Cells(Счетчик), SearchDirection:=xlPrevious,​Поскольку лучше сделать так,​ формулы должна быть​​Стиль​​Данные​

    • ​.​щелкните​​Примечание:​​ если все значения​
    • ​ проверить на наличие​Управление правилами​ Данные будут удалены из​.​Чтобы удалить повторяющиеся значения,​
    • ​ выделения только повторяющихся​

    ​и можно ли​ код этого макроса:​ lookat:=xlWhole).Interior.ColorIndex​ чтобы ячейки обновлялись​A3​​выберите пункт​​в разделе​Выберите нужные параметры и​Условное форматирование​ При копировании результатов фильтрации​ в одной строке​ повторений.​, чтобы открыть​

    • ​ всех столбцов, даже​
    • ​Чтобы скопировать в другое​

    ​ нажмите кнопку​ значений в списке.​ немного изменить макрос​

    excel2.ru>

    Фильтр уникальных значений или удаление повторяющихся значений

    ​Sub DuplicatesColoring() Dim​​Else​ каждый раз, когда​(т.е. диапазон нужно​Классический​Сервис​ нажмите кнопку​, наведите указатель на​ в другое место​ полностью совпадают со​Примечание:​ всплывающее окно​ если вы не​ место результаты фильтрации:​данные > Работа с​Необходимо выделить ячейки, содержащие​ чтобы он охватывал​ Dupes() 'объявляем массив​.Cells(Счетчик).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex​ что-то вводится в​ выделять сверху вниз).​, а затем во​

    ​нажмите кнопку​ОК​ пункт​ будут скопированы уникальные​

    • ​ значениями в другой.​ В Excel не поддерживается​​Диспетчер правил условного форматирования​ ​ выбрали всех столбцов​Нажмите кнопку​​ данными​

    • ​ значения, которые повторяются​ конкретный заданный диапозон​​ для хранения дубликатов​СчетчикЦветов = СчетчикЦветов​ ​ ячейку, макрос обрабатывает​ ​ Активная ячейка в​​ всплывающем меню​

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

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

    ​Копировать в другое место​>​ в определенном диапазоне.​ и поставить событие​ ReDim Dupes(1 To​ + 1​ событие onChange на​ выделенном диапазоне –​Форматировать только первые или​.​Выделите диапазон ячеек или​и выберите​ диапазона. Исходные данные​ значением, которое отображается​ в области "Значения"​

    ​Выполните одно из действий,​ Например при выборе​.​Удалить повторения​ Выделять повторяющиеся значения​ при изменении содержимого​ Selection.Cells.Count, 1 To​If СчетчикЦветов >​ листе «Данные».​ белая, ее адрес​ последние значения​Установите один или несколько​ убедитесь в том,​Повторяющиеся значения​ при этом не​ в ячейке, а​ отчета сводной таблицы.​ указанных ниже.​ Столбец1 и Столбец2,​В поле​.​

    ​ будем с помощью​ любой ячейки в​​ 2) Selection.Interior.ColorIndex =​ rngЦвета.Count Then СчетчикЦветов​Макрос выглядит следующим образом:​ отображается в поле​выберите пункт​ флажков, соответствующих столбцам​ что активная ячейка​.​ изменятся.​ не тем, которое​

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

    ​На вкладке​

    1. ​Чтобы добавить условное форматирование,​ но не Столбец3​Копировать​Чтобы выделить уникальные или​

    2. ​ Условного форматирования (см.​​ данном диапозоне подсвечивание​​ -4142 'убираем заливку​​ = 1​​Option Explicit​​ Имя.​​Форматировать только уникальные или​

    3. ​ таблицы, и нажмите​​ находится в таблице.​​В диалоговом окне​Установите флажок​

    ​ в ней хранится.​Главная​ нажмите кнопку​

    ​ Файл примера).​ автоматом перерисовыволось?​

    • ​ если была i​​End If​​Private Sub Worksheet_Change(ByVal​

    • ​Выберите нужное форматирование;​​ повторяющиеся значения​​ кнопку​На вкладке​

    • ​Создать правило форматирования​​Только уникальные записи​

      ​ Например, если в​выберите​Создать правило​ дубликатов «ключ» —​​ ячейку.​ ​Условного форматирования​
    • ​выделите диапазон содержащий список​​БИТ​​ = 3 For​​End If​​ Target As Range)​

    ​Нажмите ОК​.​Удалить дубликаты​

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

    ​Данные​выберите нужные параметры​и нажмите кнопку​ различных ячейках содержатся​Условное форматирование​для отображения во​ значение ОБА Столбец1​Кроме того нажмите кнопку​в группе​ значени

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

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

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

    И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в одном списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и ниже.

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

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

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

    =СЧЁТЕСЛИ(A:A; A2)>1

    Где А2 - первая ячейка из области для поиска.

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

    Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если имеются совпадения.  А для встречающихся только 1 раз значений она показывает ЛОЖЬ.

    Подсказка! Если вы ищите повторы в определенной области, а не во всей колонке, обозначьте нужный диапазон и “зафиксируйте” его знаками $. Это значительно ускорит вычисления. Например, если вы ищете в A2:A8, используйте

    =СЧЕТЕСЛИ($A$2:$A$8, A2)>1

    Если вас путает ИСТИНА и ЛОЖЬ в статусной колонке и вы не хотите держать в уме, что из них означает повторяющееся, а что - уникальное, заверните свою СЧЕТЕСЛИ в функцию ЕСЛИ и укажите любое слово, которое должно соответствовать дубликатам и уникальным:

    =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;"Дубликат";"Уникальное")

    Если же вам нужно, чтобы формула указывала только на дубли, замените "Уникальное" на пустоту (""):

    =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;"Дубликат";"")

    В этом случае Эксель отметит только неуникальные записи, оставляя пустую ячейку напротив уникальных. 

    Поиск неуникальных значений без учета первых вхождений

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

    Если вам нужно указать только совпадения, давайте немного изменим:

    =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; A2)>1;"Дубликат";"")

    На скриншоте ниже вы видите эту формулу в деле.

    Нетрудно заметить, что она не обозначает первое появление слова, а начинает отсчет со второго.

    Чувствительный к регистру поиск дубликатов

    Хочу обратить ваше внимание на то, что хоть формулы выше и находят 100%-дубликаты, есть один тонкий момент - они не чувствительны к регистру. Быть может, для вас это не принципиально. Но если в ваших данных абв, Абв и АБВ - это три разных параметра – то этот пример для вас.

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

    {=ЕСЛИ(СУММ((--СОВПАД($A$2:$A$17;A2)))<=1;"";"Дубликат")}

    Не забывайте, что формулы массива вводятся комбиинацией Ctrl + Shift + Enter.

    Если вернуться к содержанию, то здесь используется функция СОВПАД для сравнения целевой ячейки со всеми остальными ячейками с выбранной области. Результат возвращается в виде ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из 1 и 0 при помощи оператора (--).

    После этого, функция СУММ складывает эти числа. И если полученный результат больше 1, функция ЕСЛИ сообщает о найденном дубликате.

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

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

    Обнаружение повторяющихся строк

    Мы рассмотрели, как обнаружить одинаковые данные в отдельных ячейках. А если нужно искать дубликаты-строки?

    Есть один метод, которым можно воспользоваться, если вам нужно просто выделить одинаковые строки, но не удалять их.

    Итак, имеются данные о товарах и заказчиках.

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

    Предположим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:

    =A2&B2&C2

    Добавим следующую формулу в ячейку E2. Она отобразит, сколько раз встречается значение, полученное нами в столбце D:

    =СЧЁТЕСЛИ(D:D;D2)

    Скопируем вниз для всех строк данных.

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

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

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

    Теперь рассмотрим, как можно обойтись без формул при поиске дубликатов в таблице. Быть может, кому-то этот метод покажется более удобным, нежели написание выражений Excel.

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

    В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.

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

    Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.

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

    Используем условное форматирование.

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

    Сейчас я покажу, как можно в Экселе найти дубли ячеек, просто их выделив цветом.

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

    Но здесь мы не можем исключить первые появления – подсвечивается всё.

    Но эту проблему можно решить, использовав формулу условного форматирования.

    =СЧЁТЕСЛИ($B$2:$B2; B2)>1

    Результат работы формулы выденения повторяющихся значений вы видите выше. Они выделены зелёным цветом.

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

    Поиск совпадений при помощи команды «Найти».

    Еще один простой, но не слишком технологичный способ – использование встроенного поиска.

    Зайдите на вкладку Главная и кликните «Найти и выделить». Откроется диалоговое окно, в котором можно ввести что угодно для поиска в таблице. Чтобы избежать опечаток, можете скопировать искомое прямо из списка данных.

    Затем нажимаем «Найти все», и видим все найденные дубликаты и места их расположения, как на рисунке чуть ниже.

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

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

    Как применить сводную таблицу для поиска дубликатов.

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

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

    Создаем новый макет сводной таблицы. А затем в качестве строк и значений используем одно и то же поле. В нашем случае – «Товар». Поскольку название товара – это текст, то для подсчета таких значений Excel по умолчанию использует функцию СЧЕТ, то есть подсчитывает количество. А нам это и нужно. Если будет больше 1, значит, имеются дубликаты.

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

    К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:

    Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.

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

    Еще о дубликатах:

    Как выделить цветом повторяющиеся значения в Excel? - В этом руководстве вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения дублирующих ячеек, целых строк или последовательных повторений с использованием условного форматирования.  Ранее мы исследовали различные… Как посчитать количество повторяющихся значений в Excel? - Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если… Как убрать повторяющиеся значения в Excel? - В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения. Хотя… Как проверить правильность ввода данных в Excel? - Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить…

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

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

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

      Примечание. Excel не может выделять дубликаты в области значений отчета сводной таблицы.

    2. Щелкните Домой > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения .

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

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

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

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

    2. Щелкните Data > Remove Duplicates , а затем в разделе Columns отметьте или снимите отметку со столбцов, в которых вы хотите удалить дубликаты.

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

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

    3. Щелкните ОК .

    .

    Фильтр для уникальных значений или удаление повторяющихся значений

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

    • Чтобы отфильтровать уникальные значения, щелкните Данные> Сортировка и фильтр> Расширенный .

    • Чтобы удалить повторяющиеся значения, щелкните Data> Data Tools > Remove Duplicates .

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

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

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

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

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

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

    2. Щелкните Data> Advanced (в группе Sort & Filter ).

    3. Во всплывающем окне Advanced Filter выполните одно из следующих действий:

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

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

    • Щелкните Копировать в другое место .

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

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

    • Отметьте только уникальных записей , затем нажмите ОК .

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

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

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

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

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

    2. На вкладке Data щелкните Remove Duplicates (в группе Data Tools ).

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

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

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

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

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

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

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

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

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

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

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

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

    2. На вкладке Home в группе Style щелкните маленькую стрелку для Conditional Formatting , а затем щелкните Highlight Cells Rules и выберите Duplicate Values ​​.

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

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

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

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

    2. На вкладке Home в группе Styles щелкните стрелку для Conditional Formatting , а затем щелкните Manage Rules , чтобы отобразить всплывающее окно Conditional Formatting Rules Manager .

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

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

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

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

    5. В списке Форматировать все для Отредактируйте описание правила , выберите уникальный или дубликат .

    6. Щелкните Format , чтобы отобразить всплывающее окно Format Cells .

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

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

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

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

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

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

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

    2. На вкладке Data щелкните Remove Duplicates .

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

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

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

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

    .

    Как удалить дубликаты в Excel

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

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

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

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

    Если вы используете последнюю версию Microsoft Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть небольшое преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов, называемую, что неудивительно, Remove Duplicates. .

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

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

    1. Для начала выберите диапазон, в котором вы хотите удалить дубликаты.Чтобы выделить всю таблицу, нажмите Ctrl + A.
    2. Выбрав диапазон, перейдите на вкладку Data > Data Tools и нажмите кнопку Remove Duplicates .
    3. Откроется диалоговое окно Удалить дубликаты , вы выберите столбцы для проверки дубликатов и нажмите ОК .
      • Чтобы удалить повторяющихся строк , которые имеют полностью равные значения во всех столбцах, оставьте галочки рядом со всеми столбцами, как на скриншоте ниже.
      • Чтобы удалить частичных дубликатов на основе одного или нескольких ключевых столбцов, выберите только те соответствующие столбцы. Если в вашей таблице много столбцов, самый быстрый способ - нажать кнопку Unselect All , а затем выбрать столбцы, которые нужно проверить на дублирование.
      • Если в вашей таблице нет заголовков , снимите флажок Мои данные имеют заголовки в правом верхнем углу диалогового окна, которое обычно выбирается по умолчанию.

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

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

    Избавьтесь от дубликатов, скопировав уникальные записи в другое место

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

    1. Выберите диапазон или всю таблицу, которую нужно вывести.
    2. Перейдите на вкладку Data > группу Sort & Filter и нажмите кнопку Advanced .
    3. В диалоговом окне Advanced Filter выполните следующие действия:
      • Установите переключатель Копировать в другое место .
      • Проверьте, отображается ли правильный диапазон в списке Диапазон Это должен быть диапазон, который вы выбрали на шаге 1.
      • В поле Копировать в введите диапазон, в который вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку целевого диапазона).
      • Выберите Только уникальные записи

    4. Наконец, нажмите ОК , и уникальные значения будут скопированы в новое место:
    Примечание. Расширенный фильтр Excel позволяет копировать отфильтрованные значения только в другое место на активном листе.Если вы хотите скопировать или , переместить уникальных значений или повторяющиеся строки на другой лист или другую книгу , вы можете легко сделать это с помощью нашего Duplicate Remover для Excel.

    Как удалить повторяющиеся строки в Excel с помощью фильтрации

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

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

    1. В зависимости от вашей задачи используйте одну из следующих формул для обнаружения дубликатов. Формулы для поиска повторяющихся значений в 1 столбце
      • Дубликаты, кроме 1 st : = IF (COUNTIF ($ A $ 2: $ A2, $ A2)> 1, "Duplicate", "")
      • Дубликаты с 1 st вхождения: = IF (COUNTIF ($ A $ 2: $ A $ 10, $ A2)> 1, «Дубликат», «Уникальный»)

      Где A2 - первая, а A10 - последняя ячейка диапазона, в котором производится поиск дубликатов.

      Формулы для поиска повторяющихся строк

      • Дублирующиеся строки, кроме 1 st вхождений: = IF (COUNTIFS ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2, $ C $ 2: $ C2, $ C2)> 1, «Повторяющаяся строка», «Уникальная»)
      • Повторяющиеся строки с 1 st вхождения: = IF (COUNTIFS ($ A $ 2: $ A $ 10, $ A2, $ B $ 2: $ B $ 10, $ B2, $ C $ 2: $ C $ 10, $ C2)> 1, «Повторяющаяся строка», «Уникальная»)

      Где A, B и C - столбцы, которые необходимо проверить на наличие повторяющихся значений.

      Например, вот как вы можете идентифицировать повторяющиеся строки, за исключением экземпляров 1 st :

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

    2. Выберите любую ячейку в таблице и примените автоматический фильтр Excel, нажав кнопку Фильтр на вкладке Data или Сортировка и фильтр > Фильтр на вкладке Home .
    3. Отфильтруйте повторяющиеся строки, щелкнув стрелку в заголовке столбца « Duplicate », а затем отметив поле « Duplicate row ».Если кому-то нужны более подробные инструкции, см. Как отфильтровать дубликаты в Excel.
    4. И, наконец, удалите повторяющиеся строки. Для этого выберите отфильтрованные строки, перетащив указатель мыши по номерам строк, щелкните их правой кнопкой мыши и выберите Удалить строку из контекстного меню. Причина, по которой вам нужно сделать это вместо простого нажатия кнопки Удалить на клавиатуре, заключается в том, что он удалит целые строки, а не только содержимое ячейки:

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

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

    Duplicate Remover - универсальный инструмент для поиска и удаления дубликатов в Excel

    В отличие от встроенной функции Excel Remove Duplicate, надстройка Ablebits Duplicate Remover не ограничивается только удалением повторяющихся записей. Подобно швейцарскому ножу, этот многофункциональный инструмент сочетает в себе все основные варианты использования и позволяет идентифицировать , выбрать , выделить , удалить , скопировать и переместить уникальных или повторяющихся значений, абсолютно повторяющиеся строки или частично совпадающие строки, в 1 таблице или путем сравнения 2 таблиц, с первыми вхождениями или без них.

    Безупречно работает на всех операционных системах и во всех версиях Microsoft Excel 2019–2003.

    Как избавиться от дубликатов в Excel двумя щелчками мыши

    Предполагая, что в Excel установлен наш Ultimate Suite, выполните следующие простые шаги, чтобы удалить повторяющиеся строки или ячейки:

    1. Выберите любую ячейку в таблице, которую вы хотите вывести, и нажмите кнопку Dedupe Table на вкладке Ablebits Data .Вся ваша таблица будет выбрана автоматически.
    2. Откроется диалоговое окно Dedupe Table , в котором по умолчанию будут выбраны все столбцы. Вы выбираете Удалить дубликаты из Выберите действие в раскрывающемся списке и нажмите ОК . Выполнено!

    Как вы можете видеть на следующем снимке экрана, все повторяющиеся строки, кроме первых вхождений , удалены:

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

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

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

    Как найти и удалить повторяющиеся значения с 1 st или без них

    Удаление дубликатов в Excel - обычная операция. Однако в каждом конкретном случае может быть ряд особенностей. В то время как инструмент Dedupe Table фокусируется на скорости, Duplicate Remover предлагает ряд дополнительных опций для вывода ваших таблиц Excel именно так, как вы хотите.

    1. Выберите любую ячейку в таблице, из которой вы хотите удалить дубликаты, перейдите на вкладку Ablebits Data и нажмите кнопку Duplicate Remover .
    2. Мастер Duplicate Remover запустится, и будет выбрана вся таблица. Надстройка также предложит создать резервную копию, и, поскольку вы собираетесь удалить дубликаты без возможности восстановления, мы настоятельно рекомендуем вам установить этот флажок. Убедитесь, что таблица выбрана правильно, и нажмите Далее .
    3. Выберите, какие записи вы хотите найти и удалить. Вам доступны следующие варианты:
      • Дубликаты, кроме 1 st экземпляров
      • Дубликаты в том числе 1 st вхождения
      • Уникальные значения
      • Уникальные значения и 1 st повторяющиеся вхождения

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

    4. А теперь выберите столбцы для поиска дубликатов.Поскольку наша цель - удалить повторяющиеся строки, обязательно выберите все столбцы (что обычно и делается по умолчанию).
    5. Наконец, выберите действие, которое вы хотите выполнить с дублированием, и нажмите кнопку Finish . В этом примере мы ожидаемо выбрали опцию Удалить повторяющиеся значения .

    Вот и все! Надстройка Duplicate Remover быстро выполняет свою работу и уведомляет вас, сколько повторяющихся строк было найдено и удалено:

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

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

    Если вам нравится надстройка Duplicate Remover и вы решили получить лицензию, мы рады сделать вам это эксклюзивное предложение:

    Промокод для Ultimate Suite - специальное предложение для читателей нашего блога!

    Вас также может заинтересовать

    .

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

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

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

      Примечание. Excel не может выделять дубликаты в области значений отчета сводной таблицы.

    2. Щелкните Домой > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения .

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

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

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

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

    2. Щелкните Data > Remove Duplicates , а затем в разделе Columns отметьте или снимите отметку со столбцов, в которых вы хотите удалить дубликаты.

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

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

    3. Щелкните ОК .

    .

    7 способов найти и удалить повторяющиеся значения в Microsoft Excel

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

    Но найти и удалить их из данных в Excel на самом деле довольно просто.

    В этом руководстве мы рассмотрим 7 различных методов поиска и удаления повторяющихся значений из ваших данных.

    Видеоурок

    Что такое повторяющееся значение?

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

    Для заданного набора данных вы можете определять дубликаты разными способами.

    В приведенном выше примере есть простой набор данных с 3 столбцами для марки, модели и года для списка автомобилей.

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

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

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

    Удаление повторяющихся значений в данных - очень распространенная задача. Это настолько распространено, что на ленте есть специальная команда.

    Выберите ячейку внутри данных, из которой вы хотите удалить дубликаты, перейдите на вкладку Data и щелкните команду Remove Duplicates .

    Excel выберет весь набор данных и откроет окно «Удалить дубликаты».

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

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

    Эта команда изменит ваши данные, поэтому лучше всего выполнить команду с копией ваших данных, чтобы сохранить исходные данные без изменений.

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

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

    Выберите ячейку внутри данных, перейдите на вкладку Data и щелкните команду Advanced filter.

    Откроется окно расширенного фильтра.

    1. Вы можете выбрать: Отфильтровать список на месте или Копировать в другое место . Фильтрация списка на месте скроет строки, содержащие любые дубликаты, а при копировании в другое место будет создана копия данных.
    2. Excel угадывает диапазон данных, но вы можете настроить его в диапазоне List . Диапазон критериев можно оставить пустым, а поле Копировать в необходимо будет заполнить, если была выбрана опция Копировать в другое место .
    3. Установите флажок Только уникальные записи .

    Нажмите OK , и вы удалите повторяющиеся значения.

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

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

    Сводные таблицы предназначены только для анализа ваших данных, верно?

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

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

    Сначала создайте сводную таблицу на основе ваших данных.Выберите ячейку внутри ваших данных или весь диапазон данных ➜ перейдите на вкладку Insert ➜ выберите PivotTable ➜ нажмите OK в диалоговом окне Create PivotTable.

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

    Затем вам нужно будет изменить макет итоговой сводной таблицы, чтобы она была в табличном формате. Выбрав сводную таблицу, перейдите на вкладку Design и выберите Report Layout .Здесь вам нужно изменить два параметра.

    1. Выберите опцию Показать в табличной форме .
    2. Выберите опцию Повторить все метки элементов .

    Вам также потребуется удалить все промежуточные итоги из сводной таблицы. Перейдите на вкладку Design ➜ выберите Промежуточные итоги ➜ выберите Не показывать промежуточные итоги .

    Теперь у вас есть сводная таблица, которая имитирует набор данных в виде таблицы!

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

    Найдите и удалите повторяющиеся значения с помощью Power Query

    Power Query - это преобразование данных, поэтому вы можете быть уверены, что он может находить и удалять повторяющиеся значения.

    Выберите таблицу значений, из которой вы хотите удалить дубликаты ➜ перейдите на вкладку Data ➜ выберите запрос From Table / Range .

    Удаление дубликатов на основе одного или нескольких столбцов

    С помощью Power Query вы можете удалять дубликаты на основе одного или нескольких столбцов в таблице.

    Вам необходимо выбрать столбцы для удаления дубликатов. Вы можете удерживать Ctrl, чтобы выбрать несколько столбцов.

    Щелкните правой кнопкой мыши заголовок выбранного столбца и выберите в меню Удалить дубликаты .

    Вы также можете получить доступ к этой команде из вкладки Home Remove Rows Remove Duplicates .

      = Table.Distinct (# "Предыдущий шаг", {"Марка", "Модель"})  

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

    Удалить дубликаты на основе всей таблицы

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

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

    Щелкните кнопку таблицы в верхнем левом углу ➜ затем выберите Удалить дубликаты .

      = Table.Distinct (# "Предыдущий шаг")  

    Если вы посмотрите на созданную формулу, она использует ту же функцию Table.Distinct без второго параметра. Без второго параметра функция будет действовать для всей таблицы.

    Хранить дубликаты в одном столбце или во всей таблице

    В Power Query также есть команды для сохранения дубликатов для выбранных столбцов или для всей таблицы.

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

    Найдите и удалите повторяющиеся значения с помощью формулы

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

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

      = [@Make] & [@Model] & [@Year]  

    Приведенная выше формула объединит все три столбца в один столбец. Он использует оператор амперсанда для соединения каждого столбца.

      = TEXTJOIN ("", FALSE, CarList [@ [Make]: [Year]])  

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

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

      = СЧЁТЕСЛИМН ($ E $ 3: E3, E3)  

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

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

    Добавьте фильтры в список данных.

    • Перейдите на вкладку Data и выберите команду Filter .
    • Используйте сочетание клавиш Ctrl + Shift + L.

    Теперь вы можете фильтровать по столбцу Счетчик. Фильтрация по 1 произведет все уникальные значения и удалит все дубликаты.

    Затем вы можете выбрать видимые ячейки из полученного фильтра для копирования и вставки в другое место. Используйте сочетание клавиш Alt +; для выбора только видимых ячеек.

    Найти и удалить повторяющиеся значения с условным форматированием

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

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

    Затем вам нужно выбрать столбец объединенных данных.

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

    Откроется окно условного форматирования повторяющихся значений.

    1. Вы можете выделить Дубликат или Уникальный значения.
    2. Вы также можете выбрать один из предопределенных форматов ячеек, чтобы выделить значения, или создать свой собственный формат.

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

    Теперь, когда значения выделены, вы можете фильтровать повторяющиеся или уникальные значения с помощью параметра фильтрации по цвету. Обязательно добавьте фильтры к своим данным. Перейдите на вкладку Data и выберите команду Filter или используйте сочетание клавиш Ctrl + Shift + L.

    1. Щелкните переключатель фильтра.
    2. Выберите в меню Фильтр по цвету .
    3. Отфильтруйте цвет, используемый в условном форматировании, чтобы выбрать повторяющиеся значения, или отфильтруйте Без заливки, чтобы выбрать уникальные значения.

    Затем вы можете выбрать только видимые ячейки с помощью сочетания клавиш Alt +;.

    Найдите и удалите повторяющиеся значения с помощью VBA

    В VBA есть встроенная команда для удаления дубликатов в объектах списка.

      Sub RemoveDuplicates ()   Dim DuplicateValues ​​As Range   Set DuplicateValues ​​= ActiveSheet.ListObjects ("CarList"). Диапазон   DuplicateValues.RemoveDuplicates Columns: = Array (1, 2, 3  xl20), Заголовок: Концевой переходник  

    Приведенная выше процедура удалит дубликаты из таблицы Excel с именем CarList .

      Столбцы: = Массив (1, 2, 3)  

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

      Заголовок: = xl Да  

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

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

    Выводы

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

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

    Итак, каков ваш метод удаления дубликатов?

    .

    Как удалить дубликаты в Excel

    В этом примере показано, как удалить дубликаты в Excel .

    1. Щелкните любую ячейку в наборе данных.

    2. На вкладке «Данные» в группе «Работа с данными» щелкните Удалить дубликаты.

    Появится следующее диалоговое окно.

    3. Оставьте все флажки установленными и нажмите OK.

    Результат. Excel удаляет все идентичные строки (синие), кроме первой найденной идентичной строки (желтая).

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

    4. Например, удалите строки с одинаковыми фамилией и страной.

    5. Отметьте Фамилию и Страна и нажмите ОК.

    Результат. Excel удаляет все строки с одинаковой фамилией и страной (синий), за исключением первых найденных экземпляров (желтый).

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

    6. На вкладке «Данные» в группе «Сортировка и фильтр» щелкните «Дополнительно».

    Появится диалоговое окно Advanced Filter.

    7. Щелкните Копировать в другое место.

    8. Щелкните в поле Диапазон списка и выберите диапазон A1: A17 (см. Изображения ниже).

    9. Щелкните поле Копировать в и выберите ячейку F1 (см. Изображения ниже).

    10. Отметьте только уникальные записи.

    11. Щелкните OK.

    Результат. Excel удаляет все повторяющиеся фамилии и отправляет результат в столбец F.

    Примечание: на шаге 8 вместо выбора диапазона A1: A17 выберите диапазон A1: D17 для извлечения уникальных строк.

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

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

    Совет: посетите нашу страницу о поиске дубликатов, чтобы узнать больше об этих приемах.

    .

    Смотрите также

© 2020 nya-shka.ru Дорогие читатели уважайте наш труд, не воруйте контент. Ведь мы стараемся для вас!