Nhiều nếu trong excel

Xong mới thấy anh em làm xây dựng có thể dùng để thống kê công việc thi công theo ngày rất hiệu quả nhé

Hàm  ConcatIF Để nối nhiều ô dựa trên tiêu chí* Sử dụng Nối chuỗi có điều kiện *Cú pháp = ConcatIf Dấu phân cách ConcateRange Phạm vi tiêu chí Tiêu chí )*Giải thích        Tham sốÝ nghĩaKiểu giá trị    Delimiter Dấu phân cáchString    ConcateRange Vùng lấy giá trịPhạm vi    CriteriaRange Vùng điều kiệnPhạm vi    Tiêu chíĐiều kiện so sánhString

Bạn có thể sao chép hàm này thả vào mô-đun trong tệp excel để sử dụng

Hàm ConcatIf( dấu phân cách Dưới dạng Chuỗi, ConcateRange <As Range, CriteriaRange As Range, Criteria As Variant) As String
Dim rng As Range
Khi gặp lỗi Tiếp tục Tiếp theo
ConcatIf = ""
Đối với mỗi rng trong CriteriaRange
Nếu WorksheetFunction. CountIf(rng, Criteria) Then
ConcatIf = ConcatIf & dấu phân cách & rng. Độ lệch (0, ConcateRange. Cột - Phạm vi tiêu chí. Cột)
kết thúc nếu
Kế tiếp
ConcatIf = Mid(ConcatIf, Len(dấu phân cách) + 1, Len(ConcatIf))
'Tạo bởi VuMinhHoan
chức năng kết thúc


Vận dụng hàm ConcatIF, anh em có thể tổng hợp thi công và nghiệm thu, đưa vào Nhật Ký Thi Công tự động trên Excel VBA một cách đơn giản như video hướng dẫn sau

 

Tham gia nhóm Kinh nghiệm lập và quản lý hồ sơ chất lượng tại đây. https. //www. Facebook. com/groups/kinhnghiemlaphscl

Trong bài viết này, Học Excel Online sẽ hướng dẫn cách sử dụng công thức Hàm COUNTIFS và cách sử dụng Hàm COUNTIF trong Excel dựa trên logic AND và OR. Bạn sẽ thấy rất nhiều ví dụ với các kiểu 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 COUNTIFS – công thức và cách sử dụng

Công thức hàm COUNTIFS as after

=COUNTIFS (phạm vi tiêu chí1, tiêu chí1, [phạm vi tiêu chí2, tiêu chí2]…)

tiêu chí_phạm vi1 – xác định phạm vi áp dụng điều kiện đầu tiên cho điều kiện đầu tiên (tiêu chí1), bắt buộc

tiêu chí1 – đặ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 xác định các ô tính sẽ được tính và có thể biểu diễn là 10, “<=32”, A6, “sweets”

[criteria_range2,criteria2]… – đây là các điều kiện bổ sung, tùy chọn. You can enter to 127 cặp điều kiện trong công thức

Trong thực tế, bạn không được thuộc lòng cú pháp của 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ố được nhập vào hiện tại sẽ được tô đậm

COUNTIFS – Những điều cần lưu ý

  1. Bạn có thể sử dụng hàm COUNTIFS để đếm các ô trong một phạm vi đơn lẻ với một điều kiện duy nhất cũng như trong nhiều vùng dữ liệu với nhiều điều kiện. Trong trường hợp thứ hai, chỉ những ô tính đáp ứng tất cả các điều kiện mới được tính
  2. Mỗi vùng dữ liệu bổ sung phải có cùng số hàng và cột như vùng dữ liệu đầu tiên (đối số criteria_range1)
  3. There cancount the following data partitions and don't next
  4. Nếu tiêu chí là một tham chiếu đến một ô trống, hàm COUNTIFS xử lý nó như một giá trị bằng không (0)
  5. 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 (?)

Cách sử dụng hàm COUNTIFS và COUNTIF trong excel với nhiều tiêu chí

Cách đếm ô tính với nhiều tiêu chí (AND logic)

Formula 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ư trong hình bên dưới. Bạn muốn biết số lượng hàng tồn kho (giá cột B lớn hơn 0) nhưng chưa bán được (giá cột C bằng 0)

Nhiệm vụ có thể được thực hiện bằng cách sử dụng công thức này

= COUNTIFS (B2. B7, “> 0”, C2. C7, “= 0”)

Kết quả nhận được là 2 (“Cherries” và “Chanh”)

Formula 2. Công thức COUNTIFS với các tiêu chí giống nhau

Khi bạn muốn đếm các mục có các tiêu chí giống nhau, bạn vẫn phải cung cấp cho mỗi cặp criteria_range / criteria một cách riêng lẻ

Ví dụ. đây là công thức đúng để đếm các mục chứa 0 cả trong cột B và 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ỉ có “Grapes” có giá trị “0” trong cả hai cột

Sử dụng một công thức đơn giản hơn với một tiêu chí_phạm vi duy nhất như COUNTIFS(B2. C7, “= 0”) sẽ mang lại kết quả khác – tổng số các ô tính trong khoảng B2. C7 chứa 0 (trong ví dụ này là 4)

Xem thêm. Tổng hợp kiến ​​thức về Excel nâng cao

Cách đếm ô tính với nhiều tiêu chí (OR logic)

Như bạn đã thấy trong ví dụ trên, việc đếm các ô tính trả lời tất cả các tiêu chí chỉ định rất dễ dàng bởi vì hàm COUNTIFS được thiết kế để hoạt động theo cách này

Nhưng nếu bạn muốn đếm các ô có ít nhất một trong các tiêu chí chỉ được xác định là TRUE, nghĩa là dựa trên logic OR thì sao?

Formula 1. Thêm hai hoặc nhiều công thức COUNTIF hoặc COUNITFS

Trong bảng bên dưới, giả sử bạn muốn tính các đơn đặt hàng với trạng thái “Đã hủy” và “Đang chờ xử lý”, bạn chỉ có thể viết 2 công thức COUNTIF bình thường và nhận 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, sử dụng COUNTIFS thay vì COUNTIF. Ví dụ. to count the menu set row “Cancelled”  và “Pending” đối với “Apples”, 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ý”)

Formula 2. Công thức SUM COUNTIFS với hằng số

Trong tình huống khó xử, bạn cần thiết lập nhiều tiêu chí, cách tiếp cận trên không phải là cách tối ưu theo 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ả các tiêu chí của bạn trong mảng và cung cấp mảng đó cho các tiêu chí của hàm COUNTIFS. Để đếm tổng, kết hợp hàm COUNTIFS bên trong hàm SUM as after

=SUM(COUNTIFS(range, {“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 đặt hàng có trạng thái “Đã hủy“, “Đang chờ xử lý” hoặc “Đang chuyển”, công thức sẽ như sau

=SUM(COUNTIFS($C$2. $C$11,{“Đã hủy”,”Đang chờ xử lý”,”Đang chuyển”}))

Tương tự như vậy, bạn có thể đếm các ô dựa trên hai hoặc nhiều hơn các cặp tiêu chí_phạm vi/tiêu chí. Ví dụ. để biết số lượng đơn đặt hàng “Apples” trong trạng thái “Đã hủy”, “Đang chờ xử lý”, “Đang vận chuyển”, sử dụng công thức này

=SUM(COUNTIFS($A$2. $A$11,”Táo”,$C$2. $C$11,{“Đã hủy”,”Đang chờ xử lý”,”Đang chuyển”}))

Encounting number in the tool area

Formula 1. Hàm COUNTIFS đếm các ô trong khoảng số

Để tìm ra có bao nhiêu số giữa 5 và 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 này

=COUNTIFS(C2. C10,”>5″,C2. C10,”<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,”>=5″,B2. B10,”<=10″)

Formula 2. Công thức COUNTIF để đếm số giữa X và Y

Chúng ta có thể sử dụng công thức COUNTIF lồng ghép với một hàm khác để có kết quả tương tự. Công thức đầu tiên đếm những số lớn hơn giá trị giới hạn bên dưới (ví dụ là 5). Công thức thứ hai loại bỏ các số lớn hơn giá trị giới hạn trên (trong trường hợp này là 10). Kết quả bạn muốn tìm nằm trong khoảng hai giá trị nhận được trên

=COUNTIF(C2. C10,”>5″)-COUNTIF(C2. C10,”=10″) – đếm có bao nhiêu số lớn hơn 5 nhỏ hơn 10 trong vùng dữ liệu C2. C10

=COUNTIF(C2. C10,”>=5″)-COUNTIF(C2. C10,”>10″) – công thức đếm có bao nhiêu số trong đoạn từ 5 đến 10 thuộc vùng 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ư “>”, “<“, “<=” hoặc “>=” cùng với các tham chiếu ô trong công thức COUNTIFS, lưu ý hãy đặt các toán tử trong ngoặc kép và thêm dấu (&) trước tham chiếu ô để xây dựng một chuỗi văn bản.

Trong mẫu dữ liệu dưới đây, hãy tính các đơn hàng “Táo” với số tiền lớn hơn $200. With criteria_range1 trong A2. A11 and criteria_range2 in B2. B11, you can use the formula

=COUNTIFS($A$2. $A$11,”Táo”,$B$2. $B$11,”>200″)

Hoặc, bạn có thể nhập các giá trị tiêu chí và các ô định sẵn, ví dụ như F1 và F2, sau đó tham khảo các ô đó trong công thức của bạn

=COUNTIFS($A$2. $A$11,$F$1,$B$2. $B$11,”>&$F$2)

Chú ý sử dụng giá trị tuyệt đối trong tiêu chí và tiêu chí_phạm vi để công thức vẫn đúng khi sao chép sang các ô tính khác

Xem thêm. Khoá học Excel từ cơ bản đến nâng cao

Cách sử dụng COUNTIFS với các 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 đây

Dấu hỏi (?) – khớp với bất kỳ đơn ký tự nào, sử dụng nó để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định

Dấu hoa thị (*) – khớp với bất kỳ dãy ký tự nào, bạn sử dụng nó để đếm ô chứa một từ cụ thể hoặc một ký tự k1 trong ô

Lưu ý. Nếu bạn muốn đếm các ô chứa dấu 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 đã được ký kết, tức là cột B có chứa tên người. Please add a second condition – Ngày kết thúc trong cột D

=COUNTIFS(B2. B10,”*”,D2. D10,”<>”&””))

Lưu ý, bạn không thể sử dụng ký tự đại diện trong tiêu chí thứ 2 vì cột D chứa dữ liệu kiểu ngày chứ không phải định dạng văn bản. Đó là lý do tại sao bạn sử dụng tiêu chí “<>”&”” để tìm ô chứa dữ liệu

COUNTIFS và COUNTIF với nhiều tiêu chí ngày tháng

Ví dụ 1. Dem day in a perable time interval

Để đếm 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 đây đếm số ngày trong ô C2 đến C10, khoảng thời gian từ ngày 1/6/2014 đến ngày 7/7/2014 bao gồm

=COUNTIFS(C2. C9,”=6/1/2014″,C2. C9,”<=6/7/2014″)

=COUNTIF(C2. C9,”=6/1/2014″)-COUNTIF(C2. C9,”7/6/2014″)

Ví dụ 2. Dem day with many condition

Tương tự như vậy, 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 từ hai điều kiện trở lên. Ví dụ, công thức dưới đây sẽ tìm ra có bao nhiêu sản phẩm được mua sau ngày 20 tháng 5 và giao hàng sau ngày 01 tháng 6

=COUNTIFS(C2. C9,”>1/5/2014″,D2. D9,”7/6/2014″)

Ví dụ 3. Dem 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 TODAY kết hợp với COUNTIF để tính các ngày dựa trên ngày hôm nay

Ví dụ. công thức COUNTIF after with hai vùng 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 hàng

=COUNTIFS(C2:C9,”<“&TODAY(),D2:D9,”>”&TODAY())

Công thức này có thể chứa nhiều biến. Ví dụ. bạn có thể chỉnh sửa nó để đếm có bao nhiêu sản phẩm được mua hơn một tuần trước và chưa giao hàng

=COUNTIFS(C2:C9,”<=”&TODAY()-7,D2:D9,”>”&TODAY())

Như vậy với bài viết này, blog. hocexecl. online đã chia sẻ cũng như hướng dẫn các bạn một cách sử dụng công thức CountIFS và CountIF trong Excel một cách chi tiết nhất, nếu có bất kỳ thắc mắc nào các bạn hãy comment ngay bên dưới bài viết để chúng tôi có thể thực hiện được