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

Зміст

• Пошук і видалення
  • Спосіб 1: просте видалення повторюваних рядків
  • Спосіб 2: Видалення дублікатів у " розумній таблиці»
  • Спосіб 3: Застосування сортування
  • Спосіб 4: умовне форматування
  • Спосіб 5: застосування формули
• Питання та відповіді

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Урок: як зробити таблицю в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=якщо помилка(Індекс(адреса_стовпця;ПОИСКПОЗ(0;СЧЕТЕСЛІ(адреса_шапкі_стовпця_дублікатов: адреса_шапкі_стовпця_дублікатов (абсолютний); адреса_стовпця;)+якщо(СЧЕТЕСЛІ(адреса_стовпця;; адреса_стовпця;)>1;0;1);0));"")

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

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

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


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

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

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

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