Повторяющиеся значения в Таблице 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
Пример Таблицы Повторяющиеся значения в Таблице Google.

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

Как правильно писать формулы в Таблицах Google.

37 комментариев:

  1. Спасибо! только вот для одного столбца нужно было, додумывал сам)

    ОтветитьУдалить
    Ответы
    1. Пожалуйста!
      Этот вопрос пытался описать в пояснении "сравнивает в диапазоне $A$1:$F". Для одной колонки необходимо изменить диапазон, например, на "$A$1:$A"

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

    ОтветитьУдалить
    Ответы
    1. Да, для этого необходимо ссылаться на данные соседнего листа через формулу INDIRECT.

      Удалить
    2. =И(НЕ(ЕПУСТО(A1)), СЧЁТЕСЛИ($A$1:$G, "=" ДВССЫЛ("Чёрныйсписок!"&A1) > 1))
      Эта формула выдаёт ошибку. Не пойму что не так.
      "сравнивает в диапазоне $A$1:$F количество значений равных A1 с единицей" Вот эту фразу вообще не понял. Что за значения равные А1?

      Удалить
    3. Действительно, что-то запутано. Можете привести пример в Таблице? Я пока вижу две ошибки: (1) запятые, вместо точек с запятой, (2) сложная интерпретация "Чёрныйсписок!"&A1, которая, возможно, посто неверная.

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

      заранее благодарю

      Удалить
    5. Присоединюсь к просьбе

      Удалить
    6. Пожлуйста, приводите свои примеры Таблиц с минимальными данными. Для каждого просто невозможно создавать одно и тоже. Проявите инициативу. Как сделать промер https://contributor.pw/post/why-you-should-create-an-example/

      Удалить
  3. Добрый день!

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

    Например: Есть столбец с номерами договоров и нужно выделить повторяющиеся, но при этом в этом же столбце есть значения "б/н" и "на почту", которые не нужно учитывать при выделении.

    Заранее спасибо!

    ОтветитьУдалить
    Ответы
    1. Необходимо расширить проверку условия COUNTIF до нескольких параметров. Это можно сделать несколькими способами. Приведите пример Таблицы.

      Удалить
  4. Что то не получается! Точно знаю что есть одно повторения в столбце из 50 ячеек, функция "удалить дубликаты" убирает 1 ячейку и смещает вверх столбец, что тоже скверное решение(((( Почему не работает? Просто окрашивает весь столбец в серый цвет(

    ОтветитьУдалить
  5. Добрый день. Подскажите пожалуйста, например, у меня в ячейке А1 - "Номер 1", а в ячейке В1 - "22.07.2019" (Любая дата). Как сделать подсветку если, и в столбце "А" и в столбце "В" есть совпадения и по Номеру и по Дате (А3 "Номер 1", В3 "22.07.2019? Надеюсь понятно написал)

    ОтветитьУдалить
    Ответы
    1. Буду рад помочь, если будет пример https://contributor.pw/post/why-you-should-create-an-example/

      Удалить
  6. Спасибо огромное! Очень выручили

    ОтветитьУдалить
  7. Добрый день. Подскажите как исключить из сравнения определенный текст из столбца. На пример мы имеет столбец в котором идут значения
    vk.com\id1
    vk.com\id2

    И он выделяет их как совпадение ибо есть начальное совпадение. А мне необходимо что бы он сравнивал только то что идет после ID
    Заранее спасибо

    ОтветитьУдалить
    Ответы
    1. Здравствуйте. Нужен пример. У меня ничего не выделяет https://docs.google.com/spreadsheets/d/1n4CkKgDdSNn3OFFD-eluGSAms-nupJreDkVx8lqLHyY/edit#gid=1118376404

      Удалить
  8. Вот пример. Формула скопирована с текста в вашем посте.
    https://docs.google.com/spreadsheets/d/1sBgGhD7L3fRLPDTR6WainlqRXxBrOq5F6uebiab0ghE/edit?usp=sharing

    ОтветитьУдалить
    Ответы
    1. Я обнаружил, что у вас там было два правила условного форматирования. Первое перекрывало второе.

      Удалить
    2. Формула для колонки отличается от формулы для диапазона. Должно быть =AND(NOT(ISBLANK(D1)); COUNTIF($D$1:$D; "=" &D1) > 1) для колонки D. Поправил у вас в Таблице.

      Удалить
  9. Работает. Спасибо большое !

    ОтветитьУдалить
  10. Добрый день. Подскажите как прописать формулу для документа из нескольких листов с текстовыми и цифровыми данными.
    Надо, чтобы в любом листе подсвечивались данные, которые встречаются на других листах.
    Ваша формула прекрасно работает для одного листа - не получается переписать ее для нескольких листов.

    Пример таблицы https://docs.google.com/spreadsheets/d/1y9ruPbSdISy3MGo7xn9e6imzVZay1VUy_UzUYa3vU_g/edit?usp=sharing

    Заранее спасибо!

    ОтветитьУдалить
    Ответы
    1. Ага, понятно. Я добавлю этот пример в Таблицу к посту чуть позже. Отпишусь.

      Удалить
    2. Лобавил пример в основной файл. См. "Соседний лист 1" и "Соседний лист 2"

      Удалить
  11. Урааа!!! Спасибо! Вы чудо <3
    я знала, что должен быть способ))

    ОтветитьУдалить
  12. Александр большое спасибо!
    По экселевской привычке пытался решить формулами, форматированием гораздо удобнее.

    ОтветитьУдалить
  13. Здравствуйте! Подскажите как сделать если мне необходимо Выделить цветом Определенное значение и количество данных значений в диапазоне.

    Пример: https://docs.google.com/spreadsheets/d/1aZNZj_RsJUZsqiEuztXManb3hhLRGnc6hEGWFyhrGLU/edit?usp=sharing

    ОтветитьУдалить
    Ответы
    1. Я думаю, что это можно решить. Но под рукой ничего нет, а в рамках бесплатной поддержки я этого сделать не могу. Может быть в другой раз. Положил в закладки https://docs.google.com/spreadsheets/d/1FYYCkO-3R3qJr-a6UVvmxAgb_gnAm1nt_7Q_6nfU2Fs/edit?usp=sharing

      Удалить
  14. Спасибо. Воспользовался, удобно.

    ОтветитьУдалить
  15. А как выделить повторения в разных листах?

    ОтветитьУдалить

Популярные сообщения