Как найти повторяющиеся значения в столбце в Excel
Обновлено: 30.06.2024
Когда вы работаете с большим количеством данных, вы столкнетесь с несколькими повторяющимися записями.
Иногда эти повторяющиеся значения очень полезны, но часто возникает необходимость удалить повторяющиеся данные. Если у вас есть 1000 данных, становится довольно сложно найти и удалить повторяющиеся данные.
Здесь мы представили пошаговый процесс поиска дубликатов в Excel.
Посмотрите и проследите за процессом.
Оглавление
Как найти дубликаты в Excel
Существует множество способов поиска повторяющихся элементов и значений в Excel. Вы можете подумать, почему я должен применять какую-либо формулу или метод для поиска повторяющихся значений, поскольку это легко.
Но дело не в небольшом количестве данных, вы можете применить формулу или метод, когда у вас много данных.
Метод или формула для поиска и удаления повторяющихся элементов упрощает процесс и экономит ваше время. Здесь вы можете проверить три разных процесса.
1. Поиск дубликатов в Excel с помощью условного форматирования
Чтобы найти повторяющиеся значения в Excel, вы можете использовать формулы Excel с условным форматированием, Vlookup и Countif. Обнаружив повторяющиеся значения, вы можете удалить их, если хотите, с помощью различных методов, описанных ниже.
- Выберите ячейки, в которых вы хотите найти повторяющиеся значения.
- Затем перейдите на главную вкладку или ленту.
- Найдите на ленте значок условного форматирования.
- Нажмите стрелку раскрывающегося списка условного форматирования.
- Нажмите «Правила выделения ячеек».
- После этого найдите другой вариант — «Повторяющиеся значения».
- Выберите повторяющиеся значения.
- Откроется еще одно окно с параметрами настройки.
- Выберите дубликат из раскрывающегося списка.
- Выберите цветовой контраст по своему усмотрению.
- Нажмите "ОК".
- При этом будут выделены все повторяющиеся значения.
- Убедитесь, что ваши данные с повторяющимися значениями выделены.
Удалить повторяющиеся значения
- Выберите все повторяющиеся ячейки или выделенные ячейки.
- Удалите значения, нажав кнопку "Удалить".
- После удаления значений перейдите к условному форматированию.
- Выберите «Очистить правила».
- А затем выберите четкие правила из всего листа.
Еще один способ удалить повторяющееся значение
Этот метод безвозвратно удалит все повторяющиеся данные. Если вы хотите снова получить повторяющиеся данные, скопируйте данные на другой лист.
- Выберите ячейки, в которых вы хотите найти и удалить повторяющиеся значения.
- Перейдите на ленту и найдите параметр данных.
- На вкладке данных вы найдете параметр удаления дубликатов.
- Теперь установите или снимите флажок со столбца, к которому вы хотите применить это ограничение.
- Затем нажмите "ОК".
2. Поиск дубликатов в одном столбце с помощью СЧЁТЕСЛИ
Еще один способ обнаружения дубликатов — функция СЧЁТЕСЛИ. Если вы хотите применить функцию СЧЁТЕСЛИ, узнайте здесь, как найти дубликаты в Excel с помощью функции СЧЁТЕСЛИ в Excel.
- Если у вас есть список элементов, из которых вы хотите найти дубликаты, вам нужно применить к нему формулу СЧЁТЕСЛИ.
- Синтаксис для СЧЁТЕСЛИ: «=СЧЁТЕСЛИ(B:B, B2)>1».
- В столбце A указано имя покупателя, а в столбце B — название фруктов, которые ему нравятся. Теперь вы хотите найти повторяющиеся элементы.
- Поместите эту формулу СЧЁТЕСЛИ в столбец C.
- Для всех повторяющихся полей отображается ИСТИНА, тогда как для не повторяющихся полей отображается ЛОЖЬ.
- Теперь, выполнив приведенный выше пошаговый процесс, вы можете удалить все повторяющиеся элементы, которые вам не нужны.
Найти дубликаты в двух столбцах в Excel
Выше мы видели, как найти повторяющиеся значения в одном столбце, теперь мы увидим здесь, как найти дубликаты в двух столбцах в Excel.
В этом примере мы взяли таблицу, в которой имя кандидата находится в столбце A, а Fruits — в столбце B.Теперь нам нужно найти повторяющиеся значения с одинаковыми именами и плодами.
Формула для поиска повторяющихся значений в двух столбцах
Как подсчитать дубликаты в Excel
Если вы хотите узнать общее количество повторяющихся значений, вам нужно использовать функцию подсчета. Для подсчета повторяющихся значений необходимо использовать формулу countif.
3. Фильтровать дубликаты в Excel
Еще один метод поиска дубликатов в Excel – это фильтрация данных для получения повторяющихся значений.
- Получить набор данных
- Выберите стол
- Перейдите на вкладку "Данные" и нажмите значок фильтра в разделе "Сортировка и фильтрация".
- Если вам нужна автоматическая фильтрация, преобразуйте данные, доступные в таблице, в полностью функциональную таблицу Excel, для которой вам нужно нажать Ctrl+T.
- Нажав на значок фильтра, вам нужно нажать на стрелку, размещенную в заголовке дубликата столбца.
- Нажмите раскрывающееся меню "Дублировать значение".
- Чтобы отобразить повторяющиеся значения, вам нужно установить флажок "Повторяющиеся значения", а если вы хотите отфильтровать значения, выберите пустое значение и снимите флажок "Повторяющиеся значения", чтобы получить уникальное значение.
Удалить дубликаты онлайн
Чтобы удалить дубликаты, вам необходимо выполнить описанный ниже процесс
- Выберите таблицу, перетащив мышь.
- Щелкните правой кнопкой мыши по таблице.
- Удалите строку, выбрав параметр "Удалить строку".
Выделить дубликаты в Excel
Чтобы выделить повторяющиеся значения, вы можете либо использовать условную формулу для поиска дубликатов в Excel, либо выполнить описанный ниже процесс.
Помимо этого, существуют и другие доступные процессы, с помощью которых вы можете найти повторяющиеся элементы, доступные в вашем наборе данных. Обнаружив повторяющиеся значения, вы можете легко их удалить. Оставайтесь на связи с нашим веб-сайтом, чтобы узнать о других функциях Excel.
Похожие запросы
Здесь мы перечислили несколько запросов, которые задают изучающие Excel. Если вы также столкнулись с такой проблемой, вы можете проверить ее здесь.
В1. Как удалить повторяющиеся адреса электронной почты, если в вашем наборе данных 10 000 писем?
Ответ. Если у вас есть все адреса электронной почты в одном столбце, выберите столбец и перейдите на ленту «Данные». Вы найдете вкладку «Удалить дубликаты» в инструментах данных.
Нажмите «Удалить дубликаты», и вы увидите другое диалоговое окно, в котором вам нужно сделать выбор. Нажмите Ok, и вы получите уникальные адреса электронной почты.
Вопрос 2. Как найти повторяющееся имя учителя в столбце А? Данные
- B1= Math/English Jasmine, Eon
- B2= Физика Йохана
- B3= Пул истории
- B4= английский/физика Сэм, Жасмин
- B5= Химический Ромео
Ответ. Чтобы решить эту проблему, создайте три отдельных столбца. В первом столбце укажите название класса. Во втором столбце укажите имя учителя, а в третьем упомяните имя другого учителя. Теперь в четвертом столбце поместите формулу =СЧЁТЕСЛИ(B:B,B1)>1
Используя эту формулу, вы найдете «ИСТИНА», если значение повторяется.
Вопрос 3. Как сортировать данные перед применением фильтра?
Ответ. Для сортировки данных вам нужно выбрать вкладку «Сортировка» в Excel. Сортировка набора данных — довольно простой процесс. Следуйте описанному ниже процессу и полностью отсортируйте свои данные.
- Выберите набор данных.
- Перейдите на ленту данных.
- В разделе "Сортировка и фильтр" вы найдете значок "Сортировка". Нажмите на значок и отсортируйте данные.
В4. Как сравнить два столбца в Excel?
В основном этот вопрос задавал интервьюер во время интервью. Есть много способов сравнить два столбца:
- =A2=B2 Это простая формула, используемая для сравнения двух столбцов.
- Условное форматирование. В этом случае вам необходимо выполнить описанный выше процесс, вы можете проверить ссылку Excel на условное форматирование, чтобы получить подробную информацию.
- ВПР. Вы также можете использовать формулу ВПР для сравнения двух столбцов, используя ВПР для исследований, ISNA для производительности и Если для настройки результата.
- Excel If Statement: это очень полезная функция Excel, которую можно использовать по-разному. Чтобы получить подробную информацию, нажмите здесь.
В5. Как отфильтровать данные в excel?
Фильтрация в Excel — это простой процесс. В разделе «Сортировка и фильтр» вы найдете значок «Фильтр». Нажмите на значок и отфильтруйте данные.
В этом руководстве мы рассмотрели все темы Excel, которые лучше всего подходят для начинающих и экспертов. Мы всегда стараемся решить все проблемы, с которыми сталкиваются люди при работе в excel. Если у вас есть какие-либо другие вопросы или вы хотите что-то спросить, вы можете указать это в поле для комментариев ниже. Один из членов нашей команды вернется с ответом.
Поиск дубликатов в Excel — одна из самых распространенных задач любого офисного сотрудника.Для ее решения существует несколько различных методов. Но — как быстро найти дубликаты в Excel и выделить их цветом? Для ответа на этот часто задаваемый вопрос рассмотрим конкретный пример.
Как найти повторяющиеся значения в Excel?
Например, мы занимаемся чековыми заказами, поступающими в фирму по факсу и электронной почте. Возможна такая ситуация, что один и тот же заказ был по двум каналам поступающей информации. Если вы дважды оформляете один и тот же заказ, у фирмы могут возникнуть определенные проблемы. Ниже мы рассматриваем решение с помощью условного форматирования.
Чтобы избежать дублирования заказов, вы можете использовать условное форматирование, которое поможет вам быстро найти повторяющиеся значения в столбце Excel.
Пример дневных заказов на товары:
Для проверки, возможны ли дубликаты дневных заказов, проанализируем в именах клиентов – есть столбец B:
Как видно на картинке с условным форматированием мы смогли легко и быстро реализовать поиск дубликатов в функции Excel и обнаружить дублирующиеся ячейки данных для таблицы дневных заказов.
Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений
Принцип действия формулы поиска дубликатов условным форматированием прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию также можно использовать при поиске одинаковых значений в диапазоне ячеек. Первым аргументом в функции указывается видимый диапазон данных. Во втором аргументе мы указываем, что мы ищем. Первый аргумент имеет абсолютную ссылку, как и должно быть. А второй аргумент наоборот - следует изменить на адрес каждой ячейки в диапазоне просмотра, т.к. он имеет относительную ссылку.
Самый быстрый и простой способ: найти дубликаты в ячейках.
После функции мы видим оператор сравнения количества найденных значений в диапазоне с цифрой 1. То есть, если мы видим больше, чем одно значение, это означает, что формула возвращает значение ИСТИНА и для к текущей ячейке применяется условное форматирование.
В Microsoft Excel существует множество способов поиска дубликатов в одном столбце. Мы можем вставить функцию или использовать комбинированную формулу для выявления дубликатов или совпадений в нашей электронной таблице Excel. В этой статье вы узнаете все возможные простые методы поиска повторяющихся значений в одном столбце с примерами и соответствующими иллюстрациями.
Загрузить практическую рабочую тетрадь
Вы можете скачать книгу Excel, которую мы использовали для подготовки этой статьи.
8 подходящих способов поиска дубликатов в одном столбце с помощью формулы Excel
1. Используйте функцию СЧЁТЕСЛИ для поиска дубликатов наряду с первым вхождением
В следующей таблице есть несколько имен, лежащих под заголовком Name в столбце B. А под заголовком Duplicate в столбце C мы будем использовать функцию СЧЁТЕСЛИ, чтобы узнать, есть ли у какого-либо имени в левом столбце дубликаты или нет. Формула вернет TRUE для повторяющихся имен и FALSE для уникальных.
В первой выходной ячейке C5 формула с функцией СЧЁТЕСЛИ будет выглядеть так:
После нажатия Enter и автоматического заполнения остальных ячеек в столбце C мы получим следующие результаты.
В этой формуле функция СЧЕТЕСЛИ возвращает количество счетчиков для каждого имени, и с помощью логического оператора мы искали счетчики, которые больше 1. Таким образом, мы можем идентифицировать дубликаты, ища логическое значение ' только TRUE.
2. Создайте формулу с функциями ЕСЛИ и СЧЁТЕСЛИ для поиска дубликатов в одном столбце
Мы также можем комбинировать функции ЕСЛИ и СЧЁТЕСЛИ, чтобы возвращать выходные данные с настроенным текстом. Под заголовком «Вывод» формула вернет «Дублировать» для повторяющихся имен, присутствующих в столбце B. И если текст в столбце «Имя» уникален, формула вернет пустое значение для соответствующего текстового значения.
Итак, необходимая формула, объединяющая функции ЕСЛИ и СЧЁТЕСЛИ в ячейке C5, должна быть следующей:
Теперь нажмите Enter, используйте дескриптор заполнения, чтобы автоматически заполнить другие ячейки под заголовком «Вывод», и вы сразу найдете следующие выходные данные.
В этой формуле функция ЕСЛИ ищет числа, превышающие 1, и, если находит, возвращает указанный текст «Дубликат», в противном случае — пустую ячейку.
3. Поиск дубликатов в одном столбце без первого вхождения в Excel
В этом разделе мы вставим формулу, которая будет отображать «Дубликат» из второго вхождения похожего текста. Например, если имя в столбце B присутствует трижды, формула вернет определенный текст «Дубликат» только для второго и третьего вхождений.
Требуемая формула для первой выходной ячейки C5 будет следующей:
После нажатия Enter и перетаскивания вниз к последней ячейке в столбце Output мы получим следующие возвращаемые значения.
Для первого вывода в ячейке C5 мы определили диапазон ячеек только с $B$5:$B5, поэтому формула будет искать только первую ячейку, чтобы найти повторяющееся значение. При перетаскивании манипулятора заполнения для поиска следующих выходных данных количество ячеек в заданном диапазоне для функции СЧЁТЕСЛИ увеличивается на 1 для каждой последующей ячейки. Таким образом, первое появление любого текста в столбце «Имя» здесь не считается более 1.
4. Формула Excel для поиска дубликатов с учетом регистра в одном столбце
Теперь мы применим другую комбинированную формулу для поиска дубликатов с учетом регистра. Здесь мы собираемся объединить функции ЕСЛИ, СУММ и ТОЧНО. Функция EXACT проверяет, совпадают ли две строки. Функция СУММ просто суммирует числовые значения.
В первой выходной ячейке C5 объединенная формула с указанными функциями будет выглядеть так:
Теперь нажмите Enter и автоматически заполните весь столбец, чтобы найти все возвращаемые значения.
Если вы заметили, то обнаружите, что имя «Фред» присутствует в столбце «Имя» трижды. Но формула вернула «Дублировать» только для первых двух вхождений, а третье проигнорировано, так как его первый регистр букв не совпадает с другими.
🔎 Как работает формула?
- Здесь функция EXACT ищет точные совпадения с учетом регистра для первого текста в столбце "Имя" и, таким образом, возвращает следующий результат:
- При использовании двойного унарного (–) возвращаемые значения преобразуются в числа: «1» означает «ИСТИНА» и «0» — ЛОЖЬ. Итак, возвращаемые значения здесь будут такими:
- Затем функция СУММ суммирует все числовые значения, найденные на предыдущем шаге.
- =СУММ((–EXACT($B$5:$B$14, B5)))
Похожие чтения:
- Формула для поиска дубликатов в Excel (6 простых способов)
- Excel находит дубликаты в столбце и удаляет строку (4 быстрых способа)
- Excel находит повторяющиеся строки на основе нескольких столбцов
- Как найти повторяющиеся строки в Excel (5 быстрых способов)
5. Найти последовательность дубликатов с помощью формулы Excel
В этом разделе мы будем искать дубликаты с помощью функции СЧЁТЕСЛИ, а затем она будет возвращать порядковый номер каждого вхождения похожего текста в столбце вывода.
Требуемая формула в первой выходной ячейке C5:
Нажмите Enter, заполните весь столбец, и вы получите следующие возвращаемые значения. На рисунке ниже имя Фред присутствует трижды, и в выделенных выходных ячейках вы видите порядковые номера для всех дубликатов, включая первое вхождение.
В этой формуле мы использовали относительную ссылку на ячейку для выбранного диапазона ячеек. Таким образом, когда формула начинает опускаться в столбце «Вывод», она принимает увеличенное количество ячеек только до соответствующей выходной ячейки. Таким образом, повторяющийся текст во всех следующих ячейках игнорируется до тех пор, пока формула не достигнет соответствующего повторяющегося значения.
6. Фильтрация и удаление дубликатов в одном столбце в Excel
После применения формулы для поиска повторяющихся значений мы можем сразу отфильтровать и удалить их. На рисунке ниже выходные данные были найдены с помощью предыдущего метода.Теперь мы рассмотрим следующие шаги для достижения наших целей в этом разделе.
📌 Шаг 1:
➤ Сначала выберите всю таблицу, включая ее заголовки.
➤ На вкладке "Главная" выберите параметр "Фильтр" в раскрывающемся списке "Сортировка и фильтр" в группе команд "Редактирование".
Итак, мы только что активировали кнопки фильтра для наших заголовков в следующей таблице.
📌 Шаг 2:
➤ Нажмите раскрывающееся меню "Вывод" и снимите отметку с первого параметра, показывающего числовое значение "1".
➤ Теперь нажмите OK.
Теперь мы видим повторяющиеся тексты без их первых вхождений в отфильтрованной таблице ниже.
📌 Шаг 3:
➤ Выберите ячейки, содержащие имена и соответствующие выходные данные.
➤ И удалить их все.
📌 Шаг 4:
➤ Снова откройте выходной фильтр.
➤ Отметьте вариант, показывающий только «1».
➤ Нажмите Enter, и готово.
Как показано на следующем снимке экрана, теперь вы получите все уникальные текстовые данные или только имена.
7. Формула Excel для поиска дубликатов в одном столбце на основе условия
Мы также можем вставить условие и соответственно найти дубликаты для значений, лежащих в столбце. На рисунке ниже у нас есть дополнительный столбец, который представляет отделы для всех сотрудников в организации.
Теперь у нас может быть два сотрудника с одинаковым именем, но в разных отделах. И одно из их имен с соответствующим отделом может присутствовать с дублированием в следующем наборе данных. Объединив функции ЕСЛИ и СЧЁТЕСЛИМН, мы теперь будем искать эти повторяющиеся строки.
Требуемая формула в первой выходной ячейке D5 будет следующей:
После нажатия Enter и заполнения всего столбца Output мы получим возвращаемые значения, как показано ниже.
В столбце B имя «Фред» встречается трижды, но только из них относится к отделу продаж (строка 7). Два других вхождения с похожим именем относятся к отделу маркетинга (строка 9 и строка 13). Итак, один из них является дублированием. Таким образом, мы можем ввести несколько условий в функцию СЧЁТЕСЛИМН, чтобы получить требуемый результат.
8. Поиск и выделение дубликатов с помощью правила условного форматирования
В последнем разделе мы найдем дубликаты и выделим их с помощью команды условного форматирования.Мы назначим формулу с функцией СЧЁТЕСЛИ в поле правил, а затем определим формат ячеек, в которых наша формула будет возвращать только логическое значение «ИСТИНА».
Давайте выполним следующие шаги, чтобы соответствовать критериям:
📌 Шаг 1:
➤ Выберите все имена под заголовком "Имя" в столбце B.
➤ На ленте "Главная" выберите параметр "Новое правило" в раскрывающемся списке "Условное форматирование".
Появится диалоговое окно «Новое правило форматирования».
📌 Шаг 2:
➤ Выберите тип правила "Использовать формулу для определения форматируемых ячеек".
➤ Вставьте в поле "Описание правила" следующую формулу:
➤ Нажмите Формат.
📌 Шаг 3:
➤ В окне "Формат ячеек" перейдите на вкладку "Заливка" и выберите цвет фона для повторяющихся ячеек.
➤ Нажмите OK.
📌 Шаг 4:
➤ Вы увидите предварительный просмотр формата ячейки, как показано на рисунке ниже.
➤ Нажмите OK в последний раз, и все готово.
Наконец, вы заметите выделенные дубликаты, включая первые вхождения, как показано на следующем снимке экрана.
Заключительные слова
Я надеюсь, что все эти простые методы, упомянутые выше, теперь помогут вам применять их в ваших электронных таблицах Excel, когда вам нужно идентифицировать дубликаты с помощью формул. Если у вас есть какие-либо вопросы или отзывы, пожалуйста, дайте мне знать в разделе комментариев. Или вы можете ознакомиться с другими нашими статьями, посвященными функциям Excel, на этом веб-сайте.
< /p>
< /p>
Как найти повторяющиеся значения, не удаляя их в Excel?
В Excel функция «Удалить дубликаты» может помочь пользователям быстро удалить все повторяющиеся значения из списка. Но в некоторых случаях вам может потребоваться только найти и идентифицировать повторяющиеся значения, не удаляя их, как показано на снимке экрана ниже. Как вы можете быстро справиться с этим?
Найти повторяющиеся значения без удаления с помощью Kutools for Excel
Поиск повторяющихся значений без удаления с помощью условного форматирования
Условное форматирование может быстро выделить повторяющиеся значения в списке.
<р>3. Нажмите «ОК». И дубликаты были выделены.
Поиск повторяющихся значений без удаления по формулам
Вот несколько формул для определения повторяющихся значений в списке.
Выберите пустую ячейку рядом со списком значений, которые вы хотите найти дубликаты, введите эту формулу =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$14,A2)>1,"ДУБЛИКАЦИЯ","") , перетащите заполнение обработайте ячейку, к которой нужно применить эту формулу. Смотрите скриншот:
Примечание:
<р>2. В приведенных выше формулах A2 — это ячейка, которую вы хотите найти, если повторяющееся значение, вы можете изменить ее по мере необходимости.
Найти повторяющиеся значения без удаления с помощью Kutools for Excel
На самом деле, есть удобная утилита – Выберите повторяющиеся и уникальные ячейки Kutools для Excel, которая может оказать вам хорошую услугу, быстро идентифицируя и выделяя повторяющиеся значения или уникальные значения.
После установки Kutools for Excel сделайте следующее: (Загрузите Kutools for Excel сейчас!)
<р>2. В диалоговом окне «Выбрать повторяющиеся и уникальные ячейки» выполните следующие действия:
1) Выберите нужные параметры в разделе «Правило», например, выберите «Дубликаты (кроме 1-го)», чтобы выбрать повторяющиеся значения, исключая первое появляющееся;
2) Укажите, следует ли выделять повторяющиеся ячейки с другим цветом фона или цветом шрифта;
3) Укажите, следует ли выбирать целые строки или выбирать дубликаты с учетом регистра.
Совет. Если вы хотите получить бесплатную пробную версию функции «Выбрать дубликаты и уникальные ячейки», сначала перейдите к бесплатной загрузке Kutools for Excel, а затем перейдите к применению операции в соответствии с описанными выше шагами.
Читайте также: