Algumas semanas atrás, um leitor me enviou uma pergunta interessante sobre como rastrear o "tempo de parada" de uma frota de caminhões. Os caminhões são rastreados por GPS para que uma localização seja registrada a cada hora do dia para cada caminhão. Os dados são mais ou menos assim:
O desafio: qual fórmula na coluna N calculará corretamente o total de horas paradas?
Simplifiquei um pouco substituindo as coordenadas GPS reais por locais marcados como AE, mas o conceito permanece o mesmo.
O quebra-cabeça
Por quantas horas cada caminhão ficou parado?
Ou, na linguagem do Excel:
Qual fórmula calculará o total de horas de cada caminhão parado?
Por exemplo, sabemos que o Truck1 foi parado por 1 hora porque sua localização foi registrada como "A" às 16h e às 17h.
Suposições
- Existem 5 locais com estes nomes: A, B, C, D, E
- Um caminhão no mesmo local por duas horas consecutivas = 1 hora parado
Tem uma fórmula que vai resolver?
Baixe a apostila e compartilhe sua fórmula nos comentários abaixo. Como acontece com tantas coisas no Excel, há muitas maneiras de resolver esse problema!
Resposta (clique para expandir)Nesse caso, o versátil SUMPRODUCT é uma maneira elegante de resolver esse problema:
=SUMPRODUCT(--(C6:K6=D6:L6))
Intervalos de notas C6: K6 são compensados por uma coluna. Em essência, estamos comparando "posições anteriores" com "próximas posições" e contando casos em que a posição anterior é igual à próxima.
Para os dados da linha 6, a operação de comparação cria uma matriz de valores TRUE FALSE:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
O duplo negativo então força os valores TRUE FALSE para uns e zeros, e SUMPRODUCT simplesmente a soma da matriz, que é 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
