Como fazer Curva ABC – Como criar gráfico curva ABC no Excel

4

Como fazer Curva ABC – Como criar gráfico curva ABC no Excel

Você aprenderá neste artigo como funciona a curva ABC, também chamado de diagrama de Pareto e aprenderá a criar o gráfico de curva ABC no Excel.

A curva ABC é um método de classificação também chamado de 80/20 criado pelo economista italiano Vilfredo Pareto, que tem por finalidade identificar os itens de maior importância ou impacto, os quais são normalmente em um número menor.

O método tem o apelido de 80/20 por que ele separa os itens em três categorias, A, B e C.

Estas categorias por sua vez terão um grupo de dados, a categoria A terá os 20% dos valores com maior representatividade no valor total, a classificação B terá os 30% de maior impacto e a categoria C os outros 50%.

Estes percentuais estão ligados á quantidade, então para uma amostra de 100 itens, teremos:

A -> 20 linhas

B -> 30 linhas

C -> 50 linhas

Após a divisão das informações em categorias é gerado um gráfico que demonstra como é distribuído o valor ao longo da quantidade de itens.

Este gráfico é muito utilizado na avaliação de Estoques, Vendas, Clientes, Fornecedores, Risco em projetos, entre várias outras aplicações.

1. Criando os dados para o gráfico Curva ABC

Para download do modelo pronto coloque seu nome e e-mail ao final do artigo, será gerado um link automaticamente abaixo do formulário e bastará clicar nele.

Para criar o gráfico de curva ABC inicialmente crie uma pequena tabela auxiliar:

Gráfico curva ABC 2

Esta pequena tabela conterá os cálculos necessários para nos auxiliar e nos dar algumas respostas da nossa análise de dados.

A nossa tabela para análise dos dados é semelhante a tabela abaixo. No momento esqueça as colunas que não estão preenchidas, foque apenas nas duas colunas iniciais CLIENTE e VALOR.

Gráfico curva ABC

No nosso modelo iremos realizar uma análise dos clientes que são mais valiosos para a companhia.

Por esta imagem preencha as colunas com as fórmulas:Gráfico curva ABC 3

Coluna Fórmula Motivo
Qtde. =CONT.NÚM($B$2:$B$90)*SOMA(M2:$M$4) Identificar a quantidade de registros de cada categoria. Arraste a fórmula para todas as células da coluna
Valor =SOMASE(H:H;L2;B:B) Esta função realiza a soma condicional dos valores que estão em determinada categoria.
Total =P3+O2 Valor total acumulado das categorias.
Limite =MAIOR($B$2:$B$90;N2) Esta fórmula usa como referência a quantidade de registros de cada categoria e retorna o k maior valor daquela classificação.

 

As fórmulas desta tabela não estão todas funcionando ainda, pois é necessário que sejam preenchidas as classificações da tabela principal para que as somas condicionais funcionem.

Na tabela de valores classifique-a pela coluna de valor, antes de iniciar as fórmulas.

Coluna Fórmula Motivo
CLAS =PROCV(B2;$K$2:$L$4;2;1) Esta função retorna a classificação da categoria baseada no valor da lista e no limite da coluna Qtde da tabela auxiliar.

Note o uso do PROCV aproximado para realizar a consulta. Par que funcione é necessário que os valores estejam em ordem crescente.

%Valor =B2/SOMA($B$2:$B$90) A fórmula realiza o cálculo de quanto o valor da lista representa percentualmente do total da coluna de valor.
%Acum. =B2/SOMA($B$2:$B$90)+SE(ÉNÚM(F1);F1;0) Soma o percentual acumulado dos valores até aquele item da lista.
%Categ. =SE(C2<>””;F2-SOMA($G$1:G1);””) Esta função auxiliar irá marcar apenas as linhas dos limites das categorias. Será utilizada no gráfico para destacar estes valores.
Separação =SE(H2<>H3;CONT.VALORES($B$2:B2);NÃO.DISP()) Retorna a contagem dos valores que estão também nos limites das categorias A, B e C. Serão utilizados no gráfico da Curva ABC
Qtde =SEERRO(D2;””) Esta coluna irá ser utilizada como auxiliar no gráfico da curva ABC

 

Se você já pesquisou na internet a respeito deste gráfico há várias formas de criá-lo, no nosso caso nós criamos uma estrutura que permitirá a separação visual das categorias A, B e C com uma cor de fundo, como na imagem abaixo do gráfico ABC.

Gráfico curva ABC 4

2. Criando o gráfico Curva ABC

Como os dados estão agora todos preparados basta criarmos o nosso gráfico.

  1. Selecione os dados da coluna Separação;
  2. Pressione as teclas Ctrl+F1, o Excel irá criar um gráfico com estes dados;
  3. Clique com o botão direito sobre o gráfico criado e altere o tipo de gráfico para Barras Horizontais 100% Empilhadas;Gráfico curva ABC 5
  4. Clique com o botão direito sobre a barra criada do gráfico e selecione a opção Formatar série de dados;
  5. Altere a opção Largura do Espaçamento para 0%, desta forma a série irá ocupar todo o gráfico. Altere as cores como desejar, a primeira refere-se a classificação A, a segunda a classificação B e a terceira a classificação C;Gráfico curva ABC 6
  6. Selecione o gráfico e clique na barra Design do gráfico no botão Selecionar Dados;
  7. Clique no botão Adicionar;
  8. Em Valores da série selecione os valores da colunas %Acum.;
  9. Clique em OK;
  10. Veja que foi criado um gráfico desformatado, clique sobre este gráfico e na opção Formatar Série de Dados. Em Opções de Série, selecione a opção Plotar série no Eixo Secundário;
  11. Clique com o botão direito sobre qualquer parte do gráfico e clique em Alterar tipo de gráfico;
  12. No último item da lista altere para gráfico de linha, veja como os dados ficam.Gráfico curva ABC 7
  13. Neste ponto nós já conseguimos ver algumas informações, como que na classificação A temos mais de 60% de todo o faturamento. Ou seja, 20% dos clientes são responsáveis por mais de 60% do faturamento total da empresa.
  14. Clique sobre cada uma das barras de fundo e clique em Adicionar Rótulo de Dados, veja que o gráfico incluirá a quantidade de clientes que estão naquela categoria.
  15. Agora clique sobre o gráfico de linha e em Adicionar Rótulo de Dados, veja que os dados são todos preenchidos. Nas Opções do conteúdo do Rótulo de dados desmarque todas e clique em Selecionar Intervalo. Selecione a coluna %Categ. Assim serão destacados apenas os limites percentuais das categorias do gráfico.
  16. Se desejar adicione também o Eixo X Secundário, ele será exibido acima com os valores. Para espaçá-los clique na opção Rótulos Intervalo entre os rótulos e altere a propriedade Especificar unidade do intervalo para 5.Gráfico curva ABC 4

 

4. DOWNLOAD

Você pode fazer o download da planilha digitando o seu e-mail para assinar a nossa newsletter gratuita abaixo.

DOWNLOAD DESTA PLANILHA E NEWSLETTER GRATUITA GUIA DO EXCEL:

Seu nome (obrigatório)

Seu e-mail (obrigatório)

Abraço

Marcos Rieper

4 COMENTÁRIOS

  1. Marcos, parabéns por mais um excelente artigo!

    Só um detalhe: o atalho para gráficos está como Ctrl+F1, e na verdade é Alt+F1!

    (deve ter passado despercebido, pois com certeza você sabe melhor que eu rs)

    Abraços

  2. Uma dúvida: quando adiciono novos valores, o gráfico desconfigura. Como torná-lo dinâmico? Tentei passar tudo para a referência B:B, mas não sei fazer o gráfico de colunas empilhadas se ajustar

DEIXE UMA RESPOSTA

Please enter your comment!
Please enter your name here