Как сравнить два листа Excel на наличие дубликатов: 5 быстрых способов

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

Наиболее распространенные методы поиска дубликатов на двух листах Excel:

1. Функции VLOOKUP, COUNTIF или EXACT

2. Условное форматирование

3. Power Query

4. Внешние инструменты и надстройки

5. Визуальная проверка на наличие дубликатов

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

Давайте начнем!

Оглавление

• Образцы рабочих листов и данных
• 1. Использование функций VLOOKUP, COUNTIF или EXACT для поиска дубликатов
  • А. Как вы используете функцию ВПР для поиска дубликатов на двух листах?
    • Что делать, если вы работаете с разными книгами?
  • B. Как вы используете функцию СЧЁТЕСЛИ для поиска дубликатов на листах?
  • C. Как вы используете функцию EXACT для поиска дубликатов на листах?
• 2. Как использовать условное форматирование для повторяющихся строк
  • Как использовать диспетчер правил условного форматирования
• 3. Как использовать Power Query для поиска дубликатов на листах
• 4. Инструменты и надстройки для выявления дубликатов на листах
• 5. Как визуально проверить наличие дубликатов на двух листах
• 3 совета по подготовке листов Excel
• Как обрабатывать ошибки и несоответствия
• Последние мысли

Образцы рабочих листов и данных

Чтобы следовать примерам из этой статьи, создайте новую книгу Excel с этими данными в столбце A первого листа:

• Яблоко

• Апельсин

• Груша

• Клубника

Поместите эти данные в столбец A второго листа:

• Груша

• Клубника

• Яблоко

• Ананас

Ваши рабочие листы выглядят так:

После того, как вы настроили рабочие листы, мы можем перейти к различным способам поиска дубликатов на двух листах, начиная с функций ВПР, СЧЁТЕСЛИ и ТОЧНО.

Пойдем!

1. Использование функций VLOOKUP, COUNTIF или EXACT для поиска дубликатов

В Excel есть три встроенные функции, упрощающие поиск дубликатов: ВПР , СЧЁТЕСЛИ и ТОЧНО .

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

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

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

А. Как вы используете функцию ВПР для поиска дубликатов на двух листах?

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

=ВПР(искомое_значение, массив_таблиц, индекс_столбца, [диапазон_просмотра])

• lookup_value : значение, которое вы хотите найти в первом столбце table_array.

• table_array : диапазон ячеек, содержащих данные, в которых вы хотите выполнить поиск.

• col_index_num : номер столбца в table_array, из которого вы хотите получить значение.

• range_lookup : необязательно. Это либо ИСТИНА (приблизительное совпадение), либо ЛОЖЬ (точное совпадение). Значение по умолчанию — ИСТИНА.

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

Например, это ссылка на ячейки с A2 по A5 на листе 2 той же книги: Sheet 2!$A$2:$A$5.

Чтобы использовать функцию ВПР в образце электронной таблицы, выполните следующие действия:

1. Выберите ячейку B2, чтобы отобразить первый результат сравнения.

2. Введите следующую формулу: =ВПР(A2,Лист2!$A$2:$A$5, 1, ЛОЖЬ).

3. Нажмите Enter, чтобы отобразить результат сравнения.

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

Результаты будут выглядеть как на картинке ниже:

Вы можете привести в порядок отображение, показывая удобное сообщение вместо ошибки, когда дубликат не найден.

Например, эта формула будет отображать «Да» или «Нет» для найденных и не найденных значений соответственно:

=ЕСЛИ(ЕСНА(ВПР(A2, Лист 2!$A$2:$A$5, 1, ЛОЖЬ)), «Нет», «Да»)

Больше примеров этой функции есть в нашей статье об использовании ВПР для сравнения двух столбцов .

Что делать, если вы работаете с разными книгами?

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

Вам нужно:

• Заключите имя книги Excel в скобки.

• Следите за названием рабочего листа

• Заключите книгу и рабочий лист в кавычки.

Например, если ячейки находятся на листе с именем Sheet2 в книге с именем «WB 2.xlsx», формат будет выглядеть следующим образом:

'[WB 2.xlsx]Лист2'!$A$2:$A$5

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

B. Как вы используете функцию СЧЁТЕСЛИ для поиска дубликатов на листах?

Функция СЧЕТЕСЛИ в Excel используется для подсчета количества ячеек в указанном диапазоне, которые соответствуют заданным критериям.

Чтобы сравнить несколько листов, вы можете подсчитать количество ячеек на втором листе, совпадающее с ячейкой на первом листе.

Это синтаксис функции:

=СЧЁТЕСЛИ(диапазон, критерии)

• Диапазон : диапазон ячеек, которые вы хотите подсчитать на основе указанных критериев.

• Критерии : условие, которое должно быть выполнено для подсчета ячейки.

Чтобы использовать функцию с демонстрационными данными, выполните следующие действия:

1. Выберите ячейку B2, чтобы отобразить первый результат сравнения.

2. Введите эту формулу: =СЧЁТЕСЛИ(Лист2!$A$2:$A$5, A2)

3. Нажмите Enter, чтобы отобразить результат сравнения.

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

Функция найдет одно совпадение для одних ячеек и ни одного для других. В ячейке сравнения отображается количество. Вот результат выборки данных:

Функцию СЧЁТЕСЛИ можно использовать для других полезных задач, таких как подсчёт непустых ячеек в Excel .

C. Как вы используете функцию EXACT для поиска дубликатов на листах?

Функцию EXACT в Excel также можно использовать для поиска дубликатов в одних и тех же ячейках на двух разных листах Excel. Синтаксис:

=ТОЧНО(текст1, текст2)

• text1 — это первая текстовая строка, которую вы хотите сравнить.

• text2 — это вторая текстовая строка, которую вы хотите сравнить.

Следуй этим шагам:

1. Выберите ячейку B2.

2. Введите формулу =ТОЧНО(A2, Лист2!A2)

3. Нажмите Enter, чтобы отобразить результат сравнения. Формула вернет TRUE, если оба значения идентичны, или FALSE в противном случае.

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

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

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

Функции VLOOKUP, COUNTIF и EXACT полезны для поиска дубликатов, но Excel — универсальная программа, и есть и другие варианты. В следующем разделе мы рассмотрим, как можно использовать условное форматирование для выявления дубликатов на двух листах.

2. Как использовать условное форматирование для повторяющихся строк

В этом разделе вы узнаете, как использовать условное форматирование для поиска и выделения повторяющихся строк на двух листах Excel.

Чтобы создать правило условного форматирования, выполните следующие действия:

1. Выберите диапазон ячеек, содержащих данные (в данном случае A2: A5).

2. Нажмите на вкладку «Главная» на ленте Excel.

3. Нажмите «Условное форматирование» в группе «Стили».

4. Выберите «Новое правило» в раскрывающемся меню.

Следующая задача — предоставить формулу для использования вашего правила. Следуй этим шагам:

1. Выберите «Использовать формулу, чтобы определить, какие ячейки форматировать» в диалоговом окне.

2. Введите следующую формулу: =СЧЁТЕСЛИ(Лист2!$A$2:$A$5, A2) > 0

Наконец, вы применяете форматирование, которое вы предпочитаете для повторяющихся ячеек.

1. Нажмите кнопку «Формат», чтобы открыть диалоговое окно «Формат ячеек».

2. Выберите формат, например, заполните дубликаты желтым цветом фона.

3. Нажмите «ОК».

Ваши повторяющиеся данные теперь выделены желтым цветом.

Как использовать диспетчер правил условного форматирования

Создав правило условного форматирования, вы можете управлять им с помощью Диспетчера правил условного форматирования.

Чтобы получить доступ к менеджеру:

1. Перейдите на вкладку Главная.

2. Нажмите «Условное форматирование».

3. Выберите «Управление правилами».

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

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

1. Выберите диапазон, который вы хотите сравнить на втором листе.

2. Перейдите в диспетчер правил условного форматирования.

3. Выберите правило, нажмите «Дублировать правило», а затем нажмите «Изменить правило».

4. Замените «Лист2» ​​именем первого листа для сравнения.

Теперь, когда вы применили правило условного форматирования к обоим листам, дубликаты будут выделены в соответствии с выбранным вами форматированием.

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

Условное форматирование может показаться немного примитивным. Если вам нужен более точный контроль, Power Query может быть ответом! В следующем разделе мы рассмотрим, как вы можете использовать Power Query для поиска дубликатов.

3. Как использовать Power Query для поиска дубликатов на листах

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

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

1. Щелкните правой кнопкой мыши диапазон ячеек.

2. Выберите «Получить данные из таблицы/диапазона».

3. Измените имя таблицы на что-нибудь подходящее.

После импорта обоих листов первая задача — объединить данные:

1. Перейдите на вкладку Данные.

2. Нажмите «Получить данные».

3. Выберите «Объединить запросы».

4. Выберите «Объединить» и выберите две таблицы.

5. Нажмите на два ключевых столбца.

6. Выберите «Внутренний» в качестве «Вид присоединения» и нажмите «ОК».

Редактор Power Query откроется с объединенными данными из обеих таблиц на вашем листе Excel. Вы увидите два столбца, по одному из каждой таблицы. Поскольку вас интересуют только повторяющиеся значения, вы можете удалить второй столбец.

Вы можете нажать «Закрыть и загрузить» в редакторе Power Query, чтобы загрузить дубликаты на новый лист.

Чтобы изучить больше аспектов этой мощной функции, следуйте примерам в нашей статье о том, как использовать Power Query в Excel .

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

4. Инструменты и надстройки для выявления дубликатов на листах

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

Spreadsheet Compare — это инструмент Microsoft, который позволяет сравнивать две книги, выделяя различия и легко находя дубликаты. Вы можете загрузить его с веб-сайта Microsoft.

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

1. Перейдите на вкладку Вставка.

2. Нажмите «Получить надстройку».

3. Найдите «Дубликат».

4. Нажмите «Добавить» на инструменте по вашему выбору.

5. Как визуально проверить наличие дубликатов на двух листах

Если ничего не помогает, используйте свои глаза! Диалоговое окно «Упорядочить окна» в Excel позволяет просматривать несколько рабочих листов или книг рядом друг с другом.

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

1. Нажмите на вкладку «Вид» на ленте Excel.

2. Нажмите «Упорядочить все» в группе «Окно».

3. Выберите вариант расположения, например, «Вертикальное» или «Горизонтальное».

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

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

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

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

3 совета по подготовке листов Excel

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

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

Вот три предложения для обеспечения точного сравнения:

1. Расположите данные в одинаковом порядке на обоих листах. Это облегчает эффективную работу функций Excel.

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

3. Удалите ненужные пустые строки или столбцы, так как они могут помешать процессу сравнения.

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

Как обрабатывать ошибки и несоответствия

Несоответствия в ваших данных могут повлиять на процесс сравнения. Вот четыре совета по устранению несоответствий:

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

2. Убедитесь, что для дат, чисел и других типов данных используется согласованное форматирование.

3. Проверьте свои данные на наличие отсутствующих или неправильных записей и при необходимости обновите их.

4. Стандартизируйте сокращения или несовместимые соглашения об именах в своих наборах данных.

Последние мысли

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

Выбор метода зависит от потребностей пользователя, размера и сложности набора данных и желаемого результата. Для небольших наборов данных и простых сравнений может быть достаточно использования ВПР , СЧЁТЕСЛИ или условного форматирования.

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

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

Поверьте нам, когда вы станете лучше в этом, вы легко справитесь со своими задачами с данными и произведете впечатление на всех вокруг вас!