Olá Pessoal, vamos para mais um artigo, desta vez este artigo é para quem trabalha constantemente com projetos e precisa verificar o planejamento orçado do projeto vs o realizado, muitas vezes temos o valor planejado correto, mensalmente, mas o realizado não, para isso foi desenvolvido uma ferramenta para que faça essa sumarização e que nos mostre de maneira visual se estamos dentro do previsto ou não, para aumentar a complexidade, fiz a adição de projetos novos de maneira automatica e o ajuste do gráfico.
Caso esse artigo tenha 100 compartilhamentos irei disponibilizar a planilha para todos!
Abraços e vamos as etapas
Primeiro temos que montar a nossa base de dados em uma guia, aqui chamei de "Base", e na outra, será demonstrado o gráfico e todas as fórmulas.
Após isso, teremos que trazer todos os projetos existentes na nossa base em células, que segue na célula B3 até a B20 (ou quantas forem necessário), segue a fórmula:
=SE(SEERRO(ÍNDICE(Base!$B$3:$B$41; CORRESP(0; ÍNDICE(CONT.SE($B$2:B2;Base!$B$3:$B$41);0;0);0));"")=0;"";SEERRO(ÍNDICE(Base!$B$3:$B$41; CORRESP(0; ÍNDICEX(CONT.SE($B$2:B2;Base!$B$3:$B$41);0;0);0));""))Ou em inglês
=IF(IFERROR(INDEX(Base!$B$3:$B$41; MATCH(0; INDEX(COUNTIF($B$2:B2;Base!$B$3:$B$41);0;0);0));"")=0;"";IFERROR(INDEX(Base!$B$3:$B$41; MATCH(0; INDEX(COUNTIF($B$2:B2;Base!$B$3:$B$41);0;0);0));""))Agora temos que vincular a nossa validação de dados (Menu Dados->Validação de dados->Permitir: Lista) que está na célula E2:
=DESLOC($B$3;0;0;CONT.SE($B:$B;">a")-1;1)ou em inglês
=OFFSET($B$3;0;0;COUNTIF($B:$B;">a")-1;1)Uma parte fundamental para que seja vinculado tanto o gráfico "dinâmico" quanto a formatação condicional é o número de registros que temos da nossa base, sendo este o vínculo para ambos, segue a fórmula na célula E3 (esta parte poderia ser oculta, mas ficaria muito complexo para apresentar neste artigo, mas é possível trabalhar sem esta célula):
=DATADIF(MENOR(ÍNDICE((DESLOC(Base!$B$2;1;0;CONT.NÚM(Base!$A:$A);1)=E2)*(DESLOC(Base!$A$2;1;0;CONT.NÚM(Base!$A:$A);1));0);CONT.SES(Base!$B:$B;"<>"&E2;Base!$B:$B;"<>"));MAIOR(ÍNDICE((DESLOC(Base!$B$2;1;0;CONT.NÚM(Base!$A:$A);1)=E2)*(DESLOC(Base!$A$2;1;0;CONT.NÚM(Base!$A:$A);1));0);1);"m")+2Ou em inglês
=DATEDIF(SMALL(INDEX((OFFSET(Base!$B$2;1;0;COUNT(Base!$A:$A);1)=E2)*(OFFSET(Base!$A$2;1;0;COUNT(Base!$A:$A);1));0);COUNTIFS(Base!$B:$B;"<>"&E2;Base!$B:$B;"<>"));LARGE(INDEX((OFFSET(Base!$B$2;1;0;COUNT(Base!$A:$A);1)=E2)*(OFFSET(Base!$A$2;1;0;COUNT(Base!$A:$A);1));0);1);"m")+2A partir destes valores podemos começar a trazer os registros sumarizados da nossa base, vamos as colunas,
Coluna D - Data (iniciando na célula D6), segue a fórmula:
=SE(LINS($D$6:D6)=1;DATA(ANO(MENOR(ÍNDICE((DESLOC(Base!$B$2;1;0;CONT.NÚM(Base!$A:$A);1)=E2)*(DESLOC(Base!$A$2;1;0;CONT.NÚM(Base!$A:$A);1));0);CONT.SES(Base!$B:$B;"<>"&E2;Base!$B:$B;"<>")));MÊS(MENOR(ÍNDICE((DESLOC(Base!$B$2;1;0;CONT.NÚM(Base!$A:$A);1)=E2)*(DESLOC(Base!$A$2;1;0;CONT.NÚM(Base!$A:$A);1));0);CONT.SES(Base!$B:$B;"<>"&E2;Base!$B:$B;"<>")))-1;1);DATAM(D5;1))ou em inglês
=IF(ROWS($D$6:D6)=1;DATE(YEAR(SMALL(INDEX((OFFSET(Base!$B$2;1;0;COUNT(Base!$A:$A);1)=E2)*(OFFSET(Base!$A$2;1;0;COUNT(Base!$A:$A);1));0);COUNTIFS(Base!$B:$B;"<>"&E2;Base!$B:$B;"<>")));MONTH(SMALL(INDEX((OFFSET(Base!$B$2;1;0;COUNT(Base!$A:$A);1)=E2)*(OFFSET(Base!$A$2;1;0;COUNT(Base!$A:$A);1));0);COUNTIFS(Base!$B:$B;"<>"&E2;Base!$B:$B;"<>")))-1;1);EDATE(D5;1))Coluna E - Mês (Iniciando na célula E6), segue a fórmula:
=PROPER(TEXT(D6;"mmm/aa"))ou em inglês
=PRI.MAIÚSCULA(TEXTO(D6;"mmm/aa"))Coluna F - Valor Planejado (Iniciando na célula F6), segue a fórmula:3
=SOMASES(Base!$C:$C;Base!$B:$B;$E$2;Base!$A:$A;"<="&FIMMÊS(D6;0))ou em inglês
=SUMIFS(Base!$C:$C;Base!$B:$B;$E$2;Base!$A:$A;"<="&EOMONTH(D6;0))Coluna G - Valor Realizado (Iniciando na célula G6), segue a fórmula:
=SE(CONT.SES(Base!$B:$B;$E$2;Base!$A:$A;">="&D6;Base!$A:$A;"<="&FIMMÊS(D6;0);Base!$D:$D;">0")>0;SOMASES(Base!$D:$D;Base!$B:$B;$E$2;Base!$A:$A;"<="&FIMMÊS(D6;0));SE(LINS($D$6:D6)=1;0;NÃO.DISP()))ou em inglês
=IF(COUNTIFS(Base!$B:$B;$E$2;Base!$A:$A;">="&D6;Base!$A:$A;"<="&EOMONTH(D6;0);Base!$D:$D;">0")>0;SUMIFS(Base!$D:$D;Base!$B:$B;$E$2;Base!$A:$A;"<="&EOMONTH(D6;0));IF(ROWS($D$6:D6)=1;0;NA()))Agora é só aplicar as formatações condicionais e vincular o gráfico para que seja alterado de maneira dinâmica.
Gostou do artigo? Curta e compartilhe em suas redes sociais.
E como você aplicaria a Curva S - Financeira em suas atividades/projetos? Comente conosco!
Espero que tenham gostado e até o próximo artigo!
Baldini Fabio
Frase do dia - "Incendeie sua vida e procure por pessoas que abanem as suas chamas." Pensador Persa Rumi
