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

By Mirko Peters · 2026-11-12

Пошук дублікатів в Excel за допомогою умовного форматування, позначок COUNTIF, функції UNIQUE, Power Query і перевірок між аркушами, а також як знаходити схожі, але не ідентичні значення перед видаленням.

Automatically translated with AI from the original post. Read the English original

Щоб знайти дублікати в Excel без видалення, виділіть дані та скористайтеся командами Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения. Excel позначить кольором кожне значення, що трапляється більше одного разу. Якщо потрібні числа замість кольорів, додайте допоміжний стовпець із формулою COUNTIF. Ця стаття для користувачів Excel, які успадкували заплутані списки й хочуть побачити проблему, перш ніж її виправляти. Ви дізнаєтеся про метод підсвічування, формули для підрахунку та позначення, спосіб вивести лише повторювані значення, варіант із Power Query, перевірки між аркушами та план роботи зі схожими значеннями.

Головне

• Найшвидший спосіб знайти дублікати в Excel: «Главная», «Условное форматирование», «Правила выделения ячеек», «Повторяющиеся значения». Повторювані значення фарбуються, а нічого не видаляється.
• Допоміжний стовпець із COUNTIF показує, скільки разів трапляється кожне значення, а за допомогою COUNTIFS можна перевірити кілька стовпців одночасно.
• COUNTIF не враховує регістр літер і трактує символи ? та * як шаблони, тому результат підрахунку може відрізнятися від очікуваного.
• Щоб порівняти два аркуші, перевірте кожне значення з COUNTIF за другим списком, а якщо другий список міститься в іншому файлі, відкрийте обидві книги.
• Схожі, але не ідентичні значення, як Jon і John, не з'являться як дублікати. Спочатку приберіть зайві пробіли та вирівняйте регістр, а далі перегляньте решту вручну або за допомогою надбудови Fuzzy Lookup.

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

Підсвітіть дублікати за допомогою умовного форматування

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

1. Виділіть клітинки, стовпець або діапазон, який хочете перевірити. Зазвичай достатньо одного стовпця.
2. На вкладці Главная відкрийте Условное форматирование.
3. Оберіть Правила выделения ячеек, а потім Повторяющиеся значения.
4. Оберіть формат, наприклад світло-червону заливку, і натисніть OK.

Кожна клітинка, значення якої трапляється більше одного разу, тепер позначена кольором. Прогорніть аркуш або відфільтруйте стовпець за кольором, щоб бачити лише виділені клітинки. Щоб скасувати позначення, поверніться до «Условное форматирование» і видаліть правила з діапазону.

Є другий спосіб, який дає більше контролю. Відкрийте Условное форматирование > Управление правилами, створіть Создать правило і оберіть Форматировать только уникальные или повторяющиеся значения. У списку Форматировать все можна вибрати уникальные або повторяющиеся, а також налаштувати формат числа, шрифт, рамку чи заливку. Це зручно, коли потрібно позначити значення, що трапляються лише один раз, і залишити повторювані без змін.

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

Підрахунок і позначення дублікатів за допомогою COUNTIF

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

Позначення всіх повторів

Припустімо, що ваші значення містяться в діапазоні A2:A100. У комірку B2 введіть:

=COUNTIF($A$2:$A$100,A2)

Протягніть формулу вниз. Результат 1 означає, що значення унікальне. Число 2 означає, що значення трапляється двічі, і так далі. Кожна копія повторюваного значення отримує те саме число, включно з першою. Щоб перетворити кількість на TRUE або FALSE, додайте порівняння: =COUNTIF($A$2:$A$100,A2)>1.

Позначення лише наступних копій

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

=COUNTIF(A$2:A2,A2)>1

Діапазон A$2:A2 збільшується під час копіювання формули вниз. Перша поява значення дає FALSE. Кожна наступна поява дає TRUE. Це відповідає роботі команди Excel «Видалити дублікати», адже вона залишає першу появу.

Перевірка кількох стовпців за допомогою COUNTIFS

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

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

Результат більший за 1 означає, що комбінація трапляється більше одного разу. Для більшої кількості стовпців додайте інші пари. Не намагайтеся побудувати таку саму перевірку, об'єднуючи діапазони всередині COUNTIF, як у COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF очікує справжній діапазон як перший аргумент, тож ця формула не робить те, що здається. Чиста відповідь – COUNTIFS.

Що в COUNTIF дивує людей

На сторінці COUNTIF Microsoft описує кілька поведінкових особливостей, які важливі під час пошуку дублікатів:

• Порівняння тексту не враховує регістр літер. «apples» і «APPLES» рахуються як одне й те саме значення. Excel тут вважає їх дублікатами.
• Символи ? і * працюють як шаблони. Якщо ваші дані їх містять, наприклад в кодах товарів, COUNTIF може знайти більше, ніж очікуєте. Щоб шукати такий символ буквально, поставте перед ним тильду.
• COUNTIF повертає неправильні результати для рядків довших за 255 символів.
• Початкові або кінцеві пробіли, змішані прямі та фігурні лапки й недруковані символи можуть спотворити підрахунок. Microsoft пропонує спочатку очистити дані за допомогою функцій TRIM і CLEAN.

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

Фільтрування та перегляд

Коли допоміжний стовпець готовий, відфільтруйте його за TRUE або за значеннями більше 1 і перегляньте рядки. Можна відсортувати за допоміжним стовпцем, щоб зібрати всі повторювані рядки разом. Перед видаленням чогось перегляньте зразок. Часто «дублікат» виявляється двома справжніми записами, які мають однакове ім'я.

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

Іноді потрібен короткий список того, що повторюється, а не позначка біля кожного рядка.

В Excel для Microsoft 365, 2024 і 2021 це може зробити функція UNIQUE. Згідно зі сторінкою Microsoft про UNIQUE, =UNIQUE(array,[by_col],[exactly_once]) повертає унікальні значення з діапазону, а якщо встановити exactly_once у TRUE, повертаються лише значення, які трапляються один раз. Тож =UNIQUE(A2:A100) дає кожне значення один раз, а =UNIQUE(A2:A100,,TRUE) повертає значення, які взагалі не дублюються. Щоб отримати лише повторювані значення, поєднайте UNIQUE з FILTER і COUNTIF:

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))

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

У старіших версіях використовуйте допоміжний стовпець. Введіть =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") і протягніть формулу вниз. Стовпець покаже кожне повторюване значення, а решта комірок буде порожньою. Відфільтруйте порожні комірки. Ви побачите кожне повторюване значення стільки разів, скільки воно трапляється, що для перегляду цілком прийнятно.

Розширений фільтр дозволяє скопіювати лише унікальні записи в нове місце. Перейдіть до Дані > Додатково, виберіть Копіювати в інше місце, введіть комірку в полі Копіювати в і позначте Лише унікальні записи. Порівняння цього списку з оригіналом покаже, що повторювалося.

Використовуйте Power Query, щоб залишити лише дублікати

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

У статті Microsoft про збереження або видалення повторюваних рядків наведено кроки для видалення. Початок однаковий для обох варіантів:

1. Виберіть комірку в даних і виконайте Запит > Редагувати або завантажте їх командою Дані > Із таблиці/діапазону.
2. Клацніть заголовок стовпця, щоб його вибрати. Утримуйте Shift або Ctrl, щоб вибрати кілька стовпців.
3. Застосуйте команду. Кроки Microsoft для видалення використовують Основна > Видалити рядки > Видалити дублікати. Шукайте відповідну команду Зберегти дублікати разом з іншими командами для рядків на вкладці «Основна» і перевірте попередній перегляд.

Microsoft також зазначає, що Power Query чутлива до регістру, тож «Apple» і «apple» там є різними значеннями, на відміну від COUNTIF. Це реальна різниця між двома методами. Якщо ваша мета — повторювана щотижнева перевірка, оберіть одну поведінку і дотримуйтеся її, наприклад, спочатку перетворивши стовпець до верхнього регістру.

Пошук дублікатів між двома аркушами або списками

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

1. Розмістіть два списки на окремих аркушах, наприклад Аркуш1 і Аркуш2, зі значеннями у стовпці A.
2. На Аркуші1 у комірку B2 введіть =COUNTIF(Аркуш2!A:A;A2)>0.
3. Протягніть формулу вниз. ИСТИНА означає, що значення є також на Аркуші2.
4. Відфільтруйте ИСТИНА, щоб побачити збіги, або ХИБНІСТЬ, щоб побачити нові значення.

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

Якщо другий список міститься в іншій книзі, Microsoft попереджає, що формула, яка посилається на закриту книгу, може повернути помилку #ЗНАЧ!. Під час роботи тримайте обидві книги відкритими або перенесіть другий список у першу книгу за допомогою Power Query. Після перевірки пам’ятайте, що результат залежить від даних на цей момент. Якщо будь-який зі списків зміниться, позначки також зміняться.

Неточні збіги: Jon Smith і John Smith

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

Крок перший: приберіть хибні відмінності. Використовуйте TRIM, щоб прибрати зайві пробіли, і CLEAN, щоб видалити непечатні символи, і будуйте перевірку на очищених значеннях. У допоміжному стовпці =TRIM(A2) показує очищений текст. Рахуйте дублікати саме за цим стовпцем, а не за вихідним. Оскільки COUNTIF не враховує регістр, змінювати його тут не потрібно. У Power Query, де регістр важливий, перетворіть стовпець до верхнього або нижнього регістру.

Крок другий: перегляньте, що залишилося. Відсортуйте очищений список за абеткою і пройдіться по сусідніх рядках. Близькі збіги, наприклад «Jon Smith» і «John Smith», опиняться поруч, і їх помітить людина. Для великих списків Microsoft Research опублікувала надбудову Fuzzy Lookup для Excel. Вона може знаходити неточні дублікати рядків в одній таблиці або співставляти схожі рядки між двома таблицями, а кожному збігу присвоює показник схожості. Перш ніж будувати план навколо неї, перевірте вимоги: на сторінці завантаження вказано Windows, Excel 2007, 2010, 2013 і 2016, а також випуск 2020 року. Вона може не встановлюватися на поточній конфігурації Microsoft 365 і недоступна для Mac.

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

Типові помилки

• Перевірка лише одного стовпчика, коли дублікат визначається двома. Повторюване прізвище саме по собі не є дублікатом запису.
• Довіра результатам COUNTIF на даних із зайвими пробілами або дивними символами. Спочатку очистіть дані за допомогою TRIM і CLEAN.
• Забування, що COUNTIF не враховує регістр, тоді як Power Query враховує, і через це отримання різних результатів з двох інструментів.
• Використання COUNTIF($A$2:$A$100,A2)>1 і видалення всіх рядків зі значенням TRUE. Так позначається і перша копія, тож ви видалите всі копії. Використовуйте розширюваний діапазон, щоб позначати лише наступні.
• Пошук у цілих стовпчиках рядків довших за 255 символів, де COUNTIF дає хибні відповіді.
• Видалення одразу після виділення. Спочатку скопіюйте аркуш, адже Microsoft зазначає, що видалені дублікати безповоротно втрачаються.

Пов'язані теми для вивчення

• Видаліть знайдені дублікати. Команда «Видалити дублікати» на вкладці «Дані» залишає перший збіг і видаляє решту. Спочатку відсортуйте дані, щоб контролювати, який рядок залишиться.
• Залишайте найкращий запис, а не перший. Відсортуйте за датою або значенням, щоб потрібний рядок опинився першим, а потім позначте наступні копії розширюваним COUNTIF.
• Порівнюйте списки між файлами. Power Query може об'єднати кілька книг в одну таблицю, щоб перевірити їх разом.
• Запобігайте появі нових дублікатів. Перевірка даних з власною формулою може блокувати значення, яке вже існує. Спробуйте це на тестовому аркуші.
• Ведіть журнал перевірки. Залиште допоміжний стовпчик у копії аркуша, щоб пізніше можна було показати, що було позначено і чому.

Наступний крок

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