Pesquisar Último Valor em Coluna ou Linha – Excel

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

Luciano Farina

Last updated on July 1, 2023

Este tutorial vai te ensinar a procurar o último valor em uma coluna ou linha no Excel.

mf pesquisar ultimo valor coluna linha

Último valor na coluna

Você pode usar a função PROC para encontrar a última célula não vazia em uma coluna.

=PROC(2;1/(B:B<>"");B:B)

funcao-proc

Vamos examinar essa fórmula.

A parte da fórmula B:B<>”” retorna uma matriz contendo valores Verdadeiro e Falso: {FALSO, VERDADEIRO, VERDADEIRO,…}, testando se cada célula da coluna B está em branco (FALSO).

=PROC(2;1/({FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO;VERDADEIRO;VERDADEIRO;FALSO;...);B:B)

Esses valores booleanos se convertem em 0 ou 1 e são usados para dividir 1.

=PROC(2,{#DIV/0!;1;1;1;1;1;#DIV/0!;,B:B)

Esse é o vetor_proc para a função PROC. No nosso caso, o valor_procurado é 2, mas o maior valor no vetor_proc é 1, de modo que a função PROC corresponderá ao último 1 da matriz e retornará o valor correspondente em vetor_result.

Se você tiver certeza de que só tem valores numéricos na coluna, de que os dados começam na linha 1, e de que o intervalo de dados é contínuo, poderá usar uma fórmula um pouco mais simples com as funções ÍNDICE e CONT.NÚM.

=ÍNDICE(B:B;CONT.NÚM(B:B))

funcao indice cont_num

A função CONT.NÚM retorna o número de células preenchidas com dados numéricos no intervalo contínuo (4) e a função ÍNDICE fornece o valor da célula nessa linha correspondente (4ª linha, valor 4444).

Para evitar possíveis erros quando o intervalo de dados contém uma mistura de valores numéricos e não numéricos, ou até mesmo algumas células em branco, você pode usar a função PROC junto com as funções ÉCEL.VAZIA e NÃO.

=PROC(2;1/(NÃO(ÉCÉL.VAZIA(B:B)));B:B)

funcao proc nao ecel_vazia

A função ÉCEL.VAZIA retorna uma matriz que contém valores Verdadeiros e Falsos, correspondentes a 1 e 0. A função NÃO altera Verdadeiro (ou seja, 1) para Falso e Falso (ou seja, 0) para Verdadeiro. Se invertermos essa matriz resultante (ao dividir 1 por essa matriz), obteremos uma matriz que contém novamente erros #DIV/0! e uns, que podem ser usados como matriz de pesquisa (vetor_proc) em nossa função PROC. A funcionalidade da função PROC é, então, a mesma do nosso primeiro exemplo: ela retorna o valor do vetor de resultados na posição do último 1 na matriz de pesquisa.

Quando precisar que o número da linha com a última entrada seja retornado, você pode modificar a fórmula usada em nosso primeiro exemplo junto com a Função LIN em seu vetor_result.

=PROC(2;1/(B:B<>"");LIN(B:B))

funcao proc lin

Último valor na linha

Para obter o valor da última célula não vazia em uma linha preenchida com dados numéricos, talvez você queira usar uma abordagem semelhante, mas com funções diferentes: a função DESLOC juntamente com as funções CORRESP e MÁXIMO.

=DESLOC(ref, lins, cols)

=DESLOC(B2;0;CORRESP(MÁXIMO(B2:XFD2)+1;B2:XFD2;1)-1)

funcao desloc corresp maximo

Vamos ver como essa fórmula funciona.

Função CORRESP

Usamos a função CORRESP para “contar” quantos valores de células estão abaixo de 1 + o máximo de todos os valores na linha2 a partir de B2.

=CORRESP(valor_procurado, matriz_procurada, [tipo_correspondência])

=CORRESP(MÁXIMO(B2:XFD2)+1;B2:XFD2;1)

funcao corresp maximo

O valor_procurado da função CORRESP é o máximo de todos os valores na linha2 + 1. Como esse valor obviamente não existe na linha2 e o tipo_correspondência está definido como 1 (menor ou igual a valor_procurado), a função CORRESP retornará a posição da última célula “verificada” na matriz, ou seja, o número de células preenchidas com dados no intervalo B2:XFD2 (XFD é a última coluna nas versões mais recentes do Excel).

Função DESLOC

Em seguida, usamos a função DESLOC para obter o valor dessa célula, cuja posição foi retornada pela função CORRESP.

=DESLOC(B2;0;C4-1)

funcao desloc