Thông tin liên hệ
- 036.686.3943
- admin@nguoicodonvn2008.info
Hàm COUNTIFS trong Excel có rất nhiều ứng dụng thực tế. Dưới đây là những điều bạn cần biết về cách dùng hàm COUNTIFS trong Excel.
Microsoft Excel là phần mềm bảng tính nổi tiếng trên thế giới với rất nhiều tính năng hữu ích. Sự ra đời của phần mềm giúp công việc nhập liệu và tính toán hàng loạt con số trở nên đơn giản hơn bao giờ hết. Và khi dùng Excel, nhất định bạn cần biết tới hàm COUNTIFS.
Hàm COUNTIFS trong Excel dùng để đếm ô thỏa mãn nhiều điều kiện cho trước. Hàm COUNTIFS là một trong những hàm Excel thống kê được sử dụng nhiều trong Excel, là hàm nâng cao của hàm COUNTIF chỉ đếm ô với 1 điều kiện cho trước mà thôi. Khi thực hiện với hàm COUNTIFS, người dùng dễ dàng tìm ra ô kết quả thỏa mãn các điều kiện được đưa ra trong yêu cầu. Các điều kiện có thể là số, ngày tháng, text hay ô chứa dữ liệu. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm COUNTIFS trong Excel.
Cú pháp hàm COUNTIFS là =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…).
Trong đó:
Lưu ý khi sử dụng hàm COUNTIFS:
=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"
Lưu ý: Các điều kiện bổ sung phải tuân theo các quy tắc tương tự.
=COUNTIFS(A1:A10,"<"&B1) // count cells less than B1
Lưu ý: COUNTIFS là một trong một số hàm chia điều kiện thành hai phần: Phạm vi + tiêu chí. Điều này gây ra một số mâu thuẫn đối với các công thức và hàm khác.
=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank
=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1
1. Bảng dữ liệu số 1
Chúng ta có bảng dữ liệu dưới đây để thực hiện một số yêu cầu cho bảng.
Ví dụ 1: Tính số lượng nhân viên Nam có 25 ngày công làm việc.
Tại ô cần nhập kết quả chúng ta nhập công thức =COUNTIFS(C2:C7,"Nam",D2:D7,25) rồi nhấn Enter.
Trong đó:
Kết quả chúng ta có 2 nhân viên là Nam có 25 ngày công.
Ví dụ 2: Tính số nhân viên Nam có ngày nghỉ là 0.
Tại ô nhập công thức chúng ta nhập =COUNTIFS(C2:C7,"Nam",E2:E7,0) rồi nhấn Enter.
Kết quả hiển thị 1 nhân viên Nam có 0 ngày nghỉ.
Ví dụ 3: Tính số nhân viên Nam có ngày công 25 ngày và có số ngày nghỉ ít hơn hoặc bằng 2 ngày.
Chúng ta nhập công thức =COUNTIFS(C2:C7,"Nam",D2:D7,25,E2:E7,"<=2") rồi nhấn Enter.
Kết quả có 2 nhân viên Nam đáp ứng đủ yêu cầu có ngày nghỉ ít hơn hoặc bằng 2 ngày.
2. Bảng dữ liệu số 2
Ví dụ 1: Tính tổng các mặt hàng Đài Loan có đơn giá bán dưới 200,000đ.
Tại ô nhập kết quả chúng ta nhập công thức tính =COUNTIFS(B2:B7,"*Đài Loan",C2:C7,"<200,000") rồi nhấn Enter.
Kết quả được 2 mặt hàng Đài Loan đúng với yêu cầu.
Ví dụ 2: Tính tổng sản phẩm mặt hàng Đài Loan có đơn giá trong khoảng 100,000đ đến 150,000đ.
Tại ô nhập kết quả chúng ta nhập công thức =COUNTIFS(B2:B7,"*Đài Loan",C2:C7,">100000",C2:C7,"<150000") rồi nhấn Enter.
Kết quả sẽ có 2 sản phẩm Đài Loan có giá trong khoảng 100,000đ – 150,000đ.
Hàm COUNTIFS về cơ bản là một phiên bản phức tạp hơn của hàm COUNTIF. Ưu điểm chính mà COUNTIFS vượt trội hơn so với COUNTIF là nó hỗ trợ nhiều điều kiện và phạm vi.
Tuy nhiên, bạn cũng có thể xác định một phạm vi và một điều kiện duy nhất cho hàm COUNTIFS, tương tự như đã làm với hàm COUNTIF.
Một điều quan trọng mà bạn nên hiểu về hàm COUNTIFS trước khi sử dụng là hàm này không chỉ đơn giản là tính tổng kết quả của các ô đáp ứng tiêu chí cho mỗi phạm vi ô.
Trong thực tế, nếu bạn có hai điều kiện cho hai phạm vi, các ô trong phạm vi đầu tiên được lọc hai lần: Một lần qua điều kiện đầu tiên và sau đó qua điều kiện thứ hai. Điều này có nghĩa là hàm COUTNIFS sẽ chỉ trả về các giá trị đáp ứng cả hai điều kiện, trong phạm vi đã cho của chúng.
Bạn có thể hiểu rõ hơn về chức năng của hàm COUNTIFS bằng cách nghiên cứu ví dụ bên dưới.
Ngoài màu sắc của táo, còn có một cột mô tả kích thước của chúng. Mục tiêu cuối cùng trong ví dụ này là đếm số lượng táo đỏ cỡ lớn.
1. Chọn ô mà bạn muốn hiển thị kết quả. (Trong ví dụ này, bài viết sẽ hiển thị số lượng táo đỏ lớn trong ô E3).
2. Đi tới thanh công thức và nhập công thức bên dưới:
=COUNTIFS(A3:A11, "Red", B3:B11, "Big")
3. Với lệnh này, công thức kiểm tra các ô từ A3 đến A11 cho điều kiện “Red”. Các ô vượt qua vòng kiểm tra này sau đó được kiểm tra thêm lần nữa trong phạm vi từ B3 đến B11 cho điều kiện “Big”.
4. Nhấn nút Enter.
5. Bây giờ, Excel sẽ đếm số lượng táo đỏ cỡ lớn.
Hãy quan sát cách công thức đếm các ô có cả thuộc tính red và big. Công thức lấy các ô từ A3 đến A11 và kiểm tra chúng để tìm kết quả red. Các ô vượt qua điều kiện này, sau đó lại được kiểm tra với điều kiện tiếp theo trong phạm vi thứ hai, trong trường hợp này, là điều kiện big.
Kết luận, các phạm vi và điều kiện sau phạm vi và điều kiện đầu tiên ngày càng thu hẹp bộ lọc đếm và không độc lập với nhau. Vì vậy, kết quả cuối cùng của công thức là những quả táo có màu đỏ (red) và cỡ to (big). Bạn có thể đếm số lượng táo đỏ hoặc cỡ lớn bằng cách kết hợp hàm COUNTIF với hàm SUM.
1. Chọn ô mà bạn muốn hiển thị kết quả của công thức. (Trong ví dụ này, bài viết sẽ sử dụng ô E6).
2. Nhập công thức bên dưới:
=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))
3. Công thức này sẽ đếm các ô chứa táo đỏ, sau đó là số ô chứa táo lớn, và cuối cùng, nó sẽ tính tổng hai số này.
4. Nhấn nút Enter.
5. Bây giờ Excel sẽ đếm và hiển thị số lượng táo cỡ lớn hoặc có đỏ.
Trên đây là 2 bảng dữ liệu với cách sử dụng cho hàm COUNTIFS trong Excel. Người dùng phải viết điều kiện đi kèm với vùng dữ liệu điều kiện để hàm COUNTIFS xác định chính xác.
Khi đếm các chuỗi text, chúng phải được chèn vào bên trong dấu ngoặc kép. Nếu không, hàm COUNTIFS sẽ không thể đếm chuỗi văn bản và sẽ trả về giá trị 0. Ở ảnh ví dụ sau, chuỗi text chưa được đặt vào trong dấu ngoặc kép. Vì thế, công thức này trả kết quả bằng 0.
Để khắc phục lỗi này, bạn chỉ cần viết lại chính xác công thức: =COUNTIFS(E5:E12, "Car")
Khi dùng nhiều hơn một tiêu chí trong hàm COUNTIFS, phạm vi của ô cho tiêu chí khác phải có cùng số lượng ô. Nếu không, hàm COUNTIFS sẽ không hoạt động.
Giả sử ở ví dụ này muốn đếm doanh số bán ô tô ở Austin. Công thức được nhập là =COUNTIFS(E5:E12,"Car",D5:D11,"Austin"). Quan sát kỹ công thức, bạn sẽ thấy phạm vi tiêu chí đầu tiên ở đây là E5:E12 nhưng phạm vi ở tiêu chí thứ hai là D5:D11. Số ô trong phạm vi cho tiêu chí này không giống nhau.
Giờ nhấn Enter, công thức sẽ trả về lỗi #VALUE!.
Viết lại công thức đúng như sau: =COUNTIFS(E5:E12,"Car",D5:D12,"Austin")
Nếu không chèn công thức chính xác, hàm COUNTIFS sẽ không hoạt động. Khi dùng toán tử toán học bất kỳ, chẳng hạn như dấu lớn hơn (>), nhỏ hơn (<), bằng (=) và không bằng (<>), cả toán tử và tiêu chí số phải được nhập vào bên trong cùng một phương trình. Ví dụ, nếu muốn tìm doanh số bán hàng lớn hơn 100.000USD, bạn cần chèn công thức sau:
=COUNTIFS(F5:F12,">" 100000)
Ở đây, mới chỉ chèn toán tử bên trong phương trình, không có tiêu chí số.
Nếu nhấn Enter, hộp thoại nhắn tin của Microsoft Excel sẽ hiện: “There’s a problem with this formula”.
Để khắc phục vấn đề. Gõ công thức chính xác:
=COUNTIFS(F5:F12,">100000")
Giờ chúng ta đã nhập cả hai toán tử và tiêu chí bên trong dấu ngoặc. Giờ công thức này sẽ trả về số lượng theo yêu cầu.
Nhấn Enter.
Kết quả, bạn sẽ nhận được số lượng bán hàng lớn hơn 100.000USD.
Chúc các bạn thực hiện thành công!
Nguồn tin: Quantrimang.com:
Ý kiến bạn đọc
Những tin mới hơn
Những tin cũ hơn