Siga-nos no Twitter
Assine os feeds dos artigos
Assine os feeds dos comentários
Precisa de ajuda com feeds?
Receba os artigos por email
Por Arthur Gouveia em 28 de maio de 2010Um gráfico de Pareto apresenta os dados em ordem decrescente e atrelados a uma curva de porcentagens acumuladas; também conhecida como curva ABC. Portanto, para fazer um Pareto é necessário apenas colocar os dados em ordem e fazer o cálculo das porcentagens acumuladas.
Neste artigo vamos explorar como ordenar os dados no Excel através de fórmulas, sem a necessidade de usar macros ou o botão Classificar na barra de ferramentas do Excel. Para isso vamos fazer um gráfico de Pareto da quantidade em quilogramas de vendas de determinadas frutas.
Preparando a tabela
Para fazer a ordenação automática dos dados são necessárias duas tabelas: uma de entrada, onde serão lançados os dados e outra de saída, onde serão realizados os cálculos para ordenar.
Na tabela de entrada, como vemos na imagem acima, além das informações sobre as categorias (Fruta) e os dados (Qtd) existem duas colunas: Ordenador e Índice. A coluna de índice define qual seria a ordem padrão dos dados caso todos os valores fossem iguais, é como um critério padrão de desempate. Isso é necessário para que as fórmulas do Excel não fiquem em dúvida quando acontecerem valores iguais como no caso das quantidades vendidas de Tangerina e Uva. Já o Ordenador é o valor através do qual a tabela será ordenada. Ele utiliza, além dos dados de quantidade, as informações do índice para que não existam valores iguais.
Para que possamos fazer o cálculo das porcentagens acumuladas é necessário ter o valor total das quantidades vendidas. Este valor está na célula D11 e a fórmula para cálculo do total é =SOMA(D3:D10).
Ordenando os valores
Vejamos, então, como criar os dados ordenados. Na tabela de entrada é necessário calcular apenas o valor do ordenador. O valor do ordenador é a soma da quantidade com 1 / (índice * 1000). Fazer 1 / índice é necessário para que as frutas com maior índice apareçam primeiro e a multiplicação por mil é necessária para que o “critério de desempate” apareça apenas após a terceira casa decimal. Agora, cada fruta tem um número único pelo qual é possível ordenar.
Para gerar a tabela de saída devemos começar com a coluna do Ordenador; é aí onde reside o segredo da ordenação. Para ordenar os dados, vamos utilizar a função MAIOR(matriz;k). Esta função retorna o maior valor k-ésimo do conjunto de dados. Com isso os dados da coluna Ordenador da tabela de saída têm as seguintes fórmulas:
| Ordenador |
|---|
| =MAIOR($A$3:$A$10;1) |
| =MAIOR($A$3:$A$10;2) |
| =MAIOR($A$3:$A$10;3) |
| =MAIOR($A$3:$A$10;4) |
| =MAIOR($A$3:$A$10;5) |
| =MAIOR($A$3:$A$10;6) |
| =MAIOR($A$3:$A$10;7) |
| =MAIOR($A$3:$A$10;8) |
Assim, na primeira linha fica o maior valor, na segunda linha o segundo maior valor e assim por diante.
Agora é necessário encontrar a fruta e a quantidade vendida correspondente a cada número da coluna Ordenador. Para isso utilizaremos a função PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo). Já falamos sobre ela em outro artigo, mas de um modo geral, a função PROCV procura valor_procurado na primeira coluna de matriz_tabela e retorna o valor na mesma linha de valor_procurado e na coluna núm_índice_coluna.
Com isso, as fórmulas das colunas Fruta e Qtd na tabela de saída são:
| Fruta | Qtd |
|---|---|
| =PROCV(H3;$A$3:$D$10;3;FALSO) | =PROCV(H3;$A$3:$D$10;4;FALSO) |
| =PROCV(H4;$A$3:$D$10;3;FALSO) | =PROCV(H4;$A$3:$D$10;4;FALSO) |
| =PROCV(H5;$A$3:$D$10;3;FALSO) | =PROCV(H5;$A$3:$D$10;4;FALSO) |
| =PROCV(H6;$A$3:$D$10;3;FALSO) | =PROCV(H6;$A$3:$D$10;4;FALSO) |
| =PROCV(H7;$A$3:$D$10;3;FALSO) | =PROCV(H7;$A$3:$D$10;4;FALSO) |
| =PROCV(H8;$A$3:$D$10;3;FALSO) | =PROCV(H8;$A$3:$D$10;4;FALSO) |
| =PROCV(H9;$A$3:$D$10;3;FALSO) | =PROCV(H9;$A$3:$D$10;4;FALSO) |
| =PROCV(H10;$A$3:$D$10;3;FALSO) | =PROCV(H10;$A$3:$D$10;4;FALSO) |
Pronto! A tabela já está pronta e ordenará automaticamente os valores com base nas quantidades na tabela de entrada.
Criando as porcentagens acumuladas.
As colunas de porcentagem e de porcentagem acumuladas são necessárias para fazer a curva ABC.
O cálculo da coluna porcentagem é a quantidade vendida de cada fruta em relação à quantidade total. Já a coluna porcentagem acumulada é o resultado da quantidade vendida de uma fruta adicionada ao valor acumulado até então. As fórmulas são:
| % | % Acumulada |
|---|---|
| =G3/$D$11 | =I3 |
| =G4/$D$11 | =I4+J3 |
| =G5/$D$11 | =I5+J4 |
| =G6/$D$11 | =I6+J5 |
| =G7/$D$11 | =I7+J6 |
| =G8/$D$11 | =I8+J7 |
| =G9/$D$11 | =I9+J8 |
| =G10/$D$11 | =I10+J9 |
Formate as colunas como porcentagem.
Gerando o gráfico de Pareto
Clique em uma célula vazia no Excel e insira um novo gráfico de colunas.
Um gráfico em branco irá surgir. Clique com o botão direito e selecione a opção Selecionar dados. Na janela Selecionar Fonte de Dados clique em Adicionar. Na caixa Editar Série coloque o nome da série como Quantidade e no campo Valores da série apague o valor atual (={1}) e selecione apenas os dados, sem o cabeçalho, da coluna Qtd na tabela de saída. Clique em Ok.
Clique novamente no botão Adicionar, dê para a série o nome Curva ABC, apague o conteúdo do campo Valores da série e selecione os dados na coluna de porcentagens acumuladas. Clique em Ok.
Na caixa Selecionar Fonte de Dados, no grupo Rótulos do Eixo Horizontal (Categorias), clique no botão Editar e selecione os dados da coluna Fruta na tabela de saída e clique em Ok.
A caixa Selecionar Fonte de dados vai ficar assim:
Clique em Ok.
Clique com o botão direito sobre qualquer barra da série Quantidade no gráfico e selecione Formatar Série de Dados.
Na caixa Formatar Séries de Dados marque a opção Eixo Secundário e coloque Largura do Espaçamento em 10%. Clique em Fechar.
Clique com o botão direito sobre qualquer barra da série Curva ABC e selecione Alterar Tipo de Gráfico de Série. Na janela Alterar Tipo de Gráfico selecione um gráfico de linhas sem marcadores.
Pronto! Você já tem um gráfico de Pareto com a curva ABC que será alterado automaticamente se você mudar as quantidades vendidas na tabela de entrada.
Refinando o gráfico de Pareto
Algumas pessoas não gostam do gráfico com a forma que ele assume por padrão e preferem que a curva ABC comece no topo da primeira coluna de dados.
Para isso clique com o botão direito no Eixo Vertical Principal e selecione Formatar Eixo. Deixe o valor mínimo fixo em zero e o valor máximo fixo em 1. Faça o mesmo para o Eixo Vertical Secundário fixando o mínimo em zero e o máximo em 360 – o valor total de quantidades vendidas. O resultado é este:
Tags: 
Arquivado em 
![[Google]]( http://hitec.arthurgouveia.com.br/wp-content/plugins/easy-adsenser/google-light.gif)
Obrigado amigo, realmente este seu post me tirou uma duvida muito grande e me ajudou muito!
Parabéns pelo seu blog!
Valeu, Matheus! Precisando, entre em contato. Mande suas dúvidas que eu responderei aqui!
Muito bom, realmente muito bom! caiu como uma luva pra mim, muito bom pra indentificar problemas dentro da equipe.
Obrigado, Monique!
Você assimilou muito bem o conceito do Gráfico de Pareto: a identificação das causas prioritárias de problemas.
Fico feliz em saber que meu artigo te ajudou.
Assine nossos feeds RSS ou siga-nos no Twitter para ficar conectada com todas as novidades do HITec.
Prezado Arthur,
Excelente artigo!! Ja conhecia o conceito de Pareto e Curva ABC, mas não consegui de maneira alguma fazer o grafico no excel!
Suas instruções ajudaram muito.
Obrigada,
Ana Cláudia
Sr. Arthur, boa noite!
Apesar de ter dificuldades de trabalhar com o excel, eu gostaria de agradecer muito a sua ajuda. No entanto, se o Senhor pudesse me ajudar com algo mais resumido eu agradeceria porque eu tentei porém, não obtive sucesso. O Senhor pode me enviar um gráfico pronto com a tabela para eu possa só inserir os meus dados de controle de estoque? acredito que já me ajudaria bastante. Para os colegas que acertaram meus parabéns. Aguardo retorno.
Um grande abraço a todos!
Sds,
Adailton Ribeiro
estou precisando fazer um gráfico onde preciso mostrar ao mês seis colunas, para mostrar dentro de cada mês o desempenho dos meus vendedores num período de 6 meses, nao sei como montar um grafico com 6 colunas pode me ajudar???
Muito Obrigado!!
Foi de muita ajuda…
Excelente Post!
Mto obrigado pela divulgação.
Olá, a curva ABC do meu gráfico fico ao contrário, indo de cima para baixo
Caro amigo. Preciso de uma ajuda. Tenho que montar um gráfico para análise no excel 2010, e nele tenho que demonstrar as seguintes informações: Deixar a mostra no gráfico o faturamento anual da empresa e sobre ele, calcular uma porcentagem de despesas com cada departamento ou setor, da empresa, ou seja, tenho o valor anual da empresa e também tenho o valor gasto anual de cada departamento, como demonstrar para uma análise mais profunda e se você tiver mais alguma informação desse genêro para acrescentar, ficarei agradecido.
Leilton,
Faça um gráfico de Pizza. Liste as despesas de todos os departamentos e faça o gráfico. O total da empresa é a soma dos departamentos…
Nossa!
Muito bom o artigo, consegui fazer o gráfico perfeitamente!!!
Obrigadaa!
Amigo, seu post foi de grande ajuda. O mais completo e detalhado que encontrei na Web.
Muito obrigado!
Abraço.
manda o arquivo por e-mail pra facilitar. vale 5 pontos meu!!!
Caro Arthur, parabéns, acredito que assim como foi para mim, foi também para a maioria das pessoas que procuravam por algo a respeito, sem dúvida seu post foi e será de grande ajuda, muito obrigado, valeu!
Arthur,
Muito bom seu conteudo, estou aprendendo a trabalha com controle de performace e seu material me ajudou muito, pois sempre necessito de criar graficos para demostar estudos e avaliações.
Att,
Adauto Rios.
Muito boa a explicação!!! Simples e fácil de acompanhar!
Salve
Muito obrigado, o material é muito didático e de fácil entendimento. Me ajudou muito
José Almiro Marinho
Valeu pela aula, tava tentando montar um pareto mas não tava dando certo e tenho uma prova na empresa que trabalho a alguns dias, agora posso ir mais tranquilo, valeu mesmo e parabens pelo tabalho.
Olá Arthur, bom dia !
Inicialmente, parabens pelas suas informações/contribuições. Vou direto:
Apesar de ser economista e com larga especialização em gestão de pessoas e processos comerciais, confesso que necessito relembrar algumas formas de aplicação do “pareto”. Na verdade, trata-se de uma “ajuda” que estou oferecedo à um meu irmão, iniciante (25 anos – sem curso superior). Ele está iniciando uma micro-empresa voltada exclusivamente à area de confecções – moda feminina. Nisso ele é craque. O problema/dificuldade dele é “administração”, principalmente no quesito: preço de custo – preço de venda – % margem. Muito comum aos “micros”… Lembrei-me do mark-up. Vou aplicar. Porém, desejo aplicar também o “pareto” no quesito especifico “finanças/empréstimos/financiamentos/taxas de juros/retorno esperado/investimento inicial/ curto e longo prazo… digamos quase um diagnóstico da “saúde da empresa”…pelo que eu percebi no meu irmão foi quase uma “angústia” no curto prazo entre: estoque + contas a receber VS contas à pagar – imediantamente no curto prazo para que ele avalie o “fôlego” que tem… mediante da “análise” já farei as “receitas dos remédios”… Por isso, julguei que o “pareto” pode auxiliar na efetiva “situação” e eventuais ações corretivas.
Eu sei Arthur, “falei” demais… peço desculpas… se você puder me ajudar na aplicação do pareto já ficarei bem agradedcido.
Grato,
Marco Antonio
Muito obrigada, o tutorial foi muito didático e de fácil entendimento. Me ajudou muito, consegui fazer o gráfico perfeitamente!!!
Grande Arthur!!!
Cara nesse Excel 2007 eu não sabia de jeito nenhum fazer Pareto, te achei no Grande Oráculo google. Abração Colega!!!
PS depois deixo um recado mais sem educação no seu FACEBOOK!!!
Ola, Sr. Arthur!
Foi realmente um “Eureka!” ter encontrado esse seu post sobre
Gráfico de pareto na web. Trabalho com TPM – Manutenção Planejada Total em uma grande empresa, e estava com dificuldade
para ordenar as Categorias em ordem decrescente de forma, digamos, automática, no excel. Mas depois de ler esse seu artigo, ficou muito fácil. Seu artigo é resumido, didático e
bem explicativo. Foi a melhor ajuda que encontrei na NET sobre o assunto. Muito OBRIGADO pela ajuda.
Roberto.
No meio de um monte de besteiras que são publicadas na internet, ela ainda vale a pena pq dissemina boas informações sem que a gente saia fisicamente do lugar. Muito, mas muito obrigada mesmo, por disponibilizar seus conhecimentos pra quem quiser.
Sucesso
Td Bem??
Cara mez dia como posso inserir umtitulo neste grafico de pareto, ao qual, passou as orientações acima??
grato.
Parabéns Arthur! É só ler com atenção, que a tua explicação está de mestre! O Gráfico ficou perfeito! Muito Obrigada.
Parabéns pela explicação.
O desenvolvimento passo a passo ficou ótimo. Parabéns.
Não é possivel, disponibilizar Download da planilha, as vezes, mesmo seguindo as orientações não se consegue de maneira alguma, fazer o grafico no excel!
parabens Sr Arthur !
agradeçemos pela iniciativa. nao foi útil para mim , mas nao ha nada melhor que um conhecimento agregado !
parabens pela ajuda , e parabens pela pasciencia. heheh .
Grande abraço, Sucessos !!
Sr Arthur
MUITO OBRIGADO ME AJUDOU MUITO MESMO PARABÉNS
SR.ARTHUR
MUITO OBRIGADO MESMO CONSEGUIU ME AJUDAR E MUITO PARABENS PELA INICIATIVA ABRAÇOS.
Artur,
excelente esta sua explicação, facil e bem didatica.
Otima atitude sua em dividir seus conhecimentos
Abraço
Obrigada pelo post Artur!
Realmente tirou uma grande dúvida que tinha – e de maneira rápida e fácil, de como ajustar a linha de % para que ficasse no topo das colunas.
MUITO BOM mesmo!
Parabéns pelo trabalho e por compartilhar conosco.
Abraço
Primeiramente, parabens Sr ARTHUR pela explicação foi breve porem perfeita, de facil entendimento.Agora é só praticar.
Parabens e obrigado por compartilhar seu conhecimento conosco.
Amigos, tenho uma dúvida cruel. rs
Eu utilizo o Office 2007 e no Excel eu montei uma tabela mês a mês onde no final ela gera um relatório de todos os dados mês a mês. Com esses dados eu apontei vários indicadores e cada indicador montei um gráfico e colei no Power Point, onde fica linkado o excel com o power point, se eu faço uma alteração no Excel, logo vai alterar no meu gráfico q esta linkado no power point. A grande dúvida é se eu salvo todas as informações no excel e power point e fecho, depois quando eu abro os dois arquivos novamente e vou fazer alteração no excel os dados não alteram mais no power point a não ser se eu der um atualizar em todos os gráficos. Tem como criar alguma forma onde eu atualize as informações no excel e assim q eu abrir o power point ele atualize todas as informações de uma única vez sem que eu precise ficar indo atualizar gráfico por gráfico?
Gostaria de saber se essas sua instruções para um 400 itens, já que fazer desse jeito para poucos intens é uma coisa, agora e quanto a muitos intens, como acontece na realidade ? você saberia como fazer ?
Muitíssimo obrigada!!!!
Me ajudou muito… é super bem explicadinho…
Parabéns!!!!
Valeu!!!! Tchau!
Ótima explicação! É muito bom quando a gente encontra algo por alguém que realmente tem didática. Parabéns! Obrigada
gostaria de aprender fazer um grafico de pareto,pos tenho muita dificudade em aprender só,tenho muito carinho pelo sr!! e até tenho idicado para toda turma de mba da facudade pois nós estamos muito perdido nesse assunto,
desde já agradeço muito!!!
obrigado.
Sr. Arthur,
Muito bom o texto.
Parabéns!
Artur, obrigada por conseguires por o teu conhecimento ao alcance de todos . Raro!!!
Vou cololocar em prática graças ati.
Muito bom! Muito Obrigado pela ajuda!
Facilitou totalmente!!!
Obrigado!!
Muito didático, excelente.
Estava bastante enferrujado e suas explicações e metodologia foram de fundamental ajuda!
Obrigado!
Parabéns!
Muito obrigada! me ajudou demaaaaiiiiissssss num trabalho da faculdade!!!!
gostei muito da explicação e só não consegui fazer a parte de refinar o gráfico depareto, fiz do jeito que está escrito mas a linha não começa no topo do gráfico, o que será que pode ter acontecido?
Excelente trabalho professor Arthur!
Consegui pela primeira vez fazer o gráfico de pareto pelo excel.
Parabéns!!
Me auxiliou no trabalho de conclusão de curso.
Muito Obrigado.
Parabéns! Excelente explicação, realmente ficou muito fácil entender.
Abs