Як знайти та прибрати дублікати в Excel

Існує кілька способів як знайти в Excel таблиці повторювані значення, рядки або стовпці. Щоб знайти їх, потрібно скористатися одним з 4ьох способів: формулою Excel, розширеним фільтром (filter advanced), умовним форматуванням (conditional formatting) або видаленням повторів в меню Дані.

4 Способи для пошуку повторів в Excel

Формула COUNTIF в Excel
Розширений фільтр (advanced filter) в Excel
Умовне форматування (condifional formatting) в Excel
Операція Видалення повторів в Меню Дані

Далі розглянемо кожен спосіб докладно із прикладом нижче. Функції та формули використовувати можна в різному поєднанні, і те як можна використовувати функцію COUNTIF() вже пояснював в іншій своїй статті. Однак для зручності тут опишу її конкретне застосування.

Знаходимо повтори в Excel за допомогою формули COUNTIF

Для цього методу беремо таблицю з інвойсами нижче. Мета — знайти повторювані рядки в колонці № Інвойсу.

Додаємо до таблиці нову колонку «Шукаємо повтори платежів (по ID)»
Додаємо функцію COUNTIF в першу клітинку нової колонки – G2:
після введення функції, натискаємо на кнопку f_x і заповнюємо Range (Діапазон) і Criteria (Критерій) в полі Range (Діапазон) вибираємо колонку «ID Оплати» і фіксуємо діапазон (додавання доларового знаку $ перед кожною літерою і кожним числом діапазону АБО натисканням клавіші F4), що в даному випадку, результат виглядатиме як $D$2:$D$7
в полі Criteria (Критерій) ставимо першу клітинку колонки «ID Платежу», а саме D2
натискаємо ОК
Тягнемо формулу вниз до останнього рядка № 7. В результаті ми бачимо, що формула видає число 1 для одиничних рядків (унікальних або недубльованих). Всі інші значення вище одиниці, наприклад 2, означають, скільки дублікатів у таблиці, а саме скільки точно разів представлений номер інвойсу навпроти формули.
Отже, в нашій таблиці ID Платежу 51 в наявності 2 рази.

Для перегляду онлайн прикладів всіх перерахованих вище способів, нижче приклад формули:

=COUNTIF($D$2:$D$7;D2)

Видаляємо повтори в Excel за допомогою розширеного фільтра (advanced filter)

Advanced filter можна використовувати для фільтрування таблиці, використовуючи кілька фільтрів одночасно і для видалення дублікатів. Наприклад, ви можете одним натисканням клавіші відфільтрувати таблицю за заданим списком фільтрів (наприклад, відфільтрувати в таблиці 5 колонок по 6 параметрам).

В даному випадку цей інструмент дозволяє позбавлятися дублікатів – значень, що повторюються. За допомогою цього фільтра ви можете зробити 2 речі на вибір:

відфільтрувати в таблиці значення, видаливши всі дублікати
скопіювати таблицю тільки з унікальними значеннями, що не повторюються, в інше місце (але тільки в тому ж аркуші)

Щоб використовувати розширений фільтр, потрібно:

Виділити таблицю.
Зайти на вкладку Дані (Data) і знайти в графі “Сортування й Фільтр” і натиснути Додатково (Advanced)
Далі обираємо пункти меню «Розширений фільтр»
вибираємо фільтрувати список на місці (Filter the list, in-place) або скопіювати результат до іншого розташування (Copy to another location)
можемо заново вибрати таблицю (якщо ще не вибрали) (List range)
якщо вибрали копіювати в нове місце, то вибрати діапазон для результату (Copy to)
обов’язково поставити галочку “Лише унікальні записи” (Unique records only)
після цього, натиснувши ОК, ми отримаємо миттєвий результат.

Фарбуємо повтори в Excel за допомогою умовного форматування (conditional formatting)

Умовне форматування – потужний і дуже зручний візуальний інструмент Excel. Якщо вміти ним користуватися, то в залежності від того, які дані в таблиці вони будуть підсвічені тим або іншим кольором (який ви самі виберете).

Наше завдання – підсвітити жовтим кольором дублікати за допомогою Умовного Форматування (Conditional Formatting):