Содержание
Как выделить дубликаты в Google Таблицах (шаг за шагом)
Содержание:
При работе с данными в Google Таблицах рано или поздно вы столкнетесь с проблемой дублирования данных. Это могут быть повторяющиеся данные в одном столбце или повторяющиеся строки в наборе данных. Приложив немного условного форматирования, вы можете легко выделить дубликаты в Google Таблицах. После того, как вы их выделите, вы можете решить, сохранить их или удалить.
В этом уроке я покажу вам несколько простых способов выделить дубликаты в Google Таблицах .
Итак, приступим!
Выделите повторяющиеся ячейки в столбце
Наиболее распространенная ситуация — это когда у вас есть набор данных в столбце, и вы хотите быстро выделить дубликаты.
Например, предположим, что у вас есть набор данных, показанный ниже, где вы хотите выделить все имена, повторяющиеся в столбце A.
- Выберите набор данных names (без заголовков)
- Выберите в меню опцию Формат.
- В появившихся параметрах щелкните Условное форматирование. Это откроет панель правил условного формата справа.
- Нажмите на опцию «Добавить другое правило».
- Убедитесь, что диапазон (где нам нужно выделить дубликаты) правильный. Если это не так, вы можете изменить его в разделе «Применить к диапазону».
- Щелкните раскрывающееся меню «Форматировать ячейки, если», а затем выберите параметр «Пользовательская формула есть».
- В поле ниже введите следующую формулу: =COUNTIF($A$2:$A$10,A2)>1
- В параметрах «Стиль форматирования» укажите форматирование, в котором вы хотите выделить повторяющиеся ячейки. По умолчанию он будет использовать зеленый цвет, но вы можете указать другие цвета, а также стили, такие как полужирный или курсив.
- Нажмите Готово
Вышеупомянутые шаги выделят все ячейки с повторяющимися именами указанным цветом.
В условном форматировании замечательно то, что оно динамическое . Это означает, что если вы измените данные в любой из ячеек, форматирование обновится автоматически. Например, если вы удалите одно из имен, у которых есть дубликаты, выделение этого имени (в другой ячейке) исчезнет, поскольку теперь оно стало уникальным.
Как это работает?
При использовании настраиваемой формулы в условном форматировании каждая ячейка проверяется по указанной формуле.
Если формула возвращает значение ИСТИНА для ячейки, она выделяется в указанном формате, а если она возвращает ЛОЖЬ, это не так.
В приведенном выше примере проверяется каждая ячейка, и если имя появляется в диапазоне более одного раза, для формулы СЧЁТЕСЛИ возвращается ИСТИНА, и ячейка выделяется. В остальном он остается без изменений.
Также обратите внимание, что я использовал диапазон $ A $ 2: $ A $ 10 (где перед алфавитом столбца и номером строки стоит знак доллара).
Это действительно важно, так как гарантирует, что, когда формула переходит в следующую ячейку (в строке ниже), общий диапазон, который проверяется на количество имен, остается неизменным.
Если вы хотите удалить выделенные ячейки, вам необходимо удалить условное форматирование. Для этого выберите ячейки, к которым применено форматирование, щелкните параметр «Формат», щелкните «Условное форматирование» и удалите правило из панели, которая открывается справа.
Выделите повторяющиеся ячейки в нескольких столбцах
В приведенном выше примере у нас были все имена в одном столбце.
Но что, если имена находятся в нескольких столбцах (как показано ниже).
Вы по-прежнему можете использовать условное форматирование, чтобы выделить повторяющиеся имена (которые могут быть именем, которое встречается более одного раза во всех трех столбцах, вместе взятых.
Ниже приведены шаги по выделению дубликатов в нескольких столбцах:
Вышеупомянутые шаги будут выделять ячейку, если имя появляется более одного раза во всех трех выбранных столбцах вместе.
Этот тоже работал последним.
В формуле СЧЁТЕСЛИ (COUNTIF) мы охватили все ячейки в трех столбцах. Таким образом, каждая ячейка в диапазоне проверяется с использованием указанной формулы и возвращает либо ИСТИНА, либо ЛОЖЬ.
Если есть имя, которое повторяется в любом из столбцов, оно будет выделено в указанном формате.
Опять же, обратите внимание, что я использовал диапазон $ A $ 2: $ C $ 10 (где перед алфавитом столбца и номером строки стоит знак доллара). Это действительно важно, так как гарантирует, что диапазон остается неизменным, в то время как условное форматирование проверяет количество имени в ячейке.
Выделите повторяющиеся строки / записи
Это немного сложно.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите выделить все повторяющиеся записи.
В этом случае запись будет дубликатом, если она имеет точно такое же значение в каждой ячейке в строке (например, в строках 2 и 7 в приведенном выше примере).
Причина, по которой это немного сложно, заключается в том, что теперь вам не нужно проверять отдельные ячейки. Вы должны проверить всю строку и выделить только те строки, в которых повторяются все ячейки.
Но не волнуйтесь, это не так уж и сложно.
Ниже приведены шаги по выделению повторяющихся строк с использованием условного форматирования:
Вышеупомянутые шаги выделят все записи, которые повторяются в наборе данных (как показано ниже).
Этот работает так же, как наш первый пример (где мы просто выделили ячейки в столбце, в котором были дубликаты).
Но поскольку есть целая строка, которую нам нужно сравнить со всеми другими строками, мы объединили содержимое всех строк и создали одну строку для каждой строки.
Следующая часть формулы создает массив строк, в котором объединено все содержимое ячеек в строке (выполняется конкатенация с использованием знака амперсанда).
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 Таблицах с помощью условного форматирования.
Надеюсь, вы нашли этот урок полезным!
Как выделить дубликаты в Google Sheets
При работе с данными в электронных таблицах, скорее всего, у вас есть дубликаты данных. Особенно большая таблица.
В Google Sheets есть встроенная функция, которая удаляет дубликаты, но что, если вы хотите выделить только дублирующиеся данные? К счастью, в Google Sheets легко выделить дубликаты, используя несколько различных методов.
Как найти дубликаты в Google Sheets
Первый способ сделать это — выделить дубликаты цветом. Вы можете искать дубликаты по столбцу и автоматически выделять их, заполняя ячейки или изменяя цвет текста.
Перед началом убедитесь, что ваша электронная таблица содержит данные, организованные по столбцам, и каждый столбец имеет заголовок.
Откройте таблицу, которую вы хотите проанализировать в Google Sheets.
Выделите столбец, который вы хотите найти.
Нажмите Формат > Условное форматирование . Условное форматирование откроется меню справа.
Убедитесь, что диапазон ячеек — это то, что вы выбрали на шаге 2.
В раскрывающемся списке « Формат ячеек, если …» выберите « Пользовательская формула» . Новое поле появляется под ним.
Введите следующую формулу в новом поле, корректируя буквы для выбранного диапазона столбцов:
= СЧЕТЕСЛИ (А: А, А1)> 1
В разделе Стиль форматирования выберите цвет заливки для дубликатов ячеек. В этом примере мы выбрали красный.
Вы также можете изменить цвет текста в дублирующих ячейках вместо того, чтобы заполнить его цветом. Для этого щелкните значок цвета текста ( A в строке меню) и выберите свой цвет.
Нажмите Готово, чтобы применить условное форматирование. У всех дубликатов теперь должна быть красная ячейка.
Найти дубликаты в Google Sheets с формулами
Вы также можете использовать формулу, чтобы найти дубликаты данных в ваших электронных таблицах.
Этот метод может работать по столбцам или по строкам и отображать повторяющиеся данные в новом столбце или листе в вашем файле.
Найти дубликаты в столбцах с формулой
Поиск дубликатов в столбцах позволяет исследовать один столбец данных, чтобы увидеть, есть ли что-нибудь в этом столбце, который был продублирован.
Откройте таблицу, которую вы хотите проанализировать.
Нажмите на открытую ячейку на том же листе (например, следующий пустой столбец на листе).
В этой пустой ячейке введите следующее и нажмите Enter .
= UNIQUE
Функция формулы активирована.
Выберите столбец, в котором вы хотите найти дубликаты, нажав на букву в верхней части столбца. Формула автоматически добавит диапазон столбцов для вас. Ваша формула будет выглядеть примерно так:
= Unique (С2: С25)
Введите закрывающую скобку в ячейку формулы (или нажмите Enter ), чтобы завершить формулу.
Уникальные данные отображаются в этом столбце для вас, начиная с ячейки, в которой вы ввели формулу.
Найти повторяющиеся строки с помощью формулы
Метод поиска повторяющихся строк в электронной таблице аналогичен, за исключением того, что диапазон ячеек, выбранных для анализа по формуле, отличается.
Откройте таблицу, которую вы хотите проанализировать.
Нажмите на открытую ячейку на том же листе (например, следующий пустой столбец на листе).
В этой пустой ячейке введите следующее и нажмите Enter .
= UNIQUE
Функция формулы активирована.
Выберите строки, которые вы хотите проанализировать на наличие дубликатов.
Нажмите Enter, чтобы завершить формулу. Двойные строки отображаются.
Найти дубликаты в Google Sheets с помощью дополнения
Вы также можете использовать надстройку Google для поиска и выделения дубликатов в Google Sheets.
Эти дополнения позволят вам делать больше с вашими дубликатами, например, идентифицировать и удалять их; сравнить данные по листам; игнорировать строки заголовка; автоматическое копирование или перемещение уникальных данных в другое место; и более.
Если вам необходимо разрешить любую из этих ситуаций или если ваш набор данных более надежен, чем три столбца, рассмотрите возможность использования одного из следующих дополнений.
- Удалить дубликаты по Ablebits
- Электроинструменты от Ablebits
- Удалить дубликаты Expand9
Каждое из этих дополнений ведет себя немного по-разному, но все они позволяют вам находить и выделять дублирующиеся данные, копировать дублирующиеся данные в другое место и удалять дублирующиеся значения или удалять дублирующиеся строки.
Найти повторяющиеся ячейки в справке Google Sheets
Этот дополнительный инструмент для удаления дубликатов поможет вам отследить все повторяющиеся или уникальные ячейки в ваших Google Sheets.
- Перед началом работы
- Как использовать Удаление повторяющихся ячеек
- Начать удаление повторяющихся ячеек
- Шаг 1: Выберите диапазон
- Шаг 2: Решите, что найти
- Шаг 3. Настройте дополнительные параметры
- Посмотреть результат
Прежде чем начать
Мы заботимся о ваших данных и рекомендуем вам всегда создавать резервные копии ваших электронных таблиц. Специальная опция дополнения сделает это за вас, если вы выберете ее.
Имейте в виду, что Google Таблицы ограничивают общее количество возможных ячеек в вашем файле до 5 миллионов ячеек.
Наконечник. Используйте это дополнение, чтобы найти повторяющиеся или уникальные ячеек в одной электронной таблице.
Если вам нужно искать идентичные строки , а не отдельные ячейки, у нас есть другие инструменты для этого: Поиск повторяющихся или уникальных строк и это одношаговая версия Quick Dedupe.
Чтобы сравнить два листа или таблицы на наличие дубликатов или уникальных элементов, воспользуйтесь инструментом «Сравнить столбцы или листы».
Чтобы объединить или объединить уникальные записи, которые ссылаются на несколько повторяющихся значений в разных строках, выберите «Объединить повторяющиеся строки».
Как использовать Удаление дубликатов ячеек
Запустите надстройку
Используйте меню Google Таблиц и перейдите в Расширения > Удалить дубликаты > Найти повторяющиеся или уникальные ячейки :
Совет. Или откройте утилиту из группы Dedupe & Compare коллекции Power Tools:
Шаг 1: Выберите диапазон
Выберите диапазон ячеек, которые вы хотите проверить на наличие дубликатов или уникальных элементов:
Вы увидите весь используемый диапазон выделен по умолчанию.
Настройте его в соответствии с вашими потребностями, щелкнув значок Выберите диапазон и введя другой диапазон вручную или выбрав интересующую вас электронную таблицу:
Совет.
Используйте Автоматический выбор Кнопка для автоматического выбора всего используемого диапазона (до первого пустого столбца и строки).
Чтобы на всякий случай сохранить нетронутой копию электронной таблицы, выберите вариант Создать резервную копию листа .
Нажмите Далее , чтобы перейти ко второму шагу.
Шаг 2: Решите, что искать
Выберите тип данных, которые вы хотите найти в электронной таблице:
- Выберите Дубликаты из выпадающего списка, чтобы искать только дубликаты, без их первых экземпляров:
- Выберите Дубликатов + 1-е вхождение , если вы хотите увидеть в результате все дубликаты, включая их первые экземпляры:
- Перейдите с Uniques , чтобы найти только те значения, которые не имеют повторений в выбранном диапазоне:
- Переключитесь на Уникальные + 1-е вхождения для поиска как уникальных, так и первых вхождений дубликатов:
Отметьте опцию Пропускать пустые ячейки и модуль исключит из поиска все пустые ячейки.
В противном случае они будут рассматриваться как уникальные или повторяющиеся, в зависимости от их частоты в ваших данных.
Если у вас есть одни и те же записи, записанные в разных регистрах, и вы хотите, чтобы они имели разные значения, отметьте параметр Соответствовать также регистру .
Вы можете вернуться к шагу 1, нажав Назад .
Нажмите Далее , чтобы перейти к последнему шагу.
Шаг 3: Настройте дополнительные параметры
Дополнение предлагает 4 способа обработки найденных повторяющихся или уникальных ячеек:
Выберите переключатель рядом с действием, которое вы хотите применить:
- Принять решение Заполнить цветом , чтобы закрасить ячейки с найденными значениями. Стрелка рядом с опцией покажет оттенки на выбор.
- Чтобы работать с найденными ячейками на новом листе, электронной таблице или в выбранном вами месте без потери значений из исходных листов, выберите опцию Скопировать в другое место .
- В качестве альтернативы записи можно вырезать из исходного листа и вставить в указанное вами место, если вы решите Переместить в другое место .
- Параметр Очистить значения позволяет полностью удалить повторяющиеся или уникальные значения из электронной таблицы, сохраняя при этом другие данные как есть.
Щелкните Готово , чтобы найти повторяющиеся или уникальные значения.
Посмотреть результат
После того, как надстройка завершит поиск, вы увидите сводку результата с количеством найденных значений и примененным действием:
Совет. Нажмите кнопку Start over , чтобы снова использовать надстройку.
Как выделить дубликаты в Google Sheets [шаг за шагом]
Дублирование данных — это проклятие электронных таблиц, особенно в больших масштабах. Учитывая объем и разнообразие данных, которые в настоящее время вводят команды, вполне возможно, что дублирование данных в таких инструментах, как Google Sheets, может быть актуальным и необходимым, или это может раздражать и отвлекать от основной цели работы с электронными таблицами.
Потенциальная проблема поднимает хороший вопрос: как выделить дубликаты в Google Sheets?
Мы подготовили для вас пошаговый обзор того, как выделить дубликаты в Google Таблицах, а также изображения, чтобы убедиться, что вы на правильном пути, когда дело доходит до дедупликации ваших данных.
Выделение повторяющихся данных в Google Sheets
Google Sheets — это бесплатная облачная альтернатива проприетарным программам для работы с электронными таблицами, которая — неудивительно, поскольку мы имеем дело с Google — предлагает множество замечательных функций, помогающих оптимизировать данные ввод, форматирование и расчеты.
Google Таблицы имеют все знакомые функции: «Файл», «Редактировать», «Просмотр», «Формат», «Данные», «Инструменты» и т. д., а также позволяют легко и быстро вводить данные, добавлять формулы для расчетов и находить ключевые взаимосвязи. Однако в Sheets нет простого способа найти и выделить дубликаты.
В то время как другие инструменты для работы с электронными таблицами, такие как Excel, имеют встроенные инструменты условного форматирования, которые могут точно определить повторяющиеся данные в вашей таблице, решение Google требует немного больше ручных усилий.
Пошаговое руководство: как выделить дубликаты в Google Sheets (с картинками)
Так как же автоматически выделить дубликаты в Google Sheets? Хотя для этой цели нет встроенного инструмента, вы можете использовать некоторые встроенные функции для выделения повторяющихся данных.
Пошаговое руководство:
Шаг 1. Откройте электронную таблицу.
Шаг 2: Выделите данные, которые хотите проверить.
Шаг 3: В разделе «Формат» выберите «Условное форматирование».
Шаг 4. Выберите «Пользовательская формула».
Шаг 5: Введите пользовательскую формулу проверки дубликатов.
Шаг 6: Нажмите «Готово», чтобы увидеть результаты.
Шаг 1. Откройте электронную таблицу.
Сначала перейдите в Google Таблицы и откройте электронную таблицу, которую вы хотите проверить на наличие повторяющихся данных.
Шаг 2: Выделите данные, которые хотите проверить.
Затем щелкните левой кнопкой мыши и перетащите курсор на данные, которые вы хотите проверить, чтобы выделить их.
Шаг 3. В разделе «Формат» выберите «Условное форматирование».
Теперь перейдите в «Формат» в верхней строке меню и выберите «Условное форматирование». Вы можете получить уведомление о том, что «ячейка не пуста» — если это так, нажмите на нее, и вы должны увидеть это:
Шаг 4: Выберите «Пользовательская формула».
Далее нам нужно создать пользовательскую формулу. В разделе «Форматировать ячейки, если» выберите раскрывающееся меню и прокрутите вниз до «Пользовательская формула».
Шаг 5: Введите пользовательскую формулу проверки дубликатов.
Для поиска повторяющихся данных нам нужно ввести пользовательскую формулу проверки дубликатов, которая для нашего столбца данных выглядит следующим образом:
=СЧЁТЕСЛИ(A:A,A1)>1 текстовая строка, которая появляется более одного раза в нашем выбранном наборе данных и по умолчанию будет выделена зеленым цветом. Если вы предпочитаете другой цвет, щелкните значок маленького горшка с краской на панели стилей форматирования и выберите нужный цвет.
Шаг 6: Нажмите «Готово», чтобы увидеть результаты.
И вуаля — мы выделили повторяющиеся данные в Google Таблицах.
Как выделить дубликаты в нескольких строках и столбцах
Если вам нужно проверить большой набор данных, также можно выделить дубликаты данных в нескольких столбцах или строках.
Этот процесс начинается так же, как описанный выше процесс проверки дубликатов, с той лишь разницей, что вы меняете диапазон данных, чтобы включить все ячейки, которые вы хотите сравнить.
На практике это означает ввод расширенного диапазона данных в меню правил условного формата и поле пользовательского формата. Давайте используем приведенный выше пример в качестве отправной точки, но вместо того, чтобы просто искать дубликаты в столбце A, мы будем искать в трех столбцах: A, B и C, а также в строках 1-10.
Когда мы вводим наши правила условного формата, Применить к диапазону становится A1:C10, а наша пользовательская формула становится: ))>1
При этом будут выделены все дубликаты во всех трех столбцах и во всех 10 строках, что упрощает обнаружение двойников данных:
Работа с дубликатами в
Дубликаты в Гугл Таблицы? Абсолютно.