Objetivo: Atualizar um gráfico respeitando os filtros de dados de uma lista, sem que seja necessário redimensionar os dados manualmente.
Um amigo me solicitou uma solução para um problema de um gráfico que seja atualizado automaticamente sempre que fosse alterado o filtro dos dados, como por exemplo sucursais que tenham quantidades diferentes na lista filtrada.
Lembrando que isso também pode ser feito utilizando uma tabela dinâmica com gráfico dinâmico.
Para isso vamos seguir os seguintes passos:
1. Crie uma lista com os dados já sumarizados com um agrupador, como por exemplo sucursais.
2. Aplique o filtro de dados selecionando o cabeçalho da lista e clicando em Dados->Filtro
3. Clique na guia Fórmulas em Gerenciador de Nomes no botão novo e inclua a seguinte fórmula:
- Digite no campo Nome uma informação que represente a lista de dados, no nosso caso será Cidades, pois demonstrará a lista que possui o nome das cidades
- No campo Refere-se a digite a fórmula =DESLOC(Plan1!$B$2;0;0;CONT.VALORES(Plan1!$B:$B)-1;1), que é explicada no artigo : http://guiadoexcel.com.br/lista-de-dados-com-tamanho-dinamico-excel e que faz com que o endereço se refira a intervalos variáveis, que é o que precisaremos aqui, clique em OK.
4. Clique em novo ainda na mesma janela e faça a seguinte operação:
- Digite no campo Nome uma informação que se refira a valores, no caso Valores
- No campo Refere-se a digite a fórmula =DESLOC(Plan1!$C$2;0;0;CONT.VALORES(Plan1!$C:$C)-1;1) para que o intervalo nomeado dos valores também seja variável.
5. Clique na guia Inserir e selecione um tipo de Gráfico
6. Clique no gráfico e na guia Design referente ao gráfico
7. Clique no botão Selecionar Dados
8. Na tela que segue clique no botão Adicionar e em Valores da Série digite =Pasta1!Valores, importante notar que é obrigatório informar o endereço completo, incluindo a pasta da planilha
9. Retornando a tela anterior na parte destinada aos Rótulos do Eixo Horizontal clique em Editar e digite =Pasta1!Cidades
10. Clique em OK e retorne a pasta
11. O gráfico já estará com os dados atualizados conforme o filtro que for aplicado nos dados, mudando por exemplo as sucursais ou as cidades, mas você também pode melhorar o gráfico inserindo o total dos valores apresentados no gráfico por exemplo:
- Digite em algum campo a fórmula =SUBTOTAL(9;C:C) que faz a soma de campos filtrados, mudando o número há outras funções que também podem ser utilizadas
- Selecione o título do gráfico e no campo de fórmulas clique na célula aonde a função foi digitada
Abraço
Marcos Rieper