Hàm đếm ô không trống trong excel

Microsoft Excel cung cấp rất các hàm nhằm mục đích đếm những loại ô không giống nhau, ví dụ như ô trống hoặc ô ko trống, số, tháng ngày hoặc quý giá chữ, chứa những từ hoặc cam kết tự nắm thể, v.v …

Trong bài này, Học Excel Online sẽ tập trung vào hàm COUNTIF được dùng để đếm những ô với điều kiện chỉ định. Trước tiên, họ sẽ trình diễn ngắn gọn về cú pháp cùng cách áp dụng chung, và tiếp đến là một số trong những ví dụ và lưu ý về phần nhiều điều hoàn toàn có thể xảy ra khi thực hiện hàm này với rất nhiều điều kiện và những ô sệt thù.

Bạn đang xem: Hàm đếm ô không trống trong excel

Xem nhanh

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

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

Hàm COUNTIF được sử dụng để đếm những ô trong một phạm vi quy định thỏa mãn nhu cầu một tiêu chí, hoặc đk nhất định.

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

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

COUNTIF(range, criteria)

Như chúng ta thấy, chỉ bao gồm 2 đối số, cả hai đa số được yêu cầu:

Range – xác định một hoặc những ô nhằm đếm. Bạn đặt phạm vi trong công thức như bạn thường có tác dụng trong Excel, ví dụ: A1: A20.

Criteria – xác định điều khiếu nại đế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ụ đơn giản dễ dàng nhất về hàm COUNTIF. đều gì bạn thấy trong 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. Bí quyết = COUNTIF (C2: C15, “Roger Federer”) tính mốc giới hạn Roger Federer mang tên trong danh sách:

Chú ý: Điều khiếu nại không rành mạch chữ hoa chữ thường, tức là nếu chúng ta nhập “roger federer” làm đk trong cách làm trên, thì công dụng tương tự.

Ví dụ về hàm COUNTIF

Như bạn đã thấy, cú pháp của hàm COUNTIF rất đơn giản. Mặc dù nhiên, nó được cho phép nhiều thay đổi thể của điều kiện, bao gồm các ký tự đại diện, cực hiếm của ô khác, cùng thậm chí các hàm Excel khác. Tính đa dạng này làm cho hàm COUNTIF thực sự mạnh mẽ và tương xứng với nhiều nhiệm vụ, như bạn sẽ thấy trong số ví dụ tiếp theo.

Hàm COUNTIF cho văn bạn dạng và số (chính xác tuyệt đối)

Trên thực tế, chúng ta đã trao đổi về hàm COUNTIF đếm các giá trị chữ khớp với cùng 1 điều kiện đúng mực tuyệt đối vừa rồi. Hãy để tôi đề cập lại bí quyết của ô tất cả chứa một chuỗi văn bản: = COUNTIF (C2: C15, “Roger Federer”). Vì vậy, 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 vài từ trong vệt ngoặc kép làm cho điều kiện.

Thay vì chưng nhập lại từ, chúng ta cũng có thể sử dụng tham chiếu đến bất kỳ ô nào chứa từ hoặc các 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, công thức = COUNTIF (D2: D9,5) đếm những ô có mức giá trị 5 nghỉ ngơi Cột D.

Hàm COUNTIF với ký tự đại diện (khớp một phần)

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

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

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

Đếm các ô bước đầu hoặc kết thúc bằng các ký tự độc nhất vô nhị định

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

Nếu bạn có nhu cầu biết số ô bước đầu hoặc xong bằng văn phiên bản nhất định bất kỳ có từng nào ký tự khác trong ô, hãy sử dụng các 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 ô dứt bằng chữ “ed”.

Hình hình ảnh dưới phía trên minh họa cho cách làm thứ hai:

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

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

= COUNTIF (D2: D9, “Mr ??????”) – đếm số ô bắt đầu bằng chữ “Mr” và có chính xác 8 ký kết tự trong những ô tự D2 đến D9, bao hàm khoảng cách.

Xem thêm: Game Doremon Câu Cá - Game Doraemon Hay Nhất

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

Hàm COUNTIF đến ô trống cùng không trống

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

COUNTIF ô không trống:

Trong một vài hướng dẫn hàm COUNTIF khác, chúng ta cũng có thể thấy bí quyết để tính các ô ko trống vào Excel như thế này:

= COUNTIF (range, “*”)

Nhưng thực tế là, bí quyết trên chỉ đếm những ô bao gồm chứa bất kỳ giá trị văn bạn dạng nào, tức là các ô gồm ngày tháng và số vẫn được coi là các ô trống với không được đếm!

Nếu bạn cần một công thức tổng thể hàm COUNTIF để đếm tất cả các ô chưa hẳn 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 toàn bộ các nhiều loại giá trị – văn bản, ngày và số – như chúng ta thấy trong hình mặt dưới.

COUNTIF ô trống:

Nếu bạn muốn đếm những ô trống ở 1 vùng độc nhất định, chúng ta nên sử dụng một công thức tất cả ký tự thay mặt cho những giá trị văn phiên bản hoặc một tiêu chuẩn chỉnh khác (với tiêu chí “”) nhằm đếm tất cả các ô rỗng.

Công thức tính những ô ko chứa bất kỳ văn phiên bản nào: = COUNTIF (range, “” & “*”)

Các đk được áp dụng trong bí quyết trên (“” và “*”) có nghĩa là tìm những ô không bằng *, có nghĩa là không chứa ngẫu nhiên văn phiên bản nào vào phạm vi được chỉ định.

Công thức tổng quát hàm COUNTIF cho khoảng trống (tất cả những loại giá chỉ trị): = COUNTIF (range, “”)

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

Chú ý. Hãy lưu ý rằng Microsoft Excel hỗ trợ một chức năng khác nhằm đếm những ô trống = COUNTBLANK (range). Ví dụ: những công thức tiếp sau đây sẽ mang đến kết quả đúng đắn giống như cách làm COUNTIF chúng ta thấy trong hình sinh hoạt trên:

Đếm khoảng tầm 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 để ý rằng cả hai = COUNTIF (range, “”) và = COUNTBLANK (range) đếm các ô có những công thức được thể hiện bằng ô trống. Nếu bạn không hy vọng coi các ô đó là khoảng trống, hãy sử dụng công thức này: = ROWS (C2: С11) * COLUMNS (C2: С11) -countif(C2: С11, “” và “”).

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

Để đếm những ô có mức giá trị lớn hơn, nhỏ dại hơn hoặc bằng số bạn 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ư mô tả trong bảng dưới đây.

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

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

Bạn cũng rất có thể sử dụng tất cả các phương pháp trên để đếm những ô dựa trên giá trị ô khác, chỉ việc thay nỗ lực số trong các tiêu chuẩn bằng ô tham chiếu.

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

Nếu bạn muốn đếm các ô chứa những so sánh như là một trong những phần nội dung của ô, nghĩa là các ký tự “>”, “ 5 *”) sẽ tính toàn bộ ô trong dải D2: D9 với văn bản như “Giao hàng> 5 ngày” hoặc “> 5 tất cả sẵn”.

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

Nếu bạn có nhu cầu đếm ô cùng với ngày tháng to hơn, nhỏ tuổi hơn hoặc bằng ngày chúng ta chỉ định hoặc ngày vào một ô khác, chúng ta hãy làm theo cách tương tự đã làm cho trước đó. Toàn bộ các phương pháp trên dung cho một ngày tháng như cho các con số. Sau đó là vài ví dụ:

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

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

Hàm COUNTIF cùng 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 một trong những ứng dụng thông dụng nhất của hàm COUNTIF 2 đk là đếm những con số vào một giới hạn cụ thể, tức là nhỏ hơn X nhưng to 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 tuy nhiên dưới 15 trong khoảng B2: B9.

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

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

Hãy coi một ví dụ khác của hàm COUNTIF đã đếm 2 quý hiếm văn bạn dạng khác nhau. Giả sử các bạn có một danh sách mua sắm và bạn muốn biết có bao nhiêu đồ dùng uống. Vị vậy, công thức sau được dùng:

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

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

Theo phương pháp tương tự, bạn cũng 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ề tính năng COUNTIF nhằm đếm nước chanh, nước trái cây cùng kem:

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

Sử dụng công dụng COUNTIF nhằm tìm các giá trị lặp và quý hiếm duy nhất:

Một chức năng khác của hàm COUNTIF là nhằm tìm các giá trị lặp trong một cột, thân hai cột hoặc trong một hàng.

Ví dụ 1. Tìm với đếm các giá trị lặp trong 1 cột

Ví dụ, công thức dễ dàng này = COUNTIF (B2: B10, B2)> 1 đang 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 những khi một hàm không giống = COUNTIF (D2: D10, TRUE) sẽ cho chính mình biết bao gồm bao nhiêu lần trùng lặp:

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

Nếu chúng ta có hai danh sách riêng biệt, ví dụ danh sách tên trong các cột B với C, và bạn có nhu cầu biết gồm bao nhiêu tên mở ra trong cả hai cột, bạn cũng có thể sử dụng hàm COUNTIF phối kết hợp với tính năng SUMPRODUCT để đếm số lần 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 độc nhất trong Cột C, có nghĩa là các tên không mở ra trong Cột B:

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

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

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

 Đếm mốc giới hạn lặp vào 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ông ít điều kiện

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

9 túng thiếu kíp thực hiện COUNTIF kết quả trong Excel

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