Cách dùng hàm tính trung bình có điều kiện trong Excel dễ hiểu nhất

Ứng dụng tính giá trị trung bình có điều kiện trong Excel    

Việc áp dụng các hàm tính trung bình có điều kiện trong Excel có vai trò rất lớn trong việc hỗ trợ người dùng tính toán giá trị trung bình. Với những người thường xuyên phải làm việc với số liệu trong Excel sẽ có lúc cần phải tính giá trị trung bình cho 1 số trường hợp phức tạp. 

Ứng dụng của hàm cũng được dùng rộng rãi trong mọi khía cạnh của cuộc sống. Một số ví dụ có thể kể đến như: 

• Tính giá trị trung bình thành tích thể dục của các bạn nam trong lớp. 

• Tính giá trị trung bình số kg mỗi loại hoa quả bán được. 

• Tính điểm trung bình của các học sinh giỏi (có điểm trung bình >8,0 và điểm toán >6,5) trong học kỳ 1. 

Nếu không biết sử dụng hàm tính trung bình có nhiều điều kiện thì bạn sẽ phải tính thủ công bằng cách chọn hay lọc từng trường riêng rồi sau đó tính trung bình. Việc làm đó sẽ khiến bạn phải lặp lại quá trình lọc, liệt kê và áp dụng công thức nhiều lần rất mất thời gian và dễ sai sót số liệu. Vậy hãy cùng xem có những cách tính trung bình cộng có điều kiện trong Excel nào nhé!

Hàm tính trung bình có điều kiện trong Excel

Trong Excel hiện có rất nhiều hàm tính toán có điều kiện để giảm thao tác và rút ngắn thời gian thực hiện cho người dùng. Một số hàm được sử dụng phổ biến như SUMIF, COUNTIF, AVERAGEIF.

Sử dụng hàm SUMIF và COUNTIF

Để có thể tính giá trị trung bình có điều kiện trong Excel bằng hàm SUMIF và COUNTIF, trước hết bạn cần hiểu về cách hoạt động của 2 hàm này đã!

Hàm SUMIF là hàm tính tổng nhiều giá trị thỏa mãn trong một phạm vi điều kiện. 

Công thức: =SUMIF(range, criteria, [sum_range])

Trong đó: 

• Range: là phạm vi vùng muốn đánh giá theo điều kiện. 

• Criteria: là điều kiện đưa ra để tính tổng và thường là số hoặc chữ. 

• Sum_range: là vùng các ô dữ liệu cần tính tổng. 

Ví dụ: Bạn cần tính tổng thành tiền của hóa chất có số lượng bán được >200. 

Nhập công thức =SUMIF(D2:D10, ">200", E2:E10) vào ô muốn nhận kết quả. Sau đó, nhấn Enter để cho ra kết quả. 

Trong đó: 

• D2:D10 là vùng giữ liệu cần đánh giá với điều kiện đưa ra. 

• >200 là điều kiện cần so sánh

Hàm COUNTIF là hàm đếm có điều kiện sử dụng trong Excel. Bạn có thể sử dụng hàm này để đếm số lần xuất hiện của 1 giá trị (là số, chữ), số giá trị thỏa mãn điều kiện…

Công thức: =COUNTIF(range, criteria)

Trong đó: 

• Range: là vùng cần đếm. 

• Criteria: là điều kiện đưa ra, và phải để trong ngoặc kép, không phân biệt chữ hoa và chữ thường.

Ví dụ: Tìm kiếm số lượng mặt hàng là dưa hấu trong bảng thống kê hàng hóa. 

Công thức tính: =COUNTIF(B2:B8, "dưa hấu") và nhấn Enter. Kết quả đếm ra có 2 giá trị là dưa hấu. 

Sau khi bạn đã nắm được cách hoạt động của 2 hàm này thì việc bạn cần làm là kết hợp 2 hàm này lại với nhau bằng cách: chia tổng được tính bằng hàm SUMIF cho số lượng được đếm bằng hàm COUNTIF để tính trung bình có điều kiện.

Có thể tóm gọn lại cách làm trên bằng công thức sau: 

=SUMIF(range, criteria, sum_range) / COUNTIF(range, criteria)

Sử dụng hàm AVERAGEIF

AVERAGEIF là hàm tính trung bình cộng có điều kiện trong Excel phổ biến nhất. Hàm này sẽ tính trung bình cộng những dữ liệu thỏa mãn điều kiện mà người dùng thiết lập. 

Công thức: =AVERAGEIF(range, criteria, [average_range]).

Trong đó: 

• Range (bắt buộc) là phạm vi một hoặc nhiều ô cần tính giá trị trung bình và bao gồm chữ, số, mảng, tham chiếu có chứa số. 

• Criteria (bắt buộc): điều kiện để lấy các số để tính giá trị trung bình bao gồm chữ, số, mảng, tham chiếu có chứa số. 

• Average_range (tùy chọn): vùng dữ liệu được chọn để tính giá trị trung bình. 

Lưu ý: 

• Nếu các ô trong vùng dữ liệu được chọn có chứa TRUE hoặc FALSE thì sẽ bị bỏ qua. 

• Nếu ô trong vùng dữ liệu tính giá trị trung bình là ô trống thì hàm AVERAGEIF sẽ bỏ qua. 

• Nếu vùng dữ liệu average_range là giá trị trống hoặc dạng văn bản thì hàm sẽ báo lỗi #DIV0!. 

• Nếu một ô trong vùng điều kiện bỏ trống thì hàm AVERAGEIF sẽ coi nó có giá trị 0. 

• Nếu không có ô nào đáp ứng được điều kiện thì kết quả trả về là #DIV0!.

• Có thể sử dụng các ký hiệu đại diện như dấu chấm hỏi (?) khớp với bất kỳ ký tự đơn nào, dấu sao (*) khớp với bất kỳ chuỗi ký tự nào. 

Ví dụ cụ thể: Tính tiền lương trung bình của những nhân viên có tiền lương >5.000.000 VNĐ. 

Công thức tính: =AVERAGEIF(D2:D7;">5.000.000") và nhấn Enter sẽ được kết quả. 

Trong đó: 

• D2:D7 là vùng dữ liệu cần tính trung bình cộng. 

• ">5.000.000" là điều kiện chọn giá trị tính trung bình

Hàm AVERAGEIFS tính trung bình có nhiều điều kiện trong Excel    

AVERAGEIFS là hàm tính trung bình có nhiều điều kiện trong Excel. Hàm này sẽ tính tổng trung bình của tất cả các ô thỏa mãn điều kiện do người dùng đưa ra.

Công thức: =AVERAGEIFS(average_range,criteria_range1,criteria1,…)

Trong đó: 

• Average_range (bắt buộc) vùng dữ liệu được chọn để tính giá trị trung bình

• Criteria_range1 (bắt buộc), các criteria_range tiếp theo (tùy chọn) phạm vi 1 đến 127: vùng dữ liệu cần so sánh với điều kiện để lấy các số tính giá trị trung bình. 

• Criteria1 (bắt buộc), các criteria tiếp theo (tùy chọn) phạm vi 1 đến 127 điều kiện dạng số, biểu thức, tham chiếu ô hoặc văn bản.

Lưu ý: 

• Nếu average_range là giá trị trống hoặc văn bản thì kết quả phép tính sẽ báo lỗi #DIV0!. 

• Nếu một ô trong vùng tiêu chí bị bỏ trống thì AVERAGEIFS sẽ xem ô đó có giá trị 0. 

• Các ô trong vùng dữ liệu là TRUE thì có giá trị 1 còn nếu là FALSE có giá trị 0. 

• Chỉ tính trung bình cộng các giá trị ở vùng average_range thỏa mãn điều kiện. 

• Nếu các giá trị trong vùng average_range không thể chuyển thành số thì kết quả sẽ báo lỗi #DIV0!. 

• Nếu không có ô nào đáp ứng được đủ các điều kiện thì kết quả trả về báo lỗi #DIV/0!. 

• Người dùng có thể sử dụng các ký tự như * và ? trong điều kiện của hàm tính trung bình cộng.

Ví dụ 1: Tính trung bình với 2 điều kiện là số và chữ: Tính điểm trung bình của học sinh với điều kiện là học sinh nam và điểm HK2 lớn hơn 7. 

Bước 1: Dùng công thức: =AVERAGEIFS(E2:E7;C2:C7;"Nam";E2:E7;">7") 

Trong đó: 

• E2:E7 là vùng điểm HK2 cần tính trung bình 

• C2:C7;"Nam" là vùng xét điều kiện 1 (cột giới tính) và điều kiện đầu tiên (học sinh là nam). 

• E2:E7;">7" là vùng xét điều kiện 2 (cột điểm HK2) và điều kiện thứ 2 (điểm HK2>7). 

Bước 2: Nhấn Enter để cho ra kết quả hàm AVERAGEIFS

Giải thích kết quả: Theo bảng sẽ lọc ra được học sinh vừa là nam vừa đó điểm trung bình HK2 >7 là Lê Văn C, Huỳnh Văn D, Nguyễn Văn M. Khi đó, sẽ lấy điểm của 3 bạn này để tính trung bình và được kết quả như hình. 

Ví dụ 2: So sánh tính hữu dụng của hàm AVERAGEIFS. 

Bạn có bảng thống kê mặt hàng hoa quả bán được hằng ngày. Bây giờ bạn chỉ muốn tính số Cam trung bình bán được. 

Khi chưa áp dụng: Đầu tiên bạn cần lọc mặt hàng Cam riêng bằng cách sử dụng hàm IF. Sau đó, nếu ô đó đúng là Cam thì giá trị sẽ được giữ nguyên, còn nếu là loại khác thì giá trị trả về rỗng. Tiếp đến, bạn lại sử dụng hàm AVERAGE để tính giá trị trung bình các kết quả vừa rồi. Nếu bảng có nhiều giá trị, mặt hàng thì sẽ tốn rất nhiều thời gian để tính toán.

Khi áp dụng: Thay vì phải sử dụng 2 công thức trên và phải dùng cho nhiều ô thì bạn chỉ cần sử dụng hàm AVERAGEIF để tính giá trị trung bình các ô thỏa mãn điều kiện là “cam”. 

Kết luận    

Trên đây, Thủ Thuật Số đã hướng dẫn bạn sử dụng hàm tính trung bình có điều kiện trong Excel một cách chi tiết nhất. Hy vọng với những thông tin mà chúng tôi cung cấp bạn có thể tính điểm trung bình trong Excel có điều kiện một cách nhanh chóng và dễ dàng hơn.