Як видалити дублікати в Google Sheets

З цієї статті ви дізнаєтесь, як видалити повтори даних у Google Sheets за допомогою інструмента «Видалити повтори даних», функції UNIQUE, зведених таблиць, умовного форматування, сценарію програм Apps Script або надбудов.

Насамперед, що таке дублікати? І чому вони – проблема? Повторення даних (або дублюючі записи, або дублюючі рядки) – це випадки появи одного і того ж запису у ваших даних більше одного разу. Вони являють собою величезну проблем для будь-якого аналітика даних. Дуже важливо знайти та видалити їх до початку аналізу даних.

Уявіть, що у вашій базі даних є два екземпляри однієї і тієї ж клієнтської транзакції на суму $5 000. Коли ви узагальнюєте дані, ви можете подумати, що у вас є прибуток від цього клієнта в розмірі $10 000, тоді як насправді у вас є лише $5 000. Ви прийматимете рішення на основі невірних даних. А ми всі знаємо, що це погано для бізнесу.

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

Рекомендації по очищенню таблиць від повторюваних осередків (дублікатів)

У більшості випадків я рекомендував би використовувати інструмент «Видалити повтори даних», оскільки це швидше і простіше, ніж інші методи. Тим не менш, ось короткий опис того, коли має сенс використовувати різні методи:

Спосіб 1: Інструмент «Видалити повтори даних» — найнадійніший і найпрактичніший метод видалення дублікатів.

Спосіб 2: Формули. А) UNIQUE . Вона підходить для невеликих, простих наборів даних або коли вам потрібно видалити повтори даних всередині вкладеної формули.

Б) Формули IF – надійний, але досить старий підхід. Їхня перевага полягає в тому, що вам доведеться забруднити руки в даних, щоб побачити повтори даних поблизу.

Спосіб 3: Зведені таблиці – чудовий спосіб пошуку дублікатів у Google Sheets. Зведені таблиці дуже гнучкі та швидкі у використанні, тому це відмінний інструмент для використання, коли ви не впевнені, чи є у вас повтори даних, та перевіряєте свої дані.

Спосіб 4: Умовне форматування – відмінний спосіб виділення дублікатів у Google Sheets.

Спосіб 5: сценарій Apps Script корисний для розробників, які хочуть видаляти повтори даних з таблиць як частину своїх додатків, або для тих, кому потрібно багаторазово і автоматично видаляти повтори даних.

Спосіб 6: Додаток дає більше можливостей для видалення дублікатів (наприклад, створення свіжої копії даних, виділення записів, що дублюються для перегляду і т.д.), тому це хороший варіант, якщо вам потрібно розібратися в дублікатах. Однак це доповнення платне, тому здається непотрібним, враховуючи інші безкоштовні інструменти у вашому розпорядженні.

Спосіб 1: Як видалити повтори даних у Google Sheets за допомогою інструмента «Видалити повтори»

Нова функція дуже проста у використанні. Ви можете знайти цю функцію в меню: Дані > Видалити повтори даних

Як видалити дублікати в Google Sheets

Після того, як ви натиснете на кнопку «Видалити повтори даних», ви повинні вибрати, в якому саме діапазоні осередків шукати повтори.

Як видалити дублікати в Google Sheets

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

Як видалити дублікати в Google Sheets

Спосіб 2: Як видалити повтори даних у Google Sheets за допомогою формул

2.1. Формула UNIQUE()

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

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

Це дуже просто реалізувати, оскільки для цього використовується одна формула з одним аргументом – діапазон, який ви хочете очистити від дублікатів (де-дуплікувати)

=UNIQUE(A1:D11)

Ось приклад функції UNIQUE у дії. Функція знаходиться в клітинці F1 і шукає повтори даних у діапазоні даних A1: D11

Як видалити дублікати в Google Sheets

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

Як видалити дублікати в Google Sheets

2.2. Виділення дублікатів значень за допомогою COUNTIF

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

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

Потім використовуйте цю формулу в осередку B2, щоб виділити повтори даних у стовпці A:

=COUNTIF(A$2:A2;A2)>1

Зверніть увагу, що діапазон – A $ 2: A2 . Знак $ тут є ключовим, тому що він фіксує діапазон у верхній частині стовпця. Іноді ми хочемо зафіксувати осередок, щоб він не змінювався при копіюванні формули – для цього використовується абсолютна адресація. Щоб вказати програмі, що посилання не потрібно міняти, використовується абсолютна адресація зі знаком «$»:

Якщо дані в осередку з'являються у вказаному діапазоні один раз і не повторюються – повернеться брехня. Якщо двічі і більше – повернеться значення функції ІСТИНА.

Як видалити дублікати в Google Sheets

Залишиться лише виділити значення TRUE (повтори даних) та видалити їх.

Примітка : якщо у вас великий набір даних з великою кількістю дублікатів, то найкраще перетворити текст у стовпці Duplicate на значення (Копіювати > Вставити значення), потім відсортувати таблицю по цьому стовпцю, щоб усі повтори даних (TRUE) опинилися в блоці в одній частині набору даних, а потім видалити їх однією великою групою. Це набагато швидше.

2.3. Як видалити повтори даних у Google Sheets за допомогою формул IF

Саме так я вперше навчився знаходити повтори даних та видаляти їх.

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

Це включає використання формули IF для перевірки того, чи значення в поточному рядку значення в попередньому рядку. Якщо вони рівні, це дублююча запис. Дублюючим записам надається значення 1, а унікальним (не дублюючим) – 0. Таким чином, ви можете легко підрахувати кількість дублікатів (шляхом підсумовування стовпця).

Ось зображення, яке показує цей метод у дії. Формули ми розберемо трохи пізніше:

Як видалити дублікати в Google Sheets

Зверніть увагу, що в російській версії Google-таблиць знак роздільника – це точка з комою;

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

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

Ви можете бачити, що номер рахунка-фактури «196-X» у рядку 4 був позначений цифрою 1, щоб вказати, що це рядок, що дублює. Якщо ви бажаєте розглянути більше стовпців або весь ряд, використовуйте один з інших методів, описаних у цій статті.

Наступний крок — виділення пар, що дублюються:

Як видалити дублікати в Google Sheets

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

Останній крок – видалення рядків, що дублюються.

Ось весь процес, який пояснюється більш докладно:

Крок 1: Додати унікальний стовпець

Цей крок дуже важливий. Він дозволяє сортувати дані за будь-яким стовпцем, а потім легко повернутися до початкового порядку. (Примітка: на скріншоті вище це не показано).

Крок 2: Сортування А-Я за тим стовпцем, який є вашим «унікальним» стовпцем

У вас є номер рахунку, який ви можете перевірити на наявність дублікатів? Чи номер посилання на транзакцію? Чи є ідентифікатор студента? Визначте стовпець, який має бути унікальним. Потім відсортуйте його від А до Я. Це відсортує всі записи поруч один з одним (тому дублюючі записи будуть поруч з їх унікальними аналогами).

Крок 3: Вставте два нових стовпці праворуч від «унікального» стовпця.

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

Крок 4: Визначте записи, що дублюються, за допомогою формули IF

Основна формула для перевірки дублікатів у стовпці B виглядає так:

=IF(B3=B2;1;0)

Крок 5: Виявлення дублюючих угруповань за допомогою формул АБО та АБО

А потім формула в сусідньому стовпці, яка ідентифікує дублюючі групи (тобто перший запис та наступні повтори даних):

=IF(OR(D3=1;D2=1);1;0)

Крок 6: Перегляд дублікатів записів

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

Крок 7: Збережіть копії дублюючих формул для майбутніх довідок (необов'язково)

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

Крок 8: Перетворіть повтори даних стовпців на значення

Виділіть дані (що складаються з 0 та 1) у двох дублюючих стовпцях.

Копіювати > Вставити спеціальні > Вставити лише значення

Крок 9: Сортування за повторюваними стовпцями для переміщення дублікатів

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

Крок 10: відсортуйте набір даних по стовпцю вихідного порядку

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

Коротко, цей метод:

  • дозволяє виділити повтори даних у Google Sheets;
  • дозволяє визначити групи, що дублюються в Google Sheets;
  • зберігає копії всіх даних до тих пір, поки ви не будете готові видалити рядки;
  • проте це досить трудомістка робота, пов'язана із хитрими формулами;
  • змінює вихідний набір даних (якщо ви не зробили копію).

2.4. Використання формул IF для кількох стовпців

Це те саме, що і метод вище, але застосовується до кількох стовпців. У цьому випадку ви хочете шукати повтори даних у Google Sheets на основі двох (або більше) стовпців. Можливо номер рахунка-фактури та ідентифікатор продукту.

Перше, що потрібно зробити, – визначити стовпці, які ви хочете включити до розгляду дублікатів. Цього разу вставте три нові стовпці праворуч від останнього «унікального» стовпця.

Використовуйте формулу зчеплення всіх «унікальних» стовпців, які ви розглядаєте для виділення дублікатів:

=B2&C2

або ви також можете використовувати формулу CONCATENATE (на цей раз, включаючи більше стовпців):

=CONCATENATE(B2;C2;D2;E2)

Цей новий стовпець складається з усіх «унікальних» стовпців, що складаються разом. Тепер використовуйте спосіб 2.3 для видалення цього нового «унікального» стовпця об'єднаних значень. Для виявлення дублікатів та дублюючих груп пройдіть ті самі 10 кроків, що й у способі 2.3.

Ось приклад:

Як видалити дублікати в Google Sheets

Спосіб 3: Як видалити повтори даних у Google Sheets за допомогою зведених таблиць

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

Крок 1. Виділіть свій набір даних та створіть зведену таблицю (в меню «Дані»). Відкриється нова вкладка із редактором зведених таблиць.

У розділі ROWS (рядки) виберіть той стовпець, який потрібно перевірити на наявність дублікатів (наприклад, номер рахунку). Потім в VALUES (значення) виберіть інший стовпець (я часто використовую один і той же) і переконайтеся, що він налаштований на підсумовування за функціями COUNT (РАХУНОК) або COUNTA (РАХУНОК) (якщо ваш стовпець містить текст), як показано нижче:

Як видалити дублікати в Google Sheets

Виділення дублікатів у Google Sheets за допомогою зведеної таблиці виглядатиме ось так:

Як видалити дублікати в Google Sheets

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

Спосіб 4: Як видалити повтори в Google Sheets за допомогою умовного форматування

Цей метод складається з двох кроків: 1) виділення дублікатів у Google Sheets та 2) видалення виділених дублікатів (необов'язково).

Крок 1 Виберіть набір даних та відкрийте бічну панель умовного форматування (в меню «Формат»). У розділі «Форматувати комірки, якщо…» виберіть формулу користувача (останній варіант) і введіть таку формулу:

=COUNTIF($A$1:$A1;A1)>1

Ця формула перевіряє наявність дублікатів у стовпці A.

Як видалити дублікати в Google Sheets

В результаті до значень, що дублюються, застосовується виділення:

Як видалити дублікати в Google Sheets

Що, якщо ви хочете застосувати виділення до всього рядка?

Потрібно внести одну невелику зміну у формулу (виділено червоною), додавши знак $ перед кінцевим A:

=COUNTIF($A$1:$A1;$A1)>1

Тепер ваш результат буде виглядати наступним чином: весь рядок буде виділено:

Як видалити дублікати в Google Sheets

Крок 2. Це необов'язковий крок для видалення рядків, що дублюються. Додайте фільтри до набору даних, а потім відсортуйте за кольором, щоб зібрати рядки, що дублюються разом. Виділіть блок рядків, що дублюються, і видаліть його, от і всі справи.

Спосіб 5: Як видалити повтори даних у Google Sheets за допомогою Apps Script

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

Зразок програми-скрипту Apps: Як видалити повтори даних у Google Sheets

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

Вона працює таким чином: (скриншот, код або відео)

  • отримайте значення з діапазону даних Sheet1, використовуючи Apps Script;
  • перетворіть рядки масиву на рядки (блоки тексту) для порівняння;
  • відфільтруйте всі рядки, що дублюються;
  • перевірте, чи існує аркуш із дублікатами;
  • якщо існує, видаліть старі дані та вставте нові дубльовані дані;
  • якщо такого листа не існує, створіть новий лист і вставте в нього нові дубльовані дані;
  • додайте меню користувача для запуску з аркуша Google.

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

Як видалити дублікати в Google Sheets

А ось код Apps Script для цієї програми:

/** * remove duplicate rows from Google Sheets data range */ function removeDupRows() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Sheet1'); // зміна ряду номера вашого члена row var startRow = 7; // get the data var range = sheet.getRange(startRow,1,sheet.getLastRow(),sheet.getLastColumn()).getValues(); // remove duplicates with helper function var dedupRange = arrayUnique(range); Logger.log(dedupRange); // check if duplicate sheet exists already, if not create new one if (ss.getSheetByName('Sheet1 Duplicates Removed')) { // case when dedup sheet already exists var dedupSheet = ss.getSheetByName'''; var lastRow = Math.max(dedupSheet.getLastRow(),1); var lastColumn = Math.max(dedupSheet.getLastColumn(),1); // clear out any previous de-duplicate data dedupSheet.getRange(1,1,dedupSheet.getLastRow(),dedupSheet.getLastColumn()).clear(); // replace with new de-duplicated data dedupSheet.getRange(1,1,dedupRange.length,sheet.getLastColumn()).setValues(dedupRange); } else { // Case when there is no dedup sheet var dedupSheet = ss.insertSheet('Sheet1 Duplicates Removed',0); dedupSheet.getRange(1,1,dedupRange.length,dedupRange[0].length).setValues(dedupRange); } // make the de-duplicate sheet the active one dedupSheet.activate(); } /** * helper function returns unique array */ function arrayUnique(arr) { var tmp = []; // filter out duplicates return arr.filter(function(item, index){ // convert row arrays to strings for comparison var stringItem = item.toString(); // push string items в temporary arrays tmp.push(stringItem); // тільки перевірити першу передачу strings return tmp.indexOf(stringItem) >= index; }); }

Крім того, ви можете додати меню користувача, щоб запускати його з листа Google, а не з вікна редактора сценаріїв:

/** * add menu to run function from Sheet */ function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Remove duplicate rows') .addItem('Highlight duplicate rows','highlightDupRows') .addItem('Remove duplicate rows','removeDupRows') .addToUi(); }

Чим цей метод кращий?

  • можливе встановлення тригерів для запуску функції видалення дублікатів за певних умов (наприклад, щодня, при додаванні нових даних);
  • можна покращити контроль над вибором даних (тобто який лист, який діапазон тощо);
  • розглядайте всі стовпці чи ні щодо дублікатів;
  • кращий контроль над виведенням даних, ніж у інших методах

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

Найкраще в Apps Script це те, що він дозволяє вам дуже швидко створювати максимально життєздатні продукти, що підходять саме для вашої ситуації.

Як тільки ви освоїте Apps Script, вам потрібно всього 15-30 хвилин, щоб створити сценарії користувача, як, наприклад, цей для видалення дублікатів в Google Sheets.

Спосіб 6: Як видалити повтори даних у Google Sheets за допомогою надбудов

Додатки або надбудови — це спеціалізовані програми, які ви додаєте до Google Sheets, щоб отримати додаткову функціональність. Вони розробляються сторонніми розробниками, але проходять перевірку Google, перш ніж потрапити до каталогу надбудов.

Додавання надбудов у таблицю здійснюється через меню надбудов:

Як видалити дублікати в Google Sheets

Як ви помітите, тут є багато варіантів. Найкращою з них, яку я знайшов, є надбудова Remove Duplicates Add-On від Ablebits, яка є першою на цьому зображенні:

Як видалити дублікати в Google Sheets

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

Це додаток виділяє повтори даних у Google Sheets, видаляє повтори даних у Google Sheets і навіть може порівнювати два стовпці для того самого значення.

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

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

Ось GIF-зображення, що показує кроки для виділення повторів у комірках у вашому наборі даних:

Як видалити дублікати в Google Sheets

Тепер ви знаєте, як видалити повтори даних у Google Sheets за допомогою шести різних методів, тепер можна йти та очищати свої таблиці від повторів!

nBook
Додати коментар