Изменение цвета в видимых строках на основе значения столбца в Excel

Это похоже на цвет фона альтернативной сетки в Excel, когда значение одного столбца изменяется? и я считаю, что это можно сделать с помощью SUBTOTAL(109,..., но я не могу понять это.

Первый столбец в моей таблице - это номер группы, и все строки с одинаковым номером группы должны иметь одинаковый фон. Таблица отсортирована по номеру группы.

Я хочу чередовать цвет строки для группы, основываясь только на видимых строках. В этом примере я спрятал A а также C, Обратите внимание, что praesent а также libero поменялись цветами на основе видимых строк.

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

Источник

2 ответа

Решение

Вот ответ с двумя вспомогательными столбцами (конечно, вы можете скрыть их):

  • helper1: =AGGREGATE(2,5,A2)
    • он просто показывает 1 для видимых и 0 для невидимых строк (конечно, вы всегда видите 1:))
  • helper2: =IF(C2=1,IFERROR(MAX($D$1:D1)+(COUNTIFS($A$1:A1,A2,$C$1:C1,1)=0),1),"")
    • MAX($D$1:D1) - ищет самый большой номер группы до сих пор
    • COUNTIFS($A$1:A1,A2,$C$1:C1,1) - проверяет, присутствует ли текущее значение в ВЫШЕ ВИДИМЫХ строках
    • MAX(...)+(COUNTIFS(...)=0) - увеличивает номер группы, если это новая группа
    • IFERROR(...,1) - устанавливает номер группы в 1 для первой видимой строки
    • IF(C2=1,...,"") - вычисляет номер группы только для видимых строк

Настройка условного форматирования:

  • перейти к: Главная - условное форматирование - новое правило - использовать формулу...
  • в формулу введите =MOD($D1,2)=1
  • установите желаемое форматирование

Источник

Я только добавляю это как ответ, потому что у меня недостаточно представителя, чтобы комментировать. Это основано на ответе Мате, который был потрясающим. Я сжал его до одного вспомогательного столбца и сделал так, чтобы он полагался только на значения в последовательных строках (это должно способствовать повышению производительности на больших наборах) и изменил счетчик на счетчик (чтобы я мог использовать строки в дополнение к числам).

= MOD (ЕСЛИ (D6-<> Д5, СОВОКУПНАЯ (3,5, Е6)+ А5, А5),2)

Источник
Другие вопросы по тегам