Fórmula genérica
=MATCH(bigtext,range)
Resumo
Para obter a última posição relativa (ou seja, última linha, última coluna) para dados de texto (com ou sem células vazias), você pode usar a função MATCH. No exemplo mostrado, a fórmula em D5 é:
=MATCH(REPT("z",255),B4:B11)
Explicação
Esta fórmula usa a função MATCH no modo de correspondência aproximada para localizar o último valor de texto em um intervalo. Correspondência aproximada ativada definindo pelo terceiro argumento em MATCH como 1 ou omitindo este argumento, que é padronizado como 1.
O valor de pesquisa é um chamado "texto grande" (às vezes abreviado como "texto grande") que é intencionalmente um valor "maior" do que qualquer valor que aparecerá no intervalo. Ao trabalhar com texto, que é classificado em ordem alfabética, isso significa um valor de texto que sempre aparecerá no final da ordem de classificação alfabética.
Como essa fórmula corresponde ao texto, a ideia é construir um valor de pesquisa que nunca ocorrerá no texto real, mas sempre será o último. Para fazer isso, usamos a função REPT para repetir a letra "z" 255 vezes. O número 255 representa o maior número de caracteres que MATCH permite em um valor de pesquisa.
Quando MATCH não consegue encontrar este valor, ele irá "voltar" para o último valor de texto no intervalo e retornar a posição daquele valor.
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.
Última posição relativa vs número da última linha
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:
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".
