Fórmula genérica
=ROW()-ROW(rng.firstcell)+1>ROWS(rng)-n
Resumo
Para sinalizar as últimas n linhas em um intervalo, você pode usar a coluna auxiliar com uma fórmula baseada nas funções ROW e ROWS. No exemplo mostrado, a fórmula na célula E5, copiada, é:
=ROW()-ROW(INDEX(data,1,1))+1>ROWS(data)-n
onde dados (B5: E15) e n (G5) são intervalos nomeados.
Esta fórmula retorna TRUE se uma linha for uma "última linha n" e FALSE se não for.
Explicação
O núcleo desta fórmula é baseado em outra fórmula que calcula a "linha atual" em um intervalo:
=ROW()-ROW(rng.firstcell)+1
Em resumo, obtemos a linha atual na pasta de trabalho e, em seguida, subtraímos o número da primeira linha do intervalo mais 1. O resultado é um número de linha ajustado que começa em 1. A função INDEX é simplesmente uma maneira de obter a primeira célula em um determinado alcance:
ROW(INDEX(data,1,1) // first cell
Isso é apenas para conveniência. A fórmula pode ser reescrita como:
=ROW()-ROW($E$5)+1
Assim que tivermos o número da linha atual, podemos comparar o número da linha com o total de linhas nos dados menos n:
current_row > ROWS(data)-n
Se esta expressão retornar TRUE, a linha atual é uma das últimas n linhas que estamos procurando.
Últimas n linhas em uma tabela
Se os dados estiverem em uma tabela do Excel, a fórmula pode ser adaptada assim:
=ROW()-@ROW(Table1)+1>ROWS(Table1)-n
A lógica é exatamente a mesma, mas usamos uma abordagem um pouco diferente para obter a primeira célula da tabela:
@ROW(Table1)
Você pode pensar no caractere @ como uma indicação de "único":
=ROW(Table1) // returns (5;6;7;8;9;10;11;12;13;14;15) =@ROW(Table1) // returns (5)
Como antes, a fórmula da tabela retorna TRUE nas "últimas n linhas" e FALSE nas outras.
Última linha apenas
Para testar apenas a última linha, você pode usar uma versão ligeiramente simplificada da fórmula:
=ROW()-ROW(rng.first)+1=ROWS(rng)
