Hướng dẫn này trình bày cách sử dụngExcel Hàm COUNTIF và COUNTIFShàng tấn trong Excel để đếm dữ liệu đáp ứng các tiêu chí nhất định.
Tổng quan về hàm COUNTIF
Bạn có thể sử dụng hàm COUNTIF trong Excel để đếm các ô chứa một giá trị cụ thể, đếm các ô lớn hơn hoặc bằng một giá trị, v.v.
(Chú ý cách các đầu vào công thức xuất hiện)
Cú pháp và đối số của hàm COUNTIF:
= COUNTIF (phạm vi, tiêu chí)
phạm vi - Phạm vi ô cần đếm.
tiêu chuẩn - Các tiêu chí kiểm soát ô nào nên được đếm.
Hàm COUNTIF là gì?
Hàm COUNTIF là một trong những hàm cũ hơn được sử dụng trong bảng tính. Nói một cách dễ hiểu, thật tuyệt vời khi quét một phạm vi và cho bạn biết có bao nhiêu ô đáp ứng điều kiện đó. Chúng ta sẽ xem xét cách hoạt động của hàm với văn bản, số và ngày tháng; cũng như một số tình huống khác có thể phát sinh.
Ví dụ cơ bản
Hãy bắt đầu bằng cách xem danh sách các mục ngẫu nhiên này. Chúng tôi có một số số, ô trống và một số chuỗi văn bản.
Nếu bạn muốn biết có bao nhiêu mục đối sánh chính xác với tiêu chí, bạn có thể chỉ định những gì bạn muốn tìm kiếm làm đối số thứ hai. Ví dụ về công thức này có thể trông giống như
= COUNTIF (A2: A9, "Quả táo")
Công thức này sẽ trả về số 3, vì có 3 ô trong phạm vi của chúng tôi đáp ứng tiêu chí đó. Ngoài ra, chúng ta có thể sử dụng tham chiếu ô thay vì mã hóa cứng một giá trị. Nếu chúng ta đã viết "Apple" trong ô G2, chúng ta có thể thay đổi công thức thành
= COUNTIF (A2: A9, G2)
Khi xử lý số, điều quan trọng là phải phân biệt giữa số và số được lưu trữ dưới dạng văn bản. Nói chung, bạn không đặt dấu ngoặc kép xung quanh số khi viết công thức. Vì vậy, để viết một công thức kiểm tra số 5, bạn sẽ viết
= COUNTIF (A2: A9, 5)
Cuối cùng, chúng tôi cũng có thể kiểm tra các ô trống bằng cách sử dụng một chuỗi có độ dài bằng không. Chúng tôi sẽ viết công thức đó là
= COUNTIF (A2: A9, "")
Ghi chú: Công thức này sẽ đếm cả những ô thực sự trống, cũng như những ô trống do kết quả của một công thức, chẳng hạn như hàm IF.
Đối sánh từng phần
Hàm COUNTIF hỗ trợ việc sử dụng các ký tự đại diện, “*” hoặc “?”, Trong tiêu chí. Hãy xem danh sách các cửa hàng bánh ngon này:
Để tìm tất cả các mục bắt đầu với Apple, chúng ta có thể viết "Apple *". Vì vậy, để nhận được câu trả lời là 3, công thức của chúng tôi trong D2 là
= COUNTIF (A2: A5, "Apple *")
Ghi chú: Hàm COUNTIF không phân biệt chữ hoa chữ thường, vì vậy bạn cũng có thể viết “apple *” nếu muốn.
Quay lại với các món nướng của chúng tôi, chúng tôi cũng có thể muốn tìm hiểu xem chúng tôi có bao nhiêu loại bánh nướng trong danh sách của mình. Chúng tôi có thể tìm thấy điều đó bằng cách đặt ký tự đại diện ở đầu cụm từ tìm kiếm và viết
= COUNTIF (A2: A5, "* pie")
Công thức này cho kết quả là 2.
Chúng tôi cũng có thể sử dụng ký tự đại diện để kiểm tra bất kỳ ô nào có văn bản. Hãy quay lại danh sách dữ liệu ban đầu của chúng tôi.
Để đếm số ô có ít nhất một số văn bản, do đó không đếm số hoặc ô trống, chúng ta có thể viết
= COUNTIF (A2: A9, "*")
Bạn có thể thấy rằng công thức của chúng tôi trả về chính xác kết quả là 4.
Toán tử so sánh trong COUNTIF
Khi viết tiêu chí cho đến nay, chúng tôi ngụ ý rằng toán tử so sánh của chúng tôi là “=”. Trên thực tế, chúng tôi có thể đã viết điều này:
= COUNTIF (A2: A9, "= Apple")
Tuy nhiên, đó là một ký tự thừa cần viết ra, vì vậy nó thường bị bỏ qua. Tuy nhiên, điều này có nghĩa là bạn có thể sử dụng các toán tử khác như lớn hơn, nhỏ hơn hoặc không bằng. Hãy xem danh sách các độ tuổi được ghi lại này:
Nếu chúng ta muốn biết có bao nhiêu đứa trẻ ít nhất 5 tuổi, chúng ta có thể viết ra một phép so sánh "lớn hơn hoặc bằng" như sau:
= COUNTIF (A2: A8, "> = 5")
Ghi chú: Toán tử so sánh luôn được cung cấp dưới dạng một chuỗi văn bản và do đó phải nằm trong dấu ngoặc kép.
Tương tự, bạn cũng có thể kiểm tra các mục nhỏ hơn một giá trị nhất định. Nếu chúng ta cần tìm xem có bao nhiêu bé hơn 8, chúng ta có thể viết ra
= COUNTIF (A2: A8, "<8")
Điều này cho chúng ta kết quả mong muốn là 5. Bây giờ, hãy tưởng tượng rằng tất cả những đứa trẻ 6 tuổi đang đi chơi. Có bao nhiêu đứa trẻ sẽ còn lại? Chúng ta có thể tìm ra điều này bằng cách sử dụng phép so sánh “không bằng” như sau:
= COUNTIF (A2: A8, "6")
Bây giờ chúng ta có thể nhanh chóng thấy rằng chúng ta có 6 đứa trẻ chưa tròn 6 tuổi.
Trong các ví dụ so sánh này cho đến nay, chúng tôi đã cố gắng mã hóa các giá trị mà chúng tôi muốn. Bạn cũng có thể sử dụng tham chiếu ô. Mẹo là bạn cần nối toán tử so sánh với tham chiếu ô. Giả sử chúng tôi đặt số 7 vào ô C2 và chúng tôi muốn công thức của chúng tôi ở ô D2 cho biết có bao nhiêu trẻ em dưới 7 tuổi.
Công thức của chúng ta trong D2 cần phải trông như thế này:
= COUNTIF (A2: A8, "<" & C2)
Ghi chú: Đặc biệt chú ý khi viết các công thức này xem bạn cần đặt một mục bên trong dấu ngoặc kép hay bên ngoài. Các toán tử luôn ở bên trong dấu ngoặc kép, tham chiếu ô luôn nằm bên ngoài dấu ngoặc kép. Các con số ở bên ngoài nếu bạn đang thực hiện một phép so khớp chính xác, nhưng bên trong nếu bạn thực hiện một toán tử so sánh.
Làm việc với ngày tháng
Chúng tôi đã thấy cách bạn có thể đưa ra một văn bản hoặc số làm tiêu chí, nhưng còn khi chúng tôi cần làm việc với ngày tháng thì sao? Dưới đây là danh sách mẫu nhanh mà chúng tôi có thể làm việc với:
Để đếm xem sau ngày 4 tháng 5 có bao nhiêu ngày, chúng ta cần phải thận trọng. Máy tính lưu trữ ngày tháng dưới dạng số, vì vậy chúng ta cần đảm bảo máy tính sử dụng đúng số. Nếu chúng tôi viết công thức này, chúng tôi sẽ nhận được kết quả chính xác?
= COUNTIF (A2: A9, "
Câu trả lời là "có thể". Bởi vì chúng tôi đã bỏ qua năm khỏi tiêu chí của mình, máy tính sẽ cho rằng chúng tôi có nghĩa là năm hiện tại. Nếu tất cả các ngày mà chúng tôi đang làm việc là cho năm hiện tại, thì chúng tôi sẽ nhận được câu trả lời chính xác. Tuy nhiên, nếu có một số ngày trong tương lai, chúng tôi sẽ nhận được câu trả lời sai. Ngoài ra, khi năm tiếp theo bắt đầu, công thức này sẽ trả về một kết quả khác. Như vậy, có lẽ nên tránh cú pháp này.
Vì có thể khó viết ngày một cách chính xác trong một công thức, nên cách tốt nhất là viết ngày bạn muốn sử dụng trong một ô và sau đó bạn có thể sử dụng tham chiếu ô đó trong công thức COUNTIF của mình. Vì vậy, hãy viết ngày 7 tháng 5 năm 2020 vào ô C2 và sau đó chúng ta có thể đặt công thức của mình vào C4.
Công thức trong C4 là
= COUNTIF (A2: A9, "<" & C2)
Bây giờ chúng ta biết rằng kết quả của 7 là đúng và câu trả lời sẽ không thay đổi bất ngờ nếu chúng ta mở bảng tính này trong tương lai.
Trước khi chúng ta rời khỏi phần này, chúng ta thường sử dụng hàm TODAY khi làm việc với ngày tháng. Chúng ta có thể sử dụng nó giống như chúng ta sử dụng một tham chiếu ô. Ví dụ: chúng ta có thể thay đổi công thức trước đó thành:
= COUNTIF (A2: A9, "<" & TODAY ())
Bây giờ công thức của chúng tôi sẽ vẫn cập nhật khi thời gian thực diễn ra và chúng tôi sẽ có số lượng các mục ít hơn ngày hôm nay.
Nhiều tiêu chí và COUNTIFS
Hàm COUNTIF ban đầu được cải tiến vào năm 2007 khi COUNTIFS ra mắt. Cú pháp giữa cả hai rất giống nhau, với cú pháp sau cho phép bạn cung cấp các phạm vi và tiêu chí bổ sung. Bạn có thể dễ dàng sử dụng COUNTIFS trong bất kỳ trường hợp nào mà COUNTIF tồn tại. Nó chỉ là một ý kiến hay khi biết rằng cả hai chức năng đều tồn tại.
Hãy xem bảng dữ liệu này:
Để biết có bao nhiêu người đang ở mức trả tiền từ 1 đến 2, bạn có thể viết một bản tóm tắt các hàm COUNTIF như sau:
= COUNTIF (B2: B7, "> = 1") - COUNTIF (B2: B7, "> 2")
Công thức này sẽ hoạt động, vì bạn đang tìm mọi thứ trên 1, nhưng sau đó trừ số bản ghi vượt quá điểm giới hạn của bạn. Ngoài ra, bạn có thể sử dụng COUNTIFS như thế này:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Cái sau trực quan hơn để đọc, vì vậy bạn có thể muốn sử dụng tuyến đường đó. Ngoài ra, COUNTIFS mạnh hơn khi bạn cần xem xét nhiều cột. Giả sử chúng tôi muốn biết có bao nhiêu người trong Ban quản lý và ở Cấp độ thanh toán 1. Bạn không thể làm điều đó chỉ với COUNTIF; bạn cần phải viết ra
= COUNTIFS (A2: A7, "Quản lý", B2: B7, 1)
Công thức này sẽ cung cấp cho bạn kết quả chính xác là 2. Trước khi chúng ta rời khỏi phần này, hãy xem xét logic Hoặc loại. Điều gì sẽ xảy ra nếu chúng tôi muốn biết có bao nhiêu người trong Ban quản lý hoặc? Bạn sẽ cần thêm một số COUNTIFS lại với nhau, nhưng có hai cách để thực hiện việc này. Cách đơn giản hơn là viết nó như sau:
= COUNTIF (A2: A7, "HR") + COUNTIF (A2: A7, "Management")
Bạn cũng có thể sử dụng một mảng và viết công thức mảng sau:
= SUM (COUNTIF (A2: A7, {"HR", "Management"}))
Ghi chú: Công thức mảng phải được xác nhận bằng cách sử dụng `Ctrl + Shift + Enter` chứ không chỉ` Enter`.
Công thức này sẽ hoạt động như thế nào là nó sẽ thấy rằng bạn đã cung cấp một mảng làm đầu vào. Do đó, nó sẽ tính toán kết quả cho hai hàm COUNTIF khác nhau và lưu trữ chúng trong một mảng. Sau đó, hàm SUM sẽ cộng tất cả các kết quả trong mảng của chúng ta lại với nhau để tạo ra một đầu ra duy nhất. Do đó, công thức của chúng ta sẽ được đánh giá như vậy:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Đếm các giá trị duy nhất
Bây giờ chúng ta đã thấy cách sử dụng một mảng với hàm COUNTIF, chúng ta có thể thực hiện thêm một bước nữa để giúp chúng ta đếm có bao nhiêu giá trị duy nhất trong một phạm vi. Trước tiên, hãy xem lại danh sách các Phòng ban của chúng tôi.
= SUM (1 / COUNTIF (A2: A7, A2: A7))
Chúng ta có thể thấy rằng có 6 ô giá trị dữ liệu, nhưng chỉ có 3 mục khác nhau. Để giải toán, chúng ta cần mỗi mục có giá trị là 1 / N, trong đó N là số lần một mục được lặp lại. Ví dụ: nếu mỗi HR chỉ có giá trị 1/2, thì khi bạn cộng chúng lên, bạn sẽ nhận được số lượng là 1, cho 1 giá trị duy nhất.
Quay lại COUNTIF của chúng tôi, được thiết kế để tìm ra số lần một mục xuất hiện trong một phạm vi. Trong D2, chúng tôi sẽ viết công thức mảng
= SUM (1 / COUNTIF (A2: A7, A2: A7))
Công thức này sẽ hoạt động như thế nào, đối với mỗi ô trong phạm vi A2: A7, nó sẽ kiểm tra xem nó xuất hiện bao nhiêu lần. Với mẫu của chúng tôi, điều này sẽ tạo ra một loạt các
{2, 2, 3, 3, 3, 1}
Sau đó, chúng ta chuyển tất cả những số đó thành phân số bằng cách thực hiện một số phép chia. Bây giờ mảng của chúng tôi trông giống như
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Khi chúng ta cộng tất cả những thứ này lại với nhau, chúng ta sẽ nhận được kết quả mong muốn là 3.
Countif với hai hoặc nhiều điều kiện - Hàm Countifs
Cho đến nay, chúng tôi chỉ làm việc với Hàm COUNTIF. Hàm COUNTIF chỉ có thể xử lý một tiêu chí tại một thời điểm. Để COUNTIF với nhiều tiêu chí, bạn cần sử dụng Hàm COUNTIFS. COUNTIFS hoạt động chính xác như COUNTIF. Bạn chỉ cần thêm tiêu chí phụ. Hãy xem ví dụ dưới đây.
= COUNTIFS (B2: B7, "= 130")
COUNTIF & COUNTIFS trong Google Trang tính
Hàm COUNTIF & COUNTIFS hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
