Neste artigo você verá várias técnicas de Excel aplicado na Contabilidade.
São 15 técnicas úteis para contadores novatos ou experientes que você verá ao longo do artigo.
Excel para Contabilidade 1 – Procv
O Procv é uma das funções mais úteis do Excel.
Com ela você consegue fazer buscas verticais, ou seja, retornar valores de outras listas.
Isso é feito à partir de uma chave, como no caso abaixo.
No exemplo temos lançamentos e gostaríamos de retornar a descrição da conta.
Para isso usamos a função:
=PROCV(B8;$G$8:$H$17;2;0))
Nela temos apenas 4 parâmetros para a busca exata.
O primeiro é a chave, o que será procurado.
O segundo é aonde, considere sempre a primeira coluna aonde estiver a chave e selecione todas as colunas que fazem parte da busca.
O terceiro é qual coluna deseja retornar á partir do segundo parâmetro.
E o quarto é 0, para uma busca exata, retornar apenas se encontrar.
No Guia do Excel temos várias aulas sobre a função PROCV, isso porque ela pode ser usada de várias formas diferentes para resolver outros problemas.
Veja aqui os vários artigos: https://www.guiadoexcel.com.br/?s=procv para mais.
Excel para Contadores 2 – Converter Texto em Valor no Excel
A segunda dica é uma dúvida frequente.
Por que o meu Excel não soma?
Isso acontece muitas vezes porque a coluna está como texto e não como valor.
Para resolver isso coloquei 3 soluções.
Técnica 1: Use a função Valor.
A função Valor converte um texto em valor.
=Valor(C8)
Para usá-la apenas passe a célula que deseja converter em valor.
Técnica 2 : Multiplicação
A segunda técnica uso sempre. Basta multiplicar por *1 o valor para que haja uma conversão implícita pelo Excel.
=C8*1
Substitua o C8 pela célula que quer converter.
Técnica 3: Colar Especial
A terceira técnica exige algumas etapas, mas é interessante porque evita uma coluna auxiliar.
- Digite 1 em qualquer célula que não contenha dados.
- Copie o valor.
- Selecione os textos que quer converter.
- Clique com o botão direito em colar especial e selecione: Valores, Multiplicação e clique em OK
Os textos serão multiplicados por 1 e implicitamente serão também convertidos.
Excel para Contabilidade 3 – Arredondar Valores
Outra dúvida frequente é como arredondar valores no Excel.
Para isso não basta usar a formatação do Excel, pois ela arredonda apenas aparentemente os valores.
Se for o que deseja, ok, mas se quiser arredondar o valor para cálculos, siga abaixo:
Todas tem 2 parâmetros, o valor a arredondar e a quantidade de casas decimais do arredondamento.
- Arred: =ARRED(C8;0). O valor a arredondar é C8 e a quantidade de casas decimais é 0. Usa a regra de arredondamento padrão matemática.
- Arredondar.para.baixo: =ARREDONDAR.PARA.CIMA(C8;0). O valor arredonda para cima sempre.
- Arredondar.para.cima: =ARREDONDAR.PARA.BAIXO(C8;0). O valor será arredondado para cima.
Se depois de arredondado não quiser mais as fórmulas, copie e cole como valores os resultados.
Excel para Contadores 4 – Contas T e Razonetes no Excel
Algo muito frequente na vida de um contador é a necessidade de realizar análises usando Contas T, razonetes.
Para isso criamos uma solução gratuita no Excel que automatiza este processo.
Veja no vídeo abaixo como funciona:
Excel para Contabilidade 5 – Agregar
A função Agregar permite a realização de diversas contas no Excel, é realmente muito útil e seu uso vai do básico ao avançado.
No nosso exemplo, ela se resume a realizar contas aonde temos colunas com erros ou com dados filtrados, aonde desejamos ignorar os erros e considerar apenas os valores exibidos.
Ela possui 19 opções de cálculo, sendo que as que usamos no exemplo são para contar, fazer média e soma de valores que estão sendo exibidos.
Contar valores: =AGREGAR(2;7;Tabela4[Valor])
Média: =AGREGAR(1;7;Tabela4[Valor])
Soma: =AGREGAR(9;7;Tabela4[Valor])
Ela neste modelo possui 3 parâmetros:
- O Cálculo, o que deseja fazer com os dados.
- Quais valores deseja considerar
- A lista de valores a aplicar o cálculo
Veja mais sobre a função Agregar neste artigo: Agregar Excel – Como Usar
Excel para Contadores 6 – Funções Condicionais
Em qualquer profissão as funções do Excel mais utilizadas são as condicionais.
Com elas conseguimos realizar cálculos considerando apensa o que desejamos de uma base de dados ou aplicar lógicas aos dados.
No exemplo usamos a função SOMASES, aonde somamos os planos de contas no DRE para cada um dos meses.
A função que usamos para a soma foi:
=SOMASES(Tabela5[Valor];Tabela5[Demonstrativo de Resultado];$F8;Tabela5[Mês];G$7)
A função SOMASES possui no mínimo 3 parâmetros, sendo que os dois últimos podem criar vários.
- O intervalo de soma
- Intervalo de critérios
- Critério
Com isso temos então a aplicação da soma somente se os critérios sejam atendidos, sendo que se houverem mais critérios, como no caso acima, basta colocar mais um ; e passar o outro intervalo de critérios e em seguida passar outro ; e o critério. No caso adicionamos o mês.
Veja muito mais sobre SOMASES neste artigo: https://www.guiadoexcel.com.br/funcao-somases-varias-condicoes-para-soma/
Excel para Contabilidade 7 – Tabela Dinâmica
A tabela dinâmica permite realizarmos sumarizações de dados de uma forma instantânea e com o mínimo esforço.
Veja o relatório abaixo:
Veja que no exemplo temos um relatório muito rico com a soma de valor de nota fiscal, IPI, PIS e COFINS por CFOP e produto de uma base de notas fiscais.
Tudo feito com o arrastar do mouse.
Aprenda mais sobre tabelas dinâmicas neste vídeo:
Excel para Contadores 8 – Conciliação de Dados
A conciliação de dados consiste na comparação de informações à partir de sua chave e o batimento de valores.
Por exemplo, comparação de uma valores de um relatório financeiro com os lançamentos contábeis.
Veja no exemplo abaixo:
Nele temos 4 colunas:
- Número da nota fiscal
- Valor Contábil
- Valor Financeiro
- Comparação de valores
Com isso temos linha a linha quanto temos no relatório financeiro e contábil e na última coluna temos a comparação dos valores.
Aonde está zero, é porque não há diferença, e aonde temos valor, é porque não está em um dos dois lados da comparação ou porque há diferença de valores.
Não há uma forma só de fazer isso.
Mas eu gosto particularmente da solução abaixo:
Excel para Contabilidade 9 – Adicionar Data
Para adicionar datas você pode adicionar meses.
Para isso podemos usar a função DATAM.
=DATAM(B8;1)
Ela possui dois parâmetros, a data, e a quantidade de meses.
A quantidade de meses é um número inteiro, que pode ser positivo ou negativo e com ele você terá quantos meses quer adicionar ou remover da data inicial.
Excel para Contadores 10 – Primeiro Dia e Último Dia do Mês
Outra dúvida frequente é como calcular o primeiro e o último dia do mês.
Para estes dois problemas podemos usar a mesma função.
A função FIMMÊS retornar o último dia do mês.
Para retornar o último dia do mês no Excel use a seguinte fórmula:
=FIMMÊS(B8;0)
Substitua o B8 pela célula de data e o 0 pela quantidade de meses que deseja, se for o atual, coloque 0, anterior -1, sequente 1, mude esta quantidade conforme os meses que desejar.
Para o primeiro dia do mês use a função abaixo:
=FIMMÊS(B8;-1)+1
Veja que simplesmente adicionamos 1, então se quisermos o primeiro dia do mês atual, colocamos o último dia do mês anterior + 1.
Excel para Contabilidade 11 – Adicionar Dias Úteis
Para adicionar dias úteis a uma data podemos usar a função DIATRABALHO.
Esta função permite que calculemos uma data futura à partir de uma data inicial, quantidade de dias úteis e também uma lista de feriados.
No nosso exemplo usamos a função da seguinte forma:
=DIATRABALHO(F8;G8;$B$8:$B$1073)
Sendo os parâmetros:
- Data inicial
- Quantidade de dias úteis
- Lista de feriados para desconsiderar
Excel para Contadores 12 – Quantidade de Dias entre Datas
Para realizamos o cálculo da quantidade de dias entre duas datas podemos fazer simplesmente uma data menos a outra no Excel.
Mas para realizarmos cálculos como ano comercial, ou seja 360 dias e considerando cada mês 30 dias portanto, você deve usar a função DIAS360:
=DIAS360(F8;G8)
Os parâmetros necessários são a data inicial e a data final apenas.
E para calcular a quantidade de dias úteis entre duas datas usamos a função DIATRABALHOTOTAL.
=DIATRABALHOTOTAL(F8;G8;B8:B1073)
Nela passamos os parâmetros:
- Data inicial
- Data final
- Lista de feriados a desconsiderar do cálculo
Excel para Contabilidade 13 – Importar Arquivo Texto
O Excel permite importar dados de muitas formas, inclusive arquivos texto.
E esta é uma forma de comunicação muito comum entre fiscal, contábil e governo.
Uma forma de importar dados de arquivos texto no Excel é usando um recursos que existe desde o Excel 2010, chamado Power Query.
O Power Query é uma ferramenta de ETL poderosa que permite que realizar a extração, tratamento e carregamento dos dados com etapas.
O resultado final que temos ao importar o arquivo texto é o seguinte:
Veja mais sobre neste vídeo:
Excel para Contadores 14 – Extrair Dados com Preenchimento Relâmpago
O preenchimento relâmpago é um recurso do Excel que permite que possamos extrair dados de forma automática ou ainda como preencher dados com base em células.
No exemplo abaixo temos o histórico do lançamento contábil e nele podemos extrair as informações usando o preenchimento relâmpago.
Basta na coluna ao lado digitar um valor que deseja extrair e em seguida pressione CTRL+E, veja que os dados são extraídos conforme o exemplo e preenchidos abaixo.
Veja mais sobre Preenchimento Relâmpago neste artigo.
Excel para Contabilidade 15 – Análise Rápida
Um recurso interessante do Excel é a análise rápida.
Com ela você pode selecionar dados que deseja analisar e clicar na opção no canto inferior direito.
Nele selecione Formatação, Gráficos, Totais, Tabelas e Minigráficos.
Download Excel para Contabilidade
Clique no botão abaixo para realizar o download do arquivo de exemplo:
