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) |
