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

    thanks)

    ОтветитьУдалить
  2. Unknown28 февраля 2019 г. в 13:15

    Работает!!! Спасибочки!!!

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov12 января 2021 г. в 13:37

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

      Удалить
  3. Unknown6 марта 2019 г. в 15:27

    Спасибо!

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov12 января 2021 г. в 13:38

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

      Удалить
  4. Питя20 мая 2019 г. в 11:19

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov19 июня 2019 г. в 07:06

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

      Удалить
  5. ZaToWarlock18 июня 2019 г. в 15:43

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov19 июня 2019 г. в 07:05

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

      Удалить
    2. ZaToWarlock20 июня 2019 г. в 13:59

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

      Удалить
    3. Alexander Ivanov21 июня 2019 г. в 09:02

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

      Удалить
    4. Unknown22 июля 2019 г. в 15:52

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

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

      Удалить
    5. Добрая Фея29 июля 2019 г. в 11:24

      Присоединюсь к просьбе

      Удалить
    6. Alexander Ivanov1 августа 2019 г. в 07:26

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

      Удалить
  6. Unknown21 июня 2019 г. в 12:34

    Добрый день!

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

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

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov21 июня 2019 г. в 14:23

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

      Удалить
  7. Unknown10 июля 2019 г. в 16:08

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

    ОтветитьУдалить
  8. Unknown22 июля 2019 г. в 18:44

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov25 июля 2019 г. в 16:02

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

      Удалить
  9. eretik21 августа 2019 г. в 00:44

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

    ОтветитьУдалить
  10. h523 октября 2019 г. в 13:30

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

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov24 октября 2019 г. в 06:23

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

      Удалить
  11. h524 октября 2019 г. в 07:49

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov24 октября 2019 г. в 10:00

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

      Удалить
    2. Alexander Ivanov24 октября 2019 г. в 10:02

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

      Удалить
    3. Анонимный3 июля 2020 г. в 08:52

      Попробовала, у меня не работает

      Удалить
    4. Alexander Ivanov12 января 2021 г. в 09:12

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

      Удалить
  12. h524 октября 2019 г. в 10:32

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

    ОтветитьУдалить
  13. Unknown24 октября 2019 г. в 14:59

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

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

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov24 октября 2019 г. в 20:08

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

      Удалить
    2. Alexander Ivanov24 декабря 2019 г. в 15:35

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

      Удалить
  14. Unknown2 ноября 2019 г. в 05:11

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

    ОтветитьУдалить
  15. Unknown9 декабря 2019 г. в 18:47

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

    ОтветитьУдалить
  16. Viktar19 декабря 2019 г. в 16:16

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

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov28 декабря 2019 г. в 08:54

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

      Удалить
  17. Unknown26 декабря 2019 г. в 16:53

    Спасибо за статью

    ОтветитьУдалить
  18. Unknown31 декабря 2019 г. в 09:13

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

    ОтветитьУдалить
  19. Unknown1 февраля 2020 г. в 20:16

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

    ОтветитьУдалить
  20. Unknown17 марта 2020 г. в 20:51

    & - Зачем этот символ?

    ОтветитьУдалить
  21. Unknown25 апреля 2020 г. в 12:04

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov29 апреля 2020 г. в 09:26

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

      Удалить
  22. DmitryAl24 июля 2020 г. в 15:05

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

    ОтветитьУдалить
  23. Анонимный2 сентября 2020 г. в 00:30

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov12 января 2021 г. в 09:04

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

      Удалить
  24. Unknown1 октября 2020 г. в 14:52

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov12 января 2021 г. в 12:50

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

      Удалить
    2. Alexander Ivanov12 января 2021 г. в 13:00

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

      Удалить
  25. Анонимный17 октября 2020 г. в 18:48

    Спасибо

    ОтветитьУдалить
  26. Сергей Дмитриевич17 декабря 2020 г. в 09:44

    Спасибо. С новым годом!

    ОтветитьУдалить
  27. Unknown8 февраля 2021 г. в 18:35

    ОГРОМНОЕ СПАСИБО!

    ОтветитьУдалить
  28. Unknown26 марта 2021 г. в 17:51

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov27 марта 2021 г. в 05:49

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

      Удалить
    2. Unknown27 марта 2021 г. в 16:21

      не пойму где смотреть

      Удалить
    3. Alexander Ivanov27 марта 2021 г. в 18:16

      https://docs.google.com/spreadsheets/d/1n4CkKgDdSNn3OFFD-eluGSAms-nupJreDkVx8lqLHyY/edit?usp=sharing

      Удалить
    4. Unknown31 марта 2021 г. в 13:20

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

      Удалить
    5. Alexander Ivanov3 декабря 2021 г. в 14:10

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

      Удалить
  29. Unknown31 марта 2021 г. в 16:30

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov1 апреля 2021 г. в 05:42

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

      Удалить
  30. Анонимный7 апреля 2021 г. в 13:10

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov8 апреля 2021 г. в 06:12

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

      Удалить
  31. Анонимный16 апреля 2021 г. в 19:12

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

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

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

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov17 апреля 2021 г. в 06:36

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

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

      Удалить
  32. Unknown12 августа 2021 г. в 12:45

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

    ОтветитьУдалить
  33. VKolpakovv29 сентября 2021 г. в 10:56

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

    ОтветитьУдалить
  34. VKolpakovv29 сентября 2021 г. в 14:59

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

    ОтветитьУдалить
  35. Unknown25 ноября 2021 г. в 12:29

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov25 ноября 2021 г. в 14:48

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

      Удалить
  36. Анонимный5 июня 2023 г. в 14:55

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

    ОтветитьУдалить
    Ответы
    1. Alexander Ivanov5 июня 2023 г. в 17:05

      Добрый. Ответ на этом листе 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. Анонимный22 ноября 2023 г. в 12:34

      Спасибо!

      Удалить
  37. Анонимный28 марта 2024 г. в 13:12

    Здравствуйте. Подскажите, пожалуйста, как сделать, что формула работала на столбце, где ячейки содержат и цифры, и буквы. Например, "А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.

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

Имя

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

Сообщение *