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

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

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

Задачу удаления дубликатов или повторяющихся значений в Excel можно решать различными способами. В Excel 2007 и выше удалить дубликаты можно стандартными средствами, в Excel 2003 такие средства отсутствуют, но задача решается при помощи VBA (Visual Basic for Application).

Удаление дубликатов в Excel 2003

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

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

Для того чтобы ячейки удалялись со сдвигом влево, необходимо в предпоследней строке вместо xlUp написать xlToLeft.

Удаление дубликатов в Excel 2007/2010/2013

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

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

Если запустить макрорекордер и записать действие, закрепленное за кнопкой "Удалить дубликаты", получится макрос, программный код которого приведен ниже. Этот макрос удаляет дубликаты в диапазоне A1:A20.

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

Поиск и удаление дубликатов в Excel: 5 методов

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

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

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

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

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

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

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

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

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

      4. Далее приступаем к основной задаче – нахождению задвоенных строк в таблице. Для этого:
        • ставим курсор на произвольную ячейку таблицы;
        • переключаемся во вкладку “Конструктор” (если после создания “умной таблицы” переход не был осуществлен автоматически);
        • в разделе “Инструменты” жмем кнопку “Удалить дубликаты“.
        • Следующие шаги полностью совпадают с описанными в методе выше действиями по удалению дублированных строк.

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

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

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

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

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

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

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

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

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

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

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

              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 предлагает несколько инструментов для нахождения и удаления строк или ячеек с одинаковыми данными. Каждый из описанных методов специфичен и имеет свои ограничения. К универсальным варианту мы, пожалуй, отнесем использование “умной таблицы” и функции “Удалить дубликаты”. В целом, для выполнения поставленной задачи необходимо руководствоваться как особенностями структуры таблицы, так и преследуемыми целями и видением конечного результата.

                Ссылка на основную публикацию