Hàm: Average, AverageIf, AverageIfs

 

1. Hướng dẫn dùng hàm Average Excel

Cú pháp hàm Average là =AVERAGE(number1,[number2],…).

Trong đó:

• number1: đối số thứ nhất có thể là số, tham chiếu ô hoặc phạm vi chứa số mà các bạn muốn tính trung bình, bắt buộc.
• number2: là các số, tham chiếu ô hoặc phạm vi chứa số bổ sung mà các bạn muốn tính trung bình tối đa 255 số, tùy chọn.

Người dùng cần lưu ý khi sử dụng:

• Hàm AVERAGE có tối đa 256 đối số, có thể là số, tên, phạm vi hoặc tham chiếu ô có chứa số.
• Đối số tham chiếu ô hoặc phạm vi có chứa giá trị logic, văn bản hay ô rỗng thì hàm tự động bỏ qua, riêng giá trị 0 sẽ được tính.
• Những giá trị logic, biểu thị số dạng văn bản khi nhập trực tiếp vào danh sách các đối số trong hàm sẽ được đếm.
• Nếu đối số là văn bản hay giá trị không thể chuyển đổi thành dạng số sẽ báo lỗi.

Chúng ta sẽ lấy ví dụ với bảng dữ liệu dưới đây, tính trung bình điểm số của từng học sinh.

Bước 1:

Tại ô điểm số trung bình đầu tiên cho học sinh đầu tiên, người dùng nhập công thức =AVERAGE(C2,D2,E2) rồi nhấn Enter.

Bước 2:

Kết quả sẽ hiển thị như hình dưới đây. Trong trường hợp kết quả ra số thập phân như hình và muốn làm tròn, bạn đọc tham khảo bài viết Các cách làm tròn số trên Excel.

Để tính trung bình cộng của những ô tiếp theo, người dùng chỉ cần trỏ chuột vào ô kết quả đầu tiên và dùng chuột kéo xuống các ô phía dưới.

2. Sửa lỗi #VALUE dùng hàm Average

Trong khi sử dụng hàm Average để tính trung bình cộng dữ liệu trên Excel, người dùng sẽ gặp một số lỗi phổ biến như báo lỗi #VALUE!. Lỗi này xảy ra khi đối số trong phép tính mà hàm Average không thể diễn giải các giá trị đó là giá trị số.

Với hình dưới đây thì ô F2 đang báo lỗi #VALUE! vì trong phép tính có ô E2 chứa giá trị #VALUE.

Để khắc phục lỗi #VALUE!, bạn cần dùng thêm 2 hàm IF và hàm ISERROR bỏ qua vùng tham chiếu chứa lỗi, để tính trung bình các giá trị bình thường còn lại.

Công thức hàm là =AVERAGE(IF(ISERROR(C2:E2),"",C2:E2)).

Do đây là công thức mảng nên người dùng cần nhập tổ hợp phím Ctrl + Shift + Enter để hiển thị 2 dấu ngoặc đầu và cuối hàm. Nếu không có dấu ngoặc kết quả vẫn sẽ báo lỗi.

Kết quả sẽ hiển thị như dưới đây. Ô E2 sẽ không được tính trong phép tính.

Trên đây là cách sử dụng hàm Average tính trung bình cộng trên Excel. Cách thực hiện rất đơn giản, chỉ cần khoanh vùng dữ liệu cần tính. Ngoài ra cách sửa lỗi #VALUE! trên có thể áp dụng cho lỗi #N/A, #NULL, #p/0!,… của hàm Average.

2. Cách dùng hàm AVERAGEIF trên Excel

Để tính trung bình cộng các số liệu trong bảng trên Excel, chúng ta sẽ sử dụng tới hàm AVERAGE, hàm tính trung bình cộng quen thuộc. Tuy nhiên, hầu như việc tính giá trị trung bình cộng trên Excel sẽ kèm theo nhiều điều kiện khác nhau. Và như vậy, bạn cần đến hàm AVERAGEIF tính trung bình cộng có điều kiện.

AVERAGEIF sẽ tiến hành tính trung bình cộng với những vùng dữ liệu được khoanh vùng có trong bảng, kèm theo đó là điều kiện cho trước mà người dùng thiết lập. Cách sử dụng hàm AVERAGEIF tính trung bình cộng có điều kiện trên Excel sẽ được Quản trị mạng hướng dẫn trong bài viết dưới đây, qua từng ví dụ cụ thể khác nhau.

• Đây là những hàm cơ bản nhất trong Excel mà bạn cần nắm rõ
• Quên mật khẩu bảo vệ của file Excel, bạn phải làm gì?

Cú pháp hàm AVERAGEIF trên Excel là: =AVERAGEIF(range, criteria, [average_range]).

Trong đó:

• Range là phạm vi cần tính giá trị trung bình có thể là số, tên, các tham chiếu, các mảng.
• Criteria là điều kiện dưới dạng số, biểu thức, tham chiếu ô hoặc văn bản để xác định tính giá trị trung bình. Điều kiện nằm trong dấu " ".
• Average_range bao gồm các ô cần tính giá trị trung bình. Nếu Average_range bỏ trống thì sẽ thay thế bằng Range để tiến hành tính giá trị trung bình.

Lưu ý khi dùng hàm AVERAGEIF:

• Những ô trống trong Average_range, báo TRUE, FALS hàm AVERAGEIF sẽ bỏ qua.
• Nếu Range không chứa giá trị hoặc chứa hàm chuỗi thì kết quả sẽ báo lỗi.
• Nếu Criteria trống thì hàm bằng 0.
• Nếu xác định không có giá trị nào phù hợp với điều kiện Criteria chúng ta cũng sẽ nhận được báo lỗi.
• Average_range không cần bắt buộc có kích thước như vùng phạm vi Range. Nếu không có, hàm AVERAGEIF sẽ xác định từ ô bên trái trên cùng làm ô đầu tiên rồi tương ứng khớp với phạm vi.

Chúng ta sẽ tiến hành tính điểm trung bình trong bảng dữ liệu dưới đây.

Ví dụ 1: Tính điểm số trung bình của những sinh viên có điểm số lớn hơn 5.

Bạn nhập công thức =AVERAGEIF(D2:D6,">5") rồi nhấn Enter.

Kết quả sẽ như hình dưới đây.

Ví dụ 2: Tính trung bình điểm số của sinh viên có điểm số nhỏ hơn 5.

Nhập công thức =AVERAGEIF(D2:D6,"<5") và nhấn Enter.

Kết quả cuối cùng là #DIV/0! do Excel dựa vào điều kiện người dùng thiết lập và xác định không có giá trị tương ứng với điều kiện đó, ở đây không có sinh viên nào dưới điểm 5.

Ví dụ 3: Tính trung bình điểm số của những sinh viên giới tính là Nam.

Ở đây bạn cần khoanh vùng 2 khu vực là Giới tính để lọc điều kiện và khu vực Điểm số để tính số điểm trung bình.

Cú pháp sử dụng ở đây là =AVERAGEIF(C2:C6,"Nam",D2:D6), nhấn Enter để thực hiện.

Kết quả cuối cùng sẽ như hình dưới đây.

Ví dụ 4: Tính trung bình điểm số của những sinh viên không có chữ Thị trong cột Họ và Tên.

Chúng ta sẽ có công thức =AVERAGEIF(B2:B6,"<>Thị",D2:D6) và nhấn Enter.

Kết quả tính trung bình cộng điểm số phân loại theo tên sẽ như hình dưới đây.

Trên đây là bài hướng dẫn cách sử dụng hàm AVERAGEIF với cú pháp sử dụng chung, và từng trường hợp cụ thể với những điều kiện khác nhau. Bạn cần xác định rõ khu vực cần tính cũng như nội dung điều kiện để áp dụng hàm AVERAGEIF sao cho phù hợp.

3. Hàm AVERAGEIFS

Trong Excel, hàm AVERAGEIFS để tính giá trị trung bình của những số trong một dải ô đáp ứng được cùng lúc nhiều điều kiện. Các tiêu chí được sử dụng cho AVERAGEIFS có thể bao gồm toán tử logic (>,<,<>,=) và toán tử đại diện (*,?).

Nếu bạn thắc mắc hàm AVERAGEIFS khác với AVERAGEIF như thế nào thì rất đơn giản, AVERAGEIF chỉ tính được giá trị trung bình của các ô đáp ứng 1 điều kiện duy nhất. Nếu có nhiều hơn 1 điều kiện, bạn phải sử dụng kết hợp nó với hàm khác, hoặc đơn giản hơn là sử dụng hàm AVERAGEIFS.

Trong phần tiếp theo, bài viết sẽ đưa ra công thức hàm AVERAGEIFS cùng các ví dụ cụ thể để bạn dễ nắm bắt hơn.

Công thức hàm AVERAGEIFS Excel

Hàm AVERAGEIFS trên Excel có công thức hàm là AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

Trong đó:

• average_range: Vùng dữ liệu cần tính giá trị trung bình cộng bao gồm số hoặc tên hoặc mảng tham chiếu có chứa số, là tham số bắt buộc.
• criteria_range1, criteria_range2: Phạm vi chứa điều kiện cần tính tổng, criteria_range1 là tham số bắt buộc, các criteria_range còn lại tùy chọn tối đa 127 đối số, là tham số bắt buộc.
• criteria1, criteria2, ...: Là điều kiện cần tính trung bình cộng, riteria1 là tham số bắt buộc, các criteria khác tùy chọn chứa tối đa 127 điều kiện.

Lưu ý khi dùng hàm:

• Nếu giá trị các đối số có chứa giá trị logic hoặc ô trống thì giá trị đó sẽ bỏ qua.
• Nếu phạm vi là ô trống hoặc dạng văn bản thì hàm trả về giá trị lỗi #DIV/0.
• Nếu các ô trong Criteria_range là ô trống, hàm mặc định bằng 0.
• Nếu giá trị trong ô dữ liệu là giá trị logic = True thì được coi là 1, nếu giá trị False được coi là 0.
• Nếu các giá trị trong average_range không thể chuyển thành số thì báo lỗi #DIV/0.
• Nếu không có giá trị nào đáp ứng tất cả các điều kiện thì hàm báo lỗi #DIV/0!.
• Có thể sử dụng các ký tự thay thế như *.?. trong điều kiện tính trung bình cộng.

Ví dụ cách dùng hàm AVERAGEIFS trong Excel

Chúng ta sẽ lấy ví dụ với bảng dữ liệu như dưới đây.

Ví dụ 1: Tính trung bình cộng tiền lương của nhân viên lớn hơn 3,000,000 và nhỏ hơn 7,000,000.

Từ yêu cầu đề bài, ta nhận thấy vùng cần tính trung bình cộng là D2 đến D6 (D2:D6), điều kiện để tính trung bình cộng là mức lương lớn hơn 3 triệu (D2:D6,">3000000") và nhỏ hơn 6 triệu (D2:D6,"<7000000"). Vậy công thức sẽ như sau:

=AVERAGEIFS(D2:D6,D2:D6,">3000000",D2:D6,"<7000000")

Nhấn Enter và kết quả trung bình cộng các dữ liệu dựa trên điều kiện đã thiết lập sẽ hiển thị. Số tiền lương trung bình tính được là 5,433,333.

Ví dụ 2: Tính trung bình cộng tiền lương thưởng của nhân viên Nam có mức Lương thưởng là 250,000.

Ta thấy, E2:E6 là vùng cần tính giá trị, E2:E6,"250000" là điều kiện thứ nhất cho giá trị tại vùng đó. C2:C6,"Nam" là điều kiện thứ 2 với vùng dữ liệu khác. Vậy thì công thức cho bài toán này sẽ là:

=AVERAGEIFS(E2:E6,E2:E6,"250000",C2:C6,"Nam")

Khi nhấn Enter sẽ ra kết quả là 250,000 như hình dưới đây.

Ví dụ 3: Tính trung bình cộng lương thưởng của nhân viên nam lớn hơn 200,000 và tiền lương lớn hơn 4,500,000.

Vùng dữ liệu cần tính giá trị trung bình là E2:E6 là, E2:E6,">200000" là điều kiện thứ 1 tại vùng dữ liệu E2:E6, C2:C6,"Nam" là điều kiện thứ 2 tại vùng dữ liệu C2:C6, D2:D6,">4500000" là điều kiện thứ 3 tại vùng dữ liệu D2:D6. Tại ô nhập kết quả chúng ta nhập công thức tính:

=AVERAGEIFS(E2:E6,E2:E6,">200000",C2:C6,"Nam",D2:D6,">4500000")

Nhấn Enter và kết quả sẽ là 375,000 như hình dưới đây. Vì chỉ có 2 nhân viên nam đạt đủ cả 3 điều kiện trong yêu cầu.

Ví dụ 4: Tính trung bình cộng lương nhân viên nữ có tiền lương lớn hơn 7,500,000.

Bạn cũng xác định vùng dữ liệu, điều kiện tương tự như 3 ví dụ trên và cuối cùng ta sẽ có công thức tính với hàm AVERAGEIFS là:

=AVERAGEIFS(D2:D6,D2:D6,">7500000",C2:C6,"Nữ") 

rồi nhấn Enter.

Kết quả sẽ báo lỗi #DIV0! do chỉ thỏa mãn 1 điều kiện trong công thức, không có ô nào thỏa mãn tất cả 2 điều kiện trong công thức.

Trên đây là cách áp dụng hàm AVERAGEIFS trong Excel để tính giá trị trung bình cộng có nhiều điều kiện khác nhau. Nhìn chung cách sử dụng rất đơn giản, bạn cần nhập vùng tính giá trị trước, sau đó là điều kiện trên từng vùng dữ liệu khác nhau.