Як прибрати значення, що повторюються, в excel. Фільтр унікальних значень або видалення значень, що повторюються. Як прибрати дублікати в Excel

Крихітка син до батька прийшов, і спитала малюк...

Ні, не так. Насправді підійшов співробітник та сказав — а чи не поставити нам ексель 2010 року? З досвіду знаю, що йому потрібно кілька разів на день заповнювати невелику таблицю, нічого складного. Тому одразу постало логічне запитання — а тобі навіщо? На що цілком логічна відповідь — а там можна однією командою осередки, що дублюються, видалити. Угу. Тобто 3-4 УРАХУВАННЯМ. за те, щоб дублі видалити. А треба сказати, я взагалі дуже погано ставлюся до невиправданих витрат у бізнесі. Одна річ, коли щось потрібне для безпосереднього виконання якоїсь функції, яку ні в чому іншому виконати не можна. Або займає стільки часу, що дешевше оптимізувати, або написати під це спеціальну програму— ось зараз, наприклад, пишемо за півтори штуки доларів одну таку. А інша справа, коли хтось хоче на 10 хвилин довше посидіти у вконтакті в робочий час, і просто лінується розібратися, як натиснути пару кнопок.

Ну гаразд, зараз розповім, як видалити дублікати в excel 2003, і ​​можна йти далі вигадувати, навіщо ще 2010-й тобі може знадобитися (ні, для чого він потрібний мені — я чудово знаю:).

Найпростіший спосіб а) - як видалити повторювані значення excel:

1. Беремо, виділяємо діапазон осередків з дублями, натискаємо на Дані -> Фільтр -> Розширений фільтр…

3. Отримуємо результат, який можна зробити Ctrl+C - Ctrl+V на потрібне місце/аркуш.

Тепер варіант B) для тих, хто не боїться складностей 🙂

1. Лівіше крайнього лівого стовпця нашої таблиці вставляємо додатковий стовпець (припустимо, у нас був А - вставимо ще один А, щоб наш став B), і в ньому проставляємо порядкові номери ( звичайним введеннямв осередках цифр 1 і 2, виділяючи ці два осередки і подвійним кліком на чорній точці в правому нижньому куті все поширюється до кінця діапазону). Це нам буде потрібно потім, якщо ми захочемо відновити порядок записів, якщо він не важливий — так можна і не робити. Вийде приблизно так:

3. Сортуємо список за стовпцем B, скажімо, за зростанням.

4. У комірку C2 вставляємо формулу =ЯКЩО(B2=B1;0;1), яка порівнює кожне значення з попереднім. Якщо рядок дубль, то їй буде присвоєно значення 0, якщо ні то 1. Ну, звичайно, значення B2 і B1 це на моєму прикладі, все залежить, скільки стовпців в таблиці.

5. Клацаємо на обведену червоним кружечком крапку в правому нижньому кутку комірки, щоб продовжити формулу на всю колонку (аналогічно, як ми вставляли порядкові номери):

6. З отриманим результатом робимо Ctrl+C, йдемо в Правка -> Спеціальна вставка

7. У діалозі, що відкрився, вибираємо — Вставити Значення

8. Тепер виділяємо перші три осередки у рядку 2, із затиснутим шифтом клацаємо на нижній межі виділення, таким чином - виділивши все з A2 по С12. Тиснемо Дані ->Сортування, сортуємо по стовпцю С, за спаданням (це важливо - відсортувати саме за зменшенням! Якби ми дублям призначили 1, а не 0 - то треба було б відсортувати навпаки, за зростанням). Скріншот наводити не буду, оскільки абсолютно аналогічно крокам 2 та 3.

9. Виділяємо стовпець С, натискаємо Ctrl-F, вводимо у форму пошуку 0, і шукаємо в цьому стовпці першу по порядку комірку з нулем.

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

11. Видаляємо значення зі стовпця С - він теж свою роль зіграв.

12. Виділяємо стовпці А і B, тиснемо Дані ->Сортування, і сортуємо по стовпцю А (у моєму випадку - за номерами) за зростанням.

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

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

Самим простим способом, як рядки, є використання відповідного інструменту, що знаходиться у стрічці.

Отже, вам потрібно:

1. Затиснувши ліву кнопкумиші, виділити необхідний діапазон осередків, в межах якого буде пошук та автоматичне видаленняповторюваних рядків.
2. Перейти до вкладки "Дані".
3. Натисніть інструмент "Видалити дублікати", який знаходиться в групі "Робота з даними".
4. У вікні, що з'явилося, поставити галочку поруч з "Мої дані містять заголовки".
5. Встановити галочки поруч із назвою тих стовпців, у яких проводитиметься пошук. Візьміть до уваги, що якщо встановлені всі галочки, дублікатом будуть вважатися рядки, які повністю повторюються у всіх стовпцях. Таким чином, якщо ви хочете видалити дублікати з одного стовпця, необхідно залишити лише одну галочку поряд з його найменуванням.
6. Натиснути "ОК".

Як тільки ви натиснете на кнопку, почнеться пошук дублікатів виділеної області, а потім їх видалення. За підсумком з'явиться вікно, де буде продемонстровано звіт. Тепер ви знаєте, як в Excel видалити рядки, що повторюються, але лише перший спосіб, на черзі другий.

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

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

Щоб видалити повтори в Excel, вам потрібно зробити наступне:

1. Як і в попередньому способі, спочатку виділити діапазон осередків, де необхідно видалити дублікати.
2. Натисніть кнопку "Форматувати як таблицю" на вкладці "Головна" в групі інструментів "Стилі".
3. Вибрати з меню, що сподобалася стиль (будь-який).
4. У вікні необхідно підтвердити вказаний раніше діапазон осередків, а якщо він не збігається, то перепризначити. Також встановіть галочку поруч із «Таблиця із заголовками», якщо вона така, за підсумком натисніть кнопку «ОК».
5. "Розумна таблиця" створена, але це ще не все. Тепер вам необхідно виділити будь-яку комірку таблиці, щоб у вкладках з'явився пункт "Конструктор" і безпосередньо перейти до цієї вкладки.
6. У стрічці інструментів потрібно натиснути кнопку "Видалити дублікати".

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

Висновок

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

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

Microsoft Excel- це одна з самих популярних програмдля роботи з числами та таблицями

Другий підхід полягає у використанні розширеного фільтра і працює як на нових версіях Microsoft Excel, так і на старих, включаючи 2003. Тут доведеться натискати трохи більше, однак такий підхід в рази ефективніший. Отже, спочатку потрібно відкрити таблицю, потім перейдіть у вкладку "Дані", у блоці "Сортування та фільтр" виберіть пункт "Додатково". Перед вами відкриється вікно, в якому потрібно буде задати діапазон (тільки в тому випадку, якщо у вас є розриви, інакше нічого не потрібно задавати) і поставити пташку на пункті «Тільки унікальні записи». Якщо ви хочете просто приховати дублікати, щоб мати можливість ще попрацювати з ними надалі, то виберіть фільтрування списку на місці, якщо ж ви вкажете «скопіювати результат в інше місце», то елементи, що повторюються, будуть видалені. Після натискання «ОК» таблиця буде відфільтрована програмою відповідно до вибраних параметрів. Тепер виділіть кольором унікальні елементи та натисніть «Очистити». В результаті ви побачите всі дублікати, оскільки вони не будуть відзначені кольором. Скориставшись автофільтром, можна буде отримати повний список не унікальних значень.

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

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

1. Видалення значень, що повторюються, в Excel (2007+)

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

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

Клацаємо ОК, діалогове вікно буде закрито і рядки, що містять дублікати, будуть видалені.

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

2. Використання розширеного фільтра для видалення дублікатів

Виберіть будь-яку комірку в таблиці, перейдіть по вкладці Дані до групи Сортування та фільтр, клацніть на кнопці Додатково.

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

На місці, вказаному для розміщення результатів роботи розширеного фільтра, буде створено ще одну таблицю, але вже з відфільтрованими, за унікальними значеннями, даними.

3. Виділення повторюваних значень за допомогою умовного форматування в Excel (2007+)

Виділяємо таблицю, в якій необхідно виявити значення, що повторюються. Переходимо по вкладці Головна в групу Стилі, вибираємо Умовне форматування -> Правила виділення осередків -> Значення, що повторюються.

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

4. Використання зведених таблиць для визначення повторюваних значень

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

Створюємо зведену таблицю. У полі Назва рядків поміщаємо три перші стовпці, у полі Значення поміщаємо стовпець з лічильником. У створеній зведеної таблиці, записи зі значенням більше одиниці будуть дублікатами, саме значення означатиме кількість значень, що повторюються. Для більшої наочності можна відсортувати таблицю по стовпцю Лічильник, щоб згрупувати дублікати.

У даному матеріалі ми дуже, дуже швидко видалимо дублікати або залишимо унікальні записи зі списку, кому як більше подобається, відсортуємо список, а також зробимо так, щоб список з унікальними записами можна було швидко оновлювати, якщо в основному переліку відбуваються зміни.

Супершвидкий спосіб видалення дублікатів записів

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

Для цього достатньо:

а) форматувати діапазон як таблицю. Виділяємо діапазон, вибираємо вкладу "Головна", групу "Стилі", "Форматувати як таблицю" та бажаний стиль оформлення таблиці, внизу команда, де можна створити свій власний варіант

б) видалити дублікати за допомогою команди «Видалити дублікати» у групі «Інструменти» на додатковій вкладці «Конструктор», яка з'явиться після перетворення діапазону на таблицю

Діалогове вікно видалення дублікатів гранично просте.

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

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

Результат виконаної роботи:

Такий спосіб підійде, якщо потрібно швидко прибрати дублікати записів на листі Excel, а самі зміни відобразити у вихідному діапазоні. Якщо ж вихідний діапазон може оновлюватися, то щоразу робити подібні дії не самий кращий спосібпозбавитися дублікатів і тут на допомогу прийде другий спосіб видалення дублікатів на листі Excel.

Видалення дублікатів за допомогою запитів (Power Query)

У 2016 версії Excel Power Query або створення запитів спочатку входить у базове постачання, а для версій 2013, 2010 потрібно буде самостійно встановити це безкоштовну надбудову.

Перший етап видалення дублікатів з допомогою Power Query так само, як і в попередньому способі - необхідно діапазон відформатувати як таблицю. Далі на вкладці «Дані» у групі «Завантажити та перетворити» вибрати команду «З таблиці». З'явиться вікно редактора запитів, в якому, за допомогою контекстного меню по заголовку стовпця з нумерацією його можна видалити, а по заголовку стовпця з ПІБ вибрати команду «Видалити повторення», це можна зробити і за допомогою вкладки «Головна» редактора запитів.

За допомогою фільтра можна приховати порожні рядки (вони тут позначені як «null») і відсортувати, за потреби, відібрані значення, потім вибрати команду «Закрити та завантажити/ Закрити та завантажити в…»

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

Перевагою способу видалення дублікатів за допомогою Power Query є не тільки те, що вихідний діапазон залишається недоторканим, але й те, що, додаючи нові записи у вихідний діапазон, вони будуть з'являтися в обробленому, природно, якщо ці записи унікальні. Для оновлення обробленого списку потрібно викликати контекстне менюна ньому і вибрати "Оновити", або натиснути "Оновити" зі стрічки інтерфейсу.