Выделение дубликатов в Google Таблицах: условное форматирование против надстройки

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

И сегодня я постараюсь осветить для вас самые популярные кейсы. Вы будете выделять дубликаты в Google Таблицах не только условным форматированием (существуют разные формулы, основанные на разбросе дубликатов в вашей таблице), но и специальным дополнением.

Выделите повторяющиеся ячейки в одном столбце Google Sheets

Начнем с основного примера. Это когда у вас есть только один столбец с повторяющимися значениями:
Данные в один столбец с дубликатами.

Кончик. Сегодня я буду использовать условное форматирование во всех случаях, кроме последнего. Если вы не знакомы с ним, узнайте об этом в этом сообщении в блоге.

Чтобы выделить повторяющиеся ячейки в одном столбце Google Sheets, откройте условное форматирование и установите следующие параметры:

  1. применить правило к вашему диапазону ячеек — А2: А10 в моем примере
  2. выбирать Пользовательская формула из раскрывающегося списка с условием и введите следующую формулу:

    =СЧЁТЕСЛИ($A$2:$A$10,$A2)>1

    Примечание. Рядом с буквой стоит знак доллара А2. Это сделано намеренно, чтобы формула могла подсчитывать каждую ячейку из столбца A. В этой статье вы узнаете больше о ссылках на ячейки.

  3. выберите любой цвет из списка Стиль форматирования чтобы выделить эти дубликаты
  4. нажмите Готово

Эта формула COUNTIF просканирует ваш столбец A и сообщит правилу, какие записи появляются более одного раза. Все эти повторяющиеся ячейки будут окрашены в соответствии с вашими настройками:
Выделите дубликаты в одном столбце Google Sheets.

Выделите дубликаты в нескольких столбцах Google Sheets

Может случиться так, что повторяющиеся значения будут находиться более чем в одном столбце:
Несколько столбцов с повторяющимися значениями.

Как тогда сканировать и выделять дубликаты во всех трех столбцах Google Sheets? Использование условного форматирования, а также. Упражнение такое же, как и выше, с небольшими изменениями:

  1. Выбрать А2: С10 как диапазон для окрашивания повторяющихся ячеек внутри
  2. изменить диапазон для Пользовательская формула также:

    =СЧЁТЕСЛИ($A$2:$C$10;A2)>1

    Примечание. На этот раз удалите знак доллара с A2. Это позволит формуле подсчитать все вхождения каждой ячейки из таблицы, а не только из столбца A.

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

  3. выбрать цвет в Стиль форматирования раздел и нажмите Готово

Выделите все повторяющиеся ячейки во всех столбцах Google Sheets.

В отличие от вышеупомянутого COUNTIF, этот сканирует все 3 столбца и подсчитывает, сколько раз каждое значение из таблицы появляется во всех столбцах. Если более одного раза, условное форматирование выделит эти повторяющиеся ячейки в вашей таблице Google Sheets.

Выделите всю строку, если дубликаты находятся в одном столбце

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

Как видите, в столбце B есть дубликаты: макароны & приправа разделы встречаются дважды каждый.

В таких случаях вы можете рассматривать все эти строки как дубликаты. И вам может понадобиться выделить эти повторяющиеся строки в вашей электронной таблице Google.

Если это именно то, для чего вы здесь, обязательно установите их для условного форматирования:

  1. Применить правило к диапазону А2: С10
  2. И вот формула:

    =СЧЁТЕСЛИ($B$2:$B$10,$B2)>1

Выделите целые строки, если дубликаты находятся в одном столбце.

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

Выделение полных дубликатов строк в электронных таблицах

А что, если вся строка с записями во всех столбцах встречается в вашей таблице несколько раз?
Абсолютно повторяющиеся строки в вашей таблице.

Как вы проверяете все 3 столбца в таблице и выделяете абсолютные повторяющиеся строки в своей таблице Google?

Используя эту формулу в условном форматировании:

=СЧЕТЕСЛИ(Формуламассива($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1
Выделите абсолютные повторяющиеся строки в Google Sheet.

Давайте разобьем его на части, чтобы понять, как это работает:

  1. Формула массива ($A$2:$A$10&$B$2:$B$10&$C$2:$C$10) объединяет каждые 3 ячейки из каждой строки в одну текстовую строку, которая выглядит так: СпагеттиПаста9-RQQ-24

    Таким образом, в моем примере таких строк 9 — по одной на строку.

  2. Затем COUNTIFS принимает каждую строку (начиная с первой: $A2&$B2&$C2) и ищет его среди этих 9 строк.
  3. Если имеется более одной строки (>1), эти дубликаты выделяются.

Кончик. Вы можете узнать больше о COUNTIF и конкатенации в Google Таблицах в соответствующих статьях.

Выделите фактические дубликаты — экземпляры 2n, 3d и т. д.

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

Всего одним изменением в формуле вы сможете выделить эти «настоящие» повторяющиеся строки — не первые записи, а их 2-й, 3-й, 4-й и т. д. экземпляры.

Итак, вот формула, которую я предложил прямо выше для всех повторяющихся строк:

=СЧЕТЕСЛИ(Формуламассива($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1

И это формула, которая вам нужна, чтобы выделить только повторяющиеся экземпляры в Google Таблицах:

=СЧЕТЕСЛИ(Формуламассива($A$2:$A2&$B$2:$B2&$C$2:$C2),$A2&$B2&$C2)>1
Раскрашивайте фактические дубликаты с помощью условного форматирования Google Sheets.

Видите разницу в формуле?

Он находится в первом аргументе COUNTIF:

$A$2:$A2&$B$2:$B2&$C$2:$C2

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

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

Способ выделения дубликатов без формул — надстройка «Удалить дубликаты» для Google Таблиц

Конечно, у вас может быть какой-то другой вариант использования, требующий другой формулы. Тем не менее, любая формула и условное форматирование требуют обучения. Если вы не готовы посвящать этому свое время, есть более простое решение.

Надстройка «Удалить дубликаты» для Google Таблиц выделит дубликаты для вас.

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

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

Кроме того, он умеет выделять не только дубликаты, но и уникальные. И есть возможность игнорировать 1-е экземпляры:
Выберите тип данных, которые вы хотите найти.

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

Выделяйте дубликаты по расписанию с помощью надстройки

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

Вот двухминутное демонстрационное видео, подтверждающее мои слова (или см. ниже пару анимированных изображений):

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

Что еще лучше, вы можете запланировать автозапуск этих сценариев несколько раз в день:
Запуск сценария по расписанию.

Не беспокойтесь, для вас есть специальный журнал, в котором вы можете отслеживать все автоматические запуски и убедиться, что они работают правильно:
См. журнал всех автоматических запусков удаления повторяющихся строк.

Просто установить Удалить дубликаты из магазина Google Sheets, попробуйте его на своих данных, и вы увидите, сколько времени и нервов вы сэкономите на правильном окрашивании этих записей. Да, без всяких формул и всего в несколько кликов 😉

Видео: Как выделить дубликаты в Google Sheets

В этом 1,5-минутном видео показаны 3 самых быстрых способа (с формулами и без них) найти и выделить дубликаты в Google Таблицах. Вы увидите, как раскрасить 1 столбец или целые строки на основе дубликатов, даже автоматически.

Таблица с примерами формул

Выделение дубликатов в Google Sheets — примеры условного форматирования (сделать копию файла)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *