Een paar weken geleden stuurde een lezer me een interessante vraag over het bijhouden van de "stoptijd" voor een vloot vrachtwagens. De vrachtwagens worden gevolgd door GPS, zodat voor elke vrachtwagen op elk uur van de dag een locatie wordt geregistreerd. De gegevens zien er ongeveer zo uit:
De uitdaging: welke formule in kolom N berekent het totale aantal stopgezette uren correct?
Ik heb dit een beetje vereenvoudigd door de werkelijke GPS-coördinaten te vervangen door locaties met het label AE, maar het concept blijft hetzelfde.
De puzzel
Hoeveel uur is elke vrachtwagen gestopt?
Of, in Excel-spreken:
Welke formule berekent het totale aantal uren dat elke vrachtwagen is gestopt?
We weten bijvoorbeeld dat Truck1 1 uur is gestopt omdat de locatie zowel om 16.00 uur als om 17.00 uur als "A" is geregistreerd.
Veronderstellingen
- Er zijn 5 locaties met deze namen: A, B, C, D, E
- Een vrachtwagen op dezelfde locatie gedurende twee opeenvolgende uren = 1 uur gestopt
Heb je een formule die het zal doen?
Download de werkmap en deel uw formule in de onderstaande opmerkingen. Zoals met zoveel dingen in Excel, zijn er veel manieren om dit probleem op te lossen!
Antwoord (klik om uit te vouwen)In dit geval is de veelzijdige SUMPRODUCT een elegante manier om dit probleem op te lossen:
=SUMPRODUCT(--(C6:K6=D6:L6))
Let op de bereiken C6: K6 zijn verschoven met één kolom. In wezen vergelijken we "vorige posities" met "volgende posities", en tellen we gevallen waarin de vorige positie hetzelfde is als de volgende positie.
Voor de gegevens in rij 6 maakt de vergelijkingsbewerking een array van TRUE FALSE-waarden:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Het dubbele negatief dwingt vervolgens de TRUE FALSE-waarden naar enen en nullen, en SOMPRODUCT gewoon de som van de array, die 1 is:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
