Tính trung bình theo tháng trong excel
Công thức chung
= AVERAGEIFS ( giá trị , ngày , “> =” & A1 , ngày , “=”>
Giải trình
Để trung bình theo tháng, bạn có thể sử dụng một công thức dựa trên chức năng AVERAGEIFS, với sự trợ giúp từ chức năng EOMONTH.
Trong ví dụ được hiển thị, công thức trong F4 là:
= AVERAGEIFS ( số tiền , ngày tháng , “> =” & F5 , ngày , “=”>
Công thức này sử dụng các phạm vi có tên “số tiền” (D5: D104) và “ngày tháng” (C5: C104).
Công thức này hoạt động như thế nào
Chức năng AVERAGEIFS có thể có 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 sẽ định cấu hình AVERAGEIFS thành số trung bình theo tháng sử dụng hai tiêu chí: (1) các ngày phù hợp với ngày đầu tiên của tháng, (2) các ngày trùng khớp ít hơn hoặc bằng ngày cuối cùng của tháng. Nếu chúng tôi lập ngày cứng cho tháng 1 năm 2016 vào công thức sử dụng chức năng DATE, nó sẽ như thế này.
= AVERAGEIFS ( số tiền , ngày tháng , “> =” & DATE ( 2016 , 1 , 1 ), ngày , “=”>
Nhưng chúng tôi không muốn ngày mã cứ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 nỗi đau, bởi vì nếu bạn thêm tên tháng làm văn bản (ví dụ: “Tháng Giêng”, “Tháng Hai”, “Tháng Ba“, v.v.) trong cột E, bạn phải gặp rắc rối thêm để tạo ngày bạn có thể sử dụng cho các tiêu chí .
Tuy nhiên, trong trường hợp này, chúng tôi sử dụng một mẹo đơn giản để làm cho mọi thứ trở nên dễ dàng hơn: Trong cột E, thay vì nhập tên tháng, chúng tôi sẽ thêm ngày thực tế vào đầu tháng 1 (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í cần cho AVERAGEIFS. Để khớp với 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 ngày ít hơn hoặc bằng ngày cuối cùng của tháng, chúng tôi sử dụng:
“=”>
EOMONTH tự động trả về ngày cuối cùng của tháng đó vì chúng tôi cung cấp số không cho đối số tháng.
Lưu ý: nối với một ký hiệu (&) là cần thiết khi xây dựng tiêu chí dựa trên một tham chiếu ô.
Giải pháp bảng trụ
Một pivot table là một giải pháp tuyệt vời khi bạn cần phải tóm tắt dữ liệu theo năm, tháng, quý, và như vậy, vì các bảng tổng hợp cung cấp điều khiển cho nhóm tự động theo ngày. Để so sánh các công thức với bảng xoay vòng, bạn có thể xem video này: Tại sao phải xoay bàn .