Повторювані значення в Excel: як вилучити дублікати, або просто знайти і виділити "

Доброго часу доби!

З популяризацією комп'ютерів за останні 10 років - відбувається і популяризація створення звітів (документів) у програмі Excel.

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

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

Примітка: всі приклади нижче будуть представлені в Office 2016/2019 (актуально також для Office 2013, 2010, 2007). Рекомендую завжди використовувати відносно нові версії Office: в них і швидше працювати, і простіше.

*

Видалити дублікати рядків Excel за допомогою функції «Видалити дублікати»

Якщо ви використовуєте останні версії Excel 2007, Excel 2010, Excel 2013 або Excel 2020, у вас є перевага, тому що ці версії містять вбудовану функцію для пошуку і видалення дублікатів - функцію Вилучити дублікати.

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

Примітка. Оскільки функція Вилучити дублікати назавжди видаляє ідентичні записи, рекомендується створити копію вихідних даних перед видаленням повторюваних рядків.

1. Для початку виберіть діапазон, в якому ви хочете видалити дублікати. Щоб вибрати всю таблицю, натисніть Ctrl + A.
2. Потім натисніть кнопку «Видалити дублікати» на вкладку «ДАНІ» - > «Робота з даними».

Вилучити дублікати в Excel - Функція Вилучити дублікати в Excel

1. Відкриється діалогове вікно «Видалити дублікати». Виберіть стовпчики для перевірки дублікатів та натисніть кнопку «OK».

• Щоб вилучити дублікати рядків, які мають повністю однакові значення у всіх стовпчиках, залиште позначки поряд з усіма стовпчиками, як показано на зображенні нижче.
• Щоб видалити часткові дублікати на основі одного або декількох ключових стовпчиків, виберіть лише відповідні стовпчики. Якщо у вашій таблиці багато стовпчиків, краще натиснути кнопку «Зняти виділення», а потім вибрати стовпчики, які ви хочете перевірити на предмет дублікатів.
• Якщо у вашій таблиці немає заголовків, заберіть прапор з поля «Мої дані містять заголовки» у верхньому правому куті діалогового вікна, яке зазвичай вибирається за замовчуванням.

Вилучити дублікати в Excel - Вибір стовпчика (ів), який ви хочете перевірити на наявність дублікатів

Готово. Всі дублікати рядків у виділеному діапазоні видаляються, і з'являється повідомлення, що вказує, скільки було видалено дублікатів записів і скільки унікальних значень залишилося.

Вилучити дублікати до Excel - Повідомлення про вилучення дублікатів

Функція «Вилучити дублікати» у Excel видаляє 2-ий та всі наступні дублікати екземплярів, залишаючи всі унікальні рядки та перші екземпляри однакових записів. Якщо ви хочете вилучити дублікати рядків, включаючи перші входження, тобто якщо ви ходите вилучити всі дублюючі комірки. Або в іншому випадку, якщо є два або більше дублікати рядків, і перший з них ви хочете залишити, а всі наступні дублікати видалити, використовуйте одне з наступних рішень, описаних у цьому розділі:

Пошук і вилучення

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

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

Найпростіше вилучити дублікати - це скористатися спеціальною кнопкою на стрічці, призначеною для цих цілей.

1. Виділяємо весь табличний діапазон. Переходимо на вкладку «Дані». Тиснемо на кнопку «Видалити дублікати». Вона розташовується на стрічці в блоці інструментів «Робота з даними».

Відкривається вікно видалення дублікатів. Якщо у вас таблиця з шапкою (а в переважній більшості завжди так і є), то біля параметра «Мої дані містять заголовки» повинна стояти галочка. У основному полі вікна розташовано список стовпчиків, за якими буде проведено перевірку. Рядок буде вважатися дублем тільки в разі, якщо дані всіх стовпчиків, виділених галочкою, збігаються. Тобто, якщо ви знімете галочку з назви якогось стовпця, то тим самим розширюєте ймовірність визнання запису повторним. Після того, як всі необхідні параметри зроблені, тиснемо на кнопку «OK».

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

Спосіб 2: видалення дублікатів у «розумній таблиці»

Дублікати можна вилучити з діапазону комірок, створюючи розумну таблицю.

Виділяємо весь табличний діапазон.

На вкладці «Головна» натисніть кнопку «Форматувати як таблицю», розташовану на стрічці в панелі інструментів «Стилі». У списку, що з'явиться, вибираємо будь-який стиль, що сподобався.

Потім відкривається невелике віконце, в якому потрібно підтвердити виділений діапазон для формування «розумної таблиці». Якщо ви вибрали все правильно, ви можете підтверджувати помилку, якщо допустили помилку, у цьому вікні слід виправити. Важливо також звернути увагу на те, щоб біля параметра «Таблиця з заголовками» стояла галочка. Якщо її немає, то слід поставити. Після завершення всіх налаштувань натисніть кнопку «OK». «Розумна таблиця» створена.

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

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

Спосіб 3: застосування сортування

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

Виділяємо таблицю. Переходимо на вкладку «Дані». Тиснемо на кнопку «Фільтр», розташовану в блоці параметрів «Сортування та фільтр».

Фільтр увімкнено, про що показано піктограми, що з'явилися у вигляді перегорнутих трикутників у назвах стовпчиків. Тепер нам потрібно його налаштувати. Натискаємо кнопку «Додаткові», розташовану поряд з тією самою групою інструментів «Сортування та фільтр».

Відкривається вікно розширеного фільтра. Встановлюємо в ньому галочку навпроти параметра «Тільки унікальні записи». Всі інші параметри залишаємо типовими. Після цього клікаємо по кнопці «OK».

Після цього, повторювані записи будуть приховані. Але їх можна в будь-який момент включити повторним натисканням на кнопку «Фільтр».

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

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

Вилучити дублікати, скопіювавши унікальні записи в інше місце

Інший спосіб видалення дублікатів у Excel - це поділ унікальних значень та копіювання їх на інший аркуш або у виділений діапазон на поточному аркуші. Розберемо цей спосіб.

1. Виберіть діапазон або всю таблицю, яку ви хочете вилучити дублікати.
2. Перейдіть до вкладки «ДАНІ» - > «Сортування та фільтр» та натисніть кнопку «Додаткові».

Вилучити дублікати в Excel - Використання додаткового фільтра для вилучення дублікатів

1. У діалоговому вікні «Розширений фільтр» виконайте такі дії:

• Виберіть «скопіювати результат в інше місце».
• Перевірте чи буде показано правильний діапазон у Вихідному діапазоні. Це має бути діапазон, обраний на кроці 1.
• У полі Розмістити результат у діапазоні введіть діапазон, в якому ви хочете скопіювати унікальні значення (насправді достатньо вибрати верхню ліву комірку діапазону призначення).
• Виберіть Лише унікальні записи

Вилучити дублікати у фільтр дублікатів Excel

1. Нарешті, натисніть OK. Excel видалить дублікати і скопіює унікальні значення в нове вказане місце:

Вилучити дублікати в Excel - Унікальні записи, скопійовані з іншого місця

Таким чином ви отримуєте нові дані, на основі зазначених, але з віддаленими дублікатами.

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

Виділіть кольором дублікати в таблиці

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