Формула-загадка - как долго стоял грузовик? - Головоломка

Пару недель назад один читатель прислал мне интересный вопрос об отслеживании «остановленного времени» для парка грузовиков. Грузовики отслеживаются с помощью GPS, поэтому местоположение каждого грузовика фиксируется в каждый час дня. Данные выглядят примерно так:


Проблема: какая формула в столбце N правильно рассчитает общее количество остановленных часов?

Я немного упростил это, заменив фактические координаты GPS на местоположения с меткой AE, но концепция остается той же.

Головоломка

На сколько часов был остановлен каждый грузовик?

Или в Excel:

По какой формуле рассчитывается общее количество часов, в течение которых каждый грузовик был остановлен?

Например, мы знаем, что Truck1 был остановлен на 1 час, потому что его местоположение было записано как «A» как в 16, так и в 17 часов.

Предположения

  1. Есть 5 локаций с такими названиями: A, B, C, D, E
  2. Грузовик в одном и том же месте в течение двух часов подряд = 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))

Интересные статьи...