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:

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.

2. Criando o gráfico Curva ABC

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

Selecione os dados da coluna Separação;
Pressione as teclas Ctrl+F1, o Excel irá criar um gráfico com estes dados;
Clique com o botão direito sobre o gráfico criado e altere o tipo de gráfico para Barras Horizontais 100% Empilhadas;
Clique com o botão direito sobre a barra criada do gráfico e selecione a opção Formatar série de dados;
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;
Selecione o gráfico e clique na barra Design do gráfico no botão Selecionar Dados;
Clique no botão Adicionar;
Em Valores da série selecione os valores da colunas %Acum.;
Clique em OK;
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;
Clique com o botão direito sobre qualquer parte do gráfico e clique em Alterar tipo de gráfico;
No último item da lista altere para gráfico de linha, veja como os dados ficam.
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.
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.
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.
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.