Gráficos dinâmicos usando o OFFSET do Excel e os nomes dos intervalos redimensionados quando os dados são adicionados ou excluídos. Ao contrário de usar tabelas para gráficos dinâmicos, este método é compatível com versões anteriores do Excel.
Utilizar as funções OFFSET e COUNTA do Excel para criar nomes de intervalos dinâmicos ajustados automaticamente quando os dados são adicionados ou excluídos.
Clique para baixar o arquivo de exemplo de tutorial sobre redimensionamento dinâmico de gráficos usando OFFSET e nomes de intervalos. O exemplo inclui tanto dados horizontais como verticais.
O OFFSET e o método de nomes de intervalos requer mais trabalho do que os dois cliques utilizados para uma Tabela, mas dá-lhe controlo sobre tudo o que diz respeito aos dados dos gráficos, como mostra o meu livro “Balanced Scorecards and Operational Dashboards with Microsoft Excel”. Este site, CriticalToSuccess.com, tem exemplos de outras maneiras de usar OFFSET, por exemplo, criando um gráfico de rolagem, controlando gráficos com menus suspensos, e muito mais. Pesquise o CriticalToSuccess.com usando o termo “OFFSET” ou “intervalo dinâmico” para ver exemplos.
Criando um gráfico estático
Inicie seu gráfico dinâmico criando primeiro um gráfico estático como a seguir,
1. Clique dentro do intervalo B7:D12 para que o Excel saiba onde os dados do gráfico estão localizados.
2. Na guia Inserir, no grupo Gráficos, selecione um gráfico simples como um gráfico de coluna, barra ou linha.
Move este gráfico para que você tenha espaço para adicionar dados abaixo do intervalo de dados.
Criar nomes de intervalo dinâmico com OFFSET
Diferentes versões do Excel têm diferentes métodos de criação e edição de fórmulas de nomes de intervalo. As seguintes instruções são para o Excel 2007, 2010 e 2013.
1. Na guia Fórmulas, no grupo Nome Definido, clique em Definir nome. A caixa Definir nome aparece.
Criar nomes de intervalo na caixa Definir nome.
2. Na caixa de texto Nome, digite rngVDate. As convenções de nomenclatura são,
rng Range Name
V Orientação vertical dos dados em B7:B16
Date O tipo de dados no intervalo
3. Escreva um comentário na caixa de texto do comentário que o ajudará a si ou a outros a saber para que serve este nome de intervalo.
4. Na caixa Refere-se à caixa, digite a fórmula OFFSET que calcula o intervalo de dados da Data.
=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Clique em Ok.
Com esta fórmula OFFSET você pode inserir datas adicionais em qualquer lugar no intervalo B7:B16 desde que as novas datas estejam no final da lista antiga de datas sem células em branco entre as datas.
Agora, repita o mesmo processo para criar nomes de intervalo para os dados de Vendas e Unidades.
Os nomes e fórmulas que você adicionará são,
Nome do intervalo de dados Refere-se a
Vendas rngVVVendas =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Unidades rngVUnits =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Clique aqui para saber como OFFSET calcula o tamanho do nome do intervalo dinâmico. Este artigo também lhe dá dicas para verificar os nomes da gama dinâmica.
Clique aqui para saber como OFFSET calcula o tamanho do nome da gama dinâmica. Você comete este erro?
Clique para baixar o arquivo de exemplo do tutorial em gráficos de redimensionamento dinâmico usando OFFSET e nomes de intervalo. O exemplo inclui dados horizontais e verticais.
Inserindo um Range Name na Fórmula de Data do gráfico
Agora você pode tornar o gráfico dinâmico inserindo os nomes dos intervalos que você criou nos gráficos de fórmulas usados para referenciar os dados. Não se preocupe. Isto é muito simples. Na verdade, aprender como fazer isso lhe dá habilidades místicas e o poder de dobrar qualquer gráfico à sua vontade. (Ele também permite que você corrija gráficos que ficam confusos.)
Acima, você criou três nomes de intervalo dinâmico que referenciam os dados a serem usados pelo gráfico,
rngVDate
rngVSales
rngVUnits
A seguir substitua as referências de células usadas pelo gráfico pelos nomes de intervalo dinâmico que você criou,
1. Selecione o gráfico clicando em sua margem.
2. Na guia Desenho de Ferramentas de Gráfico, no grupo Dados, clique na ferramenta Selecionar Dados para exibir a caixa de diálogo Selecionar Fonte de Dados.
A caixa de diálogo Selecionar Fonte de Dados lhe dá controle mágico sobre os dados que um gráfico usa.
3. Sob as Etiquetas de Eixo Horizontal (Categoria), clique em Editar para exibir a caixa Etiquetas de Eixo. É difícil ver a fórmula nesta caixa de diálogo, então clique no ícone do seletor à direita da referência da célula. Isso exibe uma caixa mais ampla de Rótulos de Eixo, na qual você pode fazer referência à fórmula. Seja CARREGADO antes de editar!
A caixa Etiquetas de Eixo mostra a referência para o Eixo Horizontal.
4. Seja CARREGADO! Antes de tentar editar a fórmula, clique dentro da fórmula e pressione a tecla Edit, F2, para que você possa editar a fórmula. Pressione a tecla Fim para ir para o fim da fórmula. Substitua as referências das células após o sinal ! pelo nome do intervalo como mostrado nesta figura.
Sua fórmula deve agora parecer com,
=’Redimensionar dinamicamente os gráficos para Data.xlsx’!rngVDate
A caixa Etiquetas de Eixo fica assim depois de pressionar F2 e editar a fórmula para incluir o nome do intervalo de datas.
TIP: Se você cometer um erro ao editar a fórmula, pressione Ctrl+Z (Desfazer), para voltar.
5. Clique em Ok para retornar à caixa de diálogo Selecionar Fonte de Dados para que você possa adicionar os nomes de Vendas e Unidades.
Inserir um nome de intervalo na Fórmula de Vendas e Unidades do Gráfico
Agora, substitua as fórmulas de Vendas e Unidades do eixo por nomes de intervalo usando quase o mesmo processo.
1. Na caixa de diálogo Selecionar fonte de dados, clique em Vendas e no botão Editar para exibir a caixa de diálogo Editar série.
Substituir a referência da célula Salesdata pelo nome do intervalo.
2. Clique no ícone do seletor à direita da fórmula Valor da série que faz referência à série Sales.
O gráfico agora usa o nome do intervalo dinâmico para referenciar os dados de Vendas.