Kilka tygodni temu czytelnik wysłał mi interesujące pytanie dotyczące śledzenia „zatrzymanego czasu” dla floty ciężarówek. Ciężarówki są śledzone przez GPS, więc lokalizacja jest zapisywana o każdej porze dnia dla każdej ciężarówki. Dane wyglądają mniej więcej tak:
Wyzwanie: jaka formuła w kolumnie N poprawnie obliczy całkowitą liczbę zatrzymanych godzin?
Trochę to uprościłem, zastępując rzeczywiste współrzędne GPS lokalizacjami oznaczonymi jako AE, ale koncepcja pozostaje taka sama.
Zagadka
Na ile godzin zatrzymywano każdą ciężarówkę?
Lub, mówiąc językiem Excela:
Jaka formuła obliczy całkowitą liczbę godzin, w których każda ciężarówka była zatrzymana?
Na przykład wiemy, że Truck1 został zatrzymany na 1 godzinę, ponieważ jego lokalizacja została zarejestrowana jako „A” zarówno o 16:00, jak i 17:00.
Założenia
- Istnieje 5 lokalizacji o tych nazwach: A, B, C, D, E
- Ciężarówka w tym samym miejscu przez dwie kolejne godziny = 1 godzina postoju
Masz formułę, która to zrobi?
Pobierz skoroszyt i udostępnij swoją formułę w komentarzach poniżej. Podobnie jak w przypadku wielu rzeczy w programie Excel, istnieje wiele sposobów rozwiązania tego problemu!
Odpowiedź (kliknij, aby rozwinąć)W tym przypadku wszechstronny SUMPRODUCT to elegancki sposób rozwiązania tego problemu:
=SUMPRODUCT(--(C6:K6=D6:L6))
Uwaga zakresy C6: K6 są przesunięte o jedną kolumnę. W istocie porównujemy „poprzednie pozycje” z „następnymi pozycjami” i liczymy przypadki, w których poprzednia pozycja jest taka sama jak następna.
Dla danych w wierszu 6 operacja porównania tworzy tablicę wartości TRUE FALSE:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Podwójna ujemna wartość zamienia wartości PRAWDA FAŁSZ na jedynki i zera, a SUMPRODUKCJA po prostu suma tablicy, która wynosi 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
