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

Комментарии

  1. Ответы
    1. Отлично, очень рад! Только сегодня добавил новый пример в Таблицу, посмотрие, если интересно.

      Удалить
  2. Ответы
    1. Круто! Кстати, добавил новый пример в Таблицу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      Удалить
    3. Попробовала, у меня не работает

      Удалить
    4. К сожалению, без вашего примера нет возможности представить, что там пошло не так. Пришлите пример.

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

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

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

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

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

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

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

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

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

    Пример: 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

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

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

    ОтветитьУдалить
  18. пожалуйста как можно выделить точные дубли например ?: у меня выделяет номер например 9261558281 и 9251552818? нужно чтобы точное совпадение цифра в цифру совпадало?!

    ОтветитьУдалить
    Ответы
    1. Здравствуйте. Пожалуйста, приведите пример Таблицы, где показано, что работает так, как вы описали.

      Удалить
  19. Спасибо за материал, очень помог!

    ОтветитьУдалить
  20. Спасибо! А можно ли сделать чтобы подсвечивались ячейки в которых значения повторяются подряд, т.е. не поиск дубликатов по всему столбцу, а только сравнение соседних элементов в пределах одного столбца?

    ОтветитьУдалить
    Ответы
    1. Скорее всего это можно сделать, но мне сложно угадать, какие данные вы будете проверять. Поэтому пришлите пример сюда help@contributor.pw с ссылкой на этот комментарий.

      Удалить
  21. Добрый день! У меня такой вопрос, есть столбец с номерами телефонов, и мне нужно что бы подсвечивались одинаковые, ваша формула работает великолепно когда номеров по одному, НО если в одной ячейки например 89997894563 а в другой 89991234568, 89997894563, то так не выделяет, сравнивает только по первому номеру

    ОтветитьУдалить
    Ответы
    1. К сожалению, я не понял расположения данных. Пришлите пример, пожалуйста.

      Удалить
    2. Для двух колонок это может отказаться тяжелой формулой. Например, для диапазона D:E =VLOOKUP(D1; QUERY(FLATTEN($D:$E);"select Col1, count(Col1) group by Col1");2;0)>1 Добавил пример в Таблицу

      Удалить
  22. Подскажите, есть ли формула для подсвета дублей между листами, но в одинаковых колонках?

    ОтветитьУдалить
    Ответы
    1. Посмотрите пример Таблицы, который указан в статье. Там есть "Соседний лист 1" и "Соседний лист 2" с формулами типа =MATCH(B1;INDIRECT("'Соседний лист 2'!B2:B");) Это то?

      Удалить
    2. не пойму где смотреть

      Удалить
    3. https://docs.google.com/spreadsheets/d/1n4CkKgDdSNn3OFFD-eluGSAms-nupJreDkVx8lqLHyY/edit?usp=sharing

      Удалить
    4. Подскажите пожалуйста, если таких листов 3 или 4, то как нужно видоизменить формулу =MATCH(B1;INDIRECT("'Соседний лист 2'!B2:B");)

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

      Удалить
  23. Подсвет повторяющегося значения ячейки вижу, но как это сделать между лист1 и лист2 не нашёл.

    ОтветитьУдалить
    Ответы
    1. Пришлите свою Таблицу с примером данных и описанием того, что вы хотите получить.

      Удалить
  24. Подскажите пожалуйста, а если необходимо дубли из одной таблице проверять в другой и подсвечивать, как это можно сделать? С листами все понятно, а вот в разных таблицах?

    ОтветитьУдалить
    Ответы
    1. Данные из одной Таблицы нужно перенести в другую, например, с помощью IMPORTRANGE.

      Удалить
  25. Здравствуйте, подскажите пожалуйста, в этой формуле

    =AND(NOT(ISBLANK(F3)), COUNTIF($F$3:$AD, "=" & F3) > 1)

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

    Как можно решить ситуацию?

    ОтветитьУдалить
    Ответы
    1. Скорее всего дело в диапазоне на который вы применяете форматирование, возможно он не равен F3:AD.

      Еще вы не должны использовать знаки $ без веской причины. Вот это $F$3:$AD может быть неверным. Замените на F3:AD.

      Удалить
  26. Спасибо вам, за такую хорошую проделанную работу! Все заработало, только пришлось мозгами пораскинуть. Я подставлял диапазон ваших значений, а нужно свои) Спасибо еще раз)

    ОтветитьУдалить
  27. Подскажите пожалуйста формулу. Есть три столбика, и множество строк. В первом и втором столбике текст, в третьем числа от 1 до 10, числа в произвольном порядке в третьем столбике повторяются (примерно 1000строк). С помощью какой формулы сделать чтоб строки с числами 1 и 2 подсвечивались одним цветом, строки с числами 5 и 6 подсвечивались другим цветом.

    ОтветитьУдалить
  28. Подскажите пожалуйста, может у вас есть такое. Задача. Есть таблица состоящая из 3х столбцов и более 1000 строк, в первом и втором столбике текст, в третьем столбике числа от 1 до 10 в произвольном порядке. Нужно: рядом сделать таблицу в которой будет отдельно отображена сумма всех чисел в столбике от 1 до 2, сумма чисел от 3 до 6, и сумма чисел от 7 до 10.

    ОтветитьУдалить
  29. Подскажите как сделать проверку в гугл таблице всех листов на дубликаты по одной книге?

    ОтветитьУдалить
    Ответы
    1. Хороший вопрос. Скорее всего только скриптами. Возможно, есть какое-то дополнение.

      Удалить
  30. Добрый день. Подскажите как выделить последний дубль? Есть столбец с номерами телефонов, нужно подсветить первые и последние дубли. Как выделить первый дубль разобрался. А как последний - нет. B не получается выделить всю строку. Спасибо. Пример https://docs.google.com/spreadsheets/d/1NnHP61gdA1RPT0r-IyzJb6ARPnqVFjYFtLqEJSPFgbw/edit?usp=sharing

    ОтветитьУдалить
    Ответы
    1. Добрый. Ответ на этом листе https://docs.google.com/spreadsheets/d/1qDD9P6zMXImOqlEaEtIa2XzKZjGVOXcJ7S4nmPpIWvY/edit#gid=448899670&range=A1

      Если коротко, то мы проверяем является ли число дублем и находится ли оно в последнем положении в диапазоне =(COUNTIF($A:$A;$A1)>1) * (ROW(A1)=ROWS(A:A)-MATCH(A:A;SORT(A:A;ROW(A:A);0);0)+1)

      Удалить
    2. Спасибо!

      Удалить
  31. Здравствуйте. Подскажите, пожалуйста, как сделать, что формула работала на столбце, где ячейки содержат и цифры, и буквы. Например, "А111БВ", "У666РА"

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

Отправить комментарий

Спасибо за ваше сообщение. Оно может быть не опубликовано сразу из-за того, что попало на предмодерацию. Дождитесь публикации сообщения. Спасибо за понимание.

Thank you for your message. It may not be published immediately due to the fact that it got to pre-moderation. Wait for the message to be published. Thank you for understanding.

Не нашли ответ? Пишите!

Имя

Электронная почта *

Сообщение *