Коли ви працюєте з великою кількістю даних, легко втратити дані й побачити, як вони з’являються кілька разів у таблиці. Деякі дублікати розміщені навмисно, а інші є помилками. У будь-якому випадку вам може знадобитися, щоб ці дублікати автоматично виділялися.
Знайти повторювані клітинки в невеликій електронній таблиці легко, але при роботі з великими складними наборами даних може бути досить важко зробити це вручну. На щастя, в Excel є вбудовані інструменти, які дозволяють виділяти повторювані значення. У цьому підручнику ми збираємося показати вам, як виділити повторювані дані за допомогою функції умовного форматування в Excel.
Виділіть дублікати за допомогою умовного форматування в Excel. Як правило, ви можете знайти дублікати в Excel, тому що найчастіше дублікати виникають помилково, і їх слід видалити, або дублікати важливі для аналізу та мають бути виділені в Excel.
Існує два методи пошуку повторюваних значень за допомогою умовного форматування в Excel. Вони є:
- Виділіть дублікати за допомогою правила повторюваного значення
- Виділіть дублікати за допомогою спеціальної формули Excel (COUNTIF і COUNTIFS)
Виділіть дублікати за допомогою правила повторюваних значень
Припустимо, у нас є такий набір даних:
Спочатку виберіть діапазон комірок, який містить повторювані значення. Потім перейдіть на вкладку «Головна», натисніть «Умовне форматування» в розділі «Стилі» на стрічці. У спадному меню наведіть курсор на першу опцію «Виділити правила клітинки», і вона знову відобразить список правил у спливаючому вікні. Виберіть тут опцію «Дублікати значень».
Після того, як ви клацнете на Дублікати значення, з’явиться діалогове вікно Повторювані значення. Тут ви можете вибрати тип форматування для повторюваних значень. Ви можете вибрати один із кольорів, щоб заповнити лише клітинки, лише для шрифту, як межі або користувацький формат, якщо хочете. Потім натисніть «ОК», щоб закрити діалогове вікно.
Тут ми вибираємо «Зелена заливка з темно-зеленим текстом» для нашого прикладу.
Після того, як ви виберете тип форматування, він виділить усі повторювані значення у вибраному діапазоні, як показано нижче.
Виділіть Дублікати Успівайте формулу COUNTIF
Іншим методом виділення повторюваних значень є використання умовного форматування за допомогою простої формули COUNTIF в одному або в кількох стовпцях.
Виберіть діапазон даних, де потрібно виділити дублікати. Потім перейдіть на вкладку «Домашня сторінка» та натисніть опцію «Умовне форматування». У спадному меню натисніть опцію «Нове правило».
Це відкриє діалогове вікно Нове правило форматування.
У діалоговому вікні «Нове правило форматування» виберіть опцію «Використовувати формулу для визначення клітинок для форматування» у списку «Виберіть тип правила», а потім введіть таку формулу COUNTIF для підрахунку дублікатів.
=COUNTIF($A$1:$C$11,A1)>1
Потім натисніть кнопку «Формат», щоб перейти до діалогового вікна «Формат комірок». У діалоговому вікні «Формат клітинок» ви можете вибрати колір заливки з палітри кольорів для виділення клітинок, а потім натиснути «ОК». Тут ми вибираємо синій колір заливки для форматування дублікатів.
Потім знову натисніть «ОК», щоб закрити діалогове вікно. Формула виділить усі значення комірок, які з’являються більше одного разу.
Завжди вводьте формулу для верхньої лівої клітинки у вибраному діапазоні (A1:C11). Excel автоматично копіює формулу в інші клітинки.
Ви також можете визначити правила як завгодно. Наприклад, якщо ви хочете знайти значення, які з’являються в таблиці лише двічі, замість цього введіть цю формулу (у діалоговому вікні Нове правило форматування):
=COUNTIF($A$1:$C$11,A1)=2Результат:
Іноді вам може знадобитися побачити лише дублікати та відфільтрувати унікальні значення. Для цього виберіть діапазон, перейдіть на вкладку «Головна», натисніть опцію «Сортувати та фільтрувати» у верхньому правому куті Excel і виберіть параметр «Фільтр».
У першій клітинці кожного стовпця відобразиться спадне меню, де можна визначити критерій фільтрації. Натисніть спадне меню в першій клітинці стовпця та виберіть «Фільтрувати за кольором». Потім виберіть синій колір.
Тепер ви побачите лише виділені клітинки, і ви можете робити з ними все, що хочете.
Знайдіть і виділіть повторювані рядки в Excel за допомогою формули COUNTIFS
Якщо ви хочете знайти та виділити повторювані рядки в Excel, використовуйте COUNTIFS замість COUNTIF.
Виберіть діапазон, перейдіть на вкладку «Головна» та натисніть «Умовне форматування» у групі «Стилі». У спадному меню натисніть опцію «Нове правило».
У діалоговому вікні «Нове правило форматування» виберіть параметр «Використовуйте формулу для визначення клітинок для форматування» у списку «Вибір типу правила», а потім введіть наведену нижче формулу COUNTIFS:
=COUNTIFS($A$1:$A$20,$A1,$B$1:$B$20,$B1,$C$1:$C$20,$C1)>1У наведеній вище формулі діапазон A1:A20 відноситься до стовпця A, B1:B20 відноситься до стовпця B, а C1:C20 відноситься до стовпця C. Формула підраховує кількість рядків на основі кількох критеріїв (A1, B2 і C1). .
Потім натисніть кнопку «Формат», щоб вибрати стиль форматування, і натисніть «ОК».
Тепер Excel виділяє лише повторювані рядки, як показано нижче.
Це воно.