Para fazer curva ABC no Excel, você monta três abas (Vendas, Custos e ABC), calcula o lucro de cada SKU, descobre o percentual acumulado e deixa uma fórmula SE classificar em A, B ou C. A planilha funciona igual no Google Planilhas e, se você usar as fórmulas deste guia, atualiza sozinha quando colar vendas novas. Aqui o foco é a construção da planilha, célula por célula. Para entender o conceito, os critérios e a reposição de cada classe, veja o guia de curva ABC de estoque.
Resumo rápido
- Três abas resolvem: Vendas (uma linha por pedido), Custos (custo unitário por SKU) e ABC (a curva).
- O lucro por SKU vem de SOMASES e PROCV: receita menos comissão, frete, imposto e custo do produto.
- O percentual acumulado dá para calcular sem ordenar a lista, com uma única fórmula SOMASES.
- A classe usa o acumulado da linha anterior, assim o SKU que cruza a linha dos 80% não é rebaixado.
- SKU com lucro negativo fica fora da curva e vai para uma classe à parte.
▶️ Curva ABC por lucro dos produtos do Mercado Livre
O que você precisa antes de montar a curva ABC no Excel?
Você precisa de duas listas: as vendas do período e o custo unitário de cada produto. Sem custo, a curva sai por faturamento, e em marketplace isso engana (o produto que mais fatura nem sempre é o que mais sobra).
Separe estes dados antes de abrir a planilha:
- Vendas do período, uma linha por pedido ou por item vendido: data, SKU, quantidade, receita, comissão do marketplace, frete pago por você e imposto. Exporte do próprio marketplace ou do sistema que você já usa.
- Custo unitário de cada SKU, com o custo médio ponderado se você compra o mesmo item em lotes de preços diferentes (explicamos no guia de curva ABC e custo médio).
- Um período fixo para todos os SKUs. Os últimos 90 dias são um bom ponto de partida, não uma regra.
SKU é o código que identifica um item do seu estoque. Use o mesmo código nas duas listas, escrito igual, porque é ele que liga as abas.
Como organizar as abas da planilha?
Use três abas e nunca digite dado dentro da aba ABC: ela só calcula. Assim, para atualizar, você troca as linhas da aba Vendas e o resto se recalcula.
| Aba | Colunas | Quem preenche |
|---|---|---|
| Vendas | A data, B SKU, C quantidade, D receita, E comissão, F frete, G imposto | Você, colando a exportação |
| Custos | A SKU, B produto, C custo unitário | Você, uma vez por SKU (e quando o custo mudar) |
| ABC | A SKU, B produto, C quantidade, D receita, E taxas, F custo, G lucro, H % do lucro, I % acumulado, J classe | Fórmulas, nada digitado |
Na aba ABC, digite só os SKUs na coluna A (uma vez por SKU, sem repetir). Todo o resto vem de fórmula.
Como fazer a curva ABC no Excel passo a passo?
São nove colunas de fórmula, da B até a J, e cada uma depende só das anteriores. Os exemplos usam a linha 2 e uma lista de 7 SKUs (linhas 2 a 8); ajuste o $G$8 para a última linha da sua lista.
- Produto (coluna B). Busca o nome na aba Custos:
=PROCV(A2;Custos!$A:$C;2;FALSO). OFALSOno fim pede correspondência exata; sem ele, o Excel faz busca aproximada e pode trazer o produto errado (documentação da Microsoft sobre PROCV). - Quantidade (C). Soma as unidades do SKU na aba Vendas:
=SOMASES(Vendas!$C:$C;Vendas!$B:$B;$A2). No SOMASES, o intervalo a somar vem primeiro e depois os pares de critério (documentação da Microsoft sobre SOMASES). - Receita (D). Mesma ideia, trocando a coluna somada:
=SOMASES(Vendas!$D:$D;Vendas!$B:$B;$A2). - Taxas (E). Comissão, frete e imposto juntos:
=SOMASES(Vendas!$E:$E;Vendas!$B:$B;$A2)+SOMASES(Vendas!$F:$F;Vendas!$B:$B;$A2)+SOMASES(Vendas!$G:$G;Vendas!$B:$B;$A2). - Custo (F). Quantidade vendida vezes custo unitário:
=C2*PROCV(A2;Custos!$A:$C;3;FALSO). - Lucro (G). A conta que ordena a curva:
=D2-E2-F2. Esse é o lucro de contribuição do SKU, ou seja, o que sobra depois de taxas, frete, imposto e custo do produto (a conta completa está no guia de margem de contribuição). - % do lucro (H). Peso do SKU no total, ignorando quem deu prejuízo:
=SE(G2>0;G2/SOMASES($G$2:$G$8;$G$2:$G$8;">0");0). - % acumulado (I). Aqui está o truque que dispensa ordenar:
=SOMASES($G$2:$G$8;$G$2:$G$8;">="&G2;$G$2:$G$8;">0")/SOMASES($G$2:$G$8;$G$2:$G$8;">0"). Ela soma o lucro de todos os SKUs que lucram igual ou mais que o da linha, e divide pelo total. É exatamente o acumulado de uma lista ordenada, mesmo com a lista embaralhada. - Classe (J).
=SE(G2<=0;"Prejuízo";SE(I2-H2<80%;"A";SE(I2-H2<95%;"B";"C"))). Repare que a regra olha o acumulado antes do SKU (I2-H2), não depois. Quem cruza a linha dos 80% ainda é classe A, porque ele mesmo ajuda a chegar lá. Com a regra “acumulado menor ou igual a 80%”, esse SKU cairia para B.
Selecione a linha 2 de B a J, arraste até o último SKU e formate H e I como porcentagem. Por fim, ordene a tabela inteira pela coluna G, do maior para o menor (Dados > Classificar), só para ler melhor. Como o acumulado não depende da ordem, a classificação não quebra nenhuma fórmula.
Dois avisos: SKUs com o mesmo lucro dividem o mesmo acumulado, e isso é esperado. E os cortes 80% e 95% são convenção: se a sua operação usa 70/20/10, troque os números dentro da fórmula (ou, melhor, guarde-os em duas células e aponte para elas).
No Hunter, a Análise ABC faz esse trabalho pronto: você escolhe o critério (lucro, quantidade ou receita), filtra por classe e exporta em CSV, sem montar fórmula nem colar relatório.
Como fica a planilha com números?
Abaixo, a aba ABC de uma loja fictícia com 7 SKUs, depois de ordenada por lucro. Todos os números são simulação, criados para você conferir as fórmulas.
| SKU (simulação) | Qtd. | Receita (D) | Taxas (E) | Custo (F) | Lucro (G) | % lucro (H) | % acum. (I) | Classe (J) |
|---|---|---|---|---|---|---|---|---|
| ORG-02 Organizador de cabos | 340 | R$ 10.200,00 | R$ 3.060,00 | R$ 3.060,00 | R$ 4.080,00 | 33,4% | 33,4% | A |
| PRT-01 Porta-treco magnético | 220 | R$ 8.800,00 | R$ 2.420,00 | R$ 3.080,00 | R$ 3.300,00 | 27,0% | 60,5% | A |
| TAP-05 Tapete de pia | 400 | R$ 8.000,00 | R$ 2.480,00 | R$ 2.400,00 | R$ 3.120,00 | 25,6% | 86,1% | A |
| ESC-04 Escorredor dobrável | 150 | R$ 9.000,00 | R$ 3.150,00 | R$ 4.950,00 | R$ 900,00 | 7,4% | 93,4% | B |
| LUM-03 Luminária de emenda | 90 | R$ 7.200,00 | R$ 2.300,00 | R$ 4.320,00 | R$ 580,00 | 4,8% | 98,2% | B |
| KIT-06 Kit de facas | 60 | R$ 6.000,00 | R$ 2.300,00 | R$ 3.480,00 | R$ 220,00 | 1,8% | 100,0% | C |
| CAB-07 Cabide dobrável | 30 | R$ 900,00 | R$ 380,00 | R$ 660,00 | -R$ 140,00 | 0% | fora | Prejuízo |
A conta de uma linha, para você conferir a fórmula do lucro (ORG-02):
- Receita (D): R$ 10.200,00
- Taxas (E), somando comissão, frete e imposto: R$ 3.060,00
- Custo (F): 340 unidades × R$ 9,00 = R$ 3.060,00
- Lucro (G): 10.200 − 3.060 − 3.060 = R$ 4.080,00
O que a planilha mostra:
- O total de lucro dos SKUs positivos é R$ 12.200,00. É esse valor que o SOMASES usa como denominador.
- O tapete de pia é A com a regra do acumulado anterior. Ele termina em 86,1%, mas começa em 60,5%, abaixo dos 80%.
- O escorredor é o segundo em receita e o quarto em lucro. O custo alto come a margem. Trocando a coluna G pela D nas fórmulas H e I, você enxerga a curva por faturamento e compara as duas.
- O cabide dá prejuízo e não entra na conta. Ele precisa de ação própria (preço, frete ou saída do catálogo), não de reposição.
Como resumir as três classes numa tabela?
Monte um quadro ao lado da curva com duas fórmulas por classe. Para a classe A, por exemplo:
- Quantos SKUs:
=CONT.SE($J$2:$J$8;"A") - Peso no lucro:
=SOMASES($G$2:$G$8;$J$2:$J$8;"A")/SOMASES($G$2:$G$8;$G$2:$G$8;">0")
Na simulação: classe A com 3 SKUs e 86,1% do lucro, B com 2 SKUs e 12,1%, C com 1 SKU e 1,8%. Repita trocando a letra.
Como fazer a curva ABC no Google Planilhas?
As fórmulas são as mesmas. Copie as três abas, cole as fórmulas da aba ABC e confira o separador: com a planilha em português, o argumento é separado por ponto e vírgula, e em inglês as funções mudam de nome (SUMIFS, VLOOKUP, IF) e o separador vira vírgula. O Google informa que o separador depende da configuração de idioma da planilha (página do SOMASES no Google Planilhas).
Duas vantagens de usar o Google Planilhas aqui:
- Ordenação por fórmula. A função
SORT(intervalo; coluna; FALSO)devolve a tabela ordenada de forma decrescente em outra aba, sem você clicar em Dados > Classificar todo mês (documentação do Google sobre SORT). Exemplo:=SORT(ABC!A2:J8;7;FALSO), onde 7 é a coluna do lucro. - Compartilhamento. Quem compra e quem cuida dos anúncios enxerga a mesma curva, sem mandar arquivo por e-mail.
O gráfico de Pareto pronto não existe como tipo no Google Planilhas. Monte com colunas para o % do lucro e uma linha para o % acumulado.
Como deixar a planilha de curva ABC automática?
Três ajustes transformam a planilha em ferramenta de rotina: intervalos de coluna inteira, formatação por classe e gráfico.
- Intervalos de coluna inteira nas abas de dados. Por isso as fórmulas usam
Vendas!$B:$Be nãoVendas!$B$2:$B$500. Ao colar novas linhas, nada precisa ser ajustado. - Formatação condicional. Selecione A2:J8, crie uma regra com fórmula (
=$J2="A") e pinte a linha. Repita para B, C e Prejuízo. Você bate o olho e vê onde está o dinheiro. - Gráfico combinado. Colunas para o % do lucro (H) e linha no eixo secundário para o % acumulado (I). Ajuste o eixo da linha de 0 a 100%.
- Cortes em células. Escreva 80% e 95% em duas células (por exemplo, M1 e M2) e troque os números da fórmula da classe por
$M$1e$M$2. Para testar um corte diferente, basta mudar a célula.
Se o seu Excel é anterior ao 2021 (por exemplo, 2016 ou 2019), prefira PROCV em vez de PROCX: a Microsoft informa que o PROCX não está disponível nessas versões (documentação da Microsoft sobre PROCX). Todas as fórmulas deste guia funcionam sem ele.
Quais erros quebram as fórmulas da curva ABC?
Quase todo problema vem do SKU escrito diferente em duas abas ou de um número que o Excel entende como texto. A tabela cobre os sintomas mais comuns:
| Sintoma | Causa provável | Correção |
|---|---|---|
| #N/D no produto ou no custo | SKU da aba ABC não existe na aba Custos, ou tem espaço sobrando | Cadastre o SKU em Custos e limpe espaços com =ARRUMAR(A2) |
| Quantidade ou receita zerada | SKU nas Vendas escrito diferente do da aba ABC | Padronize o código; confira maiúsculas, hífens e espaços |
| SOMASES devolve zero com valores à vista | Números colados como texto (alinhados à esquerda) | Converta para número (Dados > Texto para colunas ou multiplicar por 1) |
| Fórmula não aceita o ponto e vírgula | Planilha em inglês, separador é vírgula | Troque o separador ou o idioma da planilha |
| % acumulado passa de 100% | Intervalo $G$2:$G$8 não cobre a lista toda |
Estenda o intervalo ou use uma Tabela do Excel |
| Classes estranhas após colar vendas novas | SKU novo sem custo cadastrado, lucro calculado errado | Cadastre o custo e confira a coluna F |
Três cuidados que a planilha não faz por você: use o mesmo período para todos os SKUs, mantenha o custo atualizado quando o fornecedor reajustar e deixe produto lançado há poucas semanas fora da curva até ele ter histórico. Se a manutenção começar a pesar, veja por que a planilha de lucro deixa de acompanhar o marketplace.
▶️ Assista: Curva ABC no MERCADO LIVRE: o produto que mais VENDE pode ser o que menos dá LUCRO
Mais aulas na página de vídeos do Hunter.
Perguntas frequentes
Qual é a fórmula da curva ABC no Excel?
São três: o percentual do item (=G2/SOMA($G$2:$G$8)), o percentual acumulado e a classificação com SE aninhado. Neste guia usamos SOMASES para o acumulado, o que dispensa ordenar a lista, e a regra =SE(I2-H2<80%;"A";SE(I2-H2<95%;"B";"C")) para a classe.
Dá para fazer curva ABC no Excel sem ordenar os dados?
Dá. O acumulado com SOMASES soma o lucro de todos os SKUs que lucram igual ou mais que o da linha, então o resultado é o mesmo de uma lista ordenada. Ordene só para facilitar a leitura.
A planilha de curva ABC funciona no Google Planilhas?
Funciona, com as mesmas fórmulas. Em português, o separador é ponto e vírgula. Para ordenar por fórmula, use a função SORT em outra aba.
Preciso do custo do produto para a curva ABC?
Para a curva por lucro, sim. Sem custo, você só consegue classificar por faturamento ou por quantidade, e o resultado pode colocar na classe A um produto que fatura bem e deixa pouco.
De quanto em quanto tempo atualizar a planilha?
Uma vez por mês é um ritmo comum, olhando um período fixo (os últimos 90 dias, por exemplo). Antes de datas sazonais fortes, refaça com o histórico do mesmo período do ano anterior.
Checklist: o que fazer agora
- Crie as abas Vendas, Custos e ABC com as colunas da tabela deste guia.
- Cole as vendas do período e cadastre o custo unitário de cada SKU.
- Escreva a lista de SKUs na aba ABC e arraste as fórmulas de B a J.
- Confira uma linha à mão com a conta de receita menos taxas menos custo.
- Aplique a formatação condicional por classe e monte o gráfico combinado.
- Trate os SKUs com prejuízo à parte (preço, frete ou saída do catálogo).
- Marque na agenda a atualização mensal da curva.
Teste grátis · 7 dias
Curva ABC por lucro sem montar planilha
No Hunter, a Análise ABC classifica seus produtos por lucro, quantidade ou receita e exporta em CSV. Teste grátis por 7 dias.
