Пару недель назад один читатель прислал мне интересный вопрос об отслеживании «остановленного времени» для парка грузовиков. Грузовики отслеживаются с помощью GPS, поэтому местоположение каждого грузовика фиксируется в каждый час дня. Данные выглядят примерно так:
Проблема: какая формула в столбце N правильно рассчитает общее количество остановленных часов?
Я немного упростил это, заменив фактические координаты GPS на местоположения с меткой AE, но концепция остается той же.
Головоломка
На сколько часов был остановлен каждый грузовик?
Или в Excel:
По какой формуле рассчитывается общее количество часов, в течение которых каждый грузовик был остановлен?
Например, мы знаем, что Truck1 был остановлен на 1 час, потому что его местоположение было записано как «A» как в 16, так и в 17 часов.
Предположения
- Есть 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))
