Как удалить дубликаты в Excel 2003/2007/2010/2013? Быстрое удаление дубликатов

image

VBA Удалить дубликаты

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

Как использовать Excel VBA Удалить дубликаты?

Мы научимся использовать VBA Remove Duplicates с несколькими примерами в Excel.

Вы можете скачать этот шаблон Excel для удаления дубликатов здесь — VBA Удалить шаблон Excel для дубликатов

Пример № 1 — VBA удаляет дубликаты

У нас есть список чисел, начиная с 1 по 5 и до строки 20 только в столбце А. Как мы видим на скриншоте ниже, все числа повторяются несколько раз.

Теперь наша задача — удалить дубликаты из списка с помощью VBA. Для этого перейдите в окно VBA, нажав клавишу F11.

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

Шаг 1: Откройте новый модуль из меню «Вставка», которое находится на вкладке меню «Вставка».

Шаг 2: После открытия напишите подкатегорию VBA Remove Duplicate, как показано ниже.

Код:

 Sub VBARemoveDuplicate1 () End Sub 

Шаг 3: В процессе удаления дубликата, сначала нам нужно выбрать данные. Для этого в VBA мы будем использовать функцию Selection до тех пор, пока она не опустится до полного списка данных, как показано ниже.

Код:

 Sub VBARemoveDuplicate1 () Selection.End (xlDown). Выберите End Sub 

Шаг 4: Теперь мы выберем Диапазон выбранных ячеек или столбцов А. Он будет понижаться, пока у нас не будет данных в определенном столбце. Не только до 20-го ряда.

Код:

 Sub VBARemoveDuplicate1 () Selection.End (xlDown). Выбрать диапазон (Selection, Selection.End (xlUp)). Выбрать End Sub 

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

Код:

 Sub VBARemoveDuplicate1 () Selection.End (xlDown). Выберите диапазон (Selection, Selection.End (xlUp)). Выберите ActiveSheet.Range ("A: A"). End Sub 

Шаг 6: Теперь используйте функцию RemoveDuplicate здесь. Это активирует команду для удаления повторяющихся значений из последовательности столбцов 1. Если столбцов больше, число будет добавлено и разделено запятыми в скобках как (1, 2, 3, …).

Код:

 Sub VBARemoveDuplicate1 () Selection.End (xlDown). Выберите диапазон (Selection, Selection.End (xlUp)). Выберите ActiveSheet.Range ("A: A"). RemoveDuplicates Столбцы: = 1, End Sub 

Шаг 7: Теперь мы будем использовать команду «Заголовок», которая переместит курсор в самую верхнюю ячейку листа, которая в основном находится в заголовке любой таблицы.

Код:

 Sub VBARemoveDuplicate1 () Selection.End (xlDown). Выберите диапазон (Selection, Selection.End (xlUp)). Выберите ActiveSheet.Range ("A: A"). RemoveDuplicates Столбцы: = 1, Заголовок: = xlYes End Sub 

Шаг 8: Теперь скомпилируйте шаги кода, нажав клавишу F8. После этого нажмите кнопку Play, чтобы запустить код, как показано ниже.

Как мы видим, дубликат числа удаляется из столбца A, и остается только уникальный счет.

Пример №2 — VBA удаляет дубликаты

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

Это еще один метод с немного другим типом структуры кода.

Шаг 1: Откройте новый модуль в VBA и запишите подкатегорию в VBA Remove Duplicate. Если возможно, тогда дайте ему порядковый номер, чтобы было лучше выбрать правильный код для запуска.

Код:

 Sub VBARemoveDuplicate2 () End Sub 

Шаг 2: Сначала выберите полный лист в VBA, как показано ниже.

Код:

 Sub VBARemoveDuplicate2 () Cells.Select End Sub 

Шаг 3: Теперь выберите текущий открытый лист с помощью команды ActiveSheet и выберите столбцы от A до C, как показано ниже.

Код:

 Sub VBARemoveDuplicate2 () Cells.Select ActiveSheet.Range ("A: C"). End Sub 

Шаг 4: Теперь выберите команду RemoveDuplicates и после этого выберите массив столбцов от 1 до 3, как показано ниже.

Код:

 Sub VBARemoveDuplicate2 () Cells.Select ActiveSheet.Range ("A: C"). RemoveDuplicates Columns: = Array (1, 2, 3), End Sub 

Шаг 5: При последнем использовании команда Header должна быть включена в процесс удаления дубликатов в виде xlYes, как показано ниже.

Код:

 Sub VBARemoveDuplicate2 () Cells.Select ActiveSheet.Range ("A: C"). RemoveDuplicates Столбцы: = Array (1, 2, 3), Заголовок: = xlYes End Sub 

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

Пример № 3 — VBA удаляет дубликаты

Это еще один метод удаления дубликатов, который является самым простым способом удаления дубликатов в VBA. Для этого мы будем использовать данные, которые мы видели в примере-1, а также показаны ниже.

Шаг 1: Теперь перейдите к VBA и снова напишите подкатегорию VBA Remove Duplicates. Мы дали последовательность для каждого кода, который мы показали, чтобы иметь правильную дорожку.

Код:

 Sub VBARemoveDuplicate3 () End Sub 

Шаг 2: Это довольно похожий шаблон, который мы видели в примере 2, но это краткий способ написания кода для удаления дубликатов. Для этого сначала начните с выбора диапазона столбца, как показано ниже. Мы сохранили ограничение до 100- й ячейки столбца A, начиная с 1, за которым следует точка (.)

Код:

 Sub VBARemoveDuplicate3 () Range ("A1: A100"). End Sub 

Шаг 3: Теперь выберите команду RemoveDuplicates, как показано ниже.

Код:

 Sub VBARemoveDuplicate3 () Диапазон ("A1: A100"). RemoveDuplicates End Sub 

Шаг 4: Теперь выберите столбцы A, как с командой Columns с последовательностью 1. И после этого включите Заголовок выбранных столбцов, как показано ниже.

Код:

 Sub VBARemoveDuplicate3 () Range ("A1: A100"). RemoveDuplicates Столбцы: = 1, Заголовок: = xlYes End Sub 

Шаг 5: Теперь скомпилируйте его, нажав клавишу F8, и запустите. Мы увидим, что наш код удалил дубликаты чисел из столбцов A, и только уникальные значения.

Плюсы VBA Удалить дубликаты

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

Минусы VBA Удалить дубликаты

  • Использовать VBA Remove Duplicates для очень маленьких данных нецелесообразно, так как это можно легко сделать с помощью функции Remove Duplicate, доступной в строке меню Data.

То, что нужно запомнить

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

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

Это руководство по удалению дубликатов в VBA. Здесь мы обсудили, как использовать Excel VBA Remove Duplicates вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

  1. Работа с VBA Active Cell
  2. Удаление строки в VBA
  3. Как использовать Excel VBA Transpose?
  4. Как исправить ошибку 1004 с помощью VBA

Доброго времени суток!

С популяризацией компьютеров за последние 10 лет — происходит и популяризация создания отчетов (документов) в программе Excel.

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

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

Примечание: все примеры ниже будут представлены в Office 2016/2019 (актуально также для Office 2013, 2010, 2007). Рекомендую всегда использовать относительно новые версии Office: в них и быстрее работать, и проще.

*

Метод 1: удаление дублирующихся строк вручную

Смотрите также: “Как убрать колонтитулы в Экселе”

Читайте также:  Наложение текста на видео: как создать статичную или анимированную надпись в ролике

Первый метод максимально прост и предполагает удаление дублированных строк при помощи специального инструмента на ленте вкладки “Данные”.

  1. Полностью выделяем все ячейки таблицы с данными, воспользовавшись, например, зажатой левой кнопкой мыши.
  2. Во вкладке “Данные” в разделе инструментов “Работа с данными” находим кнопку “Удалить дубликаты” и кликаем на нее.
  3. Переходим к настройкам параметров удаления дубликатов:
    • Ниже, в основном окне, перечислены названия столбцов, по которым будет осуществляться поиск дубликатов. Система считает совпадением ситуацию, в которой в строках повторяются значения всех выбранных в настройке столбцов. Если убрать часть столбцов из сравнения, повышается вероятность увеличения количества похожих строк.
    • Тщательно все проверяем и нажимаем ОК.
    • Далее программа Эксель в автоматическом режиме найдет и удалит все дублированные строки.
    • По окончании процедуры на экране появится соответствующее сообщение с информацией о количестве найденных и удаленных дубликатов, а также о количестве оставшихся уникальных строк. Для закрытия окна и завершения работы данной функции нажимаем кнопку OK.

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

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

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

    Создаем сводную таблицу Обратите внимание на скриншот, где показан пример такого метода.

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

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

    Метод 2: удаление повторений при помощи “умной таблицы”

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

    1. Для начала, нам нужно выделить всю таблицу, как в первом шаге предыдущего раздела.
    2. Во вкладке “Главная” находим кнопку “Форматировать как таблицу” (раздел инструментов “Стили“). Кликаем на стрелку вниз справа от названия кнопки и выбираем понравившуюся цветовую схему таблицы.
    3. После выбора стиля откроется окно настроек, в котором указывается диапазон для создания “умной таблицы“. Так как ячейки были выделены заранее, то следует просто убедиться, что в окошке указаны верные данные. Если это не так, то вносим исправления, проверяем, чтобы пункт “Таблица с заголовками” был отмечен галочкой и нажимаем ОК. На этом процесс создания “умной таблицы” завершен.
    4. Далее приступаем к основной задаче – нахождению задвоенных строк в таблице. Для этого:
      • переключаемся во вкладку “Конструктор” (если после создания “умной таблицы” переход не был осуществлен автоматически);
      • в разделе “Инструменты” жмем кнопку “Удалить дубликаты“.
      • Следующие шаги полностью совпадают с описанными в методе выше действиями по удалению дублированных строк.

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

      Читайте также:  Как восстановить несохраненные документы Microsoft Word

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

      Другой способ удалить дубликаты в Excel — это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.

      1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
      2. Перейдите во вкладку «ДАННЫЕ» —> группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно».
      Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов
      1. В диалоговом окне «Расширенный фильтр» выполните следующие действия:
      • Выберите пункт «скопировать результат в другое место».
      • Проверьте, отображается ли правильный диапазон в Исходном диапазоне. Это должен быть диапазон, выбранный на шаге 1.
      • В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
      • Выберите Только уникальные записи
      Удалить дубликаты в Excel – Фильтр дубликатов
      1. Наконец, нажмите «ОК». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:
      Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места

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

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

      Метод 3: использование фильтра

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

      1. Как обычно, выделяем все ячейки таблицы.
      2. Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” ищем кнопку “Фильтр” (иконка напоминает воронку) и кликаем на нее.
      3. После этого в строке с названиями столбцов таблицы появятся значки перевернутых треугольников (это значит, что фильтр включен). Чтобы перейти к расширенным настройкам, жмем кнопку “Дополнительно“, расположенную справа от кнопки “Фильтр“.
      4. В появившемся окне с расширенными настройками:
        • отмечаем галочкой пункт “Только уникальные записи“;
        • жмем ОК.
        • После этого все задвоенные данные перестанут отображаться в таблицей. Чтобы вернуться в стандартный режим, достаточно снова нажать на кнопку “Фильтр” во вкладке “Данные”.

        Метод 4: условное форматирование

        Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая инструкция”

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

        1. Выделяем все ячейки нашей таблицы.
        2. Во вкладке “Главная” кликаем по кнопке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
        3. Откроется перечень, в котором выбираем группу “Правила выделения ячеек“, а внутри нее – пункт “Повторяющиеся значения“.
        4. Окно настроек форматирования оставляем без изменений. Единственный его параметр, который можно поменять в соответствии с собственными цветовыми предпочтениями – это используемая для заливки выделяемых строк цветовая схема. По готовности нажимаем кнопку ОК.
        5. Теперь все повторяющиеся ячейки в таблице “подсвечены”, и с ними можно работать – редактировать содержимое или удалить строки целиком любым удобным способом. Читайте также:  Как активировать Office 365: все способы активации

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

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

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

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

        Пример дневного журнала заказов на товары:

        Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

        1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
        2. Вберете «Использовать формулу для определения форматируемых ячеек».
        3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
        4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

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

        Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

        Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

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

        После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

        Метод 5: формула для удаления повторяющихся строк

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

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

        =ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));»»)

        Давайте посмотрим, как с ней работать на примере нашей таблицы:

        1. Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
        2. В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»»).
        3. Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
          • жмем служебную клавишу F2 на клавиатуре;
          • затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
          • Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.

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

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

          Содержание

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

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

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

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

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

          1. Выберите ячейки, которые вы хотите проверить на наличие дубликатов.
          2. На вкладке Главная нажмите Условное форматирование в группе Стили .
          3. Выберите Правила выделения ячеек> Повторяющиеся значения .
          4. Появится всплывающее окно, в котором можно выбрать стиль формат для повторяющихся ячеек.
          5. По завершении нажмите ОК .

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

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

          Как видите, этот метод не идеален.

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

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

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

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

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

          Формула, которую мы хотим ввести:

            COUNTIFS ($ A $ 2: $ A $ 14, $ A2, $ B $ 2: $ B  $ 14, $ B2, $ C $ 2: $ C $ 14, $ C2)> 1  

          Давайте подробнее рассмотрим эту формулу.

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

          Чтобы проверить строки на наличие дубликатов, формула сначала выбирает столбец ($ A $ 2: $ A $ 14). Мы используем абсолютное местоположение, потому что хотим, чтобы все использовали один и тот же диапазон ячеек при оценке строк на наличие дубликатов.

          Следующая переменная — это цель, которую мы проверяем на наличие повторяющегося значения ($ A2). На этот раз для столбца используется абсолютное расположение, но не для строки. Это позволяет нашей формуле постепенно проверять каждую строку.

          Мы повторяем это для каждого столбца в строке.

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

          После этого наша таблица выглядела так:

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

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

          Удаление дубликатов в Excel очень просто. На вкладке Данные в группе Инструменты для работы с данными вы найдете параметр Удалить дубликаты . Этот инструмент очень удобен, поскольку позволяет вам выбрать, какие столбцы вы хотите проверить.

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

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

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

          Использование инструмента Excel «Удалить дубликаты» автоматически удаляет повторяющиеся записи.

          Связано: Как легко удалить пустые строки в Excel

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

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

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

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

          1. Выберите данные, которые вы хотите фильтровать.
          2. Нажмите Дополнительно в группе Сортировка и фильтр .
          3. Во всплывающем окне включите параметр, чтобы отображать Только уникальные записи .
          4. Нажмите ОК .

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

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

          На самом деле, если вы посмотрите на номера строк, вы можете увидеть, где спрятаны две строки. Строки перескакивают с семи на девять и снова переходят между 12 и 14. Данные не исчезли, они просто скрылись из виду.

          Обзор способов удаления дубликатов

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

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

          Как найти и удалить повторы и дубликаты в Excel

          Categories: Поиск/ Работа с текстом/ Работа с ячейками

          Распространенный вопрос: как найти и удалить дубликаты в Excel. Предположим, вы выгрузили месячный отчет из вашей учетной системы, но в итоге вам нужно понять какие контрагенты вообще взаимодействовали с компанией за этот период — составить список контрагентов без повтарений. Как отобрать уникальные значения?

          1. Как проще всего удалить дубликаты в таблице Excel

          Можно ли удалить задвоеные, затроенные и так далее значения в Excel по нескольким столбцам?

          Можно, причем очень просто. Для этого есть специальная функция. Предварительно выберите диапазон, где нужно удалять дубликаты. На ленте заходим Данные — Удалить дубликаты (смотрите картинку в начале статьи).

          Далее будет предложено выбрать столбцы, по которым будут искаться дубликаты. После выбора столбцов — жмете ОК.

          Пример, таблица:

          Выбираем первый столбец

          Получаем:

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

          Очень удобно!

          2. Как выделить все дубликаты в Excel?

          Уже слышали про Условное форматирование? Здесь оно тоже поможет! Выделяете столбец, в котором надо пометить дубликаты, выбираете в меню Главное — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения…

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

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

          Отмечу, что инструмент применяется только для выбранного одного (!) столбца.

          Кстати, если нужно увидеть уникальные, то в окне слева выберите — уникальные.

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

          Признаюсь честно, когда-то я не подозревал о существовании возможности «удалить дубликаты» и пользовался сводными таблицами. Как я это делал? Выделяете таблицу, в которой надо найти уникальные значения — Вставка — Сводная таблица — Выбираете нужный столбец из открывшегося списка (перетащите в область по строкам).

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

          4. Как посчитать кол-во повторяющихся значений?

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

          Поделитесь нашей статьей в ваших соцсетях:

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

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

          Как найти и выделить

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

          image

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

          image

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

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

          image

          Как удалить

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

          image

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

          image

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

          image

          Второй способ для удаления дубликатов – это использование фильтра. Выделяем нужные столбцы вместе с шапкой. Переходим на вкладку «Данные» и в группе «Сортировка и фильтр» нажимаем на кнопочку «Дополнительно».

          Читайте еще:  Как настроить голосовой поиск Яндекс

          image

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

          image

          Будет создана новая таблица, в которой не будет строк с повторами информации.

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

          Как посчитать

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

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

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

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

          Справой стороны перетаскиваем первые три заголовка в область «Названия строк», а поле «Код» перетаскиваем в область «Значения».

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

          Для удобства, выделим все значения в столбце «Сумма по полю Код», и отсортируем их в порядке убывания.

          Читайте еще:  Папка prefetch в Windows 7

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

          Загрузка…  

          Оцените статью
          Рейтинг автора
          5
          Материал подготовил
          Илья Коршунов
          Наш эксперт
          Написано статей
          134
          А как считаете Вы?
          Напишите в комментариях, что вы думаете – согласны
          ли со статьей или есть что добавить?
          Добавить комментарий