Содержание:
Чтение этой статьи займет у вас около 10 минут. В ближайшие 5 минут вы легко сможете сравнить два столбца в Excel и узнать, есть ли в них дубликаты, удалить их или выделить цветом. Итак, время пришло!
Excel — очень мощное и действительно классное приложение для создания и обработки больших объемов данных. Если у вас есть несколько книг с данными (или просто одна огромная таблица), то вам наверняка захочется сравнить 2 столбца, найти повторяющиеся значения, а затем что-то с ними сделать, например, удалить, выделить или очистить содержимое. Столбцы могут находиться в одной таблице, быть соседними или не соседними, располагаться на 2-х разных листах или даже в разных книгах.
Представьте, что у нас есть 2 столбца с именами людей — по 5 имен в столбце. A и 3 имени в столбце B. Вам нужно сравнить имена в этих двух столбцах и найти дубликаты. Как вы понимаете, это вымышленные данные, взятые исключительно для примера. В реальных таблицах мы имеем дело с тысячами или даже десятками тысяч записей.
Вариант: оба столбца находятся на одном листе. Например, столбец A и колонка B.
Вариант B: Столбцы находятся на разных листах. Например, столбец A на листе Sheet2 и колонка A на листе Sheet3.
Excel 2013, 2010 и 2007 имеют встроенный инструмент Удалить дубликаты (Удалить дубликаты), но в этой ситуации он бессилен, так как не может сравнивать данные в двух столбцах. Более того, он может удалять только дубликаты. Других опций, таких как выделение или изменение цвета, нет. И точка!
Далее я покажу вам возможные способы сравнения двух столбцов в Excel, что позволит вам найти и удалить повторяющиеся записи.
Сравните 2 столбца в Excel и найдите повторяющиеся записи с помощью формул
Вариант А: оба столбца находятся на одном листе
- В первую пустую ячейку (в нашем примере это ячейка С1) пишем следующую формулу:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
В нашей формуле A1 это первая ячейка первого столбца, который мы собираемся сравнивать. 1 млрд долларов и 10000 млрд долларов это адреса первой и последней ячеек второго столбца, с которыми мы будем производить сравнение. Обратите внимание на абсолютные ссылки: буквам столбцов и номерам строк предшествует знак доллара ($). Я использую абсолютные ссылки, чтобы адреса ячеек при копировании формул оставались неизменными.
Если вы хотите найти дубликаты в столбце B, измените ссылки так, чтобы формула выглядела так:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")Вместо "Только(Основной ключ) и Дублировать» Вы можете написать свои собственные ярлыки, например, «Не найдены(Основной ключ) и Найденный«или оставить только»Дублировать' и введите пробел вместо второго значения. В последнем случае ячейки, для которых не найдены дубликаты, останутся пустыми, и, я считаю, такое представление данных наиболее удобно для дальнейшего анализа.
- Теперь скопируем нашу формулу во все ячейки столбца. C, вплоть до нижней строки, содержащей данные в столбце A. Для этого наведите указатель мыши в правый нижний угол ячейки C1, указатель примет форму черного перекрестия, как показано на рисунке ниже:Нажмите и удерживайте левую кнопку мыши и перетащите границу рамки вниз, выделив все ячейки, в которые вы хотите вставить формулу. Когда все необходимые ячейки выделены, отпустите кнопку мыши:
Нажмите и удерживайте левую кнопку мыши и перетащите границу рамки вниз, выделив все ячейки, в которые вы хотите вставить формулу. Когда все необходимые ячейки выделены, отпустите кнопку мыши:
Наконечник: В больших таблицах копирование формулы будет быстрее, если использовать сочетания клавиш. Выделить ячейку C1 и нажмите Ctrl + C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl + Shift + Конец (чтобы выбрать все непустые ячейки в столбце C) и, наконец, нажмите Ctrl + V (чтобы вставить формулу во все выделенные ячейки).
- Отлично, теперь все повторяющиеся значения помечаются как «Дублировать«:
Вариант Б: два столбца находятся на разных листах (в разных книгах)
- В первой ячейке первого пустого столбца листа Sheet2 (в нашем случае это столбец B) введите следующую формулу:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Здесь Sheet3 — имя листа, на котором расположен 2-й столбец, и $ A $ 1: $ A $ 10000 — это адреса ячеек от 1-го до последнего во втором столбце.
- Скопируйте формулу во все ячейки столбца B (то же, что и вариант А).
- Мы получаем этот результат:
Обработка найденных дубликатов
Отлично, мы нашли записи в первом столбце, которые также присутствуют во втором столбце. Теперь нам нужно что-то с ними сделать. Просматривать все повторяющиеся записи в таблице вручную весьма неэффективно и занимает слишком много времени. Есть лучшие способы.
Показывать только повторяющиеся строки в столбце A
Если ваши столбцы не имеют заголовков, вам необходимо их добавить. Для этого поместите курсор на число, обозначающее первую строку, и оно превратится в черную стрелку, как показано на рисунке ниже:
Щелкните правой кнопкой мыши и выберите в контекстном меню Вносимые (Вставлять):
Дайте названия столбцам, например: «Фамилия(Основной ключ) и Дубликат?» Затем откройте вкладку Данные (Данные) и нажмите ФИЛЬТР (Фильтр):
После этого нажмите на маленькую серую стрелку рядом с надписью «Дубликат?«, чтобы открыть меню фильтров; снимите флажки со всех пунктов в этом списке, кроме Дублировать, и нажмите OK.
Вот и все, теперь вы видите только те элементы столбца А, которые дублируются в столбце В. В нашей обучающей таблице таких ячеек всего две, но, как вы понимаете, на практике их будет намного больше.
Чтобы снова отобразить все строки столбца А, щелкните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелкой, и выберите Выбрать все (Выбрать все). Или вы можете сделать то же самое через ленту, нажав Данные (Данные) > Выбрать и отфильтровать (Сортировка и фильтр) > Сбросить (Очистить), как показано на скриншоте ниже:
Измените цвет или выделите найденные дубликаты
Если записи «Дублировать» недостаточно для ваших целей, и вы хотите пометить повторяющиеся ячейки другим цветом шрифта, цветом заливки или каким-либо другим способом…
В этом случае отфильтруйте дубликаты, как показано выше, выберите все отфильтрованные ячейки и нажмите Ctrl + 1чтобы открыть диалог Формат ячеек (формат ячейки). В качестве примера изменим цвет заливки ячеек в строках с дубликатами на ярко-желтый. Конечно, вы можете изменить цвет заливки с помощью инструмента Заполнять Вкладка (Цвет заливки) Главная (Главная страница), но преимущество диалогового окна Формат ячеек (Формат ячейки), поскольку вы можете настроить все параметры форматирования одновременно.
Теперь вы точно не пропустите ни одну ячейку с дубликатами:
Удаление повторяющихся значений из первого столбца
Отфильтруйте таблицу так, чтобы отображались только ячейки с повторяющимися значениями, и выберите эти ячейки.
Если два сравниваемых столбца находятся на разных листах, то есть в разных таблицах щелкните правой кнопкой мыши выбранный диапазон и выберите Удалить строку (Удалить строку):
Нажмите OKкогда Excel попросит вас подтвердить, что вы действительно хотите удалить всю строку листа, а затем очистить фильтр. Как видите, остались только строки с уникальными значениями:
Если 2 столбца находятся на одном листе, близко друг к другу (рядом) или не близко друг к другу (не соседствуя), то процесс удаления дубликатов будет немного сложнее. Мы не можем удалить всю строку с повторяющимися значениями, так как при этом будут удалены и ячейки из второго столбца. Итак, чтобы оставить только уникальные записи в столбце А, сделай это:
- Отфильтруйте таблицу, чтобы отображались только повторяющиеся значения, и выберите эти ячейки. Щелкните по ним правой кнопкой мыши и выберите в контекстном меню Очистить содержимое (очистить содержание).
- Очистите фильтр.
- Выбрать все ячейки в столбце А, начиная с ячейки A1 вплоть до самого дна, содержащего данные.
- Нажмите Данные (Данные) и нажмите Сортировка от А до Я (Сортировать от А до Я). В открывшемся диалоговом окне выберите Продолжить текущий выбор (Сортировать внутри указанного выбора) и нажать кнопку Черный (Сортировка):
- Удалите столбец с формулой, он вам больше не понадобится, отныне у вас будут только уникальные значения.
- Всё, теперь колонка А содержит только уникальные данные, которых нет в столбце В:
Как видите, удалить дубликаты из двух столбцов в Excel с помощью формул не так уж и сложно.