Como criar um intervalo nomeado dinâmico no Excel?

Normalmente, Faixas Nomeadas são muito úteis para usuários do Excel, você pode definir uma série de valores em uma coluna, dar um nome a essa coluna e, em seguida, referir-se a esse intervalo pelo nome em vez de pelas referências de célula. Mas, na maioria das vezes, você precisa adicionar novos dados para expandir os valores dos dados de seu intervalo referido no futuro. Neste caso, você tem que voltar para Fórmulas > Gerente de Nomes e redefina o intervalo para incluir o novo valor. Para evitar isso, você pode criar um intervalo de nome dinâmico, o que significa que não precisa ajustar as referências de célula toda vez que adicionar uma nova linha ou coluna à lista.

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

Crie um intervalo nomeado dinâmico no Excel com função

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

Crie 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 uma série de dados a seguir que precisam se tornar uma faixa nomeada dinâmica.

1. Em primeiro lugar, vou definir nomes de intervalo para este intervalo. Selecione o intervalo A1: A6 e insira o nome Data no nome Box, então aperte Entrar chave. Para definir um nome para o intervalo B1: B6 como Saleprice da mesma forma. Ao mesmo tempo, crio uma fórmula = soma (Saleprice) em uma célula em branco, veja a captura de tela:

2. Selecione o intervalo e clique inserção > mesa, veja a captura de tela:

3. No Criar a tabela caixa de prompt, marque Minha tabela tem cabeçalhos (se o intervalo não tiver cabeçalhos, desmarque-o), clique em OK botão, e os dados do intervalo foram convertidos em tabela. Veja as capturas de tela:

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

Observações:

1. Seus novos dados de entrada devem ser adjacentes aos dados acima, isso significa que não há linhas ou colunas em branco entre os novos dados e os dados existentes.

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

Crie um intervalo nomeado dinâmico no Excel com função

No Excel 2003 ou versão anterior, o primeiro método não estará disponível, então aqui está outra maneira para você. Os seguintes DESLOCAMENTO( ) função pode fazer este favor para você, mas é um tanto problemática. Suponha que eu tenha um intervalo de dados que contém os nomes dos intervalos que defini, por exemplo, A1: A6 o nome do intervalo é Data e B1: B6 nome do intervalo é Preço de venda, ao mesmo tempo, crio uma fórmula para o Preço de venda. Veja a imagem:

Você pode alterar os nomes de intervalo para nomes de intervalo dinâmico com as seguintes etapas:

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

2. No Gerente de Nomes caixa de diálogo, selecione o item que deseja usar e clique em Editar botão.

3. No estalou para fora Editar nome caixa de diálogo, insira esta fórmula = OFFSET (Folha1! $ A $ 1, 0, 0, CONT.valores ($ A: $ A), 1) no Refere-se a caixa de texto, veja a captura de tela:

4. Então clique OKe, em seguida, repita as etapas 2 e 3 para copiar esta fórmula = OFFSET (Folha1! $ B $ 1, 0, 0, CONTAR.VAL ($ B: $ B), 1) no Refere-se a caixa de texto para o Preço de venda nome do intervalo.

5. E os intervalos nomeados dinâmicos foram criados. Quando você insere 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:

Observação: Se houver células em branco no meio do intervalo, o resultado da fórmula estará errado. Isso ocorre porque as células não vazias não são contadas, então seu intervalo será mais curto do que deveria e as últimas células do intervalo serão deixadas de lado.

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

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

• referência corresponde à posição inicial da célula, neste exemplo Folha1! $ A $ 1;
• linha refere-se ao número de linhas que você vai mover para baixo, em relação à célula inicial (ou para cima, se você usar um valor negativo.), neste exemplo, 0 indica que a lista começará na primeira linha para baixo
• coluna corresponde ao número de colunas que você moverá para a direita, em relação à 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, 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.

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

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

1. Ative sua planilha.

2. Segure o ALT + F11 chaves, e abre o Janela Microsoft Visual Basic for Applications.

3. Clique inserção > Móduloe cole o seguinte código no Janela 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. Então aperte F5 para executar o código, e serão gerados alguns intervalos nomeados dinâmicos que são nomeados com os valores da primeira linha e também cria um intervalo dinâmico chamado Meus dados que cobre todos os dados.

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

Observações:

1. Com este código, os nomes dos intervalos não são exibidos no nome Box, para visualizar e usar os nomes dos intervalos convenientemente, instalei Kutools for Excel, Com o seu Painel de Navegação, os nomes de intervalo dinâmico criados sã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 quando você inserir novos valores.

3. Quando você usa 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?