Fórmula genérica
=$A$1:INDEX($A:$A,lastrow)
Resumo
Uma maneira de criar um intervalo nomeado dinâmico no Excel é usar a função INDEX. No exemplo mostrado, o intervalo nomeado "dados" é definido pela seguinte fórmula:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
que resulta no intervalo $ A $ 2: $ A $ 10.
Nota: esta fórmula se destina a definir um intervalo nomeado que pode ser usado em outras fórmulas.
Explicação
Esta página mostra um exemplo de um intervalo nomeado dinâmico criado com a função INDEX junto com a função CONT.valores. Os intervalos nomeados dinâmicos se expandem e contraem automaticamente quando os dados são adicionados ou removidos. Eles são uma alternativa ao uso de uma tabela do Excel, que também é redimensionada conforme os dados são adicionados ou removidos.
A função INDEX retorna o valor em uma determinada posição em um intervalo ou matriz. Você pode usar INDEX para recuperar valores individuais ou linhas e colunas inteiras em um intervalo. O que torna INDEX especialmente útil para intervalos nomeados dinâmicos é que ele realmente retorna uma referência. Isso significa que você pode usar INDEX para construir uma referência mista como $ A $ 1: A100.
No exemplo mostrado, o intervalo nomeado "dados" é definido pela seguinte fórmula:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
que resulta no intervalo $ A $ 2: $ A $ 10.
Como essa fórmula funciona
Observe primeiro que essa fórmula é composta de duas partes que ficam de cada lado do operador de intervalo (:). À esquerda, temos a referência inicial para o intervalo, codificada como:
$A$2
À direita está a referência final para o intervalo, criado com INDEX como este:
INDEX($A:$A,COUNTA($A:$A))
Aqui, alimentamos INDEX com toda a coluna A do array e, em seguida, usamos a função CONT.valores para descobrir a "última linha" no intervalo. COUNTA funciona bem aqui porque há 10 valores na coluna A, incluindo uma linha de cabeçalho. COUNTA, portanto, retorna 10, que vai diretamente para INDEX como o número da linha. INDEX então retorna uma referência a $ A $ 10, a última linha usada no intervalo:
INDEX($A:$A,10) // resolves to $A$10
Portanto, o resultado final da fórmula é este intervalo:
$A$2:$A$10
Uma gama bidimensional
O exemplo acima funciona para um intervalo unidimensional. Para criar um intervalo dinâmico bidimensional em que o número de colunas também seja dinâmico, você pode usar a mesma abordagem, expandida desta forma:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Como antes, COUNTA é usado para descobrir o "lastrow" e usamos COUNTA novamente para obter a "última coluna". Eles são fornecidos para indexar como row_num e column_num respectivamente.
No entanto, para a matriz, fornecemos a planilha completa, inserida como todas as 1048576 linhas, o que permite que INDEX retorne uma referência em um espaço 2D.
Observação: o Excel 2003 oferece suporte a apenas 65535 linhas.
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
