För några veckor sedan skickade en läsare mig en intressant fråga om att spåra "stopptiden" för en lastbilsflotta. Lastbilarna spåras med GPS så att en plats registreras varje timme på dygnet för varje lastbil. Uppgifterna ser ungefär så här ut:
Utmaningen: vilken formel i kolumn N beräknar totalt stoppade timmar korrekt?
Jag har förenklat detta lite genom att ersätta faktiska GPS-koordinater med platser märkta AE, men konceptet är detsamma.
Pusslet
Hur många timmar stoppades varje lastbil?
Eller i Excel-tala:
Vilken formel beräknar det totala antalet timmar som varje lastbil stoppades?
Vi vet till exempel att Truck1 stoppades i en timme eftersom platsen registrerades som "A" både klockan 16 och 17.
Antaganden
- Det finns 5 platser med dessa namn: A, B, C, D, E
- En lastbil på samma plats i två timmar i rad = 1 timme stannat
Har du en formel som klarar det?
Ladda ner arbetsboken och dela din formel i kommentarerna nedan. Som med så många saker i Excel finns det många sätt att lösa detta problem!
Svar (klicka för att expandera)I det här fallet är den mångsidiga SUMPRODUCT ett elegant sätt att lösa detta problem:
=SUMPRODUCT(--(C6:K6=D6:L6))
Noteringsområden C6: K6 kompenseras av en kolumn. I huvudsak jämför vi "tidigare positioner" med "nästa positioner" och räknar fall där den tidigare positionen är densamma som nästa position.
För data i rad 6 skapar jämförelseoperationen en matris med SANT FALSKA värden:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Dubbelnegativet tvingar sedan fram SANT FALSE-värden till enor och nollor och SUMPRODUCT helt enkelt summan av matrisen, som är 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
