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

Microsoft Excel hỗ trợ rất các hàm nhằm mục đích đếm những 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ữ, chứa những từ hoặc cam kết tự cố gắng thể, v.v …

Trong bài bác này, học Excel Online sẽ triệu tập vào hàm COUNTIF được dùng để làm đếm những ô với đk chỉ định. Trước tiên, chúng ta sẽ trình diễn ngắn gọn về cú pháp với cách thực hiện chung, và tiếp nối là một số trong những ví dụ và cảnh báo về mọi điều hoàn toàn có thể xảy ra khi sử dụng hàm này với rất nhiều điều kiện và những ô đặc thù.Bạn vẫn xem: Điều kiện không giống rỗng trong excel

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

Hàm COUNTIF được thực hiện để đếm những ô trong một phạm vi quy định đáp ứng một tiêu chí, hoặc đk nhất định.

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


*

*

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

Cú pháp của hàm COUNTIF rất đối chọi giản:

COUNTIF(range, criteria)

Như các bạn thấy, chỉ tất cả 2 đối số, cả hai đầy đủ được yêu thương cầu:

Range – khẳng định một hoặc những ô để đếm. Các bạn đặt phạm vi trong cách làm như các bạn thường có tác dụng trong Excel, ví dụ: A1: A20.

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

Và đó là ví dụ dễ dàng nhất về hàm COUNTIF. Các gì các bạn thấy vào hình bên dưới là danh sách những tay vợt giỏi nhất vào 14 năm qua. Công thức = COUNTIF (C2: C15, “Roger Federer”) tính tần số Roger Federer có tên trong danh sách:


*

Chú ý: Điều kiện không rõ ràng chữ hoa chữ thường, tức là nếu chúng ta nhập “roger federer” làm đk trong bí quyết trên, thì hiệu quả tương tự.

Ví dụ về hàm COUNTIF

Như chúng ta đã thấy, cú pháp của hàm COUNTIF rất đơn giản. Mặc dù nhiên, nó có thể chấp nhận được nhiều phát triển thành thể của điều kiện, bao hàm các ký tự đại diện, giá trị của ô khác, và thậm chí các hàm Excel khác. Tính đa dạng chủng loại này tạo nên hàm COUNTIF thực sự khỏe mạnh và phù hợp với các nhiệm vụ, như bạn sẽ thấy trong những ví dụ tiếp theo.

Hàm COUNTIF cho văn bản và số (chính xác xuất xắc đối)

Trên thực tế, chúng ta đã trao đổi về hàm COUNTIF đếm những giá trị chữ khớp với một điều kiện đúng mực tuyệt đối vừa rồi. Hãy để tôi nhắc lại công thức của ô gồm chứa một chuỗi văn bản: = COUNTIF (C2: C15, “Roger Federer”). Vị vậy, các bạn nhập:

Vùng lựa chọn đếm là thông số đầu tiên;Dấu phẩy như vệt phân cách;Một trường đoản cú hoặc một số trong những từ trong vết ngoặc kép có tác dụng điều kiện.

Thay vày nhập lại từ, bạn có thể sử dụng tham chiếu đến bất kỳ ô nào cất từ hoặc những từ đó cùng nhận được hiệu quả tương tự, ví dụ: = COUNTIF (C1: C9, C7).

Hàm COUNTIF dùng tương tự như cho số. Như bạn thấy trong hình dưới đây, bí quyết = COUNTIF (D2: D9,5) đếm các ô có mức giá trị 5 ở Cột D.


*

Hc Excel nâng cao

Hàm COUNTIF với ký tự thay mặt (khớp một phần)

Trong ngôi trường hợp dữ liệu Excel của bạn bao gồm một vài thay đổi thể của (các) keyword mà bạn có nhu cầu tính, thì bạn cũng có thể sử dụng ký tự thay mặt để đếm tất cả các ô chứa một từ, nhiều từ hoặc vần âm nhất định như 1 phần của ngôn từ ô.

Giả sử các bạn có một danh sách những nhiệm vụ được giao cho những người khác nhau và bạn muốn biết số nhiệm vụ được giao cho Danny Brown. Bởi vì tên của Danny được viết bằng vô số cách thức khác nhau, ta nhập “* Brown *” là đk = COUNTIF (D2: D10, “*Brown *”).


*

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

Tự hễ hóa Excel bởi VBA cho những người mới

Đếm những ô bắt đầu hoặc ngừng bằng những ký tự tốt nhất định

Bạn rất có thể sử dụng ký kết tự đại diện, vết sao (*) hoặc giấu chấm hỏi (?), cùng với tùy ở trong vào kết quả mà chúng ta muốn.

Nếu bạn có nhu cầu biết số ô bước đầu hoặc kết thúc bằng văn phiên bản nhất định bất cứ có từng nào ký tự khác trong ô, hãy sử dụng những công thức sau:

= COUNTIF (C2: C10, “Mr*”) – tính các ô bước đầu bằng “Mr”.

= COUNTIF (C2: C10, “* ed”) – tính những ô ngừng bằng chữ “ed”.

Hình ảnh dưới đây minh họa cho cách làm thứ hai:


Nếu bạn đang tìm kiếm một số trong những ô bước đầu hoặc ngừng bằng các ký tự nhất định và chứa một số ký tự độc nhất vô nhị định, bạn áp dụng hàm COUNTIF với vết hỏi (?) trên điều kiện:

= COUNTIF (D2: D9, “??own”) – đếm số ô ngừng bằng chữ “own” với có đúng chuẩn 5 cam kết tự trong các ô từ bỏ D2 mang đến D9, bao hàm cả khoảng chừng cách.

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

Xem thêm:

Hàm COUNTIF cho ô trống và không trống

Các lấy một ví dụ sau bộc lộ cách bạn có thể sử dụng hàm COUNTIF trong Excel nhằm đếm số ô trống hoặc không trống trong một phạm vi được chỉ định.

Học VBA cơ bản

COUNTIF ô ko trống:

Trong một số hướng dẫn hàm COUNTIF khác, chúng ta cũng có thể thấy phương pháp để tính các ô ko trống trong Excel như thế này:

= COUNTIF (range, “*”)

Nhưng thực tế là, cách làm trên chỉ đếm các ô tất cả chứa bất kỳ giá trị văn bạn dạng nào, tức là các ô có ngày tháng với số sẽ được xem như là các ô trống và không được đếm!

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

= COUNTIF (range, “” & “”)

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


COUNTIF ô trống:

Nếu bạn muốn đếm những ô trống ở 1 vùng duy nhất định, các bạn nên thực hiện một công thức bao gồm ký tự thay mặt cho những giá trị văn bạn dạng hoặc một tiêu chuẩn chỉnh khác (với tiêu chuẩn “”) để đếm tất cả các ô rỗng.

Công thức tính các ô ko chứa ngẫu nhiên văn bản nào: = COUNTIF (range, “” & “*”)

Các đk được sử dụng trong cách làm trên (“” & “*”) tức là tìm những ô không bằng *, có nghĩa là không chứa bất kỳ văn bạn dạng nào trong phạm vi được chỉ định.

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

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

Chú ý. Hãy để ý rằng Microsoft Excel cung cấp một tính năng khác để đếm các ô trống = COUNTBLANK (range). Ví dụ: những công thức sau đây sẽ mang lại kết quả đúng mực giống như cách làm COUNTIF các bạn thấy vào hình sống trên:

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

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

Ngoài ra, xin chú ý rằng cả hai = COUNTIF (range, “”) = COUNTBLANK (range) đếm các ô có những công thức được thể hiện bởi ô trống. Nếu khách hàng không hy vọng coi những ô kia là khoảng tầm trống, hãy áp dụng công thức này: = ROWS (C2: С11) * COLUMNS (C2: С11) -countif(C2: С11, “” và “”).

COUNTIF khủng hơn, nhỏ dại hơn hoặc bằng:

Để đếm những ô có giá trị khủng hơn, nhỏ tuổi hơn hoặc bởi số chúng ta chỉ định, bạn chỉ việc thêm dấu đối chiếu tương ứng với những điểu kiện, như bộc lộ trong bảng dưới đây.

Hãy để ý rằng trong những công thức COUNTIF, một dấu so sánh với một số luôn luôn luôn được để trong vệt ngoặc kép.

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

Bạn cũng rất có thể sử dụng toàn bộ các bí quyết trên để đếm các ô dựa trên giá trị ô khác, chỉ cần thay rứa số trong các tiêu chuẩn bằng ô tham chiếu.

Chú ý. trong trường thích hợp của ô tham chiếu, chúng ta phải đặt những dấu so sánh trong vệt nháy kép và thêm một dấu hiệu và (&) trước ô tham chiếu. Ví dụ, đếm những giá trị to hơn ô D3 trong dải D2: D9, chúng ta dùng phương pháp = COUNTIF (D2: D9, “>” và D3)


Nếu bạn muốn đếm những ô chứa các so sánh như là 1 phần nội dung của ô, nghĩa là các ký trường đoản cú “>”, “ 5 *”) đã tính toàn bộ ô vào dải D2: D9 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 ô với ngày tháng phệ hơn, nhỏ hơn hoặc bởi 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ự như đã làm trước đó. Toàn bộ các phương pháp trên dung cho một ngày tháng như cho những con số. Sau đây là vài ví dụ:

Ngoài số đông công thức phổ biến này, bạn cũng có thể sử dụng hàm COUNTIF kết hợp với các hàm Excel ngày cùng giờ rõ ràng như TODAY () nhằm đếm các ô dựa trên ngày hiện tại tại.

Dưới đây là ví dụ về sử dụng các công thức trên tài liệu thực (tại thời khắc 25 tháng 6 năm 2014):


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

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

Một giữa những ứng dụng thịnh hành nhất của hàm COUNTIF 2 đk là đếm những con số vào một số lượng giới hạn cụ thể, tức là bé dại hơn X nhưng lớn hơn Y. Ví dụ, chúng ta có thể sử dụng công thức sau nhằm đếm các giá trị to hơn 5 dẫu vậy dưới 15 trong khoảng B2: B9.

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


Ví dụ 2. COUNTIF với nhiều điều kiện mang đến giá trị văn bản 

Hãy coi một ví dụ khác của hàm COUNTIF đã đếm 2 giá trị văn bạn dạng khác nhau. Giả sử các bạn có một danh sách bán buôn và bạn muốn biết có bao nhiêu đồ vật uống. Vì vậy, phương pháp sau được dùng:

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

Hãy chú ý đến ký kết tự đại diện (*) trong đk thứ hai, nó được sử dụng để đếm toàn bộ các loại nước hoa trái trong danh sách.


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

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

Sử dụng chức năng COUNTIF để tìm các giá trị lặp và giá trị duy nhất:

Một công dụng khác của hàm COUNTIF là để tìm các giá trị lặp trong một cột, thân hai cột hoặc vào một hàng.

Ví dụ 1. Tìm và đếm những giá trị lặp trong một cột

Ví dụ, công thức dễ dàng và đơn giản này = COUNTIF (B2: B10, B2)> 1 đã tìm những giá trị giống nhau với ô B2, trả kết quả TRUE/FALSE về cột Duplicate trong vòng B2: B10 trong khi một hàm không giống = COUNTIF (D2: D10, TRUE) sẽ cho mình biết tất cả bao nhiêu lần trùng lặp:


Ví dụ 2. Đếm chu kỳ lặp thân hai cột

Nếu các bạn có hai danh sách riêng biệt, ví dụ list tên trong những cột B cùng C, và bạn muốn biết bao gồm bao nhiêu tên lộ diện trong cả nhị cột, bạn cũng có thể sử dụng hàm COUNTIF phối kết hợp với tác dụng SUMPRODUCT nhằm đếm tần số lặp:

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

 Thậm chí ta rất có thể đếm bao nhiêu tên nhất trong Cột C, có nghĩa là các thương hiệu không mở ra trong Cột B:

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


Ví dụ 3. Đếm cực hiếm trùng lặp và cực hiếm duy nhất trong một hàng

 Nếu bạn có nhu cầu đếm những giá trị lặp hoặc những giá trị độc nhất vô nhị trong một hàng nào đó thay vì một cột, hãy thực hiện một trong những công thức bên dưới đây. Những công thức này rất có thể hữu ích, thí dụ, nhằm phân tích lịch sử dân tộc xổ số.

 Đếm mốc giới hạn lặp trong một hàng:

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

 Đếm các giá trị độc nhất vô nhị trong một hàng:

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


So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo khá nhiều điều kiện

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

9 túng thiếu kíp thực hiện COUNTIF công dụng trong Excel

Để hoàn toàn có thể ứng dụng tốt Excel vào vào công việc, họ không chỉ nắm rõ được những hàm mà lại còn nên sử dụng tốt cả những công ráng của Excel. đông đảo hàm nâng cao giúp áp dụng giỏi vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những chính sách thường sử dụng là Data validation, Conditional formatting, Pivot table…