Вы можете найти и выделить повторяющиеся записи между двумя столбцами с помощью функции условного форматирования в Google Таблицах.
Работая в Google Таблицах с большими наборами данных, вы, вероятно, столкнетесь с проблемой, когда вам придется иметь дело с множеством повторяющихся значений. При этом одни повторяющиеся записи размещены намеренно, а другие - ошибочными. Это особенно верно, когда вы работаете с командой на одном листе.
Когда дело доходит до анализа данных в Google Таблицах, возможность отфильтровывать дубликаты может быть важной и удобной. Хотя в Google Таблицах нет встроенной поддержки поиска дубликатов в таблицах, они предлагают несколько способов сравнения, идентификации и удаления дубликатов данных в ячейках.
Иногда вам нужно сравнить каждое значение в столбце с другим столбцом и выяснить, есть ли в нем дубликаты, и наоборот. В Google Таблицах вы можете легко найти дубликаты между двумя столбцами с помощью функции условного форматирования. В этой статье мы покажем вам, как сравнить два столбца в Google Таблицах и найти между ними дубликаты.
Найдите повторяющиеся записи между двумя столбцами с помощью условного форматирования
Условное форматирование - это функция в Google Таблицах, которая позволяет пользователю применять определенные форматы, такие как цвет шрифта, значки и панели данных, к ячейке или диапазону ячеек на основе определенных условий.
Вы можете использовать это условное форматирование, чтобы выделить повторяющиеся записи между двумя столбцами, заполнив ячейки цветом или изменив цвет текста. Вам нужно сравнить каждое значение в столбце с другим столбцом и выяснить, повторяется ли какое-либо значение. Чтобы это сработало, вы должны применить условное форматирование к каждому столбцу отдельно. Для этого выполните следующие действия:
Откройте электронную таблицу, которую вы хотите проверить на наличие дубликатов в Google Таблицах. Сначала выберите первый столбец (A), чтобы проверить столбец B. Вы можете выделить весь столбец, щелкнув букву столбца над ним.
Затем щелкните меню «Формат» в строке меню и выберите «Условное форматирование».
Меню условного форматирования открывается в правой части листов Google. Вы можете подтвердить, что диапазон ячеек совпадает с диапазоном, выбранным вами с помощью параметра «Применить к диапазону». Если вы хотите изменить диапазон, нажмите «значок диапазона» и выберите другой диапазон.
Затем нажмите раскрывающийся список под «Правилами формата» и выберите вариант «Пользовательская формула».
Теперь вам нужно ввести собственную формулу в поле «Значение или формула».
Если вы выбрали весь столбец (B: B), введите следующую формулу СЧЁТЕСЛИ в поле «Значение или формула» в разделе «Правила форматирования»:
= countif ($ B: $ B, $ A2)> 0Или,
Если вы выбрали диапазон ячеек в столбце (скажем, сто ячеек, A2: A30), используйте эту формулу:
= СЧЁТЕСЛИ ($ B $ 2: $ B $ 30, $ A2)> 0При вводе формулы не забудьте заменить все вхождения буквы «B» в формуле буквой выделенного столбца. Мы добавляем знак «$» перед ссылками на ячейки, чтобы сделать их абсолютным диапазоном, поэтому мы применяем формулу.
В разделе «Стиль форматирования» вы можете выбрать стиль форматирования для выделения повторяющихся элементов. По умолчанию будет использоваться зеленый цвет заливки.
Вы можете выбрать один из предустановленных стилей форматирования, щелкнув «По умолчанию» в разделе «Стиль форматирования», а затем выбрав один из предустановок.
Или вы можете использовать любой из семи инструментов форматирования (полужирный, курсив, подчеркивание, зачеркивание, цвет текста, цвет заливки) в разделе «Стиль форматирования», чтобы выделить дубликаты.
Здесь мы выбираем цвет заливки для повторяющихся ячеек, щелкнув значок «Цвет заливки» и выбрав «желтый» цвет.
Выбрав форматирование, нажмите «Готово», чтобы выделить ячейки.
Функция СЧЁТЕСЛИ подсчитывает, сколько раз каждое значение ячейки в «Столбце A» появляется в «Столбце B». Таким образом, если элемент появляется в столбце B хотя бы один раз, формула возвращает ИСТИНА. Затем этот элемент будет выделен в «Столбце A» в зависимости от выбранного вами форматирования.
При этом не выделяются дубликаты, а выделяются элементы, у которых есть дубликаты в столбце B. Это означает, что каждый выделенный желтым цветом элемент имеет дубликаты в столбце B.
Теперь мы должны применить условное форматирование к столбцу B, используя ту же формулу. Для этого выберите второй столбец (B2: B30), перейдите в меню «Формат» и выберите «Условное форматирование».
Либо нажмите кнопку «Добавить другое правило» под панелью «Правила условного формата».
Затем подтвердите диапазон (B2: B30) в поле «Применить к диапазону».
Затем установите для параметра «Форматировать ячейки, если ..» значение «Пользовательская формула равно» и введите следующую формулу в поле формулы:
= СЧЁТЕСЛИ ($ A $ 2: $ A $ 30, $ B2)> 0Здесь мы используем диапазон столбца A ($ A $ 2: $ A $ 30) в первом аргументе и «$ B2» во втором аргументе. Эта формула будет сравнивать значение ячейки в «столбце B» с каждой ячейкой в столбце A. Если совпадение (дубликат) обнаружено, то условное форматирование будет выделять этот элемент в «столбце B».
Затем укажите форматирование в параметрах «Стиль форматирования» и нажмите «Готово». Здесь мы выбираем оранжевый цвет для столбца B.
Это выделит элементы столбца B, у которых есть дубликаты в столбце A. Теперь вы нашли и выделили повторяющиеся элементы между двумя столбцами.
Вы, наверное, заметили, хотя в столбце A есть дубликат для «Arcelia», он не выделен. Это связано с тем, что повторяющееся значение находится только в одном столбце (A), а не между столбцами. Следовательно, он не выделяется.
Выделите дубликаты между двумя столбцами в одной строке
Вы также можете выделить строки, которые имеют одинаковые значения (дубликаты) между двумя столбцами, используя условное форматирование. Правило условного форматирования может проверять каждую строку и выделять строки с совпадающими данными в обоих столбцах. Вот как это сделать:
Сначала выберите оба столбца, которые вы хотите сравнить, затем перейдите в меню «Формат» и выберите «Условное форматирование».
На панели правил условного формата подтвердите диапазон в поле «Применить к диапазону» и выберите «Пользовательская формула» в раскрывающемся списке «Ячейки формулы, если ..».
Затем введите приведенную ниже формулу в поле «Значение или формула»:
= $ A2 = $ B2Эта формула будет сравнивать два столбца строка за строкой и выделять строки с одинаковыми значениями (дубликатами). Как видите, введенная здесь формула предназначена только для первой строки выбранного диапазона, но формула будет автоматически применяться ко всем строкам в выбранном диапазоне функцией условного форматирования.
Затем укажите форматирование в параметрах «Стиль форматирования» и нажмите «Готово».
Как видите, будут выделены только строки с совпадающими данными (дубликатами) между двумя столбцами, а все остальные дубликаты будут проигнорированы.
Выделите повторяющиеся ячейки в нескольких столбцах
При работе с большими электронными таблицами с большим количеством столбцов вы можете выделить все дубликаты, которые появляются в нескольких столбцах, а не только в одном или двух столбцах. Вы по-прежнему можете использовать условное форматирование, чтобы выделить дубликат в нескольких столбцах.
Сначала выберите диапазон всех столбцов и строк, в которых вы хотите искать дубликаты, а не только один или два столбца. Вы можете выбрать столбцы целиком, удерживая нажатой клавишу Ctrl, а затем щелкнув букву вверху каждого столбца. Кроме того, вы также можете щелкнуть первую и последнюю ячейки в своем диапазоне, одновременно удерживая клавишу Shift, чтобы выбрать несколько столбцов одновременно.
В этом примере мы выбираем A2: C30.
Затем нажмите в меню опцию «Формат» и выберите «Условное форматирование».
В правилах условного формата установите для правил формата значение «Пользовательская формула», а затем введите следующую формулу в поле «Значение или формула»:
= countif ($ A $ 2: $ C $ 30, A2)>Мы добавляем знак «$» перед ссылками на ячейки, чтобы сделать их абсолютными столбцами, поэтому мы применяем формулу. Вы также можете ввести формулу без знаков «$», это работает в любом случае.
Затем выберите форматирование, в котором вы хотите выделить повторяющиеся ячейки, с помощью параметров «Стиль форматирования». Здесь мы выбираем «желтый» цвет заливки. После этого нажмите «Готово».
Это выделит дубликаты во всех выбранных столбцах, как показано ниже.
После применения условного форматирования вы можете изменить или удалить правило условного форматирования в любое время.
Если вы хотите изменить текущее правило условного форматирования, выберите любую ячейку с условным форматированием, перейдите к «Форматировать» в меню и выберите «Условное форматирование».
Откроется панель «Правила условного форматирования» справа со списком правил форматирования, примененных к текущему выбору. При наведении указателя мыши на правило отображается кнопка удаления, нажмите кнопку удаления, чтобы удалить правило. Или, если вы хотите отредактировать правило, которое отображается в данный момент, щелкните само правило.
Если вы хотите добавить другое условное форматирование к текущему правилу, нажмите кнопку «Добавить другое правило».
Подсчитайте дубликаты между двумя столбцами
Иногда вам нужно подсчитать, сколько раз значение в одном столбце повторяется в другом столбце. Это легко сделать с помощью той же функции СЧЁТЕСЛИ.
Чтобы узнать, сколько раз значение из столбца A существует в столбце B, введите следующую формулу в ячейку другого столбца:
= СЧЁТЕСЛИ ($ B $ 2: $ B $ 30, $ A2)Введите эту формулу в ячейку C2. Эта формула подсчитывает, сколько раз значение в ячейке A2 существует в столбце (B2: B30), и возвращает счет в ячейке C2.
Когда вы вводите формулу и нажимаете Enter, появляется функция автозаполнения, щелкните «Галочку», чтобы автоматически заполнить эту формулу остальными ячейками (C3: C30).
Если функция автозаполнения не отображается, щелкните синий квадрат в правом нижнем углу ячейки C2 и перетащите его вниз, чтобы скопировать формулу из ячейки C2 в ячейки C3: C30.
Столбец «Сравнение 1» (C) теперь покажет вам, сколько раз каждое соответствующее значение из столбца A появляется в столбце B. Например, значение A2 или «Franklyn» не найдено в столбце B, поэтому Функция СЧЁТЕСЛИ возвращает «0». И значение «Loreta» (A5) встречается дважды в столбце B, следовательно, оно возвращает «2».
Теперь мы должны повторить те же шаги, чтобы найти повторяющиеся числа в столбце B. Для этого введите следующую формулу в ячейку D2 в столбце D (Сравнение 2):
= СЧЁТЕСЛИ ($ A $ 2: $ A $ 30, $ B2)В этой формуле замените диапазон «$ B $ 2: $ B $ 30» на «$ A $ 2: $ A $ 30» и «$ B2» на «$ A2». Функция подсчитывает, сколько раз значение в ячейке B2 существует в столбце A (A2: A30), и возвращает счет в ячейке D2.
Затем автоматически заполните формулу до остальных ячеек (D3: D30) в столбце D. Теперь «Сравнение 2» покажет вам, сколько раз каждое соответствующее значение в столбце B появляется в столбце A. Например, , значение B2 или «Старк» встречается дважды в столбце A, поэтому функция СЧЁТЕСЛИ возвращает «2».
Примечание: Если вы хотите подсчитать дубликаты во всех столбцах или нескольких столбцах, вам просто нужно изменить диапазон в первом аргументе функции СЧЁТЕСЛИ на несколько столбцов, а не только один столбец. Например, измените диапазон с A2: A30 на A2: B30, при этом будут подсчитаны все дубликаты в двух столбцах, а не только в одном.
Вот и все.