Trong quá trình xử lý dữ liệu trong Excel chúng ta thường sẽ lọc đếm dữ liệu có điều kiện. Hàm COUNTIF kết hợp với hàm LEFT/RIGHT trong Excel sẽ giúp bạn thao tác nhanh hơn đấy. Hãy cùng tìm hiểu nhé!
1. Hàm COUNTIF kết hợp hàm LEFT/RIGHT/MID trong Excel
1.1. Công thức hàm LEFT, cách kết hợp hàm LEFT với hàm COUNTIF
- Công thức:
=LEFT(text;[num_chars])
Trong đó:
+ text: Chuỗi văn bản có chứa chuỗi ký tự mà bạn muốn lấy phần bên trái.
+ [num_chars]: Số ký tự bên trái muốn trả về.
– Hàm LEFT dùng để lấy ký tự nằm ở bên trái của chuỗi.
Cho bảng dữ liệu gồm các trường SAN_PHAM, TRANG_THAI.
Bước 1: Sử dụng hàm LEFT để lấy ra 1 ký tự đầu tiên của cột SAN_PHAM
– Cách thực hiện:
Tại ô D2 > Nhập công thức “=LEFT(A2;1)” > Nhấn enter > Cho ra kết quả là 1 ký tự đầu của sản phẩm > Kéo thả chuột đến ô D9 để sao chép công thức thực hiện tương tự các sản phẩm còn lại.
Sử dụng hàm LEFT để lấy ra 1 ký tự đầu tiên
– Ý nghĩa công thức: Trích xuất ký tự đầu tiên của các sản phẩm.
Bước 2: Kết hợp COUNTIF với hàm LEFT
– Cách thực hiện:
Tại ô E2 > Nhập công thức “=COUNTIF(D2:D9;”T”)” > Nhấn enter > Cho ra kết quả là số lượng sản phẩm có ký tự đầu tiên là “T” là 3.
– Công thức:
=COUNTIF(D2:D9;”T”)
Sử dụng COUNTIF để đếm số lượng chữ “T”
Tuy nhiên việc kết hợp riêng lẻ mất thời gian, nên ta gộp chung 2 hàm COUNTIF và LEFT lại với nhau như sau:
Bước 3: Tại ô D1, nhập hàm kết hợp sau:
=COUNTIF(ARRAYFORMULA(LEFT($A$2:$A$9;1));”T”)
Kết hợp COUNTIF với hàm LEFT
Lưu ý: Hàm mảng ARRAYFORMULA dùng để sao chép công thức hàm LEFT vào các ô còn lại. Vì hàm LEFT chỉ có thể tính toán cho ô đầu tiên (ô A2). Các công thức kết hợp bên dưới cũng tương tự nhé.
1.2. Công thức hàm RIGHT, cách kết hợp RIGHT với hàm COUNTIF
- Công thức:
=RIGHT(text;[num_chars])
– Trong đó:
+ text: Chuỗi văn bản có chứa chuỗi ký tự mà bạn muốn lấy phần bên phải.
+ [num_chars]: Số ký tự bên phải muốn trả về.
– Hàm RIGHT dùng để lấy ký tự nằm ở bên phải của chuỗi.
Tương tự như cách kết hợp hàm COUNTIF với hàm LEFT ở trên, để tiết kiệm thời gian nên ta gộp chung 2 hàm COUNTIF và RIGHT lại với nhau như sau:
– Cách thực hiện:
=COUNTIF(ARRAYFORMULA(RIGHT(B2:B9;1));”Y”)
– Ý nghĩa công thức: Đếm số lần xuất hiện trạng thái “Hủy” trong dải ô B2:B9 bằng cách đếm ký tự “Y” bên phải của chữ. Kết quả là 2.
Hàm COUNTIF kết hợp hàm RIGHT
1.3. Công thức hàm MID, cách kết hợp hàm MID với hàm COUNTIF
- Công thức:
=MID(text; start_num; [num_chars])
– Trong đó:
+ text: Chuỗi văn bản có chứa chuỗi ký tự mà bạn muốn lấy phần ở giữa.
+ start_num: Vị trí của ký tự thứ nhất mà bạn muốn trích xuất trong văn bản. Ký tự thứ nhất trong chuỗi văn bản có số bắt đầu là 1.
+ [num_chars]: Số ký tự ở giữa muốn trả về.
– Hàm MID dùng để lấy ký tự nằm ở giữa của chuỗi văn bản.
– Cách thực hiện:
=COUNTIF(ARRAYFORMULA(MID(A2:A9;2;1));”Ư”)
– Ý nghĩa công thức: Đếm số lần xuất hiện sản phẩm “Bưởi” trong dải ô A2:A9 bằng cách đếm ký tự “Ư” ở giữa của chữ. Kết quả là 1.
Hàm COUNTIF kết hợp hàm MID
2. Các lỗi thường gặp khi kết hợp hàm COUNTIF và hàm LEFT/RIGHT
2.1. Lỗi #N/A
Lỗi #N/A xuất hiện là do không tìm thấy giá trị cần tìm.
Một ví dụ ở dưới hình, do dư dấu chấm phẩy (;) nên hàm COUNTIF bị dư 1 đối số. Như vậy, ta sẽ bỏ dấu chấm phẩy này đi thì có thể xử lý được lỗi #N/A.
Lỗi #N/A
2.2. Lỗi #ERROR!
Lỗi #ERROR xuất hiện là do bạn đã nhập sai cú pháp.
Như trong công thức “=COUNTIF(ARRAYFORMULA(LEFT($A$2:$A$9;1)),”T”)” trong hàm COUNTIF dấu để ngăn cách hai đối số là dấu chấm phẩy (;) không phải dấu (,).
Cách khắc phục: Kiểm tra lại cú pháp đã nhập đã đúng chưa.
Lỗi #ERROR
2.3. Lỗi #NAME?
Lỗi #NAME xuất hiện do bạn đã nhập sai tên hàm hoặc vùng dữ liệu.
Ví dụ: Trong công thức “=COUNTF(ARRAYFORMULA(LEFT($A$2:$A$9;1));”T”)” đã nhập sai tên hàm COUNTIF.
Cách khắc phục: Sửa lại tên hàm hoặc vùng dữ liệu cho đúng cú pháp.
Lỗi #NAME
2.4. Một số lưu ý khi kết hợp hàm COUNTIF và hàm LEFT/RIGHT
– Mỗi công thức hàm COUNTIF bạn chỉ có thể thực hiện đếm có điều kiện theo 1 tiêu chí duy nhất. Bạn không thể sử dụng COUNTIF để đếm cùng một lúc có bao nhiêu “Táo” và bao nhiêu “Chuối”.
– Khi nhập công thức hàm COUNTIF, LEFT và RIGHT bạn có thể viết chữ hoa – thường tùy ý. Ví dụ bạn có thể ghi 1 trong 2 cách sau nhưng hàm vẫn hiểu tiêu chí bạn đã nhập, đó là chuỗi “TÁO” hoặc “Táo”.
– Bạn có thể sử dụng hàm COUNTIF 2 lần để thống kê theo nhiều tiêu chí. Bạn có thể thống kê số lượng “Táo” và “Cam” là bao nhiêu.
3. Câu hỏi thường gặp khi kết hợp hàm COUNTIF và hàm LEFT/RIGHT
- Không sử dụng công thức mảng vẫn đếm ra giá trị bằng 1?
Trả lời:
– Mình sẽ sử dụng ví dụ ở công thức kết hợp hàm COUNTIF với hàm LEFT nhưng không có hàm mảng ARRAYFORMULA.
– Như ở mục 2 ví dụ về hàm COUNTIF và LEFT ta đã tính ra được số lượng sản phẩm táo là 3 nhưng ở đây khi thiếu hàm mảng thì vẫn xuất hiện giá trị 1. Vì sao lại như vậy?
– Lý do là hàm LEFT có thể xác định được ký tự ở ô đầu tiên (ô A2) trong dải ô đã chọn A2:A9. Và ký tự này trùng hợp phù hợp với điều kiện bạn đã đặt ra trong hàm COUNTIF là “T”. Chính vì vậy COUNTIF đã đếm được ký tự đó và xuất ra giá trị 1.
Không sử dụng công thức mảng vẫn đếm ra giá trị bằng 1
Trên đây là cách kết hợp hàm COUNTIF với hàm LEFT/RIGHT trong Excel. Mong rằng bài viết này sẽ mang lại những thông tin bạn cần. Cảm ơn bạn đã theo dõi và chúc bạn thành công nhé!
