Как найти повторяющиеся значения в таблице Excel
В этой статье мы рассмотрим несколько подходов к одной из самых распространенных и, на мой взгляд, важных задач в Excel: как найти повторяющиеся значения в ячейках и столбцах таблицы.
При работе с большими наборами данных в Excel или объединении нескольких небольших электронных таблиц в большие листы вы можете столкнуться с большим количеством идентичных строк.
И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и более ранних.
Функция СЧЁТЕСЛИ будет самой простой в использовании и в то же время эффективной в этом случае. Только с его помощью можно определить не только неуникальные позиции, но и их первые вхождения в столбец. Давайте посмотрим на разницу на примерах.
Поиск повторяющихся значений включая первые вхождения.
Предположим, у нас есть набор индикаторов в столбце A, среди которых, вероятно, есть такие же. Это могут быть номера заказов, названия продуктов, имена клиентов и другие данные. Если ваша задача — найти их, вам подойдет следующая формула:
= СЧЁТЕСЛИ (LA: LA; LA2)> 1
Где A2 — первая ячейка области поиска.
Просто введите это выражение в любую ячейку и потяните вниз по всему столбцу, который вы хотите проверить на наличие дубликатов.
Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если есть совпадение. А для значений, которые встречаются только 1 раз, отображается ЛОЖЬ.
Запрос! Если вы ищете повторы в определенной области, а не во всем столбце, укажите желаемый диапазон и «зафиксируйте» его знаками $. Это значительно ускорит расчеты. Например, если вы смотрите в формате A2: A8, используйте
= СЧЁТЕСЛИ ($ A $ 2: $ A $ 8; A2)> 1
Если вас смущают значения ИСТИНА и ЛОЖЬ в столбце статуса и вы не хотите помнить, какое из них означает повторение, а какое уникально, заключите свой СЧЁТЕСЛИ в функцию ЕСЛИ и укажите любые слова, которые должны соответствовать повторяющимся и уникальным:
= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 17; A2)> 1, «Дублировать», «Уникальный»)
Если вам нужна формула для указания только дубликатов, замените «Уникальный» пробелом («»):
= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 17; A2)> 1, «Дублировать»;»»)
В этом случае Excel будет отмечать только неуникальные записи, оставляя пустую ячейку перед уникальными.
Поиск неуникальных значений без учета первых вхождений
Вы, наверное, заметили, что в приведенных выше примерах все совпадения упоминаются как дубликаты. Но часто задача состоит в том, чтобы найти только повторения, не трогая первые вхождения. То есть когда что-то впервые встречается, это точно еще не может быть дубликатом.
Если вам просто нужно указать совпадения, давайте немного изменим:
= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2; A2)> 1, «Дублировать»;»»)
На скриншоте ниже вы можете увидеть эту формулу в действии.
легко понять, что это не означает первое вхождение слова, а отсчет начинается со второго.
Чувствительный к регистру поиск дубликатов
Я хотел бы обратить ваше внимание на то, что, хотя приведенные выше формулы находят 100% дубликатов, есть тонкий момент: они не чувствительны к регистру. Может, для тебя это не важно. Но если abc, abc и abc — три разных параметра в ваших данных, то этот пример для вас.
Как вы уже догадались, использованные нами ранее выражения не справятся с этой задачей. Здесь мы должны выполнить более тонкий поиск, в котором нам поможет следующая матричная функция:
{= ЕСЛИ (СУММ ((- ТОЧНО ($ A $ 2: $ A $ 17, A2)))
Не забывайте, что формулы массива вводятся нажатием Ctrl + Shift + Enter.
Вернувшись к оглавлению, используйте функцию ТОЧНО, чтобы сравнить целевую ячейку со всеми другими ячейками в выбранной области. Результат возвращается как ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из единиц и нулей с помощью оператора (—).
Затем функция СУММ складывает эти числа. И если результат больше 1, функция ЕСЛИ сообщает о найденном дубликате.
Если вы посмотрите на снимок экрана ниже, вы можете убедиться, что поиск действительно чувствителен к регистру при обнаружении дубликатов:
Смородина и арбуз, которые встречаются дважды, в нашем поиске не помечаются, так как регистр их первых букв разный.
Как извлечь дубликаты из диапазона.
Формулы, которые мы описали выше, позволяют вам находить дубликаты в определенном столбце. Но мы часто говорим о нескольких столбцах, то есть о диапазоне данных.
Давайте рассмотрим это на примере числовой матрицы. К сожалению, этот метод не работает со значениями символов.
Использование формулы массива
{= ИНДЕКС (МАЛЕНЬКИЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11)> 1; $ A $ 2: $ E $ 11); СТРОКА ($ 1: $ 100)); МАЛЕНЬКИЙ (ЕСЛИОШИБКА (ЕСЛИ (ПОИСК (МАЛЫЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11))> 1; $ A $ 2: $ E $ 11); RIGA ($ 1: $ 100)); SMALL (IF (COUNTIF ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11)> 1; $ A $ 2: $ E $ 11); LINE ($ 1: $ 100)); 0) = LINE ($ 1: $ 100); LINE ($ 1: $ 100)); «»); STRING () — 1))}
вы можете получить постоянно растущий список дубликатов. Для этого введите это выражение в нужную ячейку и нажмите Ctrl + Alt + Enter.
Затем перетащите маркер заливки вниз по мере необходимости.
Чтобы удалить сообщения об ошибках, когда заканчиваются повторяющиеся значения, вы можете использовать функцию SEERROR:
= ЕСЛИОШИБКА (ИНДЕКС (МАЛЫЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11)> 1, $ A $ 2: $ E $ 11), СТРОКА ($ 1: $ 100)); МАЛЕНЬКИЙ (ЕСЛИОШИБКА (ЕСЛИ (ПОИСК (МАЛЫЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11))> 1; $ A $ 2: $ E $ 11); СТРОКА ($ 1: $ 100)); МАЛЕНЬКИЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11)> 1; $ A $ 2: $ E $ 11); LINE ($ 1: $ 100)); 0) = LINE ($ 1: $ 100); LINE ($ 1: $ 100)); «»); LINE () — 1));»»)
Также обратите внимание, что приведенное выше выражение должно быть написано во второй строке. В результате над ним будет пустая строка.
Поэтому, если вам нужно поместить его, например, в ячейку K4, замените выражение LINE () — 1 в конце на LINE () — 3.
Обнаружение повторяющихся строк
Мы посмотрели, как найти одни и те же данные в разных ячейках. Что, если бы вам пришлось искать повторяющиеся строки?
Есть метод, который можно использовать, если вам просто нужно выделить одни и те же строки, но не удалять их.
Итак, у нас есть данные о товарах и покупателях.
Давайте создадим формулу справа от наших данных, которая объединит содержимое всех ячеек слева от них.
Допустим, данные хранятся в столбцах A: C. В ячейку D2 записываем:
= A2, B2 и C2
Добавьте следующую формулу в ячейку E2. Он покажет, сколько раз встречается значение, которое мы получили в столбце D:
= СЧЁТЕСЛИ (D: D; D2)
Копировать для всех строк данных.
Столбец E показывает, сколько раз эта строка появлялась в столбце D. Неповторяющиеся строки будут иметь значение 1. Повторяющиеся строки имеют значение больше 1, которое указывает, сколько раз эта строка была найдена.
Если вас не интересует конкретный столбец, не включайте его в выражение в D. Например, если вы хотите найти совпадающие строки без учета значения «Клиент», удалите ссылку на ячейку C2 из формулы слияния.
Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel.
Теперь давайте посмотрим, как можно обойтись без формул при поиске дубликатов в таблице. Возможно, кому-то этот способ покажется более удобным, чем написание выражений Excel.
После того, как вы организовали свои данные в таблицу, вы можете применить к ним различные фильтры. Вы можете установить фильтр для таблицы по одному или нескольким столбцам. Возьмем пример.
Прежде всего, я рекомендую вам отформатировать наши данные как умную таблицу. Напоминаю: Главное меню — Форматировать в виде таблицы.
Значки фильтров отображаются в строке заголовка. Если вы нажмете на любой из них, откроется раскрывающееся меню фильтров, которое содержит всю информацию для этого столбца. Выберите любой элемент из этого списка, и Excel отобразит данные на основе этого выбора.
Вы можете снять флажок «Выбрать все», а затем выбрать один или несколько желаемых элементов. Excel покажет только те строки, которые содержат выбранные значения. Таким образом вы сможете найти дубликаты, если они есть. И все готово к их быстрому удалению.
Но при этом вы видите дубликаты только для отфильтрованного. Если данных много, поиск в этом режиме последовательного поиска будет несколько утомительным. Ведь слишком много раз придется устанавливать и заменять фильтр.
Используем условное форматирование.
Условное выделение — очень важный инструмент Excel, о котором мы говорили достаточно подробно.
Теперь я покажу, как можно найти повторяющиеся ячейки в Excel, просто выделив их цветом.
Как показано на изображении ниже, выберите «Правила выбора ячеек — Дублировать». Неуникальные данные будут выделены цветом.
Но здесь нельзя исключать первых явлений: все подсвечено.
Но эту проблему можно решить с помощью формулы условного форматирования.
= СЧЁТЕСЛИ ($ B $ 2: $ B2; B2)> 1
Вы можете увидеть результат формулы для извлечения повторяющихся значений выше. Они выделены зеленым цветом.
Чтобы освежить память, вы можете прочитать нашу статью «Как изменить цвет ячейки по значению».
Поиск совпадений при помощи команды «Найти».
Другой простой, но не очень технологичный способ — использовать интегрированный поиск.
Перейдите на вкладку «Главная» и нажмите «Найти и выбрать». Откроется диалоговое окно, в котором вы можете ввести то, что вы хотите найти в таблице. Чтобы избежать опечаток, вы можете скопировать то, что ищете, прямо из списка данных.
Затем нажимаем «Найти все» и видим все найденные дубликаты и их расположение, как на изображении ниже.
В случае, если объем информации очень большой и вам нужно ускорить поиск, сначала выберите столбец или диапазон, в котором вы хотите искать, и только потом приступайте к работе. В противном случае Excel будет искать все доступные данные, что, очевидно, медленнее.
Этот метод занимает даже больше времени, чем использование фильтра. Поэтому он используется выборочно, только для единичных значений.
Как применить сводную таблицу для поиска дубликатов.
Многие люди считают сводные таблицы слишком сложными для постоянного использования. На самом деле не все так запутанно, как кажется. Для начала я рекомендую ознакомиться с нашим руководством по созданию сводных таблиц и работе с ними.
Для более опытных давайте сразу перейдем к сути дела.
Создайте новый макет сводной таблицы. И затем мы используем ту же строку и поле значения. В нашем случае — «Товар». Поскольку название продукта является текстовым, Excel по умолчанию использует функцию СЧЁТ для вычисления этих значений, то есть подсчитывает количество. И это то, что нам нужно. Если он больше 1, есть дубликаты.
На скриншоте выше видно, что несколько товаров дублируются. И что это нам дает? А затем мы можем просто щелкнуть любое из чисел, и на новом листе Excel покажет нам, как получилась эта цифра.
Например, откуда берутся 3 повторяющихся спрайта? Щелкаем на цифре 3 и видим следующее изображение:
Я думаю, что этот метод можно использовать достаточно хорошо. Самое замечательное, что формулы не нужны.
Duplicate Remover — быстрый и эффективный способ найти дубликаты в Excel
Теперь, когда вы знаете, как использовать формулы для поиска повторяющихся значений в Excel, позвольте мне показать вам еще один быстрый, эффективный и не требующий формул способ — средство удаления дубликатов для Excel.
Этот универсальный инструмент может искать повторяющиеся или уникальные значения в столбце или сравнивать два столбца. Он может находить, выбирать и выделять повторяющиеся записи или целые повторяющиеся строки, удалять найденные дубликаты, копировать или перемещать их на другой лист. Я думаю, что практический вариант использования может заменить многие слова, поэтому давайте перейдем к нему.
Как найти повторяющиеся строки в Excel за 2 быстрых шага
Во-первых, давайте посмотрим, как работает самый простой инструмент: Quick Dedupe. Воспользуемся уже знакомой таблицей, в которой мы искали дубликаты выше по формулам:
Как видите, в таблице несколько столбцов. Чтобы найти повторяющиеся записи в этих трех столбцах, сделайте следующее:
Выберите ячейку в таблице и нажмите кнопку «Быстрое исключение дубликатов» на ленте Excel. После установки Ultimate Suite for Excel вы найдете его на вкладке «Данные» программы Ablebits в группе «Дедупликация». Это простейший инструмент поиска дубликатов.
- Умное дополнение возьмет всю таблицу и попросит вас указать следующие две вещи:
- Выберите столбцы для проверки дубликатов (в этом примере это все 3 столбца: категория, продукт и клиент).
- Выберите действие, которое вы хотите выполнить с дубликатами. Поскольку наша цель — выявить повторяющиеся строки, я выбрал «Выделить».
Помимо выделения цветом у вас есть ряд других возможностей:
- Удалить дубликаты
- Выбрать дубликаты
- Укажите их в столбце статуса
- Копировать дубликаты на новый лист
- Перейти на новый лист
Нажмите ОК и подождите несколько секунд. Готовый! И никаких формул .
Как вы можете видеть на скриншоте ниже, все строки с одинаковыми значениями были найдены в первых 3 столбцах (первые вхождения не идентифицируются как дубликаты).
Если вам нужно больше возможностей для работы с дубликатами и уникальными значениями, используйте Duplicate Remover, который может находить дубликаты с первыми вхождениями или без них, а также уникальные значения. Подробные инструкции приведены ниже.
Мастер удаления дубликатов — больше возможностей для поиска дубликатов в Excel.
В зависимости от данных, с которыми вы работаете, вы можете не захотеть рассматривать ранние экземпляры идентичных записей как дубликаты. Одно из возможных решений — использовать разные формулы для каждого сценария, как обсуждалось ранее в этой статье. Если вы ищете быстрый и точный метод без формул, попробуйте Duplicate Remover. Несмотря на свое название, он не только умеет удалять дубликаты, но и выполняет с ними другие полезные действия, о которых мы подробнее поговорим ниже. Он также умеет находить уникальные ценности.
Выберите ячейку в таблице и нажмите кнопку «Удалить дубликаты» на вкладке «Данные» в Ablebits.
- Вам предлагается 4 варианта проверки дубликатов в электронной таблице Excel:
- Дублировать без первых повторений повторяющихся записей.
- Дубликат с первым вхождением.
- Уникальные записи.
- Уникальные значения и повторяющиеся первые вхождения.
В этом примере мы выберем второй вариант, т.е. Дубликаты + 1-е вхождение:
Теперь выберите столбцы, которые вы хотите проверить на наличие дубликатов. Как и в предыдущем примере, мы возьмем первые 3 столбца:
Наконец, выберите действие, которое вы хотите выполнить с дубликатами. Как и в случае с инструментом «Быстрый поиск дубликатов», мастер удаления дубликатов может идентифицировать, выбирать, выделять, удалять, копировать или перемещать повторяющиеся данные.
Поскольку цель этого примера — продемонстрировать несколько способов определения дубликатов в Excel, давайте отметим параметр «Выделить цветом» и нажмите «Готово.
Мастеру удаления дубликатов потребуется всего несколько секунд, чтобы проверить электронную таблицу и показать результат:
Как видите, результат аналогичен предыдущему. Но здесь мы выделили дубликаты, включая первое появление повторяющихся записей.
Без формул, без стресса, без ошибок: всегда быстрый и безупречный результат 🙂
Итак, мы узнали вместе с вами различные способы обнаружения повторяющихся записей в электронной таблице Excel. В следующих статьях мы разберем, что можно сделать с этим полезным.
Если вы хотите попробовать эти инструменты для поиска дубликатов в таблицах Excel, вы можете загрузить полнофункциональную пробную версию программы. Буду очень признателен за отзыв в комментариях!
