HÀM COUNTIFS VÀ CÓ THỂ BẠN CHƯA BIẾT!

Xin chào các bạn mình là Trung đến từ ERX Việt Nam.

Trong bài viết này mình sẽ kể cho bạn nghe về câu chuyện thâm cung bí sử của hàm COUNTIFS mà không phải ai cũng biết.

1. Thông số criteria_range có được phép chọn nhiều cột hay không?

Câu trả lời là có. Bạn có thể thấy trong ví dụ trên với duy nhất 1 điều kiện thì mình có thể chọn cả vùng A1:D8 với điều kiện là đếm xem trong vùng này có bao nhiêu giá trị bằng 2.

Vậy nếu có nhiều điều kiện hơn thì sao.

Ví dụ mình đổi đề bài thành lấy ra các giá trị >= 2 và <= 4 thì sao?

Câu trả lời vẫn là được nhé. Bạn có thể yên tâm xài nhiều điều kiện và các vùng criteria_range sẽ có nhiều cột cũng được.

2. Tại sao viết hàm COUNTIFS với điều kiện >= 1 số nào đó thì bạn lại phải để trong ngoặc kép?

Đây là câu hỏi mà mình tin rất nhiều bạn sẽ muốn hỏi. Mình sẽ giải thích lý do ngay ở đây thôi nên các bạn đừng ngại mà cứ kéo chuột tiếp nhé.

Bạn hãy thử gõ 2 giá trị là 2 và >=2 ra 1 ô Excel bất kỳ và xem kết quả nhé.

Trên đây là hình ảnh mình đã thử.

Với việc bạn gõ số 2 thì giá trị lệch bên phải => đây là số.

Còn nếu bạn gõ >=2 thì kết quả sẽ lệch bên tay trái => đây là chữ.

Và nếu là chữ thì khi viết công thức Excel bạn sẽ cần đặt nó trong ngoặc kép.

Đó là lý do chúng ta có công thức như hình dưới đây.

Vậy nếu bạn không cho >=2 hay <=4 vào trong ngoặc kép thì sao?

Bạn sẽ có biểu thức lỗi như hình trên và nếu kiên trì như vậy thì mãi mãi bạn sẽ không có được kết quả đâu nhé.

3. Tại sao em viết giá trị số trong ngoặc kép với criteria mà em vẫn thu được kết quả?

Hàm COUNTIFS hay COUNTIF thậm chí các hàm có kết thúc IFS đều như vậy.

Nếu bạn viết criteria là 1 số bất kỳ nhưng để trong ngoặc kép thì khi đó COUNTIFS sẽ tự động chuyển giá trị đó thành số. Nghĩa là nó sẽ chuyển chữ “2” thành số 2 tự động khi tiến hành công thức tính toán.

Tính năng này được gọi là Auto Conversion.

Vì thế khi bạn viết điều kiện là 2 hay “2” thì đều thu được kết quả đúng. Tuy nhiên để tránh hiểu nhầm không đáng có vì không phải ai cũng biết điều này nên các bạn chỉ viết số 2 thôi nhé.

Vậy nếu vùng chọn của em là chữ nhưng em vẫn tìm kiếm giá trị số thì sao?

Kết quả vẫn đúng thôi vì khi đó Excel sẽ tự chuyển đổi các giá trị trong vùng đó thành số và đưa vào đếm như bình thường.

Đến đây thì bạn có thể thấy là Excel sẽ áp dụng tính năng tự động chuyển đổi với criteria và cả criteria_range

4. Em có 1 File mà các mã đối soát của em là kiểu dữ liệu chuỗi nhưng có hơn 15 chữ số. Và khi em COUNTIF thì luôn thu được kết quả không đúng!

Các bạn hãy chú ý tới giá trị ở ô A16 A17 và A18.

Ở đây kết quả thu được đều là 3. Tại sao như vậy.

Như mình đã nói ở trên là criteria_range và criteria sẽ tự động chuyển đổi từ chữ về thành số.

Vậy nếu các giá trị quá lớn như trên là chuyển từ chữ thành số thì nó sẽ thành cái gì?

Ta sẽ thử nhé.

Bạn có thể thấy là sau khi chuyển từ chữ thành số thì các con số sau số thứ 15 đều là số 0?

Lý do của việc này vì đây là giới hạn của Excel.

Có Nghĩa là Excel chỉ hiển thị được tối đa 15 chữ số. Còn lại từ số thứ 16 trở đi đều sẽ hiển thị số 0 mà thôi.

Vì thế bạn có thể để ý giá trị dòng 16, 17, 18 sẽ giống hệt nhau. Đó là lý do ta có kết quả là bằng 3.

Vậy nếu muốn ngăn chặn việc này thì làm như thế nào?

a. Phương án 01: Ghép thêm ký tự đại diện “*” vào criteria.

Bạn sẽ thu được kết quả hoàn toàn chính xác.

Tuy nhiên phương án trên nếu các bạn có USERID mà nó từa tựa như giá trị sẵn có ví dụ như sẵn có USERID là “6277729711065974686” và 1 USERID khác là “62777297110659746862” thì nó vẫn sẽ tính là 2 nhé.

b. Phương án 02: Sử dụng SUMPRODUCT

Với phương án sử dụng SUMPRODUCT thì hiệu năng so với sử dụng COUNTIF sẽ kém hơn rất nhiều. Đặc biệt là khi nguồn dữ liệu của bạn lớn với hàng tram, hàng nghìn dòng dữ liệu.

Các kết quả bạn thu được sẽ giống như COUNTIFS thôi. Tuy nhiên nó sẽ luôn đúng trong mọi trường hợp. Chỉ là để đúng trong mọi trường hợp thì bạn phải đánh đổi bằng hiệu năng của công thức.

Các bạn có thể tải File đính kèm theo link này nhé:

https://erx.vn/Media/trunghoang/COUNTIF%20ERROR.xlsx