Pular para o conteúdo

Primeira e última célula preenchida

Primeira e última célula preenchida

Por muitas vezes registramos dados em linhas ou colunas, mas para fins de cálculos apenas precisamos do último valor inserido no intervalo, ou então o primeiro deles. Pois agora você vai aprender a como identificar a primeira e última célula preenchida de uma coluna ou linha e buscar o dado inserido nelas.

Embora os exemplos abaixo esteja utilizando colunas, o mesmo se aplica para linhas, levando em conta as alterações necessárias.


Como buscar o dado contido na primeira célula preenchida

Com base na tabela abaixo, digamos que queiramos identificar qual das células é a primeira a conter algum dado.

primeira-e-ultima-celula-preenchida-1 A fórmula que utilizaremos para encontrar o dado da primeira célula preenchida no intervalo de B3:B14 é:

=ÍNDICE(B3:B14;CORRESP(1;ÍNDICE(--(B3:B14<>"");0);0);1)

Como esta fórmula funciona?

O que temos nessa fórmula é um ÍNDICE + CORRESP incrementado, digamos assim. Se você ainda não conhece o poder dessas duas funções juntas, recomendo fortemente que dê uma olhada nos seguintes artigos:

ÍNDICE + CORRESP. Fazendo PROCV comer poeira

ÍNDICE + CORRESP com múltiplos critérios

Determinando a posição de dados utilizando CORRESP

PROCV turbinado com CORRESP

Voltando ao que nos interessa nesse artigo…. vou partir do suposto que você já entende o funcionamento dessas duas funções (caso não entenda, é só olhar os artigos citados acima).

Logo a única diferença que vemos nessa fórmula em relação ao que normalmente se utiliza de ÍNDICE + CORRESP é aquela segunda função ÍNDICE contida dentro do CORRESP com dois sinais de subtração na frente. Então vamos focar nisso.

CORRESP(1;ÍNDICE(--(B3:B14<>"");0);0)

A função CORRESP irá buscar pelo número 1 contido na matriz criada pela função ÍNDICE que para cada célula no intervalo B3:B14 irá verificar se a célula está vazia ou não. No nosso exemplo a matriz resultante dessa função ÍNDICE é:

{FALSO;FALSO;VERDADEIRO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO}

E é aí que entram os dois sinais de subtração, conforme já mostrado no artigo deste link aqui, o uso do duplo sinal de subtração apenas faz com que VERDADEIRO seja transformado em 1 e FALSO seja transformado em 0. Então após passar pelos “–” a matriz fica assim:

{0;0;1;0;0;0;1;0;1;1;0;0}

Então a função CORRESP faz sua parte encontrando em qual posição está o primeiro número 1, neste caso na posição 3. Logo sabemos que no intervalo de B3:B14 a primeira célula preenchida está na  posição, ou seja na célula B5.

Assim, esse resultado é utilizado na outra função ÍNDICE como a linha onde está o dado procurado, nos retornando o valor contido em B5.


Endereço da primeira célula preenchida

Agora, ao invés de buscar o dado contido em B5, digamos que precisamos saber qual o endereço da célula, ou seja, queremos que a função nos retorne B5. A fórmula para isso é:

=ENDEREÇO(CORRESP(1;ÍNDICE(--(B3:B14<>"");0);0)+2;2)

Note que o CORRESP + ÍNDICE contido nessa fórmula é exatamente o mesmo explicado anteriormente, então nada muda, já sabemos que essa parte da fórmula nos retorna o número 3 ou seja, a primeira célula preenchida no intervalo B3:B14 está na terceira posição.

Somamos a este resultado o número 2, isso porque nosso intervalo inicia na linha 3 e não na linha 1, logo precisamos cobrir esta diferença para encontrar corretamente a linha em que o dado está, não em relação ao intervalo selecionado de B3:B14, mas em relação á planilha como um todo. Desta forma chegamos ao número 5 (3 + 2).

Então entra a função ENDEREÇO a qual informamos 2 argumentos, o primeiro deles é a linha, que é dado pela função CORRESP + 2 e o segundo argumento é a coluna, que é o número 2 já que estamos olhando para a coluna B, ou seja, a  coluna. Com bases nestes dois dados a função ENDEREÇO nos retorna corretamente.


Alternativa 1 para buscar o dado contido na primeira célula preenchida

Existe uma outra forma de buscar o dado da primeira célula preenchida, utilizando como base a fórmula acima, que nos dá o endereço da célula onde está o dado. Para isso basta colocarmos a fórmula utilizada acima dentro da função INDIRETO e pronto, desse jeito:

=INDIRETO(ENDEREÇO(CORRESP(1;ÍNDICE(--(B3:B14<>"");0);0)+2;2))

O resultado é exatamente o mesmo obtido na primeira fórmula com as duas funções ÍNDICE e CORRESP.


Alternativa 2 para buscar o dado contido na primeira célula preenchida

Esta eu não recomendo pois utiliza função matricial, o que pode ser lento e consumir muita memória do computador.

{=ÍNDICE(B3:B14;CORRESP(FALSO;ÉCÉL.VAZIA(B3:B14);0);1)}

Ao invés de utilizar a função ÍNDICE dentro do CORRESP utilizamos uma função que verifica se a célula é vazia ou não. Então uma matriz é criada e a função CORRESP procura dentro dessa matriz a primeira aparição de FALSO, o resto do processo é igual ao primeiro exemplo mostrado. Para que essa função funcione você deve digitá-la sem as chaves {} e concluí-la pressionando CTRL + SHIFT + ENTER ao invés de somente ENTER como normalmente faria. Para saber mais sobre funções matriciais, veja aqui.

A imagem abaixo mostra o resultado de tudo.

primeira-e-ultima-celula-preenchida-2


Como buscar o dado contido na última célula preenchida

Vamos seguir utilizando a mesma tabela de antes, mas agora em busca do ultimo dado preenchido no intervalo de B3:B14.

A fórmula que utilizaremos é:

=PROC(2;1/--(B3:B14<>"");B3:B14)

Como esta fórmula funciona?

(B3:B14<>””) retorna uma matriz de FALSO e VERDADEIRO, no nosso exemplo a matriz é:

{FALSO;FALSO;VERDADEIRO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO}

Com o uso dos “–” esta matriz é convertida em 10, como já vimos anteriormente, e obtemos:

{0;0;1;0;0;0;1;0;1;1;0;0}

Então o número é dividido por cada número desta matriz, retornando uma segunda matriz de dados, formada por 1 e #DIV/0! (Erro por dividir alguma coisa por zero).

{#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!;#DIV/0!}

Então a função PROC irá buscar pelo número 2 dentro dessa matriz, porém como a matriz não possui o número 2, apenas 1 e #DIV/0!, mas o maior valor da matriz é 1, então PROC irá corresponder ao último na matriz.

Por fim, PROC irá retornar o valor que estiver na mesma posição dentro do intervalo informado no último argumento da função, ou seja, a mesma posição em que foi encontrado o último 1 na matriz de análise, será buscada no intervalo B3:B14.


Endereço da última célula preenchida

A função utilizada aqui é praticamente igual a anterior, veja:

=ENDEREÇO(PROC(2;1/--(B3:B14<>"");LIN(B3:B14));2)

A única coisa que mudou foi a inserção da função LIN no final e ter colocado tudo isso dentro da função ENDEREÇO. Então ao invés de retornar o valor contido na célula que estiver na mesma posição da primeira matriz utilizada na fórmula, irá retornar a linha desta célula. Que depois é passada para a função ENDEREÇO que retornará o nome da última célula preenchida no intervalo.

A imagem abaixo mostra o resultado de tudo.

primeira-e-ultima-celula-preenchida-3


Acompanhe o Função Excel
facebook-logo youtube-logo googleplus-logo twitter-logo

11 comentários em “Primeira e última célula preenchida”

  1. Amigão, em relação a fórmula ÍNDICE(B3:B14;CORRESP(1;ÍNDICE(–(B3:B14″”);0);0);1). Como utilizo ela em colunas? Obrigado!

    PRODT2 1 4 Trazer o primeiro valor preenchido = 1
    FSFF 44 23 Trazer o primeiro valor preenchido = 44
    DFDF 4 Trazer o primeiro valor preenchido = 4

  2. Desculpe, Como utilizo em “Linhas”. Quando insiro a fórmula, ele só está trazendo se a primeira célula do intervalo estiver preenchida, caso contrário, ele retorna erro. Obrigado!!

    1. Gabriel Stelling Pinheiro

      vamos dizer que vc tenha o intervalo A1:D1, e a primeira celula preenchida seja C1
      a fórmula vai ficar:

      =INDEX(A1:D1;1;MATCH(1;INDEX(–(A1:D1″”);0);0))

    2. Boa noite.
      Suas dicas funcionaram muito bem. Obrigado, mas fiquei com uma dúvida. Gostaria de usar o endereço encontrado em uma fórmula, mas não estou conseguindo.
      Gostaria de usar o endereço encontrado pela sua fórmula: =ENDEREÇO(PROC(2;1/–(C2:C1000″”);LIN(B2:C1000));2)
      na função: =CONTAR.VAZIO($B$2:B44), mas quando aplico a fórmula no lugar de B44, dá erro.
      Como faço para usar o =CONTAR.VAZIO(XX:com esse intervalo achado por uma busca que verificar o ultimo endereço preenchido?

    3. Rafael Dalvi Traesel

      Muito bom, mas gostaria de saber última célula preenchida usando também o índice+corresp , pois preciso deste dado informando o cabeçalho. Por exemplo, em uma planilha com membros de comissão, saber o último presidente desta comissão. Então informando no índice+corresp a palavra “presidente”

    4. Rafael Dalvi Traesel

      Muito bom, mas gostaria de saber última célula preenchida usando também o índice+corresp , pois preciso deste dado informando o cabeçalho. Por exemplo, em uma planilha com membros de comissão, saber o último presidente desta comissão. Então informando no índice+corresp a palavra “presidente”

    5. Muito obrigado pelas dicas. Excelentes! Gostaria de saber se tem algum artifício que permita buscar o dado da segunda célula preenchida e assim por diante – terceira, quarta, etc..

    6. Obrigado pelas dicas……mas gostaria de uma solução parecida…EX…..numa célula tenho um numero que varia conforme uma segunda planilha..ora é o n.3, ora é o n.5…assim por diante… esta célula variavel deve ser minha referencia para somar os 3 primeiros numeros de uma coluna…e quando variar para 5, somar os 5 primeiros numeros da mesma coluna

    7. ola, por favor como faço para a formula funcionar e localizar em varias colunas, a primeira coluna que tiver um valor digitado, ou seja a primeira que nao esteja vazia?

Deixe um comentário ou uma dúvida