Công thức chung
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Tóm lược
Để tính thời gian phản hồi trung bình theo tháng, bạn có thể sử dụng công thức dựa trên hàm AVERAGEIFS, cùng với hàm EOMONTH.
Trong ví dụ được hiển thị, công thức trong G5 là:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Giải trình
Công thức này sử dụng phạm vi được đặt tên là "ngày" (B5: B25) và "thời lượng" (D5: D25). Thời lượng cột D tính bằng phút, được tính bằng cách trừ ngày mở cửa và ngày đóng cửa.
Hàm AVERAGEIFS được thiết kế để xác định phạm vi trung bình dựa trên nhiều tiêu chí. Trong trường hợp này, chúng tôi định cấu hình AVERAGEIFS thành khoảng thời gian trung bình theo tháng bằng cách sử dụng hai tiêu chí: (1) ngày khớp lớn hơn hoặc bằng ngày đầu tiên của tháng, (2) ngày khớp nhỏ hơn hoặc bằng ngày cuối cùng của tháng.
Để ngoặc các ngày theo tháng, chúng tôi sử dụng một thủ thuật đơn giản để giúp mọi thứ dễ dàng hơn: Trong cột F, thay vì nhập tên tháng ("Jan", "Feb", Mar ", v.v.), chúng tôi thêm ngày thực tế cho đầu tiên của mỗi tháng (1/1/2016, 2/1/2016, 3/1/2016, v.v.). Sau đó, chúng tôi sử dụng định dạng ngày tùy chỉnh ("mmm") để hiển thị tên tháng.
Điều này làm cho việc xây dựng tiêu chí chúng ta cần cho AVERAGEIFS dễ dàng hơn nhiều bằng cách sử dụng các giá trị trong cột F. Để khớp các ngày lớn hơn hoặc bằng ngày đầu tiên của tháng, chúng tôi sử dụng:
">="&F5
Để khớp các ngày nhỏ hơn hoặc bằng ngày cuối cùng của tháng, chúng tôi sử dụng:
"<="&EOMONTH(F5,0)
Chúng tôi lấy EOMONTH để trả về ngày cuối cùng của cùng tháng bằng cách cung cấp số 0 cho đối số tháng .
Lưu ý: việc nối với dấu và (&) là cần thiết khi xây dựng tiêu chí dựa trên tham chiếu ô.
Giải pháp Pivot Table
Bảng tổng hợp là một giải pháp tuyệt vời khi bạn cần tóm tắt hoặc dữ liệu trung bình theo năm, tháng, quý, v.v., vì bảng tổng hợp cung cấp các điều khiển tự động nhóm các ngày. Để có so sánh song song giữa các công thức và bảng tổng hợp, hãy xem video này: Tại sao lại sử dụng bảng tổng hợp.
