Bài tập mẫu hàm COUNTIF và COUNTIFS trong Excel (Có lời giải)

Dưới đây là bài tập mẫu điển hình (kèm lời giải) làm ví dụ cho việc áp dụng hàm thống kê có điều kiện COUNTIF và COUNTIFS, tức đếm số giá trị thỏa một hoặc nhiều điều kiện được gán.

Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ A tin học văn phòng hiệu quả.

Ở cuối bài, có file Excel XLSX chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.

Bảng dữ liệu bài tập

Bảng dữ liệu bài tập hàm Countif và countifs

Yêu cầu tính toán

Lập công thức excel để đếm:

• Số nhân viên phòng Kinh Doanh;
• Số Nhân viên có nghỉ phép trong tháng;
• Số nhân viên họ Trần;
• Số nhân viên phòng Kinh Doanh có lương > 6 triệu;
• Số nhân viên phòng Kế hoạch có lương > 8 triệu;
• Số Nhân viên phòng kinh doanh không nghỉ phép;
• Số Nhân viên phòng kinh doanh nghỉ phép trên 5 ngày.

Khi đề bài yêu cầu đếm số lượng các giá trị nào đó, ta nghĩ ngay đến bộ hàm COUNT.

Nếu đề bài yêu cầu đếm có 1 điều kiện, thì hàm phải dùng là COUNTIF

Nếu đề bài yêu cầu đếm có nhiều điều kiện, thì hàm phải dùng là COUNTIFS

Cấu trúc các hàm excel này rất đơn giản, bạn có thể click xem tại các link dưới đây:

• Lý thuyết hàm COUNT
• Lý thuyết hàm COUNTIF
• Lý thuyết hàm COUNTIFS

Bây giờ ta bắt đầu ứng dụng hàm COUNTIF và COUNTIFS để tính theo yêu cầu của bài tập Excel này.

Số nhân viên phòng Kinh Doanh: 

Yêu cầu đếm có 1 điều kiện là phòng “Kinh doanh”, do đó ta dùng hàm COUNTIF

Tại ô E31, ta gõ hàm như sau:

=COUNTIF(B6:B29,”Kinh doanh”)

Enter sẽ cho kết quả

Số Nhân viên có nghỉ phép trong tháng:

Yêu cầu đếm có 1 điều kiện là có nghỉ phép, tức cột “Số ngày nghỉ” khác 0, do đó ta dùng hàm COUNTIF

Tại ô E32, ta gõ hàm như sau:

=COUNTIF(D6:D29,”<>0″)

Enter sẽ cho kết quả.

Số nhân viên họ Trần:

Yêu cầu đếm có 1 điều kiện là nhận viênc ó họ” Trần”, tức cột “Tên” bắt đầu bằng chữ “Trần”. Do đó ta dùng hàm COUNTIF

Tại ô E33, ta gõ hàm như sau:

=COUNTIF(A6:A29,“Trần *”)

Enter sẽ cho kết quả.

Chú ý điều kiện thiết lập trong hàm là “Trần *”, có ý nghĩa là chỉ đếm những dòng mà cột “Tên” có chữ “Trần” ở đầu và tiếp theo sau là một khoảng trắng rồi đến các ký tự bất kỳ khác.

Số nhân viên phòng Kinh Doanh có lương > 6 triệu:

Yêu cầu đếm có 2 điều kiện là “Phòng” “Kinh doanh” và “Lương” > 6 triệu, do đó ta dùng hàm COUNTIFS

Tại ô E35, ta gõ hàm như sau:

=COUNTIFS(B6:B29,”Kinh doanh”,C6:C29,“>6000000”)

Số nhân viên phòng Kế hoạch có lương > 8 triệu:

Yêu cầu đếm có 2 điều kiện là “Phòng” “Kế hoạch” và “Lương” > 8 triệu, do đó ta dùng hàm COUNTIFS

Tại ô E36, ta gõ hàm như sau:

=COUNTIFS(B6:B29,”Kế hoạch”,C6:C29,”>8000000″)

Số Nhân viên phòng kinh doanh không nghỉ phép:

Yêu cầu đếm có 2 điều kiện là “Phòng” “Kinh doanh” và “Số ngày nghỉ” là 0, do đó ta dùng hàm COUNTIFS

Tại ô E37, ta gõ hàm như sau:

=COUNTIFS(B6:B29,”Kinh doanh”,D6:D29,”0″)

Số Nhân viên phòng kinh doanh nghỉ phép trên 5 ngày:

Yêu cầu đếm có 2 điều kiện là “Phòng” “Kinh doanh” và “Số ngày nghỉ” > 5, do đó ta dùng hàm COUNTIFS

Tại ô E38, ta gõ hàm như sau:

=COUNTIFS(B6:B29,”Kinh doanh”,D6:D29,”>5″)

Video hướng dẫn

Nếu chi tiết lời giải như trên vẫn chưa đủ, hãy xem video hướng dẫn dưới đây.

Chú ý chọn chế độ xem HD rồi phóng to màn hình để nhìn rõ chữ.

Tải file .XLSX giải đề

Bạn cũng có thể tải file Excel XLSX sau về tham khảo: https://www.trungtamtinhoc.edu.vn/download/countifs.zip

Password giải nén là: ttth

Chúc các bạn thành công.

Trần Triệu Phú