Công thức chung
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Tóm lược
Để tính tổng lượng thời gian trên 30 phút, với một tập hợp thời gian thể hiện thời lượng, bạn có thể sử dụng hàm SUMPRODUCT và TIME. Trong ví dụ được hiển thị, công thức trong G5 là:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
trong đó "lần" là phạm vi được đặt tên C5: C14.
Giải trình
Công thức này sử dụng hàm SUMPRODUCT để tính tổng kết quả của hai biểu thức mang lại mảng. Mục đích là chỉ tính tổng thời gian lớn hơn 30 phút, thời gian "dư" hoặc "thêm". Biểu thức đầu tiên trừ đi 30 phút cho mọi thời điểm trong phạm vi "lần" được đặt tên:
times-TIME(0,30,0)
Điều này dẫn đến một mảng như thế này:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Biểu thức thứ hai là một bài kiểm tra logic cho tất cả các lần lớn hơn 30 phút:
times>TIME(0,30,0)
Điều này tạo ra một mảng các giá trị TRUE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Bên trong SUMPRODUCT, hai mảng này được nhân với nhau để tạo ra mảng này:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Lưu ý các giá trị âm trong mảng đầu tiên bây giờ là số không. Trong quá trình nhân, các giá trị TRUE FALSE được chuyển đổi thành 1 và 0, do đó, các giá trị FALSE "hủy bỏ" thời gian không lớn hơn 30 phút. Cuối cùng, hàm SUMPRODUCT trả về tổng của tất cả các giá trị trong mảng, 1 giờ 4 phút (1:04).
Thay thế với SUMIFS và COUNTIFS
Tự nó, SUMIFS không thể tính tổng các giá trị thời gian lớn hơn 30 phút. SUMIFS và COUNTIFS có thể được sử dụng cùng nhau để có được kết quả tương tự như SUMPRODUCT ở trên:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Thời gian hơn 24 giờ
Nếu tổng thời gian có thể vượt quá 24 giờ, hãy sử dụng định dạng thời gian tùy chỉnh như sau:
(h):mm:ss
Cú pháp dấu ngoặc vuông cho biết Excel không "lặp lại" thời gian quá 24 giờ.
Với một cột trợ giúp
Như được hiển thị trong ví dụ, bạn cũng có thể thêm một cột trợ giúp để tính toán và tính tổng các delta thời gian. Công thức trong D5, được sao chép xuống, là:
=MAX(C5-"00:30",0)
Ở đây, MAX được sử dụng để loại bỏ các delta thời gian âm do thời gian trong cột C ít hơn 30 phút. Chú ý kết quả trong D15 giống với kết quả trong G5.
