Acum câteva săptămâni, un cititor mi-a trimis o întrebare interesantă despre urmărirea „timpului oprit” pentru o flotă de camioane. Camioanele sunt urmărite prin GPS, astfel încât o locație este înregistrată la fiecare oră a zilei pentru fiecare camion. Datele arată cam așa:
Provocarea: ce formulă din coloana N va calcula corect numărul total de ore oprite?
Am simplificat puțin acest lucru prin înlocuirea coordonatelor GPS reale cu locații etichetate AE, dar conceptul rămâne același.
Puzzel-ul
Pentru câte ore a fost oprit fiecare camion?
Sau, în Excel-speak:
Ce formulă va calcula numărul total de ore în care a fost oprit fiecare camion?
De exemplu, știm că Truck1 a fost oprit timp de 1 oră, deoarece locația sa a fost înregistrată ca „A” atât la 16:00, cât și la 17:00.
Ipoteze
- Există 5 locații cu aceste nume: A, B, C, D, E
- Un camion în aceeași locație timp de două ore consecutive = 1 oră oprit
Ai o formulă care să o facă?
Descărcați registrul de lucru și împărtășiți-vă formula în comentariile de mai jos. Ca și în atâtea lucruri din Excel, există multe modalități de a rezolva această problemă!
Răspundeți (faceți clic pentru a extinde)În acest caz, versatilul SUMPRODUCT este un mod elegant de a rezolva această problemă:
=SUMPRODUCT(--(C6:K6=D6:L6))
Notă intervalele C6: K6 sunt compensate cu o coloană. În esență, comparăm „pozițiile anterioare” cu „pozițiile următoare” și numărăm cazurile în care poziția anterioară este aceeași cu poziția următoare.
Pentru datele din rândul 6, operația de comparație creează o matrice de valori ADEVĂRATE FALSE:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Negativul dublu constrânge apoi valorile ADEVĂRATE FALSE la unii și zerouri, iar SUMPRODUCT pur și simplu suma matricei, care este 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
