Hoje trago para vocês mais um modelo de planilha, que utilizará algumas das fórmulas e conceitos vistos até agora. Esta planilha simula o extrato bancário de uma pessoa. Ou seja, a pessoa vai digitar todas as suas entradas e saídas, bem como a descrição das mesmas, e o Excel, após a inserção das fórmulas e funções, irá dando “parciais” do que a pessoa possui no momento em sua conta.

Esta planilha utiliza as funções e recursos abaixo. Caso você não conheça o funcionamento de alguma delas, recomendo que você clique sobre o nome da mesma para saber sobre o seu funcionamento antes de prosseguir:

• Função SE;
• Função CONT.SE;
• Função SOMASE;
• Validação;
• Alça de preenchimento;
• Referência absoluta, relativa e mista no Excel;

O modelo que irei utilizar é o da imagem abaixo:

Temos que “sobrou” em nossa conta o valor de R$823,75. Este valor é o saldo que havia na conta no dia 31 de dezembro, que foi o último dia antes de começar o mês do exemplo, que é janeiro. Sendo assim, na primeira linha, não teremos nenhum tipo de fórmula, apenas inseriremos o valor. Caso houvesse uma planilha de mês anterior, seria possível “copiar” o valor desta planilha. Para isto seria necessário “buscar” o valor que estaria na outra planilha. Se você não souber como fazer isto, clique aqui.

Na coluna Tipo, irei fazer a Validação dos Dados. Não é algo obrigatório, porém recomendo. Neste caso, será possível escolher apenas entre as opções pré-definidas. As opções que irei inserir neste caso são C para crédito (ou seja, quando entra dinheiro na conta) e D para débito (quando sai dinheiro da conta). É possível fazer isto de duas maneiras. Veja como no post que fiz sobre validação.

O campo do Valor será inserido. Não será utilizada nenhuma fórmula aqui. Apenas será feita a formatação no estilo Moeda. Pressione CTRL + SHIFT + 4 ou use a janela Formatar Células para isso. A coluna que será usada fórmula é a coluna  Saldo.

Esta coluna funciona da seguinte maneira: a cada linha quando se insere algum valor, primeiro o Excel terá que verificar se é Entrada ou Saída. Caso seja entrada, irá somar o valor informado ao saldo existente. De maneira similar, a planilha deve subtrair o valor informado do saldo caso seja saída. O que irá determinar se é entrada ou saída é a coluna Tipo, que será preenchida com C ou D. Exemplo: o saldo inicial é R$823,75. Caso preenchamos a coluna do tipo com C e digitemos o valor R$100, o saldo parcial deve ficar em R$923,75. Caso seja preenchido com D, o saldo ficará R$723,75.

Para fazer isso, primeiro precisamos verificar na coluna Tipo se o valor é C ou D. Caso seja C, iremos pegar o saldo atual (está sempre na linha de cima) e somar com o valor informado. Caso não seja C, o Excel terá que diminuir o valor. Ou seja, teremos que usar a função SE. Para começar, clique em D4 e digite o seguinte:

=SE(B4=”C”;D3+C4;D3-C4)

Veja um exemplo de como ficaria neste caso:

Note que o valor em D4 diminui em R$123,00 comparado com o saldo anterior. Isso acontece porquê o valor D está em B4. Caso estivesse o valor C, o valor teria sido somado em vez de diminuído, e passaria a ser de R$946,75. Insira a fórmula e faça o teste.

Para as linhas seguintes, você pode, após inserir a primeira fórmula, “puxar” a fórmula usando a alça de preenchimento. Porém, se você é novo na função SE, recomendo que faça uma a uma, para ir praticando.

É necessário aqui ter atenção para um pequeno detalhe: caso você arraste, ou até mesmo digite a fórmula mencionada acima, todas as linhas, mesmo que estejam em branco, aparecerão com o saldo preenchido. Para evitar isso, vamos fazer uma “correção” na fórmula. Usando a função SE novamente, porém antes da que já está, iremos dar o comando para que o Excel verifique se a célula está em branco ou não. Você precisa adicionar =SE(C4=””;”” antes da fórmula já existente. No final, para a linha 4, ficará assim:

=SE(C4=””;””;SE(B4=”C”;D3+C4;D3-C4))

Após isso, é só preencher as linhas seguintes, substituindo C4 por C5, B4 por B4, D3 por D4 e assim por diante. Veja um exemplo de planilha preenchida:

Na tabela “de fora”, que está entre as colunas H e J, vamos utilizar as funções CONT.SE e SOMASE para resolvê-las. Em I8 e I9, vamos calcular quantas informações do tipo C e do tipo D que aparecem na tabela. Em J8 e J9, vamos calcular o total (soma em reais) de cada um destes tipos. Na imagem acima, por exemplo, vemos que o tipo C aparece duas vezes, sendo que a soma de ambas é R$1.150,00 (R$900 + R$250). Não vou explicar as funções mencionadas, pois já fiz posts relacionados. Veja os links no começo deste post.

Vou passar as fórmulas que serão usadas. Em caso de dúvidas, veja os posts ou entre em contato. A fórmula para I8, que calcula a quantidade do tipo C, é:

=CONT.SE($B$4:$B$19;H8)

Em lugar de H8, poderíamos ter usado “C”, entre aspas. A fórmula para J8, que calculará a soma das linhas onde aparecem o tipo C é:

=SOMASE($B$4:$B$19;H8;$D$4:$D$19)

Note que apareceram cifrões – $ – entre os endereços das células. Fiz isto para que as células ficassem fixas e pudessem ser puxadas com a alça de preenchimento. Para saber mais, veja este post.

Por fim, temos o saldo do mês e o saldo atual. O saldo do mês se refere apenas à movimentação do mês. Ou seja, o total de entradas menos o total de saídas. A fórmula fica =J8-J9. Já o saldo atual se refere ao saldo anterior (de dezembro) mais o que entrou e saiu em janeiro. A fórmula fica  =D3+I10 ou =D3+(J8-J9). Esta parte ao final ficará assim:

Estou disponibilizando a planilha para download. Clique abaixo para baixar:

[wpdm_package id=’1968′]

Por hoje é isso. Em caso de dúvida, é só entrar em contato.

Ueritom