Có thể bạn quan tâm: Tính Số Tuần Và Số Ngày Lẻ Trong Excel – Hướng Dẫn Chi Tiết
Giới thiệu
Trong công việc quản lý dữ liệu, phân tích báo cáo và đưa ra quyết định kinh doanh, việc tính toán các thống kê cơ bản là điều bắt buộc. Một trong những chỉ số thống kê quan trọng nhất là trung bình – giá trị đại diện cho mức trung bình của một tập hợp dữ liệu. Tuy nhiên, trong thực tế, chúng ta thường gặp phải các tình huống mà chỉ muốn tính trung bình của một phần dữ liệu đáp ứng một điều kiện cụ thể. Đó chính là tính trung bình có điều kiện (Conditional Average). Trong bài viết này, chúng ta sẽ đi sâu vào các công thức, phương pháp thực hiện trong Excel, cùng với những ví dụ thực tế và mẹo nhỏ giúp bạn tránh những lỗi thường gặp.
Có thể bạn quan tâm: Cách Đánh Dấu Tích Vào Ô Vuông Trong Excel 2026
Nên dùng tính trung bình có điều kiện khi nào?
Trước khi đi vào công thức, hãy xác định khi nào bạn cần dùng tính trung bình có điều kiện:
- Phân tích dữ liệu phần: Bạn muốn biết trung bình của một nhóm khách hàng có giá trị mua hàng trên một mức nhất định.
- So sánh nhóm: Tính trung bình doanh thu của các cửa hàng trong một vùng miền cụ thể.
- Kiểm tra hiệu suất: Tính trung bình điểm số của sinh viên đạt điểm tối thiểu.
- Đánh giá dự án: Tính trung bình thời gian hoàn thành các công việc trong nhóm, nhưng chỉ xét những công việc đã hoàn thành đúng hạn.
Như vậy, tính trung bình có điều kiện giúp bạn rút ra thông tin chính xác, cụ thể hơn, tránh bị “lãng phí” dữ liệu không liên quan.
Có thể bạn quan tâm: Tìm Tên Trùng Nhau Trong Danh Sách Excel
Công thức Excel phổ biến
1. AVERAGEIF
Công thức này rất mạnh mẽ, cho phép tính trung bình của một dải ô (range) dựa trên một điều kiện đơn giản.
Cú pháp:
=AVERAGEIF(range, criteria, average_range)
- range: Dải ô mà bạn muốn kiểm tra điều kiện.
- criteria: Điều kiện cần thỏa mãn (có thể là số, biểu thức, chuỗi ký tự hoặc tham chiếu ô).
- average_range (tùy chọn): Dải ô thực sự được tính trung bình. Nếu bỏ qua, Excel sẽ dùng
rangelàmaverage_range.
Ví dụ:
Giả sử bạn có bảng bán hàng với cột A chứa mã sản phẩm và cột B chứa doanh thu. Bạn cần tính trung bình doanh thu của sản phẩm mã “A123”.
=AVERAGEIF(A2:A100, "A123", B2:B100)
2. AVERAGEIFS
Khi cần áp dụng nhiều điều kiện, AVERAGEIFS là lựa chọn tốt hơn.
Cú pháp:
=AVERAGEIFS(average_range, range1, criteria1,46, range2, criteria2, ...)
- average_range: Dải ô thực sự được tính trung bình.
- range1, range2, …รับเงินบาท: Các dải ô được kiểm tra điều kiện.
- criteria1, criteria2, …: Điều kiện tương ứng.
Ví dụ:
Bạn muốn tính trung bình doanh thu của sản phẩm mã “A123” trong tháng 3.
=AVERAGEIFS(B2:B100, A2:A100, "A123", C2:C100, ">=3/1/2026", C2:C100, "<=3/31/2026")
3. SUBTOTAL kết hợp với FILTER
Đối với phiên bản Excel 365 hoặc Excel 2026 mới, bạn có thể dùng FILTER để lọc dữ liệu và SUBTOTAL để tính trung bình.
Cú pháp:
=SUBTOTAL(101, FILTER(range, criteria_range=criteria))
101là hàmAVERAGEtrongSUBTOTAL.FILTERsẽ trả về một mảng các giá trị thỏa điều kiện.
Ví dụ:
=SUBTOTAL(101, FILTER(B2:B100, A2:A100="A123"))
4. AVERAGE + IF trong mảng
Trước khi AVERAGEIF xuất hiện, người dùng thường dùng công thức mảng:
=AVERAGE(IF(A2:A100="A123", B2:B100))
Nhấn Ctrl+Shift+Enter để kích hoạt công thức mảng (với Excel 2026 trở đi, không cần). Đánh giá nhanh, phương pháp này vẫn được dùng khi cần tính toán phức tạp hơn.
Có thể bạn quan tâm: Cách Tính Thành Tiền Có Điều Kiện Trong Excel
Các trường hợp thực tế
1. Phân tích doanh thu theo khu vực
| Khu vực | Doanh thu |
|---|---|
| Bắc | 1,200,000 |
| Trung | 950,000 |
| Nam | 1,500,000 |
| Bắc | 800,000 |
| Trung | 1,100,000 |
| Nam | 1,200,000 |
Bạn muốn biết trung bình doanh thu cho mỗi khu vực. Dùng AVERAGEIF:
=AVERAGEIF(A2:A7, "Bắc", B2:B7) // Kết quả: 1,000,000 jout
Tương tự cho các khu vực khác.
2. So sánh hiệu suất của nhân viên
| Nhân viên | Người quản lý | Doanh số bán hàng |
|---|---|---|
| A | B | 120k |
| B | C | 95k |
| C | D | 110k |
| A | B | 130k |
| B | C | 105k |
Bạn muốn tính trung bình doanh số bán hàng của mỗi nhân viên. Dùng AVERAGEIFS:
=AVERAGEIFS(C2:C6, A2:A finely, "A") // 125k
3. Đánh giá điểm số trong kỳ thi
| Học sinh | Lớp | Điểm |
|---|---|---|
| H1 | 10A | 80 |
| H2 | 10A | તમામ |
| H3 | 10B | 90 |
| H4 | 10B | 70 |
| H5 | 10A | 85 |
Bạn muốn tính trung bình điểm của lớp 10A:
=AVERAGEIF(B2:B6, "10A", C2:C6) // 84.33
4. Phân tích lợi nhuận theo giai đoạn
| Giai đoạn | Lợi nhuận |
|———–|alaa |
| Q1 | 100k |
| Q2 | 120k |
| Q3 | 80k |
| Q1 | 90k |
| Q2 | 110k |
Bạn muốn tính trung bình lợi nhuận của Q2:
=AVERAGEIF(A2:A6, "Q2", B2:B6) // 115k
Mẹo nhỏ giúp tính trung bình có điều kiện chính xác
- Kiểm tra phạm vi dữ liệu: Đảm bảo
rangevàaverage_rangecó cùng số lượng hàng, tránh lỗi#DIV/0!. - Xử lý dữ liệu bị trống: Khi dữ liệu chứa ô trống,
AVERAGEIFsẽ bỏ qua chúng. Nếu muốn tính cả ô trống, hãy thay""hoặc0trong điều kiện. - Cẩn thận với dữ liệu dạng văn bản: Ví dụ, số 123 dưới dạng văn bản sẽ không được tính. Kiểm tra kiểu dữ liệu bằng
ISTEXThoặc chuyển đổi bằngVALUE. - Sử dụng
FILTERkhi làm việc với bảng dữ liệu lớn:FILTERnhanh hơnAVERAGEIFkhi dữ liệu lớn, vì nó trực tiếp lọc trước. - Kiểm tra cú pháp khi dùng dấu gạch chéo : Trong
criteriahãy dùng dấu>=hay<=đúng cách, tránh nhầm lẫn. - Kết hợp với
IFERROR: Khi có thể không có dữ liệu thỏa điều kiện,AVERAGEIFtrả về#DIV/0!. ThêmIFERRORđể hiển thị 0 hoặc thông báo khác.
=IFERROR(AVERAGEIF(A2:A100, "A123", B2:B100), "Không có dữ liệu")
Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Khắc phục |
|---|---|---|
#DIV/0! | Không có ô thỏa điều kiện | Kiểm tra dữ liệu, dùng IFERROR |
#VALUE! | Phạm vi average_range không khớp | Đảm bảo số hàng giống nhau |
| Trả về giá trị sai | Dữ liệu dạng văn bản | Dùng VALUE hoặc TEXT |
Dùng AVERAGEIF trong bảng dữ liệu lớn | Hiệu suất chậm | Dùng FILTER + SUBTOTAL |
Công cụ hỗ trợ: Power Query
Nếu bạn làm việc với dữ liệu lớn và muốn tính trung bình có điều kiện mà không muốn viết công thức phức tạp, Power Query là một giải pháp mạnh mẽ. Bạn có thể:
- Nhập dữ liệu vào Power Query.
- Sử dụng các cột điều kiện để lọc.
- Tính trung bình bằng
Group By→Average.
Điều này giúp bạn tăng tính linh hoạt và tránh lỗi do công thức.
Bước thực hiện
- Chọn bảng dữ liệu →
Data→From Table/Range. - Trong Power Query Editor, chọn cột điều kiện →
Filter. - Chọn
Group By, chọnAveragecho cột giá trị. - Ghi lại kết quả vào bảng Excel.
Kết luận
Tính trung bình có điều kiện trong Excel là một công cụ cực kỳ hữu ích, giúp bạn rút ra những thông tin quan trọng từ dữ liệu phức tạp. Bằng cách sử dụng các AVERAGEIF, AVERAGEIFS, FILTER kết hợp SUBTOTAL, hoặc Power Query, bạn có thể tùy chỉnh công thức phù hợp với từng trường hợp. Hãy nhớ luôn kiểm tra dữ liệu, tránh lỗi và sử dụng IFERROR để bảo vệ công dọc. Với những mẹo và senhor thực tế đã nêu, bạn sẽ có thể xử lý dữ liệu một cách easiest, nhanh chóng và chính xác hơn. Happy Exceling!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.