Veja como criar um gráfico de pareto no Excel passo-a-passo com download gratuito da planilha de exemplo.
1. Introdução
O princípio de Pareto, conhecido como regra do 80/20, menciona que aproximadamente 80% dos acontecimentos estão relacionados a 20% das causas.
Trazendo para a prática, esta constatação pode ser verificada em cenários comuns do cotidiano, como o fato de 80% das vendas serem oriundas de 20% dos clientes, ou ainda em relação ao fato de que 80% das riquezas estarem concentradas sob posse de 20% das pessoas.
Podemos representar graficamente a relação, criando no Excel o diagrama de Pareto. Para isso, precisamos de suas séries de dados: uma com as dimensões e seus valores e outra com o percentual de participação acumulada.
Este artigo objetiva orientar como montar o gráfico para expressar o conceito. Há duas abordagens: como montar o gráfico a partir de versões mais anteriores do Excel, usando um gráfico de combinação, e uma alternativa mais direta, com modelo de gráfico pronto para aplicar, presente em versões mais recentes do Excel.
2. Como criar os gráficos
Para apresentar as alternativas para a criação dos gráficos, tomaremos como base os dados a seguir.
Nosso objetivo é criar o gráfico com base na representatividade das Categorias ou Clientes para visualizar melhor aqueles que concentram os maiores volumes de vendas.
2.1. Usando o modelo de gráfico de Pareto (versões mais recentes do Excel)
O Excel em suas versões mais recentes já disponibiliza um modelo apropriado para a criação de nosso gráfico. Podemos acessar este modelo na guia Inserir, no grupo Gráficos. Este modelo não requer que os dados já estejam resumidos, situação que será abordada mais à frente.
A seguir, listamos as 2 etapas suficientes para inserir o gráfico de Pareto a partir de um modelo pronto.
Etapa 1 – Seleção dos dados
Esta etapa é importante, sendo recomendável selecionar apenas os dados que serão efetivamente demonstrados no gráfico. Em nosso caso, selecionamos apenas as 2 últimas colunas: Categoria e Valor.
NOTA: Com os dados devidamente formatados como tabela, é possível atualizar dinamicamente o gráfico com novos dados, uma vez que ele enxerga as colunas por completo, e não um intervalo engessado de células.
Etapa 2 – Escolha do gráfico
Com a seleção dos dados devidamente realizada, acessamos o caminho apontado na imagem a seguir.
Note que o gráfico é instantaneamente inserido.
Pronto. O gráfico já está inserido. Para otimizar o gráfico, recomendamos proceder com uma formatação mínima, que pode envolver, por exemplo, a exibição de rótulos de dados e os eixos verticais.
2.2. Usando gráfico de combinação (versões mais anteriores do Excel)
Este método poderá ser empregado tanto em versões mais anteriores do Excel, quanto em versões mais recentes. Ele é baseado nos dados devidamente organizados, com posterior inserção de um gráfico de combinação, o qual terá uma série de colunas e uma série de linhas.
Este gráfico poderá ser baseado em um intervalo de dados convencional ou ainda em uma tabela dinâmica.
a) Gráfico de Pareto com base em dados resumidos em intervalo normal
Os dados deverão já estar resumidos, como os dados a seguir.
Este resumo pode ser montado com a relação das Categorias somadas com a função SOMASES, por exemplo, tomando como base os dados de estudos apresentados no item 2.
Etapa 1 – Classificação dos dados
Note que os valores das categorias ainda não estão classificados em ordem decrescente. Isto será fundamental para a exibição do gráfico. Partindo destes dados, podemos classificar os dados da última coluna, do Maior para o Menor.
Para isto, podemos usar o autofiltro e classificar. No entanto, um modo rápido de se executar esta classificação é selecionar um dos valores da coluna em questão (por exemplo C3) e acessar a guia Dados, no grupo Classificar e Filtrar, clicando no comando Classificar do Maior para o Menor.
Etapa 2 – Criação da coluna dos percentuais acumulados
Com os dados dos valores devidamente classificados, devemos agora obter a coluna com os percentuais das representatividades acumuladas. Inicialmente, criamos a coluna.
Precisamos obter os percentuais acumulados em cada Categoria. Eles poderão ser obtidos através de fórmula. A fórmula para o cálculo das participações acumuladas será baseada na função SOMA. Nós precisamos dividir a soma acumulada (em azul na próxima imagem) pela soma total (em vermelho na próxima imagem).
O ponto de atenção é que a 1ª soma terá o intervalo que vai do 1º valor até ele próprio (em azul na próxima imagem), sendo que a sua 1ª referência ficará absoluta ($C$3) e a segunda referência ficará relativa (C3).
Basta arrastar a fórmula para as demais células para obter os percentuais relativos a todas as categorias.
Etapa 3 – Inserção do gráfico de combinação
Para inserirmos o gráfico de combinação, inicialmente selecionamos pelo menos uma célula dos dados (não é necessário selecionar todo o intervalo de dados).
Após isso, devemos acessar a guia Inserir e no grupo Gráficos escolher a opção Inserir Gráfico de Combinação.
Dentre as 3 opções disponibilizadas, escolhemos a 2ª opção – Coluna Clusterizada – Linha no Eixo Secundário. Nosso gráfico será inserido.
Etapa 4 – Dicas de formatação
Nosso gráfico pode e deve ter o visual otimizado. Inicialmente podemos inserir os rótulos de dados, deletar os eixos verticais, editar o tamanho do gráfico e cores das séries. A imagem a seguir mostra o resultado de uma edição mínima realizada neste gráfico.
b) Gráfico de Pareto com base em tabela dinâmica
Uma alternativa interessante, principalmente por conta da possibilidade de se utilizar as segmentações de dados, é criar o gráfico de Pareto com base em tabela dinâmica.
Tomando como base os dados do item 2, a nossa tabela dinâmica pode ser montada arrastando o campo de Cliente para o quadrante de linhas, com o campo de Valor inserido duas vezes (isso mesmo, duas vezes) no quadrante de valores, conforme mostra a imagem a seguir.
A princípio, nossa tabela dinâmica parece estar muito estranha e distante do desejado. Porém, faltam ajustes mínimos. O total geral é irrelevante e pode ser removido, clicando com o botão direito do mouse (onde aponta a seta vermelha na próxima imagem) e escolhendo a opção apropriada, conforme ilustra a imagem a seguir.
Outra ação necessária é a classificação da coluna de valores. Para isso, clicamos com o botão direito do mouse em algum valor da segunda coluna da tabela dinâmica, escolhendo em seguida as opções Classificar > Classificar do Maior para o Menor.
Precisamos transformar a última coluna da tabela dinâmica para que venha a exibir os percentuais acumulados. Isto pode ser obtido facilmente ao clicar com o botão direito do mouse em algum valor desta última coluna, escolhendo em seguida as opções Mostrar Valores como > % da Soma Acumulada em.
Na caixa de diálogo seguinte, devemos escolher o campo que receberá os valores percentuais acumulados. Temos disponível apenas o campo de Cliente. Basta confirmarmos, clicando em Ok.
Nossa tabela dinâmica passará a ter a aparência a seguir.
Agora, devemos formatar os campos de valores: o primeiro, como moeda e o segundo, como porcentagem. Optamos por formatar como moeda sem exibir os centavos e os percentuais com 1 casa decimal. Aproveitamos para renomear os rótulos de colunas para termos mais amigáveis, conforme pode ser visualizado na próxima imagem.
Note que agora a tabela dinâmica possui os valores dos Clientes em ordem decrescente e os percentuais acumulados (ordem crescente).
Pronto! Agora nossa tabela dinâmica está finalizada, restando a inserção do gráfico.
Para finalizar com a criação do gráfico, com pelo menos uma célula da tabela dinâmica selecionada, siga a Etapa 3 do item 2.2 a). Não é possível inserir o gráfico de Pareto a partir de uma tabela dinâmica, através da opção da Etapa 2 do item 2.1.
A imagem a seguir mostra um gráfico resultante da tabela dinâmica obtida.
De acordo com o gráfico, conclui-se que os 2 maiores clientes em faturamento (Carrefour e Extra), dentre os 6 existentes, concentram mais da metade dos valores (52,2%).
Download Planilha Gráfico de Pareto Excel
Clique no botão abaixo para realizar o download da planilha de gráfico de pareto no Excel, com exemplo de dados:
