Робота з великим набором даних у Excel зазвичай вимагає наявності повторюваних значень, які можна знайти часто. Ви можете легко знаходити та виділяти дублікати в Excel і Google Таблицях. дані можуть стати непослідовними або неточними в результаті неправильних розрахунків або формул. У програмі Excel часто потрібно звернути увагу на значення, які двічі з’являються в різних стовпцях.
Функція «Правила виділення комірок», яку можна знайти в параметрах умовного форматування Excel, на щастя, спрощує це.
Виділіть дублікати в Excel
Пошук і виділення дублікатів є простим процесом завдяки функції в Excel під назвою Умовне форматування. Зосередьтеся на дублікатах, які з’являються в кількох стовпцях на додаток до першого. Щоб виділити дублікати з наборів даних у Excel, ви можете використати формулу умовного форматування COUNTIF(range, top_cell) > 1.
Тут, щоб використовувати формулу умовного форматування, ви можете виконати такі дії:
- Виберіть діапазон даних.
- Натисніть «Нове правило» в розділі «Умовне форматування».
- Якщо з’явиться діалогове вікно «Нове правило форматування», за допомогою формули можна вказати, які комірки потрібно відформатувати. Не забудьте ввести формулу.
- Потім відформатуйте клітинки-дублікати, натиснувши кнопку «Формат» у діалоговому вікні та вибравши «ОК».
- Вибравши стиль форматування, у «Новому правилі форматування» натисніть «ОК».
Як виділити дублікати в Excel
- Виберіть дані, які потрібно перевірити на наявність дублікатів. Це може стосуватися стовпця, рядка або набору клітинок.
- Перейдіть до групи «Стилі» на вкладці «Головна».
- Щоб продовжити, просто натисніть «Умовне форматування».
- Клацніть Правила виділення клітинок,
- Виберіть Повторювані значення.
- Типовий макет вікна діалогового вікна «Повторювані значення» має світло-червону заливку та темно-червоний текст.
- Якщо натиснути «ОК», буде застосовано стандартний формат.
Розкривний список містить кілька інших попередньо визначених форматів, окрім червоної заливки та форматування тексту. Останній пункт у спадному меню «Настроюваний формат» дає змогу вибрати колір заливки та/або шрифту, який ви хочете використати, щоб затінити дублікати іншим кольором.
Виділіть дублікати за допомогою умовного форматування
Excel має справді корисну функцію під назвою Умовне форматування, яка спрощує пошук і виділення дублікатів. Окрім першого екземпляра, зверніть увагу на дублікати в кількох стовпцях.
Excel може допомогти вам знайти дублікати, якщо набір даних містить такі. Кілька записів можуть автоматично ідентифікуватися Excel. Ви також можете застосувати спеціально створене правило умовного форматування за такою формулою:
COUNTIF(діапазон, верхня_комірка) > 1
Знайти дублікати в діапазоні A3:D9 можна за такою формулою:
=COUNTIF($A$3:$D$9, A3)>1
Як показати дублікати без першого входження
Виділіть комірки, які потрібно пофарбувати, а потім створіть наступне правило на основі формули, щоб виділити другий дублікат, а також будь-які інші наступні:
- Група «Стилі» на вкладці «Головна».
- Виберіть «Нове правило» в меню «Умовне форматування». Обчисліть, які клітинки потрібно відформатувати за допомогою формули.
- Помістіть таку формулу в поле з позначкою Форматувати значення, де ця формула вірна:
=COUNTIF($A$2:$A2,$A2)>1
У цьому випадку найвищою коміркою у вибраному діапазоні є A2.
- Якщо ви хочете змінити колір шрифту або заливку, натисніть кнопку «Формат».
- Ще раз натисніть OK, щоб зберегти та застосувати правило.
Результатом є те, що повторювані комірки, окрім перших екземплярів, виділяються кольором за вашим вибором.
Як відобразити третій, четвертий і кожен наступний повторюваний запис
Використовуючи формулу як основу, створіть правило умовного форматування. Проте, щоб переглянути дублікати, починаючи з N-го входження, замініть >1 на відповідне число в кінці формули. Наприклад:
Створіть правило умовного форматування на основі наведеної нижче формули, щоб виділити третій дублікат і всі наступні:
=COUNTIF($A$2:$A2,$A2)>=3
Використовуйте цю формулу, щоб виділити четвертий повторюваний запис і всі наступні повторювані записи:
=COUNTIF($A$2:$A2,$A2)>=4
Використовуйте дорівнює оператору (=), щоб виділити лише певні випадки. Наприклад, ви можете використати таку формулу, щоб виділити лише п’ятий випадок:
=COUNTIF($A$2:$A2,$A2)=5
Як виділити дублікати в діапазоні (кілька стовпців)
Якщо ви хочете знайти дублікати в кількох стовпцях, скористайтеся одним із наведених нижче методів, а не порівнюйте стовпці між собою.
Виділіть усі дублікати — включно з першими — у всіх стовпцях.
Якщо набір даних містить елемент більше одного разу, але лише один раз у першому випадку, слід використовувати вбудоване правило дублікатів Excel.
Або скористайтеся цією формулою, щоб створити правило умовного форматування:
COUNTIF(діапазон, верхня_комірка)>1
Наприклад, щоб виділити дублікати в діапазоні A4:D8, формула виглядає так:
=COUNTIF($A$4:$D$8, A4)>1
Виділення всіх рядків, де один стовпець має повторювані значення
Якщо в одному чи кількох стовпцях вашої таблиці є повторювані записи, ви можете виділити весь рядок.
Стандартне правило дублікатів у перевершувати застосовується лише на рівні клітини, як ви вже знаєте. Однак затінення рядків не є проблемою для правила, яке використовує настроювану формулу. Головне – вибрати всі рядки, а потім використати одну з наведених нижче формул, щоб створити правило:
Щоб показати лише повторювані рядки, а не перші входження:
=COUNTIF($A$2:$A2, $A2)>1…….
Щоб привернути увагу до повторюваних рядків, які містять перші випадки:
=COUNTIF($A$2:$A$15, $A2)>1…..
Першою коміркою в стовпці, яка перевіряється на повторення, є A2, а останньою використаною коміркою є A15. Ви можете побачити, наскільки ефективним є використання як абсолютних, так і відносних посилань на клітинки.
Як виділити повторювані рядки в Excel
Повторювані значення в певному стовпці можна використовувати для фарбування цілих рядків. Однак що станеться, якщо ви захочете переглянути рядки з однаковими значеннями в кількох стовпцях? Крім того, як визначити рядки, які мають однакові значення в кожному стовпці, або абсолютні повторювані рядки?
Для цього використовуйте функцію COUNTIFS, яка дозволяє порівнювати комірки за різними критеріями. Розгляньте можливість використання однієї з наведених нижче формул, щоб привернути увагу до повторюваних рядків із однаковими значеннями в стовпцях A і B:
Виділіть повторювані рядки без першого входження:
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1………
Виділіть повторювані рядки з першим входженням:
=COUNTIFS($A$2:$A$15, $A2, $B$2:$B$15, $B2)>1…….
Виділення послідовних повторюваних клітинок в Excel
Іноді може бути достатньо відобразити лише послідовні повторювані клітинки, тобто клітинки, які розташовані поруч, замість того, щоб виділяти всі повторювані клітинки в стовпці. Для цього виберіть клітинки, що містять дані (за винятком заголовка стовпця), і створіть правило умовного форматування за допомогою однієї з наведених нижче формул:
Щоб звернути увагу на наступні дублікати без першого входження:
= $ A1 = $ A2
Щоб звернути увагу на наступні дублікати з першими входженнями:
=АБО($A1=$A2, $A2=$A3)…….
Внесіть такі зміни до формул, якщо ваш аркуш Excel може містити порожні рядки, і ви не хочете, щоб наступні порожні клітинки були виділені:
Щоб виділити наступні повторювані клітинки без першого входження та ігнорувати порожні клітинки, ви повинні:
=І($A2<>””, $A1=$A2)
Виділіть наступні повторювані комірки з першим входженням, ігноруючи порожні комірки:
=AND($A2<>””, OR($A1=$A2, $A2=$A3))
Функція видалення дублікатів в одному стовпці Excel
Ось кроки, яких слід виконати, якщо у вас є дані в одному стовпці та ви хочете позбутися всіх дублікатів:
- Виберіть дані.
- Перейдіть до Дані> Інструменти даних> Перемістити дублікати.
У діалоговому вікні для видалення дублікатів:
- Поставте прапорець біля пункту «Мої дані мають заголовки», якщо ваші дані містять заголовки.
- У випадку, коли є лише один стовпець, переконайтеся, що він вибраний.
- Виберіть OK.
Зробивши це, ви усунете всі повторювані значення стовпця та залишите лише унікальні значення.
Виділіть дублікати в двох стовпцях Excel
Одним з найпростіших методів порівняння стовпців в Excel є умовне форматування.
- В електронній таблиці виділіть усі необхідні клітинки. Для цього можна виділити діапазон клітинок.
- На вкладці «Домашня сторінка» натисніть значок «Умовне форматування».
- Далі виберіть «Правила виділення клітинок».
- потім у спадному меню виберіть «Повторюване значення».
- Коли відкриється нове вікно, ви можете вирішити, як відформатувати повторювані значення.
- Ви можете використовувати стандартне налаштування світло-червоної заливки з темно-червоним текстом і
- Далі натисніть OK.
Після натискання кнопки OK кожне повторюване значення, яке відображається в обох списках команд, буде виділено. Ці імена позначені темно-червоним текстом і світло-червоною заливкою, щоб показати, що вони з’являються в обох стовпцях.
Як виділити дублікати в Google Таблицях
Google Дублікати аркушів необхідно виділяти за допомогою формули умовного форматування =COUNTIF (A:A, A1)>1. Таблиці знають, де шукати дублікати, завдяки формулі COUNTIF [=COUNTIF (A:A, A1)>1]. Інформація в дужках позначає стовпець, який ви бажаєте відстежувати, а також конкретну клітинку, з якої ви хочете почати. Текст поза дужками вказує, що ви хочете, щоб Таблиці підраховували дублікати або будь-що, що з’являється більше одного разу (>1).
Він починає виділяти повторювані дані, як ви бачите.
- Виберіть стовпець, у якому потрібно шукати дублікати.
- У верхньому меню виберіть «Формат».
- Виберіть «Умовне форматування».
У результаті в правій частині екрана заповниться поле. У вигляді підказки відобразиться «Формат клітинок». Натисніть на це та прокрутіть униз.
- Виберіть «Спеціальна формула» зі спадного меню «Формат клітинок, якщо».
Дублікати в таблицях Google шляхом виділення їх у кількох стовпцях
- Виберіть назву набору даних (без заголовків)
- У спадному меню виберіть «Форматувати».
- Увімкніть «Умовне форматування».
- У меню виберіть «Додати інше правило».
Переконайтеся, що діапазон точний (щоб ми могли виділити дублікати). Якщо ні, ви можете налаштувати його в розділі «Застосувати до діапазону».
- Виберіть «Спеціальна формула» зі спадного меню після вибору «Формат клітинок, якщо»
- У меню «Стиль форматування» виберіть стиль форматування, який потрібно використовувати для виділення повторюваних комірок.
- Ви можете вказати різні кольори та стилі, як-от напівжирний або курсив, на додаток до зеленого кольору за замовчуванням, який використовуватиметься.
- Виберіть «Готово»
Як виділити дублікати в Excel одним кольором?
- Виберіть інформацію, яку ви хочете порівняти на наявність дублікатів. Діапазон комірок тут може бути стовпцем, рядком або тим і іншим.
- У розділі «Стилі» на вкладці «Домашня сторінка»
- виберіть «Умовне форматування» > «Правила виділення клітинок» > Повторювані значення
- Коли вперше відкриється діалогове вікно «Повторювані значення», стандартним форматом є світло-червона заливка та темно-червоний текст.
- Просто натисніть OK, щоб використовувати стандартне форматування.
- Останній пункт у спадному меню «Настроюваний формат» дає змогу вибрати колір заливки та/або шрифту, який ви хочете використати, щоб затінити дублікати іншим кольором.
Яка формула для визначення дублікатів у Excel?
Функція Excel COUNTIF() — це найпростіший спосіб підрахувати дублікати. Ця функція відстежує, скільки клітинок потрапляє в необхідний діапазон у заданому діапазоні. Формула така
(=COUNTIF(Діапазон пошуку,Посилання на клітинку)>1)
Як виділити та підрахувати дублікати в Excel?
Найпростіший спосіб підрахунку дублікатів у стовпці – це використання однієї з формул Excel, які ми використовували для пошуку дублікатів (з першими входженнями або без них). Використовуючи наведену нижче формулу COUNTIF, ви можете визначити кількість повторюваних значень:
=COUNTIF(діапазон, “дублікат”)
де «дублікат» позначає будь-які дублікати формул, які ви ідентифікували.
Як приклад, повторювана формула може виглядати так:
=COUNTIF(D4:D9, «дублікат»)
Як я можу визначити дублікати між двома аркушами Excel?
Коли дані розподілені по численним стовпцям і рядкам, може бути важко порівняти ці аркуші на наявність дублікатів записів. Наступні методи є найпопулярнішими для пошуку дублікатів між двома аркушами Excel:
- Функції VLOOKUP, COUNTIF або EXACT
- Умовне форматування
- PowerQuery
Чому дублікати не виділяються в Excel?
Перевірте, чи містить окрема клітинка зайві, початкові чи кінцеві пробіли. Хоча люди часто ігнорують це, Excel сприймає пробіл як окремий символ. Ймовірно, це найпоширеніша причина, чому Excel не виявляє дублікати
Вам потрібно виправити знаки $.
Надзвичайно поширена проблема. Excel може не просканувати всю область, якщо ви не включите знаки $ у формулу. Коли ви пересуваєте формулу вниз, здається, що Excel сканує весь список, але насправді він сканує лише кілька останніх клітинок.
Як виділити відповідні значення в Excel?
- Виберіть повний набір даних.
- Перейдіть на вкладку «Головна».
- У групі «Стилі» виберіть «Умовне форматування».
- Перетягніть вказівник миші на опцію «Виділити правила комірки»
- Натисніть Повторювані значення.
- У діалоговому вікні «Дублікати значень» обов’язково виберіть «Дублікат» у
- Дайте інструкції щодо форматування.
- Виберіть ОК
Висновок
Динамічний характер умовного форматування є однією з багатьох його переваг. Тому форматування буде автоматично оновлено, якщо ви зміните дані в будь-якій комірці. Під час роботи з великим обсягом даних можуть бути випадки, коли ви захочете ще раз перевірити, чи немає дублікатів, або ви можете виділити будь-які дублікати, щоб привернути до них увагу. У такому випадку Google Sheets має просту формулу для застосування. Використовуйте формулу COUNTIF, щоб знайти дублікати. Він подається як: (=COUNTIF(Діапазон пошуку,Посилання на клітинку)>1)
посилання
