Pesquisar Último Valor na Coluna ou Linha - Excel

Baixar exemplo de pasta de trabalho

Baixe a apostila de exemplo

Este tutorial irá ensiná-lo a pesquisar o último valor em uma coluna ou linha no Excel.

Último valor na coluna

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

1 = LOOKUP (2,1 / (B: B ""), B: B)

Vamos examinar esta fórmula.

A parte da fórmula B: B ”” retorna uma matriz contendo valores True e False: {FALSE, TRUE, TRUE,…}, testando cada célula na coluna B está em branco (FALSE).

1 = PROCURA (2,1 / ({FALSO; VERDADEIRO; VERDADEIRO; VERDADEIRO; VERDADEIRO; VERDADEIRO; FALSO; …), B: B)

Esses valores booleanos são convertidos em 0 ou 1 e são usados ​​para dividir 1.

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

Este é o lookup_vector para a função LOOKUP. Em nosso caso, lookup_value é 2, mas o maior valor no lookup_vector é 1, portanto, a função LOOKUP corresponderá ao último 1 na matriz e retornará o valor correspondente no result_vector.

Se você tiver certeza de que possui apenas valores numéricos em sua coluna, seus dados começam na linha 1 e seu intervalo de dados em contínuo, você pode usar uma fórmula um pouco mais simples com as funções INDEX e COUNT.

1 = ÍNDICE (B: B, CONTAGEM (B: B))

A função COUNT retorna o número de células preenchidas com dados no intervalo contínuo (4) e a função INDEX fornece o valor da célula na linha correspondente (4ª).

Para evitar possíveis erros quando seu intervalo de dados contém uma mistura de valores numéricos e não numéricos, ou mesmo algumas células em branco, você pode usar a função LOOKUP junto com as funções ISBLANK e NOT.

1 = LOOKUP (2,1 / (NÃO (ISBLANK (B: B))), B: B)

A função ISBLANK retorna uma matriz contendo valores True e False, correspondentes a 1's e 0's. A função NOT muda True (ou seja, 1) para False e False (ou seja, 0) para True. Se invertermos este array resultante (ao dividir 1 por este array), obtemos um array de resultado contendo novamente # DIV / 0! erros e 1's, que podem ser usados ​​como array de pesquisa (lookup_vector) em nossa função LOOKUP. A funcionalidade da função LOOKUP é então a mesma do nosso primeiro exemplo: ela retorna o valor do vetor de resultado na posição do último 1 na matriz de pesquisa.

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

1 = LOOKUP (2,1 / (B: B ""), ROW (B: B))

Último valor na linha

Para obter o valor da última célula não vazia em uma linha preenchida com dados numéricos, você pode querer usar uma abordagem semelhante, mas com funções diferentes: a Função OFFSET junto com as funções MATCH e MAX.

1 = OFFSET (Referência, Linhas, Colunas)
1 = OFFSET (B2,0, CORRESPONDÊNCIA (MAX (B2: XFD2) + 1, B2: XFD2,1) -1)

Vamos ver como essa fórmula funciona.

Função MATCH

Usamos a função MATCH para “contar” quantos valores de célula estão abaixo de 1 + o máximo de todos os valores na linha 2 começando em B2.

1 = MATCH (lookup_value, lookup_array, [match_type])
1 = CORRESPONDÊNCIA (MAX (B2: XFD2) + 1, B2: XFD2,1)

O lookup_value da função MATCH é o máximo de todos os valores na linha2 + 1. Uma vez que este valor obviamente não existe na linha2 e o match_type é definido como 1 (menor ou igual a lookup_value), a função MATCH retornará o a posição da última célula “marcada” na matriz, ou seja, o número de células preenchidas com dados no intervalo de B2: XFD2 (XFD é a última coluna nas versões mais recentes do Excel).

Função OFFSET

Em seguida, usamos a função OFFSET para obter o valor desta célula, cuja posição foi retornada pela função MATCH.

1 = OFFSET (B2,0, C4-1)

wave wave wave wave wave