Yleinen kaava
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Yhteenveto
Yhteenvetona 30 minuutin kokonaisaika, kun otetaan huomioon joukko kestoa edustavia aikoja, voit käyttää SUMPRODUCT- ja TIME-toimintoja. Esitetyssä esimerkissä kaava G5: ssä on:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
missä "ajat" on nimetty alue C5: C14.
Selitys
Tämä kaava käyttää SUMPRODUCT-funktiota kahden matriisin tuottavan lausekkeen tuloksen yhteenlaskemiseen. Tavoitteena on summata vain yli 30 minuuttia suurempi aika, ylijäämä tai ylimääräinen aika. Ensimmäinen lauseke vähentää 30 minuuttia jokaisesta mainitun ajanjakson ajasta "kertaa":
times-TIME(0,30,0)
Tuloksena on tällainen taulukko:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Toinen lauseke on looginen testi yli 30 minuutin ajan:
times>TIME(0,30,0)
Tämä luo joukon TOSI EPÄTOSI-arvoja:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
SUMPRODUCTin sisällä nämä kaksi taulukkoa kerrotaan yhdessä tämän taulukon luomiseksi:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Huomaa, että ensimmäisen taulukon negatiiviset arvot ovat nyt nollia. Kertomisen aikana TOSI EPÄTOSI -arvot muunnetaan arvoksi 1 ja 0, joten EPÄTOSI-arvot "peruuttavat" ajat, jotka eivät ole yli 30 minuuttia. Lopuksi SUMPRODUCT palauttaa taulukon kaikkien arvojen summan, 1 tunti ja 4 minuuttia (1:04).
Vaihtoehto SUMIFS ja COUNTIFS kanssa
Yksinään SUMIFS ei voi laskea yli 30 minuutin aika-arvojen delta-arvoa. SUMIFS- ja COUNTIFS-arvoja voidaan käyttää yhdessä saadaksesi saman tuloksen kuin yllä oleva SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Times yli 24 tuntia
Jos kokonaisajat voivat ylittää 24 tuntia, käytä tätä mukautettua aikamuotoa seuraavasti:
(h):mm:ss
Hakasulkujen syntaksissa kerrotaan, että Excel ei "kaatu" yli 24 tunnin kertaa.
Auttajapylväällä
Kuten esimerkissä on esitetty, voit myös lisätä apusarakkeen laskemaan ja summaamaan deltat. D5: n kaava kopioituna alaspäin on:
=MAX(C5-"00:30",0)
Tässä MAXia käytetään poistamaan negatiivisen ajan deltat, jotka aiheutuvat sarakkeessa C olevista ajoista, jotka ovat alle 30 minuuttia. Huomaa, että D15: n tulos on sama kuin G5: n tulos.
