Análise financeira eficiente: automatize o Excel com Power Query e Power Pivot

Descomplicando bases de dados com Power Query - Apresentação

Olá! Eu sou Roberto Sabino, instrutor na Alura, e começaremos juntos uma jornada tecnológica voltada para finanças. Se você é do mundo das finanças e busca auxílio para integrar mais tecnologia, especialmente o Excel, em seu cotidiano, te damos boas-vindas a essa grande jornada.

Audiodescrição: Roberto se descreve como um homem de pele clara, com cabelo e barba escuros e curtos, levemente grisalhos, e olhos castanho-escuros. Ele veste uma camiseta preta lisa, e está sentado em frente a uma parede clara iluminada em gradiente azul.

O que vamos aprender?

Este curso é destinado a quem quer aprender a automatizar tarefas, consultas, e aprimorar o tratamento de dados, utilizando inteligência artificial, Power Query e VBA com o Excel.

Iniciaremos com algumas análises necessárias, a partir de arquivos de texto disponibilizados pelos departamentos, os quais transformaremos em análises posteriormente. Ao longo das aulas, realizaremos diferentes análises utilizando o Excel com Power Query.

Além disso, vamos explorar recursos específicos do Power Query, como transformações de colunas e dados ou combinações de tabelas de dados, por exemplo.

Será uma experiência muito enriquecedora!

Este curso é projetado para proporcionar uma aprendizagem ativa. Logo, você irá interagir com os conteúdos e poderá aplicá-los em seu dia a dia. Em algumas aulas, você terá a oportunidade de realizar atividades por conta própria, e depois mostraremos como proceder, garantindo que sempre estaremos trabalhando com os conteúdos. É fundamental participar ativamente.

Conclusão

Lembre-se! Você tem acesso ao fórum e ao Discord, onde pode interagir com outras pessoas estudantes e esclarecer suas dúvidas, promovendo uma aprendizagem ainda mais ativa.

Este primeiro passo da formação está incrível e vamos aprender muito sobre Power Query. Venha conosco começar essa jornada para integrar a tecnologia ao nosso dia a dia no mundo das finanças!

Descomplicando bases de dados com Power Query - Por que as bases são tão complexas?

Antes de iniciarmos nossa jornada, é importante estabelecer alguns combinados.

Alinhando expectativas

O primeiro combinado é que este não é um curso de Excel ou de conceitos de finanças. Nosso objetivo é, com exemplos do dia a dia, entender quais recursos do Excel podem nos ajudar.

Dessa forma, alinharemos as duas coisas: olharemos para o cotidiano e trabalharemos com isso, mas destacando recursos interessantes do Excel, como o Power Query, por exemplo.

Talvez você ainda não o utilize, ou tenha começado a usá-lo, mas sem conhecer os detalhes.

Sendo assim, iremos explorar alguns recursos do Excel.

Além disso, temos um segundo combinado: a inteligência artificial estará presente em todo o curso. Caso ainda não utilize uma ferramenta, recomendamos começar, pois será muito importante.

Por que bases de dados são tão complexas?

Neste primeiro vídeo, queremos chegar a uma conclusão para o seguinte questionamento:

Por que as bases de dados usadas na área de finanças são tão complexas?

Analisando uma tabela

Começaremos com uma tabela simples, da planilha Dados do Fornecedor.

Dados do Fornecedor:

Embora a tabela acima esteja muito bem organizada, o que encontramos no dia a dia é mais complexo, com muitas colunas e informações que nem sempre compreendemos. Nesse caso, precisamos trabalhar as informações e extrair o máximo delas.

Primeiramente, vamos entender por que as bases costumam ser complexas.

É comum usarmos bases de dados extraídas de um sistema (geralmente, um ERP) e cruzar informações entre si em planilhas. Isso poderia ser mais simples, mas não é.

Por exemplo: na planilha Razao-Despesas, temos um conjunto de colunas com despesas de pessoas fornecedoras, já no formato que viria de um ERP.

Visualização dos cinco primeiros registros da planilha Razao-Despesas. Para visualizá-la na íntegra, acesse o arquivo extracao-despesas-ERP-09-24.xlsx na sua máquina.

Se você trabalha com finanças, talvez manipule planilhas em que outras pessoas já trabalharam. Caso esteja mais próximo da área de tecnologia, pode ser que você faça a extração da base de dados, ou até mesmo quem faz o SELECT na base, conectando-se ao banco de dados.

Porém, para nivelar nosso conhecimento, vamos conhecer esses passos de maneira breve.

Utilizando o Microsoft Copilot

A partir de agora, usaremos o Copilot da Microsoft, uma inteligência artificial acoplada ao motor do ChatGPT. Embora não sejam a mesma ferramenta, o Copilot é mais adequado para o Excel e para o ambiente Microsoft. Poderíamos usar o Google Gemini, mas o importante é ter o apoio da IA.

Começamos pedindo ao Copilot que listasse as bases de dados envolvidas em um registro de despesa no software SAP, um ERP.

Liste para mim as principais bases de dados envolvidas no registro de uma despesa no software de ERP SAP.

Como retorno, temos a seguinte mensagem:

No SAP ERP, o registro de uma despesa envolve várias tabelas de dados principais. Aqui estão algumas das mais importantes:

1. Tabela BKPF (Document Header): Armazena os cabeçalhos dos documentos financeiros.
2. Tabela BSEG (Document Segment): Contém os itens de linha dos documentos financeiros.
3. Tabela EKPO (Purchasing Document Item): Guarda os itens dos documentos de compra.
4. Tabela EKBE (History per Purchasing Document): Registra o histórico de movimentações dos documentos de compra.
5. Tabela COEP (CO Object: Line Items): Armazena os itens de linha dos objetos de controle.
6. Tabela COSS (Cost Totals for Internal Orders): Contém os totais de custos para ordens internas.
7. Tabela FAGLFLEXA (General Ledger: Actual Line Items): Guarda os itens de linha do ledger geral.

Com isso, entendemos a dificuldade de extrair informações simples: há muitas tabelas envolvidas. Conforme dito anteriormente, cada tabela pode ter várias colunas, mas algumas podem ter apenas duas: "Código" e "Descrição". Nesse caso, precisamos juntar várias tabelas para obter informações.

Para trabalhar com isso, alguém da área de tecnologia precisa fazer o que chamamos de query SQL.

Realizando uma query

O SQL é uma linguagem de manipulação de banco de dados, e um dos comandos dessa linguagem é o SELECT, que é um comando de consulta. Com ele, especificamos quais colunas queremos (bloco SELECT) e de quais tabelas (bloco FROM), para então juntar as informações (INNER JOIN).

Observe abaixo a consulta gerada pelo Copilot a partir do seguinte prompt:

Faça de conta que eu sou da área de tecnologia e preciso fazer uma query com SELECT nas bases de dados para trazer dados de despesas do SAP. Liste o comando final.

Retorno do comando:

SELECT 
    BKPF.BUKRS AS Empresa,
    BKPF.BELNR AS NumeroDocumento,
    BKPF.GJAHR AS AnoFiscal,
    BKPF.BUDAT AS DataDocumento,
    BSEG.BSCHL AS TipoConta,
    BSEG.HKONT AS ContaContabil,
    BSEG.WRBTR AS Valor,
    EKPO.EBELN AS NumeroPedido,
    EKPO.EBELP AS ItemPedido,
    EKPO.MENGE AS Quantidade,
    EKPO.NETPR AS PrecoUnitario
FROM
    BKPF
INNER JOIN
    BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
LEFT JOIN
    EKPO ON BSEG.EBELN = EKPO.EBELN AND BSEG.EBELP = EKPO.EBELP
WHERE
    BKPF.BUKRS = '0001' -- Substitua pelo código da empresa desejada
    AND BKPF.BUDAT BETWEEN '2023-01-01' AND '2023-12-31' -- Substitua pelo período desejado
    AND BSEG.BSCHL IN ('40', '50') -- Substitua pelos tipos de conta desejados
ORDER BY
    BKPF.BUDAT DESC;

Neste curso, não vamos nos aprofundar em SQL, mas é importante conhecer. Há outros conteúdos na plataforma que abordam SQL, caso precise ou tenha interesse em se aprofundar.

Uma vez feito o SELECT, obtemos um resultado, que é uma extração da base de dados, podendo ser salva em um arquivo .csv, por exemplo. É provável que você já tenha se deparado com isso.

A pessoa mais próxima de TI faz ou pede SELECT, resultando em um conjunto de dados que pode ser um arquivo texto do tipo CSV. Como exemplo, temos um arquivo .csv extraído para este trabalho, chamado extracao-despesas-ERP-09-24.csv, semelhante a uma planilha do Excel.

Lembre-se! Uma planilha é representada por uma aba na parte inferior do Excel. Provavelmente, você trabalha com pastas de trabalho, que contêm várias planilhas.

A extração da planilha pode ser feita com "Ctrl + C" e "Ctrl + V", ou criando um arquivo .csv onde adicionamos uma nova tabela. Podemos trabalhar de várias formas, no entanto, abrir um arquivo .csv diretamente no Excel não é a melhor prática possível.

Conclusão

No próximo vídeo, vamos explicar como manipular uma base de dados extraída do ERP da melhor forma possível, evitando práticas como abrir um arquivo .csv diretamente no Excel!

Descomplicando bases de dados com Power Query - Conhecendo o Power Query

Recebemos um arquivo .csv chamado extracao-despesas-ERP-08-24.csv, que é uma extração de uma base de dados, e precisamos manipular esse arquivo. Nesse caso, o que pode ser feito?

Conhecendo o Power Query

Provavelmente, você sabe que, quando temos o ícone de um papel com um "X" à esquerda, significa que o Windows já associou este arquivo a um tipo de aplicativo específico.

Para abrir o arquivo .csv, acessamos o Excel diretamente? Na verdade, isso pode ser um erro.

Acessando um arquivo CSV

Ao clicar duas vezes sobre o arquivo CSV, ele será aberto no Excel. Podemos confundir os arquivos .csv com planilhas, mas eles não são a mesma coisa. Vamos entender qual é a diferença?

Se quisermos manter o arquivo no formato .csv, conseguimos manter apenas uma planilha. Algumas pessoas criam várias pastas de trabalho, que são conjuntos de planilhas, para então manipular o .csv, mas essa não é uma boa prática, pois estamos manipulando a base.

Nós extraímos a base do ERP, justamente porque aquelas são as informações verdadeiras, as quais não deveriam ser manipuladas.

Entendendo a diferença entre CSV e XLSX

Antes de entendermos como proceder, vamos explicar a diferença entre um arquivo CSV (.csv) e uma planilha (.xlsx). Nesse caso, temos os seguintes arquivos:

• extracao-despesas-ERP-08-24.csv;
• extracao-despesas-ERP-08-24.xlsx.

Clicaremos com o botão direito sobre o arquivo .csv. Em vez de abrir com Excel, vamos em "Abrir com > Bloco de notas". Como é um arquivo texto, temos apenas os dados obtidos na extração, separados por ponto e vírgula. No Brasil, normalmente, usamos CSVs com ponto e vírgula.

CSV significa Comma Separated Values, ou seja, Valores Separados por Vírgula.

Empresa;Número do Documento;Ano Fiscal;Data do Documento;Tipo de Documento;Número do Item;Tipo de Conta;Conta Contábil;Valor em Moeda Local;Valor em Moeda Estrangeira;Código do Fornecedor;Nome do Fornecedor;Cidade do Fornecedor;País do Fornecedor;Grupo de Contas do Fornecedor
3000;123456;2024;22/09/2024;PF;1;F;400000;1000.00;1000.00;2000;Fornecedor A;São Paulo;BR;1
3000;123456;2024;22/09/2024;PF;2;F;400000;2000.00;2000.00;2000;Fornecedor A;São Paulo;BR;1
1000;123457;2024;22/09/2024;PF;3;F;400000;1500.00;1500.00;2001;Fornecedor B;Rio;BR;1
1000;123458;2024;22/09/2024;PF;1;F;400000;2500.00;2500.00;2002;Fornecedor C;Brasília;BR;2
1000;123459;2024;22/09/2024;PF;1;F;400000;3000.00;3000.00;2003;Fornecedor D;Salvador;BR;3

(Informações omitidas)

Dessa forma, preservamos as informações, evitamos sujeiras, e não corremos o risco de uma leitura com informações truncadas ou com problemas. No entanto, ainda podem acontecer algumas falhas.

Por exemplo: quando geramos um arquivo texto com caracteres especiais, às vezes, temos problemas. Mais adiante no curso, vamos entender melhor como manipular essas coisas.

O arquivo CSV é mais limpo e mais leve, então conseguimos usá-lo com maior facilidade.

Agora, vamos abrir o arquivo .xlsx, que é a planilha. Da mesma forma, clicaremos com o botão direito e selecionaremos "Abrir com > Bloco de notas".

Nesse caso, recebemos um texto estranho, dando a impressão de estar em outro idioma, pois o programa não conseguiu entender os caracteres que estão na planilha.

Na verdade, os caracteres estão comprimidos, ou seja, não funcionam como um arquivo texto. Portanto, temos um arquivo muito mais complexo, que não conseguimos abrir no bloco de notas.

Perceba que temos uma grande diferença de complexidade entre os arquivos analisados. Por isso, é mais comum usarmos o arquivo .csv como extração de dados.

Como abrir corretamente o arquivo CSV?

Fica a seguinte questão: como abrir corretamente o arquivo CSV? Para isso, trabalharemos de uma forma diferente. Começaremos abrindo um Excel vazio, e a partir dele, usaremos o Power Query.

O Power Query é uma funcionalidade do Excel, que depois virou uma funcionalidade à parte e deu origem ao Power BI. Hoje, quem usa Power BI ou Excel, muito provavelmente, usa Power Query.

No Power BI, o Power Query é fundamental e quase sempre começamos com ele.

Para acessar o Power Query, vamos até "Dados > Obter Dados". Com isso, já estamos no Power Query. Poderíamos, por exemplo, apenas selecionar a opção "Iniciar Editor do Power Query…".

Conforme dito anteriormente, query é uma consulta. Portanto, Power Query é uma consulta poderosa que trará os dados para nós.

Nesse caso, em vez de abrirmos o arquivo .csv, traremos os dados deste arquivo. Para isso, em "Obter Dados", selecionamos a opção "De Arquivo > De Text/CSV".

Com isso, acessamos uma funcionalidade que abrirá o arquivo texto e extrairá os registros. Obviamente, precisamos localizar o arquivo, então é importante manter os arquivos organizados.

Uma vez aberto o arquivo com o Power Query, ele irá se conectar ao arquivo e trazer o conteúdo. Ao final, clicamos em "Carregar" no canto inferior direito.

Dessa forma, será criada uma consulta chamada extracao-despesas-ERP-09-24.

Qual a diferença entre uma consulta e um CSV?

Nas consultas, em uma mesma pasta de trabalho, conseguimos manipular outras planilhas sem danificar o CSV, isto é, sem mexer nos dados diretamente.

Imagine, por exemplo, que percebemos que o valor 3000 nas duas primeiras linhas da coluna "Empresa" estão errados e queremos alterar para 1000, que é o valor correto. Nesse caso, não adianta alterar diretamente nas células, pois não abrimos o arquivo, mas sim carregamos os dados.

Ao criar uma consulta, teremos acesso a uma aba chamada "Consulta" à direita do menu superior. A partir dela, identificamos que a consulta também uma tabela.

Observação: caso você ainda não tenha o hábito de trabalhar com tabelas, falaremos um pouco sobre isso durante a exploração com os arquivos e com o Power Query.

Na aba "Consulta", conseguimos atualizar a consulta clicando em "Carregar > Atualizar". Ao fazer isso, o Power Query se conecta novamente ao arquivo, carrega os dados, e sobrepõe a informação.

Essa é a vantagem das consultas: ao mexer nelas, não manipulamos a base de dados. Nesse caso, apenas usamos os dados presentes na planilha, enquanto a base permanece preservada.

Agora, suponha que precisamos acessar o arquivo CSV para editar ou jogar um arquivo por cima. Com ele aberto no bloco de notas, faremos a substituição de 3000 por 1000 diretamente na origem.

Empresa;Número do Documento;Ano Fiscal;Data do Documento;Tipo de Documento;Número do Item;Tipo de Conta;Conta Contábil;Valor em Moeda Local;Valor em Moeda Estrangeira;Código do Fornecedor;Nome do Fornecedor;Cidade do Fornecedor;País do Fornecedor;Grupo de Contas do Fornecedor
1000;123456;2024;22/09/2024;PF;1;F;400000;1000.00;1000.00;2000;Fornecedor A;São Paulo;BR;1
1000;123456;2024;22/09/2024;PF;2;F;400000;2000.00;2000.00;2000;Fornecedor A;São Paulo;BR;1

(Informações omitidas)

Importante! Se o arquivo CSV é uma extração de base de dados, não deveria ser necessário editar a a origem. No entanto, às vezes pode acontecer.

Após salvar, vamos retornar à consulta e atualizar novamente. Assim como antes, o Power Query irá buscar as atualizações no arquivo.

Conclusão

Isso abrirá possibilidades para várias coisas que podemos fazer e que serão mais fáceis com este recurso. No próximo vídeo, falaremos mais sobre quais são as vantagens de usar o Power Query nas consultas, em vez de fazer "Ctrl + C" e "Ctrl + V" ou abrir o arquivo texto!

Sobre o curso Análise financeira eficiente: automatize o Excel com Power Query e Power Pivot

O curso Análise financeira eficiente: automatize o Excel com Power Query e Power Pivot possui 143 minutos de vídeos, em um total de 46 atividades. Gostou? Conheça nossos outros cursos de Excel em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Descomplicando bases de dados com Power Query
• Tratando as bases de dados no Power Query
• Analisando os dados com Power Query
• Acrescentando novos dados no Power Query
• Otimizando processos de consultas e arquivos