Cách đây vài tuần, một độc giả đã gửi cho tôi một câu hỏi thú vị về việc theo dõi "thời gian dừng" của một đoàn xe tải. Các xe tải được theo dõi bằng GPS nên vị trí được ghi lại vào mỗi giờ trong ngày cho mỗi xe tải. Dữ liệu trông giống như sau:
Thử thách: công thức nào trong cột N sẽ tính đúng tổng số giờ đã dừng?
Tôi đã đơn giản hóa điều này một chút bằng cách thay thế các tọa độ GPS thực tế bằng các vị trí có nhãn AE, nhưng khái niệm vẫn như cũ.
Câu đố
Mỗi xe tải dừng lại trong bao nhiêu giờ?
Hoặc, trong Excel-speak:
Công thức nào sẽ tính tổng số giờ mỗi xe tải đã dừng lại?
Ví dụ: chúng tôi biết Truck1 đã bị dừng trong 1 giờ vì vị trí của nó được ghi là "A" vào cả 4 giờ chiều và 5 giờ chiều.
Giả định
- Có 5 vị trí có tên sau: A, B, C, D, E
- Một xe tải ở cùng một địa điểm trong hai giờ liên tiếp = 1 giờ thì dừng lại
Có một công thức sẽ làm được điều đó?
Tải xuống sổ làm việc và chia sẻ công thức của bạn trong các nhận xét bên dưới. Cũng như rất nhiều thứ trong Excel, có nhiều cách để giải quyết vấn đề này!
Trả lời (bấm để mở rộng)Trong trường hợp này, SUMPRODUCT đa năng là một cách hữu ích để giải quyết vấn đề này:
=SUMPRODUCT(--(C6:K6=D6:L6))
Các dải lưu ý C6: K6 được bù đắp bởi một cột. Về bản chất, chúng tôi đang so sánh "các vị trí trước đó" với "các vị trí tiếp theo" và đếm các trường hợp mà vị trí trước đó giống với vị trí tiếp theo.
Đối với dữ liệu trong hàng 6, thao tác so sánh tạo ra một mảng các giá trị TRUE FALSE:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Sau đó, giá trị phủ định kép buộc các giá trị TRUE FALSE thành các giá trị một và số không, và SUMPRODUCT chỉ đơn giản là tổng của mảng, là 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
