Как найти совпадения в гугл таблице

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В формуле СЧЁТЕСЛИ (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 Таблицы по-прежнему не выделяют дубликаты.

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

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

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

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

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

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

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

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

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

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

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

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

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

You can use one of three options to find duplicates: color, formulas, or add-ons.

Updated on September 16, 2022

What to Know

• Highlight a column. Select Format > Conditional Formatting. Select Custom formula is in the Format cells if menu.
• Then, enter =countif(A:A,A1)>1 (adjust the letters for the chosen column range). Choose a color in the Formatting Style section.
• Other methods: Use the UNIQUE formula or an add-on.

This article explains how to highlight duplicates in Google Sheets using three methods.

How to Find Duplicates in Google Sheets Columns

One way to identify duplicates is to highlight them with color. You can search by column for duplicates and automatically highlight them, either by filling the cells with color or changing the text color. 

1. Open the spreadsheet you want to analyze in Google Sheets.

2. Make sure the spreadsheet has data organized by columns and each column has a heading.

3. Highlight the column you want to search through.

4. Click Format > Conditional Formatting. The Conditional Formatting menu opens on the right.

5. Confirm the cell range is what you selected in Step 2.

6. In the Format cells if drop-down menu, select Custom formula is. A new field appears below it.

7. Enter the following formula in the new field, adjusting the letters for the column range you selected:

   =countif(A:A,A1)>1
   
   

8. In the Formatting style section, choose a fill color for the duplicate cells. In this example, we’ve chosen red.

   Alternatively, you could change the text color in the duplicate cells instead of filling it with a color. To do this, select the text color icon (the A in the menu bar) and choose your color.

9. Select Done to apply the conditional formatting. All duplicates should now have a red-filled cell.

Find Duplicates With Formulas

You can also use a formula to find the duplicate data in your spreadsheets. This method can work by column or by row and displays the duplicate data in a new column or sheet within your file.

Find Duplicates in Columns With a Formula

Finding duplicates in columns let you examine a single column of data to see if there is anything in that column that has been duplicated.

1. Open the spreadsheet you wish to analyze.

2. Click into an open cell in the same sheet (for example, the next empty column in the sheet).

3. In that empty cell, enter the following and then press Enter.

   =UNIQUE
   
   

   The formula feature is activated.

4. Select the column you want to find duplicates in by clicking the letter at the top of the column. The formula will automatically add the column range for you. Your formula will look something like this:

   =UNIQUE(C2:C25)
   
   

5. Type the closing parenthesis in the formula cell (or press Enter) to complete the formula.

6. The unique data is displayed in that column for you, starting in the cell where you entered the formula. 

Find Duplicate Rows Using a Formula

The method to find duplicate rows in your spreadsheet is similar, except the range of cells you select to analyze by the formula is different.

1. Open the spreadsheet you wish to analyze.

2. Click into an open cell in the same sheet (for example, the next empty column in the sheet).

3. In that empty cell, enter the following and then press Enter.

   =UNIQUE
   
   

   The formula feature is activated.

4. Select the rows you wish to analyze for duplicates.

5. Press Enter to complete the formula. The duplicate rows are displayed.

Find Duplicates With a Google Add-On

You can also use a Google add-on to find and highlight duplicates in Google Sheets. These add-ons will let you do more with your duplicates, such as identify and delete them; compare data across sheets; ignore header rows; automatically copying or moving unique data to another location; and more. 

If you need to address any of these situations or if your data set is more robust than three columns, download Remove Duplicates by Ablebits or a similar app that allows you to find and highlight your duplicate data, copy the duplicate data to another location, and clear duplicate values or delete duplicate rows.

FAQ

• How do I remove duplicates in Google Sheets?

  To remove duplicates in Google Sheets, open a spreadsheet and highlight a data range, then go to Data > Data Cleanup > Remove Duplicates.

• How do I compare different Google spreadsheets for duplicates?

  Install Ablebit’s Remove Duplicates add-on for Google Sheets and use the Compare Columns or Sheets tool. Go to Extensions > Remove Duplicates > Compare columns or sheets.

Thanks for letting us know!

Get the Latest Tech News Delivered Every Day

Subscribe

Функция COUNTIF и функция очистки данных — два самых простых способа найти дубликаты в Google Sheets.

О Google Таблицах

Google Sheets — это бесплатное облачное приложение для работы с электронными таблицами, предлагаемое Google как часть полного офисного пакета для конкуренции с Microsoft Office. Google Таблицы могут подготавливать данные, выполнять расчеты и многое другое.

Он используется для изменения, организации и хранения данных. Пакет производительности Google Docs включает эту программу, состоящую из Google Drive, Google Sheets и Google Slides.

Однако одним из преимуществ Google Sheets является то, что, в отличие от Excel, его можно использовать совместно с другими пользователями в режиме реального времени.

Google Таблицы упростили создание баз данных, предлагая удобный интерфейс и практичные инструменты для организации данных.

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

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

Как Google Таблицы помогают создавать базы данных проще?

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

Для управления и хранения данных в режиме реального времени большинство предприятий используют его вместо других дорогостоящих баз данных, таких как PostgreSQL, MySQL, SQL Server и т. д. Большинство крошечных наборов данных предпочитают таблицы Google в качестве альтернативы.

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

Важно отметить, что он имеет несколько сложных функций, доступных бесплатно, таких как API Google Sheets, скрипт приложений, триггеры форм, триггеры с таймером и ImportXML.

Пользователи сценария приложения могут подключаться к различным базам данных, поскольку сценарий приложения включает службу JDBC для подключения к MySQL, Microsoft SQL Server и другим базам данных.

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

Теперь давайте обсудим влияние повторяющихся значений в базе данных.

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

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

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

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

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

Общая формула, используемая для выделения дубликатов:

=COUNTIF(range, criterion)

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

Перейдите к опции «Формат» в верхнем меню и прокрутите вниз до «Условное форматирование», как показано ниже.

Новые параметры условного форматирования появятся справа после нажатия кнопки условного форматирования, как показано ниже:

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

В это поле введите формулу:

=COUNTIF($G$6:$G$14,G6)>1

Ячейка G должна быть заменена соответствующим столбцом в зависимости от вашей потребности выделить дублирование.

После нажатия кнопки «Готово» дубликаты из столбца будут выделены, как показано на изображении выше.

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

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

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

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

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

Чтобы лучше понять это, давайте использовать один пример.

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

Диапазон ячеек указывается в разделе «Применить к диапазону».

В разделе «Правила форматирования» формула, используемая для выделения дубликатов из нескольких столбцов, выглядит следующим образом:

=COUNTIF($F$6:$I$8,F6)>1

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

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

На этой иллюстрации мы также изменили цвет подсветки на синий.

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

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

В таблице Google идентификация повторяющихся строк данных немного отличается от выделения повторяющихся столбцов.

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

Возьмем один пример.

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

В разделе «Пользовательская формула» введена формула

=COUNTIF(ARRAYFORMULA($A$3:$A$10&$B$3:$B$10&$C$3:$C$10),$A3&$B3&$C3)>1

Обратите внимание, что хотя формула кажется сложной, она состоит из начальных и конечных номеров ячеек строк, в которых вы хотите выделить дубликаты.

Результат будет отображаться, как показано ниже, после запуска этой формулы.

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

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

Если вы хотите выделить дубликаты на основе определенных критериев, вам нужно использовать оператор звездочки («*»), чтобы указать функции СЧЁТЕСЛИ связать оба заданных диапазона и отобразить результат.

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

=(COUNTIF(Range,Criteria)>1) * (New Condition) )

Давайте возьмем один пример, чтобы понять это.

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

Давайте посмотрим, как это сделать в Google Sheet. За исключением изменения формулы, все этапы такие же, как описано выше в других подходах, так что давайте перейдем непосредственно к этому.

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

=COUNTIF(ArrayFormula($A$3:$A3&$B$3:$B3&$C$3:$C3),$A3&$B3&$C3)>1

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

На приведенном ниже листе Google видно, что первые записи Apple и Orange пропущены, а последние выделены.

До этого момента мы видели, как выделять повторяющиеся данные в строках, столбцах и с определенными критериями.

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

Ярлык для удаления дубликатов

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

Вы увидите всплывающее окно для выбора альтернатив и подтверждения удаления дубликатов после нажатия «Удалить дубликаты», как показано на рисунке ниже.

Существует выбор «Данные в виде строки заголовка», как показано на изображении ниже. Установите этот флажок, если на вашем листе есть строка заголовка, чтобы Google Sheet игнорировал ее при удалении дубликатов.

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

Как создать уникальный список?

Давайте извлечем отдельный список из базы данных с многочисленными дубликатами.

Формула невероятно проста для понимания и применения.

Чтобы понять это, давайте возьмем одну из наших ранних таблиц Google.

Синтаксис для создания уникального списка:

=unique(range start: range end)

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

Как удалить пробелы в Google Таблицах?

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

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

Давайте воспользуемся приведенной ниже таблицей Google, чтобы устранить лишние пробелы.

Выберите столбец или набор данных с дополнительными пробелами, перейдите в «Меню данных» вверху, прокрутите вниз до «Очистка данных» и нажмите «Обрезать пробелы», как показано ниже.

Когда вы выберете «Обрезать пробелы», вы увидите результат, как показано на рисунке ниже. Как видите, столбец теперь свободен от дополнительных пробелов.

Заключительные слова

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

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

Затем вы можете ознакомиться с окончательной шпаргалкой по часто задаваемым вопросам Google Sheets.

Проверяет, являются ли две строки идентичными.

Пример использования

СОВПАД(A1; A2)

СОВПАД("текст"; A3)

Синтаксис

EXACT(строка1; строка2)

• строка1 – первая строка для сравнения.

• строка2 – вторая строка для сравнения.

Примечания

• СОВПАД возвращает TRUE, если две строки идентичны, и FALSE, если строки не совпадают. Чтобы считаться идентичными, строки должны содержать одинаковые символы в одном и том же регистре, включая пробелы и скрытые символы.

Примеры

Эта информация оказалась полезной?

Как можно улучшить эту статью?

Повторяющиеся значения в Таблице Google

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

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

Задача

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

Главное меню Таблицы — Формат — Условное форматирование …

Необходимо указать, что за контролем форматирования будет следить ваша
формула, и указать ее:

=AND(NOT(ISBLANK(A1)); COUNTIF($A$1:$F; "=" & A1) > 1)

Пояснения:

AND() — логическая сумма, которая «отсекает» пустые ячейки
NOT(ISBLANK(A1));
COUNTIF($A$1:$F; "=" & A1) > 1 — сравнивает в диапазоне
$A$1:$F количество значений равных A1 с единицей.
Эта формула вернет TRUE, если условие выполнится;

Почему TRUE? Условное форматирование сработает, если формула
вернула истину.

Пример

Задача, выделить цветом те ячейки, в которых значения повторяются более одного
раза. Если количество вхождений более 3, то назначить желтый цвет, если более
5, то цвет должен быть красным.

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

Главное меню Таблицы — Формат — Условное форматирование …

=COUNTIF($A$1:$F; "=" & A1) > 5
=COUNTIF($A$1:$F; "=" & A1) > 3
=COUNTIF($A$1:$F; "=" & A1) > 1

Пример Таблицы

Корректной выборки!