Як порівняти два стовпці в Excel і видалити дублікати (виділити, розфарбувати, перемістити)

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

Excel — це дуже потужна і дійсно класна програма для створення та обробки великих обсягів даних. Якщо у вас є кілька робочих книг з даними (або одна величезна таблиця), то ви, ймовірно, захочете порівняти 2 стовпці, знайти повторювані значення, а потім щось з ними зробити, наприклад, видалити, виділити або очистити вміст. Стовпці можуть бути в одній таблиці, бути суміжними або несуміжними, можуть розташовуватися на 2 різних аркушах або навіть в різних книгах.

Уявіть, що у нас є 2 стовпці з іменами людей – по 5 імен у стовпці A і 3 імена в стовпчик B. Вам потрібно порівняти імена в цих двох колонках і знайти дублікати. Як ви розумієте, це вигадані дані, взяті виключно для прикладу. У реальних таблицях ми маємо справу з тисячами або навіть десятками тисяч записів.

Варіант A: обидва стовпці знаходяться на одному аркуші. Наприклад, стовпчик A і колонка B.

Варіант B: Колонки знаходяться на різних аркушах. Наприклад, стовпчик A на аркуші Sheet2 і колонка A на аркуші Sheet3.

Excel 2013, 2010 і 2007 мають вбудований інструмент Видалити дублікати (Видалити дублікати), але в цій ситуації він безсилий, оскільки не може порівнювати дані у 2 стовпцях. Крім того, він може видаляти лише дублікати. Немає інших опцій, таких як виділення або зміна кольорів. І точка!

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

• Порівняйте 2 стовпці та знайдіть дублікати за допомогою формул
  
  • Варіант А: обидва стовпці знаходяться на одному аркуші
  • Варіант Б: колонки знаходяться на різних аркушах або в різних робочих зошитах
• Обробка знайдених дублікатів
  
  • Показувати лише повторювані рядки в стовпці A
  • Змініть колір або виділіть знайдені дублікати
  • Видаліть дублікати з першого стовпця

Порівняйте 2 стовпці в Excel і знайдіть повторювані записи за допомогою формул

Варіант А: обидва стовпці знаходяться на одному аркуші

1. У першу порожню комірку (в нашому прикладі це комірка С1) записуємо таку формулу:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   У нашій формулі A1 це перша комірка першого стовпця, яку ми будемо порівнювати. $1 B$ и $10000 B$ це адреси першої та останньої клітинок другого стовпця, з якими будемо проводити порівняння. Зверніть увагу на абсолютні посилання – буквам стовпців і номерам рядків передує знак долара ($). Я використовую абсолютні посилання, щоб адреси клітинок залишалися незмінними під час копіювання формул.

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

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Замість цього "Only"І"дублювати» Ви можете написати власні мітки, наприклад «Не знайдені"І"знайдений“, або залишити лише “дублювати' і введіть пробіл замість другого значення. В останньому випадку комірки, для яких не знайдено дублікатів, залишаться порожніми, і, я вважаю, таке представлення даних найбільш зручно для подальшого аналізу.

2. Тепер давайте скопіюємо нашу формулу в усі клітинки стовпця C, аж до нижнього рядка, який містить дані в стовпці A. Для цього переведіть вказівник миші в правий нижній кут комірки C1, вказівник матиме форму чорного перехрестя, як показано на малюнку нижче:Натисніть і утримуйте ліву кнопку миші та перетягніть рамку рамки вниз, виділяючи всі комірки, у які потрібно вставити формулу. Коли всі необхідні комірки будуть виділені, відпустіть кнопку миші:

   

Порада: У великих таблицях копіювання формули відбуватиметься швидше, якщо використовувати комбінації клавіш. Виділіть клітинку C1 і натисніть Ctrl + C (щоб скопіювати формулу в буфер обміну), потім натисніть Ctrl + Shift + End (щоб вибрати всі непорожні комірки в стовпці C) і нарешті натисніть Ctrl + V (щоб вставити формулу в усі виділені клітинки).

3. Чудово, тепер усі повторювані значення позначені як "дублювати":

Варіант Б: дві колонки на різних аркушах (у різних робочих зошитах)

1. У першій клітинці першого порожнього стовпця на аркуші Sheet2 (у нашому випадку це стовпець B) введіть таку формулу:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   тут Sheet3 - назва аркуша, на якому знаходиться 2-й стовпець, і $ A $ 1: $ A $ 10000 це адреси комірок від 1-го до останнього в цьому 2-му стовпці.

2. Скопіюйте формулу в усі клітинки стовпця B (те саме, що варіант А).
3. Отримуємо такий результат:

Обробка знайдених дублікатів

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

Показувати лише повторювані рядки в стовпці A

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

Клацніть правою кнопкою миші та виберіть у контекстному меню вставка (Вставити):

Дайте назви стовпцям, наприклад, «ІМ'Я"І"дублікат?» Потім відкрийте вкладку дані (Дані) і натисніть фільтр (Фільтр):

Після цього натисніть маленьку сіру стрілку поруч із «дублікат?« відкрити меню фільтрів; зніміть позначки з усіх пунктів у цьому списку, крім дублювати, і натисніть OK.

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

Щоб знову відобразити всі рядки стовпця Аклацніть символ фільтра в стовпці В, який тепер виглядає як воронка з маленькою стрілкою, і виберіть Вибрати всі (Вибрати все). Або ви можете зробити те саме через стрічку, клацнувши дані (Дані) > Вибір і фільтр (Сортувати та фільтрувати) > Очистити (Очистити), як показано на знімку екрана нижче:

Змініть колір або виділіть знайдені дублікати

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

У цьому випадку відфільтруйте дублікати, як показано вище, виберіть усі відфільтровані комірки та натисніть Ctrl + 1щоб відкрити діалогове вікно Формат ячеек (формат комірки). Для прикладу змінимо колір заливки комірок у рядках з дублікатами на яскраво-жовтий. Звичайно, ви можете змінити колір заливки за допомогою інструмента Заповнювати Вкладка (Колір заливки). Головна (Додому), але перевага діалогового вікна Формат ячеек (Формат комірки), оскільки ви можете налаштувати всі параметри форматування одночасно.

Тепер ви точно не пропустите жодної комірки з дублікатами:

Видалення повторюваних значень з першого стовпця

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

Якщо 2 стовпці, які ви порівнюєте, знаходяться на різних аркушах, тобто в різних таблицях клацайте правою кнопкою миші виділений діапазон і вибирайте Видалити рядок (Видалити рядок):

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

Якщо 2 стовпці знаходяться на одному аркуші, близько один до одного (суміжні) або не близько один до одного (не сусідні), то процес видалення дублікатів буде трохи складнішим. Ми не можемо видалити весь рядок із повторюваними значеннями, оскільки це також видалить клітинки з другого стовпця. Таким чином, щоб залишити лише унікальні записи в стовпці А, зробити це:

1. Відфільтруйте таблицю, щоб відобразити лише повторювані значення, і виберіть ці клітинки. Клацніть їх правою кнопкою миші та виберіть у контекстному меню Очистити вміст (прозорий зміст).
2. Очистіть фільтр.
3. Виділіть усі клітинки в стовпці А, починаючи з клітини A1 аж до самого низу, де містяться дані.
4. Натисніть дані (Дані) і натисніть Сортувати від А до Я (Сортувати від А до Я). У діалоговому вікні, що відкриється, виберіть Продовжте поточний вибір (Сортувати в межах зазначеного вибору) і натисніть кнопку Black (Сортування):
5. Видаліть стовпець з формулою, він вам більше не знадобиться, відтепер у вас тільки унікальні значення.
6. Ось і все, тепер колонка А містить лише унікальні дані, яких немає в стовпці В:

Як бачите, видалити дублікати з двох стовпців в Excel за допомогою формул не так вже й складно.