Primeira e última célula preenchida

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on whatsapp
Share on telegram

Leia também...

Contagem de valores únicos

Contagem de valores únicos O Excel é uma ferramenta incrível, sou suspeito pra falar, mas admito que ainda faltam algumas coisas, algumas funções que ainda

Ler »

Valor Presente e Valor Futuro

Valor Presente e Valor Futuro Calcular o valor futuro e o valor presente no Excel pode ser um pouquinho complicado caso você não saiba exatamente como estes dois são

Ler »

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

E aí, o que achou?

Compartilhe

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on telegram

Planilhas prontas

Bolão da Copa do Mundo FIFA 2018

Com o Bolão da Copa do Mundo FIFA 2018 do Função Excel, você terá um planilha super confiável, segura, fácil de mexer e de visual super profissional para controlar o bolão com seus amigos, familiares e principalmente, os colegas de trabalho.

Saber mais »

Leia também...

Histórico de Cotações

Baixe cotações históricas de ações, índices, moedas, criptomoedas, commodities e fundos em mais de 50 bolsas de valores no mundo, direto no Excel. Selecione os parâmetros desejados e deixe o arquivo fazer o resto para você, de forma rápida, simples e organizada.

Saber mais »
Logo - Ícone

COMPARTILHE COM O MUNDO

Gostou do artigo? Não seja egoísta, compartilhe!

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on whatsapp
WhatsApp

VOCÊ ESTÁ POR DENTRO?

Junte-se a lista de e-mails do Função Excel

Inscreva-se agora