Trong bài viết này, học excel trực tuyến sẽ hướng dẫn bạn cách sử dụng công thức hàm đếm và cách sử dụng hàm đếm trong excel dựa trên logic và và hoặc . bạn sẽ tìm thấy nhiều ví dụ với các loại dữ liệu khác nhau: số, ngày tháng, văn bản và ô chứa dữ liệu.
xem thêm:
- hàm đếm trong excel
- hàm counta trong excel
- đánh số tự động với bộ đếm
- tự học xuất sắc
- Bạn có thể sử dụng hàm countifs để đếm các ô trong một phạm vi với một điều kiện duy nhất, cũng như trong nhiều phạm vi có nhiều điều kiện. trong trường hợp thứ hai, chỉ ô đáp ứng tất cả các điều kiện mới được tính.
- mỗi phạm vi bổ sung phải có cùng số hàng và số cột với phạm vi đầu tiên (đối số tiêu_phí1 ).
- có thể đếm các vùng dữ liệu liền kề và không liền kề .
- nếu tiêu chí là một tham chiếu đến một ô trống , thì hàm countifs sẽ coi nó như một giá trị bằng không (0).
- Bạn có thể sử dụng các ký tự đại diện trong các tiêu chí: dấu hoa thị (*) và dấu chấm hỏi (?).
hàm countifs: công thức và sử dụng
công thức hàm countifs như sau:
= countifs (tiêu chí_ dải ô1, tiêu chí1, [dải ô tiêu chí2, tiêu chí2]…)
criteria_range1 : xác định phạm vi đầu tiên mà điều kiện đầu tiên được áp dụng (tiêu chí1), bắt buộc.
tiêu chí1 : đặt các điều kiện cho biểu mẫu số , tham chiếu ô , văn bản chuỗi , một mảng hoặc một hàm excel khác , bắt buộc. tiêu chí này xác định ô nào cần đếm và có thể được biểu thị dưới dạng 10, “& lt; = 32”, a6, “kẹo”.
[criteria_range2, criteria2]… : Đây là các điều kiện bổ sung và tùy chọn. bạn có thể nhập tối đa 127 cặp điều kiện trong công thức.
Trên thực tế, bạn không cần phải ghi nhớ cú pháp hàm countifs . Microsoft Excel sẽ hiển thị các đối số của hàm ngay khi bạn bắt đầu nhập. đối số hiện được nhập sẽ được đánh dấu.
countifs – những điều cần xem xét:
cách sử dụng hàm countifs và countif trong excel với nhiều tiêu chí:
cách đếm ô có nhiều tiêu chí (và logic):
công thức 1. công thức countifs với các tiêu chí khác nhau
Giả sử bạn có một danh sách các sản phẩm như trong hình bên dưới. bạn muốn biết số lượng hàng tồn kho (giá trị cột b lớn hơn 0) nhưng không bán được (giá trị cột c bằng 0).
công việc có thể được thực hiện bằng cách sử dụng công thức sau:
= số lượng (b2: b7, “> 0”, c2: c7, “= 0”)
kết quả là 2 (“quả anh đào” và “quả chanh”):
công thức 2. công thức countifs có cùng tiêu chí
khi bạn muốn đếm các mục có cùng tiêu chí, bạn vẫn cần cung cấp từng cặp tiêu chí / dải tiêu chí riêng lẻ.
ví dụ: đây là công thức chính xác để đếm các phần tử chứa 0 trong cả cột b và cột c:
= bộ đếm ($ b $ 2: $ b $ 7, “= 0”, $ c $ 2: $ c $ 7, “= 0”)
công thức countifs này trả về 1 vì chỉ “nho” mới có giá trị “0” trong cả hai cột.
Việc sử dụng một công thức đơn giản hơn với một phạm vi_của_bảng chẳng hạn như countifs (b2: c7, “= 0”) sẽ trả về một kết quả khác: tổng số ô trong phạm vi b2: c7 chứa 0 (trong ví dụ 4 này).
xem thêm: kiến thức chung về excel nâng cao
cách đếm ô có nhiều tiêu chí (hoặc logic):
Như bạn đã thấy trong ví dụ trên, việc đếm các ô đáp ứng tất cả các tiêu chí đã chỉ định thật dễ dàng vì hàm countifs được thiết kế để hoạt động theo cách này.
nhưng điều gì sẽ xảy ra nếu bạn muốn đếm các ô trong đó ít nhất một trong các tiêu chí được chỉ định là đúng , tức là dựa trên logic hoặc? Nói chung, có hai cách để thực hiện: thêm một số công thức countif hoặc sử dụng công thức sum countifs với một hằng số mảng.
công thức 1. thêm hai hoặc nhiều công thức countif hoặc countifs
Trong bảng sau, giả sử bạn muốn đếm các đơn hàng có trạng thái “đã hủy” và “đang chờ xử lý”, bạn có thể viết 2 công thức countif thông thường và nhận được kết quả:
= countif ($ c $ 2: $ c $ 11, ”đã hủy”) + countif ($ c $ 2: $ c $ 11, ”đang chờ xử lý”)
Trong trường hợp mỗi hàm chứa nhiều hơn một điều kiện, hãy sử dụng countifs thay vì countif . Ví dụ: để đếm các đơn đặt hàng “đã hủy” và “đang chờ xử lý” cho “táo”, hãy sử dụng 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ý”)
công thức 2. tổng của công thức đếm với ma trận không đổi
Trong tình huống bạn cần đặt nhiều tiêu chí, cách tiếp cận trên không phải là tối ưu vì công thức của bạn sẽ quá dài dòng. Để thực hiện các phép tính tương tự trong một công thức nhỏ gọn hơn, hãy liệt kê tất cả tiêu chí của bạn trong một mảng và cấp dữ liệu mảng đó cho hàm tiêu chí countifs countifs countifs countifs > mạnh>. để đếm tổng, hãy kết hợp hàm countifs bên trong hàm sum như sau:
= sum (bộ đếm (phạm vi, {“tiêu chí1”, “tiêu chí2”, “tiêu chí 3”,…}))
Trong bảng mẫu của chúng tôi, để đếm các đơn hàng có trạng thái “đã hủy”, “đang chờ xử lý” hoặc “đang vận chuyển”, công thức sẽ có dạng như sau:
= sum (countifs ($ c $ 2: $ c $ 11, {“đã hủy”, “đang chờ xử lý”, “đang chuyển tiếp”}))
Tương tự, bạn có thể đếm các ô dựa trên hai hoặc nhiều cặp tiêu_chức / tiêu chí . Ví dụ: để biết số lượng đơn đặt hàng “táo” ở trạng thái “đã hủy”, “đang chờ xử lý”, “đang vận chuyển”, hãy sử dụng công thức sau:
= sum (countifs ($ a $ 2: $ a $ 11, “apple”, $ c $ 2: $ c $ 11, {“đã hủy”, “đang chờ xử lý”, “đang chuyển tiếp”}))
cách đếm số trong một phạm vi cụ thể:
công thức 1. Hàm countifs đếm các ô trong phạm vi số
Để biết có bao nhiêu số từ 5 đến 10 (không bao gồm 5 và 10) được chứa trong các ô từ c2 đến c10, hãy sử dụng công thức sau:
= countifs (c2: c10, ”& gt; 5 ″, c2: c10,” & lt; 10 ″)
Để bao gồm cả 5 và 10, hãy sử dụng “lớn hơn hoặc bằng” và “nhỏ hơn hoặc bằng”:
= countifs (b2: b10, ”& gt; = 5 ″, b2: b10,” & lt; = 10 ″)
công thức 2. công thức countif để đếm số từ x đến y
Chúng tôi có thể sử dụng công thức countif được lồng với một hàm khác để nhận được kết quả tương tự. công thức đầu tiên đếm các số lớn hơn giá trị giới hạn dưới (ví dụ: 5). công thức thứ hai loại bỏ các số lớn hơn giới hạn trên (trong trường hợp này là 10). kết quả bạn đang tìm kiếm nằm trong phạm vi của hai giá trị trước đó.
= countif (c2: c10, ”& gt; 5 ″) – countif (c2: c10,” = 10 ″) – đếm số lượng lớn hơn 5 và nhỏ hơn 10 trong dữ liệu phạm vi nếu c2: c10.
= countif (c2: c10, ”& gt; = 5 ″) – countif (c2: c10,” & gt; 10 ″) – công thức đếm có bao nhiêu số trong phạm vi từ 5 đến 10 nằm trong dải dữ liệu c2: c10, bao gồm cả 5 và 10.
cách sử dụng tham chiếu ô trong công thức countifs
Khi sử dụng các toán tử logic như “& gt;”, “& lt;“, “& lt; =” hoặc “& gt; =” kết hợp với các tham chiếu ô trong công thức countifs , hãy tiếp tục lưu ý rằng bao gồm các toán tử trong dấu ngoặc kép và thêm dấu và (& amp;) trước tham chiếu ô để tạo chuỗi văn bản.
Trong tập dữ liệu mẫu bên dưới, hãy đếm các đơn đặt hàng “táo” có số tiền lớn hơn 200 đô la. với tiêu chí_ dải tần1 trong a2: a11 và tiêu chí_ dải ô2 trong b2: b11, bạn có thể sử dụng công thức:
= countifs ($ a $ 2: $ a $ 11, ”apple”, $ b $ 2: $ b $ 11, ”& gt; 200 ″)
Hoặc bạn có thể nhập các giá trị tiêu chí và các ô nhất định, ví dụ: f1 và f2, sau đó tham chiếu các ô đó trong công thức của bạn:
= countifs ($ a $ 2: $ a $ 11, $ f $ 1, $ b $ 2: $ b $ 11, ”& gt; & amp; $ f $ 2)
chú ý đến việc sử dụng giá trị tuyệt đối trong tiêu chí và tiêu chí_ dải ô để giữ cho công thức chính xác khi sao chép sang các ô khác.
xem thêm: khóa học excel từ cơ bản đến nâng cao
cách sử dụng số đếm với ký tự đại diện:
trong công thức excel, bạn có thể sử dụng các ký tự đại diện sau:
dấu chấm hỏi (?) – Đối sánh với bất kỳ ký tự đơn nào, sử dụng để đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định.
dấu hoa thị (*) : khớp với bất kỳ chuỗi ký tự nào, được sử dụng để đếm các ô chứa một từ cụ thể hoặc k1 ký tự trong ô.
lưu ý. Nếu bạn muốn đếm các ô chứa dấu chấm hỏi hoặc dấu hoa thị, hãy nhập dấu ngã (~) trước dấu hoa thị hoặc dấu chấm hỏi.
giả sử bạn có danh sách các dự án trong cột a. bạn muốn biết có bao nhiêu dự án đã ký, tức là cột b chứa tên của người đó. thêm điều kiện thứ hai: ngày kết thúc trong cột d.
= countifs (b2: b10, ”*”, d2: d10, ”& lt; & gt;” & ””))
Lưu ý rằng bạn không thể sử dụng ký tự đại diện trong tiêu chí thứ hai vì cột d chứa kiểu dữ liệu ngày chứ không phải kiểu văn bản. đó là lý do tại sao nó sử dụng tiêu chí “& lt; & gt;” & amp; ”” để tìm các ô chứa dữ liệu.
countifs và countif với nhiều tiêu chí ngày:
ví dụ 1. đếm ngày trong một khoảng thời gian cụ thể
Để đếm số ngày trong một khoảng thời gian cụ thể, bạn có thể sử dụng công thức countifs với hai tiêu chí hoặc kết hợp hai hàm countif với nhau.
Ví dụ: các công thức sau đếm số ngày trong các ô từ c2 đến c10, khoảng thời gian từ ngày 1 tháng 6 năm 2014 đến ngày 7 tháng 7 năm 2014 bao gồm:
= countifs (c2: c9, ”= 6/1/2014 ″, c2: c9,” & lt; = 6/7/2014 ″)
= count (c2: c9, ”= 6/1/2014 ″) – count (c2: c9,” 6/7/2014 ″)
ví dụ 2. đếm ngày với nhiều điều kiện
Tương tự, bạn có thể sử dụng công thức countifs để đếm số ngày trong các cột khác nhau đáp ứng hai hoặc nhiều điều kiện. Ví dụ: công thức sau sẽ xác định số lượng sản phẩm được mua sau ngày 20 tháng 5 và được giao sau ngày 1 tháng 6:
= countifs (c2: c9, ”> 5/1/2014 ″, d2: d9,” 6/7/2014 ″)
ví dụ 3. đếm ngày với nhiều điều kiện dựa trên ngày hiện tại
Bạn có thể sử dụng hàm hôm nay kết hợp với countif để tính toán các ngày dựa trên hôm nay.
Ví dụ: công thức countif sau với hai phạm vi dữ liệu và hai tiêu chí sẽ cho bạn biết có bao nhiêu sản phẩm đã được mua nhưng chưa được giao:
= countifs (c2: c9, ”& lt;“ & amp; today (), d2: d9, ”& gt;” & amp; today ())
Công thức này có thể chứa nhiều biến. ví dụ: bạn có thể sửa đổi nó để đếm số lượng sản phẩm đã được mua cách đây hơn một tuần và chưa được vận chuyển:
= countifs (c2: c9, ”& lt; =” & amp; today () – 7, d2: d9, ”& gt;” & amp; today ())
vậy là với bài viết này blog.hocexecl.online đã chia sẻ và hướng dẫn cách sử dụng công thức countifs và countif trong excel chi tiết hơn, nếu bạn có thắc mắc vui lòng liên hệ với chúng tôi. bình luận bên dưới bài viết để chúng tôi giải quyết mọi thắc mắc của bạn trong thời gian sớm nhất.
kiến thức bạn đang xem thuộc khóa học excel cơ bản đến nâng cao để học excel trực tuyến. Khóa học này cung cấp cho bạn kiến thức đầy đủ và hệ thống hóa về các hàm, công cụ, ứng dụng Excel trong công việc, v.v. Tham gia ngay tại: học excel trực tuyến