Blog

Postado em em 7 de fevereiro de 2023

Controle de Estoque Automático no Excel – Aprenda a Criar Um Hoje

Planilha de controle de estoque automático no Excel! Você sabe montar uma do absoluto zero? Aprenda detalhado nesta aula!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

Fala Impressionadores! Na aula de hoje eu quero te mostrar como criar um controle de estoque automático no Excel do zero!

Então mesmo que você ainda não tenha o seu controle, você vai conseguir entender como ele funciona e como criar o seu próprio controle para os seus produtos!

Para esse controle de estoque nós vamos utilizar a função SOMASES no Excel para conseguir calcular o estoque dos produtos que temos.

Vamos utilizar a função PROCV do Excel dentro da validação de dados para garantir que o usuário não registre uma venda com um valor maior do que tem em estoque!

Dessa forma você não vai ficar com o estoque negativo e consegue ter esse controle eficiente.

E aí, abra seu Excel e vamos construir do ZERO usando apenas os recursos de SOMASES, VALIDAÇÃO DE DADOS e PROCV?

Exemplo Prático!

Primeiro vamos precisar de um cadastro de produtos, vendas e estoque, faça segundo o seu negócio!

Cadastro de produtos
Cadastro de produtos

Para deixar mais organizado vamos inserir tabela -> Para inserir tabela selecione uma das planilhas -> Vá na guia Inserir -> Tabela -> Marque a opção “Minha tabela tem cabeçalhos” -> Dê ok.

Inserir tabela
Inserir tabela

Faça isso para a tabela de Data Compra e Data Venda.

Agora temos que definir a quantidade em estoque, afinal nossa tabela serve justamente para não nos deixar vender o que não temos em estoque!

A quantidade em estoque será o resultado entre a quantidade comprada menos a quantidade vendida, certo?

Qtd_Estoque = Quantidade Comprada – Quantidade Vendida

Qtd_Estoque
Qtd_Estoque

Como fazer este cálculo de forma automática?

Primeiro para somar todas as compras vamos usar o SOMASES

SOMASES
SOMASES

O intervalo de soma será -> a coluna de quantidades, o intervalo de critérios será -> a coluna de produtos e por fim o critério será -> o nome do produto entre aspas ou a célula que contém o nome do produto.

Intervalos
Intervalos

Feito isso, vamos colocar o sinal de menos (-) e fazer outro SOMASES para calcular a soma dos produtos vendidos, ou seja, o primeiro argumento será -> a quantidade de produtos vendidos o segundo será -> a coluna com os produtos e o terceiro argumento será -> a célula A2 que é a mesma célula utilizada no primeiro SOMASES, a célula onde temos ‘Bermuda’.

Função completa
Função completa

Nossa função tem então dois SOMASES um que soma a quantidade comprada e outro que soma a quantidade vendida, estamos subtraindo um do outro.

Um SOMASES para cada tabela
Um SOMASES para cada tabela

Agora que esta parte esta pronta, vamos arrastar para baixo a função e obter os valores para camisa e moletom.

Arraste a função
Arraste a função

Perceba que agora o cálculo está automático, ele vai a cada mudança nas quantidades alterar os valores de estoque.

Feito isso vamos inserir planilha na tabela de Qtd_Estoque como fizemos com as outras tabelas e para facilitar podemos adicionar o título em cada tabela também.

Formatar tudo como tabela
Formatar tudo como tabela

Agora vamos acrescentar na tabela de vendas mais uma venda de 10 bermudas, observe que o nosso estoque vai ficar negativo.

Controle de Estoque Automático no Excel
Valor negativo

Precisamos impedir a tabela de aceitar na tabela de vendas um valor superior ao que temos em estoque.

Para fazer isso, vamos utilizar a validação de dados na coluna de quantidade da tabela de vendas.

Clique na primeira célula da coluna -> Vá na guia Dados -> Validação de dados

Validação de dados
Validação de dados

Vamos usar a opção personalizado, assim com o PROCV vamos fazer uma regra para impedir que o valor fique negativo.

Controle de Estoque Automático no Excel
Personalizado

No PROCV:

  • O primeiro argumento será na tabela de vendas o nome do primeiro produto
  • O segundo argumento será as duas colunas que contêm a tabela onde vamos encontrar este produto (Qtd_Estoque)
  • O terceiro argumento é a coluna da tabela Qtd_Estoque que queremos retornar o resultado, neste caso a 2º coluna
  • O quarto argumento é 0 porque queremos uma correspondência exata.
Controle de Estoque Automático no Excel
Função

Feito o Procv fechamos os parênteses, o resultado do Procv até aqui pode ser positivo ou negativo, mas, como queremos excluir a possibilidade de resultados serem negativos vamos após o último parênteses colocar que o resultado deve ser maior ou igual a zero >=0.

=PROCV(l3;A:B;2;0) >= 0

Criada a regra, dê ok.

Observe que agora qualquer valor até 0 de estoque é permitido

Controle de Estoque Automático no Excel
Estoque zerado

Valores que deixam o estoque negativo geram uma mensagem de erro

Mensagem de erro
Mensagem de erro

Replicando a validação para as demais células

Para isso vamos copiar (control + C) a célula onde fizemos a validação de dados -> com o mouse selecione todos os valores abaixo:

Controle de Estoque Automático no Excel
Replicando a validação de dados

Na guia página inicial -> clique em colar -> colar especial -> selecione a opção Validação. Feito isso, todas as linhas vão obedecer a mesma validação de dados.

Personalizando a mensagem da validação de dados

Para personalizar vamos selecionar todas as células da tabela onde fizemos a validação.

Na tabela de Vendas -> selecione a coluna Quantidades -> Vá na guia Dados -> Novamente clique em validação de dados -> Clique em Alerta de erro

Controle de Estoque Automático no Excel
Mensagem personalizada

Personalize a mensagem a seu gosto, neste caso o título foi modificado para “ATENÇÃO” e a mensagem ficou -> “Não existe produto em estoque para esta venda!”.

Controle de Estoque Automático no Excel
Resultado

Feito isso, não apenas impedimos a venda de um item que não temos em estoque como também educamos para que o pedido do produto em falta seja programado, mostrando o motivo do erro.

Conclusão – Controle de Estoque Automático no Excel

Nesta aula trouxemos um passo a passo detalhado de como preparar uma planilha de estoque do zero no Excel.

Para fazer esta planilha usamos os recursos de SOMASES, VALIDAÇÃO DE DADOS e PROCV do Excel, caso você tenha dúvidas sobre esses conceitos, temos links das aulas detalhadas.

Durante a aula você vai entender não só como montar a planilha, mas também a linha de raciocínio usada para construir essa ferramenta que será muito útil para o seu negócio.

Espero que tenham gostado e que aproveitem o material! Um abraço e até a próxima!

Hashtag Treinamentos

Para acessar outras publicações de Excel Básico, clique aqui!


Quer aprender mais sobre Excel com um minicurso básico gratuito?