Excel є універсальним додатком, який виріс далеко за межами своїх ранніх версій, як просто рішення для електронних таблиць. Зайнятий як записуючий, адресна книга, інструмент прогнозування, і багато іншого, багато людей навіть використовують Excel таким чином, що він ніколи не був призначений.
Якщо ви використовуєте Excel багато вдома або в офісі, ви знаєте, що іноді файли Excel можуть швидко стати громіздкими через велику кількість записів, з якими ви працюєте.
На щастя, Excel має вбудовані функції, які допомагають знаходити та видаляти дублікати записів. На жаль, існує декілька застережень щодо використання цих функцій, тому будьте обережні, або ви можете невірно видаляти записи, які ви не збираєтеся видаляти. Крім того, обидва методи нижче миттєво видаляють дублікати, не даючи вам побачити, що було видалено.
Я також згадаю спосіб виділення перших рядків, щоб ви могли побачити, які з них будуть видалені функціями перед їх запуском. Ви повинні використовувати спеціальне правило умовного форматування, щоб виділити рядок, який повністю дублюється.
Видалити дублікати функції
Припустимо, що ви використовуєте Excel для відстеження адрес, і ви підозрюєте, що у вас є дублікати записів. Нижче наведено приклад таблиці Excel:
Зверніть увагу, що запис "Jones" з'являється двічі. Щоб видалити такі повторювані записи, клацніть на вкладці Дані на стрічці і знайдіть функцію Видалити дублікати в розділі Інструменти даних . Натисніть кнопку Видалити дублікати і відкриється нове вікно.
Тут ви повинні прийняти рішення, виходячи з того, чи використовуєте ви заголовки у верхній частині ваших стовпців. Якщо ви це зробите, виберіть параметр " Мої дані з заголовками" . Якщо ви не використовуєте мітки заголовків, ви будете використовувати стандартні позначення стовпців Excel, такі як стовпець A, стовпець B тощо.
У цьому прикладі ми виберемо лише стовпець А і натисніть кнопку ОК . Вікно опцій закривається, а Excel видаляє другий запис “Jones”.
З, звичайно, це був просто простий приклад. Будь-які записи адрес, які ви продовжуєте використовувати в Excel, набагато складніше. Припустимо, наприклад, у вас є файл адреси, який виглядає так.
Зверніть увагу, що, хоча є три записи “Джонса”, тільки дві ідентичні. Якщо ми використали наведені вище процедури для видалення дублікатів записів, залишиться лише один запис "Jones". У цьому випадку, ми повинні розширити наші критерії прийняття рішення, щоб включити і ім'я та прізвище, знайдені у стовпцях A та B відповідно.
Для цього ще раз перейдіть на вкладку Дані на стрічці і натисніть кнопку Видалити дублікати . На цей раз, коли з'явиться вікно параметрів, оберіть стовпці А і В. Натисніть кнопку ОК і зауважте, що на цей раз Excel видалив лише один з записів «Мері Джонс».
Це пояснюється тим, що ми наказали Excel видалити дублікати, зіставляючи записи на основі стовпців A і B, а не просто стовпця А. Чим більше вибраних стовпців, тим більше критеріїв необхідно виконати, перш ніж Excel розгляне запис як дублікат. Виберіть усі стовпці, якщо ви хочете видалити рядки, які повністю дублюються.
Excel надасть вам повідомлення про те, скільки дублікатів було видалено. Однак він не покаже вам, які рядки видалено! Прокрутіть вниз до останнього розділу, щоб дізнатися, як виділити повторювані рядки перед запуском цієї функції.
Розширений метод фільтра
Другий спосіб видалення дублікатів полягає у використанні додаткового параметра фільтра. Спочатку виберіть всі дані в аркуші. Далі на вкладці Дані в стрічці натисніть кнопку Додатково в розділі Сортувати і фільтрувати .
У діалоговому вікні, що з'явиться, переконайтеся, що прапорець Унікальні записи вимкнено.
Можна або фільтрувати список на місці, або скопіювати не повторювані елементи до іншої частини тієї ж електронної таблиці. З деяких причин неможливо скопіювати дані на інший аркуш. Якщо потрібно на іншому аркуші, спочатку виберіть місце на поточному аркуші, а потім виріжте і вставте ці дані в новий аркуш.
За допомогою цього методу ви навіть не отримаєте повідомлення про те, скільки рядків було видалено. Рядки видалені і все.
Виділіть повторювані рядки в Excel
Якщо ви хочете побачити, які записи дублюються, перш ніж видалити їх, ви повинні зробити трохи ручної роботи. На жаль, Excel не має способу виділити рядки, які повністю дублюються. Вона має функцію в умовному форматуванні, яка виділяє дубльовані комірки, але ця стаття стосується дубльованих рядків.
Перше, що вам потрібно зробити, це додати формулу в стовпці праворуч від набору даних. Формула проста: просто об'єднайте всі стовпці для цього рядка разом.
= A1 & B1 & C1 & D1 & E1
У наведеному нижче прикладі я маю дані в стовпцях A thru F. Однак перший стовпець є ідентифікаційним номером, тому я виключаю це з моєї формули нижче. Не забудьте включити всі стовпці з даними, на яких потрібно перевірити наявність дублікатів.
Я поклав цю формулу в колонку H, а потім потягнув її вниз для всіх моїх рядків. Ця формула просто поєднує всі дані в кожному стовпці як один великий фрагмент тексту. Тепер пропустіть кілька стовпців і введіть наступну формулу:
= COUNTIF ($ H $ 1: $ H $ 34, $ H1)> 1
Тут ми використовуємо функцію COUNTIF, а перший параметр - це набір даних, на які ми хочемо подивитися. Для мене це був стовпець H (який має формулу даних комбайна) від рядків 1 до 34. Також добре було б позбутися рядка заголовка перед цим.
Ви також повинні переконатися, що ви використовуєте знак долара ($) перед літерою і номером. Якщо у вас є 1000 рядків даних, а комбінована формула рядків у стовпці F, то ваша формула буде виглядати наступним чином:
= COUNTIF ($ F $ 1: $ F $ 1000, $ F1)> 1
Другий параметр має тільки знак долара перед буквою стовпця, так що він заблокований, але ми не хочемо блокувати номер рядка. Знову ж таки, ви перетягнете це вниз для всіх рядків даних. Він повинен виглядати так, а дублікати рядків повинні мати TRUE в них.
Тепер давайте виділимо рядки, які мають TRUE в них, так як це дублікати рядків. Спочатку виберіть весь аркуш даних, натиснувши на маленький трикутник у верхньому лівому перетині рядків і стовпців. Тепер перейдіть на вкладку Домашня сторінка, а потім клацніть на Умовне форматування і натисніть кнопку Нове правило .
У діалоговому вікні натисніть кнопку Використовувати формулу, щоб визначити, які комірки форматувати .
У полі Значення формату, де ця формула є істинною, введіть наступну формулу, замінивши P на ваш стовпець з значеннями TRUE або FALSE. Обов'язково вкажіть знак долара перед буквою стовпця.
= $ P1 = TRUE
Після цього натисніть Формат і натисніть вкладку Заливка. Виберіть колір, який буде використано для виділення всього дублюючого рядка. Натисніть "ОК", і ви побачите виділені повторювані рядки.
Якщо це не спрацювало для вас, почніть спочатку і знову робіть це знову повільно. Це має бути зроблено саме так, щоб все це працювало. Якщо ви пропустите один символ $ на цьому шляху, він не буде працювати належним чином.
Застереження щодо видалення повторюваних записів
Є, звичайно, кілька проблем, що дозволяють Excel автоматично видалити дублікати записів для вас. По-перше, потрібно бути обережним у виборі занадто мало або занадто багато стовпців для використання Excel як критеріїв ідентифікації дублікатів записів.
Занадто мало, і ви можете випадково видалити потрібні записи. Занадто багато або включають стовпець ідентифікатора випадково і не знайдено жодних дублікатів.
По-друге, Excel завжди припускає, що перший унікальний запис, з яким він стикається, є основним записом. Будь-які наступні записи вважаються дублікатами. Це проблема, якщо, наприклад, вам не вдалося змінити адресу одного з користувачів у файлі, а створили новий запис.
Якщо новий (правильний) запис адреси з'являється після старого (застарілого) запису, Excel буде вважати, що перший (застарілий) запис буде майстром і видаляти будь-які наступні записи, які він знаходить. Ось чому ви повинні бути обережними, як ви публічно або консервативно дозволяєте Excel вирішувати, що є або не є дублікатом запису.
Для цих випадків слід використати метод дублювання виділення, про який я писав, і вручну видалити відповідний дублікат запису.
Нарешті, Excel не просить вас перевірити, чи дійсно ви хочете видалити запис. Використовуючи вибрані параметри (стовпці), процес повністю автоматизований. Це може бути небезпечно, коли у вас величезна кількість записів, і ви вірите, що прийняті вами рішення були правильними і дозволили Excel автоматично видаляти дублікати записів для вас.
Також обов'язково перевірте нашу попередню статтю про видалення порожніх рядків у Excel. Насолоджуйтесь!
