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

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

  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. Добрый день, можете пожалуйста привести пример, где сравнение идет с конкретным столбцом на другом листе, а не по всему другому листу?

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

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

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

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

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

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

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

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

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

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