Para montar uma planilha de cálculo de entradas e saídas, como um arquivo para controle financeiro, por exemplo, podemos calcular esses valores basicamente de duas formas: 1) usando números negativos e positivos e realizando uma soma simples; ou 2) inserindo sempre números sem sinal e alterando a forma como o Excel realiza os cálculos de acordo com a operação atribuída a cada um desses valores (entrada ou saída).
Neste vídeo vamos aprender a calcular entradas e saídas usando como parâmetro justamente a operação relacionada a cada um dos valores, sem que precisemos diferenciar sinais para valores negativos. Veremos como fazer isso de duas maneiras, sendo a segunda a mais abrangente.
Compreendendo a lógica dos cálculos
Observe a planilha abaixo. Assim que um valor é inserido na coluna Valor, o Excel adiciona ou subtrai esse valor e mostra o resultado na coluna Valor Final, repetindo o processo para cada operação inserida. Note que os números não são digitados com diferenciação de sinais (positivos ou negativos); a adição ou subtração ocorre de acordo com o item escolhido na lista suspensa presente na coluna Operação (entrada ou saída).
Desse modo, não precisamos inserir um sinal negativo nos valores sempre que eles representarem uma saída. Nossa fórmula é que realizará a operação de acordo com o tipo de movimentação.
Para começar, temos que entender que a primeira linha sempre exibirá o primeiro valor inserido. Já da segunda em diante teremos um padrão novo: o Excel buscará o valor final da linha anterior e realizará uma soma ou subtração (de acordo com a operação) usando o número inserido na coluna Valor. É isso que definirá o saldo final para cada uma das linhas.
Com isso em mente, vamos começar.
Calculando a primeira linha
O cálculo da primeira linha é o mais simples, pois exibirá apenas o número digitado como o primeiro valor. No entanto, esse valor pode ser positivo ou negativo, portanto, desde já, precisaremos testar a operação dessa linha.
Para melhorar ainda mais nossos cálculos, vamos acrescentar à nossa fórmula uma condição que observa se a operação é uma entrada, uma saída ou se a célula ainda está vazia. Caso a célula esteja vazia, o Excel não realizará nenhuma operação, porque estará indefinido o tipo de operação.
=SE(D5=””;“”;SE(D5=”Entrada”;E5;-E5))
Usando a função SE, testaremos, na célula G5, se a célula D5 (que contém o tipo de operação) está vazia (“”). Caso esteja, o Excel simplesmente retornará vazio (“”), ou seja, se não há operação definida, nenhum cálculo será feito. Isso é muito útil caso estendamos a fórmula de modo sequencial por autopreenchimento para uma quantidade grande de células, inclusive abrangendo intervalos que ainda não foram preenchidos com nenhum dado em nossa planilha.
O próximo argumento da função SE solicita o que o Excel deve fazer se o teste for falso, ou seja, se a célula não estiver vazia. Nesse caso, teremos um novo teste para verificar se a operação é uma entrada ou saída. Caso a operação seja uma entrada (D5=”Entrada”), o Excel exibirá simplesmente o número contido em E5, ou seja, o valor propriamente dito, como número positivo. Entretanto, se a opção não for “Entrada”, só pode ser “Saída” (é a única opção restante).
É útil destacar que, em nosso exemplo, estamos usando uma validação de dados do tipo lista suspensa em nossa coluna de operações. Essa validação impede que outro valor seja digitado na célula a não ser os dois presentes.
Bem, nesse caso, só há três probabilidades: ou a célula está vazia, ou contém o texto “Entrada” ou contém o texto “Saída”. Já testamos a célula vazia e o valor “Entrada”; só nos resta definir um resultado para o valor “Saída”.
Como só há três possibilidades e já testamos duas, não há necessidade de um terceiro teste, já que sobra apenas uma alternativa provável. Nesse caso, o valor_se_falso da nossa função SE já corresponde ao texto “Saída”, portanto podemos definir o que ocorrerá nesse caso diretamente como teste final. Nesse cenário, o Excel exibirá o valor com sinal negativo (-E5).
Ao trocarmos o tipo de operação, notamos a mudança de sinal no valor final.
O propósito deste artigo é apenas calcular entradas e saídas, não formatar os valores conforme a operação. Você pode notar que, em nosso exemplo, os valores ficam vermelhos quando são saídas e azuis quando são entradas, mas esse é objeto de outro artigo. O que nos interessa, no momento, é apenas o cálculo de valores.
Finalizada a primeira linha, temos agora duas alternativas. Vamos compreender a primeira e depois seus impasses, para que fique mais simples entender o segundo método e suas vantagens.
Método 1: Soma em sequência
Daqui em diante, a ideia é sempre usar o último valor final e realizar uma soma ou subtração com o novo valor inserido na linha seguinte, tudo de acordo com o tipo de operação.
Na célula G6, vamos inserir a seguinte fórmula:
=SE(D6=””;“”;SE(D6=”Entrada”;G5+E6;G5-E6))
O primeiro teste da função SE é idêntico ao que fizemos na primeira linha: vamos antes testar se a célula das operações está vazia. Caso ela contenha o texto “Entrada”, o Excel buscará o valor final da linha anterior (G5) e somará com o valor inserido para a operação da linha em questão (E6). Caso contrário, há apenas uma outra alternativa: a operação só pode ser “Saída”. Portanto, o valor falso dessa segunda função SE é justamente o valor anterior (G5) menos o valor da linha em questão (E6).
Perceba como o valor é alterado conforme modificamos a operação.
Ao aplicarmos o autopreenchimento, a fórmula funciona corretamente, desde que haja dados devidamente preenchidos. No caso de querermos inserir um valor no final da tabela apenas como lembrete, sem uma data ainda definida, por exemplo, o Excel não calculará o valor final corretamente.
Como na linha 19 não inserimos nenhuma operação, o Excel retornou um valor vazio para a célula que contém o valor final. A próxima linha depende do valor final da antecessora para que seja realizada a operação, entretanto o programa não consegue somar ou subtrair um número do “vazio”, o que resulta o erro #VALOR!, indicando incompatibilidade de tipos de dados.
Mesmo que substituamos o termo vazio (“”) por zero (0), ainda sim teremos um erro, porém diferente do anterior:
=SE(D6=””;0;SE(D6=”Entrada”;G5+E6;G5-E6))
Nesse âmbito, o Excel busca o valor final antecessor (zero) e realiza a soma/subtração com o valor da linha atual, o que não nos retorna o valor correto, já que não leva em consideração os valores anteriores.
Esse método pode funcionar caso você opte apenas por inserir valores sempre em sequência. Caso não, o segundo método pode ser mais relevante.
Método 2: Soma condicional
Nesse segundo método, faremos uma soma de todas as entradas e subtrairemos pela soma de todas as saídas, porém de forma sequencial, cada operação restrita à sua própria linha. Talvez seja mais fácil observar toda a fórmula e compreendê-la posteriormente em partes.
=SE(D6=“”;“”;SOMASES($E$5:E6;$D$5:D6;”Entrada”)–SOMASES($E$5:E6;$D$5:D6;”Saída”))
Para realizar esse tipo de cálculo, faremos uso da função SOMASES, que realiza somas de acordo com condições específicas.
Somaremos o intervalo que vai de E5 até E6, ou seja, da primeira célula da tabela até a célula da linha atual, usando como parâmetro o intervalo de D5 até D6, ou seja, a coluna de operações. Dentro desse intervalo de parâmetros, analisaremos todos os que forem iguais ao texto “Entrada”. De modo resumido, o Excel somará todos os valores do intervalo da coluna VALOR cujo texto correspondente na coluna anterior (OPERAÇÃO) seja “Entrada”.
Observe que usamos referência absoluta nas células D5 e E5, ou seja, elas foram inseridas na fórmula desta maneira: $D$5 e $E$5, diferentemente do limite do intervalo, que é definido de forma usual, com referência relativa (D6 e E6). Essa técnica fará com que, ao usarmos autopreenchimento, o Excel mantenha D5 e E5 sempre como o início do intervalo, ao passo que o fim desse intervalo será alterado de acordo com a célula da linha em questão.
Veja a alteração da abrangência na linha 11, por exemplo:
Dessa maneira, o programa sempre restringirá nossa operação ao intervalo delimitado pela linha atual da fórmula.
A mesma dinâmica é aplicada à função SOMASES que calculará a soma das saídas. De forma minimalista, a explicação do que está ocorrendo nessa fórmula é bem simples: se a operação for vazia, o Excel exibirá o valor vazio; caso não, somará os valores da coluna E (VALOR) cuja célula correspondente na coluna D (OPERAÇÃO) seja “Entrada” e subtrairá da soma dos valores da coluna E cuja célula correspondente na coluna D seja “Saída”. Ou seja, estamos basicamente fazendo um balanço de todas as entradas e saídas até o ponto atual de cada fórmula.
Assim, nosso problema de “pular linhas” é solucionado. Ao sempre abranger um intervalo sequencial e fazer uma soma baseada na operação, o Excel não precisará usar o último valor final (que estará vazio na linha anterior). Como não há valor e/ou operação, não há condições que satisfaçam os critérios de nenhuma das SOMASES, portanto a linha em branco é ignorada.
Somando entradas e saídas
Por fim, usaremos também a SOMASES para realizar a soma de todas as entradas e de todas as saídas:
=SOMASES(E:E;D:D;”Entrada“)
=SOMASES(E:E;D:D;”Saída“)
O Excel somará todos os valores da coluna E (E:E) cuja célula correspondente na coluna D (D:D) contenha o texto “Entrada”. Para as saídas, a operação é a mesma, alterando-se apenas o critério.
Já o saldo nada mais é que a soma das entradas menos a soma das saídas (=J2-L2).
- Para evitar o descuido de se digitar números negativos para saídas (o que resultará em cálculos errados), você pode usar validação de dados para não permitir a digitação de números negativos, por exemplo.
