Excel

Пошук і видалення дублікатів в Microsoft Excel

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

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

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

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

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

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

    2. Видалення дублікатів в Microsoft Excel

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

    3. Вікно видалення дублікатів в Microsoft Excel

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

Інформаційне вікно в Microsoft Excel

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

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

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

    2. Виділення таблиці в Microsoft Excel

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

    3. Створення розумної таблиці в Microsoft Excel

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

    4. Підтвердження діапазону для створення розумної таблиці в Microsoft Excel

  4. Але створення «розумної таблиці» - це тільки один крок для вирішення нашого головного завдання - видалення дублікатів. Кількома по будь-якому осередку табличного діапазону. При цьому з'являється додаткова група вкладок «Робота з таблицями». Перебуваючи у вкладці «Конструктор» натискаємо на кнопку «Видалити дублікати», яка розташована на стрічці в блоці інструментів «Сервіс».

    5. Перехід до видалення дублікатів в Microsoft Excel

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

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

урок: Як зробити таблицю в Excel

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

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

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

    2. Включення фільтра в Microsoft Excel

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

    3. Перехід в розділ Додатково в Microsoft Excel

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

Вікно розширеного фільтра в Microsoft Excel

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

Включення показу дублів в Microsoft Excel

урок: Розширений фільтр в Excel

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

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

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

    2. Перехід до умовного форматіпррованію в Microsoft Excel

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

Налаштування форматування в Microsoft Excel

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

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

урок: Умовне форматування в Excel

урок: Умовне форматування в Excel

Спосіб 5: застосування формули

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

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));"")

  1. Створюємо окремий стовпець, куди будуть виводитися дублікати.

    2. Стовпець для дублікатів в Microsoft Excel

  2. Вводимо формулу за вказаною вище шаблоном в першу вільну комірку нового стовпчика. У нашому конкретному випадку формула буде мати такий вигляд:

    =ЕСЛИОШИБКА(ИНДЕКС(A8:A15;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E7:$E$7;A8:A15)+ЕСЛИ(СЧЁТЕСЛИ(A8:A15;A8:A15)>1;0;1);0));"")

    3. Формула в Microsoft Excel

  3. Виділяємо весь стовпець для дублікатів, крім шапки. Встановлюємо курсор в кінець рядка формул. Натискаємо на клавіатурі кнопку F2. Потім набираємо комбінацію клавіш Ctrl + Shift + Enter. Це обумовлено особливостями застосування формул до масивів.

Виділення сторлбца в Microsoft Excel

Після цих дій в стовпці «Дублікати» відобразяться повторювані значення.

Відображення дублікатів в Microsoft Excel

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

Як бачимо, в Ексель є безліч інструментів призначених для пошуку і видалення дублів. У кожного з них є свої особливості. Наприклад, умовне форматування передбачає пошук дублів тільки по кожному осередку окремо. До того ж, не всі інструменти можуть не тільки шукати, але і видаляти повторювані значення. Найбільш універсальний варіант - це створення «розумної таблиці». При використанні даного способу можна максимально точно і зручно налаштувати пошук дублікатів. До того ж, їх видалення відбувається моментально.