Нумерация строк в фильтре

Question

В диапазоне с фильтром я хочу иметь столбец с номерами строк, которые будут меняться в зависимости от фильтра, так что строки всегда будут отсчитываться последовательно от 1. Например, следующее - это диапазон с фильтром:

Number   Name    Gender
1        Alice   F
2        Jason   M
3        Ka      F
4        Fiona   F
5        Albert  M

Теперь предположим, что мы применяем фильтр, чтобы показать строки, где Gender равен M:

Number   Name    Gender
2        Jason   M
5        Albert  M

Выше приведено то, что обычно показывает Excel, но я хочу, чтобы число считалось от 1 последовательно, например так:

Number   Name    Gender
1        Jason   M
2        Albert  M

Таким образом, строки должны быть перенумерованы в соответствии с фильтром. Я пробовал что-то вроде =MAX(...)+1 или с помощью функции SUBTOTAL() , но мне пока не удалось. Можно ли написать формулу для столбца Number для выполнения этой задачи? Как?

Answer 1

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

=AGGREGATE(3,5,$A$1:A1)

в клетку A2 .  (Это предполагает, что у вас есть заголовок столбца в ячейке A1 .)

Параметры AGGREGATE():

• Function_num = 3, подсчитать все непустые ячейки в диапазоне
• Опции = 5, игнорировать скрытые строки в диапазоне
• Array = $ A $ 1:A1, диапазон от первой строки до строки над выбранной ячейкой

Образец вывода:

Как упомянуло benshepherd, SUBTOTAL() может быть использован.

Answer 2

@ Мате Юхас только что опередил меня. Я собирался на альтернативный подход с использованием SUBTOTAL . В A2 положите =SUBTOTAL(103,B$2:B2) и заполните.

Функция SUBTOTAL выполняет пронумерованную функцию для своих аргументов - эти функции описаны в справке. 103 соответствует COUNTA игнорирующему скрытые строки. (Вы бы использовали значение 3, чтобы включить скрытые строки.) COUNTA подсчитывает количество непустых ячеек в диапазоне. Мы используем синтаксис B$2:B2 чтобы сохранить верхнюю ячейку одинаковой, и расширяем диапазон по мере продвижения по списку.