Numeração de linhas em um filtro

Question

Em um intervalo com filtro, desejo ter uma coluna de números de linha que será alterada de acordo com o filtro , de forma que as linhas sempre contenham consecutivamente 1. Por exemplo, o seguinte é um intervalo com filtro:

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

Agora, suponha que apliquemos um filtro para mostrar linhas nas quais o sexo é M:

Number   Name    Gender
2        Jason   M
5        Albert  M

O que está acima é o que o Excel normalmente mostra, mas eu quero que o Número conte de 1 consecutivamente assim:

Number   Name    Gender
1        Jason   M
2        Albert  M

Portanto, as linhas devem ser renumeradas de acordo com o filtro. Eu tentei algo como =MAX(...)+1 ou usando a função SUBTOTAL() , mas ainda não consegui. É possível escrever uma fórmula para a coluna Número para executar essa tarefa? Como?

    

Answer 1

Use esta fórmula:

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

na célula A2 . (Isso pressupõe que você tenha um cabeçalho de coluna na célula A1 .)

Parâmetros de AGGREGATE() :

• Function_num = 3, conta todas as células não vazias em um intervalo
• Opções = 5, ignorar linhas ocultas no intervalo
• Array = $ A $ 1: A1, intervalo da primeira linha até a linha acima da célula selecionada

Exemplo de saída:

Como mencionado por benshepherd, SUBTOTAL() também pode ser usado.

    

Answer 2

@ Máté Juhász acabou de chegar antes de mim. Eu estava indo para uma abordagem alternativa usando SUBTOTAL . Em A2, coloque =SUBTOTAL(103,B$2:B2) e preencha.

A função SUBTOTAL executa uma função numerada em seus argumentos - essas funções são descritas na Ajuda. 103 corresponde a COUNTA ignorando linhas ocultas. (Você usaria um valor de 3 para incluir linhas ocultas). COUNTA conta o número de células não vazias em um intervalo. Usamos a sintaxe B$2:B2 para manter a célula principal igual e expandir o intervalo à medida que descemos na lista.