Curva ABC Excel

Curva ABC Excel

No post de hoje, você irá aprender como criar uma Curva ABC utilizando o Excel. Mais precisamente, a tabela dinâmica. Caso desconheça o que seja, recomendamos que você veja nosso post explicativo de como fazer uma tabela dinâmica. Ou mesmo assista ao nosso vídeo instrutivo, disponível em nosso canal.

A Curva ABC é grandemente utilizada nas empresas, no controle de estoque, custo, além disso, permite que você programe melhor suas compras, reconheça os produtos críticos para sua empresa. Fazendo dessa forma, um método de classificação de informações.

Construindo a Curva ABC

Para compreendermos melhor como irá funcionar, irei utilizar uma planilha de exemplo, onde caso você queira ir acompanhando, pode baixar logo abaixo:

[DOWNLOAD]

Na imagem abaixo, temos um exemplo de tabela. No entanto, não podemos usar ela para fazer a curva ABC. Por mais, que ela seja uma tabela, temos que “dizer” isso ao Excel, formatando-a como tabela dinâmica.

Para fazer isso, vá em >Inserir > Tabela > Selecione o Intervalo

Dessa forma o Excel já consegue confirmar que é realmente uma tabela. Após isso, precisamos gerar uma tabela dinâmica. Façamos então, da seguinte maneira:

Então, você verá o seguinte menu, clique em ok:

Configurando a Tabela Dinâmica

Primeiro de tudo, vamos selecionar somente produto como o principal item avaliado. Colocando ele no Rótulos de Linha. E valor de Vendas, colocando então na aba valores. Observe:

Para executar estas ações, é necessário clicar e manter pressionado com o botão esquerdo do mouse sobre o item.

Em seguida, vamos formatar o Campo de Valor. Para fazer isso, clique sobre a região “Soma de Valor” e então, configurações de campo de valor. E depois ir na opção “Formato do Número”. Veja:

Selecionamos então, o formato como “moeda”. Após selecionado, clique em “Ok”.

Agora veja, que a Coluna Campo de Valor ficaram no formato moeda, e não mais em número. Dessa forma, temos então, o valor monetário acumulado para cada um dos produtos vendidos naquele mês.

Nesse sentido, como se trata de Curva ABC, precisamos fazer uma classicação decrescente(do maior para o menor). E para fazer isso, é simples! Clique no filtro do Rótulos de Linha, em seguida Mais opções de Classificações, e então selecione a ordem Decrescente, na opção Soma de Valor.

E então, já é possível visualizar a nossa alteração:

Inserindo o Percentual acumulado

Continuando na mesma linha de raciocínio ABC, nós precisamos do percentual acumulado, colocando ao lado da coluna B. Para fazer isso, iremos teoricamente, repetir os processos anteriormente, no entanto, com algumas mudanças.

Vamos colocar então novamente, o item valor na área “valores”. E você verá o mesmo procedimento, e será gerado mais um campo, que ficará na coluna C. Observe:

E na configuração da tabela dinâmica, clicamos no item Soma de Valor2, e então Configurações do Campo de Valor.

Em seguida, clicamos na opção Mostrar Valores Como > % da Soma acumulada em. E após isso, é só clicarmos em “Ok”.

Classificação da Tabela Dinâmica

Veja então, que os valores chegam até 100%. E em seguida, vamos utilizar como critério de classificação o pareto 80/20. Dessa forma, digitamos ao lado da Coluna C, “Classificação”. E iremos utilizar a função SES, que nada mais é que uma fórmula que nos permite colocar diversas condicionais. Caso desconheça a função SE, clique no link em azul.

Vamos utilizar a seguinte fórmula:

^{=SES(C2<20%;"A";C2<50%;"B";C2<=100%;"C")}

Na fórmula, você pode estar pensando que eu selecionei a célula C2, contudo, como estamos trabalhando com a tabela dinâmica, precisamos digitar a célula que sofrerá a ação da fórmula. Em nosso caso é a célula C2. O que “passamos” para o Excel foi basicamente: Se a célula C2 for menor que 20% então retorne o valor A; E se a C2 for menor que 50%, retorne B; mas caso C2 seja menor ou igual a 100% me retorne C. Veja que é uma fórmula bem simples e fácil, mas que precisamos ter alguns cuidados. Por exemplo, sempre quando queremos que o Excel nos retorne algum texto, devemos colocar as aspas duplas(“), antes e após o final do texto(em nosso caso, da letra).

Após digitado a fórmula, basta clicarmos duas vezes sobre o ponto da célula D2. E automaticamente a fórmula será reproduzida até o final.

Agora nossa tabela está classificada no pareto 80/20, e a tabela em si já está pronta. Vamos então para o Gráfico!

Construindo o Gráfico da Curva ABC

Agora que nossa tabela está terminada, é bem simples para adicionarmos o gráfico de pareto. Confira os passos a seguir:

1 – Clique sobre a Tabela dinâmica
2 – Navegue até a aba Inserir
3 – Selecione a Opção de Gráficos
4 – Selecione o Gráfico de Barras

E você deve obter esse resultado:

Mas nesse caso, não queremos esse tipo de gráfico. Vamos então escolher a opção combinação, alterando o tipo de gráfico. Dessa forma:

Após ter selecionado o gráfico combinação, você precisa selecionar o item Soma de Valor2, como Eixo Secundário, clicando na caixa de seleção, como representado na imagem acima. E então, só clicar em “Ok”. Veja na imagem a seguir, que nosso gráfico já tem um novo formato, e já nos permite visualizar nossa curva ABC. Nesse momento, já podemos dizer que temos um gráfico de Curva ABC. Mas se tiver que apresentar para algum gestor, ou mesmo para sua empresa, o visual não é tão agradável. Dessa forma você pode personalizar da forma que se torne atrativo aos olhos dos usuários. Caso não tenha muitas ideias de formatação, pode conferir nosso post de como criar gráficos, clicando também no link em azul.

Ou caso queira personalizar do modo que iremos ensinar, você pode continuar acompanhando.

Formatação do Gráfico(oferecendo um visual mais atrativo)

Um gráfico que é pouco vísivel, e possui dados minúsculos, dependendo da ocasião, não torna muito eficiente. Já que todo gráfico tem como objetivo o resumo de informações. Você pode remediar este problema, aumentando seu gráfico, ajustando pelos pontos de dimensão dele.

Prosseguindo, podemos colocar uma forma, indicando os parâmetros A, B e C, para uma melhor visualização dos itens. Veja:

Depois de adicionado, sabemos que a letra A, corresponde a linha 20(linha do 20%), que estará no primeiro intervalo. Veja:

E em seguida, podemos diminuir a transparência. Para isso clique sobre o gráfico com o botão direito do mouse > Formatar Objeto> Selecione a Transparência desejada.

E aplicamos o mesmo processo para as letras B e C. E teremos como resultado:

O diferencial desse formato, é que se nós adicionarmos, mais uma linha da tabela, não desconfigura nosso gráfico, nem nossa tabela dinâmica, pois como o nome sugere, os dados também são dinâmicos. Classificando o item que foi adicionado de forma automática, e inserindo na tabela. Formatando logo após, o gráfico também de forma automatizada.

E por aqui temos nosso gráfico de curva ABC. Ficou com dúvidas? Não tem problema, assista ao nosso vídeo instrutivo, que também ensina passo a passo.

Outros posts como esse, você continua a conferir em nosso site.