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

В этом уроке я покажу вам несколько простых способов выделить дубликаты в Google Таблицах .

Итак, приступим!

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

Наиболее распространенная ситуация — это когда у вас есть набор данных в столбце, и вы хотите быстро выделить дубликаты.

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

Ниже приведены шаги по выделению дубликатов в столбце:

  • Выберите набор данных names (без заголовков)
  • Выберите в меню опцию Формат.
  • В появившихся параметрах щелкните Условное форматирование. Это откроет панель правил условного формата справа.
  • Нажмите на опцию «Добавить другое правило».
  • Убедитесь, что диапазон (где нам нужно выделить дубликаты) правильный. Если это не так, вы можете изменить его в разделе «Применить к диапазону».
  • Щелкните раскрывающееся меню «Форматировать ячейки, если», а затем выберите параметр «Пользовательская формула есть».
  • В поле ниже введите следующую формулу: =COUNTIF($A$2:$A$10,A2)>1
  • В параметрах «Стиль форматирования» укажите форматирование, в котором вы хотите выделить повторяющиеся ячейки. По умолчанию он будет использовать зеленый цвет, но вы можете указать другие цвета, а также стили, такие как полужирный или курсив.
  • Нажмите Готово

Вышеупомянутые шаги выделят все ячейки с повторяющимися именами указанным цветом.

В условном форматировании замечательно то, что оно динамическое . Это означает, что если вы измените данные в любой из ячеек, форматирование обновится автоматически. Например, если вы удалите одно из имен, у которых есть дубликаты, выделение этого имени (в другой ячейке) исчезнет, ​​поскольку теперь оно стало уникальным.

Как это работает?

При использовании настраиваемой формулы в условном форматировании каждая ячейка проверяется по указанной формуле.

Если формула возвращает значение ИСТИНА для ячейки, она выделяется в указанном формате, а если она возвращает ЛОЖЬ, это не так.

В приведенном выше примере проверяется каждая ячейка, и если имя появляется в диапазоне более одного раза, для формулы СЧЁТЕСЛИ возвращается ИСТИНА, и ячейка выделяется. В остальном он остается без изменений.

Также обратите внимание, что я использовал диапазон $ A $ 2: $ A $ 10 (где перед алфавитом столбца и номером строки стоит знак доллара). Это действительно важно, так как гарантирует, что, когда формула переходит в следующую ячейку (в строке ниже), общий диапазон, который проверяется на количество имен, остается неизменным.

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

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

В приведенном выше примере у нас были все имена в одном столбце.

Но что, если имена находятся в нескольких столбцах (как показано ниже).

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

Ниже приведены шаги по выделению дубликатов в нескольких столбцах:

  • Выберите набор данных names (без заголовков)
  • Выберите в меню опцию Формат.
  • В появившихся параметрах щелкните Условное форматирование.
  • Нажмите на опцию «Добавить другое правило».
  • Убедитесь, что диапазон (где нам нужно выделить дубликаты) правильный. Если это не так, вы можете изменить его в разделе «Применить к диапазону».
  • Щелкните раскрывающееся меню «Форматировать ячейки, если», а затем выберите параметр «Пользовательская формула есть».
  • В поле ниже введите следующую формулу:
    =COUNTIF($A$2:$C$10,A2)>1

  • В параметрах «Стиль форматирования» укажите форматирование, в котором вы хотите выделить повторяющиеся ячейки. По умолчанию он будет использовать зеленый цвет, но вы можете указать другие цвета, а также стили, такие как полужирный или курсив.
  • Нажмите Готово

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

Как это работает?

Этот тоже работал последним.

В формуле СЧЁТЕСЛИ (COUNTIF) мы охватили все ячейки в трех столбцах. Таким образом, каждая ячейка в диапазоне проверяется с использованием указанной формулы и возвращает либо ИСТИНА, либо ЛОЖЬ.

Если есть имя, которое повторяется в любом из столбцов, оно будет выделено в указанном формате.

Опять же, обратите внимание, что я использовал диапазон $ A $ 2: $ C $ 10 (где перед алфавитом столбца и номером строки стоит знак доллара). Это действительно важно, так как гарантирует, что диапазон остается неизменным, в то время как условное форматирование проверяет количество имени в ячейке.

Выделите повторяющиеся строки / записи

Это немного сложно.

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

В этом случае запись будет дубликатом, если она имеет точно такое же значение в каждой ячейке в строке (например, в строках 2 и 7 в приведенном выше примере).

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

Но не волнуйтесь, это не так уж и сложно.

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

  • Выберите набор данных (без заголовков)
  • Выберите в меню опцию Формат.
  • В появившихся параметрах щелкните Условное форматирование.
  • Нажмите на опцию «Добавить другое правило».
  • Щелкните раскрывающееся меню «Форматировать ячейки, если», а затем выберите параметр «Пользовательская формула есть».
  • В поле ниже введите следующую формулу: =COUNTIF(ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1

  • В параметрах «Стиль форматирования» укажите форматирование, в котором вы хотите выделить повторяющиеся ячейки. По умолчанию он будет использовать зеленый цвет, но вы можете указать другие цвета, а также стили, такие как полужирный или курсив.
  • Нажмите Готово

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

Как это работает?

Этот работает так же, как наш первый пример (где мы просто выделили ячейки в столбце, в котором были дубликаты).

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

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

ARRAYFORMULA ($ A $ 2: $ A $ 10 и $ B $ 2: $ B $ 10 и $ C $ 2: $ C $ 10)

Этот массив используется в формуле Countif, и используемое условие снова представляет собой объединенную строку, которая имеет все значения в строке. Это делается с использованием следующих критериев:

$ A2 и $ B2 и $ C2

Теперь это преобразовано в простую конструкцию типа столбца, в которой функция COUNTIF проверяет, сколько раз эта объединенная строка повторяется в созданном нами массиве строк.

В результате будут выделены все повторяющиеся записи.

В Google Таблицах не выделяются дубликаты — возможные причины

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

Вот несколько возможных причин, по которым вы можете проверить:

Лишние места в камерах

Есть ли лишние пробелы (начальные или конечные пробелы) в тексте в одной ячейке, а не в другой?

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

Поэтому, даже если вы видите дубликат, он может не выделиться.

Чтобы избавиться от этого, вы можете использовать функцию TRIM (и функцию CLEAN), чтобы избавиться от всех лишних пробелов.

Неправильная ссылка

В Google Таблицах есть три разных типа ссылок.

  • Абсолютные ссылки (пример — $ A $ 1)
  • Относительные ссылки (пример — A1)
  • Смешанные ссылки (пример — A1 или A $ 1)

Если формула требует одного типа ссылки, а вы в конечном итоге используете другие, у вас, скорее всего, возникнет проблема.

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

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

Надеюсь, вы нашли этот урок полезным!

Рекомендуем! Пройти очень эффективный Онлайн-курс «Гугл-таблицы» и «Excel» для начинающих.