For noen uker siden sendte en leser meg et interessant spørsmål om sporing av "stoppetiden" for en lastebilflåte. Lastebilene blir sporet av GPS, slik at en plassering registreres hver time på dagen for hver lastebil. Dataene ser omtrent slik ut:
Utfordringen: hvilken formel i kolonne N beregner riktig stoppet antall timer?
Jeg har forenklet dette litt ved å erstatte faktiske GPS-koordinater med steder merket AE, men konseptet forblir det samme.
Puslespillet
I hvor mange timer ble hver lastebil stoppet?
Eller i Excel-snakk:
Hvilken formel beregner det totale antallet timer hver lastebil ble stoppet?
For eksempel vet vi at Truck1 ble stoppet i 1 time fordi beliggenheten ble registrert som "A" klokken 16.00 og 17.00.
Antagelser
- Det er 5 steder med disse navnene: A, B, C, D, E
- En lastebil på samme sted i to sammenhengende timer = 1 time stoppet
Har du en formel som vil gjøre det?
Last ned arbeidsboken og del formelen din i kommentarene nedenfor. Som med så mange ting i Excel, er det mange måter å løse dette problemet på!
Svar (klikk for å utvide)I dette tilfellet er den allsidige SUMPRODUCT en elegant måte å løse dette problemet på:
=SUMPRODUCT(--(C6:K6=D6:L6))
Merknader C6: K6 motregnes av en kolonne. I hovedsak sammenligner vi "tidligere posisjoner" med "neste posisjoner", og teller tilfeller der forrige posisjon er den samme som neste posisjon.
For dataene i rad 6 oppretter sammenligningsoperasjonen en matrise med SANTE FALSKE verdier:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Dobbeltnegativet tvinger deretter de SANNE FALSKE verdiene til ener og nuller, og SUMPRODUCT bare summen av arrayet, som er 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
