George có một trang tính bao gồm ngày tháng (trong cột A) và các giá trị được liên kết với những ngày đó (trong cột B). Bảng tính bao gồm các giá trị trong vài năm qua. Anh ta muốn tính giá trị trung bình của tất cả các giá trị cho một tháng nhất định trong một năm nhất định. Ví dụ, George muốn tính giá trị trung bình của tất cả các giá trị cho tháng 5 năm 2011.

Có một số cách khác nhau để tiếp cận vấn đề này. Một cách là tạo PivotTable dựa trên dữ liệu của bạn. (PivotTables rất tuyệt vời để tổng hợp và phân tích lượng dữ liệu khổng lồ.) Bạn có thể dễ dàng đặt trường giá trị thành Trung bình (thay vì Tổng mặc định) và nhóm cột Ngày theo bất kỳ thứ gì bạn muốn.

Nếu bạn không muốn sử dụng PivotTable, có bất kỳ số lượng công thức nào bạn có thể thêm vào trang tính của mình. Ví dụ: công thức sau sử dụng hàm SUMPRODUCT để tính giá trị trung bình:

=SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2011)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2011)1))

Công thức giả định ngày và giá trị của bạn bắt đầu từ hàng 2 (để cho phép các tiêu đề) và không vượt qua hàng 1000. Nếu không có ngày nào trong dữ liệu thuộc tháng 5 năm 2011, thì công thức trả về lỗi #DIV / 0!

lỗi.

Một cách tiếp cận khác là sử dụng công thức mảng, chẳng hạn như sau:

=AVERAGE(IF((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2011),B2:B1000))

Cách tiếp cận này ngắn hơn so với công thức SUMPRODUCT, nhưng bạn phải nhớ giữ Ctrl + Shift + Enter khi nhập công thức.

Bạn cũng sẽ nhận được lỗi chia cho 0 nếu không có dữ liệu cho tháng và năm mong muốn.

Vẫn còn một cách tiếp cận khác là sử dụng một trong các hàm cơ sở dữ liệu của Excel, DAVERAGE. Tất cả những gì bạn cần làm là thiết lập một bảng tiêu chí xác định những gì bạn đang tìm kiếm. Ví dụ, giả sử rằng các tiêu đề trên các cột là một cái gì đó nguyên bản, như Ngày (ô A1) và Giá trị (ô B1).

Bạn có thể thiết lập bảng tiêu chí ở một nơi khác, chẳng hạn như D1: E2. Bảng có thể trông như thế này:

Date         Date >4/30/11     <6/1/11

Bảng tiêu chí cho biết bạn muốn DAVERAGE sử dụng bất kỳ thứ gì trong đó cột Ngày chứa ngày lớn hơn 30/04/11 và ngày nhỏ hơn 6/1/11. Đây là công thức:

=DAVERAGE(A1:B1000,"Value",D1:E2)

Tham số đầu tiên xác định cơ sở dữ liệu của bạn, tham số thứ hai cho biết rằng bạn muốn tính trung bình thông tin trong cột Giá trị (cột B) và tham số thứ ba cho DAVERAGE biết vị trí của bảng tiêu chí của bạn.

Một cách khá dễ dàng là áp dụng tính năng lọc ngày tháng và sử dụng hàm SUBTOTAL. Nhập công thức sau vào một ô:

=SUBTOTAL(101,B2:B1000)

Chọn một ô trong phạm vi dữ liệu của bạn và bật AutoFilter (chọn Data | Filter | AutoFilter). Nhấp vào mũi tên lọc ở đầu cột A và sau đó chọn Bộ lọc tùy chỉnh từ danh sách thả xuống. Excel sẽ hiển thị hộp thoại Bộ lọc Tự động Tùy chỉnh. (Xem Hình 1.)

image

Hình 1. Hộp thoại Custom AutoFilter.

Sử dụng các điều khiển trong hộp thoại để chỉ định rằng bạn muốn các bản ghi lớn hơn 4/30/11 và nhỏ hơn 6/1/11. Khi bạn nhấp vào OK, chỉ những bản ghi trong tháng 5 năm 2011 mới được hiển thị và công thức tổng phụ hiển thị giá trị trung bình của những bản ghi hiển thị đó.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (10670) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: