Як знайти та видалити дублікати в Excel. 5 методів пошуку та видалення дублікатів в Excel

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

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

Спосіб 1: видаліть повторювані рядки вручну

Першим кроком є ​​використання найпростішого способу боротьби з дублікатами. Це ручний спосіб, який передбачає використання вкладки «Дані»:

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

5. Далі з’являється вікно, яке відображає інформацію по стовпцях. Потрібно вибрати стовпці, які потрібно перевірити на наявність дублікатів. Найкраще вибрати всі, щоб мінімізувати кількість пропусків.

6. Коли все буде готово, ще раз перевірте позначену інформацію та натисніть OK.
7. Excel автоматично проаналізує вибрані клітинки та видалить усі відповідні параметри.
8. Після повної перевірки та видалення дублікатів з таблиці в програмі з'явиться вікно, в якому буде повідомлення про завершення процесу та інформація про те, скільки відповідних рядків було видалено.

Вам залишається лише натиснути «ОК» і можна вважати, що все готово. Уважно виконуйте кожну дію, і результат вас точно не розчарує.

Спосіб 2: Видалення дублікатів за допомогою Smart Table

Тепер давайте детальніше розглянемо ще один корисний метод видалення дублікатів, який заснований на використанні «розумної таблиці». Досить дотримуватися таких рекомендацій:

1. Перш за все, виберіть всю таблицю, до якої ви хочете застосувати розумний алгоритм автоматичної обробки інформації.

2. Тепер скористайтеся панеллю інструментів, де потрібно вибрати розділ «Головна», а потім знайти «Форматувати як таблицю». Цей значок зазвичай знаходиться в підрозділі «Стилі». Залишилося скористатися спеціальною стрілкою вниз біля іконки і вибрати стиль оформлення столу, який вам найбільше сподобався.

3. Після того, як все буде зроблено правильно, з’явиться додаткове повідомлення про форматування таблиці. Він визначає діапазон, для якого буде застосовано функцію Smart Table. А якщо ви попередньо виділили потрібні комірки, то діапазон буде вказано автоматично і вам залишиться тільки перевірити його.

4. Залишається тільки почати пошук і подальше видалення повторюваних рядків. Для цього потрібно зробити додаткові кроки:
   • встановити курсор на довільну клітинку таблиці;
   • на верхній панелі інструментів виберіть розділ «Оформлення таблиці»;
   • шукаємо іконку у вигляді двох стовпчиків комірок різного кольору, при наведенні на них буде відображатися напис «Видалити дублікати»;
   • виконайте кроки, які ми вказали в першому методі після використання вказаної піктограми.

Звернути увагу! Цей метод має унікальну властивість – завдяки йому можна буде працювати з таблицями різних діапазонів без будь-яких обмежень. Будь-яка виділена область під час роботи з Excel буде ретельно проаналізована на наявність дублікатів.

Спосіб 3: Використання фільтра

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

1. Першим кроком є ​​вибір усієї таблиці, у якій ви збираєтеся маніпулювати, щоб видалити дублікати.
2. Тепер переходимо в розділ «Дані» і відразу переходимо в підрозділ «Фільтр».

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

Таким чином, можна відразу відфільтрувати всі дублікати і провести з ними додаткові маніпуляції.

Розширений фільтр для пошуку дублікатів у Excel

Є ще один додатковий спосіб використання фільтрів в Excel, для цього вам знадобляться:

1. Виконайте всі кроки попереднього способу.
2. У вікні інструментарію скористайтеся іконкою «Додатково», яка розташована поруч з тим же фільтром.

3. Після використання цього значка вам просто потрібно звернути увагу на вікно додаткових налаштувань. Цей розширений інструментарій дозволить вам ознайомитися з початковою інформацією:
   • спочатку слід перевірити вказаний діапазон таблиці, щоб він відповідав тому, що ви відмітили;
   • обов'язково встановіть прапорець «Тільки унікальні записи»;
   • як тільки все готово, залишається тільки натиснути на кнопку «ОК».

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

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

Спосіб 4: Умовне форматування

Умовне форматування - це спеціальний інструментарій, який використовується при вирішенні багатьох завдань. Ви можете використовувати цей інструмент для пошуку та видалення дублікатів у таблиці. Для цього вам потрібно буде зробити наступне:

1. Як і раніше, спочатку потрібно буде виділити комірки таблиці, які планується відформатувати.
2. Тепер слід перейти у вкладку «Головна» і знайти спеціальну іконку «Умовне форматування», яка знаходиться в підрозділі «Стилі».

3. Після виконання дій вам буде доступно вікно під назвою «Правила вибору комірок», далі потрібно вибрати пункт «Повторювані значення».

4. Обов'язково зверніть увагу на параметри форматування, їх слід залишити без змін. Єдине, що можна змінити, це колірне кодування відповідно до ваших уподобань. Коли все буде готово, можна натиснути «ОК».

5. Завдяки таким діям ви можете виділити всі дублікати іншим кольором і почати з ними працювати надалі.

Увага! Основний недолік цього методу в тому, що при використанні такої функції відзначаються абсолютно всі однакові значення, а не тільки ті варіанти, де збігається вся рядок. Варто запам'ятати цей нюанс, щоб уникнути проблем із зоровим сприйняттям і зрозуміти, як саме діяти і на що звернути увагу.

Спосіб 5. Формула для видалення повторюваних рядків

Даний спосіб є найскладнішим з усіх перерахованих, так як призначений виключно для тих користувачів, які розуміються на функціях і можливостях цієї програми. Адже метод передбачає використання складної формули. Це виглядає так: =ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));»»). Тепер потрібно визначитися, як саме його використовувати і де застосовувати:

1. Першим кроком є ​​додавання нової колонки, яка буде присвячена виключно дублікатам.

2. Виділіть верхню ячейку і введіть її формулу: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;А2:А90)>1;0;1);0));»»).
3. Тепер виберіть весь стовпець для дублікатів, не торкаючись заголовка.

• Ставте курсор в кінець формули, тільки будьте обережні з цим пунктом, так як формула не завжди добре видно в комірці, краще скористатися верхнім рядком пошуку і уважно подивитися на правильне розташування курсора.
• Після встановлення курсору необхідно натиснути кнопку F2 на клавіатурі.
• Після цього потрібно натиснути комбінацію клавіш «Ctrl + Shift + Enter».

4. Завдяки виконаним діям можна буде правильно заповнити формулу необхідною інформацією з таблиці.

Пошук збігів за допомогою команди Знайти

Тепер варто розглянути ще один цікавий варіант пошуку дублікатів. Саме для такого способу знадобиться інша формула, яка виглядає так: =COUNTIF(A:A, A2)>1.

Додаткова інформація! У цій формулі A2 означає позначку першої клітинки з області, в якій планується пошук. Як тільки формула буде введена в першу клітинку, ви можете перетягнути значення і отримати потрібну інформацію. Завдяки таким діям можна буде розподілити інформацію на «ПРАВДА» і «ХИБЬ». А якщо вам потрібно шукати в обмеженій області, то відзначте діапазон пошуку і обов'язково закріпіть ці позначення знаком $, що підтвердить фіксацію і зробить її основою.

Якщо вас не влаштовує інформація у формі «ПРАВДА» або «НЕПРАВДА», то пропонуємо скористатися такою формулою, яка структурує інформацію: =IF(COUNTIF($A$2:$A$17, A2)>1;”Дублікат”;”Унікальний”). Правильне виконання всіх дій дозволить отримати всі необхідні дії та швидко впоратися з наявною дублікатом інформації.

Як використовувати зведену таблицю для пошуку дублікатів

Додатковим методом використання функцій Excel для пошуку дублікатів є зведена таблиця. Правда, для того, щоб нею користуватися, все одно потрібно базове уявлення про всі функції програми. Що стосується основних дій, то вони виглядають так:

1. Першим кроком є ​​створення макета таблиці.
2. Ви повинні використовувати те саме поле як інформацію для рядків і значень.
3. Вибрані відповідні слова стануть основою для автоматичного підрахунку дублікатів. Тільки не забувайте, що основою функції підрахунку є команда «COUNT». Для подальшого розуміння майте на увазі, що всі значення, які перевищують значення 1, будуть дублікатами.

Зверніть увагу на скріншот, на якому показаний приклад такого способу.

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

Висновок

Тепер у вас є вся необхідна інформація щодо методів використання пошуку та видалення дублікатів, а також є рекомендації та поради, які допоможуть вам швидко вирішити проблему.