Este fórum está sendo desativado

[JCabral]

Boa tarde, de novo pedindo ajuda

No tópico, https://comunidade.databinteligencia.c ... 4b5#p62865 , solicitei ajuda para num TIMELINE e em função de um determinado Roteiro aparece-se “pintado” via formatação condicional as estradas que pertenciam ao roteiro definido e ao dia da semana.

@babdallas deu-me essa preciosa ajuda através da formula “=SOMARPRODUTO((DESLOCAMENTO((ROTEIROS!$A$2);0;0;CONTAR.VAL(ROTEIROS!$A:$A)-1;6)=$A20)*(DESLOCAMENTO((ROTEIROS!$A$2);0;0;CONTAR.VAL(ROTEIROS!$A:$A)-1;1)=M$19)*(ROTEIROS!$A$2:$F$2=DIA.SEMANA(M$7;11))*($A20<>""))” que faz o destaque das células (Estradas#) em função do roteiro escolhido e dia da semana.



Atendendo a que a cada célula destacada corresponde kms de estrada (Coluna E), conforme mostro na figura abaixo, o que eu precisava agora era calcular para cada estrada e por semana e por mês o valor total de kms, na aba Calculos.

Como mostro na figura e a titulo de exemplo para a Estrada#1 e na Semana 2 o valor esperado em Calculo será 17,250 = 5,750 + 5,750 + 5,750 (células destacadas na semana 2 para a Estrada#1), precisava o mesmo para os meses.



Na aba Calculos_ESPERADO estão os valores esperados do calculo pretendido (só fiz para 20 Estradas)

Obrigado
Jorge

NOTA: Tb aqui - https://www.excelforum.com/excel-formul ... d-kms.html

[Estevaoba]

Boa noite.

Fiz com fórmulas usando a sua planilha Timeline para listar valores nas células em que a sua formatação é verdadeira com esta fórmula em M20 da aba Timeline, arrastar para a direita e para baixo (com botão direito e sem formatação):

Código: Selecionar todos

=SEERRO(CONT.SES(ÍNDICE(ROTEIROS!$B$3:$F$28;0;CORRESP(DIA.DA.SEMANA(TIMELINE!M$7;2);ROTEIROS!$B$2:$F$2;0));TIMELINE!$A20;ROTEIROS!$A$3:$A$28;TIMELINE!M$19);0)

Então esta fórmula para soma semanal em L3 da aba Cálculos e arrastar para a direita e para baixo:

Código: Selecionar todos

=TIMELINE!$E20*CONT.SES(TIMELINE!$M$8:$NM$8;L$2;TIMELINE!$M20:$NM20;">0")

E esta para soma mensal em DT3 da aba Cálculos e arrastar para a direita e para baixo:

Código: Selecionar todos

=TIMELINE!$E20*CONT.SES(TIMELINE!$M$7:$NM$7;">="&DATA(ANO(TIMELINE!$M$7);COL(A$1);1);TIMELINE!$M$7:$NM$7;"<="&FIMMÊS(DATA(ANO(TIMELINE!$M$7);COL(A$1);1);0);TIMELINE!$M20:$NM20;">0")

Caso não possa usar a planilha Timeline para aplicar a primeira fórmula acima, aplique-a numa nova aba e ajuste os intervalos nas fórmulas conforme a nova aba usada.

Good luck!

[JCabral]

@Estevaoba,

Antes de mais quero agradecer muito a sua resposta, infelizmente continuo sem poder agradecer "lá em cima" pois continua a aparecer a mensagem "Você não está autorizado a fazer esta ação"

Quanto à sua solução: por culpa minha não referi que a planilha TIMELINE é onde eu coloco os km diários que faço, ou seja, os dados (kms) que ai coloco vão servir para calcular não os kms planeados mas os kms fiscalizados, eu como deve ter reparado "escondi" essas colunas na aba Calculos somente para não criar muito "ruido" na aba.

Por esta razão não posso utilizar essa planilha para calcular os valores planeados, semana/mês. Sem querer entrar em grandes explicações para não tirar o foco do que necessito, as células que estão destacadas na aba TIMELINE apenas servem de alerta para eu saber que devo passar nessa estrada ou seja esse é o meu PLANEAMENTO.

Assim e com sua solução, que funciona perfeitamente, tenho que duplicar, como refere, obrigatoriamente a aba TIMELINE.

Pergunto se não será possível que isso não aconteça?
Ou seja, ou utilizando a sua primeira formula, que faz o "destaque", identifica as células e sinaliza ou utilizando a formula do @babdallas, que também identifica as células dos roteiros , não será possível não duplicar a planilha?

Seria fantástico.
Fica o desafio.

Muito obrigado mais uma vez.
Jorge

[Estevaoba]

De nada, amigo. Fico feliz em poder ajudar.

Desafio interessante.
Para o cálculo semanal, consegui com esta fórmula, em L3, arrastar para a direita e para baixo:

Código: Selecionar todos

=TIMELINE!$E20*SOMARPRODUTO((ROTEIROS!$A$3:$A$28=ÍNDICE(TIMELINE!$M$19:$NM$19;CORRESP(L$2;TIMELINE!$M$8:$NM$8;0)))*(ROTEIROS!$B$3:$G$28=$K3))

Obs: A fórmula só funciona se dentro de cada semana for um único valor (F-FER1 ou R-ROT1), conforme a sua planilha.

Para o cálculo mensal está mais difícil.

Ab.

[JCabral]

Caro @Estevaoba,

Mais uma vez muito obrigado.
Mas.....

Obs: A fórmula só funciona se dentro de cada semana for um único valor (F-FER1 ou R-ROT1), conforme a sua planilha.

Não será muito comum mas pode acontecer, basta que tenha um acontecimento qualquer num dia e então eu para compensar um dia da semana que não fui fiscalizar uma estrada eu posso ter que criar um roteiro para um dia especifico e assim posso ter 2ª, 3ª, 4ª e 6ª um Roteiro qualquer (R-TIPO1) e na quinta-feira ter o roteiro R-ROT2.

Ab

[JCabral]

Caro @Estevaoba,

Desde já agradecer mais uma vez a sua solução e deixar aqui uma solução desenvolvida pelo XLent -
https://www.excelforum.com/excel-formul ... d-kms.html - que permite o calculo sem a necessidade de duplicar planilhas para cálculos intermédios.

Código: Selecionar todos

calculations!L3:

=IFERROR(LET(dt,TIMELINE!$M$7:$NM$7,wk,TIMELINE!$M$8:$NM$8,itinx,TIMELINE!$M$19:$NM$19,ext,TIMELINE!$E20,itin,ITINERARY!$A$3:$F$28,roads,IFERROR(REPT(INDEX(itin,SEQUENCE(ROWS(itin)),1+WEEKDAY(dt,2)),itinx=INDEX(itin,SEQUENCE(ROWS(itin)),1)),""),SUMPRODUCT((FILTER(roads,wk=L$2)=$K3)*ext)),0)

applied to matrix

calculations!DT3:

=IFERROR(LET(dt,TIMELINE!$M$7:$NM$7,wk,TIMELINE!$M$8:$NM$8,itinx,TIMELINE!$M$19:$NM$19,ext,TIMELINE!$E20,itin,ITINERARY!$A$3:$F$28,roads,IFERROR(REPT(INDEX(itin,SEQUENCE(ROWS(itin)),1+WEEKDAY(dt,2)),itinx=INDEX(itin,SEQUENCE(ROWS(itin)),1)),""),SUMPRODUCT((FILTER(roads,TEXT(dt,"mmmm")=Calculations!DT$2)=$DS3)*ext)),0)

applied to matrix

Julgo que esta solução só funciona para o Excel 365, pelo que deixo o desafio de a tornar disponível para outras versões.
Anexo também um ficheiro para que as conversões das formulas seja mais fácil.

Obrigado mais uma vez.

[Estevaoba]

Boa tarde, @JCabral. Obrigado pelo feedback.

Eu até cheguei a considerar a possibilidade de uma solução com a função LET, mas não pude dedicar o tempo necessário.
Que bom que deu certo!

Ab.