Numeração Sequencial para Dados Filtrados no Excel

Veja neste artigo como inserir numeração sequencial para dados filtrados no Excel passo-a-passo com imagens.

1. Numeração Sequencial de Dados Filtrados no Excel

Há vários métodos para inserirmos numeração sequencial no Excel, seja com a alça de preenchimento, com o recurso de preencher séries na guia Página Inicial, ou ainda com fórmulas, empregando funções como SEQUÊNCIA, LIN, LINS, COL e COLS.

No entanto, podemos precisar de exibir numeração sequencial para dados filtrados, de modo que tenhamos resultados dinâmicos de acordo com o filtro, associados apenas a dados visíveis. É aí que entra a relevância de função SUBTOTAL, que é sensível a dados filtrados.

Desta forma, este artigo propõe fórmulas para este fim, como resultados dinâmicas conforme exibidos na 1ª coluna das tabelas da imagem a seguir.

Note que há filtro na coluna Estado (coluna D). O primeiro filtro retornou 4 registros, numerados de 1 a 4 ao ser filtrado o estado AM. Ao editar o filtro para o estado ES, nota-se que a numeração é alterada, indo de 1 a 5, visto que temos agora 5 registros.

Esta ilustração demonstra bem o objetivo deste artigo e a seguir são propostas 2 fórmulas distintas, baseadas na função SUBTOTAL, para este fim.

2. Fórmulas para a Numeração Incremental

2.1. Usando apenas a função SUBTOTAL

A fórmula proposta usa a função SUBTOTAL que em seu primeiro argumento (que identifica o cálculo a ser realizado, podendo ser uma soma, uma contagem, uma média etc.) terá o parâmetro 3 para estabelecer a contagem de valores. A grande atenção dá-se para o segundo argumento, em que a função pede um intervalo de dados.

Neste segundo argumento, devemos passar dados de uma coluna que terá sempre os valores preenchidos, podendo ser um campo de identificador, por exemplo, ou outro campo obrigatório nos dados. Aqui, destaca-se o fato de que a coluna inteira não será informada: apenas a primeira célula do intervalo será informada, duas vezes, sendo a primeira referência absoluta e a segunda referência relativa, conforme imagem seguinte, cuja fórmula inserida no primeiro registro (independente de ele fazer parte eventualmente dos dados visíveis no filtro, ou não) foi: =SUBTOTAL(3;$C$3:C3). Escolhemos a coluna da data por entender que sempre teremos dados inseridos.

O intervalo do segundo argumento de SUBTOTAL $C$3:C3 cresce a cada registro: na linha seguinte é $C$3:C4, depois $C$3:C5, $C$3:C6, $C$3:C7 e assim por diante, visto que a segunda referência de C3 na primeira fórmula está relativa. Vejamos a fórmula da linha 7 para notar que o intervalo está expandido:

Desta forma, a cada filtro realizado, a função conta o número de células visíveis e esta contagem sempre aumenta a cada linha visível, permitindo a contagem incremental.

2.2. Números Sequencial Filtrados Usando a função SUBTOTAL + N

A fórmula que será proposta funciona retomando a contagem da linha anterior, tendo assim dependência dos resultados já obtidos inicialmente. Aqui, a função SUBTOTAL conta valores apenas da linha em que está inserida, somando ao valor já calculado na linha acima.

A imagem acima ilustra a aplicação da fórmula, que foi =SUBTOTAL(3;C3)+N(B2).

A função N é utilizada e influencia diretamente apenas o cálculo da primeira linha: ela soma 0 ao primeiro resultado de SUBTOTAL. É necessário somar 0 porque não há linha anterior, visto que há cabeçalho. A função N verifica a célula anterior, que é cabeçalho, e retorna 0. Isso ocorre porque esta função retorna 0 em caso de texto. Ela também retorna o próprio número quando o valor é numérico, ou seja: retorna a própria contagem anterior.

Veja a fórmula copiada para uma nova linha:

Note que a função SUBTOTAL conta valor visível na própria linha em que está inserida (linha 7 da referência C7) e soma a este resultado o valor calculado na linha acima (valor 4, da fórmula de B6).

Uma fórmula alternativa utilizando a função SE e ÉTEXTO: =SUBTOTAL(3;C2)+SE(ÉTEXTO(B2);0;B2). Esta fórmula apresenta a mesma lógica de cálculo da anterior, usando, porém, a função ÉTEXTO para reconhecer se há cabeçalho (texto), devendo somar 0 ao resultado anterior. Neste contexto, ÉTEXTO e SE se unem para entregar o mesmo resultado que na fórmula anterior obtivemos apenas com a função N.

Download Planilha Número Sequencial Dados Filtrados

Clique no botão abaixo para realizar o  download do arquivo de exemplo: