Fórmula genérica
(=MATCH(2,1/(range"")))
Resumo
Para obter a última posição relativa (ou seja, última linha, última coluna) para dados mistos que podem conter células vazias, você pode usar a função MATCH conforme descrito abaixo.
Observação: esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter.
No exemplo mostrado, a fórmula em E5 é:
(=MATCH(2,1/(B4:B10"")))
Última posição * relativa *, não linha na planilha
Ao construir fórmulas mais avançadas, geralmente é necessário descobrir a última localização dos dados em uma lista. Dependendo dos dados, esta pode ser a última linha com dados, a última coluna com dados ou a interseção de ambas. Queremos a última * posição relativa * dentro de um determinado intervalo, não o número da linha na planilha:
Explicação
Esta fórmula usa a função MATCH configurada para encontrar a posição da última célula não vazia em um intervalo.
Trabalhando de dentro para fora, a matriz de pesquisa dentro de MATCH é construída assim:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Nota: todos os valores na matriz são 1 ou # DIV / 0! erro.
MATCH é então definido para corresponder ao valor 2 no "modo de correspondência aproximada", omitindo o terceiro argumento.
Como o valor de pesquisa de 2 nunca será encontrado, MATCH sempre encontrará o último 1 na matriz de pesquisa, que corresponde à última célula não vazia.
Esta abordagem funcionará com qualquer tipo de dado, incluindo números, texto, datas, etc. Também funciona com strings de texto nulas que são retornadas por fórmulas como esta:
=IF(A1<100,"")
Gama dinâmica
Você pode usar esta fórmula para criar uma faixa dinâmica com outras funções como INDEX e OFFSET. Veja os links abaixo para exemplos e explicação:
- Faixa dinâmica com INDEX e COUNTA
- Faixa dinâmica com OFFSET e COUNTA
A inspiração para este artigo veio do excelente livro de Mike Girvin Control + Shift + Enter, onde Mike faz um ótimo trabalho explicando o conceito de "última posição relativa".
