Excel поиск одинаковых значений в двух столбцах

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

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

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

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

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

1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

   

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

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

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

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

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

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

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

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

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

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

Как сравнить два столбца в Excel по строкам

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

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

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

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

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

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)

Пример результата вычислений может выглядеть так:

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

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

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

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

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

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

Способ 2: использование “умной таблицы”

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

Для удаления повторов в Excel, вам нужно сделать следующее:

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

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

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

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

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

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

• Выделить столбцы с данными, в которых нужно вычислить совпадения;
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

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

Рассмотрим как найти совпадающие строки в таблице:

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

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

Применение расширенного фильтра

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

Чтобы использовать фильтр, необходимо:

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

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

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

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

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

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

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

Вместо “Unique” и “Duplicate” Вы можете записать собственные метки, например, “Не найдено” и “Найдено“, или оставить только “Duplicate” и ввести символ пробела вместо второго значения. В последнем случае ячейки, для которых дубликаты найдены не будут, останутся пустыми, и, я полагаю, такое представление данных наиболее удобно для дальнейшего анализа. Теперь давайте скопируем нашу формулу во все ячейки столбца C, вплоть до самой нижней строки, которая содержит данные в столбце A. Для этого наведите указатель мыши на правый нижний угол ячейки C1, указатель примет форму чёрного перекрестия, как показано на картинке ниже:Нажмите и, удерживая левую кнопку мыши, протащите границу рамки вниз, выделяя все ячейки, в которые требуется вставить формулу. Когда все необходимые ячейки будут выделены, отпустите кнопку мыши:

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

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

Способ 1: стандартное удаление дубликатов

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

Итак, вам необходимо:

1. Зажав левую кнопку мыши, выделить необходимый диапазон ячеек, в пределах которого будет поиск и автоматическое удаление повторяющихся строк.
2. Перейти во вкладку “Данные”.
3. Нажать по инструменту “Удалить дубликаты”, что находится в группе “Работа с данными”.
4. В появившемся окне поставить галочку рядом с “Мои данные содержат заголовки”.
5. Установить галочки рядом с названием тех столбцов, в которых будет проводиться поиск. Возьмите во внимание, что если установлены все галочки, то дубликатом будут считаться строки, которые полностью повторяются во всех столбцах. Таким образом, если вы хотите удалить дубликаты с одного столбца, то необходимо оставить лишь одну галочку рядом с его наименованием.
6. Нажать “ОК”.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 2. Поиск и удаление повторяющихся значений с помощью кнопки «Удалить дубликаты»

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

1. Аналогичным образом выделить таблицу или конкретный диапазон ячеек на рабочем листе Excel.
2. В списке инструментов сверху главного меню программы кликнуть по слову «Данные» один раз левой клавишей манипулятора.
3. В подразделе «Работа с данными» нажать на кнопку «Удалить дубликаты».

Путь к кнопке «Удалить дубликаты»

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

Необходимые действия в окне удаления дубликатов

1. На экране появится уведомление о найденных дубликатах. Они автоматически удалятся.

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