Vor ein paar Wochen schickte mir ein Leser eine interessante Frage zur Verfolgung der "angehaltenen Zeit" für eine LKW-Flotte. Die Lastwagen werden per GPS verfolgt, sodass zu jeder Tageszeit für jeden Lastwagen ein Standort aufgezeichnet wird. Die Daten sehen ungefähr so aus:
Die Herausforderung: Welche Formel in Spalte N berechnet die Gesamtstoppstunden korrekt?
Ich habe dies ein wenig vereinfacht, indem ich die tatsächlichen GPS-Koordinaten durch Orte mit der Bezeichnung AE ersetzt habe, aber das Konzept bleibt das gleiche.
Das Puzzle
Für wie viele Stunden wurde jeder LKW angehalten?
Oder in Excel-Sprache:
Welche Formel berechnet die Gesamtstunden, die jeder LKW angehalten wurde?
Zum Beispiel wissen wir, dass Truck1 für 1 Stunde angehalten wurde, weil sein Standort sowohl um 16 Uhr als auch um 17 Uhr als "A" aufgezeichnet wurde.
Annahmen
- Es gibt 5 Orte mit diesen Namen: A, B, C, D, E.
- Ein LKW am selben Ort für zwei aufeinanderfolgende Stunden = 1 Stunde hielt an
Hast du eine Formel, die das macht?
Laden Sie die Arbeitsmappe herunter und teilen Sie Ihre Formel in den Kommentaren unten. Wie bei so vielen Dingen in Excel gibt es viele Möglichkeiten, dieses Problem zu lösen!
Antwort (zum Vergrößern anklicken)In diesem Fall ist das vielseitige SUMPRODUCT eine elegante Möglichkeit, dieses Problem zu lösen:
=SUMPRODUCT(--(C6:K6=D6:L6))
Die Notenbereiche C6: K6 sind um eine Spalte versetzt. Im Wesentlichen vergleichen wir "vorherige Positionen" mit "nächsten Positionen" und zählen Fälle, in denen die vorherige Position mit der nächsten Position identisch ist.
Für die Daten in Zeile 6 erstellt die Vergleichsoperation ein Array von TRUE FALSE-Werten:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Das doppelte Negativ zwingt dann die TRUE FALSE-Werte zu Einsen und Nullen und SUMPRODUCT einfach die Summe des Arrays, die 1 ist:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
