Как в xl найти одинаковые данные. Как найти одинаковые файлы на компьютере

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

В таблице остались две строки с дублями. В верхней ячейке отфильтрованного столбца B пишем слово «Да». Копируем по столбцу.

Мы подсветили ячейки со словом «Да» условным форматированием. Вместо слов, можно поставить числа. Получится так.

В столбце F написали формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"+";"-") Получилось так.

В строке «Форматировать формулу для определения форматируемых ячеек» пишем такую формулу. =СЧЁТЕСЛИ($A:$A;A5)>1 Устанавливаем формат, если нужно выбрать другой цвет ячеек или шрифта.

Нужно сравнить и выделить данные по трем столбцам сразу. У нас такая таблица.

Поиск дублей в Excel – это одна из самых распространенных задач для любого офисного сотрудника. Для ее решения существует несколько разных способов. Но как быстро как найти дубликаты в Excel и выделить их цветом? Для ответа на этот часто задаваемый вопрос рассмотрим конкретный пример.

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

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

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

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

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

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

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

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

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

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

Пожалуй, каждый, кто работает с данными в 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 по нескольким столбцам?

Можно, причем очень просто. Для этого есть специальная функция. Предварительно выберите диапазон, где нужно удалять дубликаты. На ленте заходим Данные — Удалить дубликаты (смотрите картинку в начале статьи).

Выбираем первый столбец

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

Очень удобно!

2. Как выделить все дубликаты в Excel?

Уже слышали про ? Да, здесь оно еще как поможет! Выделяете столбец в котором надо пометить дубликаты, выбираете в меню Главное — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения…

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

Нажимаете ОК, если не хотите изменять форматирование. Теперь все данные по выбранным условиям подкрасятся.

Отмечу, что инструмент применяется только для выбранного одного (!) столбца.

Кстати, если нужно увидеть уникальные, то в окне слева выберите — уникальные.

3. Уникальные значения при помощи сводных таблиц

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

В двух предыдущих уроках мы удаляли дубликаты. Прочитать об этом можно и . В этом уроке мы будем осуществлять поиск дубликатов .

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

Есть задача: в исходной таблице выделить все записи, у которых есть дубликат.

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

В открывшемся окне оставляем выбранной опцию "Фильтровать список на месте". В поле "Исходный диапазон" по умолчанию у вас должна быть указана таблица. А также обязательно ставим галочку "Только уникальные записи" для того, чтобы дубликаты были скрыты. В конце нажимаем кнопку "Ок".

Если сейчас внимательно посмотреть на наш пример, то нумерация строк стала синего цвета, что свидетельствует о применении фильтра и наличии дубликатов, а строки 9, 10 и 11 просто были скрыты, так как они являются дубликатами и не уникальны.

Теперь мы можем пометить все уникальные строки. Например, выделить их цветом.

Или присвоить им отдельную метку. Создадим отдельную колонку "Уникальность" и всем этим строкам поставим значение "1".

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

Теперь осталось убрать фильтр для того, чтобы открыть все строки таблицы. Заходим во вкладку "Данные" и щелкаем по кнопке "Очистить".

Все строки, которые у нас были дублирующими, будут не подписаны.

Теперь давайте таблице добавим "Фильтр". Для этого выделим ее, затем перейдем на вкладку "Данные" и щелкнем по иконке "Фильтр".

Благодаря этому у нас появилась возможность через фильтр выбрать все дубликаты. Щелкаем по иконке фильтра в столбце "Уникальность" и из списка выбираем все пустые строки. Нажимаем "Ок".

Все записи будут отсортированы и вы получите в свое распоряжение все дублирующие записи.

В этом уроке расскажу как сделать разбивку текста по столбцам в Excel. Данный урок подойдет вам в том случае, если вы хотите произвести разбивку текста из одного столбца на несколько. Сейчас приведу пример. Допустим, у вас есть ячейка "A", в которой находится имя, фамилия и отчество. Вам необходимо сделать так, чтобы в первой ячейке "A" была только фамилия, в ячейке "B" - имя, ну и в ячейке "C" отчество.

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