Как удалить дубликаты в 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 с помощью инструмента «Удалить повторы»

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

После того, как вы нажмете на кнопку «Удалить повторы данных», вы будете должны выбрать, в каком именно диапазоне ячеек искать повторы.

Затем повторы данных будут удалены, и вам будет представлен сводный отчет, в котором будет указано, сколько дубликатов было удалено

Способ 2: Как удалить повторы данных в Google Sheets с помощью формул

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

Этот метод удаляет повторы данных в выбранном вами диапазоне данных.

При определении дубликатов учитываются все столбцы диапазона данных. Другими словами, он сравнивает каждую строку данных и удаляет все строки, которые дублируют друг друга (идентичными всем другим по всему ряду).

Это очень просто реализовать, поскольку для этого используется одна формула с одним аргументом — диапазон, который вы хотите очистить от дубликатов (де-дуплицировать)

=UNIQUE(A1:D11)

Вот пример функции UNIQUE в действии. Функция находится в ячейке F1 и ищет повторы данных в диапазоне данных A1:D11

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

2.2. Выделение дубликатов значений с помощью COUNTIF

Этот метод сначала выделяет повторы данных в вашем наборе данных.

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

Затем используйте эту формулу в ячейке B2, чтобы выделить повторы данных в столбце A:

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

Обратите внимание, что диапазон — A$2:A2. Знак $ здесь ключевой, потому что он фиксирует диапазон в верхней части столбца. Иногда мы хотим зафиксировать ячейку, чтобы она не менялась при копировании формулы — для этого используется абсолютная адресация. Чтобы указать программе, что ссылку не нужно менять, используется абсолютная адресация — со знаком «$»:

Если данные в ячейке появляются в указанном диапазоне один раз и не повторяются — вернётся значение ЛОЖЬ. Если два раза и более — вернётся значение функции ИСТИНА.

Останется только выделить значения TRUE (повторы данных) и удалить их.

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

2.3. Как удалить повторы данных в Google Sheets с помощью формул IF

Именно так я впервые научился находить повторы данных и удалять их.

Это работает путем сортировки данных и сравнения соседних строк. Поначалу это довольно сложно, поскольку формулы немного запутаны. Однако, как только вы привыкнете, все становится просто, и это дает вам преимущество — вы сможете разобраться с данными. Вы увидите повторы данных вблизи и сможете выяснить, почему они возникают.

Это включает в себя использование формулы IF для проверки того, равно ли значение в текущей строке значению в предыдущей строке. Если они равны, то это дублирующая запись. Дублирующим записям присваивается значение 1, а уникальным (не дублирующим) — 0. Таким образом, вы можете легко подсчитать количество дубликатов (путем суммирования столбца).

Вот изображение, показывающее этот метод в действии. Формулы мы разберём чуть позже:

В данном примере для целей дублирования учитывается только первый столбец — номер счета.

Это означает, что если две строки имеют одинаковый номер счета, то они будут помечены как повторы данных, независимо от того, отличаются ли другие точки данных, например, дата.

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

Следующий шаг — выделение дублирующихся пар:

Это делается для того, чтобы вы могли сравнить уникальную запись и дублирующие записи бок о бок и исследовать, что происходит, прежде чем слепо удалять их.

Последний шаг — удаление дублирующихся строк.

Вот весь процесс, объясненный более подробно:

Шаг 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.

Вот пример:

Способ 3: Как удалить повторы данных в Google Sheets с помощью сводных таблиц

Сводные таблицы чрезвычайно полезны для анализа данных. Это отличный инструмент для поиска дубликатов в Google Sheets. Они очень гибкие и быстрые в использовании, поэтому с них стоит начать, если вы не уверены, есть ли в ваших данных повторы данных.

Шаг 1. Выделите свой набор данных и создайте сводную таблицу (в меню «Данные»). Откроется новая вкладка с редактором сводных таблиц.

В разделе ROWS (строки) выберите тот столбец, который вы хотите проверить на наличие дубликатов (например, номер счета). Затем в VALUES (значения) выберите другой столбец (я часто использую один и тот же) и убедитесь, что он настроен на суммирование по функциям COUNT (СЧЁТ) или COUNTA (СЧЁТЗ) (если ваш столбец содержит текст), как показано ниже:

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

Можно заметить, что повторы значений в ячейках имеют значение больше единицы. По этому показателю вы определяете повторы данных и действуете, исходя из ситуации. Как понимаете, данный метод подходит для тех людей, которые не хотят удалять все повторы, а изучить каждый конкретный случай в отдельности.

Способ 4: Как удалить повторы в Google Sheets с помощью условного форматирования

Этот метод состоит из двух шагов: 1) выделение дубликатов в Google Sheets и 2) удаление выделенных дубликатов (необязательно).

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

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

Эта формула проверяет наличие дубликатов в столбце A.

В результате к дублирующимся значениям применяется выделение:

Что если вы хотите применить выделение ко всей строке?

Нужно внести одно небольшое изменение в формулу (выделено красным), добавив знак $ перед конечным A:

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

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

Шаг 2. Это необязательный шаг для удаления дублирующихся строк. Добавьте фильтры к набору данных, а затем отсортируйте по цвету, чтобы собрать дублирующиеся строки вместе. Выделите блок дублирующихся строк и удалите его, вот и все дела.

Способ 5: Как удалить повторы данных в Google Sheets с помощью Apps Script

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

Образец программы-скрипта Apps: Как удалить повторы данных в Google Sheets

Эта программа удаляет повторы данных из набора данных в Листе 1. Она очень специфична для листа и диапазона данных, но ее легко создать и модифицировать.

Она работает следующим образом: (скриншот, код или видео)

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

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

А вот код Apps Script для этой программы:

/**
* remove duplicate rows from Google Sheets data range
*/
function removeDupRows() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  
  // change the row number of your header 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('Sheet1 Duplicates Removed');
    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 into temporary arrays
    tmp.push(stringItem);
    
    // only return the first occurrence of the strings
    return tmp.indexOf(stringItem) >= index;
    
  });
}

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

/**
* add menu to run function from Sheet
*/
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Remove duplicates')
  .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, прежде чем попасть в каталог надстроек.

Добавление надстроек в таблицу осуществляется через меню надстроек:

Как вы заметите, здесь много вариантов. Лучшая из них, которую я нашел, это надстройка Remove Duplicates Add-On от Ablebits, которая является первой на этом изображении:

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

Это дополнение выделяет повторы данных в Google Sheets, удаляет повторы данных в Google Sheets и даже может сравнивать два столбца для одного и того же значения.

Оно очень простое в использовании и очень удобно, если вы часто работаете с повторами данных или не уверены, где или почему в ваших данных есть повторы данных. Прежде чем удалить их, вы можете исследовать, в каких строках данных есть повторы данных, и посмотреть, можно ли выявить какие-либо проблемы.

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

Вот GIF-изображение, показывающее шаги для выделения повторов в ячейках в вашем наборе данных:

Теперь вы знаете, как удалить повторы данных в Google Sheets с помощью шести различных методов, теперь можно идти и очищать свои таблицы от повторов!