Como criar um intervalo nomeado dinâmico no Excel?

Normalmente, os Intervalos Nomeados são muito úteis para os usuários do Excel. Você pode definir uma série de valores em uma coluna, dar um nome a essa coluna e, em seguida, pode se referir a esse intervalo pelo nome, em vez de usar suas referências de célula. No entanto, na maioria das vezes, você precisará adicionar novos dados para expandir os valores de dados do intervalo referenciado no futuro. Nesse caso, você terá que voltar ao menu Fórmulas > Gerenciador de Nomes e redefinir o intervalo para incluir o novo valor. Para evitar isso, você pode criar um intervalo nomeado dinâmico, o que significa que não será necessário ajustar as referências de célula toda vez que adicionar uma nova linha ou coluna à lista.

Criar um intervalo nomeado dinâmico no Excel criando uma tabela

Criar um intervalo nomeado dinâmico no Excel com Função

Criar um intervalo nomeado dinâmico no Excel com código VBA

Criar um intervalo nomeado dinâmico no Excel criando uma tabela

Se você estiver usando o Excel 2007 ou versões posteriores, a maneira mais fácil de criar um intervalo nomeado dinâmico é criar uma tabela nomeada do Excel.

Digamos que você tenha um intervalo dos seguintes dados que precisam se tornar um intervalo nomeado dinâmico.

1. Primeiro, vou definir nomes de intervalo para este intervalo. Selecione o intervalo A1:A6 e insira o nome Data na Caixa de Nome, depois pressione a tecla Enter. Para definir um nome para o intervalo B1:B6 como Preçovenda da mesma forma. Ao mesmo tempo, crio uma fórmula =soma(Preçovenda) em uma célula em branco, veja a captura de tela:

2. Selecione o intervalo e clique em Inserir > Tabela, veja a captura de tela:

3. Na caixa de diálogo Criar Tabela, marque Minha tabela tem cabeçalhos (se o intervalo não tiver cabeçalhos, desmarque esta opção), clique no botão OK, e os dados do intervalo serão convertidos em tabela. Veja as capturas de tela:

4. E quando você inserir novos valores após os dados, o intervalo nomeado será ajustado automaticamente e a fórmula criada também será alterada. Veja as capturas de tela a seguir:

Notas:

1. Os novos dados que você inserir devem estar adjacentes aos dados acima, o que significa que não deve haver linhas ou colunas em branco entre os novos dados e os dados existentes.

2. Na tabela, você poderá inserir dados entre os valores existentes.

Criar um intervalo nomeado dinâmico no Excel com Função

No Excel 2003 ou versões anteriores, o primeiro método não estará disponível, então aqui está outra maneira para você. A função OFFSET( ) a seguir pode fazer isso por você, mas é um pouco complicado. Suponhamos que eu tenha um intervalo de dados que contém os nomes de intervalo que defini, por exemplo, A1:A6 o nome do intervalo é Data, e o nome do intervalo B1:B6 é Preçovenda, ao mesmo tempo, crio uma fórmula para o Preçovenda. Veja a captura de tela:

Você pode alterar os nomes dos intervalos para nomes de intervalo dinâmicos com os seguintes passos:

1. Clique em Fórmulas > Gerenciador de Nomes, veja a captura de tela:

2. Na caixa de diálogo Gerenciador de Nomes, selecione o item que deseja usar e clique no botão Editar.

3. Na caixa de diálogo Editar Nome que aparece, insira esta fórmula =OFFSET(Planilha1!$A$1, 0, 0, CONT.VALORES($A:$A), 1) na caixa de texto Refere-se a, veja a captura de tela:

4. Em seguida, clique em OK, e repita a etapa 2 e a etapa 3 para copiar esta fórmula =OFFSET(Planilha1!$B$1, 0, 0, CONT.VALORES($B:$B), 1) na caixa de texto Refere-se a para o nome do intervalo Preçovenda.

5. E o intervalo nomeado dinâmico foi criado. Quando você inserir novos valores após os dados, o intervalo nomeado será ajustado automaticamente e a fórmula criada também será alterada. Veja as capturas de tela:

Nota: Se houver células em branco no meio do seu intervalo, o resultado da sua fórmula estará errado. Isso ocorre porque as células não vazias não são contadas, então seu intervalo será menor do que deveria, e as últimas células do intervalo ficarão de fora.

Dica: explicação para esta fórmula:

• =OFFSET(referência,linhas,colunas,[altura],[largura])
• 
• =OFFSET(Planilha1!$A$1, 0, 0, CONT.VALORES($A:$A), 1)

• referência corresponde à posição inicial da célula, neste exemplo Planilha1!$A$1;
• linha refere-se ao número de linhas que você vai mover para baixo, relativo à célula inicial (ou para cima, se você usar um valor negativo.), neste exemplo, 0 indica que a lista começará a partir da primeira linha para baixo.
• coluna corresponde ao número de colunas que você vai mover para a direita, relativo à célula inicial (ou para a esquerda, usando um valor negativo.), na fórmula do exemplo acima, 0 indica expandir 0 colunas para a direita.
• [altura] corresponde à altura (ou número de linhas) do intervalo começando na posição ajustada. $A:$A, ele contará todos os itens inseridos na coluna A.
• [largura] corresponde à largura (ou número de colunas) do intervalo começando na posição ajustada. Na fórmula acima, a lista terá 1 coluna de largura.

Você pode alterar esses argumentos conforme sua necessidade.

Criar um intervalo nomeado dinâmico no Excel com código VBA

Se você tiver várias colunas, poderia repetir e inserir fórmulas individuais para todas as colunas restantes, mas isso seria um processo longo e repetitivo. Para facilitar, você pode usar um código para criar o intervalo nomeado dinâmico automaticamente.

1. Ative sua planilha.

2. Mantenha pressionadas as teclas ALT + F11, e abrirá a janela Microsoft Visual Basic for Applications.

3. Clique em Inserir > Módulo, e cole o seguinte código na Janela do Módulo.

Código VBA: criar intervalo nomeado dinâmico

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. Em seguida, pressione a tecla F5 para executar o código, e alguns intervalos nomeados dinâmicos serão gerados, que são nomeados com os valores da primeira linha, e também cria um intervalo dinâmico chamado MeusDados que cobre todos os dados.

5. Quando você inserir novos valores após as linhas ou colunas, o intervalo será expandido também. Veja as capturas de tela:

Notas:

1. Com este código, os nomes dos intervalos não são exibidos na Caixa de Nome, para visualizar e usar os nomes dos intervalos convenientemente, instalei o Kutools para Excel, com seu Painel de Navegação, os nomes de intervalo dinâmicos criados estão listados.

2. Com este código, todo o intervalo de dados pode ser expandido vertical ou horizontalmente, mas lembre-se de que não deve haver linhas ou colunas em branco entre os dados ao inserir novos valores.

3. Ao usar este código, seu intervalo de dados deve começar na célula A1.

Artigo relacionado:

Como atualizar automaticamente um gráfico após inserir novos dados no Excel?