Trong thế giới phân tích dữ liệu và báo cáo, Microsoft Excel là một công cụ không thể thiếu. Khả năng xử lý số liệu mạnh mẽ của nó giúp người dùng dễ dàng trích xuất thông tin có giá trị. Tuy nhiên, đôi khi việc tính toán trung bình không đơn giản là tìm giá trị trung bình của toàn bộ tập dữ liệu. Thay vào đó, bạn cần tính trung bình dựa trên một hoặc nhiều điều kiện cụ thể. Đây chính là lúc các hàm AVERAGEIF và AVERAGEIFS phát huy sức mạnh.
Hai hàm này cho phép bạn lọc và tính toán trung bình của một tập hợp dữ liệu, nhưng khác với hàm AVERAGE cơ bản ở chỗ chúng có thể bao gồm hoặc loại trừ các giá trị nhất định dựa trên các tiêu chí bạn thiết lập. Việc nắm vững cách sử dụng các hàm này sẽ nâng cao đáng kể kỹ năng xử lý dữ liệu của bạn, giúp bạn đưa ra các quyết định chính xác hơn từ bộ dữ liệu lớn.
Hàm AVERAGEIF Trong Excel: Tính Trung Bình Với Một Điều Kiện
Hàm AVERAGEIF của Excel cho phép bạn tính toán 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 do bạn đặt ra. Đây là lựa chọn hoàn hảo khi bạn chỉ cần áp dụng một tiêu chí lọc.
Cú pháp Hàm AVERAGEIF
Hàm AVERAGEIF có ba đối số như sau:
=AVERAGEIF(vung_xet_dieu_kien,dieu_kien,vung_tinh_trung_binh)Trong đó:
vung_xet_dieu_kien(bắt buộc): Là phạm vi các ô mà bạn muốn kiểm tra dựa trên tiêu chí.dieu_kien(bắt buộc): Là tiêu chí (phép thử cho đối sốvung_xet_dieu_kien). Tiêu chí này có thể là số, văn bản, biểu thức logic hoặc tham chiếu ô.vung_tinh_trung_binh(tùy chọn): Là phạm vi các ô chứa giá trị cần tính trung bình nếu điều kiện trong đối sốdieu_kienđược thỏa mãn. Nếu bạn bỏ qua đối số này, Excel sẽ tính trung bình các giá trị được xác định trongvung_xet_dieu_kien.
AVERAGEIF Hoạt Động Như Thế Nào Trong Thực Tế?
Hãy cùng đi vào một ví dụ thực tế để hiểu rõ hơn cách hàm AVERAGEIF hoạt động.
Giả sử bạn có một 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.
Bảng dữ liệu Excel ví dụ về tên, tuổi và mức lương của 12 người để tính trung bình với AVERAGEIF.
Trong trường hợp này, dựa vào cú pháp ở trên, cột B (Tuổi) chứa các giá trị bạn muốn kiểm tra (vung_xet_dieu_kien), tiêu chí “lớn hơn 40” (>40) là điều kiện (dieu_kien) cho các giá trị trong cột B, và cột C (Lương) chứa các giá trị bạn muốn tính trung bình (vung_tinh_trung_binh).
Vì vậy, trong một ô riêng biệt, bạn cần nhập công thức sau:
=AVERAGEIF(Table1[Age],">40",Table1[Salary])Và nhấn Enter.
Ví dụ áp dụng công thức AVERAGEIF trong Excel để tính trung bình lương của người trên 40 tuổi.
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 có thể nhận thấy là việc sử dụng tham chiếu có cấu trúc cho các đối số vung_xet_dieu_kien và vung_tinh_trung_binh. Thay vì sử dụng tham chiếu ô trực tiếp (chẳng hạn B2:B13 cho vung_xet_dieu_kien và C2:C13 cho vung_tinh_trung_binh), công thức tham chiếu đến tiêu đề cột. Điều này là do dữ liệu nằm 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 mới 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ố dieu_kien được đặt trong dấu ngoặc kép. Bất cứ khi nào bạn sử dụng các toán tử logic trong các 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 Dùng AVERAGEIF
Trước khi bạn áp dụng AVERAGEIF vào bảng tính của riêng mình, có một số điểm quan trọng bạn nên biết:
Đầu tiên, đối số dieu_kien (tiêu chí 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ố vung_xet_dieu_kien, có nhiều loại tiêu chí khác mà bạn có thể sử dụng:
| Loại Tiêu Chí | Ví Dụ Công Thức | Công Thức Sẽ Tính Toán Gì |
|---|---|---|
| Giá trị | =AVERAGEIF(Table1[Age],**44**,Table1[Salary]) |
Mức lương trung bình của những người 44 tuổi |
| Văn bản | =AVERAGEIF(Table1[Person],**"Jenny"**,Table1[Salary]) |
Mức lương trung bình của những người tên là Jenny |
| Ký tự đại diện | =AVERAGEIF(Table1[Person],**"Jo*"**,Table1[Salary]) |
Mức lương trung bình của bất kỳ ai có tên bắt đầu bằng “Jo-“ |
| Tham chiếu ô | =AVERAGEIF(Table1[Age],**B15**,Table1[Salary]) |
Mức lương trung bình của bất kỳ ai khớp với tiêu chí tuổi trong ô B15 |
| Tổ hợp | =AVERAGEIF(Table1[Person],**"<>Ja*"**,Table1[Salary]) |
Mức lương trung bình của bất kỳ ai có tên KHÔNG bắt đầu bằng “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 đó bị trống, nó sẽ bị bỏ qua trong tính toán trung bình. Tuy nhiên, nếu lương của ai đó là 0 đô la, giá trị này sẽ được bao gồm trong tính toán trung bình.
Cuối cùng, nếu không có ô nào trong phạm vi được chỉ định đáp ứng tiêu chí, Excel sẽ trả về lỗi #DIV/0! để cho bạn biết rằng không thể tính toán giá trị trung bình.
Hàm AVERAGEIFS Trong Excel: Tính Trung Bình Với Nhiều Điều Kiện
Trong khi AVERAGEIF kiểm tra một điều kiện duy nhất trước khi tính trung bình tất cả các giá trị đáp ứng điều kiện đó, 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 Hàm 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 biệt đáng kể so với hàm AVERAGEIF:
=AVERAGEIFS(vung_tinh_trung_binh,vung_dieu_kien_1,dieu_kien_1,vung_dieu_kien_2,dieu_kien_2,...)Trong đó:
vung_tinh_trung_binh(bắt buộc): Là phạm vi các ô chứa giá trị cần được tính trung bình.vung_dieu_kien_1(bắt buộc): Là phạm vi các ô chứa tập hợp giá trị đầu tiên cần được kiểm tra.dieu_kien_1(bắt buộc): Là tiêu chí chovung_dieu_kien_1.vung_dieu_kien_2(tùy chọn): Là phạm vi các ô chứa tập hợp giá trị thứ hai cần được kiểm tra.dieu_kien_2(bắt buộc nếuvung_dieu_kien_2được bao gồm): Là tiêu chí chovung_dieu_kien_2.
Nói cách khác, cú pháp trên đại diện cho hàm AVERAGEIFS được sử dụng để tạo hai phép thử (cho điều kiện 1 và điều kiện 2), mặc dù bạn có thể bao gồm tối đa 127 phép thử (cặp vung_dieu_kien và dieu_kien) trong tổng số.
AVERAGEIFS Hoạt Động Như Thế Nào Trong Thực Tế?
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 khi bạn xem cách hàm này hoạt động trong một 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 mức lương trung bình của tất cả nam giới trên 35 tuổi.
Bảng dữ liệu Excel minh họa tên, tuổi, giới tính và lương của 12 người cho ví dụ hàm AVERAGEIFS.
Vì có hai tiêu chí (tuổi và giới tính), bạn cần sử dụng hàm AVERAGEIFS:
=AVERAGEIFS(Table2[Salary],Table2[Age],">35",Table2[Gender],"M")Trong đó:
Table2[Salary]là phạm vi các ô chứa giá trị cần được tính trung bình (đối sốvung_tinh_trung_binh).Table2[Age]là phạm vi các ô chứa tập hợp giá trị đầu tiên cần được kiểm tra (đối sốvung_dieu_kien_1).">35"là tiêu chí cho phạm vi đầu tiên (đối sốdieu_kien_1).Table2[Gender]là phạm vi các ô chứa tập hợp giá trị thứ hai cần được kiểm tra (đối sốvung_dieu_kien_2)."M"là tiêu chí cho phạm vi thứ hai (đối sốdieu_kien_2).
Công thức AVERAGEIFS trong Excel tính lương trung bình của nam giới trên 35 tuổi với hai điều kiện.
Những Điều Cần Lưu ý Khi Dùng AVERAGEIFS
Dưới đây là một số điều cần nhớ khi sử dụng hàm AVERAGEIFS:
- Các tiêu chí kiểm tra có thể là các biểu thức logic, giá trị số, văn bản, tham chiếu ô, hoặc sự kết hợp của chúng.
- Các biểu thức logic và văn bản phải luôn được đặt trong dấu ngoặc kép.
- Chỉ những ô đáp ứng tất cả các điều kiện mới được bao gồm trong tính toán 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 (số hàng và số cột).
- Hàm AVERAGEIFS sẽ bỏ qua các ô trống nhưng sẽ bao gồm các ô có giá trị là 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í đã đặt ra.
Kết luận
Hàm AVERAGEIF và AVERAGEIFS là những công cụ mạnh mẽ, không thể thiếu trong bộ kỹ năng Excel của bất kỳ ai làm việc với dữ liệu. Trong khi AVERAGEIF đơn giản và hiệu quả cho các tình huống chỉ có một điều kiện, AVERAGEIFS mở ra khả năng phân tích phức tạp hơn với nhiều tiêu chí cùng lúc. Việc nắm vững cách sử dụng các hàm này sẽ giúp bạn trích xuất thông tin chính xác và có giá trị từ các bảng tính của mình, hỗ trợ đắc lực trong việc đưa ra các quyết định dựa trên dữ liệu.
Ngoài AVERAGEIF và AVERAGEIFS, Excel còn có các hàm khác để tính 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 hợp các số, văn bản và đối số logic. Tùy thuộc vào yêu cầu phân tích, bạn có thể lựa chọn hàm phù hợp nhất để tối ưu hóa công việc của mình. Hãy luyện tập để thành thạo các hàm này và khai thác tối đa sức mạnh của Excel!
