Fórmula genérica
=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))
Resumo
Uma maneira de criar um intervalo nomeado dinâmico com uma fórmula é usar a função OFFSET junto com a função CONT.valores. Os intervalos dinâmicos também são conhecidos como intervalos de expansão - eles se expandem e contraem automaticamente para acomodar dados novos ou excluídos.
Nota: OFFSET é uma função volátil, o que significa que ela é recalculada a cada alteração em uma planilha. Com uma máquina moderna e um conjunto de dados menor, isso não deve causar problemas, mas você pode observar um desempenho mais lento em grandes conjuntos de dados. Nesse caso, considere construir um intervalo nomeado dinâmico com a função INDEX.
No exemplo mostrado, a fórmula usada para a faixa dinâmica é:
=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
Explicação
Esta fórmula usa a função OFFSET para gerar um intervalo que se expande e se contrai ajustando a altura e a largura com base em uma contagem de células não vazias.
O primeiro argumento em OFFSET representa a primeira célula nos dados (a origem), que neste caso é a célula B5. Os próximos dois argumentos são deslocamentos para linhas e colunas e são fornecidos como zero.
Os dois últimos argumentos representam altura e largura. A altura e a largura são geradas instantaneamente usando CONT.valores, o que torna a referência resultante dinâmica.
Para altura, usamos a função CONT.valores para contar valores não vazios no intervalo B5: B100. Isso não assume nenhum valor em branco nos dados e nenhum valor além de B100. COUNTA retorna 6.
Para largura, usamos a função CONT.valores para contar valores não vazios no intervalo B5: Z5. Isso pressupõe que não há células de cabeçalho e nenhum cabeçalho além de Z5. COUNTA retorna 6.
Neste ponto, a fórmula se parece com esta:
=OFFSET(B5,0,0,6,6)
Com esta informação, OFFSET retorna uma referência a B5: G10, que corresponde a um intervalo de 6 linhas de altura por 6 colunas de largura.
Observação: os intervalos usados para altura e largura devem ser ajustados para corresponder ao layout da planilha.
Variação com referências completas de coluna / linha
Você também pode usar referências completas de coluna e linha para altura e largura, como:
=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))
Observe que a altura está sendo ajustada com -2 para levar em consideração os valores do cabeçalho e do título nas células B4 e B2. A vantagem dessa abordagem é a simplicidade dos intervalos dentro de CONT.valores. A desvantagem vem do grande tamanho de colunas e linhas cheias - deve-se tomar cuidado para evitar valores errôneos fora do intervalo, pois eles podem facilmente atrapalhar a contagem.
Determinando a última linha
Existem várias maneiras de determinar a última linha (última posição relativa) em um conjunto de dados, dependendo da estrutura e do conteúdo dos dados na planilha:
- Última linha em dados mistos com espaços em branco
- Última linha em dados mistos sem espaços em branco
- Última linha nos dados de texto
- Última linha em dados numéricos
