Строка формата, которая отличается от предыдущей строки VISIBLE

Question

У меня есть лист Excel в качестве базы данных, где каждая строка имеет категорию и всегда сортируется по категориям.

      Category     Item
1     AAA          jjj
2     AAA          kkk
3     AAA          lll
4     BBB          mmm
5     BBB          nnn
6     CCC          ooo
7     CCC          ppp
8     CCC          qqq
9     DDD          rrr

Я использую условное форматирование для визуальной группировки категорий: например, первая строка для новой категории должна иметь рамку над ней, а значение категории должно быть выделено жирным шрифтом. Я делаю это с помощью формулы, примененной к строке, например = $A2<>$A1 и она работает отлично ... пока я не использую фильтрацию, чтобы скрыть строки.

Например, если фильтрация скрывает строку 6/CCC/ooo то строки 7 и 8 представляются частью категории BBB.

Есть ли способ создать формулу условного форматирования, которая говорит: «Если значение в этой ячейке отличается от значения в первой строке VISIBLE над мной»?

Answer 1

Изменить: это не идеальный ответ. Требуется, чтобы строки сортировались по категориям; если они не, это потерпит неудачу. Это также требует добавления дополнительного столбца (который вы можете скрыть), используемого для определения того, является ли строка скрытой или нет.

Я предпочту и приму любой ответ, который соответствует названию вопроса, т.е. основан на значении предыдущей видимой строки, а не на первом появлении значения.

---

Формат первого вхождения значения

1. Добавьте "видимый" столбец, который вычисляет, видима ли ваша строка, используя формулу:

   SUBTOTAL(103,B2)
   

   ... где 103 - это "магическое число" (оно указывает на функцию COUNTA , игнорируя видимые ячейки), а B2 - это столбец в одной строке с вашей категорией. Затем вы можете скрыть этот столбец.

2. Используйте условную формулу, подобную этой:

   =COUNTIFS($B$1:$B1,$B2,$D$1:$D1,1)=0
   

   ... где B - столбец с вашей категорией, а D - столбец с вашей "видимой" формулой.

Как это устроено

Он подсчитывает, сколько раз текущее значение категории ($B2) было видно (видимо) ранее в столбце, начиная с первой строки и заканчивая строкой, предшествующей этой. Если это значение равно 0 , то вы можете применить пользовательское форматирование.

В бою

Здесь я применил одно условное форматирование ко всей строке, чтобы добавить границу выше, а другое - только к столбцу категории, чтобы выделить и затемнить имя первой категории. Вы можете «скрыть» повторяющиеся значения категорий, установив их в белый цвет; Я включил их сюда, чтобы прояснить, что происходит.