Excel có nhiều hàm mà người dùng cần chỉ định một hoặc nhiều điều kiện để nhận được kết quả.Ví dụ: nếu bạn muốn đếm ô dựa trên nhiều tiêu chí, bạn có thể sử dụng hàm COUNTIF hoặc COUNTIFS trong Excel.
Hướng dẫn này bao gồm nhiều cách khác nhau để sử dụng một hoặc nhiều tiêu chí trong các hàm COUNTIF và COUNTIFS của Excel.
Trong khi tôi sẽ tập trung chủ yếu vào các hàm COUNTIF và COUNTIFS trong hướng dẫn này, tất cả các ví dụ này cũng có thể được sử dụng với các hàm Excel khác lấy nhiều tiêu chí làm đầu vào (chẳng hạn như SUMIF, SUMIFS, AVERAGEIF và AVERAGEIFS).
Nội dung
- 1 Giới thiệu về hàm COUNTIF và COUNTIFS trong Excel
- 2 Sử dụng điều kiện NUMBER trong hàm COUNTIF của Excel
- 3 Sử dụng điều kiện TEXT trong các hàm Excel
- 4 Sử dụng Điều kiện DATE trong Hàm COUNTIF và COUNTIFS trong Excel
- 5 Sử dụng các ký tự đại diện trong các điều kiện của hàm COUNTIF và COUNTIFS
- 6 Ồ, xin chào, rất vui được làm quen với bạn.
Giới thiệu về hàm COUNTIF và COUNTIFS trong Excel
Đầu tiên chúng ta hãy hiểu cách sử dụng các hàm COUNTIF và COUNTIFS trong Excel.
Hàm COUNTIF trong Excel (với một tiêu chí duy nhất)
Hàm COUNTIF trong Excel hoạt động tốt nhất khi bạn muốn đếm ô dựa trên một tiêu chí duy nhất.Nếu bạn muốn đếm dựa trên nhiều tiêu chí, hãy sử dụng hàm COUNTIFS.
句法
= COUNTIF (phạm vi, điều kiện)
Thông số đầu vào
- phạm vi – Phạm vi ô bạn muốn tính toán.
- điều kiện –Tiêu chí phải được đánh giá dựa trên phạm vi ô sẽ được đếm.
Hàm COUNTIFS trong Excel (với nhiều tiêu chí)
Hàm COUNTIFS trong Excel hoạt động tốt nhất khi bạn muốn đếm ô dựa trên nhiều tiêu chí.
句法
= COUNTIFS (tiêu chí_ dải ô1, tiêu chí1, [dải ô tiêu chí2, tiêu chí2]…)
Thông số đầu vào
- tiêu_phí1 - Phạm vi ô bạn muốn đánh giá theo tiêu chí 1.
- tiêu chí1 - Tiêu chí bạn muốn đánh giá là criteria_range1 để xác định các ô cần đánh giá.
- [tiêu chí_phạm vi2] - Phạm vi ô bạn muốn đánh giá theo tiêu chí 2.
- [tiêu chí2] - Tiêu chí bạn muốn đánh giá là criteria_range2 để xác định các ô cần đếm.
Bây giờ chúng ta hãy xem xét một số ví dụ về việc sử dụng nhiều tiêu chí trong hàm COUNTIF của Excel.
Sử dụng điều kiện NUMBER trong hàm COUNTIF của Excel
# 1 Đếm ô khi tiêu chí bằng một giá trị
Để có được số lượng ô mà tham số điều kiện bằng giá trị được chỉ định, bạn có thể nhập điều kiện trực tiếp hoặc sử dụng tham chiếu ô có chứa điều kiện.
Dưới đây là một ví dụ trong đó chúng tôi đếm các ô có chứa số 9 (có nghĩa là tham số điều kiện bằng 9).Đây là công thức:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Trong ví dụ trên (trong hình), tiêu chí nằm trong ô D3.Bạn cũng có thể nhập điều kiện trực tiếp vào công thức.Ví dụ: bạn cũng có thể sử dụng:
= COUNTIF ($ B $ 2: $ B $ 11,9)
# 2 Đếm ô khi điều kiện lớn hơn giá trị
Để có được số lượng ô có giá trị lớn hơn một giá trị được chỉ định, chúng ta sử dụng toán tử lớn hơn (">").Chúng ta có thể sử dụng nó trực tiếp trong các công thức hoặc chúng ta có thể sử dụng các tham chiếu ô với các điều kiện.
Bất cứ khi nào chúng ta sử dụng một toán tử trong một điều kiện trong Excel, chúng ta cần đặt nó bên trong dấu ngoặc kép.Ví dụ, nếu điều kiện lớn hơn 10, thì chúng ta cần nhập "> 10" làm điều kiện (xem hình ảnh bên dưới):
Đây là công thức:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 10 ″)
Bạn cũng có thể bao gồm các điều kiện trong ô và sử dụng tham chiếu ô làm điều kiện.Trong trường hợp này, bạn không cần đặt điều kiện trong dấu ngoặc kép:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Đây cũng có thể là trường hợp khi bạn muốn một điều kiện nằm trong một ô nhưng không muốn sử dụng nó với một toán tử.Ví dụ: bạn có thể muốn số trong ô D3 là 10 thay vì> 10.
Trong trường hợp này, bạn cần tạo một tham số có điều kiện, là sự kết hợp của toán tử và tham chiếu ô (xem hình ảnh bên dưới):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
Lưu ý: Khi bạn kết hợp toán tử và tham chiếu ô, các toán tử luôn được đặt trong dấu ngoặc kép.Toán tử và tham chiếu ô được kết nối bằng dấu và (&).
# 3 Đếm ô khi tiêu chí nhỏ hơn một giá trị
Để có được số lượng ô có giá trị nhỏ hơn một giá trị được chỉ định, chúng ta sử dụng toán tử less than ("<").Chúng ta có thể sử dụng nó trực tiếp trong các công thức hoặc chúng ta có thể sử dụng các tham chiếu ô với các điều kiện.
Bất cứ khi nào chúng ta sử dụng một toán tử trong một điều kiện trong Excel, chúng ta cần đặt nó bên trong dấu ngoặc kép.Ví dụ: nếu tiêu chí là số lượng phải nhỏ hơn 5, thì chúng ta cần nhập "<5" làm tiêu chí (xem hình ảnh bên dưới):
= COUNTIF ($ B $ 2: $ B $ 11, ”<5”)
Bạn cũng có thể bao gồm các điều kiện trong ô và sử dụng tham chiếu ô làm điều kiện.Trong trường hợp này, bạn không cần đặt tiêu chuẩn trong dấu ngoặc kép (xem hình ảnh bên dưới):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Ngoài ra, bạn có thể muốn điều kiện nằm trong ô, nhưng không phải với toán tử.Ví dụ: bạn có thể muốn ô D3 có số 5 thay vì <5.
Trong trường hợp này, bạn cần tạo một tham số có điều kiện là sự kết hợp của toán tử và tham chiếu ô:
= COUNTIF ($ B $ 2: $ B $ 11, "<" & D3)
Lưu ý: Khi bạn kết hợp toán tử và tham chiếu ô, các toán tử luôn được đặt trong dấu ngoặc kép.Toán tử và tham chiếu ô được kết nối bằng dấu và (&).
# 4 Đếm ô có nhiều tiêu chí - giữa hai giá trị
Để có được số lượng giá trị giữa hai giá trị, chúng ta cần sử dụng nhiều điều kiện trong hàm COUNTIF.
Đây là hai cách để làm điều này:
Phương pháp XNUMX: Sử dụng hàm COUNTIFS
Hàm COUNTIFS có thể nhận nhiều điều kiện làm đối số và chỉ đếm ô nếu tất cả các điều kiện đều ĐÚNG.Để đếm các ô có giá trị giữa hai giá trị được chỉ định, chẳng hạn như 5 và 10, chúng ta có thể sử dụng hàm COUNTIFS sau:
=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)
Lưu ý: Công thức trên không tính các ô chứa 5 hoặc 10.Nếu bạn muốn bao gồm các ô này, hãy sử dụng các toán tử lớn hơn hoặc bằng (> =) và nhỏ hơn hoặc bằng (<=).Đây là công thức:
=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″)
Bạn cũng có thể bao gồm các điều kiện này trong ô và sử dụng tham chiếu ô làm điều kiện.Trong trường hợp này, bạn không cần đặt tiêu chuẩn trong dấu ngoặc kép (xem hình ảnh bên dưới):
Bạn cũng có thể sử dụng kết hợp các tham chiếu ô và toán tử (nhập trực tiếp toán tử vào công thức).Khi kết hợp các toán tử và tham chiếu ô, các toán tử luôn được đặt trong dấu ngoặc kép.Toán tử và tham chiếu ô được kết nối bằng dấu và (&).
Phương pháp 2: Sử dụng hai hàm COUNTIF
Nếu bạn có nhiều điều kiện, bạn có thể sử dụng COUNTIFS hoặc tạo kết hợp các hàm COUNTIF.Công thức sau sẽ làm điều tương tự:
=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)
Trong công thức trên, đầu tiên chúng ta tìm số ô có giá trị lớn hơn 5 và sau đó trừ số ô có giá trị lớn hơn 10.Điều này sẽ cho chúng ta kết quả là 5 (tức là với các giá trị lớn hơn 5 và nhỏ hơn hoặc bằng 10).
Nếu bạn muốn công thức chứa cả 5 và 10, hãy sử dụng công thức sau để thay thế:
=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)
Nếu bạn muốn công thức loại trừ "5" và "10" khỏi số lượng, hãy sử dụng công thức sau:
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)
Bạn có thể bao gồm các điều kiện này trong ô và sử dụng tham chiếu ô hoặc bạn có thể sử dụng kết hợp các toán tử và tham chiếu ô.
Sử dụng điều kiện TEXT trong các hàm Excel
# 1 Đếm ô khi điều kiện bằng với văn bản được chỉ định
Để đếm các ô có chứa kết quả khớp chính xác với một văn bản được chỉ định, chúng ta có thể chỉ cần sử dụng văn bản đó làm điều kiện.Ví dụ: trong một tập dữ liệu (được hiển thị bên dưới), nếu tôi muốn đếm tất cả các ô có tên Joe, tôi có thể sử dụng công thức sau:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
Vì đây là một chuỗi văn bản, tôi cần đặt điều kiện văn bản trong dấu ngoặc kép.
Bạn cũng có thể bao gồm một điều kiện trong một ô và sau đó sử dụng tham chiếu ô đó (như được hiển thị bên dưới):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Lưu ý: Bạn có thể nhận được kết quả sai nếu có khoảng trắng ở đầu / cuối trong điều kiện hoặc phạm vi điều kiện.Đảm bảo làm sạch dữ liệu trước khi sử dụng các công thức này.
# 2 Đếm ô khi điều kiện không bằng văn bản đã chỉ định
Tương tự như những gì chúng ta đã thấy trong ví dụ trên, bạn cũng có thể đếm các ô không chứa văn bản được chỉ định.Để làm điều này, chúng ta cần sử dụng toán tử not = (<>).
Giả sử bạn muốn đếm tất cả các ô không chứa tên JOE, đây là công thức có thể thực hiện điều này:
= COUNTIF ($ B $ 2: $ B $ 11, ”<> Joe”)
Bạn cũng có thể bao gồm các điều kiện trong ô và sử dụng tham chiếu ô làm điều kiện.Trong trường hợp này, bạn không cần đặt tiêu chuẩn trong dấu ngoặc kép (xem hình ảnh bên dưới):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Đây cũng có thể là trường hợp khi bạn muốn một điều kiện nằm trong một ô nhưng không muốn sử dụng nó với một toán tử.Ví dụ: bạn có thể muốn ô D3 có tên Joe thay vì <> Joe.
Trong trường hợp này, bạn cần tạo một tham số có điều kiện, là sự kết hợp của toán tử và tham chiếu ô (xem hình ảnh bên dưới):
= COUNTIF ($ B $ 2: $ B $ 11, ”<>” & E3)
Khi kết hợp các toán tử và tham chiếu ô, các toán tử luôn được đặt trong dấu ngoặc kép.Toán tử và tham chiếu ô được kết nối bằng dấu và (&).
Sử dụng Điều kiện DATE trong Hàm COUNTIF và COUNTIFS trong Excel
Excel lưu trữ ngày và giờ dưới dạng số.Vì vậy, chúng ta có thể sử dụng nó giống như một con số.
# 1 Đếm ô khi điều kiện bằng với ngày đã chỉ định
Để có được số lượng ô có chứa một ngày cụ thể, chúng ta sẽ sử dụng toán tử bằng (=) cùng với ngày.
Để làm việc với ngày tháng, tôi khuyên bạn nên sử dụng hàm DATE, vì hàm này loại bỏ bất kỳ khả năng giá trị ngày nào bị sai.Vì vậy, ví dụ: nếu tôi muốn sử dụng ngày 2015 tháng 9 năm 1, tôi có thể sử dụng hàm DATE như sau:
= DATE (2015,9,1)
Bất chấp sự khác biệt giữa các khu vực, công thức này sẽ trả về cùng một ngày.Ví dụ: 01-09-2015 sẽ là ngày 2015 tháng 9 năm 1 theo cú pháp ngày của Hoa Kỳ và ngày 2015 tháng 1 năm 9 theo cú pháp ngày của Vương quốc Anh.Tuy nhiên, công thức này sẽ luôn trả về ngày 2105 tháng 9 năm 1.
Đây là công thức để đếm số ô chứa ngày 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
# 2 Đếm ô khi tình trạng trước hoặc sau ngày được chỉ định
Để đếm các ô chứa ngày trước hoặc sau một ngày cụ thể, chúng ta có thể sử dụng toán tử nhỏ hơn / lớn hơn.
Ví dụ: nếu tôi muốn đếm tất cả các ô chứa ngày sau ngày 2015 tháng 9 năm 2, tôi có thể sử dụng công thức sau:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))
Tương tự như vậy, bạn cũng có thể đếm số ô trước một ngày cụ thể.Nếu bạn muốn đưa ngày vào số, hãy sử dụng toán tử "bằng" cùng với toán tử "lớn hơn / nhỏ hơn".
Bạn cũng có thể sử dụng tham chiếu ô có chứa ngày tháng.Trong trường hợp này, bạn cần sử dụng dấu và (&) để kết hợp toán tử (trong dấu ngoặc kép) với ngày.
Xem ví dụ bên dưới:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & F3)
# 3 Đếm các ô có nhiều tiêu chí - giữa hai ngày
Để có được số lượng giá trị giữa hai giá trị, chúng ta cần sử dụng nhiều điều kiện trong hàm COUNTIF.
Chúng ta có thể làm điều này theo hai cách - một hàm COUNTIFS hoặc hai hàm COUNTIF.
Phương pháp XNUMX: Sử dụng hàm COUNTIFS
Hàm COUNTIFS có thể nhận nhiều điều kiện làm đối số và chỉ đếm ô nếu tất cả các điều kiện đều ĐÚNG.Để đếm các ô có giá trị giữa hai ngày được chỉ định, chẳng hạn như ngày 9 tháng 2 và ngày 9 tháng 7, chúng ta có thể sử dụng hàm COUNTIFS sau:
=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))
Công thức trên không tính các ô có chứa ngày được chỉ định.Nếu bạn cũng muốn bao gồm các ngày này, hãy sử dụng các toán tử lớn hơn hoặc bằng (> =) và nhỏ hơn hoặc bằng (<=).Đây là công thức:
=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))
Bạn cũng có thể bao gồm ngày trong ô và sử dụng tham chiếu ô làm tiêu chí.Trong trường hợp này, bạn không thể để toán tử bao gồm ngày trong ô.Bạn cần thêm toán tử vào công thức theo cách thủ công (được đặt trong dấu ngoặc kép) và sử dụng dấu và (&) để thêm tham chiếu ô.Xem bên dưới:
=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)
Phương pháp 2: Sử dụng hàm COUNTIF
Nếu bạn có nhiều điều kiện, bạn có thể sử dụng một hàm COUNTIFS hoặc tạo kết hợp hai hàm COUNTIF.Công thức sau cũng sẽ thực hiện thủ thuật:
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
Trong công thức trên, trước tiên chúng ta tìm số ô có ngày sau ngày 9 tháng 2, sau đó trừ số ô có ngày sau ngày 9 tháng 7.Điều này sẽ cho kết quả là 7 (nghĩa là số ô có ngày sau ngày 9 tháng 2 và vào hoặc trước ngày 9 tháng 7).
Nếu bạn không muốn công thức tính cả ngày 9 tháng 2 và ngày 9 tháng 7, hãy sử dụng công thức sau:
=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
Nếu bạn muốn loại trừ hai ngày khỏi số lượng, hãy sử dụng công thức sau:
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))
Ngoài ra, bạn có thể đặt ngày chuẩn trong một ô và sử dụng tham chiếu ô (và các toán tử trong dấu ngoặc kép có dấu và).
Sử dụng các ký tự đại diện trong các điều kiện của hàm COUNTIF và COUNTIFS
Có ba ký tự đại diện trong Excel:
- * (Dấu hoa thị) - nó đại diện cho bất kỳ số ký tự nào.Ví dụ: ex * có thể có nghĩa là excel, vượt trội, ví dụ, chuyên gia, v.v.
- ? (dấu chấm hỏi) - Nó đại diện cho một ký tự duy nhất.Ví dụ: Trömp có thể có nghĩa là Trump hoặc Kẻ lang thang.
- ~ (dấu ngã) - Dùng để xác định các ký tự đại diện (~, *,?) Trong văn bản.
Bạn có thể sử dụng hàm COUNTIF với các ký tự đại diện để đếm ô khi các hàm đếm tích hợp sẵn khác bị lỗi.Ví dụ: giả sử bạn có một tập dữ liệu giống như sau:
Bây giờ chúng ta hãy lấy một vài ví dụ:
# 1 Đếm các ô có chứa văn bản
Để đếm các ô có văn bản, chúng ta có thể sử dụng ký tự đại diện * (dấu hoa thị).Vì dấu hoa thị đại diện cho bất kỳ số ký tự nào, nó sẽ đếm tất cả các ô có chứa bất kỳ văn bản nào.Đây là công thức:
= COUNTIFS ($ C $ 2: $ C $ 11, "*")
Lưu ý: Công thức trên bỏ qua các ô chứa số, ô trống và giá trị lôgic, nhưng đếm các ô có chứa dấu nháy đơn (và do đó xuất hiện trống) hoặc ô có thể đã được bao gồm như một phần của công thức.
Đây là hướng dẫn chi tiết về cách xử lý các trường hợp có chuỗi rỗng hoặc dấu nháy đơn.
# 2 Đếm các ô không trống
Nếu bạn đang cân nhắc sử dụng hàm COUNTA, hãy suy nghĩ lại.
Hãy thử nó, nó có thể làm bạn thất vọng.COUNTA cũng sẽ đếm các ô có chứa chuỗi trống (thường được trả về bởi công thức là = "" hoặc khi mọi người chỉ nhập dấu nháy đơn trong ô).Các ô chứa chuỗi trống có vẻ là ô trống nhưng không phải là ô trống, do đó, được tính bằng hàm COUNTA.
COUNTA cũng sẽ đếm các ô có chứa chuỗi trống (thường được trả về bởi công thức là = "" hoặc khi mọi người chỉ nhập dấu nháy đơn trong ô).Các ô chứa chuỗi trống có vẻ là ô trống nhưng không phải là ô trống, do đó, được tính bằng hàm COUNTA.
Vì vậy, nếu bạn sử dụng công thức = COUNTA (A1: A11), nó trả về 11 khi nó sẽ trả về 10.
Đây là bản sửa lỗi:
=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))
Hãy hiểu công thức này bằng cách chia nhỏ nó:
# 3 Đếm các ô chứa văn bản cụ thể
Giả sử chúng ta muốn đếm tất cả các ô có tên đại diện bán hàng bắt đầu bằng J.Điều này có thể dễ dàng đạt được bằng cách sử dụng các ký tự đại diện trong hàm COUNTIF.Đây là công thức:
= COUNTIFS ($ C $ 2: $ C $ 11, "J *")
Điều kiện J * chỉ định rằng văn bản trong ô phải bắt đầu bằng J và có thể chứa bất kỳ số ký tự nào.
Nếu bạn muốn đếm các ô có chứa các chữ cái ở bất kỳ vị trí nào trong văn bản, hãy bao quanh chúng bằng dấu hoa thị.Ví dụ: nếu bạn muốn đếm các ô có chứa ký tự "a", hãy sử dụng * a * làm điều kiện.
So với các bài báo khác của tôi, bài này dài một cách bất thường.Hy vọng bạn thích nó.Hãy cho tôi biết bạn nghĩ gì bằng cách để lại bình luận.
