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.

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 3ª 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 2ª 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.

---

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 1 e 0, como já vimos anteriormente, e obtemos:

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

Então o número 1 é 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 1 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.

---

Acompanhe o Função Excel