Excel, công cụ bảng tính mạnh mẽ, không chỉ giúp chúng ta tổ chức dữ liệu mà còn cung cấp vô số hàm để phân tích và tính toán. Trong số đó, các hàm tính trung bình là những công cụ cơ bản nhất. Tuy nhiên, nếu hàm AVERAGE đơn thuần chỉ tính trung bình cộng của một tập hợp số, thì AVERAGEIF và AVERAGEIFS lại vượt trội hơn hẳn nhờ khả năng tính trung bình dựa trên một hoặc nhiều điều kiện cụ thể. Điều này giúp người dùng dễ dàng lọc và phân tích dữ liệu một cách linh hoạt, chính xác và hiệu quả hơn rất nhiều so với phương pháp thủ công, đặc biệt khi làm việc với các tập dữ liệu lớn.
Bài viết này của thichcongnghe.net sẽ đi sâu vào cách sử dụng hai hàm quan trọng này, cung cấp cú pháp, ví dụ thực tế và những lưu ý cần thiết để bạn có thể làm chủ việc tính trung bình có điều kiện trong Excel.
Hàm AVERAGEIF Trong Excel: Tính Trung Bình Với Một Điều Kiện
Hàm AVERAGEIF trong 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 mà bạn thiết lập. Điều này cực kỳ hữu ích khi bạn muốn tính toán một giá trị trung bình cụ thể, ví dụ như lương trung bình của nhân viên có độ tuổi nhất định, hay doanh số trung bình của các sản phẩm thuộc một danh mục cụ thể.
Cú pháp hàm AVERAGEIF
Hàm AVERAGEIF có ba đối số, với cú pháp như sau:
=AVERAGEIF(phạm_vi_kiểm_tra, điều_kiện, [phạm_vi_tính_trung_bình])Trong đó:
phạm_vi_kiểm_tra(bắt buộc): Là dải ô mà bạn muốn kiểm tra điều kiện.điều_kiện(bắt buộc): Là tiêu chí mà bạn muốn áp dụng chophạm_vi_kiểm_tra. Đây có thể là một số, một biểu thức, một tham chiếu ô, hoặc một chuỗi văn bản.phạm_vi_tính_trung_bình(tùy chọn): Là dải ô chứa các giá trị mà bạn muốn tính trung bình nếu điều kiện được đáp ứng. Nếu bạn bỏ qua đối số này, Excel sẽ sử dụng các giá trị trongphạm_vi_kiểm_trađể tính trung bình.
AVERAGEIF qua ví dụ thực tế
Hãy cùng đi thẳng 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 dữ liệu Excel chứa tên, tuổi và mức lương của 12 người. Nhiệm vụ của bạn là tính toán 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ới danh sách người, tuổi và mức lương, dùng cho ví dụ hàm AVERAGEIF.
Trong trường hợp này, dựa theo cú pháp trên, cột “Tuổi” (Age) chứa các giá trị bạn muốn kiểm tra (phạm_vi_kiểm_tra), điều kiện là “lớn hơn 40” (>40) cho các giá trị trong cột “Tuổi”, và cột “Lương” (Salary) chứa các giá trị bạn muốn tính trung bình (phạm_vi_tính_trung_bình).
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])Sau đó nhấn Enter.
Ví dụ công thức AVERAGEIF trong Excel để tính lương trung bình của nhân viên 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ểm đầ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 (structured references) cho các đối số phạm_vi_kiểm_tra và phạm_vi_tính_trung_bình. Thay vì sử dụng các tham chiếu ô trực tiếp (chẳng hạn như B2:B13 cho cột “Age” và C2:C13 cho cột “Salary”), công thức tham chiếu đến tên cột của bảng. Điều này là do dữ liệu được chứa trong một bảng Excel được định dạng sẵn (formatted Excel table), và chương trình mặc định sử dụng tên tiêu đề bảng trong 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 đó mà không cần chỉnh sửa.
Thứ hai, đối số điều_kiện (">40") đượ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ô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 lưu ý quan trọng khi sử dụng AVERAGEIF
Trước khi bạn áp dụng hàm AVERAGEIF vào bảng tính của riêng mình, có một số điểm quan trọng bạn cần lưu ý.
Đầu tiên, đối số điều_kiện rất linh hoạt. Mặc dù ví dụ trên sử dụng ">40" (một toán tử logic) để kiểm tra phạm vi trong đối số phạm_vi_kiểm_tra, nhưng 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ụ | Hàm này sẽ tính toán |
|---|---|---|
| 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 |
| Kết hợp các loại trên | =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 tính đến các ô trống. Ví dụ, nếu giá trị lương của một người nào đó bị 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 một người nào đó 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 điều kiện, Excel sẽ trả về lỗi #DIV/0! để thông báo rằng nó 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 của 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ả của mình hơn nữa bằng cách sử dụng nhiều tiêu chí đồng thời. Đây là điểm khác biệt cốt lõi và làm cho AVERAGEIFS mạnh mẽ hơn khi bạn cần phân tích dữ liệu phức tạp hơn với các điều kiện chồng chéo.
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. Cú pháp chung như sau:
=AVERAGEIFS(phạm_vi_tính_trung_bình, phạm_vi_kiểm_tra1, điều_kiện1, [phạm_vi_kiểm_tra2, điều_kiện2], ...)Trong đó:
phạm_vi_tính_trung_bình(bắt buộc): Là dải ô chứa các giá trị mà bạn muốn tính trung bình. Lưu ý: Đối số này đứng đầu tiên, khác vớiAVERAGEIF.phạm_vi_kiểm_tra1(bắt buộc): Là dải ô chứa các giá trị cho điều kiện đầu tiên.điều_kiện1(bắt buộc): Là tiêu chí chophạm_vi_kiểm_tra1.phạm_vi_kiểm_tra2,điều_kiện2, … (tùy chọn): Là các cặp phạm vi và điều kiện bổ sung. Bạn có thể bao gồm tối đa 127 cặp điều kiện trong một công thứcAVERAGEIFS.
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 điều kiện (điều_kiện1 và điều_kiện2), mặc dù bạn có thể bao gồm tổng cộng tới 127 điều kiện.
AVERAGEIFS qua ví dụ 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 rất nhiều 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. Mục tiêu của bạn là tính toán 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 bao gồm tên, tuổi, giới tính và lương, minh họa 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à dải ô chứa các giá trị cần tính trung bình (phạm_vi_tính_trung_bình).Table2[Age]là dải ô chứa tập hợp giá trị đầu tiên cần kiểm tra (phạm_vi_kiểm_tra1).">35"là điều kiện cho phạm vi đầu tiên (điều_kiện1).Table2[Gender]là dải ô chứa tập hợp giá trị thứ hai cần kiểm tra (phạm_vi_kiểm_tra2)."M"là điều kiện cho phạm vi thứ hai (điều_kiện2).
Công thức AVERAGEIFS trong Excel tính lương trung bình của nam giới trên 35 tuổi.
Những lưu ý quan trọng khi sử dụng AVERAGEIFS
Dưới đây là một số điều cần nhớ khi sử dụng hàm AVERAGEIFS:
- Các điều kiện có thể là các toán tử logic, giá trị số, văn bản, tham chiếu ô, hoặc kết hợp các loại này.
- Các toán tử 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. Nếu một ô chỉ đáp ứng một phần các điều kiện, nó sẽ bị bỏ qua.
- Tất cả các dải ô được chỉ định trong một công thức
AVERAGEIFS(phạm vi tính trung bình và tất cả các phạm vi kiểm tra) phải có cùng kích thước (số hàng và cột). Nếu không, Excel sẽ báo lỗi. - Hàm
AVERAGEIFSbỏ qua 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í đã đặt ra.
Kết luận
Hàm AVERAGEIF và AVERAGEIFS là những công cụ vô cùng mạnh mẽ trong Excel, giúp bạn phân tích dữ liệu một cách linh hoạt và chính xác hơn bằng cách tính toán giá trị trung bình dựa trên một hoặc nhiều điều kiện. 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, cho phép bạn trích xuất thông tin hữu ích từ các bảng tính phức tạp một cách nhanh chóng.
Cho dù bạn là sinh viên, nhân viên văn phòng hay chuyên gia dữ liệu, việc áp dụng AVERAGEIF và AVERAGEIFS vào công việc hàng ngày sẽ giúp bạn tiết kiệm thời gian và đưa ra các quyết định dựa trên dữ liệu hiệu quả hơn.
Ngoài AVERAGEIF và AVERAGEIFS, Excel còn có các hàm khác để tính trung bình một tập hợp dữ liệu. Ví dụ, hàm AVERAGE đơn thuần tính trung bình cộng số học của một tập hợp số, 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à các đối số logic. Hãy tiếp tục khám phá các hàm này để tối ưu hóa khả năng làm việc với Excel của bạn!
Bạn đã thử dùng các hàm này trong công việc của mình chưa? Hãy chia sẻ kinh nghiệm hoặc bất kỳ câu hỏi nào của bạn trong phần bình luận bên dưới nhé!
