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 2 năm 2020.

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)=2020)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2020)1))

Công thức giả định các 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 2 năm 2020, 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)=2020),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 >1/31/20     <3/1/20

Bảng tiêu chí cho biết rằng 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 1/31/20 và ngày nhỏ hơn 3/1/20. Đâ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à lọc dữ liệu của bạn (hiển thị tab Dữ liệu của ruy-băng và nhấp vào công cụ Bộ lọc). Bấm vào mũi tên lọc ở đầu cột A, sau đó chọn Bộ lọc Ngày | 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 1/31/20 và nhỏ hơn 3/1/20. Khi bạn nhấp vào OK, chỉ những bản ghi đó trong tháng 2 năm 2020 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ị đó.

Tuy nhiên, có lẽ cách tiếp cận đơn giản nhất là sử dụng hàm AVERAGEIFS. Nó cho phép bạn tính toán trung bình của một số giá trị tùy thuộc vào việc một giá trị được liên kết (ngày, trong trường hợp này) có đáp ứng các tiêu chí bạn chỉ định hay không. Đây là cách công thức trông:

=AVERAGEIFS(B2:B1000,A2:A1000,">31 Jan 2020",A2:A1000,"<01 Mar 2020")

Lưu ý rằng đây không phải là công thức mảng và bạn không cần xác định bảng tiêu chí cho tiêu chí của mình — chúng được xây dựng trực tiếp vào công thức.

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

Mẹo này (10671) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: