Modelo de diagrama de Pareto em Excel

Montar um diagrama de Pareto no Excel é meia hora de trabalho. Ler o resultado e sair dele com uma ação específica leva bem mais tempo, e é a parte que quase nenhum tutorial cobre. O gráfico fica pronto, a curva sobe bonito, e a equipe volta para a reunião seguinte com o mesmo gráfico e nenhuma mudança no indicador.

Este artigo entrega o modelo de diagrama de Pareto completo. Quais colunas a planilha precisa ter, as fórmulas de percentual e de percentual acumulado, a sequência de cliques do gráfico de combinação com eixo secundário e os ajustes de formatação que mudam o que o leitor entende.

Mas o núcleo está depois do gráfico: o corte de 80% que não é lei, o caso em que a distribuição é plana e o Pareto não decide nada, e a diferença de ordem entre o Pareto por frequência e o por custo. Fecha com o Pareto de segunda camada, onde a categoria vencedora deixa de ser rótulo e vira algo acionável.

O caso é um centro de distribuição com 1.250 devoluções em um trimestre, distribuídas em dez motivos, com custo unitário apurado por motivo. Os mesmos dados são usados nas três análises, o que deixa visível como a resposta muda conforme a pergunta.

Os números do exemplo foram montados para este artigo, no formato dos kits da Voitto. São ilustrativos: mostram a mecânica de cálculo e a aritmética que precisa fechar, conferida linha a linha, não descrevem uma empresa real.

O que o diagrama de Pareto mostra e o que ele não mostra

O diagrama de Pareto é um gráfico de duas camadas sobre a mesma base: barras em ordem decrescente de frequência e uma linha de percentual acumulado que sobe da esquerda para a direita. A pergunta que ele responde é uma só: onde o problema se concentra?

Isso já muda a conversa de "temos devoluções demais" para "três motivos respondem por 71,7% das devoluções". Mas o limite precisa ficar claro: o diagrama de Pareto localiza, não explica. Ele não diz por que a barra maior é a maior nem escolhe o que fazer.

Quem confunde as duas coisas ataca o rótulo da barra em vez da causa. Depois do Pareto vem a investigação de causa, com diagrama de Ishikawa ou com os 5 porquês, e só então a ação.

Por que 80/20 é observação e não lei

A origem do número é conhecida: Vilfredo Pareto observou, no fim do século 19, que uma fração pequena dos proprietários detinha a maior parte das terras na Itália. Joseph Juran generalizou a ideia para qualidade nos anos 1940 e cunhou a expressão "poucos vitais, muitos triviais".

Nada nessa história produz uma lei. O 80/20 é um padrão empírico, comum em dados de defeito e de falha porque esses fenômenos têm causas muito desiguais. Na prática a proporção varia: 70/30, 90/10, 65/20. O caso deste artigo fecha em 81,4% com quatro categorias de dez, e isso é resultado dos dados, não premissa.

O erro que vem da crença na lei é forçar o corte: traçar a linha em 80% mesmo quando a curva não tem joelho ali, separando categorias empatadas. O corte sai da forma da curva.

Quando o Pareto não se aplica: a distribuição plana

Existe um caso em que o gráfico fica bonito e não serve para nada: quando as categorias têm frequências parecidas. A linha acumulada sobe quase em reta, sem joelho, e não há "poucos vitais" para separar. Compare os dois cenários abaixo, ambos com 1.250 ocorrências em dez categorias:

No cenário plano, atacar a maior categoria elimina 12,2% do problema, e seriam necessárias oito das dez para chegar a 88%. O esforço de análise por unidade de resultado fica alto demais.

Distribuição plana indica uma de três coisas. O problema é sistêmico e atravessa todas as categorias. A estratificação escolhida é a errada: tente por turno, fornecedor, cliente ou equipamento. Ou as categorias estão em granularidade fina demais e fragmentaram o mesmo fenômeno.

Antes de descartar o Pareto, refaça o gráfico com outra variável. Muitas vezes o dado que fica plano por "tipo de defeito" fica concentrado por "linha de produção".

O que precisa existir antes da planilha: a coleta

A planilha não cria dado. Um Pareto feito com números lembrados em reunião reproduz a memória de quem falou mais alto, com a autoridade visual de um gráfico. O insumo correto é a folha de verificação: registro estruturado, feito no momento do evento, por quem observa.

A folha define as categorias antes da contagem e obriga o registrador a encaixar cada evento em uma delas. É isso que garante que as barras sejam comparáveis entre si. Três exigências mínimas:

• Período declarado e fechado. "Terceiro trimestre" serve; "nos últimos tempos" não. Sem período, a barra não tem denominador e o gráfico não pode ser comparado com o do próximo ciclo.
• Categorias mutuamente exclusivas. Se um evento pode ser contado em duas linhas, a soma das barras supera o total real e o acumulado passa de 100%.
• Regra de registro escrita. Quem classifica, em que momento, e o que fazer quando o evento não encaixa em nenhuma categoria existente.

Quando a coleta vem de sistema (ERP, chamados, ordens de serviço), o campo de motivo costuma ser texto livre ou lista longa herdada de outro processo. O agrupamento prévio é decisão analítica, não limpeza de dado.

A estrutura da planilha: quais colunas e em que ordem

O modelo de diagrama de Pareto no Excel precisa de quatro colunas e uma linha de total. Mais que isso vira poluição; menos quebra o gráfico.

A linha 1 leva os cabeçalhos e os dados começam na linha 2. Com dez categorias, a última linha de dado é a 11 e o total vai na 12. Fixar essa geometria no começo economiza retrabalho, porque as fórmulas de acumulado dependem de referências absolutas ao intervalo.

Uma quinta coluna opcional, com o custo unitário por ocorrência, permite gerar o segundo Pareto sem refazer nada. Ela fica à direita do acumulado, fora do intervalo do gráfico.

Não use células mescladas na área de dados, não deixe linha em branco no meio da lista e não formate a frequência como texto. Os três produzem o mesmo sintoma: o gráfico ignora categorias sem avisar.

O caso usado neste modelo

O exemplo é o setor de devoluções de um centro de distribuição de material elétrico. Toda devolução gera um registro com motivo, escolhido de uma lista fechada de dez opções pelo conferente da doca.

O período é um trimestre fechado, com 1.250 devoluções sobre 24.800 pedidos faturados, o que dá 5,04% de taxa de devolução. A meta interna é 2,5%.

Cada motivo tem custo médio por ocorrência apurado pela controladoria: frete de retorno, hora de conferência, reembalagem e perda quando o item não volta ao estoque vendável. O custo total do trimestre é de R$ 87.252,00.

Contagem e custo vão gerar dois Paretos diferentes na mesma planilha, e a diferença entre eles é um dos pontos centrais deste artigo.

A ordenação decrescente e o que ela esconde

Ordenar parece trivial e é onde mais planilha se corrompe. Selecione o intervalo inteiro, com todas as colunas preenchidas, e use Dados, Classificar, coluna de frequência, do maior para o menor. Nunca classifique uma coluna sozinha: o Excel oferece "continuar com a seleção atual", e aceitar isso desalinha categoria de frequência em silêncio.

Ordene antes de escrever as fórmulas de acumulado. O acumulado é uma soma corrente que depende da posição da linha; ordenar depois reordena resultados já calculados e produz uma curva que sobe e desce.

A categoria "Outros", quando existir, sai da ordenação e vai manualmente para a última posição, mesmo com frequência maior que a de alguma categoria nomeada. Ela é agregado, não item comparável.

Confira depois: a primeira frequência tem que ser a maior da lista e a última célula do acumulado tem que marcar 100,0%.

As fórmulas de percentual e de percentual acumulado

Com o total em B12, a coluna de percentual simples é direta. Em C2, digite a fórmula e arraste até C11:

• Percentual simples: =B2/$B$12 , formatado como porcentagem com uma casa decimal.
• Total de controle: =SOMA(B2:B11) na célula B12.

O cifrão antes de B e de 12 trava a referência do total. Sem ele, ao arrastar, o denominador desce junto com a linha e o resultado vira um número sem sentido que ainda parece plausível.

Para o acumulado existem duas escritas. A encadeada: D2 recebe =C2 e D3 recebe =D2+C3, arrastado até D11. Funciona, mas quebra se alguém excluir uma linha do meio.

A segunda é a do modelo, porque é a mesma fórmula em todas as células e resiste a inserção e exclusão de linha. Em D2, digite e arraste até D11:

• Acumulado robusto: =SOMA($B$2:B2)/SOMA($B$2:$B$11)

O truque está no intervalo misto $B$2:B2. O início fica travado na primeira linha de dado e o fim é relativo, então ao arrastar para D3 o intervalo vira $B$2:B3. É uma soma corrente que se estende sozinha. Se preferir, nomeie B12 como TOTAL e escreva =SOMA($B$2:B2)/TOTAL.

A tabela completa do exemplo, com a aritmética conferida

Abaixo, as dez categorias já ordenadas, com frequência, percentual simples e acumulado. A aritmética foi conferida linha a linha.

A última coluna é a que importa. Quatro motivos de dez concentram 81,4% das devoluções. O quinto acrescenta 6,2% e o sexto, 4,3%: a curva já está deitada. Entre a quarta e a quinta barra há um degrau de 3,4 pontos percentuais (9,7% contra 6,2%), e é ali que a inclinação muda.

Esse degrau, e não o número 80, é o critério de corte. Se ele estivesse entre a segunda e a terceira barra, o grupo vital teria duas categorias e 56,6%, e estaria certo do mesmo jeito.

Montar o gráfico de combinação, passo a passo

O Excel monta o Pareto como gráfico de combinação: barras para a frequência e linha para o acumulado, com a linha em um segundo eixo. A sequência abaixo vale das versões 2016 em diante, no Windows e no Mac.

1. Selecione A1:B11 (categoria e frequência). Segure Ctrl e selecione também D1:D11 (percentual acumulado). No Mac, use Command no lugar de Ctrl. A coluna C fica de fora do gráfico.
2. Vá em Inserir, grupo Gráficos, e abra Gráficos Recomendados. Na aba Todos os Gráficos, escolha Combinação, a última opção da lista lateral.
3. No painel de séries, defina a série Frequência como Colunas Agrupadas, sem marcar eixo secundário.
4. Defina a série % acumulado como Linhas com Marcadores e marque a caixa Eixo Secundário, na mesma linha da série.
5. Confirme. O gráfico aparece com as barras à esquerda e a linha subindo à direita.
6. Clique no eixo secundário, abra Formatar Eixo e fixe Mínimo em 0 e Máximo em 1 (se o eixo estiver em fração) ou em 100 (se estiver em número). Deixar automático faz o topo do eixo variar a cada atualização de dado.
7. Clique em uma barra, abra Formatar Série de Dados e reduza a Largura do Espaçamento para algo entre 15% e 30%. Barras encostadas são a convenção do Pareto e diferenciam o gráfico de um gráfico de colunas comum.
8. Adicione rótulos de dados nas barras (frequência absoluta) e na linha (percentual). Sem rótulo, o leitor tem que estimar valor pela altura, e a estimativa em eixo duplo erra muito.

Existe um atalho no Excel 2016 ou superior: Inserir, Gráficos Estatísticos, Pareto. Ele ordena e calcula o acumulado sozinho a partir de duas colunas. É rápido, mas prende: não troca a base de frequência para custo, não controla onde entra a categoria Outros e dificulta a linha de referência. Para modelo reusável, monte à mão.

O eixo secundário e os ajustes que mudam a leitura

O eixo secundário é o que mais gera versão errada, porque dois eixos independentes permitem combinações visualmente enganosas.

A linha de referência em 80% é opcional. Para criá-la, adicione uma coluna auxiliar com 80% repetido em todas as linhas, inclua como terceira série do tipo linha, jogue no eixo secundário, tire os marcadores e deixe tracejada. Cuidado para ela não virar o critério de corte no lugar do degrau da curva.

A categoria "Outros" e o risco de virar depósito

Quase todo Pareto real tem categoria de resíduo. Ela é legítima: agrupa eventos raros demais para linha própria e mantém a soma em 100%. O problema é quando cresce. A regra prática é um teto de 5%. No exemplo, "Outros" tem 17 eventos, 1,4%. Se estivesse em 12%, seria a terceira maior barra e o Pareto estaria escondendo o que deveria mostrar.

Quando o resíduo estoura o teto, há três caminhos:

1. Abrir o que está dentro dela e verificar se algum motivo recorrente foi jogado ali por falta de opção na lista. Nesse caso, crie a categoria e recontagem.
2. Verificar se a lista de categorias está desatualizada em relação ao processo. Processos mudam e a folha de verificação não acompanha.
3. Aceitar o resíduo alto e registrar isso como achado, se o processo realmente gera cauda longa. Aí o Pareto por frequência perde força e o Pareto por custo costuma ser mais informativo.

O que não pode acontecer é "Outros" virar destino do que o conferente não quer classificar. Isso é defeito de coleta, não de planilha, e se resolve no posto de registro.

Pareto por frequência contra Pareto por custo

O Pareto por frequência responde "o que mais acontece". O por custo responde "o que mais dói". São perguntas diferentes e dão respostas diferentes, porque nem todo evento custa o mesmo.

A validade curta aparece em 78 devoluções, quinto lugar por frequência. Mas cada ocorrência custa R$ 210,00, quase o triplo da média, porque o item costuma ser descartado. Refazendo o Pareto sobre o valor:

A ordem muda em três posições. Validade curta salta do quinto para o segundo lugar. Quantidade divergente cai do terceiro para o quinto. Nota fiscal divergente, que tinha 27 eventos, passa a ser o último item da lista por custo, com R$ 621,00 no trimestre inteiro.

Para montar o segundo Pareto na mesma planilha, crie um bloco à direita com as mesmas categorias, uma coluna de custo unitário, uma de custo total (frequência vezes custo unitário) e repita as fórmulas sobre ela. Ordene esse bloco pelo custo total. Dois gráficos, uma planilha.

Qual usar? Se a decisão é sobre dinheiro, o de custo. Se é sobre carga de trabalho, o de frequência. Quando os dois apontam para a mesma categoria no topo, como aqui, a prioridade fica sem discussão.

O Pareto de segunda camada, o passo que a maioria pula

Aqui está a diferença entre o Pareto que vira slide e o que vira ação. A primeira camada entrega uma vencedora: "produto avariado no transporte", 412 eventos, 33,0% das devoluções e 39,7% do custo. Ainda é grande demais para virar contramedida.

"Avariado no transporte" não é acionável: não existe uma ação chamada "resolver avaria". Existe ação sobre empilhamento, sobre amarração de carga ou sobre especificação de embalagem. Descobrir qual exige abrir a barra vencedora em um novo Pareto, com os mesmos 412 eventos e outra variável.

O setor reclassificou as 412 avarias pela condição observada na chegada, com foto no recebimento:

Agora existe alvo. Empilhamento acima de cinco caixas responde por 173 eventos, 42,0% das avarias e 13,8% das devoluções do trimestre. Em dinheiro, R$ 14.532,00, ou 16,7% do custo total: mais que qualquer categoria da primeira camada exceto a própria avaria e a validade curta.

E, diferente da categoria mãe, essa é acionável em uma frase: limitar o empilhamento a quatro caixas, marcar o limite no baú e incluir a verificação no romaneio. Duas camadas abaixo, um plano de ação começa a se escrever sozinho.

A regra de parada: desça camadas até que a categoria vencedora descreva algo que uma pessoa possa mudar na segunda-feira. Normalmente duas camadas, raramente três. Quem para na primeira volta no mês seguinte com o mesmo gráfico.

Quando as categorias estão mal definidas

Categoria ruim estraga o Pareto antes de qualquer fórmula, e os sintomas aparecem na planilha.

O último item é o mais custoso. Categorias como "erro do operador" e "falta de atenção" não descrevem evento: descrevem julgamento sobre o evento. Elas absorvem tudo que é difícil de classificar, e o Pareto vira um gráfico sobre a opinião do conferente.

O teste: a categoria precisa ser algo que duas pessoas, olhando o mesmo evento, classificariam igual. "Terminal mal crimpado" passa. "Falha de atenção na crimpagem" não passa.

Quando a lista de categorias estiver ruim, não conserte no gráfico: volte à folha de verificação, ajuste a lista e recomece a coleta. Um mês de dado bom vale mais que um trimestre de dado ambíguo.

A leitura que gera ação

Um Pareto pronto precisa produzir três frases escritas, não uma impressão visual.

1. A frase de concentração: quantas categorias respondem por qual percentual, com o número absoluto junto. "Quatro motivos de dez concentram 1.017 das 1.250 devoluções, 81,4%."
2. A frase de alvo: qual categoria foi escolhida, por qual critério, e quanto ela vale. "Avaria no transporte, 412 eventos e R$ 34.608,00, líder por frequência e por custo."
3. A frase de próximo passo: qual análise ou ação vem a seguir e quem conduz. "Abrir a avaria em segunda camada pela condição de chegada, com o supervisor de recebimento, até o dia 15."

Dentro do ciclo PDCA, o Pareto pertence à análise do Plan, entre a definição do problema e a investigação de causa. No método DMAIC, ocupa a etapa Analyze. No relatório MASP, é o instrumento da etapa de observação.

Com várias ações candidatas na mesa, a priorização não volta para o Pareto: ela sai da matriz GUT ou da matriz esforço impacto, que comparam ações entre si, não categorias de problema.

Depois que a ação entrar, o Pareto não verifica nada: ele mostra fotografia, não filme. Para saber se a mudança pegou, use carta de controle sobre o indicador afetado, que separa variação normal de mudança real.

Como o Pareto conversa com o resto do ferramental

Confundir o papel das ferramentas faz equipe rodar em círculo. Cada uma responde uma pergunta, e o Pareto ocupa um lugar estreito na sequência.

A linha do histograma merece nota. O histograma trabalha com variável contínua e mostra a forma da distribuição em faixas: tempo de ciclo, diâmetro, temperatura. O Pareto trabalha com categoria e mostra concentração. Barras encostadas em ambos, propósitos distintos.

A passagem para a execução acontece via 5W2H, que vira tarefa com dono e prazo. E com o processo sob acompanhamento contínuo, o Pareto entra como diagnóstico periódico dentro do controle estatístico de processo, não como monitoramento.

Erros no Excel que aparecem com mais frequência

A lista cobre o que mais aparece em planilha de Pareto quebrada, com o sintoma visível e a origem real.

Um último item é de uso, não de Excel: guardar o arquivo com um único período. O modelo de diagrama de Pareto rende mais quando a mesma planilha acumula trimestre a trimestre em abas separadas, com categorias idênticas. Comparar dois gráficos mostra se a barra atacada encolheu, e é isso que fecha o ciclo.