Digamos que você esteja executando vários projetos com orçamentos diferentes e queira visualizar seus custos para cada um deles. Ou seja, a partir desta tabela de origem:
.. obter algo assim:
Em outras palavras, você precisa distribuir o orçamento ao longo dos dias de cada projeto e obter uma versão simplificada do gráfico de Gantt do projeto. Fazer isso com as mãos é longo e chato, macros são difíceis, mas o Power Query para Excel em tal situação mostra seu poder em toda a sua glória.
Consulta de energia é um complemento da Microsoft que pode importar dados para o Excel de quase qualquer fonte e depois transformá-los de várias maneiras diferentes. No Excel 2016, esse suplemento já vem integrado por padrão e, para o Excel 2010-2013, pode ser baixado do site da Microsoft e instalado no seu PC.
Primeiro, vamos transformar nossa tabela original em uma tabela “inteligente” escolhendo o comando Formatar como uma tabela aba Início (Início — Formatar como Tabela) ou pressionando o atalho de teclado Ctrl+T :
Então vá para a aba Data (se você tiver o Excel 2016) ou na guia Consulta de energia (se você tiver o Excel 2010-2013 e instalou o Power Query como um suplemento separado) e clique no botão Da Tabela/Intervalo. :
Nossa tabela inteligente é carregada no editor de consultas do Power Query, onde a primeira etapa é configurar os formatos de número para cada coluna usando as listas suspensas no cabeçalho da tabela:
Para calcular o orçamento por dia, você precisa calcular a duração de cada projeto. Para fazer isso, selecione (mantenha pressionada a tecla Ctrl) coluna primeiro Acabamento, e depois Início e escolha uma equipe Adicionar coluna – Data – Subtrair dias (Adicionar coluna - data - subtrair dias):
Os números resultantes são 1 a menos do que o necessário, porque devemos iniciar cada projeto no primeiro dia da manhã e terminar no último dia à noite. Portanto, selecione a coluna resultante e adicione uma unidade a ela usando o comando Transformar – Padrão – Adicionar (Transformar — Padrão — Adicionar):
Agora vamos adicionar uma coluna onde calculamos o orçamento por dia. Para isso, na aba Adicionar coluna eu não jogo Coluna personalizada (Coluna personalizada) e na janela que aparece, digite o nome do novo campo e a fórmula de cálculo, utilizando os nomes das colunas da lista:
Agora o momento mais sutil – criamos outra coluna calculada com uma lista de datas do início ao fim com um passo de 1 dia. Para fazer isso, pressione novamente o botão Coluna personalizada (Coluna personalizada) e use a linguagem interna do Power Query M, que é chamada Lista.Datas:
Esta função tem três argumentos:
- data de início – no nosso caso, é retirada da coluna Início
- o número de datas a serem geradas – no nosso caso, esse é o número de dias para cada projeto, que contamos anteriormente na coluna Subtração
- passo de tempo - definido por design #duração(1,0,0,0), significando na linguagem de M – um dia, zero horas, zero minutos, zero segundos.
Depois de clicar em OK obtemos uma lista (List) de datas, que pode ser expandida em novas linhas usando o botão no cabeçalho da tabela:
… e obtemos:
Agora tudo o que resta é recolher a tabela, usando as datas geradas como nomes para as novas colunas. A equipe é responsável por isso. Coluna de detalhes (Coluna Dinâmica) aba Converter (Transformar):
Depois de clicar em OK obtemos um resultado muito próximo do desejado:
Nulo é, neste caso, um análogo de uma célula vazia no Excel.
Resta remover colunas desnecessárias e descarregar a tabela resultante ao lado dos dados originais com o comando Fechar e carregar – Fechar e carregar… (Fechar e carregar — Fechar e carregar para…) aba Início (Home):
Obtemos como resultado:
Para maior beleza, você pode personalizar a aparência das tabelas inteligentes resultantes na guia Construtor (Desenhar): definir um estilo de cor única, desabilitar botões de filtro, habilitar totais, etc. Além disso, você pode selecionar uma tabela com datas e habilitar o realce de número para ela usando a formatação condicional na guia Home — Formatação Condicional — Escalas de Cores (Início — Formatação Condicional — Escalas de Cores):
E a melhor parte é que no futuro você pode editar com segurança os antigos ou adicionar novos projetos à tabela original e, em seguida, atualizar a tabela certa com as datas com o botão direito do mouse – e o Power Query repetirá todas as ações que fizemos automaticamente .
Voilà!
- Gráfico de Gantt no Excel usando formatação condicional
- Calendário de marcos do projeto
- Gerando linhas duplicadas com o Power Query