For et par uger siden sendte en læser mig et interessant spørgsmål om sporing af "stoppetiden" for en flåde af lastbiler. Lastbiler spores med GPS, så der registreres en placering på hver time på dagen for hver lastbil. Dataene ser sådan ud:
Udfordringen: hvilken formel i kolonne N beregner korrekt det samlede antal stoppede timer?
Jeg har forenklet dette lidt ved at erstatte faktiske GPS-koordinater med placeringer mærket AE, men konceptet forbliver det samme.
Puslespillet
I hvor mange timer blev hver lastbil stoppet?
Eller i Excel-tale:
Hvilken formel beregner det samlede antal timer, hver truck blev stoppet?
For eksempel ved vi, at Truck1 blev stoppet i 1 time, fordi dets placering blev registreret som "A" kl. 16:00 og 17:00.
Antagelser
- Der er 5 placeringer med disse navne: A, B, C, D, E
- En lastbil på samme sted i to på hinanden følgende timer = 1 time stoppet
Har du en formel, der kan gøre det?
Download projektmappen, og del din formel i kommentarerne nedenfor. Som med så mange ting i Excel, er der mange måder at løse dette problem på!
Svar (klik for at udvide)I dette tilfælde er den alsidige SUMPRODUCT en elegant måde at løse dette problem på:
=SUMPRODUCT(--(C6:K6=D6:L6))
Bemærkningsintervaller C6: K6 udlignes med en kolonne. I det væsentlige sammenligner vi "tidligere positioner" med "næste positioner" og tæller tilfælde, hvor den tidligere position er den samme som den næste position.
For dataene i række 6 opretter sammenligningsoperationen en matrix med SANDE FALSKE værdier:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Dobbeltnegativet tvinger derefter de SANDE FALSKE værdier til én og nuller, og SUMPRODUCT simpelthen summen af arrayet, som er 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
