Điều kiện khác rỗng trong excel

Microsoft Excel cung ứng rất nhiều hàm nhằm mục tiêu mục đích đếm hồ hết các loại ô khác nhau, ví dụ như ô trống hoặc ô không trống, số, tháng ngày hoặc quý giá chữ, đựng những trường đoản cú hoặc ký trường đoản cú cụ thể, v.v …

Trong bài này, Học Excel Online đã tập trung vào hàm COUNTIF được dùng để làm đếm những ô với điều kiện hướng đẫn. Thứ nhất, bọn họ vẫn trình bày nlắp gọn gàng về cú pháp với bí quyết thực hiện tầm thường, và tiếp đến là một trong những ví dụ và cảnh báo về gần như điều hoàn toàn có thể xẩy ra khi thực hiện hàm này với khá nhiều ĐK với những ô đặc thù.

Bạn đang xem: Điều kiện khác rỗng trong excel

Hàm COUNTIF – cú pháp và biện pháp sử dụng:

Hàm COUNTIF được sử dụng nhằm đếm những ô trong một phạm vi hình thức đáp ứng một tiêu chí, hoặc điều kiện một mực.


*

*

Ví dụ: bạn có thể viết cách làm COUNTIF nhằm đưa ra bao gồm từng nào ô vào bảng tính của người sử dụng chứa số lớn hơn hoặc bé dại rộng số các bạn chỉ định. Hoặc để đếm các ô với cùng 1 trường đoản cú cụ thể hoặc ban đầu với cùng một (các) vần âm cụ thể.

Cú pháp của hàm COUNTIF hết sức 1-1 giản:

COUNTIF(range, criteria)

Nlỗi các bạn thấy, chỉ bao gồm 2 đối số, cả nhì số đông được yêu cầu:

Range – khẳng định một hoặc nhiều ô nhằm đếm. Quý khách hàng đặt phạm vi vào cách làm nhỏng bạn thường xuyên có tác dụng vào Excel, ví dụ: A1: Atrăng tròn.

Criteria – xác minh điều kiện đếm ô. Nó hoàn toàn có thể là một trong những, chuỗi văn bản, ô tyêu thích chiếu hoặc biểu thức. Ví dụ: bạn cũng có thể sử dụng những tiêu chuẩn nhỏng sau: “10”, A2, “> = 10”, “từ bỏ gì đó”.

Và đó là ví dụ đơn giản dễ dàng tuyệt nhất về hàm COUNTIF. Những gì bạn thấy vào hình dưới là danh sách mọi tay vợt xuất sắc nhất vào 14 năm vừa qua. Công thức = COUNTIF (C2: C15, “Roger Federer”) tính chu kỳ Roger Federer mang tên vào danh sách:

*

Crúc ý: Điều khiếu nại không rành mạch chữ hoa chữ hay, Tức là nếu khách hàng nhập “roger federer” làm cho ĐK trong phương pháp trên, thì kết quả tương tự.

lấy ví dụ về hàm COUNTIF

Nlỗi chúng ta đang thấy, cú pháp của hàm COUNTIF rất đơn giản và dễ dàng. Tuy nhiên, nó chất nhận được nhiều biến chuyển thể của điều kiện, bao gồm những ký kết tự thay mặt đại diện, cực hiếm của ô không giống, cùng thậm chí là những hàm Excel khác. Tính nhiều chủng loại này tạo cho hàm COUNTIF đích thực trẻ khỏe và tương xứng với rất nhiều trọng trách, như các bạn sẽ thấy trong số ví dụ tiếp sau.

Hàm COUNTIF mang lại văn bản cùng số (đúng chuẩn hay đối)

Trên thực tiễn, chúng ta đang đàm luận về hàm COUNTIF đếm những giá trị chữ khớp với cùng 1 ĐK đúng đắn hoàn hảo nhất vừa rồi. Hãy để tôi kể lại phương pháp của ô có cất một chuỗi văn bản: = COUNTIF (C2: C15, “Roger Federer”). Vì vậy, bạn nhập:

Vùng chọn đếm là ttê mê số đầu tiên;Dấu phẩhệt như lốt phân cách;Một tự hoặc một số từ trong vệt ngoặc kép làm cho ĐK.

Ttuyệt vày nhập lại từ, chúng ta có thể sử dụng tmê say chiếu mang đến ngẫu nhiên ô nào cất từ hoặc các tự đó với cảm nhận hiệu quả tựa như, ví dụ: = COUNTIF (C1: C9, C7).

Hàm COUNTIF dùng tựa như mang lại số. Nlỗi chúng ta thấy trong hình sau đây, cách làm = COUNTIF (D2: D9,5) đếm các ô có giá trị 5 sinh sống Cột D.

*

Hc Excel nâng cao

Hàm COUNTIF cùng với ký kết trường đoản cú thay mặt (khớp một phần)

Trong ngôi trường hòa hợp tài liệu Excel của công ty gồm một vài biến hóa thể của (các) từ khoá mà bạn muốn tính, thì chúng ta có thể thực hiện cam kết từ thay mặt đại diện nhằm đếm toàn bộ các ô đựng một trường đoản cú, nhiều từ bỏ hoặc vần âm nhất định như 1 phần của câu chữ ô.

Giả sử chúng ta bao gồm một list các nhiệm vụ được giao cho những người khác nhau với bạn có nhu cầu biết số trọng trách được giao cho Danny Brown. Bởi vì thương hiệu của Danny được viết bằng vô số phương pháp khác nhau, ta nhập “* Brown *” là điều kiện = COUNTIF (D2: D10, “*Brown *”).

*

Dấu hoa thị (*) được sử dụng thay thế sửa chữa các cực hiếm đầu với cuối, nhỏng được minc họa trong ví dụ trên. Nếu bạn cần phải khớp từng ký kết tự solo, hãy nhập một lốt hỏi (?), như được trình diễn dưới đây.

Tự động hóa Excel bằng VBA cho người mới

Đếm những ô bắt đầu hoặc kết thúc bằng các ký kết trường đoản cú tuyệt nhất định

Bạn có thể sử dụng ký kết trường đoản cú đại diện thay mặt, dấu sao (*) hoặc dấu chấm hỏi (?), với tùy trực thuộc vào kết quả nhưng mà bạn có nhu cầu.

Nếu bạn muốn biết số ô bước đầu hoặc hoàn thành bởi văn bản khăng khăng bất kể gồm từng nào ký tự khác vào ô, hãy thực hiện những bí quyết sau:

= COUNTIF (C2: C10, “Mr*”) – tính các ô bắt đầu bởi “Mr”.

= COUNTIF (C2: C10, “* ed”) – tính các ô ngừng bằng văn bản “ed”.

Tấm hình sau đây minc họa mang lại bí quyết sản phẩm hai:

*

Nếu ai đang search tìm một số trong những ô bắt đầu hoặc kết thúc bởi các ký kết tự một mực cùng cất một số cam kết từ nhất định, bạn thực hiện hàm COUNTIF cùng với vết hỏi (?) tại điều kiện:

= COUNTIF (D2: D9, “??own”) – đếm số ô chấm dứt bằng chữ “own” với bao gồm đúng đắn 5 ký từ trong những ô tự D2 mang đến D9, bao gồm cả khoảng cách.

= COUNTIF (D2: D9, “Mr ??????”) – đếm số ô bắt đầu bằng văn bản “Mr” cùng gồm đúng mực 8 ký trường đoản cú trong số ô tự D2 cho D9, bao gồm khoảng cách.

Xem thêm: Làm Cách Tính Tổng Số Giờ Trong Excel ? Cộng Tổng Thời Gian Quá 24 Giờ Trong Excel

Mẹo. Để tra cứu số ô cất vệt hỏi “?” hoặc dấu hoa thị “*”, hãy gõ vết bổ (~) trước dấu ? hoặc * vào công thức. Ví dụ, = COUNTIF (D2: D9, “* ~? *”) để đếm tất cả các ô có chứa dấu chnóng hỏi trong dải D2: D9.

Hàm COUNTIF cho ô trống cùng không trống

Các ví dụ sau diễn đạt giải pháp bạn cũng có thể sử dụng hàm COUNTIF vào Excel nhằm đếm số ô trống hoặc ko trống trong một phạm vi được hướng dẫn và chỉ định.

Học VBA cơ bản

COUNTIF ô không trống:

Trong một số khuyên bảo hàm COUNTIF khác, bạn cũng có thể thấy cách làm để tính các ô ko trống trong Excel như vậy này:

= COUNTIF (range, “*”)

Nhưng thực tế là, bí quyết bên trên chỉ đếm những ô gồm đựng ngẫu nhiên giá trị vnạp năng lượng phiên bản làm sao, Tức là các ô có ngày tháng cùng số sẽ được xem là các ô trống và không được đếm!

Nếu bạn phải một công thức tổng quát hàm COUNTIF nhằm đếm toàn bộ các ô không phải là ô trống vào một phạm vi xác định, hãy dùng:

= COUNTIF (range, “” & “”)

Công thức này hoạt động đúng đắn với tất cả những loại giá trị – văn uống bạn dạng, ngày và số – nlỗi các bạn thấy vào hình dưới.

*

COUNTIF ô trống:

Nếu bạn muốn đếm các ô trống ở 1 vùng nhất mực, chúng ta nên sử dụng một công thức gồm ký từ đại diện cho những giá trị vnạp năng lượng bạn dạng hoặc một tiêu chuẩn chỉnh không giống (cùng với tiêu chuẩn “”) để đếm toàn bộ những ô rỗng.

Công thức tính những ô không cất ngẫu nhiên vnạp năng lượng phiên bản nào: = COUNTIF (range, “” & “*”)

Các điều kiện được áp dụng vào cách làm trên (“” & “*”) có nghĩa là tìm kiếm những ô ko bằng *, tức là không chứa ngẫu nhiên văn uống bản như thế nào vào phạm vi được hướng dẫn và chỉ định.

Công thức tổng thể hàm COUNTIF mang đến không gian (toàn bộ những loại giá trị): = COUNTIF (range, “”)

Công thức trên đếm số, ngày và quý giá văn phiên bản. ví dụ như, bí quyết = COUNTIF (C2: C11, “”) trả về số của tất cả những ô trống trong dải C2: C11.

Chụ ý. Hãy xem xét rằng Microsoft Excel cung cấp một công dụng khác nhằm đếm các ô trống = COUNTBLANK (range). Ví dụ: các phương pháp tiếp sau đây đang cho tác dụng chính xác y hệt như cách làm COUNTIF bạn thấy trong hình sinh hoạt trên:

Đếm khoảng chừng trống: = COUNTBLANK (C2: C11)

Đếm không khoảng cách: = ROWS (C2: C11) * COLUMNS (C2: C11) -COUNTBLANK (C2: C11)

Ngoài ra, xin xem xét rằng cả hai = COUNTIF (range, “”) = COUNTBLANK (range) đếm các ô bao gồm những cách làm được mô tả bằng ô trống. Nếu bạn không muốn coi các ô chính là không gian, hãy sử dụng bí quyết này: = ROWS (C2: С11) * COLUMNS (C2: С11) -countif(C2: С11, “” & “”).

COUNTIF to hơn, nhỏ tuổi hơn hoặc bằng:

Để đếm các ô có mức giá trị lớn hơn, bé dại rộng hoặc bằng số bạn chỉ định và hướng dẫn, chúng ta chỉ việc thêm lốt đối chiếu khớp ứng với các điểu khiếu nại, nlỗi diễn tả vào bảng sau đây.

Hãy lưu ý rằng trong những phương pháp COUNTIF, một lốt so sánh với một số luôn luôn luôn được đặt vào vệt ngoặc kép.

Điều kiệnCông thức ví dụDiễn giải
Đếm nếu bự hơn=COUNTIF(A2:A10,”>5″)Đếm đông đảo ô có mức giá trị to hơn 5
Đếm nếu như nhỏ nhắn hơn=COUNTIF(A2:A10,”5″)Đếm hồ hết ô có mức giá trị không giống 5
Đếm nếu như bởi hoặc bự hơn=COUNTIF(C2:C8,”>=5″)Đếm đều ô có mức giá trị to hơn hoặc bằng 5
Đếm ví như bởi hoặc bé hơn=COUNTIF(C2:C8,”

Quý khách hàng cũng rất có thể thực hiện tất cả các cách làm trên để đếm những ô dựa trên giá trị ô khác, chỉ việc thay thế số trong số tiêu chí bởi ô tđắm say chiếu.

Chụ ý. Trong ngôi trường phù hợp của ô tsay đắm chiếu, bạn phải đặt các vệt so sánh vào vết nháy knghiền với thêm một dấu hiệu và (&) trước ô tđắm say chiếu. lấy ví dụ, đếm các giá trị lớn hơn ô D3 trong dải D2: D9, bạn sử dụng công thức = COUNTIF (D2: D9, “>” & D3)

*

Nếu bạn có nhu cầu đếm các ô đựng các đối chiếu như thể một phần văn bản của ô, tức thị những ký trường đoản cú “>”, “ 5 *”) sẽ tính tất cả ô vào dải D2: D9 cùng với ngôn từ như “Giao hàng> 5 ngày” hoặc “> 5 bao gồm sẵn”.

Sử dụng hàm COUNTIF với ngày:

Nếu bạn muốn đếm ô cùng với ngày tháng to hơn, bé dại rộng hoặc bằng ngày bạn chỉ định hoặc ngày vào một ô khác, các bạn hãy làm theo cách tương tự sẽ làm cho trước đó. Tất cả các phương pháp trên dung cho 1 ngày tháng nhỏng cho các số lượng. Sau đó là vài ví dụ:

Ngoài số đông cách làm phổ cập này, chúng ta cũng có thể áp dụng hàm COUNTIF kết phù hợp với các hàm Excel ngày với giờ ví dụ nlỗi TODAY () để đếm những ô dựa trên ngày hiện nay.

Dưới đây là ví dụ về sử dụng các bí quyết bên trên tài liệu thực (trên thời gian 25 mon 6 năm 2014):

*

Hàm COUNTIF cùng với nhiều điều kiện:

ví dụ như 1. COUNTIF cùng với nhị điều kiện về số

Một trong những vận dụng phổ cập tốt nhất của hàm COUNTIF 2 điều kiện là đếm những con số trong một giới hạn cụ thể, có nghĩa là bé dại rộng X dẫu vậy lớn hơn Y. lấy một ví dụ, chúng ta có thể áp dụng cách làm sau để đếm các cực hiếm to hơn 5 nhưng mà dưới 15 trong khoảng B2: B9.

= COUNTIF (B2: B9, “> 5”) – COUNTIF (B2: B9, “> = 15”)

*

Ví dụ 2. COUNTIF với nhiều ĐK mang lại cực hiếm văn bản 

Hãy xem một ví dụ không giống của hàm COUNTIF đã đếm 2 quý hiếm văn bản khác biệt. Giả sử các bạn tất cả một list sắm sửa và bạn có nhu cầu biết gồm bao nhiêu thức uống. Vì vậy, bí quyết sau được dùng:

 = COUNTIF (B2: B13, “Lemonade”) + COUNTIF (B2: B13, “* juice”)

Hãy để ý đến ký từ bỏ đại diện thay mặt (*) vào điều kiện lắp thêm hai, nó được áp dụng để đếm toàn bộ những nhiều loại nước hoa trái vào list.

*

Theo biện pháp tựa như, chúng ta có thể viết một cách làm COUNTIF cùng với một số điều kiện. Dưới đây là ví dụ về tác dụng COUNTIF nhằm đếm nước ckhô giòn, nước hoa trái với kem:

= COUNTIF (B2: B13, “Lemonade”) + COUNTIF (B2: B13, “* juice”) + COUNTIF (B2: B13, “Ice cream”)

Sử dụng công dụng COUNTIF để tra cứu những quý hiếm lặp với quý giá duy nhất:

Một tính năng không giống của hàm COUNTIF là để kiếm tìm những giá trị lặp trong một cột, thân nhì cột hoặc trong một hàng.

ví dụ như 1. Tìm với đếm các quý giá lặp trong một cột

ví dụ như, công thức đơn giản này = COUNTIF (B2: B10, B2)> 1 sẽ tìm kiếm các giá trị đụng hàng với ô B2, trả hiệu quả TRUE/FALSE về cột Duplicate trong vòng B2: B10 trong lúc một hàm khác = COUNTIF (D2: D10, TRUE) sẽ cho bạn biết bao gồm bao nhiêu lần trùng lặp:

*

lấy ví dụ như 2. Đếm tần số lặp thân nhị cột

Nếu chúng ta gồm hai danh sách cá biệt, ví dụ list thương hiệu trong những cột B với C, cùng bạn có nhu cầu biết tất cả từng nào thương hiệu lộ diện trong cả nhì cột, bạn có thể áp dụng hàm COUNTIF kết hợp với tính năng SUMPRODUCT nhằm đếm chu kỳ lặp:

 = SUMPRODUCT ((COUNTIF (B2: B1000, C2: C1000)> 0) * (C2: C1000 “”))

 Thậm chí ta hoàn toàn có thể đếm bao nhiêu tên duy nhất trong Cột C, có nghĩa là những tên không xuất hiện trong Cột B:

= SUMPRODUCT ((COUNTIF (B2: B1000, C2: C1000) = 0) * (C2: C1000 “”))

*

ví dụ như 3. Đếm giá trị trùng lặp cùng quý giá độc nhất trong một hàng

 Nếu bạn có nhu cầu đếm những cực hiếm lặp hoặc các quý giá tốt nhất vào một hàng làm sao đó thay vị một cột, hãy áp dụng một trong những phương pháp dưới đây. Những phương pháp này có thể bổ ích, tỉ dụ, nhằm so với lịch sử dân tộc xổ số kiến thiết.

 Đếm chu kỳ lặp vào một hàng:

 = SUMPRODUCT ((COUNTIF (A2: I2, A2: I2)> 1) * (A2: I2 “”))

 Đếm những quý hiếm duy nhất trong một hàng:

 = SUMPRODUCT ((COUNTIF (A2: I2, A2: I2) = 1) * (A2: I2 “”))

*

So sánh hàm COUNTIFS với SUMPRODUCT vào câu hỏi đếm theo nhiều điều kiện

Cách sử dụng hàm COUNTIFS và COUNTIF với khá nhiều điều kiện

9 bí mật thực hiện COUNTIF kết quả vào Excel

Để có thể áp dụng xuất sắc Excel vào vào công việc, họ không chỉ có nắm rõ được các hàm mà hơn nữa yêu cầu thực hiện xuất sắc cả những nguyên tắc của Excel. Những hàm cải thiện góp áp dụng xuất sắc vào các bước nhỏng SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những hình thức thường xuyên sử dụng là Data validation, Conditional formatting, Pivot table…