Assistir ao vídeo - Como fazer um gráfico de Pareto no Excel
O Gráfico de Pareto é baseado no princípio de Pareto (também conhecido como regra 80/20), que é um conceito bem conhecido em gerenciamento de projetos.
De acordo com este princípio, ~ 80% dos problemas podem ser atribuídos a cerca de ~ 20% dos problemas (ou ~ 80% de seus resultados podem ser um resultado direto de ~ 20% de seus esforços e assim por diante …).
O valor percentual 80/20 pode variar, mas a ideia é que de todas as questões / esforços, existem alguns que resultam em impacto máximo.
Este é um conceito amplamente utilizado em gerenciamento de projetos para priorizar o trabalho.
Criando um gráfico de Pareto no Excel
Neste tutorial, vou mostrar como fazer um:
- Gráfico de Pareto simples (estático) no Excel.
- Gráfico de Pareto dinâmico (interativo) no Excel.
Criar um gráfico de Pareto no Excel é muito fácil.
Todos os truques estão ocultos na maneira como você organiza os dados no back-end.
Vejamos um exemplo de um hotel para o qual os dados de reclamações podem ser parecidos com os mostrados abaixo:
NOTA: Para fazer um gráfico de Pareto no Excel, você precisa ter os dados organizados em ordem decrescente.
Criando um gráfico de Pareto simples (estático) no Excel
Aqui estão as etapas para criar um gráfico de Pareto no Excel:
- Configure seus dados conforme mostrado abaixo.
- Calcule a% cumulativa na coluna C. Use a seguinte fórmula: = SUM ($ B $ 2: B2) / SUM ($ B $ 2: $ B $ 1)
- Selecione todo o conjunto de dados (A1: C10), vá para Inserir -> Gráficos -> Coluna 2-D -> Coluna em Cluster. Isso insere um gráfico de coluna com 2 séries de dados (número de reclamações e a porcentagem cumulativa).
- Clique com o botão direito em qualquer uma das barras e selecione Alterar tipo de gráfico de série.
- Na caixa de diálogo Alterar tipo de gráfico, selecione Combo no painel esquerdo.
- Faça as seguintes alterações:
- Nº de reclamações: coluna agrupada.
- % Cumulativa: linha (marque também a caixa de seleção Eixo secundário).
[Se você estiver usando o Excel 2010 ou 2007, será um processo de duas etapas processar. Primeiro, altere o tipo de gráfico para um gráfico de linha. Em seguida, clique com o botão direito no gráfico de linha e selecione Formatar série de dados e selecione Eixo secundário em opções de série]
- Seu gráfico de Pareto no Excel está pronto. Ajuste os valores do eixo vertical e o título do gráfico.
Como interpretar este gráfico de Pareto no Excel
Este gráfico de Pareto destaca as principais questões em que o hotel deve se concentrar para classificar o número máximo de reclamações. Por exemplo, mirar nos primeiros 3 problemas cuidaria automaticamente de aproximadamente 80% das reclamações.
Por exemplo, mirar nos primeiros 3 problemas cuidaria automaticamente de aproximadamente 80% das reclamações.
Criação de um gráfico de Pareto dinâmico (interativo) no Excel
Agora que temos um gráfico de Pareto estático / simples no Excel, vamos dar um passo adiante e torná-lo um pouco interativo.
Algo conforme mostrado abaixo:
Nesse caso, um usuário pode especificar a% de reclamações que precisam ser resolvidas (usando a barra de rolagem do Excel), e o gráfico irá destacar automaticamente os problemas que devem ser examinados.
A ideia aqui é ter 2 barras diferentes.
O vermelho é destacado quando o valor percentual cumulativo está próximo do valor alvo.
Aqui estão as etapas para fazer este gráfico de Pareto interativo no Excel:
- Na célula B14, tenho o valor alvo que está vinculado à barra de rolagem (cujo valor varia de 0 a 100).
- Na célula B12, usei a fórmula = B14 / 100. Como você não pode especificar um valor percentual para uma barra de rolagem, simplesmente dividimos o valor da barra de rolagem (em B14) por 100 para obter o valor percentual.
- Na célula B13, insira a seguinte combinação de funções INDEX, MATCH e IFERROR:
= IFERROR (INDEX ($ C $ 2: $ C $ 10, IFERROR (CORRESPONDÊNCIA ($ B $ 12, $ C $ 2: $ C $ 10,1), 0) +1), 1)
Esta fórmula retorna o valor cumulativo que cobriria o valor de destino. Por exemplo, se você tiver o valor alvo de 70%, ele retornará 77%, indicando que você deve tentar resolver os três primeiros problemas.
- Na célula D2, insira a seguinte fórmula (e arraste ou copie para todas as células - D2: D10):
= SE ($ B $ 13> = C2, B2, NA ()) - Na célula E2, insira a seguinte fórmula (e arraste ou copie para todas as células - E2: E10):
= SE ($ B $ 13<> - Selecione os dados na coluna A, C, D e E (pressione control e selecione usando o mouse).
- Vá para Inserir -> Gráficos -> Coluna 2-D -> Coluna em Cluster. Isso irá inserir um gráfico de coluna com 3 séries de dados (porcentagem cumulativa, as barras a serem destacadas para atingir a meta e todas as outras barras restantes)
- Clique com o botão direito em qualquer uma das barras e selecione Alterar tipo de gráfico de série.
- Na caixa de diálogo Alterar tipo de gráfico, selecione Combo no painel esquerdo e faça as seguintes alterações:
- % Cumulativa: linha (marque também a caixa de seleção Eixo secundário).
- Barras em destaque: coluna agrupada.
- Barras restantes: coluna agrupada.
- Clique com o botão direito em qualquer uma das barras destacadas e altere a cor para Vermelho.
É isso!
Você criou um Gráfico de Pareto interativo no Excel.
Agora, quando você altera o alvo usando a barra de rolagem, o gráfico de Pareto seria atualizado de acordo.
Você usa o Gráfico de Pareto no Excel?
Eu adoraria ouvir sua opinião sobre esta técnica e como você a usou. Deixe suas pegadas na seção de comentários 🙂
- Analisando reclamações de restaurantes usando o gráfico de Pareto.
- Criando um gráfico de Gantt no Excel.
- Criando um gráfico Milestone no Excel.
- Criando um histograma no Excel.
- Modelo de calculadora do quadro de horários do Excel.
- Modelo de rastreador de licenças de funcionários.
- Calculando a média ponderada no Excel.
- Criando uma curva de sino no Excel.
- Gráficos Excel Avançados
- Como adicionar um eixo secundário em gráficos do Excel.
