Hướng dẫn tính trung bình tuổi theo năm/tháng/ngày trên Excel

Một ví dụ trực quan cho tình huống xuất hiện yêu cầu này chính là: Ta có một danh sinh viên với ngày sinh và tuổi của họ trên Excel. Làm cách nào để ta có thể tính tuổi trung bình của họ theo tháng hoặc năm. Ví dụ như những người sinh vào tháng 9 có tuổi trung bình là bao nhiêu. Và giả sử phải tính giá trị trung bình theo một ngày xác định trong Excel phải giải quyết như nào. 

Trong bài này Gitiho.com sẽ cung cấp cho bạn giải pháp nhanh, gọn và đơn giản nhất.

Dùng công thức mảng để tính trung bình theo Năm / Tháng trong Excel

Ví dụ bạn có một bảng dữ liệu như hình minh họa bên dưới. Bạn có thể tính tuổi trung bình theo năm hoặc theo tháng bằng công thức mảng trên Excel một cách nhanh chóng.

Tính trung bình theo tháng: Chọn một ô trống bên cạnh bảng, như trong ví dụ này là ô F2, nhập công thức sau vào ô trống đó:

=SUM((MONTH(B2:B15)=12)*C2:C15)/SUM(IF(MONTH(B2:B15)=12,1))
 

Tiếp theo nhấn đông thời tổ hợp phím Ctrl + Shift + Enter để nhận kết quả.

Chú ý: Trong công thức này, phạm vi B2:B15 là cột ngày sinh, C2:C15 là cột tuổi, 12 là tháng xác định, ở đây chúng ta tính tháng 12 và bạn có thể thay đổi nó dựa theo nhu cầu của mình.

Tính trung bình tuổi theo năm: Chọn một ô trống bên cạnh bảng, như trong minh họa này là ô F4, nhập công thức sau vào ô trống đó:

=SUM((YEAR(B2:B15)=1990)*C2:C15)/SUM(IF(YEAR(B2:B15)=1990,1))

Tiếp theo nhấn đông thời tổ hợp phím Ctrl + Shift + Enter để nhận kết quả.

Chú ý: Trong công thức này, phạm vi B2:B15 la cột ngày sinh, C2:C15 là cột tuổi, 1990 là năm xác định mà ta muốn tính trung bình theo đó. Tất nhiên bạn hoàn toàn có thể thay đổi nó theo nhu cầu của mình cần cho tính toán.

Dùng bộ Lọc để tính trung bình tuổi theo Năm / Tháng trên Excel

Trong phần này chúng ta vẫn dùng bảng dữ liệu như bên dưới, và ta có thể lọc cột ngày sinh đầu tiên. Sau đó tính tuổi trung bình theo năm hoặc tháng sau khi có kết quả lọc bằng hàm Subtotal trong Excel.

Bước 1: Chọn cột Ngày sinh và nhấn chọn Data, chọn Filter.

Bước 2: Nhấn vào nút tam giác ở đầu cột Ngày sinh vừa mới xuất hiện. Sau đó một danh sách tùy chọn sẽ được thả xuống, chọn  Date Filters > All Dates in the Period > September (hoặc bất kỳ tháng nào mà bạn muốn tính tuổi trung bình theo nó). Giống như hình minh họa dưới đây:

Chú ý: Nếu bạn muốn tính tuổi trung bình theo một năm xác định thì hãy nhấn vào nút tam giác đầu cột Ngày sinh. Sau đó bỏ tích Select All đi và tích vào ô năm sinh mà bạn muốn tính trung bình theo đó. Nhấn nút OK. Giống như hình dưới này:

Bước 3: Bây giờ bộ lọc đã giúp chúng ta lọc tất cả các kết quả theo năm hoặc tháng xác định. Giờ hãy chọn bất kỳ ô trống nào (như trong này ta chọn ô C18) và nhập công thức sau vào đó:

=SUBTOTAL(1,C3:C9)

Trong đó C3:C9 có nghĩa là tất cả các giá trị tuổi được lọc trong ô tuổi).

Nhấn nút Enter để lấy kết quả.

Tính trung bình tuổi theo ngày trong Excel

Thỉnh thoảng chúng ta cần tính trung bình theo một ngày được cho. Như trong ví dụ này, ta có bảng hoa quả như hình dưới. Và ta cần tính trung bình sản lượng đến ngày 2014-04-04. Để giải quyết tình huống ta sẽ làm như sau.

Chọn một ô trống, nhập công thức sau vào đó:

=AVERAGEIF(J2:J24,P2,M2:M24)

Nhấn nút Enter để nhận kết quả. Tới đây bạn đã có giá trị trung bình theo ngày xác định.

Chú ý: Trong công thức =AVERAGEIF(J2:J24,P2,M2:M24) thì J2:J24 là cột ngày, P2 là ô chứa ngày mà ta cần tính giá trị trung bình đến đó. M2:M24 là cột sản lượng, nơi mà ta sẽ tính giá trị trung bình và bạn có thể thay đổi căn cứ này theo nhu cầu của mình.