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

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:

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.

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:

ColunaFórmulaMotivo
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+O2Valor 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.

ColunaFórmulaMotivo
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.

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;
  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.
  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.

Planilha de Pequenas Empresas

Você tem uma pequena empresa e precisa de um controle simples e profissional?

Conheça a nossa planilha de Vendas e Estoque.

Funcionalidades

  1. Cadastro de clientes
  2. Configurações
  3. Fornecedores
  4. Metas Vendas
  5. Produtos
  6. Vendedores
  7. Entrada de produto
  8. Saída de estoque
  9. Fluxo de caixa
  10. Vendas

Relatórios

  1. Comissões
  2. Estoque
  3. Fluxo de caixa mensal
  4. Fluxo de caixa Perda/Ganho
  5. Vendas por canal
  6. Vendas por dia
  7. Vendas por mês
  8. Vendas por produto

Dashboards

  1. Dashboard de Vendas
  2. Dashboard de Fluxo de Caixa

Ferramentas

  1. Cálculo de preço do produto
  2. Planilha de orçamento para o cliente

São mais de 20 funcionalidades em uma solução completa para o gerenciamento da sua empresa, clique abaixo para conhecer mais e comprar a planilha.

4. DOWNLOAD

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

Abraço

Marcos Rieper

Avalie este post
Sair da versão mobile