Destaque Seletivo em Gráfico de Barras no Excel - Curso Gratuito de Dashboard

Nesta quinta aula do Mini Curso de Dashboard você vai montar um gráfico de barras que destaca apenas os produtos que cabem dentro de um limite que você mesmo define. Digita 80% numa célula e o gráfico colore só os itens que respondem por 80% do valor do estoque, deixando o resto em cinza ao fundo. Muda para 90% e mais produtos ganham cor na hora. É a técnica de séries sobrepostas aplicada ao raciocínio da curva ABC, onde poucos itens concentram a maior parte do valor.

A base é uma tabela simples com o nome do produto e o valor de cada um. Um cuidado é obrigatório aqui: a tabela precisa estar ordenada do maior para o menor valor, senão o acumulado não faz sentido e o destaque sai errado.

Como calcular a representatividade de cada produto

O primeiro passo é descobrir quanto cada produto pesa dentro do total. Numa coluna ao lado do valor, divida o valor do item pela soma de todos os valores:

=C6/SOMA($C$6:$C$18)

Trave o intervalo da SOMA com F4 (os cifrões). Isso é o que permite arrastar a fórmula para baixo sem que o intervalo do total escorregue junto com a linha. No exemplo, o produto de R$ 8.000 representa 26% de tudo.

Como conferir se a coluna está correta

Existe um jeito rápido de validar: selecione todas as células de percentual e olhe a barra de status, no canto inferior do Excel. A soma precisa dar exatamente 100%. Se o indicador de soma não estiver aparecendo, clique com o botão direito sobre a barra de status e marque a opção Soma.

Como montar o percentual acumulado

O acumulado é o que conecta esta aula à lógica da curva ABC. Ele responde à pergunta: somando este produto e todos os que vêm acima dele, quanto do total já cobrimos?

Na primeira linha, o acumulado é simplesmente a representatividade do próprio item, já que não há nada acima. Da segunda linha em diante, some a representatividade do item com o acumulado da linha de cima. Arraste a fórmula até o fim e o último produto deve fechar em 100%. Se não fechar, algo está errado nas fórmulas anteriores.

Se você quiser se aprofundar nesse raciocínio de concentração de valor, vale conhecer a planilha de Curva ABC automática, que aplica o mesmo princípio em um material completo.

Como criar a coluna auxiliar que respeita o limite

Agora reserve uma célula para o limite, em algum lugar visível acima da tabela, e escreva o percentual desejado. É esse número que vai comandar o gráfico inteiro.

Em seguida crie a coluna auxiliar com uma função SE. Ela compara o acumulado daquela linha com o limite: se o acumulado for menor, ela devolve o valor do produto. Se não for, devolve vazio.

=SE(E6<$F$3;C6;"")

O detalhe que não pode falhar é travar a célula do limite com F4, prendendo coluna e linha. Sem isso, ao arrastar a fórmula para baixo cada linha passa a comparar contra uma célula diferente e o resultado vira ruído.

Com o limite em 80%, a coluna auxiliar preenche os primeiros produtos e para. O item cujo acumulado chega a 81% já está fora do limite, então fica vazio, junto com todos os que vêm depois. As aspas duplas sem nada dentro representam esse vazio, e é ele que faz a barra colorida não aparecer.

Como criar e ajustar o gráfico de barras

Selecione os dados, vá em Inserir e escolha o gráfico de barras. Ele nasce com três problemas que precisam de conserto, e todos são rápidos.

Invertendo a ordem das categorias

O Excel desenha as barras de baixo para cima, deixando o menor produto no topo, o oposto do que se espera. Clique sobre o eixo com os nomes, depois com o botão direito e vá em Formatar Eixo. No ícone de opções do eixo, marque Categorias em ordem inversa. O maior produto sobe para o primeiro lugar.

Colocando os nomes dos produtos no eixo

Se o eixo estiver mostrando números em vez dos nomes, clique com o botão direito no gráfico, vá em Selecionar Dados e clique em Editar, do lado dos rótulos do eixo horizontal. Aponte para a coluna com os nomes dos produtos e dê OK. Aproveite para selecionar os números do eixo de valores e apagá-los com Delete, já que eles não acrescentam nada quando o destaque é visual.

Deixando as barras de fundo discretas

Clique sobre as barras, depois com o botão direito em Formatar Série de Dados. Reduza a largura do espaçamento para algo em torno de 30%, o que engrossa as barras e aproxima umas das outras. Na lata de tinta, escolha preenchimento sólido em um cinza claro. Essas são as barras dos itens que ficarão fora do limite, então elas precisam ser discretas.

Como sobrepor a série de destaque

Aqui acontece a mágica. Clique com o botão direito no gráfico, vá em Selecionar Dados e clique em Adicionar. Dê à série o nome de Coluna Auxiliar e, nos valores, selecione a coluna auxiliar inteira. Dê OK. Uma segunda série aparece, mas só nos itens que têm valor.

Selecione essa nova série, vá em Formatar Série de Dados e ajuste a Sobreposição de Séries para 100%. Esse é o passo que faz a barra de destaque cair exatamente em cima da barra cinza, em vez de ficar ao lado dela. Por fim, na lata de tinta, escolha uma cor forte para os itens em destaque.

Agora faça o teste. Troque o limite de 80% para 90% e veja mais barras ganharem cor. Reduza para 70% e o gráfico fecha o foco em menos produtos. Nada disso exige tocar no gráfico: tudo passa pela coluna auxiliar, que responde à célula do limite.

Perguntas frequentes

Por que a tabela precisa estar ordenada do maior para o menor? Porque o percentual acumulado soma cada item aos anteriores. Se a ordem estiver bagunçada, o acumulado deixa de representar a concentração de valor e o corte no limite passa a selecionar produtos aleatórios, em vez dos mais relevantes.

O que a sobreposição de 100% faz no gráfico? Ela empilha a série de destaque exatamente sobre a série cinza, ocupando o mesmo espaço. Sem esse ajuste, o Excel desenha as duas barras lado a lado e o efeito de destaque não acontece.

Qual a relação disso com a curva ABC? A curva ABC parte da ideia de que uma minoria dos itens concentra a maior parte do valor. O percentual acumulado é exatamente o cálculo usado nessa classificação, e o limite funciona como o corte entre as faixas. O gráfico só torna esse corte visual.

Consigo baixar a planilha da aula? Sim. A planilha desta aula está disponível para download gratuito, já com a representatividade, o acumulado, a coluna auxiliar e o gráfico prontos para você adaptar ao seu painel.

Baixe a planilha da Aula 05 para praticar com o mesmo modelo do vídeo e siga para as próximas aulas do Mini Curso de Dashboard.