Пожалуй, каждый, кто работает с данными в Excel сталкивается с вопросом как сравнить два столбца в Excel на совпадения и различия. Существует несколько способов как это сделать. Давайте рассмотрим подробней каждый из них.
Как сравнить два столбца в Excel по строкам
Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции . Рассмотрим как это работает на примерах ниже.
Пример 1. Как сравнить два столбца на совпадения и различия в одной строке
Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу . Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.
Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:
=ЕСЛИ(A2=B2; “Совпадают”; “”)
Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:
=ЕСЛИ(A2<>B2; “Не совпадают”; “”)
Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:
=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)
=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)
Пример результата вычислений может выглядеть так:
Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:
=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)
Как сравнить несколько столбцов на совпадения в одной строке Excel
В Excel есть возможность сравнить данные в нескольких столбцах одной строки по следующим критериям:
- Найти строки с одинаковыми значениями во всех столбцах таблицы;
- Найти строки с одинаковыми значениями в любых двух столбцах таблицы;
Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы
Представим, что наша таблица состоит из нескольких столбцов с данными. Наша задача найти строки в которых значения совпадают во всех столбцах. В этом нам помогут функции Excel и . Формула для определения совпадений будет следующей:
=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)
Если в нашей таблице очень много столбцов, то более просто будет использовать функцию в сочетании с :
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)
В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.
Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы
Представим, что наша задача выявить из таблицы с данными в несколько столбцов те строки, в которых данные совпадают или повторяются как минимум в двух столбцах. В этом нам помогут функции и . Напишем формулу для таблицы, состоящей из трех столбцов с данными:
=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)
В тех случаях, когда в нашей таблице слишком много столбцов – наша формула с функцией будет очень большой, так как в ее параметрах нам нужно указать критерии совпадения между каждым столбцом таблицы. Более простой способ, в этом случае, использовать функцию .
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)
=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)
Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.
Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.
Как сравнить два столбца в Excel на совпадения и выделить цветом
Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.
Поиск и выделение совпадений цветом в нескольких столбцах в Эксель
В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:
- Выделить столбцы с данными, в которых нужно вычислить совпадения;
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены цветом совпадения:
Поиск и выделение цветом совпадающих строк в Excel
Поиск совпадающих ячеек с данными в двух, нескольких столбцах и поиск совпадений целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:
В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.
Рассмотрим как найти совпадающие строки в таблице:
- Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:
=A2&B2&C2&D2
Во вспомогательной колонке вы увидите объединенные данные таблицы:
Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:
- Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены дублирующиеся строки:
Привет всем. Сегодня я хочу рассказать вам, как найти повторяющиеся значения в Excel и что с ними можно сделать. Если вам приходиться работать с таблицей, где есть дублирующиеся данные, то хотелось бы знать, как их отыскать. Именно этим, в этом уроке, мы и займемся.
Для примера я взял вот такую таблицу. Взял людей из какой-то группы вКонтакте, разделил имена и фамилии, и наделал несколько ячеек с дублями.
Выделяем цветом дубликаты в таблице
Первым способом я покажу вам, каким образом можно найти дубликаты и выделить их цветом. Это может вам потребоваться, для сравнения каких-либо данных без их удаления. В моем примере это будут одинаковые имена и фамилии людей.
Открывает вкладку «Главная», в разделе «Стили» выбираем «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения».
Открылось окно, в котором есть два пункта: что выделить – уникальные или повторяющиеся значения, и, как их выделить – в какую цветовую гамму. И, конечно же, кнопка «ОК».
Чтобы поиск был осуществлен не по всей таблице, предварительно выделите один или несколько столбцов.
Посмотрите на мой результат. Правда такой способ имеет существенный недостаток: нет выборки, выделяет все, что встречается более одного раза.
Выборочная подсветка или удаление одинаковых значений в Excel
Способ, может быть, банальный, но действенный. Воспользуемся функцией «Поиск».
Открывает вкладку «Главная» — раздел «Редактирование» — «Найти и выделить» (CTRL+F).
В окне в поле «Найти» набираем, что мы ищем. Затем жмем по кнопке «Найти все», нажимаем сочетание клавиш CTRL+A, чтобы выделить все результаты поиска, и выделяю их цветом. Так же их можно удалить, а не выделять.
Удаляем все одинаковые значения в Excel с помощью расширенного фильтра
Для использования расширенного фильтра, выберем любую ячейку в таблице. Я выбрал верхнюю левую. Затем открываем вкладку «Данные», переходим в раздел «Сортировка и фильтр», и жмем по кнопке «Дополнительно».
Теперь нужно настроить в этом окне, каким образом будет произведена фильтровка. Можно скопировать результаты фильтра в другое место (ставим галочку и указываем место, куда скопируется результат), либо результат оставить в том же месте. И, обязательно, ставим галочку «Только уникальные значения».
Вот мой результат применения к таблице расширенного фильтра. Как видим, в результате Excel смог найти и удалить дубликаты.
Еще один способ быстро удалить дубли в таблице
Этот способ удалит все одинаковые значения, которые встречаются в таблице. Если вам нужен поиск только в некоторых столбцах, то выделите их.
Теперь откройте вкладку «Данные», раздел «Работа с данными», «Удалить дубликаты».
Расставим нужные галочки. Мне нужен поиск по двум столбцам, потому оставляю, как есть, и жму на кнопку «ОК».
На этом метод закончился. Вот мой результат его работы.
Спасибо за прочтение. Не забывайте делиться с друзьями с помощью кнопок социальных сетей, и комментируйте.
Удаление одинаковых файлов (копий одного и того же файла) или папок может освободить свободное дисковое пространство и уменьшает ненужный мусор в системе, что благоприятно отражается на быстродействии системы. Иногда дубликаты создаются самим пользователем (например, одни и те же фотографии копируются в разные папки, находящиеся внутри одного физического диска), в некоторых случаях они остаются после использования различного программного обеспечения. Функцию поиска ненужных копий выполняют множество приложений, большинство которых распространяются по статусу free (бесплатно ). Опишем работу нескольких таких приложений.
Удаление дубликатов с помощью Total Commander
Используем Dupeguru
Данное ПО разработано исключительно для сканирования системы на наличие копий и не выполняет иные функции.
AllDup для определения дубликатов
Дизайн программы выполнен в красивом и удобном для использования стиле. Сразу же после запуска появляется окно с кратким руководством по поиску дубликатов. Можно искать по всему физическому диску целиком, отметив галочкой нужные локальные диски.
Или добавить отдельные папки, нажав на стрелочку возле кнопки «Исходные папки
» и выбрав в выпадающем списке «Добавить папки
».
Давайте попробуем найти дубликаты в папке Games в локальном диске «С:\»
Активируем вкладку «Метод поиска
», настраиваем критерии. По умолчанию активен параметр поиска дубликатов только по схожим именам
, при необходимости отмечаем галочкой нужные настройки
. Желательно включить поиск и по расширениям
, иначе ПО может показать файлы со схожими именами, даже если они не являются копиями друг друга.
После, нажимаем кнопку начало поиска
.
После завершения откроется новое окно с результатами , в котором отмечаем галочками ненужные файлы , щелкаем правой кнопкой мышки по файлу , который предполагается очистить от дубликатов (открываем контекстное меню) и выбираем «Удалить все остальные файлы из этой группы навсегда », чтобы удалить копии. При этом сам оригинал останется не тронутым, удалятся только дубликаты.
Как использовать DuplicateCleaner Free
Очень продуманное приложение для поиска и удаления дубликатов на компьютере. При этом можно выполнять поиск
всех файлов в обычном режиме, искать копии изображений или музыки, или одинаковых папок.
Запускаем приложение
. Первым делом настраиваем критерии поиска, для этого в разделе «Дополнительные опции
» отмечаем настройку «С тем же именем
» и переходим во вкладку «Сканировать путь
»
Выбираем папку
, нажимаем кнопку добавления
выбранной папки в включенные пути и жмем кнопку «Сканировать
».
Дожидаемся окончания сканирования, в котором будет отображена статистика
обнаруженных дубликатов в отдельном окне. Закрываем окно.
Во вкладке «Дубликаты Файлов
» списком отображаются одинаковые
файлы, отмечаем ненужные и нажимаем меню «Вид
», выбираем пункт «Удаления файлов
»
В открывшемся окне нажимаем «Удалить файл(ы) ». При этом желательно включить удаление в Корзину , чтобы можно было восстановить нужные файлы.
Поиск дублей в Excel – это одна из самых распространенных задач для любого офисного сотрудника. Для ее решения существует несколько разных способов. Но как быстро как найти дубликаты в Excel и выделить их цветом? Для ответа на этот часто задаваемый вопрос рассмотрим конкретный пример.
Как найти повторяющиеся значения в Excel?
Допустим мы занимаемся регистрацией заказов, поступающих на фирму через факс и e-mail. Может сложиться такая ситуация, что один и тот же заказ поступил двумя каналами входящей информации. Если зарегистрировать дважды один и тот же заказ, могут возникнуть определенные проблемы для фирмы. Ниже рассмотрим решение средствами условного форматирования.
Чтобы избежать дублированных заказов, можно использовать условное форматирование, которое поможет быстро найти одинаковые значения в столбце Excel.
Пример дневного журнала заказов на товары:
Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:
Как видно на рисунке с условным форматированием нам удалось легко и быстро реализовать поиск дубликатов в Excel и обнаружить повторяющиеся данные ячеек для таблицы журнала заказов.
Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений
Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.
Самые быстрые и простые способы: найти дубликаты в ячейках .
После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.
Распространенный вопрос как найти и удалить дубликаты в Excel. Предположим вы выгрузили месячный отчет из вашей учетной системы, но в итоге вам нужно понять какие контрагенты вообще взаимодействовали с компанией за этот период — оставить список контрагентов без повтарений. Как отобрать уникальные значения?
Можно ли удалить задвоеные, затроенные и так далее значения в Excel по нескольким столбцам?
Можно, причем очень просто. Для этого есть специальная функция. Предварительно выберите диапазон, где нужно удалять дубликаты. На ленте заходим Данные — Удалить дубликаты (смотрите картинку в начале статьи).
Выбираем первый столбец
При этом важно понимать, что если вы выберите только первый столбец, то все данные в не выбранных столбцах удаляться в случае неуникальности.
Очень удобно!
2. Как выделить все дубликаты в Excel?
Уже слышали про ? Да, здесь оно еще как поможет! Выделяете столбец в котором надо пометить дубликаты, выбираете в меню Главное — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения…
В открывшемся окне Повторяющиеся значения, выберите какие ячейки выделяем (уникальные или повторяющиеся), а так же формат выделения, либо из преложенных, либо создайте Пользовательский формат. Предустановлено форматом будет красная заливка и красный текст.
Нажимаете ОК, если не хотите изменять форматирование. Теперь все данные по выбранным условиям подкрасятся.
Отмечу, что инструмент применяется только для выбранного одного (!) столбца.
Кстати, если нужно увидеть уникальные, то в окне слева выберите — уникальные.
3. Уникальные значения при помощи сводных таблиц
Признаюсь честно, когда-то я не подозревал о существовании возможности «удалить дубликаты» и пользовался сводными таблицами. Как я это делал? Выделяете таблицу, в которых надо найти уникальные значения — Вставка —
