Trong bài viết này, Học Excel Online sẽ trả lời cách áp dụng công thức Hàm COUNTIFS và áp dụng Hàm COUNTIF vào Excel dựa bên trên thuật súc tích AND và OR. Các bạn sẽ tìm thấy không ít ví dụ với các kiểu dữ liệu khác nhau – số, ngày tháng, text và ô chứa dữ liệu.
Bạn đang xem: Hàm đếm giá trị có điều kiện
Hàm COUNTIFS – công thức và phương pháp sử dụng
Công thức hàm COUNTIFS như sau:=COUNTIFS (criteria_range1, criteria1,
criteria_range1 – xác định phạm vi thứ nhất áp dụng điều kiện đầu tiên (criteria1), bắt buộc.
criteria1 – đặt điều kiện cho dạng số, ô tham chiếu, chuỗi văn bản, một mảng hoặc một hàm Excel khác, bắt buộc. Tiêu chí này khẳng định những ô tính nào sẽ tiến hành đếm và rất có thể biểu diễn là 10, “
Trong thực tế, bạn chưa phải thuộc lòng cú pháp của hàm COUNTIFS. Microsoft Excel đã hiển thị các đối số của hàm ngay lúc bạn bước đầu gõ. Đối số được nhập vào hiện tại sẽ tiến hành tô đậm.
COUNTIFS – phần đa điều cần lưu giữ ý:
bạn có thể sử dụng hàm COUNTIFS để đếm những ô trong một phạm vi riêng lẻ với một đk duy nhất tương tự như trong những vùng dữ liệu với rất nhiều điều kiện. Vào trường hợp sản phẩm hai, chỉ số đông ô tính đáp ứng toàn bộ các điều kiện new được đếm. Từng vùng dữ liệu bổ sung cập nhật phải bao gồm cùng số hàng và cột như vùng dữ liệu trước tiên (đối số criteria_range1). Hoàn toàn có thể đếm mọi vùng dữ liệu liền kề và không liền kề. Nếu tiêu chuẩn là một tham chiếu mang đến một ô trống, hàm COUNTIFS giải pháp xử lý nó như một giá chỉ trị bằng không (0). Bạn có thể sử dụng các wildcard characters trong các tiêu chuẩn – vệt hoa thị (*) với dấu chấm hỏi (?).Cách thực hiện hàm COUNTIFS với COUNTIF vào excel với tương đối nhiều tiêu chí:
Cách đếm ô tính với nhiều tiêu chuẩn (AND 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 sản phẩm như vào hình mặt dưới. Bạn muốn biết số sản phẩm tồn kho (giá trị cột B to hơn 0) nhưng lại chưa bán tốt (giá trị cột C bởi 0).
Nhiệm vụ hoàn toàn có thể được thực hiện bằng phương pháp sử dụng công thức này:
= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)
Kết quả cảm nhận là 2 (“Cherries” và “Lemons”):
Công thức 2. Bí quyết COUNTIFS với các tiêu chuẩn giống nhau
Khi bạn có nhu cầu đếm các mục gồm các tiêu chuẩn giống nhau, bạn vẫn phải cung ứng mỗi cặp criteria_range / criteria một giải pháp riêng lẻ.
Ví dụ: đấy là công thức đúng nhằm đếm những mục đựng 0 cả trong cột B với cột C:
= COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)
Công thức COUNTIFS này trả về 1 vì chưng chỉ tất cả “Grapes” có mức giá trị “0” vào cả hai cột.
Sử dụng một công thức đơn giản và dễ dàng hơn với 1 criteria_range duy tốt nhất như COUNTIFS(B2: C7, “= 0”) sẽ với lại tác dụng khác – tổng số những ô tính trong tầm B2:C7 cất 0 (trong lấy ví dụ này là 4).
Cách đếm số ô tính với nhiều tiêu chuẩn (OR logic):
Như chúng ta đã thấy trong ví dụ trên, việc đếm các ô tính đáp ứng nhu cầu tất cả các tiêu chuẩn chỉ định rất giản đơn dàng cũng chính vì hàm COUNTIFS được thiết kế theo phong cách để chuyển động theo phương pháp này.
Nhưng nếu bạn có nhu cầu đếm các ô tất cả ít nhất 1 trong các tiêu chí được hướng dẫn và chỉ định là TRUE, tức là dựa trên xúc tích OR thì sao? chú ý chung, có hai phương pháp để thực điều này – bằng cách thêm một số công thức COUNTIF hoặc áp dụng công thức SUM COUNTIFS với hằng số mảng.
Công thức 1. Thêm nhị hoặc nhiều phương pháp COUNTIF hoặc COUNITFS
Trong bảng bên dưới, mang sử bạn có nhu cầu tính các đơn đặt đơn hàng với trạng thái “Cancelled” với “Pending“, chúng ta bạn chỉ hoàn toàn có thể viết 2 phương pháp COUNTIF thông thường và dìm kết quả:
=COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)
Trong trường thích hợp mỗi hàm đựng nhiều hơn một điều kiện, áp dụng COUNTIFS thay bởi vì COUNTIF. Ví dụ: nhằm đếm số đơn đặt đơn hàng “Cancelled” và “Pending” đối cùng với “Apples”, sử dụng công thức sau:
=COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Cancelled”)+COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Pending”)
Công thức 2. Công thức SUM COUNTIFS cùng với hằng mảng
Trong trường hợp bạn quan trọng ập những tiêu chí, phương pháp tiếp cận trên chưa phải là cách tối ưu bởi công thức của các bạn sẽ quá lâu năm dòng. Để thực hiện các phép tính tương tự trong một công thức nhỏ tuổi gọn hơn, hãy liệt kê toàn bộ các tiêu chí của người sử dụng trong mảng và cung ứng mảng đó cho criteria của hàm COUNTIFS. Để đếm tổng, phối kết hợp hàm COUNTIFS phía bên trong hàm SUM như sau:
=SUM (COUNTIFS (range, “criteria1”, “criteria2”, “criteria3”, …))
Trong bảng mẫu của bọn chúng tôi, để đếm các đơn đặt hàng có tâm trạng “Cancelled“, “Pending” hoặc “In transit“, cách làm sẽ như sau:
=SUM(COUNTIFS($C$2:$C$11,“Cancelled”,”Pending”,”In Transit”))
Tương tự, bạn có thể đếm các ô dựa vào hai hoặc nhiều hơn nữa các cặp criteria_range/criteria. Ví dụ: để biết con số đơn đặt đơn hàng “Apples” trong tâm trạng “Cancelled”, “Pending”, “In transit”, áp dụng công thức này:
=SUM(COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,“Cancelled”,”Pending”,”In transit”))
Cách đếm số trong tầm cụ thể:
Công thức 1. Hàm COUNTIFS đếm các ô trong tầm số
Để tra cứu ra bao gồm bao nhiêu số thân 5 cùng 10 (không bao gồm 5 với 10) được chứa trong các ô C2 mang đến C10, sử dụng công thức này:
=COUNTIFS(C2:C10,”>5″,C2:C10,”=5″,B2:B10,”5″)-COUNTIF(C2:C10,”=10″) – đếm tất cả bao nhiêu số lớn hơn 5 nhỏ tuổi hơn 10 trong vùng dữ liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – bí quyết đếm bao gồm bao nhiêu số trong khúc từ 5 cho 10 thuộc vùng dữ iệu C2:C10, bao hàm cả 5 và 10.
Cách áp dụng tham chiếu ô trong cách làm COUNTIFS
Khi sử dụng các toán tử logic như “>”, “=” cùng với các tham chiếu ô trong cách làm COUNTIFS, để ý hãy đặt những toán tử trong ngoặc kép với thêm vệt (&) trước tham chiếu ô để xây dựng một chuỗi văn bản.
Trong tập dữ liệu mẫu bên dưới đây, hãy tính các giao dịch “Apples” với số tiền to hơn $200. Cùng với criteria_range1 vào A2: A11 với criteria_range2 vào B2: B11, bạn cũng có thể sử dụng công thức:
=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)
Hoặc, chúng ta có thể nhập những giá trị tiêu chí và đầy đủ ô duy nhất định, lấy một ví dụ F1 với F2, kế tiếp tham chiếu hầu như ô đó trong bí quyết của bạn:
=COUNTIFS($A$2:$A$11,$F$1,$B$2:$B$11,”>&$F$2)
Chú ý thực hiện giá trị tuyệt đối trong criteria và criteria_range để bí quyết vẫn đúng khi coppy sang những ô tính khác.
Cách sử dụng COUNTIFS với các ký từ bỏ đại diện:
Trong bí quyết Excel, bạn có thể sử dụng những ký tự thay mặt đại diện sau đây:
Dấu hỏi (?) – khớp với bất kỳ ký tự đối kháng nào, áp dụng nó để đếm những ô ban đầu hoặc dứt bằng một số trong những ký tự nhất định.
Dấu hoa thị (*) – khớp với ngẫu nhiên dãy ký tự nào, bạn dùng nó nhằm đếm ô cất một từ ví dụ hoặc một k1 tự vào ô.
Lưu ý. Nếu bạn muốn đếm các ô đựng dấu hỏi hoặc giấu hoa thị, gõ dấu bổ (~) trước vệt hoa thị hoặc có thể dấu chấm hỏi.
Giả sử chúng ta có danh sách những dự án vào cột A. Bạn có nhu cầu biết tất cả bao nhiêu dự án đã cam kết kết, tức là cột B gồm chứa thương hiệu người. Hãy thêm một điều kiện thứ nhì – End date trong cột D.
=COUNTIFS(B2:B10,”*”,D2:D10,””&””))
Lưu ý, chúng ta không thể thực hiện ký tự đại diện thay mặt trong tiêu chí thứ 2 vì cột D đựng kiểu tài liệu ngày mon chứ chưa phải dạng text. Đó là lý do tại sao các bạn sử dụng tiêu chí “”&”” nhằm tìm ô chứa dữ liệu.
COUNTIFS với COUNTIF cùng với nhiều tiêu chuẩn ngày tháng:
Ví dụ 1. Đếm ngày vào một khoảng thời hạn cụ thể
Để đếm ngày vào một khoảng thời hạn cụ thể, bạn có thể sử dụng bí quyết COUNTIFS cùng với hai tiêu chuẩn hoặc phối hợp hai hàm COUNTIF với nhau.
Ví dụ 3. Đếm ngày với tương đối nhiều điều kiện dựa trên ngày hiện tại
Bạn hoàn toàn có thể sử dụng hàm TODAY kết hợp với COUNTIF nhằm tính các ngày dựa trên ngày hôm nay.
Ví dụ: bí quyết COUNTIF sau với nhị vùng dữ liệu và hai tiêu chuẩn sẽ cho chính mình biết tất cả bao nhiêu thành phầm đã được mua nhưng chưa giao hàng:
=COUNTIFS(C2:C9,””&TODAY())
Công thức này còn có thể chứa được nhiều biến. Ví dụ: chúng ta cũng có thể tinh chỉnh nó nhằm đếm gồm bao nhiêu sản phẩm được thiết lập hơn một tuần trước và chưa giao hàng:
=COUNTIFS(C2:C9,””&TODAY())
Như vậy với bài viết này, blog.hocexecl.online đã chia sẻ cũng như lí giải cho các bạn một cách thực hiện công thức Count
IFS với Count
IF vào Excel một cách cụ thể nhất, giả dụ có ngẫu nhiên thắc mắc nào các bạn hãy bình luận ngay ở bên dưới nội dung bài viết để chúng tôi có thể giải quyết và xử lý mọi thắc mắc của chúng ta một cách sớm nhất.
Trong Excel, hàm đếm gồm điều kiện là giữa những phép toán cơ bạn dạng mà mỗi người sử dụng cần hiểu rõ trong thừa trình thao tác làm việc với dữ liệu. Hàm đếm vào Excel bao gồm hai dạng cơ phiên bản là COUNTIFS và COUNTIF. Chúng dễ bị nhầm lẫn do sự không giống nhau không đáng kể và gồm cùng mục đích sử dụng. Nội dung bài viết hôm nay không chỉ có phân biệt được nhị hàm đếm trên ngoại giả đưa ra cách áp dụng chúng sao cho công dụng nhất.
Hàm đếm có đk COUNTIFS và giải pháp sử dụng
Hàm đếm có đk trong Excel là hàm được dùng để đếm các ô như cái tên thường gọi của nó trên phạm với những đk mà yêu ước đưa ra. Hiện nay, hàm này xuất hiện tại hầu như các phiên bản được sử dụng phổ biến của Excel như bản 2007, 2010, 2013 và 2016.
Cú pháp của hàm = COUNTIFS (criteria_range1, criteria1,
Trong đó:
criteria_range1: hotline là vùng chứa đk thứ nhất, vô cùng đặc trưng và không thể không có trong cú pháp.criteria1: là điều kiện thứ nhất nằm vào vùng điều kiện đầu tiên gồm có những số, những ô, một đoạn văn bản hoặc một hay các hàm Excel.Criteria_range2: giống như với criteria_range1, đây đó là nơi chứa điều kiện thứ nhị ( quý hiếm về độ lớn của những vùng đk 2 luôn luôn phải bởi nhau)criteria2: điều kiện thứ nhì thuộc vùng đk thứ hai. Vào cú pháp này, bạn cũng có thể sử dụng về tối đa 127 cặp điều kiện hoặc phạm vi Tương từ với các criteria_range3, criteria3, criteria_range4, criteria4,…..
Tuy nhiên, những người tiêu dùng Excel không bắt buộc ghi lưu giữ câu lệnh lâu năm như trên vì chưng hàm đã có được lưu. Vì thế những thông số của hàm sẽ hiển thị ngay sau khi chúng ta nhập thương hiệu hàm.
Khi thực hiện hàm đếm có đk COUNTIFS vào Excel, chúng ta phải chú ý những điều sau:
Hàm đếm có điều kiện được dùng để làm đếm các ô theo một yêu ước cho trước trong một vùng duy nhất định. Hoàn toàn có thể chỉ gồm một đk và một phạm vi mà lại cũng rất có thể đếm cùng với số nhiều các yếu tố trên.criteria1 dùng để biểu diễn những phép toán như 10, “Các phạm vi được bổ sung sau phải gồm hàng với cột trùng cùng với phạm vi đầu tiên. Cho phép cả đầy đủ vùng đk trực tiếp với vùng đk gián tiếp.Hàm COUNTIFS đang coi một ô trống có giá trị 0 khi điều kiện là 1 trong những tham chiếu cho ô đó.Trong hàm đếm bao gồm điều kiện, số đông ký tự quan trọng như những dấu, những phép tính vẫn có thể sử dụng bình thường.Cách sử dụng hàm đếm COUNTIFS trong Excel với khá nhiều điều kiện
Để hiểu rõ hơn về kiểu cách sử dụng hàm, họ cùng nhau vận dụng hàm vào các ví dụ.
Ta có một bảng dữ liệu về số ngày đi làm, ngày ngủ của nhân lực tại một công ty nào đó:
Yêu cầu được đưa ra:
Đếm số nhân công nữ có 23 ngày công trong tháng.Đếm số nam giới lao rượu cồn không nghỉ ngơi ngày nào.Đếm số người bạn nữ có 23 ngày công và tất cả số ngày nghỉ không thực sự 1 ngày.Để có thể giải quyết các phép toán trên, ta tiến hành sử dụng hàm COUNTIFS trong Excel với từng ý.
Yêu cầu 1: Đếm số nhân công người vợ có 23 ngày công trong tháng
Ta xác định các vùng đếm cùng điều kiện
criteria1_range1: Cột nam nữ C2:C9.criteria1: Nữ.criteria2_range2: Cột ngày công E2:E9.criteria2: 23 ngày công.Từ đó, ta chế tạo được cú pháp.
= COUNTIFS (C2:C9, “nữ”, E2:E9, 23) với ta có công dụng là 4 như trong ảnh.
Yêu ước 2: Đếm số phái nam lao đụng không ngủ ngày nào
Tương trường đoản cú như yêu mong 1, trước hết ta phải xác minh được điều kiện và các vùng đếm
criteria1_range1: Cột nam nữ C2:C9.tiêu chí1: Nam.criteria2_range2: Cột ngày nghỉ F2:F9.criteria2: 0 ngày nghỉ.Kết quả thu được là 0 sau thời điểm xác lập công thức.
= COUNTIFS (C2: C9, “nam”, F2: F9, 0)
Yêu ước 3: Đếm số người con gái có 23 ngày công và có số ngày nghỉ không thực sự 1 ngày
Khác hơn với hai yêu mong trên, tại chỗ này có đựng 3 điều kiện.Hàm đếm có điều kiệntrong Excel vẫncóthể xử lý được bài xích toán thông thường với:
Lắp các số liệu bên trên vào cú pháp của hàm đếm, ta có:
= COUNTIFS (C2:C9, “nữ”, E2:E9, 23, F2:F9, 0) và có kết quả là 2
Cách áp dụng hàm đếm có đk COUNTIF
Bên cạnh hàm COUNTIFS, hàm COUNTIF cũng là 1 hàm đếm gồm điều kiện, là phiên phiên bản thấp rộng của COUNTIFS nhưng vẫn đang còn những bài toán cần áp dụng hàm này.
Dùng hàm đếm có đk COUNTIF đếm ô cất giá trị trùng
Cú pháp củahàm đếm gồm điều kiện
COUNTIF whenđếmnhững ôcógiá trị như là nhau khá solo giản, như ví dụ như được nêu ra bên trên hình hình ảnh minh họa. Một xem xét quan trọng mà chúng ta nên nhớ: trong đk đọc ô chứa giá trị trùng, criteria không khác nhau chữ hoa tuyệt chữ thường.
Dùng hàm COUNTIF trong phép so sánh
Khi triển khai phép toán so sánh,hàm đếm có điều kiệntrong Excel cho 3 cực hiếm là nhỏ bé hơn, lớn hơn hoặc bằng bao gồm 2điều kiệnđi kèm làđiều kiệntrực tiếp vàđiều kiệntham số.
Điều khiếu nại trực tiếp, dạng của nó sẽ là “>= A” . Lúc này, A đó là giá trị đề xuất so sánh. Điều khiếu nại tham chiếu, ta yêu cầu viết theo phương thức hàm &:“>=” và I8. Trong số ấy I8 là ô được tham chiếu tới.
Dùng làm COUNTIF lọc quý giá đúng sai
Công dụng của hàm COUNTIF không chỉ là là đếm hay so sánh mà còn được sử dụng như một phương pháp lọc thông tin trong Excel.
Khi nên lọc quý giá đúng sai, bao gồm 2 cách cho mình sử dụng:
= COUNTIF (phạm vi, đúng) hoặc.= COUNTIF (phạm vi, sai).Với range là vùng lọc giá trị đúng sai theo yêu thương cầu.
Ứng dụng của hàm COUNTIF vào doanh nghiệp
Đếm số ngày làm việc trên bảng chấm công
Nguyên tắc: đặt ra các ký kết hiệu đại diện thay mặt để theo dõi và quan sát tình trạng thao tác như đi làm việc đủ ngày, làm nửa ngày giỏi nghỉ làm…Có thể dùng để làm theo dõi đều ngày nghỉ tất cả phép hay là không có phép của nhân viên. Trong một thời gian nhất định như tháng, năm..
Dùng để đếm số lần mua sắm và chọn lựa của khách hàng hàng
Hàm COUNTIF được các cửa hàng sử dụng nhằm theo dõi lượt cài sản phẩm của bạn trong ngày, tháng hay năm. Đây trở nên yêu cầu phải có khi các nhà tuyển dụng tìm kiếm kiếm những nhân viên bán hàng.
Xem thêm: 35+ Mẫu Bánh Sinh Nhật Bé Trai Đơn Giản, Đáng Yêu, Các Sản Phẩm Bánh Sinh Nhật Bé Trai
Không chỉcóvậy,hàm đếm có điều kiệntrong Excel này còn được vận dụng nhiều trong cuộc sống như lọc các thông tin duy nhất, đào thải các cực hiếm giống nhau…
Hàm đếm có điều kiện ngày 1 được sử dụng thông dụng trong Excel. Hàm này sở hữu đến cho những người dùng nhiều app như đọc tin tức theo yêu cầu, so sánh. Với lọc tin tức đúng sai, đếm các ô có giá trị như là nhau lúc có điều kiện nhất định. Bởi vậy, bạn nên ghi nhớ hàm đếm có điều kiệnnày bởi vì các chức năng linh hoạt và đạt được hiệu quả cao.