Як видалити дублікати в Excel: команда, формули, Power Query

By Mirko Peters · 2026-11-12

Як видалити дублікати в Excel: скористайтеся командою «Дані» > «Видалити дублікати», або збережіть вихідні дані за допомогою UNIQUE, формул і Power Query. Кроки, обмеження та типові помилки.

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

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

Головне

• Виділіть дані, відкрийте «Дані», далі «Видалити дублікати», позначте стовпці, що визначають дублікат, і натисніть OK. Excel видаляє весь рядок і залишає перший збіг.
• Видалення безповоротне, тож спочатку скопіюйте дані на інший аркуш. Ctrl+Z працює лише доти, доки ви не виконаєте іншу дію.
• Excel порівнює те, що відображається в клітинці, тож 3/8/2006 і 8 бер 2006 вважаються різними значеннями. Спочатку приведіть формати до єдиного вигляду.
• Якщо хочете переглянути дублікати перед видаленням, виділіть їх умовним форматуванням або позначте допоміжним стовпцем з функцією COUNTIF.
• UNIQUE і Power Query дають чистий список без змін у вихідних даних, а Power Query повторює очищення при кожному оновленні.

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

Видалення дублікатів за допомогою команди на вкладці «Дані»

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

1. Створіть резервну копію даних. Скопіюйте діапазон на інший аркуш або в іншу книгу. Microsoft прямо попереджає: дублікати видаляються назавжди.
2. Виділіть діапазон. Клацніть будь-яку клітинку у своїх даних або в таблиці Excel чи виділіть діапазон вручну. Змінюється лише виділений діапазон або таблиця. Клітинки поза ним залишаються на місці.
3. Відкрийте команду. Перейдіть на вкладку Дані і виберіть Видалити дублікати у групі «Знаряддя для роботи з даними».
4. Виберіть стовпці. У діалоговому вікні позначте стовпці, які Excel має порівнювати. Можна скористатися Виділити все або Зняти все. Якщо перший рядок містить назви стовпців, залиште позначку Мої дані мають заголовки.
5. Натисніть OK. Excel покаже повідомлення з кількістю видалених повторюваних значень і кількістю унікальних значень, що залишилися.

Зверніть увагу на крок 4, бо саме тут люди найчастіше помиляються. Рядок є дублікатом лише тоді, коли збігаються всі відмічені стовпці. Візьмімо список із полями ID, Name і Price. Якщо ви відмітили ID і Name, але не Price, два рядки з однаковими ID та іменем вважаються дублікатами, навіть коли їхні ціни різні, і Excel видаляє весь другий рядок разом із ціною. Якщо ви хочете зберегти кожен рядок, у якому щось відрізняється, відмітьте всі стовпці. Якщо запис визначається лише ID, відмітьте тільки ID, і завжди пам’ятайте, що саме ви втрачаєте.

Одразу після команди натисніть Ctrl+Z, якщо результат виглядає неправильно. Не розраховуйте на це пізніше.

Який рядок залишиться: спочатку сортування

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

Що вважається однаковим

Excel порівнює те, що відображається в клітинці, а не те, що зберігається. За прикладом Microsoft, 3/8/2006 і 8 бер 2006 вважаються різними значеннями, хоча дата та сама. Так само стоїть справа з числами у різних форматах або з текстом, що має пробіл наприкінці. У статті про пошук і видалення дублікатів також зазначено, що кількість, яку Excel показує після цього, може враховувати порожні клітинки або пробіли.

Перш ніж щось видаляти, приведіть клітинки до однакового вигляду. Застосуйте один формат дати, приберіть зайві пробіли (це робить функція TRIM) і перевірте, що числа не збережено як текст. На сторінках Microsoft не сказано, чи ігнорує команда регістр літер, тому перевірте це на невеликому діапазоні, якщо регістр для вас важливий.

Коли команда не працює

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

Перегляньте дублікати перед видаленням

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

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

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

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

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

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

1. Вставте стовпець поруч із даними та додайте заголовок, наприклад «Дублікат?».
2. Якщо перевіряєте стовпець A, введіть у клітинку B2: =COUNTIF(A$2:A2,A2)>1
3. Протягніть формулу вниз. Перша поява значення дає FALSE. Кожна наступна поява дає TRUE.
4. Відфільтруйте допоміжний стовпець за TRUE. Перегляньте ці рядки. Видаліть їх, якщо це справді дублікати.

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

Щоб перевірити кілька стовпців одночасно, створіть ключовий стовпець, який їх об’єднує: =A2&"|"&B2&"|"&C2. Застосуйте COUNTIF до цього ключа. Між полями ставте роздільник, наприклад вертикальну риску. Без нього «12» plus «34» і «1» plus «234» стануть однаковим рядком «1234» і будуть сприйняті як один і той самий запис.

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

Отримайте список без дублікатів і збережіть джерело

Іноді нічого видаляти не потрібно. Вам потрібен лише чистий список для випадаючого меню, зведення або імпорту. Тут підходять три варіанти.

Функція UNIQUE

В Excel для Microsoft 365, Excel 2024 та Excel 2021 =UNIQUE(A2:A100) повертає всі унікальні значення з діапазону, а результат розгортається в нижні клітинки. Microsoft зазначає, що функція доступна також в Excel для Інтернету та мобільних застосунках. Синтаксис: =UNIQUE(array,[by_col],[exactly_once]):

• array — діапазон для перевірки. Якщо він містить кілька стовпців, UNIQUE порівнює цілі рядки.
• by_col — TRUE, якщо потрібно порівнювати стовпці, а не рядки. Якщо аргумент пропущено, виконується звичайне порівняння рядків.
• exactly_once — TRUE, якщо потрібно отримати лише рядки, які зустрічаються в даних один раз. Якщо аргумент пропущено, ви отримаєте всі унікальні рядки.

Результат оновлюється разом із джерелом. Якщо джерелом є таблиця Excel і ви використовуєте структуровані посилання, діапазон розгортання змінює розмір при додаванні або видаленні рядків. Для відсортованого списку загорніть функцію: =SORT(UNIQUE(A2:A100)).

Пара другого аргументу важливіша, ніж здається багатьом. =UNIQUE(A2:A100,,TRUE) повертає значення, які зустрічаються рівно один раз, і це зовсім інше питання, ніж «прибрати зайві копії». Спершу визначте, що саме вам потрібно.

Одне обмеження: старіші версії Excel (2019 і раніші) не мають функції UNIQUE. На сторінці Microsoft перелічено лише версії, згадані вище.

Розширений фільтр

Цей спосіб працює в усіх настільних версіях і копіює результат в інше місце.

1. Виберіть свої дані.
2. Перейдіть до Дані > Додатково у групі «Сортування та фільтр».
3. Виберіть Копіювати в інше місце.
4. Введіть клітинку в полі Копіювати в і позначте Лише унікальні записи.
5. Натисніть OK.

Вихідні дані залишаються на місці, а унікальні рядки з'являються в новому місці.

Старіший шаблон формули

Якщо ви не можете використати UNIQUE, введіть у новий стовпець =IF(COUNTIF($A$2:A2,A2)=1,A2,"") і протягніть формулу вниз. Кожне значення з'явиться один раз, а повтори стануть порожніми рядками. Скопіюйте стовпець і вставте його як значення, а потім відфільтруйте порожні клітинки. Це громіздко, зате працює скрізь.

Обробка часткових збігів і різного регістру

Реальні дані рідко бувають ідеальною копією. Три ситуації трапляються знову і знову.

Точні дублікати. Збігаються всі позначені стовпці. Команда з цим справляється добре.

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

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

Автоматизація: Power Query та VBA

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

Power Query

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

1. Завантажте дані в Редактор Power Query. Якщо вони вже є на аркуші, виберіть клітинку в даних і натисніть Запит > Редагувати, або скористайтеся Дані > Отримати дані, щоб завантажити нове джерело.
2. Клацніть заголовок стовпця, щоб вибрати його. Утримуйте Shift або Ctrl, щоб вибрати більше стовпців. Стовпці не обов'язково мають бути поруч.
3. Перейдіть до Главная > Удалить строки > Удалить дубликаты.
4. Закрийте та завантажте результат.

Сторінка Microsoft про збереження або видалення повторюваних рядків у Power Query описує ці кроки. Її рекомендації щодо дублікатів варто знати: саме вибрані стовпці визначають, що вважати дублікатом, і Power Query не гарантує, що залишиться перший рядок із групи. Якщо важливо, який рядок залишиться, не покладайтеся лише на сортування. Microsoft посилається на розділ «Preserve sort» у статті про поширені проблеми з Power Query. Пов'язана опція, Keep Duplicates, працює навпаки: вона залишає лише ті рядки, значення яких повторюються, і це швидкий спосіб перевірити список.

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

Макрос VBA

Якщо ви щодня повторюєте одне й те саме очищення робочих книг, можна скористатися макросом. Об'єкт Range має метод RemoveDuplicates, який описано в довідці Microsoft з VBA. Він приймає стовпці для порівняння і визначає, чи містить перший рядок заголовки. Приклад від самої Microsoft:

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

Цей рядок порівнює перші два стовпці діапазону A1:C100 і вважає перший рядок заголовком. Щоб скористатися ним, натисніть Alt+F11, вставте модуль, помістіть рядок усередину Sub і збережіть файл як книгу з підтримкою макросів (.xlsm). Спочатку запустіть його на копії. Макрос видаляє так само безповоротно, як і команда, і Ctrl+Z не поверне рядки. Ваш ІТ-відділ може також блокувати макроси, тож перевірте це, перш ніж будувати щось, чим не зможуть скористатися колеги. Якщо хочете дізнатися більше про мову, на сторінці VBA зібрано пов'язані матеріали.

Excel для веб і мобільних пристроїв

Статті Microsoft про видалення дублікатів описують лише настільні версії Excel (Microsoft 365, 2024, 2021, 2019 і 2016) і нічого не кажуть про веб-версію чи мобільні застосунки. Microsoft не описує команду «Видалити дублікати» для цих версій, тож не будуйте на ній процес. Для веб-версії та мобільних застосунків Microsoft документує функцію UNIQUE, тому використовуйте її, щоб отримати чистий список на цих пристроях, а остаточне видалення виконуйте на настільній версії.

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

• Запуск команди без резервної копії. Щойно ви закриєте файл, рядки зникнуть назавжди.
• Вибір不正確 стовпців. Якщо пропустити стовпець, зміниться те, які рядки вважаються дублікатами, і буде видалено весь рядок, а не лише повторювану клітинку.
• Забути відсортувати дані, а потім дивуватися, чому залишився старий запис, а не новий.
• Порівняння клітинок, які виглядають однаково, але мають різне форматування, наприклад дати, записані двома способами, або текст із пробілами в кінці.
• Спроба виконати команду на згрупованих або з проміжними підсумками даних. Спочатку приберіть ці елементи.
• Використання UNIQUE з exactly_once, встановленим у TRUE, як способу видалити зайві рядки. Функція повертає лише значення, які трапляються один раз, тож повторювані значення зникають із результату повністю.

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

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

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

Перед наступним очищенням скопіюйте дані, підсвітьте дублікати умовним форматуванням і подивіться, скільки їх є. Потім виконайте «Видалити дублікати» на копії та порівняйте кількість із числами, які показує Excel.