Khi làm việc với dữ liệu lớn, chúng ta thường gặp phải một vấn đề là có những dữ liệu bị trùng, lặp do nhiều nguyên nhân không xác định được.

Yêu cầu của người làm việc với dữ liệu là phải thực hiện việc lọc các giá trị trùng, lặp đó để tránh việc tính toán 2 lần trên 1 nội dung. Tuy nhiên không phải lúc nào việc lọc dữ liệu trùng cũng dễ dàng, bởi căn cứ xác định thế nào là trùng đôi khi lại phức tạp hơn chúng ta vẫn nghĩ.

Ví dụ: Xét bảng dữ liệu sau:

Yêu cầu: Xác định xem trong bảng dữ liệu trên nội dung ở dòng nào đang bị trùng

Hướng dẫn cách làm:

* Phân tích yêu cầu đề bài

Khảo sát bảng dữ liệu trên, chúng ta thấy rằng: Việc xác định thế nào là trùng ở đây phải căn cứ theo nhiều điều kiện.

• Ngày: Nếu cùng 1 ngày mà các mặt hàng khác nhau, hoặc số lượng hàng bán khác nhau thì không bị trùng
• Tên hàng: Nếu cùng 1 tên hàng mà bán ở nhiều ngày khác nhau, hoặc trong cùng 1 ngày nhưng số lượng bán khác nhau sẽ không phải là trùng
• Số lượng: Nếu số lượng bằng nhau nhưng khác ngày bán, hoặc khác mặt hàng thì không phải trùng.

Để có thể xác định trùng, chúng ta buộc phải xét cùng 1 lúc trên 3 điều kiện trên: Cùng ngày, cùng mặt hàng, cùng số lượng bán thì là trùng

(Ở đây bài toán chỉ có những dữ liệu cho sẵn nên chúng ta chỉ xét trên những nội dung cho sẵn, còn không giả định phức tạp hơn)

* Hàm sử dụng

Yêu cầu xác định dữ liệu trùng -> Chúng ta nghĩ tới hàm Đếm

Yêu cầu bao gồm nhiều điều kiện cùng lúc -> Đếm theo nhiều điều kiện

=> Trong trường hợp này sử dụng hàm COUNTIFS là phù hợp.

* Cách viết hàm

Sau khi đã xác định được hàm phù hợp, chúng ta phải tìm hiểu tiếp 1 bước nữa, đó là xem dùng hàm như thế nào.

Dựa trên việc khảo sát yêu cầu, chúng ta gắn với cấu trúc hàm để biện luận từng nội dung như sau:

Hàm COUNTIFS sẽ viết theo 3 điều kiện:

• Ngày: Criteria_Range1 là cột Ngày, Criteria1 là ngày tại dòng kiểm tra
• Tên hàng: Criteria_Range2 là cột Tên hàng, Criteria2 là tên mặt hàng tại dòng kiểm tra
• Số lượng: Criteria_Range3 là cột Số lượng, Criteria3 là số lượng tại dòng kiểm tra

Chúng ta viết hàm như sau:

G2 = COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$D$2:D2,D2)

Từ ô G2 chúng ta sẽ filldown công thức tới ô G25

* Giải thích: tại sao lại dùng $A$2:A2 chứ không phải $A$2:$A$25

Khi dùng  $A$2:$A$25 thì khi filldown công thức ở cột G, vùng tham chiếu luôn cố định. Vì vậy các giá trị Trùng / lặp sẽ tính = tổng số các giá trị trùng

Khi dùng $A$2:A2  thì khi filldown công thức ở cột G, vùng tham chiếu tăng dần từ A2 tới A25. Vì vậy giá trị Trùng / lặp nếu có xuất hiện sẽ tăng dần theo thứ tự xuất hiện so với vùng dữ liệu trước đó => Chúng ta có thể nhận biết được khi nào giá trị đó chưa bị trùng, khi nào nó bắt đầu bị trùng (Nếu kết quả hàm >1 thì tức là bắt đầu bị trùng)

Tìm hiểu thêm: Tài liệu Excel cơ bản

Chúng ta cùng xem bảng kết quả:

Trong bảng kết quả trên, mình đã tô màu để chúng ta có thể nhận ra được giá trị trùng.

Kết hợp với phương pháp lọc (Filter) chúng ta hoàn toàn có thể lọc tách các kết quả có giá trị không trùng (giá trị bằng 1 ở cột G) sang một bảng tính khác để lấy riêng ra những giá trị không bị trùng.

Kết luận

Hàm COUNTIFS là một hàm rất mạnh trong excel, ứng dụng trong nhiều trường hợp.

Trong thực tế, chúng ta hầu hết luôn cần kiểm tra dữ liệu trước khi bắt đầu làm việc với dữ liệu nhận được. Do đó thao tác kiểm tra dữ liệu trùng thường là thao tác đầu tiên, giúp chúng ta kiểm tra dữ liệu để đảm bảo tính hiệu quả khi làm việc ở các bước tiếp theo. Các bạn hãy tập luyện thao tác này nhiều để rèn luyện tư duy xử lý dữ liệu trước khi dùng nhé (Giống việc chúng ta phải rửa sạch hoa quả trước khi ăn vậy, nếu không rất dễ bị đau bụng đấy).

Chúc các bạn học tốt cùng Học Excel Online.