Lệnh đếm có điều kiện trong excel

     

Trong toàn bộ các hàm Excel, hàm đếm có đk COUNTIFS cùng COUNTIF chắc rằng thường dễ dẫn đến nhầm lẫn nhất vì chưng chúng trông cực kỳ giống nhau và cả hai phần đông cùng có mục đích dùng nhằm đếm những ô dựa trên các điều kiện đã được chỉ định.

Bạn đang xem: Lệnh đếm có điều kiện trong excel

Sự khác hoàn toàn duy độc nhất vô nhị giữa hai hàm này là COUNTIF được thiết kế theo phong cách để đếm các ô có một đk trong một phạm vi, trong những lúc COUNTIFS có thể đánh giá các điều kiện không giống nhau trong thuộc hoặc trong những phạm vi không giống nhau.

Nhằm khiến cho bạn đọc rành mạch một cách đúng chuẩn và rứa được cách áp dụng hai hàm đếm có điều kiện COUNTIFS đúng trong Excel, trong bài viết này astore.vn sẽ giới thiệu và phía dẫn độc giả cách sử dụng và lựa chọn công thức công dụng nhất mang đến từng trọng trách từng vấn đề cụ thể, đặc biệt là đối với hàm COUNTIFS.

1. Hàm COUNTIFS của Excel - cú pháp và phương pháp sử dụng

Như vẫn đề cập, hàm COUNTIFS của Excel được dùng để đếm những ô trên nhiều phạm vi dựa trên một hoặc một số điều kiện. Hàm COUNTIFS tất cả sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007, vì vậy bạn cũng có thể sử dụng các ví dụ dưới đây trong bất kỳ phiên bản học Excel nào.

Cú pháp: =COUNTIFS(criteria_range1, criteria1, ,…)

Trong đó: 

- criteria_range1: Vùng chứa điều kiện thứ nhất, đấy là phần sẽ phải có

- criteria1: Điều kiện đầu tiên (thuộc vào vùng điều kiện thứ nhất) gồm có những dạng số, ô tham chiếu, chuỗi văn bản, hàm Excel hoặc vùng Excel khác.

- criteria_range2: Vùng chứa đk thứ hai( yêu cầu vùng criteria_range2 phải bao gồm độ lớn bằng nhau)

criteria2, ... Điều kiện máy hai (thuộc trong vùng đk thứ nhất). Chúng ta cũng có thể chỉ định về tối đa 127 cặp phạm vi / điều kiện trong cách làm của mình.

- giống như như vậy với các điều khiếu nại khác cũng sẽ áp dụng như vậy.

*

Hình minh họa

Thực tế, bạn không nhất thiết phải nhớ cú pháp của hàm đếm tất cả điều kiện COUNTIFS. Microsoft Excel sẽ hiển thị những đối số của hàm ngay trong khi bạn ban đầu nhập; đối số nhiều người đang nhập vào hôm nay được tô đậm.

Bạn chỉ cần nhớ bạn muốn sử dụng hàm COUNTIFS trong Excel là được.

LƯU Ý: 

- bạn cũng có thể sử dụng hàm COUNTIFS trong Excel để đếm những ô vào một phạm vi cùng với một điều kiện duy nhất cũng giống như trong nhiều phạm vi với nhiều điều kiện. Nếu như sau này, chỉ hồ hết ô thỏa mãn nhu cầu tất cả những điều kiện được chỉ định new được tính.

- Criteria1 bạn hoàn toàn có thể biểu diễn chúng y như các ví dụ: 10, "- mỗi phạm vi bổ sung cập nhật phải tất cả cùng số hàng cùng cột như phạm vi đầu tiên.Cả phạm vi tiếp liền kề và ko liền kề phần đông được mang đến phép.

- ví như điều kiện là một tham chiếu cho một ô trống , hàm COUNTIFS coi nó là quý giá 0 (0).

- bạn cũng có thể sử dụng các ký từ bỏ đại diện trong điều kiện - lốt hoa thị (*) và dấu hỏi (?). 

2. Cách thực hiện hàm đếm có đk COUNTIFS với khá nhiều điều kiện

Dưới đây bạn sẽ tìm thấy một số trong những ví dụ về phương pháp minh họa giải pháp sử dụng các hàm COUNTIFS vào Excel để review nhiều điều kiện.

Thông qua đông đảo ví dụ được minh họa bên dưới đây các bạn sẽ hình dung ra được cách thực hiện những hàm này trong số trường hợp ví dụ tương ứng để rất có thể áp dụng hàm COUNTIFS vào Excel để đánh giá nhiều đk khác nhau

2.1. Cách đếm những ô có không ít điều kiện

Công thức 1. Cách làm COUNTIFS với tương đối nhiều điều kiện không giống nhau

Ví dụ bạn gồm một danh sách thành phầm như hiển thị trong ảnh chụp màn hình hiển thị bên dưới. Bạn muốn có được một trong những lượng các sản phẩm trong kho (giá trị vào cột B to hơn 0) nhưng không được bán (giá trị là cột C bằng 0).

Nhiệm vụ có thể được thực hiện bằng cách sử dụng phương pháp này:

=COUNTIFS(B2:B7,">0", C2:C7,"=0")

Và con số là 2 ("Anh đào " với " Chanh "):

*

Hình minh họa

Công thức 2. Cách làm COUNTIFS gồm cùng tiêu chí

Khi bạn có nhu cầu đếm các mục gồm điều kiện giống hệt nhau, bạn vẫn cần cung ứng từng cặp điều kiện_range / điều kiện .

Xem thêm: Xem Nhiều 4/ 2 Phân Vàng 18K Bao Nhiêu Tiền 1 Chỉ? 5 Phân Vàng (Nửa Chỉ) Giá Bao Nhiêu Hôm Nay

Ví dụ: đấy là công thức đúng để đếm các mục tất cả 0 cả vào cột B cùng cột C:

=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")

Công thức COUNTIFS này trả về 1 vì chỉ " Nho " có mức giá trị "0" trong cả nhị cột

*

Hình minh họa

2.2. Giải pháp đếm ô có tương đối nhiều điều kiện không giống nhau (logic OR)

Như các bạn đã thấy trong các ví dụ trên, việc đếm những ô thỏa mãn nhu cầu tất cả các điều khiếu nại đã chỉ định là tiện lợi vì hàm đếm bao gồm điều kiện COUNTIFS có phong cách thiết kế để hoạt động theo giải pháp này.

Nhưng nếu bạn có nhu cầu đếm những ô mà tối thiểu một trong số điều kiện được hướng đẫn là TRUE , tức là dựa bên trên logic OR thì sao? quan sát chung, gồm hai cách để thực hiện bài toán này - bằng phương pháp thêm một số công thức COUNTIFS hoặc thực hiện công thức SUM COUNTIFS.

Công thức 1. Thêm nhì hoặc nhiều công thức trong COUNTIFS

Ví dụ: để sở hữu được số lượng đơn mua hàng " Đã bỏ " và " Đang ngóng xử lý" mang lại " táo bị cắn ", hãy sử dụng công thức sau:

=COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đã hủy") + COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đang ngóng xử lý")

*

Hình minh họa

Công thức 2. SUMIFS với hằng số mảng

Trong các tình huống khi bạn phải reviews rất nhiều điều kiện, cách trên không hẳn là cách tốt nhất vì bí quyết của các bạn sẽ quá dài.

Để tiến hành các phép tính tương tự trong một công thức nhỏ tuổi gọn hơn, hãy liệt kê tất cả các điều kiện của chúng ta trong một hằng số mảng và hỗ trợ mảng đó mang lại đối số điều kiện của hàm đếm gồm điều kiện COUNTIFS. 

Ta sẽ dùng bí quyết sau:

=SUM(COUNTIFS(range,"criteria1","criteria2","criteria3",…))

Trong bảng bên dưới đây, nhằm đếm ‘Táo” có trạng thái " Đã diệt " hoặc " Đang chờ cách xử trí " hoặc " Nhập kho ", cách làm sẽ như sau:

=SUM(COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,"Đã hủy","Đang hóng xử lý","Nhập kho"))

*

Hình minh họa

2.3. Cách đếm số trong khoảng cụ thể

Công thức 1: Hàm COUNTIFS đếm các ô trong vòng số

Công thức COUNTIFS nhằm đếm những ô thân hai số

Để khám phá có từng nào số trường đoản cú 5 đến 10 (không bao gồm 5 cùng 10) được chứa trong những ô trường đoản cú C2 mang lại C10, hãy áp dụng công thức này:

= COUNTIFS (C2: C10, "> 5", C2: C10, "

*

Hình minh họa

Để đếm những số 5 và 10 trong các đếm, hãy sử dụng công thức "lớn rộng hoặc bằng" cùng "nhỏ rộng hoặc bằng":

= COUNTIFS (B2: B10, "> = 5", B2: B10, "

Công thức 2: công thức COUNTIF nhằm đếm số giữa X với Y

Bạn rất có thể áp dụng 2 bí quyết sau: 

=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″) – đếm bao gồm bao nhiêu số to hơn 5 bé dại hơn 10 trong vùng dữ liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – bí quyết đếm tất cả bao nhiêu số trong đoạn từ 5 mang lại 10 nằm trong vùng dữ iệu C2:C10, bao hàm cả 5 với 10.

Trong đó:

- Công thức trước tiên dùng nhằm đếm hầu như số lớn hơn giá trị giới hạn dưới (Ví dụ là 5)

- công thức thứ 2 sa thải những cố to hơn giá trị số lượng giới hạn trên (Ví dụ là 10)

Do vậy hiệu quả nhận được vẫn nằm trong khoảng giá trị nhận ra trên. 

Cách sử dụng tham chiếu ô trong cách làm COUNTIFS

Ví dụ: đến bảng tài liệu sau, hãy tính các giao dịch "Apples" cùng với số tiền >200$.

Bạn vận dụng công thức như sau: =COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)

Trong đó: 

criteria_range1 trong A2: A11

 - criteria_range2 trong B2: B11. 

Lưu ý sử dụng giá trị tuyệt vời nhất trong criteria và criteria_range để bí quyết vẫn đúng khi xào luộc sang những ô tính khác.

*

Hình hình ảnh minh họa

2.4. Cách áp dụng COUNTIFS với các ký từ đại diện

Trong những câu thức Excel, chúng ta cũng có thể sử dụng các ký tự đại diện như sau:

- ? : được sử dụng để đếm các ô ban đầu hoặc xong bằng một trong những ký tự duy nhất định.

- * " Được sử dụng để đếm ô đựng một từ rõ ràng hoặc một k1 tự trong ô.

Ví dụ các bạn có danh sách những dự án vào cột A. Bạn muốn biết tất cả bao nhiêu dự án đã ký kết kết, có nghĩa là cột B gồm chứa thương hiệu người. Hãy thêm một đk thứ hai - over Date trong cột D.

=COUNTIFS(B2:B10,”*”,D2:D10,””&””))

Lưu ý, các bạn không thể thực hiện ký tự đại diện thay mặt trong tiêu chí thứ 2 vày cột D đựng kiểu tài liệu ngày mon chứ chưa hẳn dạng text. Đó là lý do tại sao chúng ta sử dụng tiêu chí “”&”” nhằm tìm ô đựng dữ liệu.

*

Hình minh họa

3. COUNTIFS cùng COUNTIF cùng với nhiều tiêu chí ngày tháng

Ví dụ 1: Đếm số ngày vào một khoảng thời gian cụ thể

Với lấy ví dụ này, chúng ta cũng có thể sử dụng 2 hàm COUNTIFS và COUNTIF.

Xem thêm: Đánh Giá Máy Lọc Nước Ao Smith, Ưu Nhược Điểm Ra Sao

*

Hình minh họa

Ví dụ 3: Đếm ngày với nhiều điều kiện dựa vào ngày hiện nay tại

Với trường thích hợp này, bạn có thể sử dụng hàm COUNTIF kết phù hợp với hàm TODAY. 

Ví dụ: dùng hàm COUNTIF để khẳng định có bao nhiêu sản phẩm đã được tải nhưng không giao hàng:

=COUNTIFS(C2:C9,””&TODAY())

*

Hình minh họa

Trên đấy là hàm đếm có đk và những cách phổ cập nhất để bạn tiến hành sử dụng những hàm đếm đó trong Excel, trường đoản cú đó, thuận lợi thống kê và phân tích số liệu một cách hiệu quả, nâng cao tối đa năng suất công việc.