Como determinar a última célula preenchida de uma coluna

Já mostramos em outra postagem o método para se determinar qual a última linha preenchida (ou coluna) de uma planilha, mas usando o VBA.

Agora, mostraremos como determinar a última célula preenchida sem o uso do VBA, apenas com a utilização de uma função do Excel, a função DESLOC (você pode ver outros exemplos de uso dela aqui).

Suponha que você tenha uma planilha que é atualizada freqüentemente pela adição de novas linhas e/ou colunas, e para isto você precise retornar o valor que se encontre na última célula preenchida, que pode ser o último valor da coluna como o último valor de uma linha.

Veja o exemplo a seguir:

Queremos retornar o valor da última célula preenchida de determinada coluna. Se for a Coluna 1, queremos trazer o 150, da 2 o 110, etc.

Para podermos trazer o valor da última célula preenchida, a função deverá ser capaz de:

1. Saber qual é a coluna
2. Determinar qual a última linha preenchida
3. Retornar o valor daquela linha x coluna

A função DESLOC possui um total de 5 argumentos. No exemplo, usaremos os 3 primeiros (você pode ver o uso desta função com todos os argumentos no tutorial de criação deste gráfico).

Os 3 argumentos que usaremos da função são:

=DESLOC(célula inicial; linhas a subir ou descer; colunas para a direita ou esquerda)

A célula inicial pode ser qualquer uma. Para facilitar a compreensão, usaremos a célula A6, que é a linha inicial de nossa tabela.

No 2º argumento informamos quantas linhas devemos descer (ou subir, se fosse o caso). Se for a coluna 1, são 15 linhas; a 2, 2 linhas, etc.

Veja que na linha 5 inserimos uma função que irá contar o total de células preenchidas. A função chama-se CONT.VALORES (o exemplo só funcionará se NÃO tivermos linhas em branco no meio dessa listagem).

O que precisamos fazer é informar qual dos valores vamos querer, se é o da Coluna 1, 2 ou 3.

Na célula B1 vamos digitar o número da coluna que queremos. No exemplo acima, queremos a coluna 1.

Na célula B2 inserimos uma função que irá trazer, da lista de número de linhas preenchidas (o intervalo que vai de B5 a D5) o valor correspondente à coluna desejada. Se for coluna 1, ele trará o 1º valor. Se for coluna 2, o 2º valor e da coluna 3, o 3º valor.

A função ÍNDICE faz exatamente isto: a partir de uma lista, indicamos qual ítem queremos.

Então até agora temos a célula onde tudo vai começar e o total de linhas a se moverem para baixo.

Falta informar quantas colunas deveremos nos deslocar para a direita. Mas já temos isto: é o número que digitamos na célula A2.

Então, nossa função ficará assim:

=DESLOC(A6;B2;A2)

Como lemos a função: a iniciando na célula B6, vá para cima / baixo pela quantidade de linhas informada na célula B2, e vá para a direita/esquerda pela quantidade de colunas informada na célula B1 (você já deve ter notado que, se quiséssemos ir para cima deveríamos informar o número com sinal negativo, bem como se fôssemos para a esquerda).

Essa função é uma das mais estranhas do Excel, mas ao mesmo tempo é uma das versáteis. Recomendo fortemente que você conheça-a mais. Você vi se surpreender 🙂

OUTROS ARTIGOS DE INTERESSE