Как в Excel сравнить два столбца и удалить дубликаты (выделить, раскрасить, переместить)

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

Сравнить столбцы и удалить дубликаты в Excel

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

Представьте, что у нас есть 2 столбца с именами людей – 5 имён в столбце A и 3 имени в столбце B. Необходимо сравнить имена в этих двух столбцах и найти повторяющиеся. Как Вы понимаете, это вымышленные данные, взятые исключительно для примера. В реальных таблицах мы имеем дело с тысячами, а то и с десятками тысяч записей.

Вариант А: оба столбца находятся на одном листе. Например, столбец A и столбец B.

Сравнить столбцы и удалить дубликаты в Excel

Вариант В: Столбцы расположены на разных листах. Например, столбец A на листе Sheet2 и столбец A на листе Sheet3.

Сравнить столбцы и удалить дубликаты в Excel

В Excel 2013, 2010 и 2007 есть встроенный инструмент Remove Duplicate (Удалить дубликаты), но он бессилен в такой ситуации, поскольку не может сравнивать данные в 2 столбцах. Более того, он может только удалить дубликаты. Других вариантов, таких как выделение или изменение цвета, не предусмотрено. И точка!

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

Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул

Вариант А: оба столбца находятся на одном листе

  1. В первой пустой ячейке (в нашем примере это ячейка C1) запишем вот такую формулу:

    =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")
    =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

    Сравнить столбцы и удалить дубликаты в Excel

    В нашей формуле A1 это первая ячейка первого столбца, который мы собираемся сравнивать. $B$1 и $B$10000 это адреса первой и последней ячеек второго столбца, с которым будем выполнять сравнение. Обратите внимание на абсолютные ссылки – буквам столбца и номерам строки предшествует знак доллара ($). Я использую абсолютные ссылки для того, чтобы адреса ячеек оставались неизменными при копировании формул.

    Если Вы хотите найти дубликаты в столбце B, поменяйте ссылки, чтобы формула приняла такой вид:

    =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")
    =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

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

  2. Теперь давайте скопируем нашу формулу во все ячейки столбца C, вплоть до самой нижней строки, которая содержит данные в столбце A. Для этого наведите указатель мыши на правый нижний угол ячейки C1, указатель примет форму чёрного перекрестия, как показано на картинке ниже:

    Сравнить столбцы и удалить дубликаты в Excel

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

    Сравнить столбцы и удалить дубликаты в Excel

Подсказка: В больших таблицах скопировать формулу получится быстрее, если использовать комбинации клавиш. Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).

  1. Отлично, теперь все повторяющиеся значения отмечены как "Duplicate":

    Сравнить столбцы и удалить дубликаты в Excel

Вариант В: два столбца находятся на разных листах (в разных книгах)

  1. В первой ячейке первого пустого столбца на листе Sheet2 (в нашем случае это столбец В) введите такую формулу:

    =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-ой до последней в этом 2-ом столбце.

  2. Скопируйте формулу во все ячейки столбца B (как и в варианте А).
  3. У нас получается вот такой результат:

    Сравнить столбцы и удалить дубликаты в Excel

Обработка найденных дубликатов

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

Показать только повторяющиеся строки в столбце А

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

Сравнить столбцы и удалить дубликаты в Excel

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

Сравнить столбцы и удалить дубликаты в Excel

Дайте названия столбцам, например, "Name" и "Duplicate?" Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

Сравнить столбцы и удалить дубликаты в Excel

После этого нажмите меленькую серую стрелку рядом с "Duplicate?", чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.

Сравнить столбцы и удалить дубликаты в Excel

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

Сравнить столбцы и удалить дубликаты в Excel

Чтобы снова отобразить все строки столбца А, кликните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:

Сравнить столбцы и удалить дубликаты в Excel

Изменение цвета или выделение найденных дубликатов

Если пометки "Duplicate" не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Format Cells (Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

Сравнить столбцы и удалить дубликаты в Excel

Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

Сравнить столбцы и удалить дубликаты в Excel

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

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

Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

Сравнить столбцы и удалить дубликаты в Excel

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

Сравнить столбцы и удалить дубликаты в Excel

Если 2 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:

  1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).

    Сравнить столбцы и удалить дубликаты в Excel

  2. Очистите фильтр.
  3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
  4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):

    Сравнить столбцы и удалить дубликаты в Excel

  5. Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
  6. Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:

    Сравнить столбцы и удалить дубликаты в Excel

Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.

30.06.2015 22:28
32571

2 комментария

10.11.2015 18:31
Здравствуйте. Огромнейшее спасибо вам за ваш сайт.

Помогите пожалуйста мне необходимо написать формулу для определения дубликатов. В данной статье описано как находить дубликаты на разных листах. Но мне необходимо что бы сверка была не по 1 критерию, а по двум. Т.е. артикул и дату производства сравнивал на одном листе и на втором.
Здравствуйте, Юлия!

Спасибо за добрые слова. Как вариант решения вашей проблемы — просто немного расширить имеющуюся формулу и добавить функцию ИЛИ. Например:

=ЕСЛИ(ИЛИ(ЕОШИБКА(ПОИСКПОЗ(A1; Лист3!$A$1:$A$10000;0)); ЕОШИБКА(ПОИСКПОЗ(B1; Лист3!$B$1:$B$10000;0)));"";" Дубликат ")

Если не понятно:
  • Функция ПОИСКПОЗ возвращает номер позиции, если находит искомое значение, или ошибку #Н/Д, если не находит.

  • Функция ЕОШИБКА возвращает значения ИСТИНА или ЛОЖЬ. ИСТИНА, если ПОИСКПОЗ возвращает #Н/Д. ЛОЖЬ, если какую-либо позицию.

  • Функция ИЛИ возвращает ЛОЖЬ только в том случае, если обе функции ЕОШИБКА возвращают ЛОЖЬ, т.е. когда обе функции ПОИСКПОЗ возвращают номера позиций (т.е. находят значения).

  • С функцией ЕСЛИ думаю все понятно