Hàm đếm trong excel có điều kiện

     

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

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

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

Nhằm giúp đỡ bạn đọc riêng biệt một cách đúng đắn và vắt được cách áp dụng hai hàm đếm có đk COUNTIFS đúng trong Excel, trong nội dung bài viết này dulichthienthai.vn sẽ trình làng và hướng dẫn độc giả cách thực hiện và chọn lọc công thức công dụng nhất mang lại từng nhiệm vụ từng câu hỏi cụ thể, nhấ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ư sẽ đề cập, hàm COUNTIFS của Excel được dùng để làm đếm những ô trên nhiều phạm vi dựa trên một hoặc một số trong những điều kiện. Hàm COUNTIFS tất cả sẵn trong Excel 2016, 2013, Excel 2010 với Excel 2007, bởi vì vậy bạn có thể sử dụng những ví dụ sau đây trong ngẫu nhiên phiên bản học Excel nào.

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

Trong đó: 

- criteria_range1: Vùng chứa đk thứ nhất, đó là phần cần phải có

- criteria1: Điều kiện trước tiên (thuộc trong vùng điều kiện thứ nhất) gồm có các 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 điều kiện thứ hai( yêu cầu vùng criteria_range2 phải có độ lớn bằng nhau)

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

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

*

Hình minh họa

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

Bạn chỉ việc nhớ bạn có nhu cầu sử dụng hàm COUNTIFS trong Excel là được.

LƯU Ý: 

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

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

- trường hợp điều kiện là một tham chiếu mang lại một ô trống , hàm COUNTIFS coi nó là quý hiếm 0 (0).

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

2. Cách sử dụng 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 trong những ví dụ về công thức minh họa biện pháp sử dụng những hàm COUNTIFS trong Excel để đánh giá nhiều điều kiện.

Thông qua phần lớn ví dụ được minh họa bên dưới đây bạn sẽ hình dung ra được cách áp dụng những hàm này trong những trường hợp cụ thể tương ứng để hoàn toàn có thể áp dụng hàm COUNTIFS trong Excel để nhận xét nhiều đk khác nhau

2.1. Bí quyết đếm những ô có nhiều điều kiện

Công thức 1. Bí quyết COUNTIFS với rất nhiều điều kiện khác nhau

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

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

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

Và số lượng là 2 ("Anh đào " cùng " Chanh "):

*

Hình minh họa

Công thức 2. Bí quyết COUNTIFS bao gồm cùng tiêu chí

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

Xem thêm: Cách Bật/Tắt Chế Độ Hút Ẩm Của Điều Hòa Panasonic Dễ Nhất, Công Dụng Ra Sao

Ví dụ: đây là công thức đúng nhằm đếm những 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" vào cả nhị cột

*

Hình minh họa

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

Như chúng ta đã thấy trong các ví dụ trên, việc đếm các ô thỏa mãn nhu cầu tất cả các điều kiện đã chỉ định là dễ ợt vì hàm đếm có điều kiện COUNTIFS có thiết kế để hoạt động theo cách này.

Nhưng nếu bạn muốn đếm những ô mà ít nhất một trong những điều khiếu nại được hướng dẫn và chỉ định là TRUE , tức là dựa bên trên logic OR thì sao? nhìn chung, tất cả hai phương pháp để thực hiện vấn đề này - bằng cách thêm một số trong những công thức COUNTIFS hoặc thực hiện công thức SUM COUNTIFS.

Công thức 1. Thêm hai hoặc nhiều bí quyết trong COUNTIFS

Ví dụ: để có được số lượng đơn đặt hàng " Đã bỏ " cùng " Đang ngóng xử lý" mang lại " apple ", hãy thực hiện 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 chờ 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 review rất các điều kiện, cách trên chưa phải là cách cực tốt vì phương pháp của các bạn sẽ quá dài.

Để triển khai các phép tính tương tự như trong một công thức nhỏ dại gọn hơn, hãy liệt kê toàn bộ các điều kiện của các bạn trong một hằng số mảng và cung cấp mảng đó cho đối số điều kiện của hàm đếm gồm điều kiện COUNTIFS. 

Ta vẫn 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 " Đã bỏ " hoặc " Đang chờ xử trí " hoặc " Nhập kho ", công thức 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. Biện pháp đếm số trong vòng cụ thể

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

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

Để tìm hiểu có bao nhiêu số tự 5 cho 10 (không bao gồm 5 và 10) được chứa trong số ô từ bỏ C2 cho C10, hãy áp dụng công thức này:

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

*

Hình minh họa

Để đếm các số 5 với 10 trong những đếm, hãy thực hiện công thức "lớn rộng hoặc bằng" với "nhỏ rộng hoặc bằng":

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

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

Bạn có thể áp dụng 2 phương pháp 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 vào vùng tài liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – phương pháp đếm gồm bao nhiêu số trong đoạn từ 5 đến 10 trực thuộc vùng dữ iệu C2:C10, bao hàm cả 5 và 10.

Trong đó:

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

- bí quyết thứ 2 vứt bỏ những cố lớn hơn giá trị số lượng giới hạn trên (Ví dụ là 10)

Do vậy kết quả nhận được đang nằm trong tầm giá trị nhận thấy trên. 

Cách thực hiện tham chiếu ô trong công thức COUNTIFS

Ví dụ: cho bảng dữ 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 để cách làm vẫn đúng khi xào nấu sang những ô tính khác.

*

Hình ảnh minh họa

2.4. Cách thực hiện COUNTIFS với các ký tự đại diện

Trong các câu thức Excel, bạn cũng có thể sử dụng các ký tự đại diện thay mặt như sau:

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

- * " Được áp dụng để đếm ô cất một từ cụ thể hoặc một k1 tự vào ô.

Ví dụ bạn có danh sách những dự án vào cột A. Bạn có nhu cầu biết gồm bao nhiêu dự án công trình đã cam kết kết, tức là cột B tất cả chứa thương hiệu người. Hãy thêm một điều kiện thứ nhì - end Date vào cột D.

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

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

*

Hình minh họa

3. COUNTIFS cùng COUNTIF cùng với nhiều tiêu chuẩn ngày tháng

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

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

Xem thêm: Đường Về Quê Hương Ơi Việt Nam Nước Tôi Tôi Mong Ngày Về" (Kiếm Được 2 Bài)

*

Hình minh họa

Ví dụ 3: Đếm ngày với khá nhiều điều kiện dựa vào ngày hiện tại 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ụ: sử dụng hàm COUNTIF để khẳng định có bao nhiêu thành phầm đã được mua nhưng không giao hàng:

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

*

Hình minh họa

Trên đó là hàm đếm có đk và các cách phổ biến nhất nhằm bạn triển khai sử dụng những hàm đếm đó trong Excel, tự đó, dễ dãi thống kê cùng phân tích số liệu một bí quyết hiệu quả, nâng cấp tối đa công suất công việc.