Prieš porą savaičių skaitytojas man išsiuntė įdomų klausimą apie sunkvežimių parko „sustojimo laiko“ stebėjimą. Sunkvežimiai yra stebimi GPS pagalba, todėl kiekvieno sunkvežimio vieta registruojama kiekvieną dienos valandą. Duomenys atrodo maždaug taip:
Iššūkis: kokia N stulpelio formulė teisingai apskaičiuos visas sustabdytas valandas?
Šiek tiek supaprastinau pakeisdamas faktines GPS koordinates vietomis, pažymėtomis AE, tačiau koncepcija išlieka ta pati.
Dėlionė
Kiek valandų buvo sustabdytas kiekvienas sunkvežimis?
Arba „Excel“ kalba:
Kokia formulė apskaičiuos bendras kiekvieno sunkvežimio valandas?
Pavyzdžiui, žinome, kad „Truck1“ buvo sustabdytas 1 valandai, nes jo vieta buvo užfiksuota kaip „A“ ir 16, ir 17 val.
Prielaidos
- Yra 5 vietos šiais pavadinimais: A, B, C, D, E
- Sunkvežimis toje pačioje vietoje dvi valandas iš eilės = 1 valanda sustojo
Turite formulę, kuri tai padarys?
Atsisiųskite darbaknygę ir pasidalykite savo formule toliau pateiktose pastabose. Kaip ir tiek daug „Excel“ dalykų, yra daugybė būdų, kaip išspręsti šią problemą!
Atsakymas (spustelėkite, jei norite išplėsti)Šiuo atveju universalus „SUMPRODUCT“ yra elegantiškas būdas išspręsti šią problemą:
=SUMPRODUCT(--(C6:K6=D6:L6))
Užrašų diapazonai C6: K6 kompensuojami vienu stulpeliu. Iš esmės mes lyginame „ankstesnes pozicijas“ su „kitomis pozicijomis“ ir skaičiuojame atvejus, kai ankstesnė pozicija sutampa su kita pozicija.
6 eilutės duomenims palyginimo operacija sukuria TRUE FALSE reikšmių masyvą:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Tada dvigubas neigiamasis verčia TRUE FALSE reikšmes į vienetus ir nulius, o SUMPRODUCT tiesiog masyvo suma, kuri yra 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
