Пару тижнів тому читач надіслав мені цікаве запитання про відстеження "зупиненого часу" для парку вантажівок. Вантажівки відстежуються за допомогою GPS, тому місцезнаходження реєструється щогодини доби для кожної вантажівки. Дані виглядають приблизно так:
Проблема: яка формула у графі N правильно розрахує загальну кількість зупинених годин?
Я трохи спростив це, замінивши фактичні координати GPS місцями, позначеними AE, але концепція залишається незмінною.
Головоломка
На скільки годин зупиняли кожну вантажівку?
Або, розмовляючи Excel:
За якою формулою буде розрахована загальна кількість годин, на яких зупинено кожен вантажівка?
Наприклад, ми знаємо, що Truck1 зупинили на 1 годину, оскільки його місцезнаходження було зафіксовано як "A" і в 16:00, і в 17:00.
Припущення
- Є 5 місць з цими назвами: A, B, C, D, E
- Вантажівка в тому самому місці протягом двох годин поспіль = 1 година зупинилася
Отримали формулу, яка це зробить?
Завантажте книгу та поділіться своєю формулою в коментарях нижче. Як і багато іншого в Excel, існує безліч способів вирішити цю проблему!
Відповідь (натисніть, щоб розгорнути)У цьому випадку універсальний SUMPRODUCT - це елегантний спосіб вирішити цю проблему:
=SUMPRODUCT(--(C6:K6=D6:L6))
Діапазони приміток C6: K6 зміщуються на один стовпець. По суті, ми порівнюємо "попередні позиції" з "наступними позиціями" і підраховуємо випадки, коли попередня позиція така ж, як і наступна позиція.
Для даних у рядку 6 операція порівняння створює масив значення TRUE FALSE:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Потім подвійний мінус примушує значення TRUE FALSE до одиниць і нулів, а SUMPRODUCT - просто сума масиву, яка дорівнює 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
