Thành thạo COUNTIFS, SUMIFS, AVERAGEIFS: Hướng dẫn thực hành cho người dùng Excel

Ba công thức phổ biến nhất trong Excel thực hiện các phép tính toán học đơn giản là COUNT, SUM và AVERAGE. Dù bạn đang quản lý ngân sách tài chính trong Excel hay chỉ theo dõi kỳ nghỉ sắp tới, bạn có thể đã dùng một trong các hàm này trước đây.

Trong bài viết này, chúng ta sẽ đi qua cơ bản của ba hàm này và các đối ứng hữu ích, liên quan của chúng: COUNTIFS, SUMIFS và AVERAGEIFS.

Giả sử chúng ta đang bắt đầu kinh doanh trực tuyến bán điện thoại và có một sheet liệt kê các giao dịch bán hàng trong hai tháng đầu. Tải xuống bảng tính Excel mẫu tại đây.

Excel COUNT, SUM và AVERAGE

Để biết số điện thoại đã bán, chúng ta có thể nhanh chóng dùng công thức COUNT như sau:

=COUNT(E2:E16)

Để có tổng doanh số bán hàng, chúng ta dùng công thức SUM như sau:

=SUM(E2:E16)

Cuối cùng, để tìm mức doanh số trung bình cho tất cả điện thoại, ta dùng AVERAGE như sau:

=AVERAGE(E2:E16)

Kết quả nên như sau:

Công thức COUNT, SUM và AVERAGE chỉ hoạt động cho các bản ghi mà giá trị ô ở định dạng số. Bất kỳ bản ghi nào trong phạm vi công thức (ví dụ E2:E16 trong ví dụ này) không ở định dạng số sẽ bị bỏ qua.

Vì vậy, hãy đảm bảo tất cả các ô trong phạm vi công thức COUNT, SUM và AVERAGE đều được định dạng là Number (Số), không phải Text (Văn bản). Thử dùng cùng công thức nhưng với phạm vi E:E thay vì E2:E16. Nó sẽ trả về cùng kết quả vì nó bỏ qua tiêu đề (ví dụ Sale Price), ở định dạng văn bản.

Bây giờ, nếu chúng ta muốn biết số lượng bán, tổng doanh số và doanh số trung bình mỗi điện thoại, chỉ cho những cái bán tại USA? Đây là lúc COUNTIFS, SUMIFS và AVERAGEIFS đóng vai trò quan trọng. Quan sát công thức dưới đây:

COUNTIFS

Phân tích công thức:

1. =COUNTIFS( - Dấu “=” chỉ bắt đầu công thức trong ô và COUNTIFS là phần đầu của hàm Excel chúng ta dùng.
2. D2:D16 - Tham chiếu đến phạm vi dữ liệu cần kiểm tra xem có thỏa mãn tiêu chí để được tính vào đếm không.
3. “USA” – Tiêu chí cần tìm trong phạm vi dữ liệu chỉ định (D2:D16)
4. ) – Dấu ngoặc đóng chỉ kết thúc công thức.

Công thức trả về 6 là số lượng giao dịch cho sản phẩm xuất từ kho USA.

SUMIFS

Phân tích công thức:

1. =SUMIFS( - Dấu “=” chỉ bắt đầu công thức.
2. E2:E16 - Tham chiếu đến phạm vi dữ liệu chúng ta muốn tổng, ví dụ giá bán.
3. D2:D16 - Tham chiếu đến phạm vi dữ liệu kiểm tra tiêu chí để được tính vào tổng.
4. “USA” – Tiêu chí cần tìm trong phạm vi dữ liệu chỉ định (D2:D16)
5. ) – Dấu ngoặc đóng kết thúc công thức.

Công thức cho thấy $6,050 tổng doanh số cho sản phẩm xuất từ kho USA.

AVERAGEIFS

Phân tích công thức:

1. =AVERAGEIFS( - Dấu “=” chỉ bắt đầu công thức.
2. E2:E16 - Tham chiếu đến phạm vi dữ liệu chúng ta muốn trung bình. Trong ví dụ này, chúng ta muốn lấy mức doanh số trung bình cho tất cả điện thoại bán tại USA.
3. D2:D16 - Tham chiếu đến phạm vi dữ liệu kiểm tra tiêu chí để được tính vào trung bình.
4. “USA” – Tiêu chí cần tìm trong phạm vi dữ liệu chỉ định
5. ) – Dấu ngoặc đóng kết thúc công thức.

Công thức cho thấy chúng ta bán sản phẩm khoảng $1,008 mỗi điện thoại tại USA.

Ba công thức trên đều có thể nhận nhiều hơn một tiêu chí. Ví dụ, nếu chúng ta muốn cùng những số liệu (COUNT, SUM, AVERAGE) cho sản phẩm bán tại USA, nhưng cụ thể chỉ cho thương hiệu Samsung, chúng ta chỉ cần thêm phạm vi dữ liệu cần kiểm tra theo sau là tiêu chí của nó.

Xem ví dụ dưới đây nơi tiêu chí thứ hai được thêm vào kiểm tra tiêu chí ban đầu. (Văn bản xanh chỉ tiêu chí đầu tiên và đỏ chỉ tiêu chí thứ hai)

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung") =SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung") =AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

Bạn sẽ nhận thấy Excel cũng có công thức COUNTIF, SUMIF và AVERAGEIF không có hậu tố “S”. Chúng được dùng tương tự như COUNTIFS, SUMIFS và AVERAGEIFS. Tuy nhiên, những công thức không có hậu tố “S” có giới hạn chỉ cho phép một tiêu chí mỗi công thức.

Vì cú pháp hơi khác, tôi khuyên nên chỉ dùng COUNTIFS, SUMIFS và AVERAGEIFS vì chúng có thể dùng cho một tiêu chí hoặc nhiều hơn, nếu cần. Thưởng thức!