Hàm COUNTIFS được sử dụng nhiều trong các lĩnh vực liên quan tới tính toán, thống kê,… Trong bài viết này Azgad Edu sẽ hướng dẫn chi tiết cho bạn cách dùng hàm COUNTIFS và cố gắng đưa ra ví dụ với nhiều dữ liệu nhất để bạn có thể nắm rõ được cách dùng của này này nhé!
1. Công thức và ứng dụng hàm COUNTIFS trong excel
Công thức hàm COUNTIFS
=COUNTIFS(Range1, Criteria1,[Range2, Criteria2],…)
Tham số trong hàm COUNTIFS:
- Range (Phạm vi): Phạm vi vùng dữ liệu cần đếm. Các ô có thể chứa số, mảng hoặc tham chiếu có chứa số.
- Criteria (Điều kiện): Điều kiện để đếm các giá trị trong phạm vi Range. Điều kiện có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.
- [Range2, Criteria2]: Các cặp phạm vị và điều kiện bổ sung. Cấu trúc hàm COUNTIFS cho phép bạn nhập tối đa 127 cặp điều kiện này.
Ứng dụng hàm COUNTIFS
- COUNTIFS là hàm cao cấp hơn của hàm COUNTIF, cấu trúc này được sử dụng để đếm số lượng ô dữ liệu thỏa mãn nhiều điều kiện cho trước.
- Cấu trúc có thể được sử dụng kết hợp với nhiều hàm khác trong Excel mang đến sự thuận tiện khi xử lý công việc.
Lưu ý: Hướng dẫn được thực hiện với phiên bản Excel 2019. Bạn hoàn toàn có thể thực hiện tương tự trên các phiên bản Excel khác nhau và trên các dòng laptop có hệ điều hành khác nhau.
2. Cách sử dụng hàm COUNTIFS trong Excel
Để hiểu rõ hơn về hàm đếm nhiều điều kiện này Azgad Edu sử dụng ví dụ minh hoạ cụ thể về bảng thống kê số lượng hàng hoá bán được như sau:
Hàm COUNTIFS với nhiều tiêu chí
Trong trường hợp này cấu trúc COUNTIFS được sử dụng để đếm các giá trị khớp với nhiều điều kiện xác định (điều kiện có thể là chuỗi văn bản hoặc số).
Ví dụ 1: Đếm số hoá đơn bán được trên 15 cái bàn.
Để giải quyết được bài toán này, trước hết chúng ta cần phải xác định các tham số của hàm COUNTIFS, cụ thể:
- Range (C2:C11): Phạm vi vùng dữ liệu chứa tên hàng hoá cần đếm
- Criteria (“bàn”): Điều kiện yêu cầu của tên hàng hoá
- Range2 (D2:D11): Phạm vi vùng dữ liệu chứa số lượng cần đếm
- Criteria2 (“>15”): Điều kiện yêu cầu của số lượng hàng hoá đã bán
Lưu ý:Các công thức trong Excel không phân biệt chữ hoa hay chữ thường vì vậy các điều kiện sau (“bàn”), (“Bàn) hay (“BÀN”) đều cho cùng một kết quả.
Nhập công thức =COUNTIFS(C2:C11,“bàn”,D2:D11,“>15”) ta được kết quả sau:
Ý nghĩa: Trong phạm vi giá trị từ C2:C11 (cột tên hàng), đếm số lượng bàn bán được (trong trường hợp này là 3). Tiếp đến điều kiện thứ 2, trong phạm vi giá trị từ D2:D11 (cột số lượng), đếm số lượng bàn bán được trên 15 cái. Kết quả chúng ta có 2 hoá đơn là bàn bán được trên 15 cái.
Cách đếm số trong khoảng cụ thể với nhiều điều kiện
Ví dụ 2: Dùng dữ liệu trên, đếm số hoá đơn bán được lớn hơn 10 và bé hơn 50 sản phẩm.
Các tham số cụ thể trong công thức COUNTIFS:
- Range (D2:D11): Phạm vi vùng dữ liệu chứa số lượng cần đếm
- Criteria (“>10”): Điều kiện yêu cầu của số lượng hàng hoá đã bán
- Range2 (D2:D11): Phạm vi vùng dữ liệu chứa số lượng cần đếm
- Criteria2 (“<50”): Điều kiện yêu cầu của số lượng hàng hoá đã bán
Nhập công thức =COUNTIFS(D2:D11,“>10”,D2:D11,“<50”) ta được kết quả sau:
Ý nghĩa: Trong phạm vi giá trị từ D2:D11 (cột số lượng), tìm ra bao nhiêu số lớn hơn 10 và bé hơn 50 (không bao gồm 10 và 50 trong trường hợp này). Ta nhận được kết quả là 4 hoá đơn có số lượng sản phẩm bán được lớn hơn 10 và bé hơn 50 sản phẩm.
Để đếm bao gồm cả 10 và 50, ta chỉ cần thay đổi Criteria (Điều kiện) thành:
=COUNTIFS(D2:D11,“>=10”,D2:D11,“<=50”)
Trong trường hợp này ta đếm bao gồm luôn những hoá đơn bán được 10 hoặc 50 sản phẩm nên kết quả nhận được là 6 hoá đơn.
Cách dùng hàm COUNTIFS với các ký tự đại diện
Trong trường hợp các bài toán có điều kiện chỉ bao gồm 1 vài ký tự của giá trị mà bạn muốn đếm, bạn có thể sử dụng ký tự đại diện đếm tất cả các ô chỉ chứa 1 vài ký tự nhất định.
Các ký tự đại diện trong Excel:
- Dấu hỏi chấm (?): đại diện cho một ký tự duy nhất tại một vị trí cụ thể.
- Dấu hoa thị (*): đại diện cho ký tự bất kỳ.
Ví dụ 3: Sử dụng dữ liệu trên, tổng hợp số lượng hoá đơn có mã kết thúc là “L1” và có số lượng tồn kho trên 50 sản phẩm.
Tương tự như các bài toán khác, ta xác định tham số cụ thể như sau:
- Range (B2:B11): Phạm vi vùng dữ liệu chứa mã hoá đơn cần đếm
- Criteria (“*L1”): Điều kiện yêu cầu của mã hoá đơn
Ký tự hoa thị (*) đại diện cho ký tự bất kỳ ở phía trước của mã hoá đơn cần tìm
Ví dụ: Mã hoá đơn B1230L1, ký tự (*) đại diện cho: “B1230”
- Range2 (E2:E11): Phạm vi vùng dữ liệu chứa số lượng tồn kho
- Criteria2 (“>50”): Điều kiện yêu cầu của số lượng hàng tồn kho
Ý nghĩa:
- Trong phạm vi dữ liệu B2:B11 (cột mã hoá đơn), đếm số lượng mã hoá đơn kết thúc bằng “L1”, trong trường hợp này ta có 4 hoá đơn có mã kết thúc là “L1”.
- Tiếp đến điều kiện thứ 2, trong vùng dữ liệu E2:E11 (cột tồn kho), tiếp tục đếm trong 4 hoá đơn trên có bao nhiêu hoá đơn có số lượng tồn kho trên 50 sản phẩm. Cuối cùng ta nhận được kết quả là 2 hoá đơn có mã kết thúc bằng “L1” và có số lượng tồn kho trên 50 sản phẩm.
Sử dụng COUNTIFS với nhiều tiêu chí là ngày tháng
Trong trường hợp ta này sử dụng hàm đếm nhiều điều kiện COUNTIFS để đếm các điều kiện liên quan tới ngày tháng, cách dùng tương tự cách sử dụng các điều kiện như text và số.
Ví dụ 1: Cho bảng thống kê dưới đây, đếm số đơn hàng bán được từ ngày 01/10/2021 đến ngày 15/10/2021.
Để giải quyết được bài toán này, trước hết chúng ta cần phải xác định các tham số của hàm COUNTIFS, cụ thể:
- Range (D2:D11): Phạm vi vùng dữ liệu chứa ngày bán
- Criteria (“>01/10/2021”): Điều kiện yêu cầu của ngày bán
- Range (D2:D11): Phạm vi vùng dữ liệu chứa ngày bán
- Criteria (“<15/10/2021”): Điều kiện yêu cầu thứ 2 của ngày bán
Nhập công thức =COUNTIFS(D2:D11,“>01/10/2021”,D2:D11,“<15/10/2021”) ta được kết quả sau:
Ý nghĩa: Trong phạm vi dữ liệu D2:D11 (cột ngày bán), tìm ra bao nhiêu đơn đã được bán từ ngày 01/10/2021 đến 15/10/2021. Ta có kết quả là 5.
Ví dụ 2: Có bao nhiêu đơn đã được mua nhưng chưa giao hàng.
Trong trường hợp này, ta có thể sử dụng hàm COUNTIFS kết hợp với hàm TODAY để giải quyết bài toán. Cụ thể:
- Range (D2:D11): Phạm vi vùng dữ liệu chứa ngày bán
- Criteria (“<”&TODAY()): Điều kiện yêu cầu của ngày bán phải trước ngày hôm nay (xác định số đơn đã được mua).
- Range (E2:E11): Phạm vi vùng dữ liệu chứa ngày giao phải sau ngày hôm nay.
- Criteria (“>”&TODAY()): Điều kiện yêu cầu ngày giao phải sau ngày hôm nay (chứng tỏ đơn hàng chưa được giao).
Nhập công thức =COUNTIFS(D2:D11,“<”&TODAY(),E2:E11,“>”&TODAY()) ta nhận kết quả:
Toc
Bài viết liên quan 01:
1. https://azgad.vn/archive/1127/
2. https://azgad.vn/archive/1038/
3. https://azgad.vn/archive/990/
Bài viết liên quan 02:
1. https://azgad.vn/archive/969/
2. https://azgad.vn/archive/1123/
3. https://azgad.vn/archive/1004/
3. Những điều cần lưu ý khi sử dụng hàm COUNTIFS
Để sử dụng hàm COUNTIFS hiệu quả ngoài nắm được các tham số ra bạn cần phải lưu ý một số điều dưới đây để tránh những sai sót nhé.
- Mỗi vùng điều kiện bổ sung [Range2, Criteria2], [Range3, Criteria3],… phải có cùng số hàng với vùng chọn 1 và các vùng dữ liệu không nhất thiết phải liền kề nhau.
- Đối với mỗi máy tính khác nhau, các toán tử liên kết trong phần mềm Excel sẽ hiển thị dấu phẩy (,) hoặc dấu chấm phẩy (;). Vì vậy bạn cần xác định đúng toán tử này để tránh sai sót khi thực hiện nhé.
- Bạn có thể sử dụng tiêu chí với các ký tự đại diện là dấu chấm hỏi (?) hoặc hoa thị (*). Trong trường hợp bạn cần tìm điều kiện là dấu hoa thị (*) hay dấu chấm hỏi (?) thì hãy gõ ký dấu ngã (~) trước ký tự cần tìm nhé.
- Nếu điều kiện của vùng chọn tham chiếu đến 1 ô trống thì hàm COUNTIFS sẽ coi ô trống là giá trị 0.
Như vậy qua các ví dụ cụ thể trên Azgad Edu đã cố gắng đưa ra những minh hoạ với nhiều trường hợp cụ thể giúp bạn hiểu rõ hơn về cách dùng hàm COUNTIFS. Nếu bạn có thắc mắc gì thì hãy để lại bình luận phía dưới để được hỗ trợ nhé.
Tác giả: Lê Thị Hoài Thương
