Como criar um intervalo nomeado dinâmico no Excel?
Normalmente, os Intervalos Nomeados são muito úteis para utilizadores do Excel: pode definir uma série de valores numa coluna, atribuir-lhe um nome e, posteriormente, referir-se a esse intervalo pelo seu nome em vez das respetivas referências de células. Contudo, na maioria das vezes, precisará de adicionar novos dados para expandir os valores do seu intervalo referenciado no futuro. Nesse caso, terá de voltar ao Fórmulas > Gerenciador de Nomes e redefinir o intervalo para incluir os novos valores. Para evitar isso, pode criar um intervalo nomeado dinâmico — assim, nunca mais terá de ajustar manualmente as referências de células sempre que adicionar uma nova linha ou coluna à sua lista.
Criar intervalo nomeado dinâmico no Excel criando uma tabela
Criar intervalo nomeado dinâmico no Excel com uma função
Criar intervalo nomeado dinâmico no Excel com código VBA
Criar intervalo nomeado dinâmico no Excel criando uma tabela
Se estiver a utilizar o Excel 2007 ou versões posteriores, a forma mais fácil de criar um intervalo nomeado dinâmico é transformar os seus dados numa tabela do Excel com nome.
Imagine que tem um intervalo com os seguintes dados e precisa de o transformar num intervalo nomeado dinâmico.
1. Em primeiro lugar, vou atribuir um nome a este intervalo. Selecione o intervalo A1:A6 e introduza o nome Data na Caixa de Nome, depois prima a tecla Enter. De seguida, defina também o nome Preçovenda para o intervalo B1:B6. Ao mesmo tempo, insira a fórmula =SOMA(Preçovenda) numa célula vazia — veja a imagem:
2. Selecione o intervalo e clique em Inserir > Tabela; veja a imagem:
3. Na caixa de diálogo Criar Tabela, assinale A minha tabela tem cabeçalhos (se o intervalo não tiver cabeçalhos, desmarque esta opção) e clique no botão OK; os dados do intervalo serão convertidos numa tabela. Veja as imagens:
 |  |  |
4. E quando introduzir novos valores após os dados existentes, o intervalo nomeado ajusta-se automaticamente e a fórmula criada é igualmente atualizada. Veja as imagens seguintes:
 | |  |
Notas:
1. Os novos dados introduzidos devem ser adjacentes aos dados anteriores, ou seja, não pode haver linhas ou colunas em branco entre os novos e os dados existentes.
2. Na tabela, pode inserir dados entre os valores existentes.
Criar intervalo nomeado dinâmico no Excel com uma função
No Excel 2003 ou versões anteriores, o primeiro método não estará disponível, por isso aqui está outra solução. A função DESLOC() pode ajudá-lo nesta tarefa, embora seja um pouco trabalhosa. Suponhamos que tenho um intervalo de dados em que defini nomes de células: por exemplo, A1:A6, cujo nome é Data, e B1:B6, cujo nome é Preçovenda. Ao mesmo tempo, crio uma fórmula para o Preçovenda. Veja a imagem:
Pode transformar o Nome da célula num Nome da célula dinâmico seguindo os passos seguintes:
1. Aceda a Fórmulas > Gestor de Nomes; veja a imagem:
2. Na caixa de diálogo Gerenciador de Nomes, selecione o item que pretende utilizar e clique no botão Editar.
3. Na caixa de diálogo Editar Nome que surge, introduza esta fórmula =DESLOC(Sheet1!$A$1; 0; 0; CONTAR.VAL($A:$A); 1) na caixa de texto Refere-se a; veja a imagem:
4. Depois, clique em OK e repita os passos 2 e 3 para copiar esta fórmula =DESLOC(Sheet1!$B$1; 0; 0; CONTAR.VAL($B:$B), 1) para a caixa de texto Refere-se a do Preçovenda Nome da célula.
5. Assim, os intervalos nomeados dinâmicos foram criados. Ao introduzir novos valores após os dados existentes, o intervalo nomeado ajusta-se automaticamente e a fórmula associada é atualizada em conformidade. Veja as imagens:
| | |
Nota: Se houver células vazias no meio do seu intervalo, o resultado da fórmula ficará incorreto. Isto acontece porque as células não vazias não são contadas, tornando o intervalo mais curto do que deveria ser e fazendo com que as últimas células sejam omitidas.
Dica: explicação desta fórmula:
- =DESLOC(ref;linhas;colunas;[altura];[largura])
- =DESLOC(Sheet1!$A$1; 0; 0; CONTAR.VAL($A:$A); 1)
- refcorresponde à posição inicial da célula; neste exemplo,Sheet1!$A$1;
- linhas refere-se ao número de linhas que irá mover para baixo, relativamente à célula inicial (ou para cima, se utilizar um valor negativo). Neste exemplo, 0 indica que a lista começará na primeira linha abaixo.
- colunas corresponde ao número de colunas a mover para a direita em relação à célula inicial (ou para a esquerda, utilizando um valor negativo). Na fórmula acima, 0 indica que não há expansão de colunas para a direita.
- [altura] corresponde à altura (ou número de linhas) do intervalo que começa na posição ajustada. $A:$A contará todos os itens introduzidos na coluna A.
- [largura] corresponde à largura (ou número de colunas) do intervalo que começa na posição ajustada. Na fórmula acima, a lista terá uma largura de 1 coluna.
Pode adaptar estes argumentos às suas necessidades.
Criar intervalo nomeado dinâmico no Excel com código VBA
Se tiver várias colunas, poderá repetir e introduzir uma fórmula individual para todas as colunas restantes, mas isso seria um processo longo e repetitivo. Para facilitar, pode utilizar um código para criar automaticamente o intervalo nomeado dinâmico.
1. Ative a sua folha de cálculo.
2. Mantenha premidas as teclas ALT + F11 para abrir a janela do Microsoft Visual Basic para Aplicações.
3. Clique em Inserir > Módulo e cole o código seguinte 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. Depois, prima a tecla F5 para executar o código; serão gerados vários intervalos nomeados dinâmicos com os nomes dos valores da primeira linha e também será criado um intervalo dinâmico chamado MeusDados que abrange todos os dados.
5. Quando introduzir novos valores após as linhas ou colunas, o intervalo expande-se automaticamente. Veja as imagens:
 |
 |
 |
Notas:
1. Com este código, os nomes das células não são apresentados na Caixa de Nome. Para visualizar e utilizar facilmente os nomes das células, instalei o Kutools para Excel, cujo Navegação lista os nomes dinâmicos das células criados.
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 introduzir novos valores.
3. Ao utilizar este código, o seu intervalo de dados deve começar na célula A1.
Artigo relacionado:
Como atualizar automaticamente um gráfico após introduzir novos dados no Excel?
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos