Conteúdo
Você já deve ter ouvido falar da Lei de Pareto ou do Princípio 20/80. No final do século XIX, o sociólogo e economista italiano Vilfredo Pareto descobriu que a distribuição da riqueza na sociedade é desigual e sujeita a uma certa dependência: com o aumento da riqueza, o número de pessoas ricas diminui exponencialmente com um coeficiente constante ( entre os lares italianos, 19% da renda estava em 80% das famílias). Mais tarde, essa ideia foi desenvolvida em seu livro por Richard Koch, que propôs a formulação do “Princípio 20/20” universal (80% dos esforços dão 20% do resultado). Na prática, essa lei geralmente não é expressa em números tão bonitos (leia “A Cauda Longa” de Chris Anderson), mas mostra claramente a distribuição desigual de recursos, lucros, custos, etc.
Na análise de negócios, um gráfico de Pareto geralmente é construído para representar essa irregularidade. Ele pode ser usado para mostrar visualmente, por exemplo, quais produtos ou clientes trazem mais lucro. Geralmente se parece com isso:
Suas principais características:
- Cada coluna azul do histograma representa o lucro do produto em unidades absolutas e é plotada ao longo do eixo esquerdo.
- O gráfico laranja representa a porcentagem acumulada dos lucros (ou seja, a participação nos lucros em base cumulativa).
- Em uma borda condicional de 80%, uma linha horizontal de limite geralmente é desenhada para maior clareza. Todos os bens à esquerda do ponto de intersecção desta linha com o gráfico do lucro acumulado nos trazem 80% do dinheiro, todos os bens à direita – os 20% restantes.
Vamos ver como construir um gráfico de Pareto no Microsoft Excel por conta própria.
Opção 1. Um gráfico de Pareto simples baseado em dados prontos
Se os dados de origem chegaram a você na forma de uma tabela semelhante (ou seja, já na forma final):
… então fazemos o seguinte.
Classifique a tabela em ordem decrescente de lucro (aba Dados - Ordenação) e adicione uma coluna com a fórmula para calcular o percentual acumulado de lucro:
Essa fórmula divide o lucro total acumulado desde o início da lista até o item atual pelo lucro total de toda a tabela. Também adicionamos uma coluna com uma constante de 80% para criar uma linha tracejada de limite horizontal no gráfico futuro:
Selecionamos todos os dados e construímos um histograma regular na guia Inserir – Histograma (Inserir – Gráfico de Colunas). Deve ser parecido com isto:
A série percentual no gráfico resultante deve ser enviada ao longo do eixo secundário (direita). Para fazer isso, você precisa selecionar as linhas com o mouse, mas isso pode ser difícil, pois são difíceis de ver no fundo de grandes colunas de lucro. Portanto, é melhor usar a lista suspensa na guia para destacar traçado or Formato:
Em seguida, clique com o botão direito do mouse na linha selecionada e selecione o comando Formato de série de dados e na janela que aparece, selecione a opção No eixo secundário (eixo secundário). Como resultado, nosso diagrama ficará assim:
Para a série Participação nos Lucros Acumulados e Limite, você precisa alterar o tipo de gráfico de colunas para linhas. Para fazer isso, clique em cada uma dessas linhas e selecione o comando Alterar o tipo de gráfico de série.
Tudo o que resta é selecionar a linha horizontal Limiar e formatá-la para que pareça uma linha de corte em vez de dados (ou seja, remova os marcadores, faça a linha tracejada vermelha etc.). Tudo isso pode ser feito clicando com o botão direito do mouse na linha e selecionando o comando Formato de série de dados. Agora o diagrama tomará sua forma final:
De acordo com ele, podemos concluir que 80% do lucro é trazido pelas 5 primeiras mercadorias, e todas as outras mercadorias à direita da batata representam apenas 20% do lucro.
No Excel 2013, você pode fazer isso ainda mais fácil – use o novo tipo de gráfico de combinação integrado imediatamente ao plotar:
Opção 2: Tabela Dinâmica e Gráfico de Pareto Dinâmico
O que fazer se não houver dados prontos para construção, mas apenas a informação bruta original? Vamos supor que no início temos uma tabela com dados de vendas assim:
Para construir um gráfico de Pareto nele e descobrir quais produtos vendem melhor, primeiro você terá que analisar os dados de origem. A maneira mais fácil de fazer isso é com uma tabela dinâmica. Selecione qualquer célula na tabela de origem e use o comando Inserir – Tabela Dinâmica (Inserir – Tabela Dinâmica). Na janela intermediária que aparece, não altere nada e clique em OK, em seguida, no painel que aparece à direita, arraste os campos de dados de origem da parte superior para a parte inferior do layout da futura tabela dinâmica:
O resultado deve ser uma tabela resumo com a receita total de cada produto:
Classifique-o em ordem decrescente de receita, definindo a célula ativa para a coluna Valor no campo Receita e usando o botão classificar От Я до А (De Z a A) aba Data.
Agora precisamos adicionar uma coluna calculada com ganhos de juros acumulados. Para fazer isso, arraste o campo novamente Receita para a área Valores no painel direito para obter uma coluna duplicada no pivô. Em seguida, clique com o botão direito do mouse na coluna clonada e escolha o comando Cálculos adicionais - % do total acumulado no campo (Mostrar dados como - % do total acumulado em). Na janela que aparece, selecione o campo Nome, em que a porcentagem da receita será acumulada de cima para baixo. A saída deve se parecer com esta tabela:
Como você pode ver, esta é quase uma tabela pronta da primeira parte do artigo. Só falta para a felicidade completa uma coluna com um valor limite de 80% para construir uma linha de corte em um diagrama futuro. Essa coluna pode ser facilmente adicionada usando um campo calculado. Destaque qualquer número no resumo e clique na guia Home – Inserir – Campo Calculado (Home – Inserir – Campo Calculado). Na janela que se abre, digite o nome do campo e sua fórmula (no nosso caso, uma constante):
Depois de clicar em OK uma terceira coluna será adicionada à tabela com um valor de 80% em todas as células, e finalmente tomará a forma desejada. Então você pode usar o comando Gráfico Dinâmico (Gráfico Dinâmico) aba parâmetros (Opções) or Análise (Análise) e configure o gráfico exatamente da mesma maneira que a primeira opção:
Destaque dos principais produtos
Para destacar os fatores mais influentes, ou seja, as colunas localizadas à esquerda do ponto de interseção da curva de juros acumulados laranja com a linha de corte horizontal de 80% podem ser destacadas. Para fazer isso, você terá que adicionar outra coluna à tabela com a fórmula:
Essa fórmula gera 1 se o produto estiver à esquerda do ponto de interseção e 0 se estiver à direita. Então você precisa fazer o seguinte:
- Adicionamos uma nova coluna ao gráfico – a maneira mais fácil de fazer isso é copiando simples, ou seja, destacar a coluna backlight, copie-o (Ctrl + C), selecione o diagrama e insira (Ctrl + V).
- Selecione a linha adicionada e alterne-a ao longo do eixo secundário, conforme descrito acima.
- Tipo de gráfico de série backlight mudar para colunas (histograma).
- Removemos a folga lateral nas propriedades da linha (clique com o botão direito do mouse na linha Iluminação – Formato de Linha – Espaço Lateral) para que as colunas se fundam em um único todo.
- Removemos as bordas das colunas e tornamos o preenchimento translúcido.
Como resultado, temos um destaque tão bom dos melhores produtos:
PS
A partir do Excel 2016, o gráfico de Pareto foi adicionado ao conjunto padrão de gráficos do Excel. Agora, para construí-lo, basta selecionar o intervalo e na aba inserção (Inserir) escolha o tipo apropriado:
Um clique – e o diagrama está pronto:
- Como criar um relatório usando uma tabela dinâmica
- Configurar cálculos em tabelas dinâmicas
- O que há de novo em gráficos no Excel 2013
- artigo da Wikipedia sobre a lei de Pareto