Ви можете знайти та виділити повторювані записи між двома стовпцями за допомогою функції умовного форматування в Google Таблицях.
Працюючи в Google Таблицях з великими наборами даних, ви, ймовірно, зіткнетеся з проблемою, коли вам доведеться мати справу з багатьма повторюваними значеннями. Хоча деякі повторювані записи розміщені навмисно, а інші є помилками. Це особливо вірно, коли ви співпрацюєте над одним аркушем з командою.
Коли справа доходить до аналізу даних у Google Таблицях, можливість відфільтровувати дублікати може бути важливою та зручною. Хоча Google Таблиці не мають вбудованої підтримки пошуку дублікатів у аркушах, він пропонує кілька способів порівняння, ідентифікації та видалення повторюваних даних у клітинках.
Іноді потрібно порівняти кожне значення в стовпці з іншим стовпцем і з’ясувати, чи є в ньому дублікати, і навпаки. У Google Таблицях ви можете легко знайти дублікати між двома стовпцями за допомогою функції умовного форматування. У цій статті ми покажемо вам, як порівняти два стовпці в Google Таблицях і знайти дублікати між ними.
Знайдіть повторювані записи між двома стовпцями за допомогою умовного форматування
Умовне форматування — це функція в Google Таблицях, яка дозволяє користувачеві застосовувати конкретні форматування, такі як колір шрифту, значки та панелі даних, до клітинки або діапазону комірок на основі певних умов.
Ви можете використовувати це умовне форматування, щоб виділити повторювані записи між двома стовпцями, заповнюючи клітинки кольором або змінюючи колір тексту. Вам потрібно порівняти кожне значення в стовпці з іншим стовпцем і з’ясувати, чи не повторюється якесь значення. Щоб це спрацювало, потрібно застосувати умовне форматування до кожного стовпця окремо. Для цього виконайте наведені нижче дії.
Відкрийте електронну таблицю, яку потрібно перевірити на наявність дублікатів у Google Таблицях. Спочатку виберіть перший стовпець (A), щоб перевірити стовпець B. Ви можете виділити весь стовпець, клацнувши літеру стовпця над ним.
Потім натисніть меню «Формат» на панелі меню та виберіть «Умовне форматування».
Меню умовного форматування відкривається з правого боку таблиць Google. Ви можете підтвердити, що діапазон комірок вибрано в опції «Застосувати до діапазону». Якщо ви хочете змінити діапазон, натисніть «значок діапазону» та виберіть інший діапазон.
Потім клацніть спадне меню під «Правила форматування» та виберіть параметр «Спеціальна формула».
Тепер вам потрібно ввести спеціальну формулу в поле «Значення або формула».
Якщо ви вибрали цілий стовпець (B:B), введіть таку формулу COUNTIF у полі «Значення або формула» під Правилами форматування:
=countif($B:$B,$A2)>0або,
Якщо ви вибрали діапазон комірок у стовпці (скажімо, сто клітинок, A2:A30), використовуйте цю формулу:
=COUNTIF($B$2:$B$30, $A2)>0Коли ви вводите формулу, не забудьте замінити всі екземпляри літери «B» у формулі буквою виділеного стовпця. Ми додаємо знак «$» перед посиланнями на клітинки, щоб зробити їх абсолютним діапазоном, тому це не зміниться, ми застосовуємо формулу.
У розділі Стиль форматування ви можете вибрати стиль форматування для виділення повторюваних елементів. За замовчуванням він використовуватиме зелений колір заливки.
Ви можете вибрати один із попередньо встановлених стилів форматування, натиснувши «За замовчуванням» під параметрами «Стиль форматування», а потім вибравши один із попередніх налаштувань.
Або ви можете використовувати будь-який із семи інструментів форматування (жирний, курсив, підкреслення, закреслення, колір тексту, колір заливки) у розділі «Стиль форматування», щоб виділити дублікати.
Тут ми вибираємо колір заливки для повторюваних клітинок, клацнувши піктограму «Колір заливки» та вибравши «жовтий» колір.
Вибравши форматування, натисніть «Готово», щоб виділити клітинки.
Функція COUNTIF підраховує, скільки разів кожне значення клітинки в «Стовпці A» з’являється в «Стовпці B». Отже, якщо елемент з’являється хоча б один раз у стовпці B, формула повертає TRUE. Потім цей елемент буде виділено в «Стовпці A» відповідно до вибраного форматування.
Це не виділяє дублікати, а скоріше виділяє елементи, які мають дублікати в стовпці B. Це означає, що кожен виділений жовтим елементом має копії в стовпці B.
Тепер нам потрібно застосувати умовне форматування до стовпця B, використовуючи ту саму формулу. Для цього виберіть другий стовпець (B2:B30), перейдіть до меню «Формат» і виберіть «Умовне форматування».
Крім того, натисніть кнопку «Додати інше правило» під панеллю «Правила умовного формату».
Далі підтвердьте діапазон (B2:B30) у полі «Застосувати до діапазону».
Потім встановіть для параметра «Формат клітинок, якщо...» значення «Спеціальна формула» та введіть формулу нижче у полі формул:
=COUNTIF($A$2:$A$30, $B2)>0Тут ми використовуємо стовпець A діапазон ($A$2:$A$30) у першому аргументі та «$B2» у другому аргументі. Ця формула перевірить значення комірки в «стовпці B» проти кожної клітинки в стовпці A. Якщо знайдено збіг (дублікат), то умовне форматування виділить цей елемент у «стовпці B».
Потім вкажіть форматування в параметрах «Стиль форматування» та натисніть «Готово». Тут ми вибираємо помаранчевий колір для стовпця B.
Це виділить елементи стовпця B, які мають дублікати в стовпці A. Тепер ви знайшли та виділили повторювані елементи між двома стовпцями.
Ви, напевно, помітили, хоча в стовпці А є дублікат «Арселія», але він не виділений. Це тому, що дублікат значення знаходиться лише в одному стовпці (A), а не між стовпцями. Значить, не висвітлюється.
Виділіть дублікати між двома стовпцями в одному рядку
Ви також можете виділити рядки, які мають однакові значення (дублікати) між двома стовпцями за допомогою умовного форматування. Правило умовного форматування може перевірити кожен рядок і виділити рядки, які мають відповідні дані в обох стовпцях. Ось як ви це робите:
Спочатку виберіть обидва стовпці, які потрібно порівняти, потім перейдіть до меню «Формат» і виберіть «Умовне форматування».
На панелі правил умовного формату підтвердьте діапазон у полі «Застосувати до діапазону» та виберіть «Спеціальна формула» зі спадного меню «Комірки формули, якщо..».
Потім введіть наведену нижче формулу в полі «Значення або формула»:
=$A2=$B2Ця формула порівнює два стовпці рядок за рядком і виділяє рядки, які мають ідентичні значення (копії). Як бачите, введена тут формула призначена лише для першого рядка вибраного діапазону, але формула буде автоматично застосована до всіх рядків у вибраному діапазоні за допомогою функції умовного форматування.
Потім вкажіть форматування в параметрах «Стиль форматування» та натисніть «Готово».
Як бачите, будуть виділені лише ті рядки, які мають відповідні дані (дублікати) між двома стовпцями, а всі інші дублікати ігноруватимуться.
Виділіть повторювані клітинки в кількох стовпцях
Працюючи з великими таблицями з багатьма стовпцями, ви можете виділити всі дублікати, які з’являються в кількох стовпцях, а не лише в одному або двох стовпцях. Ви все ще можете використовувати умовне форматування, щоб виділити дублікат у кількох стовпцях.
Спочатку виберіть діапазон усіх стовпців і рядків, які потрібно шукати, а не лише один або два стовпці. Ви можете виділити цілі стовпці, утримуючи клавішу Ctrl, а потім клацнувши літеру у верхній частині кожного стовпця. Крім того, ви також можете клацнути першу та останню клітинки у вашому діапазоні, утримуючи клавішу Shift, щоб вибрати кілька стовпців одночасно.
У прикладі ми вибираємо A2:C30.
Потім натисніть опцію «Формат» у меню та виберіть «Умовне форматування».
У правилах умовного формату встановіть для правил формату значення «Спеціальна формула», а потім введіть таку формулу в поле «Значення або формула»:
=countif($A$2:$C$30,A2)>Ми додаємо знак «$» перед посиланнями на клітинки, щоб зробити їх абсолютними стовпцями, тому це не зміниться, ми застосовуємо формулу. Ви також можете ввести формулу без знаків «$», вона працює в будь-якому випадку.
Потім виберіть форматування, у якому ви хочете виділити повторювані клітинки, використовуючи параметри «Стиль форматування». Тут ми вибираємо «жовтий» колір заливки. Після цього натисніть «Готово».
Це виділить дублікати у всіх вибраних стовпцях, як показано нижче.
Після застосування умовного форматування ви можете будь-коли змінити або видалити правило умовного форматування.
Якщо ви хочете відредагувати поточне правило умовного форматування, виберіть будь-яку клітинку з умовним форматуванням, перейдіть до «Формат» у меню та виберіть «Умовне форматування».
Це відкриє панель «Правила умовного форматування» праворуч зі списком правил форматування, застосованих до поточного вибору. Коли ви наведете курсор миші на правило, воно покаже кнопку видалення, натисніть кнопку видалити, щоб видалити правило. Або, якщо ви хочете відредагувати правило, яке зараз відображається, натисніть саме правило.
Якщо ви хочете додати інше умовне форматування до поточного правила, натисніть кнопку «Додати інше правило».
Порахуйте дублікати між двома стовпцями
Іноді потрібно підрахувати, скільки разів значення в одному стовпці повторюється в іншому стовпці. Це можна легко зробити за допомогою тієї ж функції COUNTIF.
Щоб дізнатися, скільки разів значення в стовпці A існує в стовпці B, введіть таку формулу в клітинку в іншому стовпці:
=COUNTIF($B$2:$B$30,$A2)Введіть цю формулу в клітинку C2. Ця формула підраховує, скільки разів значення в клітинці A2 існує в стовпці (B2:B30), і повертає кількість у клітинці C2.
Коли ви вводите формулу та натискаєте клавішу Enter, з’являється функція автозаповнення, клацніть «галочку», щоб автоматично заповнити цю формулу рештою клітинок (C3:C30).
Якщо функція автозаповнення не з’являється, клацніть синій квадрат у нижньому правому куті клітинки C2 і перетягніть його вниз, щоб скопіювати формулу з клітинки C2 до клітинок C3:C30.
У стовпці "Порівняння 1" (C) тепер буде показано, скільки разів кожне відповідне значення в стовпці A з’являється в стовпці B. Наприклад, значення A2 або "Franklyn" не знайдено в стовпці B, тому Функція COUNTIF повертає «0». А значення «Loreta» (A5) зустрічається двічі в стовпці B, отже, воно повертає «2».
Тепер ми повинні повторити ті самі кроки, щоб знайти повторювані лічби стовпця B. Для цього введіть таку формулу в клітинку D2 у стовпці D (Порівняння 2):
=COUNTIF($A$2:$A$30,$B2)У цій формулі замініть діапазон від "$B$2:$B$30" на "$A$2:$A$30" і "$B2" на "$A2". Функція підраховує, скільки разів значення в клітинці B2 існує в стовпці A (A2:A30) і повертає кількість у клітинці D2.
Потім автоматично заповніть формулу рештою клітинок (D3:D30) у стовпці D. Тепер «Порівняння 2» покаже, скільки разів кожне відповідне значення в стовпці B з’являється в стовпці A. Наприклад , значення B2 або “Stark” зустрічається двічі в стовпці A, тому функція COUNTIF повертає “2”.
Примітка: Якщо ви хочете підрахувати дублікати в усіх стовпцях або кількох стовпцях, вам просто потрібно змінити діапазон у першому аргументі функції COUNTIF на кілька стовпців замість одного стовпця. Наприклад, змініть діапазон з A2:A30 на A2:B30, який зараховуватиме всі дублікати в двох стовпцях, а не лише в одному.
Це воно.