Công thức Excel: Trung bình theo tháng
Công thức Excel: Trung bình theo tháng

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 “số tiền” (D5: D104) và “ngày tháng” (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 cách sử dụng hai tiêu chí: (1) ngày đối sánh lớn hơn hoặc bằng Đầu tiên ngày trong tháng, (2) ngày đối sánh nhỏ hơn hoặc bằng Cuối cùng Ngày trong 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 cách sử dụ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 mà 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 để làm 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ới bảng tổng hợp, hãy xem video này: Tại sao lại có bảng tổng hợp.

https://exceljet.net/formula/average-by-month