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

Question

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

Number   Name    Gender
1        Alice   F
2        Jason   M
3        Ka      F
4        Fiona   F
5        Albert  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, игнорировать скрытые строки в диапазоне
• Массив = $A$1:A1, диапазон от первой строки до строки над выбранной ячейкой

Пример вывода:

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

Answer 2

@Máté Juhász только что опередил меня. Я собирался использовать альтернативный подход, используя SUBTOTAL. В A2, поместите =SUBTOTAL(103,B$2:B2)и заполните вниз.

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