Ví dụ, bạn có một bảng như sau ảnh chụp màn hình được hiển thị. Bạn có thể tính tuổi trung bình theo năm hoặc tháng với các công thức mảng một cách nhanh chóng trong Excel.

Tuổi trung bình theo năm: Chọn một ô trống bên cạnh bảng, cho biết Ô F4, nhập công thức =SUM((YEAR(B2:B15)=1990)*C2:C15)/SUM(IF(YEAR(B2:B15)=1990,1)) vào nó và nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi chìa khóa cùng một lúc.

Lưu ý: Trong công thức này, B2: B15 là cột Sinh nhật, C2: C15 là cột Tuổi, 1990 là năm cụ thể bạn muốn lấy trung bình và bạn có thể thay đổi chúng dựa trên nhu cầu của mình.

Công thức quá phức tạp để nhớ? Lưu công thức dưới dạng mục nhập Văn bản Tự động để sử dụng lại chỉ với một cú nhấp chuột trong tương lai! Đọc thêm ...     Dùng thử miễn phí

Dễ dàng tính tuổi theo ngày tháng năm sinh (ngày sinh) nhất định trong Excel

Kutools cho Excel's thu thập nhiều công thức thường được sử dụng để người dùng Excel dễ dàng tính toán mà không cần nhớ các công thức, bao gồm Tính tuổi dựa trên ngày sinhy công thức.

Trong bảng ví dụ của chúng tôi như ảnh chụp màn hình sau được hiển thị, trước tiên chúng tôi cũng có thể lọc cột ngày sinh, sau đó tính trung bình theo năm hoặc tháng sau khi lọc bằng hàm Subtotal trong Excel.

1. Chọn Cột sinh nhật và nhấp vào Ngày > Lọc.

2. Nhấp chuột  bên cạnh tiêu đề cột của cột Sinh nhật, sau đó trong danh sách thả xuống, hãy nhấp vào Bộ lọc ngày > Tất cả các ngày trong khoảng thời gian > Tháng Chín (hoặc bất kỳ tháng nào khác mà bạn muốn tính trung bình). Xem ảnh chụp màn hình bên dưới:

Chú thích: Nếu bạn muốn tính tuổi trung bình theo năm cụ thể, bạn cần nhấp vào  bên cạnh tiêu đề cột của cột Sinh nhật, sau đó bỏ chọn tất cả các năm ngoại trừ năm bạn sẽ tính trung bình và nhấp vào OK cái nút. Xem ảnh chụp màn hình bên dưới:

3. Bây giờ độ tuổi được lọc theo năm hoặc tháng cụ thể. Chọn bất kỳ ô trống nào, cho biết Ô C42, công thức = SUBTOTAL (1, C3: C9) (C3: C9 có nghĩa là tất cả các độ tuổi được lọc ra trong cột Độ tuổi) và nhấn đăng ký hạng mục thi Chìa khóa.

Đôi khi, bạn có thể được yêu cầu tính giá trị trung bình với ngày nhất định. Ví dụ: bạn có một bảng như màn hình sau được hiển thị và bạn cần tính trung bình của Số tiền xảy ra vào ngày 2014 tháng 04 năm 04, bạn có thể giải quyết nó như sau:

Chọn một ô trống, nhập công thức =AVERAGEIF(J2:J24,P2,M2:M24) vào nó và nhấn đăng ký hạng mục thi Chìa khóa. Sau đó, bạn sẽ nhận được giá trị trung bình của ngày được chỉ định.

Chú thích: Trong công thức = AVERAGEIF (J2: J24, P2, M2: M24), J2: J24 là cột ngày, P2 là ô tham chiếu với ngày bạn sẽ tính trung bình, M2: M24 là cột số tiền mà bạn sẽ tính trung bình và bạn có thể thay đổi chúng dựa trên nhu cầu của mình.

Nếu bạn cần tính tuổi trung bình của mỗi tháng (từ tháng XNUMX đến tháng XNUMX) hoặc mỗi năm trong Excel, thì việc áp dụng công thức mảng phức tạp có thể tốn rất nhiều thời gian. Ở đây tôi sẽ giới thiệu phần bổ trợ thứ ba của Kutools cho Excel để giải quyết nó.

1. Chọn cột ngày mà bạn sẽ tính giá trị trung bình và nhấp vào Kutools > Định dạng > Áp dụng định dạng ngày.

2. Trong hộp thoại Mở Áp dụng Định dạng Ngày, hãy chọn một định dạng ngày chỉ hiển thị tên tháng trong Định dạng ngày tháng hộp, chẳng hạn như "Tháng Ba"Hoặc"Tháng Ba"và nhấp vào Ok cái nút. Xem ảnh chụp màn hình:

3. Tiếp tục chọn cột ngày và nhấp vào Kutools > Thực tế. Tiện ích này sẽ chuyển đổi ngày thành văn bản của tên tháng.

Thực tế là một trong những tiện ích của Kutools cho Excel và nó có thể giúp bạn thay thế giá trị thực (hiển thị trong thanh công thức, chẳng hạn như công thức = A1 + B1) với giá trị hiển thị (mà bạn thấy trong ô đã chọn, chẳng hạn như kết quả công thức 10). 

4. Chọn phạm vi mà bạn sẽ tính trung bình theo tháng / năm và nhấp vào Kutools > Nội dung > Các hàng kết hợp nâng cao.

Các hàng kết hợp nâng cao là một trong những tiện ích Kutools cho Excel và nó có thể giúp bạn kết hợp / hợp nhất các hàng dựa trên cùng một giá trị trong một cột và sau đó thực hiện một số phép tính cho các cột khác, chẳng hạn như tổng, đếm, trung bình, tối đa, v.v. 

5. Trong hộp thoại Mở Kết hợp Hàng Dựa trên Cột, hãy chỉ định cài đặt như ảnh chụp màn hình ở trên:

1. Bấm vào cột Tên, rồi bấm vào Kết hợp > Dấu phẩy;
2. Bấm vào cột Sinh nhật, sau đó bấm vào Khóa chính.
3. Bấm vào cột Tuổi, sau đó bấm vào Tính toán > Trung bình.

6. Nhấn vào Ok cái nút. Sau đó, bạn sẽ thấy tuổi trung bình của mỗi tháng được tính với việc liệt kê các tên tương ứng. Xem ảnh chụp màn hình bên dưới:

Trung bình mỗi ngày / tháng / quý / giờ với bảng tổng hợp trong Excel

Dấu thời gian trung bình trong ngày trong Excel

Trung bình hàng tuần trong Excel