Montando uma planilha para lançamentos contábeis no LibreOffice: parte 3

E continuando a construção de um programa contábil partimos para a formação dos relatórios e para tanto, funções de estatística, a saber, o SOMASE, se puder, o CONT.SE e concatenar.

Como eu havia informado, eu poderia construir o programa contábil na planilha baseando todas as informações no plano de contas, mas, só para tornar a coisa um pouco menos comum, contruí tudo sobre o Balancete. Assim, sobre ele eu busquei os dados para os Históricos, lançamentos, Balanço Patrimonial, Demonstração do Resultado do Exercício e Livro Diário.

Para acessar as postagens anteriores, acesse aqui: Parte 1, e Parte 2.

Na figura a seguir apresentamos mais uma vez aquela função do PROCV, em Históricos, em que utilizamos como origem dos dados a planilha do Balancete:

Na figura seguir apresentamos as colunas de dados e as contas filtradas de nossas contas. Para construção não há nada de diferente aqui. O processo que resume em três etapas: 1 – a criação dos números das contas e das contas propriamente ditas¹; 2 – a inserção de colunas denominadas débitos e créditos e 3 – as colunas de saldos e que servirão para que realizemos filtros e até verifiquemos erros para tomarmos as devidas correções.

Uma dica para a construção de nossas contas é reservar vários espaços de contas para compôr grupos. Por exemplo, não tem ideia de quantas contas de clientes você terá em seu plano de contas, crie dez contas lá, todas juntas. Isso fará com que as sequências não sejam perdidas. Pode colocar “clientes 01”, “clientes 02”... “clientes 10”, e à medida em que os clientes venham surgindo bastará trocar os nomes. Isso é algo que aprendemos com o sistema de arquivos das distribuições Linux contra o registro do Windows² e que cabe muito bem aqui.

Em nosso modelo criamos as primeiras contas 1 – Caixa, 2 – Bancos A, 3 – Bancos B. Na terceira coluna deixamos os Débitos, que buscam os valores da planilha de Digitação e, os Créditos, na quarta coluna, buscando também os lançamentos a crédito dessas contas. Na quinta coluna de dados criamos um saldo (débitos menos créditos), depois uma para indicar se ficou devedor ou credor (respectivamente, com sinal positivo ou negativo), o tipo e o teste lógico, que nos indicará se está tudo correto.

Para as colunas de valores de débito e crédito usaremos a função SOMASE, que é uma forma mais complexa do CONT.SE³.

Como está apresentado no quadro, temos a fórmula no Calc na planilha Balancete: =SOMASE(Digitação.$I$7:$I$2045;B5;Digitação.$D$7:$D$2045), que pode ser entendida sintaticamente como a funcionalidade em que você entra com um intervalo de uma coluna de dados com títulos ou textos (se servirão de parâmetro de busca), indica onde está ou digita um valor a ser procurado e depois acrescenta mais o intervalo de uma coluna formada por valores, na mesma planilha onde se extraiu o intervalo anterior, em mesmas linhas, apenas coluna diferente. Na prática temos:

Indicação do intervalo com os dados: Digitação.$I$7:$I$2045: o intervalo selecionado contém os dados dos nomes das contas de débitos em cada lançamento. Por exemplo, se em nosso primeiro lançamento, lá naquela planilha, efetuamos a integralização de capital social contra caixa, naturalmente nossa conta com lançamento a débito será Caixa. Como indicamos que o intervalo vai da linha 5 até 2045, mostramos ao sistema do Calc que este deverá considerar para a análise e busca todos os nomes de contas que constam naquela coluna e naquela planilha. E apenas para reforçar, como utilizamos os $ antes de cada coluna e linha, o espaço é travado, logo, se arrastarmos a fórmula para as linhas de baixo a função será copiada sem acréscimos.

Indicação de parâmetro de busca B5: a célula B5: a célula B5 traz o nome da primeira conta em nosso Balancete, no caso, a denominada Caixa. Como podemos verificar, não há o símbolo $ nem antes da indicação da coluna e nem antes da indicação da linha, o que significa que caso copiemos a fórmula da célula para as linhas de baixo, esta será atualizada para B6, B7, bem como para os lados, C5, D5.Isso aqui é um parâmetro e com ele indicamos para o Calc que daquele intervalo de contas (em que tínhamos vários lançamentos a débito e a crédito, o sistema utilizará apenas aqueles que tenham um texto que corresponda exatamente ao que está escrito em B5 (Caixa). E daí alguém vem perguntar: tá, mas e para os pagamentos? São feitos creditando-se o caixa, logo, não aparecerão na coluna I, mas sim, na coluna K. E respondemos: sim, para isso que selecionamos a coluna que queremos usar como base. Mostraremos isso ao apresentar a fórmula da coluna D, da planilha Balancete.

Indicação das colunas a serem somadas com base no intervalo destacado anteriormente: Digitação.$D$7:$D$2045. Aqui mostramos ao sistema qual coluna contém os dados numéricos que serão somados. Por exemplo, indicamos que no intervalo de células I7 a I2045 constante na planilha Digitação, era para separar apenas as células com o texto Caixa, conforme indicado por B5 e após isso, definimos que era para somar as células dos intervalos D7 a D2045, que correspondiam todos os textos separados contendo o texto Caixa. Temos que pelo resultado, foram lançados a débito na conta caixa um total em R$ 35.830,00.

Na coluna de Crédito (D) utilizamos a seguinte fórmula para encontrar apenas os saldos das contas que nos lançamentos, foram lançadas a crédito: =SOMASE(Digitação.$K$7:$K$2045;B5;Digitação.$D$7:$D$2045). Os entendimentos são os mesmos, apenas que agora mudamos a coluna na nossa planilha de Digitação, para buscar apenas as contas lançadas a crédito (coluna K).

Para as colunas seguintes usamos fórmulas menores: C5 – D5 para a coluna de saldo – lembrando que não acrescentamos o símbolo $, o que indica que ao se copiar a fórmula para as células seguintes a baixo, serão atualizados – e =SE(E5>0;"Devedor";SE(E5=0;"Nulo";"Credor")) para a coluna Ficou. Para as colunas Tipo apenas preenchemos manualmente “Devedor” ou “Crédito” , com base na natureza normal de cada conta. Na última coluna entramos com o teste lógico de =SE(F5="Nulo"; "Nulo";SE(F5=G5;"ok";"Corrigir")). Detalhando em cada caso temos que:

Para =SE(E5>0;"Devedor";SE(E5=0;"Nulo";"Credor")) indicamos ao LibreOffice Calc que para cada resultado na célula E5 (a coluna de saldo, que resulta em Débito ou Crédito) seja maior que zero⁴, o valor quando verdadeiro será Débito, caso seja igual a zero, Nulo e por fim, para qualquer resultado, terá o retorno como Crédito.

Em =SE(F5="Nulo"; "Nulo";SE(F5=G5;"ok";"Corrigir")) usamos a mesma lógica que aplicamos na coluna F. Com a fórmula indicamos que se o resultado de natureza for o que definimos para a respectiva conta o sistema indicará que está correto, caso contrário nos mostrará que falta corrigir. Isso pode ocorrer caso haja qualquer problema na fórmula só SOMASE.

Ao final das edições das fórmulas, copiamos as suas estruturas, arrastando-as para baixo com o mouse e concluímos com a utilização de filtros. Por exemplo, após todos os lançamentos e terminada a verificação de eventuais inconsistências utilizamos o filtro na coluna de Saldo ocultando todas as linhas com valores iguais a Zero. Isso diminuirá o tamanho de nosso Balancete, escondendo contas não utilizadas.

Não detalharemos como fizemos a formatação, ou mesmo daremos dicas de como dar forma ao Balancete que você venha a fazer, tomando como exemplo o nosso, pois acreditamos que cores, formatos de fontes, tamanhos, margem, bem como paginação, local para o nome da empresa e do escritório são coisas que cada um deve personalizar conforme o seu gosto. E com isso concluímos a formação de nosso Balancete no Calc. No próximo tópico apresentaremos como criamos o Livro diário, utilizando para tanto a funcionalidade CONCATENAR, também presente no Microsoft Excel.

1Aqui os números das contas não são aqueles números analíticos (por exemplo, 1.01.01.0001 Caixa Geral), mas sim são apenas os códigos reduzidos, queles que tomamos por base na hora dos lançamentos. Esses números analíticos serão utilizados mais adianta, quando inserirmos a função de concatenar, já nos relatórios.

2Num sistema Windows os arquivos são distribuídos em blocos (algo com uns 60 MB cada um). A cada arquivo novo um novo bloco formado num espaço vazio do disco, e quando o arquivo ultrapassa o tamanho (por exemplo, por alguma edição e com isso o arquivo ficou maior), o que falta vai formar outro bloco, que não ficará junto do anterior. Para ilustrar bem o cenário vamos imaginar que você tem uma coleção de 10 livros em sequência e tem uma prateleira ou estante preparada para o armazenamento com quatro andares. O natural é que você coloque a fila de 10 livros juntos. Vamos supor que você adquira mais coleções de livros e acomode-as ao lado da sua coleção antiga. Cabem 20 livros em cada andar, logo, terá espaço para 80 livros e a sua primeira coleção ocupou metade do espaço de cima, completando o resto com os da segunda coleção e na parte de baixo ocupou metade com a terceira coleção e mais 1/3 com outra. Agora você dobrou aquela a sua primeira coleção, mas a parte onde estava o começo dela já estava copado, bem como a maior parte da parte de baixo. Para resolver você coloca 3 livros na terceira fileira de cima para baixo e os outros você guarda na última fileira, à baixo. Se um dia for precisar de todos os livros terá que pegar lá 10 em cima, 3 em baixo e outros 7 mais em baixo. Não seria melhor deixar espaços maiores para cada coleção já antecipando o aumento delas? Pois é isso o que ocorre com as distribuições Linux: os blocos de arquivos são dispostos bem distantes uns dos outros, de modo que caso corroam aumentos em arquivos, eles sempre poderão ficar juntos. Aí, quando você vai acessar esse arquivo o sistema procurará de forma bem mais dinâmica, pois todos os blocos deles estarão juntos. No Windows isso não ocorre: como a nossa prateleira / estante de livros mal organizada acontece com o sistema das Janelas: os arquivos são alocados muito próximos e isso causa essa desordem caso venham a sofrer acréscimos de dados.

3No CONT.SE dizemos para o sistema somar quantas vezes surge um determinado texto ou número dentro de um intervalo tendo como premissa uma célula que nós indicamos. Em SOMASE dizemos ao Calc para contar os valores com base num que indicamos (conta quantas vezes se repete) e indicamos uma coluna com intervalo para somar apenas destes selecionados.

4Isto é, números positivos, ou seja, o valor a débito seja maior que o valor a crédito, visto que para a fórmula na coluna D usamos Débito menos Crédito.