Fórmula genérica
=MATCH(bignum,range)
Resumo
Para obter a última posição relativa (ou seja, última linha, última coluna) para dados numéricos (com ou sem células vazias), você pode usar a função MATCH com o chamado "grande número".
No exemplo mostrado, a fórmula em E5 é:
=MATCH(9.99E+307,B4:B9)
Última posição * relativa *, não linha na planilha
Ao construir fórmulas avançadas que criam intervalos dinâmicos, 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. Observação: 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 no modo de correspondência aproximada para localizar o último valor numérico em um intervalo. Correspondência aproximada habilitada definindo pelo terceiro argumento em MATCH como 1, ou omitindo este argumento, que é padronizado como 1.
O valor de pesquisa é um chamado "grande número" (às vezes abreviado como "bignum") que é intencionalmente maior do que qualquer valor que aparecerá no intervalo.
O resultado é que MATCH irá "voltar" para o último valor numérico no intervalo e retornar essa posição.
Observação: essa abordagem funciona bem com células vazias no intervalo, mas não é confiável com dados mistos que incluem números e texto.
Sobre bignum
O maior número que o Excel pode manipular é 9,999999999999999E + 307.
Ao usar MATCH dessa forma, você pode usar qualquer número grande que seja maior do que qualquer valor no intervalo, por exemplo:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
A vantagem de usar 9,99E + 307 ou similar é que ele é (1) um grande número e (2) reconhecível como um marcador para um "grande número". Você o verá usado em várias fórmulas avançadas do Excel.
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 explica o conceito de "última posição relativa".
