Преди няколко седмици читател ми изпрати интересен въпрос относно проследяването на „спряното време“ за флота от камиони. Камионите се проследяват от 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))
