Neste artigo vou ensinar criar um gráfico que redimensiona automaticamente a(s) série(s) conforme lançamento de dados em suas categorias usando os conceitos da função DESLOC e de Nomes do Excel 2013.
Quando criamos um gráfico, usualmente definimos um intervalo de dados fixo para compor as séries a serem exibidas e o gráfico é gerado com todas as entradas do intervalo, independente de existir ou não um valor na categoria. Imagine a situação em que você tenha um gráfico que mostra cada mês do ano nas categorias e uma série contendo a soma das vendas realizadas. Para compor o gráfico você precisa de uma tabela de origem contendo os meses e os valores, como por exemplo na imagem abaixo:
Imagine que estamos no mês de março/16 e portanto não existem vendas realizadas a partir de abril/16. Como os valores estão sendo calculados via fórmulas que processam dados de uma tabela de vendas, os meses a partir de abril/16 estão recebendo o valor zero, que são plotados no gráfico, podendo gerar uma falsa interpretação de dados, principalmente pela queda que há entre o mês de março e abril.
Antes de montar o gráfico que se ajustará dinamicamente, vou dar duas dicas para quem opta por manter o gráfico mostrando a categoria completa:
DICA 1: Use a função NÃO.DISP() para não plotar os valores não realizados no gráfico:
Observe que a fórmula é uma condição para caso a função SOMASES traga “zero”, retorne a função NÃO.DISP(), e caso diferente de “zero, faça a soma de vendas do mês.
DICA 2: Para omitir a expressão de erro #N/D das células, aplique uma formatação condicional com a função logica ÉERROS no intervalo formatando a cor da fonte e o preenchimento das células para a cor usada no fundo da planilha, que por padrão é branco:
Dessa forma, se sua tabela de origem do gráfico fica visível no relatório, a visualização fica mais agradável sem os #N/D.
Voltando a explicação para montar o gráfico que se ajusta dinamicamente, o próximo é criar a fórmula que irá definir o intervalo de dados realmente realizado. A função que nos possibilita isso é a função DESLOC.
Há duas possibilidades de intervalos para compor. Quando criamos um gráfico e abrimos a janela de “Fonte de Dados”, temos a possibilidade de definir o “Intervalo de dados do gráfico”, as “Entradas de Legenda (Série)” e “Rótulo do eixo horizontal (Categorias)”:
Não precisamos nos preocupar com esta última opção de categorias, pois o tamanho da categoria será definido pelo tamanho da série, por isso não será problema manter as categorias de janeiro a dezembro.
Na teoria, faria sentido utilizar uma fórmula com a função DESLOC direto nas caixas da janela de Fonte de dados do Excel, mas se tentar fazer isso, verá que o Excel não permite e dá a mensagem de erro:
Neste exemplo, vou criar uma fórmula com DESLOC para compor o intervalo da série desejado, que na última imagem do gráfico postada acima é o intervalo C3:C5 (Vendas de Janeiro a março). E, após desenvolver a fórmula, vamos criar um nome usando esta fórmula como referência.
Não vou aprofundar a explicação sobre a função DESLOC, recomendo que estude bem ela, pois ela é como um canivete suíço no Excel, servindo para muitas utilidades.
A dica aqui é criar a fórmula primeiro na planilha para facilitar o desenvolvimento dela. No meu modelo, a fórmula que vou aplicar para resultar no intervalo C3:C5 será:
=DESLOC($C$2;1;0;MÁXIMO(SE($C$3:$C$14<>0;LIN($C$3:$C$14);""))-LIN($C$2);1)
Breve explicação da fórmula:
- Argumento [ $C$2 ]: Referência do ponto de partida do deslocamento
- Argumento [ 1 ]: Deslocamento de 1 linha para baixo, ou seja, o intervalo gerado pelo DESLOC começará em C3. Poderíamos colocar este argumento como zero se o 1º argumento fosse C3, é questão de escolha pessoal, eu particularmente prefiro iniciar o deslocamento do cabeçalho da tabela.
- Argumento [ 0 ]: Nenhum deslocamento de coluna, pois meu ponto de partida indicado já está na coluna do intervalo da série que desejo. Este argumento poderia ser ignorado, ficando dois ponto e vírgulas seguidos na fórmula (;;).
- Argumento [MÁXIMO(SE($C$3:$C$14<>0;LIN($C$3:$C$14);””))-LIN($C$2)]: Este é o argumento que define a altura do deslocamento. Na fórmula, a função MÁXIMO está sendo aplicada de modo matricial. A função MÁXIMO está se encarregando de trazer o número da última linha que contém algum número diferente de zero, e a função LIN está trazendo o número da linha do cabeçalho da tabela, e com a subtração entre esses valores chega-se a quantidade desejada da altura do intervalo. Eu utilizei esta função para ficar uma resposta completa, resolvendo exceções que podem acontecer dependendo da aplicação do gráfico. Neste argumento, você pode utilizar diversas outras funções para calcular a altura desejada do intervalo da séria, como por exemplo, CONT.SE para contar valores maiores que zero e CONT.VALORES para contar os valores caso as informações sejam inseridas manualmente.
- Argumento [ 1 ]: É onde definimos a largura do intervalo, que no caso, é somente 1 coluna.
Com a fórmula desenvolvida, copie a fórmula (copie o texto da fórmula no modo de edição da célula, e não a célula em si) e acesse o “Gerenciador de nomes” na aba de “Fórmulas” e clique em “Novo…” e surgira a janela abaixo:
Não se preocupe se vir campos preenchidos nos campos, pois você irá substituir eles.
- No campo nome, defina um novo nome, que aqui vou chamar de Série_Vendas.
- No campo “Refere-se a:” cole o texto da fórmula desenvolvida com a função DESLOC.
- E o ponto mais importante, altere o escopo de Pasta de trabalho para o nome da planilha onde está o gráfico.
Veja no meu modelo como ficou:
NOTA: Apesar da fórmula desenvolvida ser matricial, no gerenciador de nomes não será necessário inserir com CTRL+SHIFT+ENTER, como usualmente se faz com uma fórmula matricial na planilha. Neste caso o gerenciador já entenderá que esta é uma fórmula matricial.
Após criar o nome, você pode reparar que todos intervalos da fórmula que não tinham referência de planilha receberam agora o nome da planilha do escopo na fórmula. Feche a janela de nomes e entre na janela de “Seleção de Dados” do gráfico previamente criado, e clique em “Editar” na seção da Série. Clique no campo “Valores da Série”, apague o intervalo existente e pressione a tecla F3 para aparecer a relação de Nomes criados. Localize o nome “Série_Vendas”e clique OK. Confirme todo o resto e pronto!
Agora o gráfico está modificado para responder dinamicamente as séries que existem lançamentos de vendas. Observe:
Observe a razão de eu ter feito o DESLOC com a contagem da altura de modo matricial. Imagine que não houve vendas em maio/17 e houve vendas em junho/17:
Se eu tivesse aplicado um método para definir a altura com a contagem de valores diferentes de zero, eu teria como resultado o número 5, sendo que neste cenário é necessário que a altura seja de 6 linhas (jan a jun). Por isso é necessário avaliar corretamente em que situação o gráfico será aplicado para evitar todas exceções.
Segue o link do arquivo para download:
gráfico-redimensionado-automaticamente