Pari viikkoa sitten lukija lähetti minulle mielenkiintoisen kysymyksen kuorma-autokannan "pysähtyneen ajan" seuraamisesta. Kuorma-autoja seurataan GPS: n avulla, joten jokaiselle kuorma-autolle tallennetaan sijainti kellonajan joka päivä. Tiedot näyttävät tältä:
Haaste: mikä sarakkeen N kaava laskee oikein pysäytetyt tunnit?
Olen yksinkertaistanut tätä hieman korvaamalla todelliset GPS-koordinaatit AE-merkinnöillä, mutta konsepti pysyy samana.
Palapeli
Kuinka moneksi tunniksi kukin kuorma-auto pysähtyi?
Tai Excel-puhu:
Mikä kaava laskee kuorma-autojen pysäyttämisen kokonaisajan?
Esimerkiksi tiedämme, että Truck1 pysäytettiin 1 tunniksi, koska sen sijainti kirjattiin "A": ksi sekä kuusitoista että kello 17.
Oletukset
- Näitä nimiä on 5: A, B, C, D, E
- Kuorma-auto samassa paikassa kahden peräkkäisen tunnin ajan = 1 tunti pysähtynyt
Onko sinulla kaava, joka tekee sen?
Lataa työkirja ja jaa kaava alla olevissa kommenteissa. Kuten niin monissa asioissa Excelissä, on myös monia tapoja ratkaista tämä ongelma!
Vastaa (laajenna napsauttamalla)Tässä tapauksessa monipuolinen SUMPRODUCT on tyylikäs tapa ratkaista tämä ongelma:
=SUMPRODUCT(--(C6:K6=D6:L6))
Huomautusalueet C6: K6 on siirretty yhdellä sarakkeella. Pohjimmiltaan verrataan "edellisiä positioita" ja "seuraavia positioita" ja laskemme tapauksia, joissa edellinen sijainti on sama kuin seuraava.
Rivin 6 tiedoille vertailutoiminto luo taulukon TOSI EPÄTOSI-arvoista:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Kaksoisnegatiivi pakottaa sitten TOSI EPÄTOSI -arvot yksiköihin ja nolliin, ja SUMPRODUCT yksinkertaisesti taulukon summa, joka on 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
