Як порівняти два аркуші Excel на дублікати: 5 швидких способів

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

Найпоширенішими методами пошуку дублікатів на двох аркушах Excel є використання:

1. Функції VLOOKUP, COUNTIF або EXACT

2. Умовне форматування

3. Power Query

4. Зовнішні інструменти та надбудови

5. Візуальна перевірка на наявність дублікатів

У цій статті крок за кроком описано ці п’ять методів визначення та обробки дублікатів на кількох аркушах. Наприкінці ви побачите, наскільки легко керувати повторюваними значеннями.

Давайте розпочнемо!

Зміст

• Зразки аркушів і даних
• 1. Використання функцій VLOOKUP, COUNTIF або EXACT для пошуку дублікатів
  • A. Як використовувати функцію VLOOKUP для пошуку дублікатів на двох аркушах?
    • Що робити, якщо ви працюєте з різними книгами?
  • B. Як використовувати функцію COUNTIF для пошуку дублікатів на аркушах?
  • C. Як використовувати функцію EXACT для пошуку дублікатів на аркушах?
• 2. Як використовувати умовне форматування для повторюваних рядків
  • Як користуватися менеджером правил умовного форматування
• 3. Як використовувати Power Query для пошуку дублікатів на робочих аркушах
• 4. Інструменти та надбудови для виявлення дублікатів на робочих аркушах
• 5. Як візуально перевірити наявність дублікатів на двох аркушах
• 3 поради щодо підготовки робочих аркушів Excel
• Як виправляти помилки та невідповідності
• Заключні думки

Зразки аркушів і даних

Щоб виконати приклади в цій статті, створіть нову книгу Excel із цими даними в стовпці A першого аркуша:

• Яблуко

• Помаранчевий

• груша

• Полуниця

Помістіть ці дані в колонку А другого аркуша:

• груша

• Полуниця

• Яблуко

• Ананас

Ваші аркуші виглядають так:

Після того, як ви налаштували робочі аркуші, ми можемо розглянути різні способи пошуку дублікатів на двох аркушах, починаючи з функцій VLOOKUP, COUNTIF і EXACT.

Ходімо!

1. Використання функцій VLOOKUP, COUNTIF або EXACT для пошуку дублікатів

Excel має три вбудовані функції, які можуть полегшити пошук дублікатів: VLOOKUP , COUNTIF і EXACT .

Ці функції розроблено, щоб допомогти вам знаходити, підраховувати та порівнювати дані у ваших електронних таблицях, що робить їх ідеальними інструментами для пошуку повторюваних записів.

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

У наступних розділах ми розглянемо, як використовувати функції VLOOKUP, COUNTIF і EXACT для ефективного виявлення дублікатів у ваших аркушах Excel.

A. Як використовувати функцію VLOOKUP для пошуку дублікатів на двох аркушах?

VLOOKUP означає вертикальний пошук. Ця функція використовується для пошуку повторюваних значень між двома стовпцями. Ось синтаксис:

=VLOOKUP(пошукове_значення, масив_таблиці, номер_індексу_стовпця, [діапазон_пошуку])

• lookup_value : значення, яке потрібно шукати в першому стовпці table_array.

• table_array : діапазон комірок, що містять дані, у яких потрібно виконати пошук.

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

• діапазон_пошуку : необов'язковий. Це TRUE (приблизний збіг) або FALSE (точний збіг). Типовим значенням є TRUE.

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

Наприклад, це посилання на клітинки A2–A5 на аркуші 2 тієї самої книги: Аркуш 2!$A$2:$A$5.

Щоб використовувати функцію VLOOKUP у зразку електронної таблиці, виконайте такі дії:

1. Виберіть клітинку B2, щоб відобразити перший результат порівняння.

2. Введіть цю формулу: =VLOOKUP(A2,Sheet2!$A$2:$A$5, 1, FALSE).

3. Натисніть Enter, щоб відобразити результат порівняння.

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

Результати виглядатимуть як на малюнку нижче:

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

Наприклад, ця формула відображатиме «Так» або «Ні» для знайдених і не знайдених значень відповідно:

=IF(ISNA(VLOOKUP(A2, Аркуш 2!$A$2:$A$5, 1, FALSE)), “Ні”, “Так”)

Більше прикладів цієї функції можна знайти в нашій статті.

Що робити, якщо ви працюєте з різними книгами?

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

Тобі потрібно:

• У дужках уведіть назву робочої книги Excel

• Слідуйте за назвою аркуша

• Робочий зошит і аркуш візьміть у лапки.

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

'[WB 2.xlsx]Аркуш2'!$A$2:$A$5

Перш ніж ввести формулу, закрийте другу книгу. Інакше ви отримаєте помилку.

B. Як використовувати функцію COUNTIF для пошуку дублікатів на аркушах?

Функція COUNTIF в Excel використовується для підрахунку кількості комірок у заданому діапазоні, які відповідають заданим критеріям.

Щоб порівняти кілька аркушів, ви можете підрахувати кількість клітинок на другому аркуші, яка відповідає клітинці на першому аркуші.

Ось синтаксис функції:

=COUNTIF(діапазон, критерії)

• Діапазон : діапазон комірок, які потрібно підрахувати на основі вказаних критеріїв.

• Критерії : умова, яка має бути виконана, щоб клітинка була підрахована.

Щоб використовувати функцію з зразками даних, виконайте такі дії:

1. Виберіть клітинку B2, щоб відобразити перший результат порівняння.

2. Введіть цю формулу: =COUNTIF(Sheet2!$A$2:$A$5, A2)

3. Натисніть Enter, щоб відобразити результат порівняння.

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

Функція знайде один збіг для деяких клітинок і не знайде для інших. Комірка порівняння відображає кількість. Ось результат вибірки даних:

Функцію COUNTIF можна використовувати для інших корисних завдань, наприклад.

C. Як використовувати функцію 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. Введіть таку формулу: =COUNTIF(Sheet2!$A$2:$A$5, A2) > 0

Нарешті, ви застосовуєте форматування, яке ви віддаєте перевагу, для дублікатів клітинок.

1. Натисніть кнопку «Формат», щоб відкрити діалогове вікно «Формат клітинок».

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

3. Натисніть OK.

Ваші дублікати даних тепер виділені жовтим кольором.

Як користуватися менеджером правил умовного форматування

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

Щоб отримати доступ до менеджера:

1. Перейдіть на вкладку «Головна».

2. Натисніть Умовне форматування.

3. Виберіть «Керувати правилами».

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

Щоб застосувати те саме правило до іншого аркуша, виконайте такі дії:

1. Виберіть діапазон, який потрібно порівняти, на другому аркуші.

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

3. Виберіть правило, натисніть «Дублювати правило», а потім натисніть «Редагувати правило».

4. Замініть «Sheet2» назвою першого аркуша для порівняння.

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

Обов’язково налаштуйте діапазон і посилання на клітинки у формулах, щоб охопити всі дані, які потрібно порівняти.

Умовне форматування може здатися трохи примітивним. Якщо вам потрібен кращий контроль, тоді Power Query може стати відповіддю! У наступному розділі ми розповідаємо, як можна використовувати Power Query для пошуку дублікатів.

3. Як використовувати Power Query для пошуку дублікатів на робочих аркушах

Power Query — це засіб перетворення та підготовки даних у Microsoft Excel. Виявлення однакових значень є лише одним із багатьох завдань аналізу, які можна виконати за допомогою інструменту.

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

1. Клацніть правою кнопкою миші діапазон клітинок.

2. Виберіть «Отримати дані з таблиці/діапазону».

3. Змініть назву таблиці на щось відповідне.

Після імпортування обох аркушів першим завданням є об’єднання даних:

1. Перейдіть на вкладку Дані.

2. Натисніть «Отримати дані».

3. Виберіть «Об'єднати запити».

4. Виберіть «Об’єднати» та виберіть дві таблиці.

5. Натисніть на два ключових стовпці.

6. Виберіть «Inner» як «Join Kind» і натисніть OK.

Редактор Power Query відкриється з об’єднаними даними з обох таблиць на аркуші Excel. Ви побачите два стовпці, по одному з кожної таблиці. Оскільки вас цікавлять лише повторювані значення, ви можете видалити другий стовпець.

Ви можете натиснути «Закрити та завантажити» в редакторі Power Query, щоб завантажити дублікати на новий аркуш.

Щоб дослідити більше аспектів цієї потужної функції, дотримуйтесь прикладів у нашій статті на .

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

4. Інструменти та надбудови для виявлення дублікатів на робочих аркушах

Зовнішні інструменти та надбудови можуть запропонувати розширені функції, які можуть бути недоступні у власних функціях Excel. Ці інструменти можуть додатково спростити процес порівняння аркушів на наявність дублікатів.

Порівняння електронних таблиць — це інструмент Microsoft, який дозволяє порівнювати дві робочі книги пліч-о-пліч, виділяючи відмінності та легко виявляючи дублікати. Ви можете завантажити його з веб-сайту Microsoft.

Є кілька надбудов, які можна встановити, щоб автоматизувати процес пошуку дублікатів. Одним із прикладів є « Duplicate Remover ». Щоб установити надбудову:

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

2. Натисніть «Отримати надбудову».

3. Знайдіть «Дублікат».

4. Натисніть «Додати» на інструменті за вашим вибором.

5. Як візуально перевірити наявність дублікатів на двох аркушах

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

Хоча він не знаходить дублікатів безпосередньо, він може допомогти вам візуально порівняти дані на аркушах або книгах, щоб виявити дублікати. Виконайте такі дії:

1. Клацніть вкладку «Перегляд» на стрічці Excel.

2. Натисніть «Упорядкувати все» в групі «Вікно».

3. Виберіть варіант розташування, наприклад «Вертикально» або «Горизонтально».

Це відобразить обидва аркуші поруч або один над одним. Тепер ви можете вручну порівнювати дані на кожному аркуші, щоб виявити дублікати.

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

Зауважте, що цей метод неефективний для великих наборів даних, оскільки вимагає ручного порівняння. Використання інших методів у цій статті буде ефективнішим для пошуку дублікатів у великих наборах даних.

І це останній з наших поширених методів пошуку повторюваних значень на аркушах Excel! У наступному розділі ми дамо вам кілька порад щодо підготовки робочих аркушів.

3 поради щодо підготовки робочих аркушів Excel

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

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

Ось три поради, щоб забезпечити точні порівняння:

1. Розмістіть дані в однаковому порядку на обох аркушах. Це полегшує ефективну роботу функцій Excel.

2. Нормалізуйте свої дані за допомогою узгодженого форматування, використання великих літер і типів даних. Це запобіжить невідповідності записів через незначні відмінності.

3. Видаліть непотрібні порожні рядки або стовпці, оскільки вони можуть заважати процесу порівняння.

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

Як виправляти помилки та невідповідності

Невідповідності ваших даних можуть вплинути на процес порівняння. Ось чотири поради щодо вирішення невідповідностей:

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

2. Переконайтеся, що для дат, чисел та інших типів даних використовується узгоджене форматування.

3. Перевірте свої дані на наявність відсутніх або неправильних записів і за потреби оновіть їх.

4. Стандартизуйте абревіатури або непослідовні угоди про найменування у своїх наборах даних.

Заключні думки

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

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

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

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

Повірте нам, коли ви станете кращими в цьому, ви легко впораєтеся зі своїми завданнями щодо даних і вразите всіх навколо!