Lọc và đánh dấu dữ liệu trùng lặp

Vấn đề bạn đang gặp

Bảng kê hóa đơn của bạn có vài dòng bị nhập hai lần: dán hai lần, nhập lại, hoặc gộp hai tệp có phần trùng nhau. Tổng số lượng vì thế bị tính trùng. Bạn cần thấy dòng nào trùng trước khi xóa, để không xóa nhầm.

Microsoft khuyên xem các dòng trùng trước rồi mới quyết định có xóa không. Việc đầu tiên là chọn cột làm khóa: một dòng trùng khi giá trị ở các cột khóa giống một dòng khác. Với bảng kê này, khóa là Mã HĐ.

Cách sửa từng bước

Đánh dấu hóa đơn nhập trùng và cộng bỏ các lần lặp

1. 1

   Làm gì: Chọn cột khóa: ở bảng này là Mã HĐ (cột A). Bạn sẽ đánh dấu các dòng có cùng Mã HĐ.

   Vì sao: Excel không tự biết hai dòng là một hóa đơn. Bạn nói cho nó biết cột nào quyết định, ví dụ chỉ Mã HĐ, hoặc cả Mã HĐ lẫn Mã hàng.

2. 2

   Làm gì: Ở cột phụ (ví dụ H2) gõ =IF(COUNTIF($A$2:$A$11,A2)>1,"Trùng","Duy nhất") rồi sao chép xuống.

   Vì sao: COUNTIF đếm Mã HĐ của dòng này xuất hiện bao nhiêu lần trong cả cột A. Lớn hơn 1 nghĩa là trùng. Dấu $ giữ vùng đếm là A2:A11 khi sao chép. Cách này đánh dấu cả dòng đầu lẫn các dòng lặp.

3. 3Chỗ hay sai

   Làm gì: Muốn biết dòng nào là lần lặp để giữ dòng đầu, gõ =COUNTIF($A$2:A2,A2) rồi sao chép xuống.

   Vì sao: Ô đầu của vùng có $ nên đứng yên, ô cuối trượt theo từng dòng. Vùng đếm chỉ gồm các dòng từ hàng 2 đến dòng hiện tại, nên lần đầu ra 1, lần lặp ra 2, 3.

4. 4

   Làm gì: Lọc cột vừa tạo để chỉ xem các dòng lớn hơn 1, đối chiếu với nguồn, rồi mới xóa các dòng đó.

   Vì sao: Microsoft khuyên lọc hoặc tô màu các giá trị trùng trước để chắc kết quả đúng như mong đợi, rồi mới xóa. Xem trước các dòng bị đánh dấu giúp bạn tránh xóa nhầm.

5. 5

   Làm gì: Để cộng số lượng mà không tính trùng, gõ =SUMIFS(F2:F11,H2:H11,1) (cột F là Số lượng, cột H là Lần xuất hiện).

   Vì sao: SUMIFS chỉ cộng các dòng có Lần xuất hiện bằng 1, tức lần đầu của mỗi hóa đơn.

Tự hỏi mình

• Vì sao cách 2 đánh dấu cả dòng đầu còn cách 3 thì không?
• Điều gì xảy ra với cột Lần xuất hiện nếu bạn quên dấu $ ở ô đầu của vùng đếm?

Danh sách không trùng với UNIQUE (Excel 2021/365)

=UNIQUE(A2:A11) trả về mỗi Mã HĐ một lần, kết quả tràn ra các ô bên dưới. Hàm này có từ Excel 2021 và Microsoft 365; trên Excel cũ hơn dùng cách đánh dấu bằng COUNTIF ở trên. Đối số thứ ba của UNIQUE đổi nghĩa kết quả: =UNIQUE(A2:A11,,TRUE) chỉ trả về các Mã HĐ xuất hiện đúng một lần, nên loại cả các hóa đơn bị nhập trùng chứ không giữ lại một bản.

Tô màu ô trùng (Excel thật)

Conditional Formatting (định dạng có điều kiện) tô màu các ô trùng để bạn nhìn thấy ngay. Lưới DeskLab chưa chấm định dạng có điều kiện, nên phần này không có bài tập: hãy làm trên Excel thật.

Microsoft cũng cho ví dụ dùng công thức COUNTIF làm quy tắc tô màu, cùng ý với cột đánh dấu ở trên: =COUNTIF($A$2:$A$11,A2)>1.

Xóa dòng trùng với Remove Duplicates (Excel thật)

Remove Duplicates xóa cả dòng khi các cột khóa bạn chọn giống một dòng phía trên, và giữ lại lần xuất hiện đầu tiên. Dữ liệu bị xóa hẳn, nên hãy sao lưu trước.

Vì sao lỗi này xảy ra

Excel không biết hai dòng là một hóa đơn nhập hai lần. Nó chỉ so sánh giá trị. Với Remove Duplicates và bộ lọc, Microsoft nói rằng việc so sánh dựa trên cái hiện ra trong ô, không phải giá trị ẩn bên dưới: hai ô cùng một ngày nhưng một ô định dạng 3/8/2006 và ô kia Mar 8, 2006 được coi là khác nhau. Với COUNTIF, Microsoft cảnh báo dấu cách thừa hoặc ký tự ẩn có thể làm kết quả khác mong đợi.

Dữ liệu bị nhập hai lần thường đến từ thao tác lặp lại: dán hai lần, nhập lại một lô, gộp hai tệp có phần chung. Bảng kê càng dài, mắt càng khó phát hiện, nên cần một cột đánh dấu bằng công thức để máy tìm giúp bạn.

• Mọi dòng đều ra Duy nhất dù bạn thấy rõ có dòng trùng.

  Nguyên nhân

  Hai Mã HĐ khác nhau một dấu cách thừa ở cuối hoặc một ký tự ẩn. Theo Microsoft, COUNTIF có thể cho kết quả không mong đợi khi dữ liệu có dấu cách đầu, dấu cách cuối hoặc ký tự không in được.

  Cách sửa

  Làm sạch cột khóa bằng TRIM ở một cột phụ, rồi đếm trùng trên cột đã làm sạch.

• Dòng đầu của cặp trùng cũng bị ghi Trùng, và bạn không biết giữ dòng nào.

  Nguyên nhân

  Vùng đếm cố định cả hai đầu ($A$2:$A$11) nên đếm cả dòng đầu lẫn dòng lặp.

  Cách sửa

  Muốn giữ dòng đầu, dùng vùng đếm mở rộng dần =COUNTIF($A$2:A2,A2): lần đầu ra 1, lần lặp ra 2 trở lên.

• Cột Lần xuất hiện toàn số 1.

  Nguyên nhân

  Ô đầu của vùng đếm không có $, nên khi sao chép xuống nó trượt theo và vùng chỉ còn một ô.

  Cách sửa

  Cố định ô đầu bằng $: =COUNTIF($A$2:A2,A2).

• Dùng Remove Duplicates xong thì mất dữ liệu cần giữ.

  Nguyên nhân

  Remove Duplicates xóa hẳn dữ liệu, và xóa cả dòng khi các cột bạn chọn làm khóa giống nhau, kể cả các cột khác.

  Cách sửa

  Sao lưu dữ liệu gốc sang trang tính khác trước, và chỉ chọn đúng các cột làm khóa.

Đọc xong, giờ thử

Luyện tập ngay (2 bài tập)

Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.

Bắt đầu luyện tập

Chấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.

Trên Excel thật

• Lưới DeskLab: COUNTIF và SUMIFS dùng được như trên Excel. Bài tập dùng vùng đếm cố định và vùng đếm mở rộng dần. Bài tập không dùng UNIQUE, vì kết quả tràn ra nhiều ô không phù hợp với cách chấm từng ô.
• Remove Duplicates và Conditional Formatting: lưới DeskLab chưa có nút xóa trùng và chưa chấm định dạng có điều kiện, nên hai phần này chỉ để đọc. Trên Excel thật, hãy dùng cột đánh dấu để rà soát trước rồi mới xóa.
• UNIQUE: cần Excel 2021 hoặc Microsoft 365. Trên Excel cũ hơn, dùng cột đánh dấu bằng COUNTIF hoặc Advanced Filter với Unique records only.

Câu hỏi thường gặp

Excel có nút xóa dòng trùng không, và vì sao DeskLab không có?

Trên Excel thật có: Data, Data Tools, Remove Duplicates (Xóa dữ liệu trùng). Nút này xóa hẳn dữ liệu và giữ lại lần xuất hiện đầu tiên, nên hãy sao chép dữ liệu gốc sang trang tính khác trước. Lưới DeskLab chưa có nút này, vì vậy bài tập luyện cách đánh dấu bằng công thức, là bước rà soát nên làm trước khi xóa.

Hai ô nhìn giống hệt mà COUNTIF không coi là trùng?

Thường do dấu cách thừa ở đầu hoặc cuối một trong hai ô, hoặc ký tự không in được. Microsoft cảnh báo COUNTIF có thể cho kết quả không mong đợi khi dữ liệu có dấu cách đầu, dấu cách cuối hoặc ký tự không in được, và gợi ý dùng TRIM hoặc CLEAN. COUNTIF không phân biệt chữ hoa và chữ thường.

Muốn có danh sách các mã không trùng thì làm sao?

Từ Excel 2021 hoặc Microsoft 365 dùng UNIQUE, ví dụ =UNIQUE(A2:A11), kết quả tràn ra các ô bên dưới. Với Excel cũ hơn, đánh dấu bằng COUNTIF như trên rồi lọc các dòng có Lần xuất hiện bằng 1, hoặc dùng Advanced Filter với Unique records only trên Excel thật.