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

Question

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

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

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

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

Answer 1

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

• helper1: = AGGREGATE =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
• установите желаемое форматирование

Answer 2

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

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