Γενική φόρμουλα
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Περίληψη
Για να συνοψίσουμε το συνολικό χρονικό διάστημα πάνω από 30 λεπτά, δεδομένου ενός συνόλου χρόνων που αντιπροσωπεύουν τη διάρκεια, μπορείτε να χρησιμοποιήσετε τις συναρτήσεις SUMPRODUCT και TIME. Στο παράδειγμα που εμφανίζεται, ο τύπος στο G5 είναι:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
όπου "φορές" είναι η ονομαζόμενη περιοχή C5: C14.
Εξήγηση
Αυτός ο τύπος χρησιμοποιεί τη συνάρτηση SUMPRODUCT για να συνοψίσει το αποτέλεσμα δύο εκφράσεων που δίνουν πίνακες. Ο στόχος είναι να αθροίσουμε μόνο χρόνο μεγαλύτερο από 30 λεπτά, το "πλεόνασμα" ή "επιπλέον" χρόνο. Η πρώτη έκφραση αφαιρεί 30 λεπτά από κάθε φορά στην ονομαζόμενη περιοχή "φορές":
times-TIME(0,30,0)
Αυτό έχει ως αποτέλεσμα έναν πίνακα όπως αυτό:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Η δεύτερη έκφραση είναι μια λογική δοκιμή για όλες τις ώρες μεγαλύτερες από 30 λεπτά:
times>TIME(0,30,0)
Αυτό δημιουργεί έναν πίνακα τιμών TRUE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Μέσα στο SUMPRODUCT, αυτοί οι δύο πίνακες πολλαπλασιάζονται μαζί για να δημιουργήσουν αυτόν τον πίνακα:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Παρατηρήστε ότι οι αρνητικές τιμές στον πρώτο πίνακα είναι τώρα μηδενικά. Κατά τον πολλαπλασιασμό, οι τιμές TRUE FALSE μετατρέπονται σε 1 και μηδέν, επομένως οι τιμές FALSE "ακυρώνουν" φορές που δεν υπερβαίνουν τα 30 λεπτά. Τέλος, το SUMPRODUCT επιστρέφει το άθροισμα όλων των τιμών στον πίνακα, 1 ώρα και 4 λεπτά (1:04).
Εναλλακτική λύση με SUMIFS και COUNTIFS
Από μόνη της, το SUMIFS δεν μπορεί να αθροίσει το δέλτα των τιμών του χρόνου μεγαλύτερο από 30 λεπτά. Τα SUMIFS και COUNTIFS μπορούν να χρησιμοποιηθούν μαζί για να έχουν το ίδιο αποτέλεσμα με το SUMPRODUCT παραπάνω:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Ώρες άνω των 24 ωρών
Εάν οι συνολικοί χρόνοι μπορεί να υπερβαίνουν τις 24 ώρες, χρησιμοποιήστε αυτήν τη μορφή προσαρμοσμένου χρόνου όπως αυτή:
(h):mm:ss
Η σύνταξη του τετραγώνου αγκυλών λέει στο Excel να μην "ανακυκλώνει" φορές μεγαλύτερες από 24 ώρες.
Με βοηθητική στήλη
Όπως φαίνεται στο παράδειγμα, μπορείτε επίσης να προσθέσετε μια βοηθητική στήλη για να υπολογίσετε και να αθροίσετε τις χρονικές διαγραφές. Ο τύπος στο D5, αντιγράφεται, είναι:
=MAX(C5-"00:30",0)
Εδώ, το MAX χρησιμοποιείται για να απαλλαγούμε από αρνητικά χρονικά δέλτα, που προκαλούνται από χρόνους στη στήλη Γ που είναι λιγότερο από 30 λεπτά. Παρατηρήστε ότι το αποτέλεσμα στο D15 είναι το ίδιο με το αποτέλεσμα στο G5.
