Hoctin.vn đã 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.
Trong bài viết này, hoctin.vn sẽ hướng dẫn các bạn cách sử dụng và ứng dụng của các hàm trung bình trong Excel 2007, 2010, 2013, 2016 và 2019.
Mục Lục Bài Viết
1, Các Công Thức Tính Trung Bình Cộng Trong Excel Không Dùng Hàm Trung Bình 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 1 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:
=AVERAGEIF(range, criteria, [average_range])
Trong đó:
Range: phạm vi của các ô mà theo đó các tiêu chí được đánh giá, có thể là số, văn bản, mảng hoặc tham chiếu có chứa số.
Criteria: Tiêu chí ở dạng số, biểu thức, biểu thức logic, tham chiếu ô hoặc văn bản. Ví dụ, các tiêu chí có thể được biểu thị dưới dạng như 10, A1*10, “>10”, B2 hoặc “nhóm A”.
Average_range: các ô cần tính giá trị trung bình.
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:
=AVERAGEIFS(average_range, range1, criteria1, range2, criteria2,…)
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ố.
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ố)
Chi tiết đã được giới thiệu ở bài viết: Hàm SUMPRODUCT trong Excel.
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:
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 hoctin.vn đã 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.
Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm Excel: