Fazer a Planilha de Saldo do Estoque

por Tudo Excel | quinta-feira, fev 11, 2016 | 7844 acessos

Este é o Passo 4 para fazer a planilha: Ver Parte 1, Ver Parte 2, Ver Parte 3

Essa é a quarta parte do nosso tutorial, que estamos lhe ensinando fazer uma planilha de controle de estoque. A tabela Saldo, que criamos na primeira parte deste tutorial, vai mostrar o saldo de itens do estoque, com seus valores e locais. Tudo que foi cadastrado e lançado anteriormente no estoque, se resume nesta tabela.

Observe, que todas as planilhas começam na linha 5 com os títulos e a linha 6 é a linha que começamos a fazer o lançamento. Mantenha isso para todas as planilhas que você fizer. Isso vai facilitar na montagem de todas as tabelas em sua pasta de trabalho, quando for inserir as fórmulas. Procure definir um parâmetro para todas as planilhas que você criar, começarem em uma determinada linha, vai facilitar muito e parece ficar mais organizada.

Vamos lá.

A tabela já foi criara com o nome SALDO.

Nesta planilha, vamos usar o formato tabela dinâmica, como nas outras tabelas, como dissemos na primeira parte deste tutorial, definimos um limite máximo de 2000 itens que podem ser cadastrados. Portanto, todas as fórmulas aqui, temos que clicar e arrastar até a linha 2006. Seleciona de A5 a L5 e escolha o formato de tabela, pela guia Página Inicial e na ferramenta formatar como tabela.

Agora altere os títulos das colunas na seguinte ordem:

Item, Código do Item, Descrição, Saldo, Medida, Preço Unitário, Valor Total, Família de Itens, Marca, Estoque, Rua, Local

saldo-de-estoque

Nesta planilha não digitamos absolutamente nada. Ela pega todos os dados das outras planilhas que já fizemos e calcula tudo automaticamente.

O que é necessário aqui é criar as fórmulas que fazem as buscas nas outras planilhas.

Coluna Item: vamos usar a função SE, para achar o valor inserido na mesma coluna da tabela de cadastro dos itens.

=SE(‘Cadastrar-Itens’!A6>=1;’Cadastrar-Itens’!A6;””)

Pode copiar e colar essa fórmula na tabela Saldo. Arraste até a linha 2005 e solte. Lembra que nós definimos no máximo 2000 itens? Vai precisar definir agora.

Coluna Código digite a seguinte formula: =SE(‘Cadastrar-Itens’!A6>=1;’Cadastrar-Itens’!B6;””)

Pode copiar e colar na célula B6, depois clicar e arrastar até a célula 2006. Faça isso com todas as colunas desta tabela. Pode ser feito tudo de uma só vez, quando preencher todas as células da linha 6 clicar na última célula preenchida com as fórmulas e arrastar até o destino final.

Coluna Descrição, digite a fórmula: =SE(‘Cadastrar-Itens’!A6>=1;’Cadastrar-Itens’!C6;””)

COLUNA SALDO

Essa parte é mais difícil

Como você está utilizando o formato de tabela para todas as suas planilhas, é muito fácil montar esta função, ou melhor estas funções, porque vamos usar a função SE e SEMASE, para determinar a condição do cálculo do item para o saldo do estoque.

=SE(A6<=$A$2;SOMASE(Tabela102[Código];[@[Código do Item]];Tabela102[Quantidade])-SOMASE(Tabela10[Código];[@[Código do Item]];Tabela10[Quantidade]);””)

Não se assuste. Essa função é fácil de montar. Vamos descrevê-la aqui.

Vamos tomar por base a célula A6 que está na primeira linha das linhas que tem dados inseridos.

SE A6 for maior, ou igual a um; SOMASE tabela entrada | código | volta para a tabela saldo e pega código do item; Tabela de entrada de materiais | quantidade; menos a tabela de saída de materiais | código | volta para a tabela saldo e pega código do tem; Tabela de saída de matérias | quantidade. Caso contrário a célula fica vazia.

Entendeu?

Não? Então veja abaixo a fórmula completa, copie e cole no local específico

=SOMASE(Entrada!E6;Saldo!B6;Entrada!D6)-SOMASE(Saida!D6;Saldo!B6;Saida!C6)

Acrescente a função SE, porque depois que arrastar a fórmula até a linha 2005, e neste caso se não tiver movimentação, o saldo fica ZERO. Com a função SE as células vazias ficarão em branco.

Basta acrescentar SE(A6<=$A$2; antes de SOMASE e ;””) depois de fechar a fórmula SOMASE.

NOTA: as formulas devem ter exatamente os nomes das planilhas com letras MAÍUSCULAS e minúsculas.

 

Coluna MEDIDA =SE(A6<=$A$2;Tabela8[@Medida];””)

Pega o item da coluna A da tabela saldo e compara com o mesmo item da tabela de cadastro e se for verdadeiro, mostra a unidade de medida.

O Número da tabela pode variar conforme sua adição, para saber o nome da sua tabela basta digitar o sinal de igual em uma célula e clicar na guia da Tabela.

Coluna PREÇO UNITÁRIO =SE(A6<=$A$2;Tabela8[@[Preço Unitário]];””)

Usa a mesma função da coluna Medida.

Coluna VALOR TOTAL é o preço unitário vezes a quantidade, ou seja em nossa planilha G6*D6 – Use a condição SE para limpar as células vazias

As colunas H, I, J, K, L utilizam a mesma função que a coluna E. Só precisa buscar a informação na coluna correspondente, na planilha de cadastrar itens.

 

Acesse os links por partes.

Parte 1: Clique Aqui para fazer cadastro de itens

Parte 2: Clique Aqui para fazer tabela de entrada de materiais

Parte 3: Clique Aqui para fazer tabela de saída de materiais

Parte 5: Clique Aqui para definir estoques mínimo e máximo

Pensou que acabou? Ainda falta a planilha que vai definir os estoques mínimo e máximo.

Essa planilha já pode ser usada, para controla seu estoque.

Se não conseguiu fazer a planilha funcionar, nós temos ela prontinha, funcionando NESTE LINK e custa apenas R$ 29,00 e já está com a tabela que define as compras.

 

Talvez você queira ler também:
 

7 Comments até agora. Sinta-se livre para se juntar a esta conversa.

  1. Tatieli 29/04/2016 at 16:49 - Reply

    Boa tarde,

    Gostaria de tirar um dúvida na planilha saldo, na coluna saldo ele não deveria procurar todo e qualquer valor que estiver na entrada ou saída com o código do produto informado.
    A minha planilha só funciona se a plnilha entrada e saída estiver na mesma ordem da do saldo, se eu inserir uma entrada que esta na linha 20 na planilha de saldo a mesma não é calculada, não entra as 20 peças, poderia me ajudar.

    Grata,

    • Tudo Excel 05/05/2016 at 17:07 - Reply

      a planilha saldo calcula certinho a entrada menos a saída.

  2. Raquel 14/02/2016 at 17:53 - Reply

    Qual a diferença entre Estoque e Saldo???

  3. Fernando 14/02/2016 at 17:21 - Reply

    Amigo, me parece que nessa sua tabela tem um monte de confusão, se inserido algumas fórmulas fazendo as devidas alterações, ainda sim fica errado, pois a sintaxe está errada ou então o raciocínio.

    • Tudo Excel 16/02/2016 at 14:45 - Reply

      Podemos verificar, mas seguimos passo a passo de todas as funções e a planilha está funcionando corretamente. Aliás, já foram vendidas centenas dessa planilha e ninguém reclamou.

  4. Erasmo 25/01/2016 at 11:43 - Reply

    Amigo, to tendo problema pra montar essa função saldo, fiz as formulas como informado no office pro 2010, mas a funçao SE sempre retorna a palavra #NOME? nas celulas que deveriam ficar vazias isso atrapalha muito a visualizar a planilha

    e seguindo o passo a passo acima as tabelas usadas sao 1 2 3 e 4 isso tambem confunde bem o leitor leigo… como eu … primeira vez que uso o excel na vida.

    me ajuda a solucionar isso ai posso enviar a tabela mas fiz passo a passo como no tutorial e tem 4 tabelas… a funçao SE tanto na coluna saldo quanto na cadastrar retornam #NOME? e na coluna saldo as colunas ficam vazias em vez de pegar valores….. isso com base na celula A2 que ta vazia sem nada.

    • Tudo Excel 26/01/2016 at 18:10 - Reply

      Olá Erasmo. Isso pode ser porque algum nome está diferente na célula que você está tentando buscar o valor, ou a função está com algum erro. Por favor revise a função SE.

Deixe um Comentário