Como fazer Dashboard de projetos em Excel

Olá, pessoal! Hoje aprenderemos a criar um dashboard para gerenciamento de projetos. Esse Dashboard de projetos em Excel proporcionará, sobretudo, uma visão geral de múltiplos projetos, permitindo filtrar por projeto e gerente, além de mostrar uma visão geral das tarefas em andamento e do orçamento. Também é possível visualizar um gráfico de Gantt para controlar as tarefas.

Mãos à obra!

Na nossa planilha, temos os dados do projeto, como nome, tarefa, gerente, data de início, duração, término, dias completados para cada tarefa, progresso, orçamento e valor real. Você pode exportar esses dados de um software de gestão de projetos, como o MS Project, ou inseri-los diretamente na planilha. Com base nessa tabela, criaremos mais 2 guias: uma chamada “Dashboard” e outra chamada “Cálculo”, que servirão de apoio para o nosso dashboard.

No “Dashboard”, formataremos as 3 primeiras linhas, colorindo-as e nomeando-as “Dashboard de Gerenciamento de Projetos”. Em seguida, na guia “Dados”, selecionaremos toda a tabela usando o atalho “Ctrl + *”. Então, na guia “Inserir”, transformaremos essa seleção em uma tabela dinâmica. Escolheremos a opção “Planilha Existente” e alocaremos a tabela dinâmica na guia “Dashboard”, aproximadamente na linha 9. Ao pressionar “Enter” e confirmar com “Ok”, criaremos a tabela dinâmica.

Dentro da tabela dinâmica, adicionaremos os campos de projeto, tarefa, gerente, data de início e data de término da tarefa. De fato, ao adicionar algumas datas, notaremos a aparição da opção de agrupamento por mês, que podemos desmarcar. O campo “Progresso”, por ser um número, será enviado para o campo de valores. No entanto, podemos selecioná-lo e arrastá-lo para a linha. Os campos “Real” e “Orçado” também seguirão para a coluna de valores.

Para uma melhor apresentação visual, podemos acessar a opção “Design” e escolher o formato de tabela. Em seguida, podemos remover os subtotais que são acumulados em cada linha. Dessa forma, as informações se tornarão mais claras e compreensíveis. As datas ficarão agrupadas, mas basta clicar com o botão direito do mouse e selecionar “Desagrupar” tanto na coluna de início quanto na de término. Caso desejemos, também podemos remover os botões da tabela dinâmica acessando o menu “Analisar” e desmarcando a opção correspondente.

Criando os elementos do Dashboard de projetos

Passaremos à criação do primeiro elemento do Dashboard de projetos em Excel, que será um gráfico de rosca. Esse gráfico mostrará a quantidade de dias pendentes e concluídos para cada projeto. Para fazê-lo, voltaremos à guia “Dados”, selecionaremos a tabela dinâmica e, na guia “Inserir”, clicaremos em “Tabela Dinâmica” e em “Planilha Existente”. A tabela será alocada na guia “Cálculo”.

Nessa tabela, consideraremos os dias completos e os dias de duração, ajustando a coluna para se tornar uma linha. À frente, criaremos as categorias “dias completos” e “dias pendentes”. Para calcular a porcentagem de dias completos, podemos dividir o número de dias concluídos por 75 (total de dias). Isso resultará em 53% de dias completos. A diferença será obtida subtraindo 53% de 100%, o que nos dará 47% de dias pendentes. Com essa informação, acima de tudo, poderemos inserir um gráfico de rosca, movendo-o para uma posição mais adequada e removendo o título através da opção de legenda.

Acabou? Ainda não!

Vamos em “Formatação de série”, seja através do botão direito do mouse ou com o atalho “Ctrl + 1”, para alterarmos a dimensão da rosca. Selecione-a e recorte-a com “Ctrl + X”, em seguida, vá para o Dashboard. Os elementos gráficos serão adicionados após a coluna H. Cole o gráfico na parte superior, configurando-o sem preenchimento, sem linha e com a legenda na cor branca.

Uma outra informação útil é a inserção de um elemento de texto utilizando a opção “Formas”. Com a caixa de texto selecionada, vá para a barra de fórmulas e insira o sinal de igual (=), determinando que o valor seja igual ao valor de dias completos, obtido na guia de Cálculo. Veja como o valor de 53 é trazido, sendo uma informação dinâmica que se altera conforme as mudanças ocorridas. Podemos sobrepor esse valor sobre o gráfico, indicando que 53% das tarefas estão concluídas.

Contagem de tarefas

Nosso segundo elemento do Dashboard de projetos em Excel será a contagem de tarefas não iniciadas, em andamento e tarefas concluídas. Na guia de Cálculo, escreveremos o rótulo “Tarefas” e, logo abaixo, “Não Iniciou”, “Em Andamento” e “Completa”. Utilizaremos a fórmula “CONT.SE” para saber a quantidade de tarefas, sendo a condicional o intervalo da porcentagem de progresso no Dashboard. Dessa forma, o que estiver com 0% indica que não teve início. Selecione esse intervalo, insira um ponto e vírgula (;), digite o sinal de igual (=) entre aspas duplas e escreva “&” para concatenar com 0 (zero). Feche os parênteses e pressione “Enter”. O resultado será 21 tarefas que não iniciaram.

E como veremos o progresso das tarefas?

Para saber quantas tarefas estão “Em Andamento”, ou seja, tarefas que têm mais de 0% e menos de 100%, utilizaremos a função “CONT.SES”, já que é necessário mais de uma condicional. O intervalo será o mesmo do passo anterior. Digite o sinal “maior” (>) entre aspas duplas e concatene com zero. Insira ponto e vírgula, passe o intervalo de Progresso novamente, ponto e vírgula, digite o sinal “menor” (<) que 1 (representando 100%) e concatene com “&”. Finalizando, o Excel apresentará 18 tarefas “Em Andamento”.

Para as tarefas completas, consideraremos as que possuem progresso igual a um. Você pode utilizar a mesma fórmula das tarefas “Em Andamento” e substituir o zero por um, obtendo um total de 14 tarefas completadas.

Gráficos para o nosso Dashboard de projetos

Posteriormente, vamos inserir um gráfico baseado nessas informações. Selecione esse intervalo, clique em “Inserir” e depois em “Gráfico Recomendado”. Escolha o “Gráfico de Barras Empilhadas em Porcentagem”. Remova o eixo e a linha de grade. Coloque a legenda na parte superior e deixe a largura em 0%. Recorte o gráfico dessa área e leve-o para o Dashboard. Adicione os rótulos de dados, formatando a cor e o tamanho da fonte, além de configurar o preenchimento e as linhas como “sem cor”. O título e a legenda podem ficar na cor branca.

Concluindo, com o gráfico do Dashboard de projetos em Excel pronto, vamos nomeá-lo como “Agendamento das Tarefas”. Para deixar o dashboard visualmente agradável, definiremos um padrão de cores. Para isso, vá em “Layout da Página” e selecione a paleta de cores desejada. No nosso caso, escolhemos o verde azulado.

Mais indicadores

Vamos adicionar alguns elementos relacionados ao orçamento do projeto. Novamente, na guia “Dados”, inseriremos uma tabela dinâmica. Escolheremos um local existente na guia “Cálculo” e confirmaremos a criação da tabela, selecionando os valores de Orçamento e Valor Real. Em seguida, mova esses valores da coluna para as linhas. Queremos calcular a porcentagem utilizada do orçamento em cada projeto, para isso, dividimos o valor real pelo valor orçado, chegando ao resultado de 68%. A diferença será obtida através de 1 – 68%, o que resultará em 32% ainda a ser utilizado.

Também criaremos um gráfico de rosca para representar essa informação. Use o atalho “Ctrl + 1” para dimensioná-lo adequadamente, remova a legenda e mantenha apenas o título. Em seguida, recorte o gráfico e leve-o para a área do Dashboard, retirando o preenchimento e as linhas; adicione o nome “Usado do Orçamento” em branco. Para mostrar o valor do percentual, insira mais um elemento de texto sobre o gráfico, selecione-o e, na barra de fórmulas, digite “=Cálculo!68%” (a referência ao valor de 68%), formatando-o sem preenchimento e sem linhas. Assim, teremos o gráfico que demonstra o quanto do orçamento foi utilizado.

Complementando o Dashboard

Para complementar a informação do orçamento do Dashboard de projetos em Excel, vamos mostrar o valor gasto em cada projeto, comparando-os. Na guia “Cálculo”, insira um Gráfico de Barras Deitadas na tabela dinâmica de valores. Deixe-o sem o eixo, sem as linhas de grade e sem a legenda, mantendo apenas o título. Oculte os botões da tabela dinâmica e adicione os rótulos de dados. Utilizando novamente o atalho “Ctrl + 1”, você pode formatar a série com um pequeno espaço. Recorte esse gráfico e cole-o no Dashboard, dimensionando-o e nomeando-o como “Orçado vs Real”.

Segmentação de dados

Agora, selecionaremos a tabela dinâmica e iremos para “Análise” e clicaremos em “Segmentação de Dados“, selecionando tanto “Projeto” quanto “Gerente”. Em seguida, mova-os para a parte superior e adicione algumas colunas. Conecte essa Segmentação de Dados com a tabela principal, clicando com o botão direito do mouse sobre ela e selecionando “Conexão de Relatórios”, conectando-a com as tabelas 5 e 18. Faça o mesmo com a outra segmentação, mas desta vez com os Relatórios. Como resultado, ao selecionar um projeto, como o da Alstom, você verá como o Dashboard traz dinamicamente as tarefas, os indicadores e o valor de orçamento empregado.

O próximo elemento que trabalharemos é o gráfico de Gantt, que pode ser feito através da guia “Desenvolvedor”. Caso essa guia não esteja disponível, você pode habilitá-la acessando “Personalizar” com o botão direito do mouse. Em seguida, clique em “Inserir” e escolha o elemento “Barra de Rolagem”, desenhando-o na horizontal. Depois de posicioná-lo, vá para a opção “Formatar controle”, definindo o valor atual como zero, o mínimo como zero, o máximo como 30 e a alteração em 7 (para ser semanal).

O vínculo da célula será na guia de Cálculo. Selecione algum espaço e pressione “Enter”. Quando você mudar a posição da barra de rolagem em Cálculo, perceberá que o valor começa a se alterar. Vamos chamá-la de barra de rolagem. Atualmente, o valor é 3, mas ao movimentar a barra, o valor é automaticamente atualizado.

Rotulagem das datas do Dashboard de projetos

Agora, precisamos criar os rótulos das datas. A primeira delas será a data mínima do nosso projeto. Para obter essa informação, vá para a aba de Cálculo e escreva “Data mínima”. Utilize a fórmula “MÍNIMO” com o intervalo da coluna “Início”. O valor trazido será 10/08. Na primeira linha, defina que essa informação é igual a 10/8, mas somada com a informação da barra de rolagem. Em formatação (Ctrl + 1), escolha o formato de data 14/3 (somente dia/mês). Conforme movimentamos a barra, as datas se alteram, com base na soma da data atual com 1.

Você pode arrastar essa célula até o tamanho necessário para o gráfico de Gantt. A formatação será igual à das linhas, que você pode aplicar com o “Pincel de Formatação” da guia “Página Inicial”. No entanto, a data precisa ser formatada novamente para o formato 14/3.

Barras para o gráfico de Gantt

Para criar as barras do gráfico de Gantt, você deve selecionar uma área que vai até a última célula do intervalo da tabela dinâmica. Para formatar essas barras, acesse “Formatação Condicional” e depois “Nova Regra”, escolhendo a opção “Usar uma fórmula para determinar quais células devem ser formatadas”. No campo de fórmula, utilize “=E”, o qual requer verificar duas condições na tabela dinâmica. Quando essas condições forem verdadeiras, haverá uma formatação no gráfico de Gantt.

Vamos ver como fica? A primeira verificação é que a data de 14/8 deve ser maior ou igual à data de início dessa linha. A segunda verificação é se a data de 14/8 é menor ou igual à data de término. Ou seja: Verifique se a data de 14/8 está dentro desse período; se sim, a célula será formatada. Para estender essa fórmula para essa região, é importante fixar algumas referências.

A primeira referência é a célula I9, que corresponde a esse dia 14. Você precisa fixar a referência da linha 9 e remover a referência da coluna. Na data de início, fixe apenas a coluna D, deixando a referência 10 sem cifrão ($). Repita o mesmo procedimento para a segunda verificação, ou seja, a linha fica fixa, e na segunda verificação, relacionada ao término, a coluna é fixada.

Toques finais

Por fim, vamos definir a formatação. Clique em “Formatar” e escolha uma cor, confirmando a formatação condicional. Agora os caminhos do Gantt começam a aparecer para cada um dos projetos. Ao selecionar um projeto específico, você verá as tarefas, os indicadores e o gráfico de Gantt atualizados conforme a seleção feita na parte superior.

Outra informação interessante para formatar no Dashboard de projetos em Excel é a barra de Progresso, que você pode criar através da “Formatação Condicional”. Utilize a “Formatação de Barras” com preenchimento sólido. Em “Gerenciar Regras”, você pode mudar a cor para se adequar à paleta que já está sendo empregada.

Em resumo, o Dashboard de projetos em Excel é uma solução completa para o gerenciamento eficiente de múltiplos projetos. Com suas funcionalidades avançadas e atualização em tempo real, oferece uma visão abrangente e personalizada, facilitando o planejamento, o monitoramento e o controle das iniciativas empresariais. É uma ferramenta indispensável para otimizar o desempenho e o sucesso dos projetos.