Admin Edu đã có bài viết về hàm AVERAGE để tính trung bình cộng trong Excel. Tuy nhiên, khi làm việc với dữ liệu Excel, ngoài công thức tính trung bình cộng đơn giản, nhiều lúc người dùng cần tính trung bình có điều kiện, tính giá trị trung bình của n giá trị lớn nhất hay tính bình quân gia quyền có trọng số. Để thực hiện các công việc trên, bạn có thể sử dụng hàm các hàm trung bình có điều kiện AVERAGEIF, AVERAGEIFS và các hàm SUM, SUMPRODUCT kết hợp các hàm khác.

1. Các công thức tính trung bình cộng trong Excel không dùng hàm AVERAGE

Để tính trung bình cộng của các giá trị cho trước, ngoài việc sử dụng hàm AVERAGE, bạn có thể áp dụng hàm SUM để cộng các giá trị và chia cho số lượng giá trị cần tính trung bình.

Ví dụ dưới đây áp dụng khi số lượng giá trị cần tính trung bình cộng đã được biết trước và đã được cố định (nếu có ô trống thì giá trị trung bình vẫn chia đều cho cả ô trống)

=SUM(C3:E3)/3

Nếu bạn muốn bỏ qua ô trống, hãy kết hợp hàm SUM tính tổng các ô và thực hiện phép chia cho hàm COUNT đếm số lượng ô có chứa các số:

=SUM(B4:E4)/COUNT(B4:E4)

2. Hàm trung bình có điều kiện trong Excel: AVERAGEIF và AVERAGEIFS

2.1. Hàm trung bình AVERAGEIF tính trung bình với một tiêu chí duy nhất

Cách hoạt động tương tự như hàm COUNTIF và SUMIF, hàm AVERAGEIF trả về giá trị trung bình cộng của tất cả các ô trong một phạm vi đáp ứng tiêu chí đưa ra:

Ví dụ dưới đây tính điểm trung bình cộng của các sản phẩm thuộc nhóm Alpha và nhóm Beta theo công thức hàm trung bình có điều kiện:

2.2. Hàm trung bình AVERAGEIFS tính trung bình với nhiều điều kiện

Để tính số trung bình dựa trên nhiều tiêu chí, bạn có thể sử dụng hàm AVERAGEIFS:

Trong ví dụ dưới đây, ô G5 tính doanh số trung bình của Sản phẩm SP1 ở Khu vực Hà Nội. Như vậy, có 2 tiêu chí cần xác định là Sản phẩm và Khu vực. Công thức trong ô G5 được diễn giải như sau:

• Average_range: dải ô $B$5:$B$13 chứa các giá trị cần tính trung bình cộng.
• Range1: dải ô $A$5:$A$13 tương ứng tiêu chí 1 về Tên sản phẩm.
• Criteria1: ô E5 (“SP1”) là tiêu chí 1.
• Range2: dải ô C$5:C$13 tương ứng tiêu chí 2 về Khu vực.
• Criteria2: ô F5 (“Hà Nội”) là tiêu chí 2.

3. Tính trung bình TOP N giá trị lớn nhất/ nhỏ nhất.

3.1. Hàm trung bình AVERAGE kết hợp hàm LARGE tính trung bình TOP N giá trị lớn nhất

Để tính trung bình của n giá trị hàng đầu, bạn có thể kết hợp hàm AVERAGE với hàm LARGE.

Hàm LARGE(array,k) trả về giá trị thứ k từ trên xuống của một tập hợp các số.

Ví dụ:

• Hàm LARGE (array, 5) sẽ trả về giá trị cao thứ 5.
• Hàm LARGE(array, {1,2,3}) trả về 3 giá trị lớn nhất.
• Tương tự, với top 5 lớn nhất, sử dụng công thức LARGE(array, {1,2,3,4,5})

Trong ví dụ dưới đây, công thức trong cột I tính trung bình của top 3 điểm hàng đầu trong mỗi hàng:

=AVERAGE(LARGE(phạm vi cần chọn top 3 tính trung bình, {1,2,3}))

3.2. Hàm trung bình AVERAGE và hàm SMALL tính trung bình TOP N giá trị nhỏ nhất.

Ngược lại với hàm LARGE, bạn có thể kết hợp hàm SMALL với hàm AVERAGE để tính trung bình của n giá trị cuối với cú pháp tương tự.

Ví dụ, để tính trung bình top 5 nhỏ nhất, công thức hàm trung bình kết hợp như sau:

=AVERAGE(SMALL(phạm vi cần chọn top 5 tính trung bình, {1,2,3,4,5}))

4. Sử dụng hàm SUMPRODUCT để tính toán trung bình có trọng số

Công thức tính bình quân gia quyền:

• Nếu trọng số ở định dạng phần trăm: =SUMPRODUCT(mảng giá trị, mảng trọng số)
• Nếu trọng số không ở định dạng phần trăm mà là giá trị số thì lệnh tính trung bình cộng là =SUMPRODUCT(mảng giá trị, mảng trọng số)/ SUM(trọng số)

Ví dụ dưới đây tính điểm tổng kết học phần khi có các điểm thành phần môn học và trọng số điểm tương ứng:

Cùng tham khảo thêm video dưới đây để biết thêm về cách sử dụng các hàm trung bình trong Excel nhé:

Trên đây là các ví dụ về các hàm trung bình có điều kiện, hàm trung bình có trọng số trong Excel. Hi vọng bài viết của Admin Edu đã mang lại cho các bạn các thông tin hữu ích về các hàm trung bình trong Excel.