Cách dụng hàm AVERAGEIFS trong Excel để tính trung bình cộng

TÁC GIẢ DungDM
, Kiến thức
, Tháng 10 8, 2026
11:16 sáng

Nội dung bài viết

Việc tính toán trung bình cộng khi phải lọc đồng thời nhiều điều kiện bằng phương pháp thủ công không chỉ tiêu tốn hàng giờ đồng hồ mà còn rất dễ dẫn đến những sai sót không đáng có trong báo cáo. Bạn đang tìm kiếm một giải pháp tự động hóa giúp xử lý các tập dữ liệu lớn nhanh chóng, chính xác mà không lo phát sinh mã lỗi? Bài viết này từ MSO sẽ hướng dẫn bạn từ A-Z cách sử dụng hàm AVERAGEIFS trong Excel, từ bản chất khái niệm, cấu trúc cú pháp chuẩn, các ví dụ thực tế (lọc theo văn bản, ngày tháng, toán tử, ký tự đại diện) cho đến mẹo khắc phục triệt để các lỗi #DIV/0!, #VALUE! thường gặp!

Cách dùng hàm AVERAGEIFS qua các ví dụ chi tiết

Cách dùng hàm AVERAGEIFS thực hiện bằng việc xác định chính xác vùng chứa số liệu cần tính trung bình, sau đó truyền các cặp vùng điều kiện và giá trị điều kiện vào công thức theo đúng thứ tự.

Tính trung bình theo nhiều điều kiện văn bản (Text)

Ví dụ: Tính doanh thu trung bình của các nhân viên thuộc phòng “Kinh doanh 1” và làm việc tại khu vực “Hà Nội”.

Phân tích:

Vùng tính trung bình (average_range): Doanh thu D2:D7
Vùng điều kiện 1 (criteria_range1): Phòng ban B2:B7
Điều kiện 1 (criteria1): “Kinh doanh 1”
Vùng điều kiện 2 (criteria_range2): Khu vực C2:C7
Điều kiện 2 (criteria2): “Hà Nội”

Công thức áp dụng:

=AVERAGEIFS(D2:D7, B2:B7, “Kinh doanh 1”, C2:C7, “Hà Nội”)

*Tính trung bình theo nhiều điều kiện văn bản (Text)*

Kết quả: Công thức lọc ra dòng 1, dòng 3 và dòng 6. Giá trị trung bình tính được là: (80.000.000+120.000.000+110.000.000)/3=103.333.333 VNĐ.

Tính trung bình kết hợp với các toán tử so sánh (>, <, >=, <=, <>)

Ví dụ: Tính doanh thu trung bình của các nhân viên có số hợp đồng từ 10 trở lên (>=10) và có doanh thu dưới 100 triệu đồng (<100000000).

Phân tích:

Vùng tính trung bình (average_range): D2:D7
Vùng điều kiện 1 (criteria_range1): Số hợp đồng E2:E7
Điều kiện 1 (criteria1): “>=10”
Vùng điều kiện 2 (criteria_range2): Doanh thu D2:D7
Điều kiện 2 (criteria2): “<100000000”

Công thức áp dụng:

=AVERAGEIFS(D2:D7, E2:E7, “>=10”, D2:D7, “<100000000”)

**Lưu ý: Các toán tử so sánh khi đi kèm số phải được bọc trong dấu ngoặc kép “”.

*Tính trung bình kết hợp với các toán tử so sánh (>, <, >=, <=, <>)*

Kết quả: Các dòng thỏa mãn là dòng 1 và dòng 5. Giá trị trung bình là: (80.000.000+95.000.000)/2=87.500.000 VNĐ.

**Sử dụng một số ký tự đại diện Wildcards (* và ?) trong điều kiện**

Ký tự đại diện giúp bạn lọc dữ liệu chứa chuỗi văn bản không cố định:

Dấu sao (*): Đại diện cho một chuỗi nhiều ký tự bất kỳ.
Dấu hỏi (?): Đại diện cho đúng một ký tự đơn lẻ.

Ví dụ: Tính số hợp đồng trung bình của các phòng ban bắt đầu bằng chữ “Kinh doanh” (bao gồm Kinh doanh 1, Kinh doanh 2) tại khu vực “Hà Nội”.

Công thức áp dụng:

=AVERAGEIFS(E2:E7, B2:B7, “Kinh doanh*”, C2:C7, “Hà Nội”)

*Sử dụng một số ký tự đại diện Wildcards (* và ?) trong điều kiện*

Kết quả: Công thức xét duyệt tất cả các dòng thuộc phòng Kinh doanh 1 và Kinh doanh 2 ở Hà Nội (dòng 1, 3, 5, 6) và tính trung bình số hợp đồng: (12+15+11+14)/4=13 hợp đồng.

Tính trung bình theo điều kiện khoảng thời gian (Ngày/Tháng/Năm)

Ví dụ: Tính doanh thu trung bình ghi nhận trong khoảng thời gian từ ngày 01/01/2026 đến ngày 28/02/2026.

Phân tích: Để lọc theo một khoảng thời gian, bạn tách thành 2 điều kiện: lớn hơn hoặc bằng ngày bắt đầu và nhỏ hơn hoặc bằng ngày kết thúc.

Công thức áp dụng:

=AVERAGEIFS(D2:D7, F2:F7, “>=01/01/2026”, F2:F7, “<=28/02/2026”)

*Tính trung bình theo điều kiện khoảng thời gian (Ngày/Tháng/Năm)*

Kết quả: Công thức tính trung bình doanh thu của các dòng 1, 2, 3, 4.

Hàm AVERAGEIFS trong Excel có tác dụng gì?

Hàm AVERAGEIFS dùng để tính trung bình cộng của tất cả các ô thỏa mãn đồng thời hai hoặc nhiều điều kiện cho trước, giúp tự động hóa việc thống kê dữ liệu đa chiều mà không cần lọc thủ công.

Khái niệm và cơ chế hoạt động của hàm AVERAGEIFS

Hàm AVERAGEIFS là sự kết hợp giữa tính toán trung bình cộng (Average) và kiểm tra điều kiện (Ifs – dạng số nhiều). Điểm đặc biệt của hàm này nằm ở cơ chế kiểm tra theo logic AND. Điều này có nghĩa là một dòng dữ liệu chỉ được đưa vào tập hợp để tính trung bình khi và chỉ khi nó đáp ứng đầy đủ tất cả các tiêu chuẩn mà bạn thiết lập.

Khi bạn thực hiện công thức, Excel sẽ tiến hành quét qua từng dòng trong các vùng điều kiện. Nếu dòng đó thỏa mãn điều kiện 1, điều kiện 2, điều kiện 3… thì giá trị tương ứng tại vùng tính trung bình mới được cộng dồn và chia trung bình ở bước cuối cùng.

Phân biệt AVERAGE, AVERAGEIF và AVERAGEIFS trong Excel

Để áp dụng đúng hàm cho từng bài toán cụ thể, bạn có thể tham khảo bảng so sánh chi tiết dưới đây:

Tiêu chí so sánh	Hàm AVERAGE	Hàm AVERAGEIF	Hàm AVERAGEIFS
Số lượng điều kiện	0 điều kiện (tính toàn bộ)	Đúng 1 điều kiện	Từ 1 đến 127 điều kiện
Thứ tự vùng tính trung bình	Bắt buộc chọn ở đầu công thức	Đặt ở vị trí cuối công thức	Bắt buộc đặt ở vị trí đầu công thức
Logic kiểm tra	Không kiểm tra	Kiểm tra 1 tiêu chí đơn lẻ	Kiểm tra đồng thời nhiều tiêu chí (Logic AND)
Trường hợp sử dụng	Tính trung bình chung cho cả cột dữ liệu	Tính trung bình khi chỉ có 1 yêu cầu lọc	Tính trung bình khi có từ 2 yêu cầu lọc trở lên

Cú pháp và các tham số của hàm AVERAGEIFS trong Excel

Cú pháp hàm AVERAGEIFS trong Excel bao gồm vùng tính trung bình bắt buộc nằm ở đầu tiên, theo sau là các cặp vùng điều kiện và điều kiện tương ứng được phân tách bằng dấu phẩy hoặc dấu chấm phẩy.

Cú pháp chuẩn của công thức

Trong môi trường bảng tính Excel, công thức được thiết lập theo cấu trúc sau:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Các tham số trong hàm

average_range (Bắt buộc): Vùng chứa các giá trị số mà bạn muốn tính trung bình cộng. Vùng này có thể là một cột, một hàng hoặc một khối ô chứa dữ liệu số.
criteria_range1 (Bắt buộc): Vùng dữ liệu thứ nhất chứa các giá trị dùng để kiểm tra điều kiện 1.
criteria1 (Bắt buộc): Điều kiện áp dụng cho criteria_range1. Điều kiện này có thể ở dạng con số, biểu thức so sánh (ví dụ: “>50”), văn bản (ví dụ: “Hà Nội”), hoặc tham chiếu ô (ví dụ: B2).
[criteria_range2, criteria2] (Tùy chọn): Các cặp vùng điều kiện và điều kiện bổ sung từ cặp thứ 2 trở đi. Excel cho phép bạn khai báo tối đa 127 cặp điều kiện trong cùng một công thức.

Quy tắc bắt buộc về kích thước vùng dữ liệu (Range Dimensions)

Một quy tắc kỹ thuật quan trọng khi viết hàm AVERAGEIFS là tất cả các vùng criteria_range phải có cùng số hàng và số cột với vùng average_range.

Ví dụ, nếu average_range của bạn là khối ô từ D2:D20 (gồm 19 hàng), thì criteria_range1 và criteria_range2 cũng bắt buộc phải quét từ hàng 2 đến hàng 20 (ví dụ B2:B20 và C2:C20). Nếu bạn khai báo criteria_range1 là B2:B25, Excel sẽ lập tức trả về lỗi định dạng #VALUE!.

Các lỗi thường gặp và cách khắc phục khi dùng hàm AVERAGEIFS trong Excel

Khi sử dụng hàm AVERAGEIFS trong Excel, bạn có thể gặp sự cố như lỗi #DIV/0!, #VALUE! hoặc kết quả sai lệch, nguyên nhân chủ yếu đến từ dữ liệu đầu vào không thỏa mãn điều kiện hoặc sai lệch kích thước vùng chọn.

Lỗi #DIV/0! (Lỗi chia cho 0)

Nguyên nhân: Lỗi này xuất hiện khi không có bất kỳ dòng dữ liệu nào trong bảng thỏa mãn toàn bộ các điều kiện đưa ra, hoặc vùng average_range chỉ chứa ô trống/văn bản.

Cách khắc phục: Lồng hàm AVERAGEIFS vào bên trong hàm IFERROR để hiển thị thông báo thân thiện hơn thay vì hiển thị mã lỗi.

=IFERROR(AVERAGEIFS(D2:D7, B2:B7, “Kinh doanh 3”, C2:C7, “Hà Nội”), “Không có dữ liệu phù hợp”)

Lỗi #VALUE! do lệch kích thước vùng dữ liệu

Nguyên nhân: Số lượng hàng/cột giữa các vùng tham chiếu không đồng nhất với nhau.

Ví dụ gây lỗi: =AVERAGEIFS(D2:D7, B2:B10, “Kinh doanh 1”) (Vùng tính trung bình có 6 hàng nhưng vùng điều kiện lại quét 9 hàng).

Cách khắc phục: Kiểm tra lại tọa độ địa chỉ ô trong công thức và điều chỉnh cho tất cả các vùng quét đúng cùng một phạm vi hàng (ví dụ chuyển toàn bộ về từ hàng 2 đến hàng 7).

Kết quả trả về bằng 0 hoặc sai số do khoảng trắng và định dạng Text

Nguyên nhân: Các con số trong vùng average_range đang bị lưu ở dạng chuỗi (Text), hoặc các từ ngữ trong vùng điều kiện chứa khoảng trắng thừa ở đầu/cuối (ví dụ: “Hà Nội “ thay vì “Hà Nội”).

Cách khắc phục:

Dùng tính năng Value để chuyển đổi cột số từ Text về dạng Number.
Sử dụng hàm TRIM hoặc công cụ Find and Replace để làm sạch toàn bộ khoảng trắng thừa trong bảng tính trước khi thực hiện viết công thức.

Nếu bạn hoặc doanh nghiệp đang tìm kiếm các giải pháp phần mềm bản quyền Microsoft Office, Microsoft 365 chính hãng để trải nghiệm đầy đủ các tính năng phân tích nâng cao, hãy liên hệ ngay với MSO để nhận được hỗ trợ tư vấn chuyên sâu cùng chi phí ưu đãi.

ĐĂNG KÝ TẠI ĐÂY

Các câu hỏi thường gặp (FAQ)

Dưới đây là một số thắc mắc thường gặp và giải đáp ngắn gọn giúp bạn làm chủ hàm AVERAGEIFS dễ dàng hơn:

Hàm AVERAGEIFS có phân biệt chữ hoa và chữ thường không?

Không. Hàm không phân biệt chữ hoa hay chữ thường (ví dụ: “HÀ NỘI” và “Hà Nội” đều trả về kết quả giống nhau).

Hàm AVERAGEIFS có tính trung bình theo điều kiện HOẶC (Logic OR) được không?

Không trực tiếp. Hàm chỉ hỗ trợ logic VÀ (AND – thỏa mãn tất cả điều kiện). Muốn tính theo điều kiện HOẶC, bạn cần kết hợp hàm AVERAGE với FILTER hoặc dùng công thức mảng.

Hàm AVERAGEIFS hỗ trợ tối đa bao nhiêu điều kiện?

Tối đa 127 cặp vùng điều kiện và điều kiện trong cùng một công thức.

Hàm xử lý thế nào nếu vùng tính trung bình chứa ô trống hoặc văn bản?

Hàm sẽ tự động bỏ qua các ô trống và ô chứa văn bản trong vùng average_range khi tính toán mà không gây ra lỗi.

Điểm khác biệt lớn nhất về thứ tự tham số giữa AVERAGEIF và AVERAGEIFS là gì?

Trong hàm AVERAGEIF (1 điều kiện), vùng tính trung bình nằm ở cuối công thức; còn trong hàm AVERAGEIFS (nhiều điều kiện), vùng tính trung bình bắt buộc phải nằm ở đầu công thức.

Lời kết

Việc am hiểu và làm chủ hàm AVERAGEIFS trong Excel từ cấu trúc cú pháp, cách kết hợp toán tử so sánh, điều kiện ngày tháng cho đến kỹ năng xử lý lỗi sẽ giúp bạn nâng cao năng lực phân tích số liệu và hoàn thiện các báo cáo quản trị một cách tự động, chuẩn xác. Hãy áp dụng ngay các kiến thức trong bài viết vào công việc hàng ngày để tối ưu quy trình xử lý dữ liệu của bạn.

———————————————————

Fanpage: MSO.vn – Microsoft 365 Việt Nam

Hotline: 024.9999.7777