Công thức chung
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Tóm lược
Để tính trung bình theo tháng, bạn có thể sử dụng công thức dựa trên hàm AVERAGEIFS, với sự trợ giúp từ hàm EOMONTH. Trong ví dụ được hiển thị, công thức trong F4 là:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Công thức này sử dụng phạm vi được đặt tên là "số tiền" (D5: D104) và "ngày" (C5: C104).
Giải trình
Hàm AVERAGEIFS có thể trung bình các phạm vi 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 số tiền trung bình theo tháng bằng hai tiêu chí: (1) ngày đối sánh lớn hơn hoặc bằng ngày đầu tiên của tháng, (2) ngày đối sánh nhỏ hơn hoặc bằng ngày cuối cùng của tháng. Nếu chúng tôi mã hóa ngày tháng 1 năm 2016 vào công thức bằng hàm DATE, nó sẽ giống như thế này.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Nhưng chúng tôi không muốn mã hóa ngày tháng, chúng tôi muốn Excel tạo ra những ngày này cho chúng tôi. Thông thường, đây là một khó khăn, bởi vì nếu bạn thêm tên tháng dưới dạng văn bản (ví dụ: "Tháng Giêng", "Tháng Hai", "Tháng Ba", v.v.) trong cột F, bạn phải gặp thêm rắc rối để tạo ngày bạn có thể sử dụng cho tiêu chí .
Tuy nhiên, trong trường hợp này, 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, chúng tôi thêm ngày thực tế cho ngày đầu tiên của mỗi tháng (1/1/2016, 2/1/2016, 3 / 1/2016, v.v.) và sử dụng định dạng ngày tùy chỉnh ("mmm") để hiển thị tên tháng.
Điều này giúp bạn dễ dàng xây dựng các tiêu chí chúng ta cần cho AVERAGEIFS. Để đối sánh 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:
">="&E4
Và để 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(E4,0)
EOMONTH tự động trả về ngày cuối cùng của tháng vì chúng tôi 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 dữ liệu 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 theo 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.
