Cách tính trung bình cộng trong Excel?

[Nguyễn Hoàng]

Các hàm AVERAGEIF và AVERAGEIF của Excel cho phép bạn tìm giá trị trung bình của một tập dữ liệu. Tuy nhiên, điểm khác biệt của chúng so với hàm AVERAGE đơn giản hơn là khả năng bao gồm hoặc loại trừ một số giá trị nhất định trong phép tính.

Cách sử dụng hàm AVERAGEIF và AVERAGEIFS trong Excel như thế nào?​

Hàm AVERAGEIF của Excel cho phép bạn tính giá trị trung bình của một tập dữ liệu đã được lọc dựa trên một điều kiện duy nhất mà bạn đặt.

Cú pháp AVERAGEIF​

Hàm AVERAGEIF có ba đối số:

=AVERAGEIF( x , y , z )

Trong đó:

• x (bắt buộc) là phạm vi các ô để kiểm tra theo tiêu chí,
• y (bắt buộc) là tiêu chí (kiểm tra đối số x ) và
• z (tùy chọn) là phạm vi ô để tính trung bình nếu đáp ứng được bài kiểm tra trong đối số y . Nếu bạn bỏ z ra, Excel sẽ tính trung bình các giá trị được xác định trong đối số x .

Hàm AVERAGEIF hoạt động thế nào?​

Chúng ta hãy cùng xem hàm AVERAGEIF hoạt động như thế nào trong ví dụ thực tế.

Giả sử bạn có bảng Excel chứa tên, tuổi và mức lương của 12 người và bạn được yêu cầu tính mức lương trung bình của những người trên 40 tuổi.
Trong trường hợp này, khi nghĩ lại cú pháp ở trên, cột B chứa các giá trị bạn muốn kiểm tra (đối số x ), tiêu chí (đối số y ) cho các giá trị trong cột B lớn hơn 40 và cột C chứa các giá trị bạn muốn tính trung bình (đối số z ).

Vì vậy, trong một ô riêng biệt, bạn cần nhập:

=AVERAGEIF(Bảng1[Tuổi],">40",Bảng1[Lương])

và nhấn Enter.

Hãy dành chút thời gian để phân tích công thức này.

Điều đầu tiên bạn sẽ nhận thấy là việc sử dụng tham chiếu có cấu trúc cho các đối số x và z. Nói cách khác, thay vì sử dụng tham chiếu ô trực tiếp (chẳng hạn như B2:B13 cho đối số x và C2:C13 cho đối số z ), công thức sẽ tham chiếu đến tiêu đề cột. Điều này là do dữ liệu được chứa trong một bảng Excel được định dạng và chương trình mặc định sử dụng tiêu đề bảng trong các công thức. Điều này có nghĩa là nếu bạn thêm các hàng bổ sung vào cuối dữ liệu, công thức sẽ tự động bao gồm các giá trị mới đó.

Thứ hai, đối số y được chứa trong dấu ngoặc kép. Bất cứ khi nào bạn sử dụng toán tử logic trong công thức Excel—như bằng, lớn hơn hoặc nhỏ hơn—chúng phải được đặt trong dấu ngoặc kép.

Những điều cần lưu ý khi sử dụng AVERAGEIF​

Trước khi tiếp tục sử dụng AVERAGEIF trong bảng tính của mình, bạn cần lưu ý một số điểm quan trọng.

Đầu tiên, đối số y (bài kiểm tra) rất linh hoạt. Trong khi ví dụ trên sử dụng ">40" (một toán tử logic) để kiểm tra phạm vi trong đối số x , thì có nhiều loại tiêu chí khác mà bạn có thể sử dụng thay thế:

Loại tiêu chí	Công thức ví dụ	Điều này sẽ tính toán những gì
Giá trị	=AVERAGEIF(Bảng1[Tuổi], 44 , Bảng1[Lương])	Mức lương trung bình của những người ở độ tuổi 44
Chữ	=AVERAGEIF(Bảng1[Người], "Jenny" , Bảng1[Lương])	Mức lương trung bình của những người có tên là Jenny
Ký tự đại diện	=AVERAGEIF(Bảng1[Người], "Jo*" , Bảng1[Lương])	Mức lương trung bình của bất kỳ ai có tên bắt đầu bằng chữ Jo-
Tham chiếu ô	=AVERAGEIF(Bảng1[Tuổi], B15 , Bảng1[Lương])	Mức lương trung bình của bất kỳ ai phù hợp với tiêu chí độ tuổi trong ô B15
Sự kết hợp của những điều trên	=AVERAGEIF(Bảng1[Người], "Ja*" , Bảng1[Lương])	Mức lương trung bình của bất kỳ ai có tên không bắt đầu bằng chữ Ja-

Thứ hai, hàm AVERAGEIF không xem xét các ô trống. Ví dụ, nếu giá trị lương của ai đó là trống, nó sẽ bị bỏ qua trong phép tính trung bình. Tuy nhiên, nếu lương của ai đó là $0, nó sẽ được bao gồm trong phép tính trung bình.

Cuối cùng, nếu không có ô nào được chỉ định đáp ứng được bài kiểm tra, Excel sẽ trả về lỗi #DIV/0! để cho bạn biết rằng nó không thể tính giá trị trung bình.

Cách sử dụng AVERAGEIFS trong Excel​

Trong khi AVERAGEIF kiểm tra một điều kiện trước khi tính toán giá trị trung bình của tất cả các giá trị đáp ứng được phép thử, AVERAGEIFS cho phép bạn thu hẹp kết quả hơn nữa bằng cách sử dụng nhiều tiêu chí.

Cú pháp AVERAGEIFS​

Điều quan trọng cần lưu ý là thứ tự và số lượng đối số trong hàm AVERAGEIFS khác đáng kể so với hàm AVERAGEIF:

=AVERAGEIFS( x , y¹ , y² , z¹ , z² , ...)

Trong đó:

• x (bắt buộc) là phạm vi các ô chứa các giá trị cần tính trung bình,
• y¹ (bắt buộc) là phạm vi các ô chứa các giá trị đầu tiên cần kiểm tra,
• y ² (bắt buộc) là phép thử cho y¹ ,
• z¹ (tùy chọn) là phạm vi các ô chứa giá trị thứ hai cần kiểm tra và
• z² (bắt buộc nếu bao gồm z¹ ) là phép thử cho z¹ .

Nói cách khác, cú pháp trên biểu diễn hàm AVERAGEIFS được sử dụng để tạo hai bài kiểm tra ( y và z ), mặc dù bạn có thể bao gồm tối đa 127 bài kiểm tra.

Hàm AVERAGEIFS hoạt động thế nào?​

Nếu cú pháp AVERAGEIFS khiến bạn bối rối, mọi thứ sẽ trở nên rõ ràng hơn nhiều khi bạn xem cách hàm hoạt động trong ví dụ.

Bảng Excel này chứa tên, độ tuổi, giới tính và mức lương của mọi người và mục tiêu của bạn là tính ra mức lương trung bình của tất cả nam giới trên 35 tuổi.

Vì có hai tiêu chí (tuổi và giới tính) nên bạn cần sử dụng hàm AVERAGEIFS:

=AVERAGEIFS(Bảng2[Lương],Bảng2[Tuổi],">35",Bảng2[Giới tính],"M")

Trong đó:

• Bảng 2 [Lương] là phạm vi các ô chứa các giá trị cần tính trung bình (đối số x ),
• Bảng 2[Tuổi] là phạm vi các ô chứa tập giá trị đầu tiên cần kiểm tra ( đối số y¹ ),
• ">35" là phép thử cho phạm vi đầu tiên ( đối số y² ),
• Bảng 2[Giới tính] là phạm vi các ô chứa tập giá trị thứ hai cần được kiểm tra ( đối số z¹ ) và
• "M" là phép thử cho phạm vi thứ hai ( đối số z² ).

​

Những điều cần lưu ý khi sử dụng AVERAGEIFS​

Sau đây là một số điều cần nhớ khi sử dụng hàm AVERAGEIFS:

• Các bài kiểm tra có thể là các đối số logic, giá trị, văn bản, tham chiếu ô hoặc kết hợp các yếu tố này.
• Các lập luận logic và văn bản luôn phải được đặt trong dấu ngoặc kép.
• Chỉ những ô đáp ứng đủ mọi điều kiện mới được đưa vào phép tính trung bình.
• Tất cả các phạm vi được chỉ định trong công thức AVERAGEIFS phải có cùng kích thước.
• Hàm AVERAGEIFS loại bỏ các ô trống nhưng bao gồm các ô có giá trị bằng 0.
• Lỗi #DIV/0! sẽ được trả về nếu không có ô nào đáp ứng tất cả các tiêu chí.

AVERAGEIF và AVERAGEIFS không phải là những hàm duy nhất trong Excel tính toán giá trị trung bình của một tập dữ liệu. Ví dụ, hàm AVERAGE tạo ra giá trị trung bình số học đơn giản của một tập dữ liệu và hàm AVERAGEA trả về giá trị trung bình của một tập số, văn bản và đối số logic.

Theo Howtogeek