Guru do Excel e Power BI

Bom dia pessoal!

Assim como nos outros tópicos que criei anteriormente, meu sistema contábil gera relatórios em tamanho A4 para impressão, exporta para Excel (.csv) e eles na maioria das vez vêm com quebras de linha, linhas em branco, cabeçalhos das páginas e tudo isso tem que ser removido para que eu possa enviar ao meu cliente.

Vamos lá... dessa vez é um "Acompanhamento Mensal" com o resultado de várias unidades de uma empresa. Nessa caso são oito (02; 03; 04; 05; 06; 07; 09; 10).

Separei o arquivo Excel em várias planilhas separando cada passo a passo e aqui no tópico vou explicar o que eu faço para que fique mais fácil de entender.

Planilha "Original": É como o relatório sai do sistema... e o primeiro passo é remover as colunas brancas que estão entre as colunas B e X (destacado de vermelho).
** Importante: Esse relatório compreende 12 meses, de janeiro a dezembro, mas a quantidade de meses no relatório pode variar.

Planilha "Passo 1": Após removidas as colunas que estavam em branco, terei de separar as informações de cada unidade.
Cada relatório inicia com a linha "2648 RESULTADO LIQUIDO APOS IRPJ / CSLL". <- Essa será sempre a primeira linha da cada relatório, e eu destaquei a célula de verde para facilitar a visualização.
> Aqui entra o primeiro "problema", pois para identificar as unidades operacionais, seja "02 - Correia Pinto", "03 - Matriz" ou qualquer outra, essa informação vem na próxima quebra de página...
Por exemplo, o primeiro está na célula A14, e o centro de custo correspondente está na quebra de página, na célula A52.
Sempre o primeiro relatório já vem com Centro de Custo correto, mas os próximos sempre vem errado...

Nesse caso o que eu preciso é que seja inserido na linha superior de cada "2648 RESULTADO LIQUIDO APOS IRPJ / CSLL" o "Centro de Custo" que está na quebra de página abaixo. Ou seja, é só localizar o próximo Centro de Custo (que vem abaixo, não acima) e colar na linha superior do "2648 RESULTADO LIQUIDO APOS IRPJ / CSLL"

Eu não o porquê, mas o sistema traz o "Centro de Custo" no meio de cada relatório...
Eu coloquei Setas vermelhas na planilha mostrando a forma que tem que ser alterada...

E também remover as linhas em branco e essas que tem o "Centro de Custo" desnecessários (estão pintadas de um tom de laranja)

Planilha "Passo 2": Nessa planilha eu exemplifico como ficou organizado após transferir as linhas com os "Centros de Custo" acima de cada "2648 RESULTADO LIQUIDO APOS IRPJ / CSLL".

Planilha "Passo 3": Para que eu possa formatar essa planilha manualmente, eu insiro uma coluna de "total", onde ele soma cada linha, e filtro pelos que estão "0,00"... Mas Atenção! Existem linhas que possuem valores positivos e negativos que no "total" vão aparecer zeradas, e daí não podem ser removidas...
O Excel deve possuir um método que veja as linhas que possuam somente valores "0,00", e essas linhas devem ser removidas, como mostro nos passos 4, 5 e 6

Planilha "Passo 7": É o resultado final... como ficou o relatório após realocar os "Centros de Custo", remover as linhas e colunas sobressalentes e remover as linhas que possuam somente valores zerados.

Bônus: Seria muito bom e prático se também tivesse um jeito de fazer com que o Excel já separasse em planilhas o relatório de cada Centro de Custo...

Por Exemplo... 02 - Correia Pinto na Plan2; 03 - Matriz na Plan3 e assim por diante...


Eu sei que pela imensidão do texto parece coisa pra caramba, mas vejam pelo meu desespero em digitar tudo isso e ainda esperara a ajuda de alguém rsrs... perco muito tempo fazendo a formatação disso.

Conto com a ajuda de vocês... Muito obrigado!!

Luiz,
Quanto a aba "Passo 1", percebi que o seu sistema que traz essa informacao do Centro de Custo, como um cabeçalho no momento da conversao SISTEMA -> arquivo csv, ou seja, deverá "ajeitar" manualmente.
Tenho um sistema aqui que tambem importo o csv e relatorios com + de uma folha, ele mostra o cabeçalho novamente.

ragealgu escreveu:Luiz,
Quanto a aba "Passo 1", percebi que o seu sistema que traz essa informacao do Centro de Custo, como um cabeçalho no momento da conversao SISTEMA -> arquivo csv, ou seja, deverá "ajeitar" manualmente.
Tenho um sistema aqui que tambem importo o csv e relatorios com + de uma folha, ele mostra o cabeçalho novamente.

Boa tarde regealgu!

Na verdade acho que isso não implica em muita coisa, pois nos outros relatórios eu apenas exporto em ".csv" e executo as macros normalmente. Se esse for o problema, também posso salvar como ".xlsx"...

Ninguém?

Olá luizhalmeida!

Uma alternativa é usar um dos recursos de Power BI, o Power Query! É uma recurso para transformação de grandes quantidades de dados, a fim de manter a atualização constante ao alterar os dados no original.

Acontece que é preciso testar! Não sei quanto ao teu prazo, mas posso avaliar e te propor. Muito provavelmente meio dia eu tenho como auxiliar.

Caso ninguém tenha solucionado até lá! Tomara que surja uma opção logo!

Voltaremos!

Olá luizhalmeida!

Eu desenvolvi essa ferramenta a partir do recurso Power Query e Power Pivot, ambas do conjunto Power BI.

Ela não é uma solução simples, porém, possibilita que você mantenha uma rotina padrão, ou seja, sempre que precisar você irá poder extrair o documento no mesmo formato que apresentou no início do post, lançar em uma pasta padrão, e, o relatório será gerado automaticamente.

Caso sirva, acredito que seja preciso mantermos contato para que possa auxilia-lo no que diz respeito aos aspectos básicos do uso desses recursos, pois estão sendo aplicado linguagem e métodos novos.

O teste para vc deve prosseguir da seguinte maneira:

1-Faça o download do zip em anexo;
2-Extraia em no local desejado;
3-Instale o Power Query aqui (verifique se vc está usando Office 32 ou 64: https://www.microsoft.com/pt-br/downloa ... x?id=39379
4-Abra a pasta de trabalho "Relatórios_Gerenciais_Centro_Custo_Plano_Contabil_proposta";
5-Veja a planilha "CentrodeCustoeCabeçalhos", ela é automática, não exclua essa planilha. No caso do seu centro de custo mudar, ela irá pegar qualquer mudança e tornar disponível no relatório;
6-Veja a planilha "Relatório" e vc irá perceber que todos os centros de custos estão listados;
7-Selecione o centro de custo desejado no controle superior, caso queira escolher mais de um, use Ctrl + mouse e vá selecionando;
8-Na eventualidade de querer manter uma planilha para cada centro de custo na mesma pasta de trabalho, me diga, faço o ajuste.

Como ele pode ser útil?

No momento em que você precisar atualizar os valores, extraia do seu sistema, no mesmo formato apresentado aqui, renomeie para "Matriz_CentrodeCusto_PlanoContábil_Todos", e substitua o arquivo dentro da pasta. Abra o relatório e estará pronto!! Toda a estrutura foi criada dentro da pasta de trabalho "Relatório", portanto, não exclua!

É possível que tenhas alguns contratempos, pois eu não conheço sua rotina diária e como os dados são extraídos. Vamos avaliando juntos cada intercorrência! Mantenha contato.

Opa!

Um passo que faltou, no caso de não conseguir atualizar a pasta relatório:

Office 2013
1-Vá em Arquivo, Opções, SUlpementos > Suplementos COM > Ir, habilite Microsoft PowerQuery, Esc para retornar
2-Vá na planilha CentrodeCustoeCabeçalhos, clique em qualquer célula da tabela para abrir o painel à direita. Com exceção de Power Trim, ambas as consultas estarão com um sinal de atenção.

3-Caso não abra nenhuma painel, vá em Power Query, Mostrar Painel de Consulta.

4-Escolha uma consulta, clique com o botão direito em cima e depois em Editar. Irá abrir o painel do Power Query
5-Ao lado direito haverá uma lista, vá em "Source" ou "Fonte" e clique na "Engrenagem", altere o caminho/local, para onde vc salvou a pasta de trabalho, no meu caso C:\Users\Felipe\Desktop\Luiz H Almeida\Matriz_CentrodeCusto_PlanoContábil_Todos.xlsx
6- Faça isso nas demais consultas, caso elas estejam com o sinal de atenção. Pronto! Instalado na sua máquina!

Olá Vaz!

Tudo bem?

Demorei para ver essas respostas pois estava desacreditado que alguém daria uma resposta aqui, rsrs.
Pois então, fiz o download do arquivo anexo, instalei o Power Query e vou começar a mexer para ver o que encontro. Sou um usuário bem iniciante, mas qualquer dúvida ou problema que encontrar voltarei a comentá-los aqui.

Desde já o meu Muito Obrigado!

Olá Vaz!

Segue anexo com o erro encontrado... creio que seja porque eu tirei um relatório somente com o mês de janeiro/2016, e por isso o sistema detectou a falta de colunas (que representaria os meses de fevereiro a dezembro...). Gostaria de saber a possibilidade de usar esse sistema para relatórios que não envolvam 12 meses...
E outra coisa... (não tão importante) Pelo menos aqui no meu PC o scroll do mouse não funcionou para rolar pela planilha... isso se deve a alguma configuração?

Muito obrigado!

Dae luizhalmeida!

Eu tbm notei essa do scroll não funcionar! Não sei por que, mas já aconteceu outras vezes. Tente abrir e fechar novamente a pasta de trabalho.

Agora, qt aos meses, preciso que me diga quais são as visualizações disponíveis no momento em que vc extrai o relatório "original" do seu sistema.

É possível extrair um mês só?
Outras visualizações?

Como a linguagem M, utilizada pelo Power Query, é relativamente nova, não tem tanto conteúdo prático, e, eu ainda não tenho completo domínio dela. Portanto, não sei o tempo que pode demorar para conseguir lidar com o número variável de colunas nos meses. Enquanto isso estou analisando o código para adaptar para o número de coluna variável.

Se houver apenas a visualização por mês, poderias extrair cada mês, colocar em uma pasta, zipar e upar aqui? Assim, posso trabalhar no relatório final tendo como rotina extrair o arquivo "original" por mês e colocar em uma pasta para que o Power Query identifique (depois te explico melhor). Sei que, ao invés de fazer uma extração terás que fazer 12, mas acredito que é o mais certo agora até eu descobrir o código correto.

Bom dia Vaz!

Obrigado pela atenção nesse assunto!

Sim, há a possibilidade de extrair uma quantidade variada de meses... como estamos em janeiro vou extrair somente janeiro. Em fevereiro o relatório vai ter Janeiro e Fevereiro. E assim por diante... até que em Dezembro/2016 o relatório vai ter de janeiro até dezembro.

Posso até tirar uma relatório com cada mês separado, mas é o mesmo que pegar o relatório que você já tem e excluir as colunas dos meses que não vai ser usado... ele não muda em praticamente nada...

Vou ver se no início da tarde gero os relatórios pra você, pois além de gerar tenho que abrir ele e converter de .csv para .xlsx...

Forte Abraço!

Capaz! Estou aprendendo muito com tua situação.

A rotina seria, por exemplo, mesmo que estivéssemos no mês de Março, vc tiraria apenas o mês de Março, não mais de Janeiro até Março, e, colocaria em uma pasta. Ao final do ano, teríamos 12 pastas de trabalho.

Preciso que extraia mês a mês, mesmo sabendo que é só tirar algumas linhas, para saber como os dados vem formatados no documento, para compreender o padrão e formatar o relatório.

Faça o seguinte também, envia em *.csv e xlsx todos os meses, por que posso importar ele como csv para dentro do Power Query. Vou tentar das duas maneiras com o intuito de agilizar seu trabalho. Talvez eu consiga fazer com que vc sempre extraia em *.csv, coloque na pasta e o relatório fique formatado automaticamente.

Opa, olá Felipe!

Eu entendo sua forma de pensar em mensalmente gerar o relatório desse mês, mas tenho quase certeza que isso não é viável por um simples problema:

As linhas (ou contas Contábeis) possuem uma sequência...
Digamos que em Janeiro/fevereiro/março não houve valor nenhum na conta B, que fica entre a conta A e a C. Como o valor nessa conta é "0,00", ele não vai sair no relatório nos meses de Janeiro/fevereiro/março (da conta A vai direto para a B). Porém, em Abril essa conta foi usada e apareceu um valor qualquer... então em Janeiro/fevereiro/março essa conta não apareceu e finalmente em abril ela vai aparecer no relatório... como fica? Ela vai aparecer no local correto (entre A e C)?

Tendo em vista o exposto acima é que aqui no escritório é sempre gerado o relatório com os meses que queremos...

De qualquer forma está anexo uma pasta zipada com os meses.

Abraços!

Luiz!

Você tem como baixar uma lista de contas contábeis e a respectiva sequência? É um padrão estipulado pela sua empresa ou uma norma do mercado?

Vaz,
É uma ordem estipulada pela empresa e que ao mesmo tempo segue as normas contábeis, mas que ao longo do ano pode sofrer "N" alterações.
Outra coisa, não será apenas eu quem vai operar esse tipo de relatório, pois eu cuido de apenas algumas empresas, então os funcionários não terão conhecimento suficiente para ficar manipulando, editando, resolvendo problemas... rsrs.

Por isso que o ideal é falar: "Você gera o relatório, cola nessa pasta e renomeia dessa forma". Pronto, tudo resolvido.

Se você achar que possa ser a solução, podemos fazer com que eu SEMPRE gere o relatório com os últimos 12 meses, Exemplo: fev/2015...jan/2016; mar/2015... fev/2016.

Você acha que isso é melhor? Pois de qualquer forma vou ter que pegar o relatório que foi "convertido" e colar em outro planilha padrão do cliente. O cliente mesmo não vai ter acesso à essa planilha TOP que você me forneceu...

Abraços!

Olá Luiz,

Sim! A melhor solução enquanto não há um código melhor é extrair SEMPRE os 12 meses. Pois, caso queira uma visualização de alguns meses é possível filtrar aqueles desejados.

Quanto às outras opções, elas são mais demoradas. Eu vou continuar com seu caso para tentar aprimorar o código, pois é um desafio. Caso sinta-se satisfeito com essa solução, Voilà! Aproveite! Mantemos contato, fique a vontade!

Uma coisa para facilitar vc a filtrar os meses é Selecionar uma célula no meio do relatório>Ao lado direito em cima em "Analisar">Inserir segmentação de dados>Escolha Mês/Ano>Posicione no local desejado. Clique para filtrar, e, para filtrar mais de um mês, selecione o primeiro e após mantenha pressionado o Ctrl para ir selecionando os demais!

Felipe, boa tarde!

Desculpe a demora para dar as caras por aqui, mas entrei no meu período de férias e literalmente me desliguei de tudo, rsrs.

Estou usando duas macros desenvolvidas pelo pessoal aqui do fórum e vou tentar me habituar com o uso dessa também.
Novamente, MUITO OBRIGADO por todo o empenho no desenvolvimento dessa macro!