Última linha preenchida Excel com critérios

Neste artigo aprenda como retornar a última linha preenchida no Excel usando critérios na fórmula.

1. Contextualizando

Último lançamento, última ocorrência ou última linha são termos que expressam situações que cotidianamente são úteis para realizamos cálculos ou fazermos buscas no Excel. É comum desejarmos saber o último preço praticado, a data mais recente de uma compra de um produto por um cliente ou ainda buscar o dado que, em uma certa coluna, foi o último valor a ser preenchido.

É possível obter os resultados utilizando tabela dinâmica (com ou sem DAX) e Power Query. Este artigo objetiva apresentar caminhos para obter as respostas para as situações citadas utilizando fórmulas com funções nativas do Excel, incluindo matrizes dinâmicas.

Nota: algumas fórmulas apresentadas são matriciais e, a depender da versão do Excel utilizada, necessitará do atalho CTRL + SHIFT + ENTER em seu fechamento.

2. Como exibir a data da última compra de um cliente

Para obter a data da última compra de um cliente (poderia ser a data última da venda de um produto, data do faturamento mais recente em um estado, da última venda realizada por um vendedor etc.) teremos como único critério o próprio cliente e buscamos a maior data (mais recente) relacionada ao mesmo.

Com base nos dados a seguir, note que todos os clientes possuem várias compras definidas por várias datas e produtos diferentes. Dentre as várias compras do cliente Pão de Açúcar, por exemplo, não é correto afirmar que a última compra exibida será a mais recente, visto que não podemos contar com a ordenação dos dados a nosso favor e é até prudente buscar soluções que não dependam da ordenação dos dados.

2.1. Usando a função MÁXIMOSES para a última linha preenchida no Excel

Desejamos saber para cada cliente qual foi a data de sua última compra. Criamos uma tabela com todos os clientes relacionados (de G5 a G8) e desejamos preenchê-la com as datas desejadas (datas mais recentes para cada cliente). A imagem a seguir mostra uma fórmula que retorna corretamente a data mais recente de cada cliente usando a função MÁXIMOSES. Usamos a fórmula =MÁXIMOSES($B$5:$B$21;$C$5:$C$21;G5). 

A função MÁXIMOSES empregada possui em seu primeiro argumento o intervalo das datas, destinado a abranger as datas (de B5 a B21), visto que retornará dentre estas aquela que for a maior, vinculada à condição ou às condições estabelecidas. O segundo argumento é uma coluna contendo critérios, sendo, no nosso caso, a coluna com os nomes dos clientes (de C5 a C21). Por fim, no terceiro argumento, temos o único critério abordado, que é o cliente (na coluna G).

2.2. Aninhando as funções MÁXIMO e SE

Caso a versão do Excel não possua a função MÁXIMOSES, podemos reproduzir o mesmo efeito aninhando as funções MÁXIMO e SE. A fórmula empregada foi =MÁXIMO(SE($C$5:$C$21=G5;$B$5:$B$21)). A imagem a seguir mostra como ficaria a fórmula e os resultados.

2.3. Aninhando as funções MÁXIMO e FILTRO para retornar a última linha preenchida no Excel

A imagem a seguir mostra uma fórmula que retorna corretamente cada data usando o recurso de matrizes dinâmicas do Excel (presente em suas versões mais recentes). Empregamos a poderosa função FILTRO. O primeiro argumento da função FILTRO é destinado a conter tudo aquilo que estará no resultado final, e será reduzido com base nos critérios. Neste caso, é o campo das datas. Dentre todas as datas existentes, a função FILTRO deverá retornar apenas aquelas que se referem ao cliente em questão. Com base nestas datas vinculadas à cada cliente, a função MÁXIMO retorna apenas a maior data, indicando a data mais recente da compra para cada cliente. A fórmula empregada foi =MÁXIMO(FILTRO($B$5:$B$21;$C$5:$C$21=G5)), conforme imagem a seguir.

3. Como exibir o último preço praticado com base no cliente e produto

Para obter o último preço praticado temos como base a data mais recente. Levando em consideração que desejamos o preço de um certo produto ou até mesmo inserir mais condições como o cliente, por exemplo, estaremos incrementando critérios.

Vamos considerar inicialmente que temos um produto em questão, do qual desejamos obter o último preço praticado, independente do cliente. Neste caso, nosso critério é apenas o produto e desejamos obter a data mais recente a ele associada. A imagem a seguir mostra uma primeira solução com a utilização da fórmula =SOMARPRODUTO(($D$5:$D$21=G5)*(MÁXIMO(($D$5:$D$21=G5)*($B$5:$B$21))=$B$5:$B$21)*($E$5:$E$21)).

Uma fórmula alternativa utilizando a função SE poderia ser =SOMA(SE($D$5:$D$21=G5;SE(MÁXIMO(SE($D$5:$D$21=G5;$B$5:$B$21))=B5:B21;E5:E21))).

Outra fórmula alternativa seria =MÁXIMO(FILTRO($E$5:$E$21;($B$5:$B$21=MÁXIMO(FILTRO($B$5:$B$21;$D$5:$D$21=G5)))*($D$5:$D$21=G5))) que usa a função FILTRO.

Por fim, vamos considerar outro cenário em que desejamos saber o preço mais recente praticado na venda de um determinado produto considerando também o cliente em questão. Em relação ao cenário anterior temos uma condição adicional, que é o cliente.

Na imagem a seguir, emprega-se a fórmula =SOMA(SE($B$5:$B$21=MÁXIMO(($D$5:$D$21=$G5)*($B$5:$B$21)*($C$5:$C$21=H$4));SE($D$5:$D$21=$G5;SE($C$5:$C$21=H$4;$E$5:$E$21)))) para retornar corretamente os últimos preços de cada produto em cada cliente.

Diversificando as alternativas e fazendo busca com ÍNDICE e CORRESP, poderemos usar a fórmula =ÍNDICE($E$5:$E$21;CORRESP(1;($B$5:$B$21=MÁXIMO(($D$5:$D$21=$G5)*($B$5:$B$21)*($C$5:$C$21=H$4)))*($D$5:$D$21=$G5)*($C$5:$C$21=H$4);0)).

4. Como exibir o último valor preenchido (diferente de vazio)

Vamos abordar agora como retornar o último valor preenchido em uma coluna de acordo com a ordem de exibição dos dados. Com base nos dados das duas últimas imagens a seguir, pretendemos obter a última nota fiscal lançada de acordo com os dados exibidos, ou seja: a última nota fiscal preenchida.

A nossa primeira solução será com a função PROC, cujas sintaxes estão na imagem a seguir.

Utilizaremos a primeira sintaxe apresentada. Buscaremos pelo valor 2 (primeiro argumento, de nome valor_procurado) no vetor procurado (segundo argumento, de nome vetor_proc). Como este valor não exista neste vetor procurado, será retornado do vetor resultante (terceiro argumento, de nome vetor_result) um valor que corresponda à posição do último valor válido (que não é erro) dentro vetor procurado. A fórmula empregada foi =PROC(2;1/(D5:D21<>"");D5:D21).

Para buscar o cliente e filial da última nota fiscal faturada podemos usar a mesma fórmula, apenas mudando o vetor resultante da função PROC, apontando para a coluna que contém os dados desejados (coluna B para filial e coluna C para cliente).

Uma alternativa usando ÍNDICE está proposta na imagem a seguir. A fórmula utilizada foi =ÍNDICE(D5:D21;MÁXIMO((D5:D21<>"")*(LIN(D5:D21)-LIN(D4)))).

Para buscar o cliente e filial da última nota fiscal faturada podemos usar a mesma fórmula, apenas mudando o primeiro argumento da função ÍNDICE, apontando para a coluna que contém os dados desejados (coluna B para filial e coluna C para cliente).

Download Planilha Última linha preenchida Excel com critérios

Realize o download da planilha de última linha preenchida no Excel neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: