Найти одинаковые значения в столбцах excel. Найти одинаковые значения в столбце Excel с помощью различных функций

Как отыскать повторяющиеся значения в таблице Excel

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

Работая с большими наборами данных в Excel либо объединяя несколько маленьких электронных таблиц в наиболее большие, вы сможете столкнуться с огромным числом схожих строк.

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

Самой обычной в использовании и вкупе с тем действенной в данном случае будет функция COUNTIF. С помощью одной лишь неё можно найти не лишь неуникальные позиции, но и их 1-ые возникновения в столбце. Разглядим разницу на примерах.

Поиск циклических значений включая 1-ые вхождения.

Предположим, что у вас в колонке А находится набор каких-либо характеристик, посреди которых, возможно, есть однообразные. Это могут быть номера заказов, наименования продуктов, имена клиентов и остальные данные. Ежели ваша задачка – отыскать их, то последующая формула для вас:

=СЧЁТЕСЛИ(A:A; A2)>1

Где А2 – 1-ая ячейка из области для поиска.

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

Как вы могли увидеть на скриншоте выше, формула возвращает ИСТИНА, ежели имеются совпадения.  А для встречающихся лишь 1 раз значений она указывает ЛОЖЬ.

Подсказка! Если вы отыскиваете повторы в определенной области, а не во всей колонке, обозначьте подходящий спектр и “зафиксируйте” его знаками $. Это существенно убыстрит вычисления. К примеру, ежели вы ищете в A2:A8, используйте

=СЧЕТЕСЛИ($A$2:$A$8, A2)>1

Если вас путает ИСТИНА и Ересь в статусной колонке и вы не желаете держать в уме, что из их значит циклическое, а что – неповторимое, заверните свою СЧЕТЕСЛИ в функцию Ежели и укажите хоть какое слово, которое обязано соответствовать дубликатам и уникальным:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;”Дубликат”;”Уникальное”)

Если же для вас необходимо, чтоб формула указывала лишь на дубли, замените “Уникальное” на пустоту (“”):

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;”Дубликат”;””)

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

Поиск неуникальных значений без учета первых вхождений

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

Если для вас необходимо указать лишь совпадения, давайте мало изменим:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; A2)>1;”Дубликат”;””)

На скриншоте ниже вы видите эту формулу в деле.

Нетрудно увидеть, что она не обозначает 1-ое возникновение слова, а начинает отсчет со второго.

Чувствительный к регистру поиск дубликатов

Хочу направить ваше внимание на то, что хоть формулы выше и находят 100%-дубликаты, есть один узкий момент – они не чувствительны к регистру. Быть может, для вас это не принципиально. Но ежели в ваших данных абв, Абв и АБВ – это три различных параметра – то этот пример для вас.

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

{=ЕСЛИ(СУММ((–СОВПАД($A$2:$A$17;A2)))<=1;””;”Дубликат”)}

Не запамятовывайте, что формулы массива вводятся комбиинацией Ctrl + Shift + Enter.

Читайте также  Не работает браузер не один что делать. Доступ к Интернету есть, но странички не грузятся — как исправить?

Если возвратиться к содержанию, то тут употребляется функция СОВПАД для сопоставления мотивированной ячейки со всеми остальными ячейками с избранной области. Итог ворачивается в виде ИСТИНА (совпадение) либо Ересь (не совпадение), которые потом преобразуются в массив из 1 и 0 при помощи оператора (–).

После этого, функция СУММ складывает эти числа. И ежели приобретенный итог больше 1, функция Ежели докладывает о отысканном дубликате.

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

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

Обнаружение циклических строк

Мы разглядели, как найти однообразные данные в отдельных ячейках. А ежели необходимо находить дубликаты-строки?

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

Итак, имеются данные о товарах и заказчиках.

Создадим справа от наших данных формулу, объединяющую содержание всех расположенных слева от нее ячеек.

Предположим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:

=A2&B2&C2

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

=СЧЁТЕСЛИ(D:D;D2)

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

В столбце E отображается количество возникновений данной строчки в столбце D. Неповторяющимся строчкам будет соответствовать значение 1. Повторам строчкам соответствует значение больше 1, указывающее на то, сколько раз таковая строчка была найдена.

Если вас не интересует определенный столбец, просто не включайте его в выражение, находящееся в D. К примеру, ежели для вас охото найти совпадающие строчки, не беря во внимание при этом значение Заказчик, уберите из объединяющей формулы упоминание о ячейке С2.

Обнаруживаем схожие ячейки при помощи интегрированных фильтров Excel.

Теперь разглядим, как можно обойтись без формул при поиске дубликатов в таблице. Быть может, кому-то этот способ покажется наиболее комфортным, ежели написание выражений Excel.

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

В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Основная – Форматировать как таблицу.

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

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

Но при этом вы видите дубли лишь по отфильтрованному. Ежели данных много, то находить таковым методом поочередного перебора будет несколько утомительно. Ведь очень много раз необходимо будет устанавливать и поменять фильтр.

Используем условное форматирование.

Выделение цветом по условию – очень принципиальный инструмент Excel, о котором довольно тщательно мы рассказывали.

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

Как показано на рисунке ниже, избираем Правила выделения ячеек – Повторяющиеся. Неуникальные данные будут подсвечены цветом.

Но тут мы не можем исключить 1-ые возникновения – подсвечивается всё.

Но эту делему можно решить, использовав формулу условного форматирования.

=СЧЁТЕСЛИ($B$2:$B2; B2)>1

Результат работы формулы выденения циклических значений вы видите выше. Они выделены зелёным цветом.

Чтобы освежить память, сможете управляться нашим материалом «Как поменять цвет ячейки в зависимости от значения».

Читайте также  Как сбросить пароль на модеме huawei. Сброс Настроек и Восстановление Заводских на Модеме и Роутере Huawei

Поиск совпадений при помощи команды «Найти».

Еще один обычной, но не очень технологичный метод – внедрение встроенного поиска.

Зайдите на вкладку Основная и кликните «Найти и выделить». Раскроется диалоговое окно, в котором можно ввести что угодно для поиска в таблице. Чтоб избежать опечаток, сможете скопировать разыскиваемое прямо из перечня данных.

Затем жмем «Найти все», и лицезреем все отысканные дубликаты и места их расположения, как на рисунке чуток ниже.

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

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

Как применить сводную таблицу для поиска дубликатов.

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

Для наиболее опытнейших – сходу перебегаем к сущности вопроса.

Создаем новейший макет сводной таблицы. А потом в качестве строк и значений используем одно и то же поле. В нашем случае – «Товар». Так как заглавие продукта – это текст, то для подсчета таковых значений Excel по умолчанию употребляет функцию СЧЕТ, то есть подсчитывает количество. А нам это и необходимо. Ежели будет больше 1, означает, имеются дубликаты.

Вы наблюдаете на скриншоте выше, что несколько продуктов дублируются. И что нам это дает? А дальше мы просто можем щелкнуть мышкой на хоть какой из цифр, и на новеньком листе Excel покажет нам, как вышла эта цифра.

К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и лицезреем такую картину:

Думаю, этот способ полностью можно применять. Что приятно – никаких формул не требуется.

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

Еще о дубликатах:

Как выделить цветом повторяющиеся значения в Excel?-В этом руководстве вы узнаете, как показывать дубликаты в Excel. Мы разглядим разные способы затенения дублирующих ячеек, целых строк либо поочередных повторений с внедрением условного форматирования.  Ранее мы изучили различные…
Как посчитать количество циклических значений в Excel?-Зачем считать дубликаты? Мы можем получить ответ на множество увлекательных вопросцев. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если…
Как убрать повторяющиеся значения в Excel?-В этом руководстве разъясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько разных способов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите четкие повторы и частичные совпадения. Хотя…
Как проверить корректность ввода данных в Excel?-Подтверждаем корректность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы инспектировать корректность ввода в согласовании с имеющимся перечнем допустимых значений. В случае правильного ввода в отдельном столбце ставить…

Найти однообразные значения в столбце Excel с помощью разных функций

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

Читайте также  Cisco сброс настроек до заводских настроек. Как сбросить настройки у Cisco по дефолту или возвращаем магазинный вид дорогому оборудованию

Поиск по цвету

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

  1. Выделите область, в которой будет выполняться поиск схожих значений.
  2. Откройте вкладку “Главная”.
  3. Перейдите в раздел “Стили”.
  4. Выберите “Условное форматирование”.
  5. Далее “Правила выделения ячеек”.
  6. Затем “Повторяющиеся значения”.

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

  • указывающий на повторяющиеся данные;
  • показывает, в какой цвет будет окрашивать обнаруженные дубликаты;
  • кнопка подтверждения.

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

Функция “Найти и выделить”

Следующий способ, как и предшествующий, чрезвычайно прост и надежен в плане поиска циклических объектов. Он гарантированно принесет положительный результат.

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

  1. Открыть вкладку “Главная”.
  2. В правом верхнем углу отыскать раздел “Редактирование”.
  3. Выбрать функцию “Найти и выделить”.
  4. Высветится маленькое окошко, вверху которого нажмите клавишу “Найти”.
  5. В указанное поле вчеркните заглавие подходящего значения и нажмите “Найти все”.
  6. Далее нажмите сочетание кнопок Ctrl+A для выделения всех отысканных объектов.

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

Расширенный фильтр

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

  1. Выделить одну всякую ячейку нужной нам таблицы.
  2. Зайти во вкладку “Данные”.
  3. Найти раздел “Сортировка и фильтр”.
  4. Выбрать пункт “Дополнительно”.

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

  1. В разделе “Обработка” выберите пункт “Скопировать итог в другое место” (указываем спектр, куда конкретно нужно перенести отысканные значения).
  2. Установите галочку напротив пт “Только неповторимые данные”.

Проделав все вышеупомянутые деяния, вы можете без всяких заморочек отыскать схожие значения в столбце Excel и удалить их, скопировав при этом в указанную вами область (только при том условии, что вы выделили подходящий для вас столбец Excel и сделали все вышеописанные операции). Кроме остального, можно также обработать данные на прежнем месте. Для этого делаете все те же деяния, не считая 1-го – заместо пт “Скопировать итог в другое” выберите “Фильтровать перечень на месте”.

Затем перейдите во вкладку “Данные” и найдите раздел “Работа с данными”, там нажмите “Удалить дубликаты”. Дальше перед вашими очами покажется маленькое окно, где необходимо будет выбрать нужные столбики и надавить клавишу “ОК”.

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

Заключение

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

Статья закончилась. Вопросцы остались?

Оставьте комментарий