Условное форматирование по первому числу строк, независимо от фильтра или сортировки, в Excel

Question

Я пытаюсь найти способ легко идентифицировать первые десять строк в столбце таблицы, независимо от того, как она была отсортирована / отфильтрована. Есть ли способ использовать условное форматирование для выделения этих ячеек?

Примеры желаемых результатов ...

Пример данных:

product price   units   code
Item02  15.97   2191    7UQC
Item05  12.95   1523    TAAI
Item13  9.49    1410    LV9E
Item01  5.69    591     6DOY
Item04  15.97   554     ZCN2
Item08  10.68   451     2GN0
Item03  13.95   411     FP6A
Item07  25.45   174     PEWK
Item09  14.99   157     B5S4
Item06  18      152     XJ4G
Item10  11.45   148     BY8M
Item11  16.99   66      86C2
Item12  24.5    17      X31K
Item14  24.95   14      QJEI

1. При сортировке по price первые 10 выделенных товаров отличаются от приведенных в следующем примере.

2. Первые 10 видимых продуктов выделяются после фильтрации Item12 , Item05 и Item08 .

Answer 1

Используйте эту формулу в условном форматировании:

=SUBTOTAL(3,$A$2:$A2)<11

Убедитесь, что это относится ко всему набору данных.

Формула возвращает номер строки относительно видимого номера строки. Таким образом, поскольку строка скрыта, строка под скрытым возвращает на единицу больше, чем была бы.

Чтобы увидеть, как это работает, поместите SUBTOTAL(3,$A$2:$A2) в пустой столбец. Затем отфильтруйте таблицу и посмотрите, как меняются числа.

3 относится к функции COUNTA(), которая будет считать любую непустую ячейку.

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

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

Answer 2

Существует хороший ответ здесь.

В основном вы добавляете вспомогательный столбец с формулой

=SUBTOTAL(3, $A$2:A2)

Это дает вам количество видимых ячеек между A2 и вашей текущей строкой. Затем вы должны выделить столбец A и использовать условное форматирование что-то вроде

= $ E1 <10 с форматом, который вы хотите, и он должен выделить первые 10 строк