Thủ thuật

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất, nếu bạn có một bảng dữ liệu Excel với rất nhiều thông tin mang tính chất “giống nhau” thì việc trùng dữ liệu là điều không thể tránh được, nếu dữ liệu bị trùng điều này sẽ dẫn dẫn đến rất nhiều hệ lụy cho công việc của bạn?

Bạn có biết rằng những danh sách trong Excel sau khi được copy nhiều lần sẽ rất dễ xảy ra hiện tượng trùng lặp dữ liệu? vậy tìm dữ liệu trùng lặp trong excel bằng cách nào? Làm sao để kiểm tra trùng lặp trong excel một cách chính xác nhất?

Chọn để xem

Lọc dữ liệu trùng Excel chuẩn xác nhất

Việc tìm dữ liệu trùng trong Excel rất quan trọng, nó giúp dữ liệu của bạn trở nên “Sạch” hơn, chỉ cần một vài số liệu dư thừa sẽ ảnh hưởng đến toàn bộ kết quả tính toán. Hiểu được tầm quan trọng của vấn đề này, mình sẽ hướng dẫn các bạn một số mẹo lọc dữ liệu trùng Excel chuẩn xác nhất.

Để tránh mắc lỗi trùng lặp trong việc nhập dữ liệu lớn công cụ lọc dữ liệu trùng excel đã xuất hiện rất sớm trên các phiên bản Excel.Với các phương pháp dưới đây các bạn có thể áp dụng cho Microsoft Excel phiên bản 2007, 2010, 2013, 2016, 2019 và Excel cho Office 365.

Sử dụng Conditional Formatting để lọc dữ liệu trùng Excel

Conditional Formatting (định dạng có điều kiện) là một công cụ mạnh mẽ và có thể thay đổi “diện mạo” của một ô dựa trên giá trị của nó, giúp người xem xác định dữ liệu quan trọng nhanh chóng. Bạn có thể thêm màu sắc, icon, thanh dữ liệu và thang màu vào ô bằng cách tạo quy tắc định dạng có điều kiện.

Với phương pháp này chỉ giúp bạn tìm kiếm những dữ liệu bị trùng bằng cách làm cho dữ liệu bị trùng được làm nổi bật hơn, từ đó người dùng có thể kiểm tra dữ liệu bị trùng.

Đầu tiên bạn hãy tô đen những dữ liệu cần kiểm tra dữ liệu trùng lặp trong Excel. Trên thanh công cụ của Excel, bạn điều hướng theo thứ tự Home > Conditional Formatting > Highlight Cells Rules > Duplicate Value.

6 mẹo lọc dữ liệu trùng Excel

Lúc này hộp thoại Duplicate Value của Excel hiện ra. Có những lựa chọn cơ bản cho phép bạn đánh dấu dữ liệu trùng lặp theo màu sắc như bên dưới.

Bạn hãy lựa chọn kiểu đánh dấu, tô màu dữ liệu trùng trong Excel mà bạn thích tương ứng như sau:

  • Light red fill with dark red text: sử dụng nền đỏ, chữ màu đỏ đậm.
  • Yellow fill with dark yellow text: nền màu vàng, chữ màu vàng đậm.
  • Green fill with dark green text: nền màu xanh, chữ hiển thị màu xanh đen.
  • Light red fill: tô nền màu đỏ, màu chữ không đổi.
  • Red text: chỉ tô màu đỏ cho chữ, màu nền không ảnh hưởng
  • Red border: tự động tô khung viền màu đỏ cho các giá trị bị trùng nhau.
  • Custom format: bạn có thể tùy chỉnh màu sắc chữ, màu nền theo sở thích của cá nhân mình.

Ví dụ, ở đây tôi chọn Light red fill: tô nền màu đỏ, màu chữ không đổi.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 1

key windows giá rẻ

Lúc này bạn sẽ thấy những dữ liệu bị trùng sẽ được tô nền màu đỏ, màu chữ không đổi.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 2

Ngoài ra, bạn có thể lọc dữ liệu bằng cách lọc ra những giá trị duy nhất trong bảng tính bằng cách trên hộp thoại Duplicate Value của Excel hiện ra > chọn Unique để lọc ra những giá trị dữ liệu duy nhất.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 3

Sử dụng Remove Duplicate để lọc dữ liệu trùng Excel

Tính năng Remove Duplicates sẽ giúp bạn nhanh chóng tìm và loại bỏ những bản ghi trùng lặp trong bảng tính và chỉ giữ lại 01 bản ghi duy nhất.

Trong bảng tính dưới đây, bạn có thể thấy có một vài dòng dữ liệu bị trùng. Chúng ta cần phải loại bỏ các dữ liệu trùng lặp để cho danh sách “sạch” hơn.

Đầu tiên, bạn hãy tô đen toàn bộ khu vực dữ liệu mà bạn muốn tìm dữ liệu trùng nhau trong Excel. Tìm đến thẻ DATA trên thanh công cụ, lựa chọn tính năng Remove Duplicates.

Bảng lọc dữ liệu Remove Duplicates xuất hiện, bạn lựa chọn cột muốn lọc dữ liệu bằng cách tích vào trường dữ liệu cần lọc.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 4

Trường hợp bạn muốn lọc dữ liệu nhiều hay tất cả cột thì hãy chọn Select All cho nhanh nhé. Sau khi lựa chọn các cột dữ liệu cần lọc xong thì hãy nhấn OK. Hệ thống sẽ tự động xóa các dữ liệu trùng và để lại bảng dữ liệu đã được chuẩn hóa.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 5

Sử dụng Advanced Filter để lọc dữ liệu trùng Excel

Chức năng Advanced Filter là một trong những chức năng giúp các bạn lọc dữ liệu một cách nhanh chóng và hiệu quả nhất.

Điều kiện để sử dụng chức năng Advanced Filter:

  • Tiêu đề bảng dữ liệu chỉ được sử dụng một dòng duy nhất.
  • Trong bảng, không được merge bất kì một ô nào.

Đầu tiên, chọn tiêu đề cột cần làm điều kiện lọc, sau đó copy, nhấn tổ hợp phím Ctrl + C rồi sau đó dán, Ctrl+V vào ô điều kiện có màu vàng ở trên.

Ví dụ, Ở đây mình muốn lọc dữ liệu trùng ở hai điều kiện là sinh năm 1992giới tính là Nam.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 6

Tiếp tục vào Data > chọn Advanced > Hộp thoại Advanced Filter xuất hiện.

Trong đó cần quan tâm đến:

  • Phần Action (xem tùy chọn bên dưới).
  • List range: Vùng chứa dữ liệu.
  • Criteria range: Vùng chứa điều kiện.

Để lọc dữ liệu bạn có 2 hành động (Action) lọc.

Chọn Filter the list, in place tại mục Action, lọc dữ liệu ngay trên bảng. Nhấp OK bạn sẽ có được bảng dữ liệu được lọc.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 7

Chọn Copy to another location tại mục Action khi muốn lọc dữ liệu sang một vị trí khác ngoài bảng dữ liệu đã cho. Phần Copy to là nơi mà bạn muốn đưa dữ liệu mới đến. Nhấp OK ta được kết quả.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 8

Sử dụng Pivot Table để lọc dữ liệu trùng Excel

Pivot Table là một trong những tính năng hữu dụng và là công cụ thống kê dữ liệu mạnh mẽ nhất trong Excel, dùng để tổng hợp, trích lọc, phân tích dữ liệu dễ dàng và nhanh chóng khi cần.

Với phương pháp này bạn không cần phải xóa các giá trị trùng lặp ra khỏi dữ liệu của mình, phương pháp này sẽ giúp bạn hiển thị những giá trị duy nhất từ bảng dữ liệu.

Tạo Pivot Table từ dữ liệu của bạn bằng cách chọn một ô bất kỳ bên trong dữ liệu của bạn hoặc toàn bộ phạm vi dữ liệu và chuyển đến tab Insert > chọn Pivot Table > nhấn OK trong hộp thoại Create PivotTable. Với bảng Pivot Table mới, bạn hãy đánh dấu vào các dữ liệu để thêm đầy đủ các trường dữ liệu vào khu vực Row của bảng.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 9

Tiếp theo, bạn sẽ cần phải thay đổi cách sắp xếp kết quả để Pivot Table có dạng bảng bằng cách chuyển đến tab Design và lựa chọn Report Layout. Sau đó bạn hãy chọn lần lượt 2 lựa chọn sau.

  • Lựa chọn tùy chọn Show in Tabular Form.
  • Lựa chọn tùy chọn Repeat All Item Labels.
6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 10

Ngoài ra, bạn cần cần phải xóa bỏ các subtotals khỏi Pivot Table bằng cách di chuyển đến tab Design > lựa chọn Subtotals > chọn Do Not Show Subtotals.

Bây giờ bạn đã có một Pivot Table giống với một tập hợp dữ liệu dạng bảng thông thường. Pivot Table chỉ liệt kê các giá trị duy nhất cho các mục trong khu vực Row. Và do đó, nó sẽ tự động xóa mọi giá trị trùng lặp trong dữ liệu của bạn

Sử dụng Power Query để lọc dữ liệu trùng Excel

Power Query là một công nghệ kết nối dữ liệu để cho phép bạn khám phá, kết nối, kết hợp và tinh chỉnh nguồn dữ liệu để đáp ứng nhu cầu phân tích của bạn. Các tính năng trong Power query có sẵn trong Excel.

Với Power Query bạn có thể tìm kiếm nguồn dữ liệu, tạo kết nối, định hình dữ liệu đó (chẳng hạn như loại bỏ cột, thay đổi kiểu dữ liệu hoặc sáp nhập bảng) theo cách thức đáp ứng nhu cầu của bạn. Sau khi bạn đã định hình dữ liệu, bạn có thể chia sẻ kết quả của mình hoặc dùng truy vấn để tạo báo cáo.

Để tiến hành thực hiện các thao tác lọc dữ liệu trong cửa sổ Power Query Editor thì trước hết ta cần đưa dữ liệu từ Excel vào trong Power Query Editor.

Tại Tab Data bạn tìm tới From Table/Range để tiến hành nhập dữ liệu từ 1 Table hoặc 1 vùng (range) vào Power Query Editor để xử lý.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 11

Ví dụ, cho bài viết hướng dẫn loại bỏ giá trị trùng lặp (danh sách hàng hoá với các loại hoa quả), ta sẽ loại bỏ những loại hoa quả trùng lặp này như sau.

Sau khi đã đưa dữ liệu vào Power Query Editor, dữ liệu của bạn sẽ hiển thị trong cửa sổ Power Query Editor. Bạn chọn cột dữ liệu mà bạn muốn loại bỏ giá trị trùng lặp, tại Tab Home bạn tìm tới nhóm Reduce Rows, tại đây, bạn chọn Remove Rows. Bạn chọn Remove Duplicates để thực hiện xoá các giá trị trùng lặp.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 12

Tại Applied Steps bạn sẽ thấy 1 bước được Power Query Editor ghi lại cho thao tác xoá trùng bạn vừa thực hiện trở trên. Bây giờ bạn chỉ cần xuất kết quả đó vào Excel là được.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 13

Chỉ đơn giản như vậy thôi là bạn đã hoàn thành xong việc loại bỏ giá trị trùng lặp với Power Query. Giờ đây, mỗi lần bạn thực hiện thêm dữ liệu vào Data gốc ban đầu, bạn chỉ cần Refresh lại bảng kết quả này thì ngay lập tức Power Query sẽ thực hiện loại bỏ giá trị trùng lặp và trả về kết quả một cách chính xác cho bạn.

Sử dụng công thức để lọc dữ liệu trùng Excel

Bạn có thể sử dụng công thức để tìm các giá trị trùng lặp trong bảng dữ liệu của mình một cách đơn giản.

Giả sử mình có bảng dữ liệu với các trường bên dưới.

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 14

Lúc này bạn hãy thêm một cột dữ liệu để làm cột định nghĩa dữ liệu trùng. Ở cột này bạn hãy dùng công thức nối tất cả các cột dữ liệu thành một cột duy nhất (sử dụng toán tử & để nối dữ liệu).

Ở bảng dữ liệu trên bạn sẽ có 3 cột gồm: Make, Model, Year và cột Combined (cột này được thêm vào). Lúc này công thức nối 3 cột dữ liệu sẽ là.

Tại cột Combined bạn sử dụng công thức: = [@Make] & [@Model] & [@Year]

Ngoài ra, nếu bạn có một danh sách dài các cột để kết hợp, bạn có thể sử dụng công thức này: = TEXTJOIN("", FALSE , CarList[@[Make]:[Year]]) để thay thế. Bằng cách này, bạn có thể tham chiếu tất cả các cột dưới dạng một phạm vi duy nhất.

Sau đó, bạn cần thêm một cột khác để đếm các giá trị trùng lặp. Điều này được sử dụng để lọc ra các hàng dữ liệu xuất hiện nhiều lần.

Tại cột mới (Count) sử dụng công thức: = COUNTIFS($E$3:E3, E3)

6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất 15

Sao chép công thức từ trên xuống các cột và nó sẽ đếm số lần giá trị hiện tại xuất hiện trong danh sách.

Nếu số đếm là 1 thì đây là lần đầu tiên giá trị xuất hiện trong bảng dữ liệu Excel và bạn có thể giữ lại giá trị này. Nếu số lượng là 2 trở lên thì giá trị đã xuất hiện trong bảng dữ liệu và đó là giá trị trùng lặp có thể bị xóa.

Thêm bộ lọc vào danh sách dữ liệu cần xóa bằng cách: Chuyển đến thẻ Data và bấm nút Filter(hoặc cũng có thể sử dụng tổ hợp phím tắt Ctrl + Shift + L). Bây giờ bạn có thể lọc trên cột Count với giá trị được chọn là 1. Kết quả bảng dữ liệu sẽ hiển thị tất cả các giá trị duy nhất và loại bỏ mọi trùng lặp.

//Bạn soạn thảo văn bản mà không kiểm soát được lỗi chính tả? vậy phải làm sao để kiểm soát được lỗi một cách hiệu quả nhất, hãy tham khảo cách kiểm tra lỗi chính tả trong Word chuẩn 100%.

Kết luận

Như vậy mình đã hướng dẫn các bạn 6 mẹo lọc dữ liệu trùng Excel chuẩn xác nhất rồi đấy.

Với các mẹo này sẽ giúp bạn dễ dàng lọc dữ liệu trùng Excel với các phương pháp từ đơn giản đến phức tạp gồm:

  • Sử dụng Conditional Formatting để lọc dữ liệu trùng Excel
  • Sử dụng Remove Duplicate để lọc dữ liệu trùng Excel
  • Sử dụng Advanced Filter để lọc dữ liệu trùng Excel
  • Sử dụng Pivot Table để lọc dữ liệu trùng Excel
  • Sử dụng Power Query để lọc dữ liệu trùng Excel
  • Sử dụng công thức để lọc dữ liệu trùng Excel

Quá đơn giản để bạn có thể lọc dữ liệu trùng Excel chuẩn xác rồi đấy, nếu bạn có phương pháp nào khác hãy chia sẻ bằng cách để lại bình luận bên dưới. Chúc các bạn vui vẻ !

Tỉ lệ / 5. Phiếu:

Chúng tôi rất tiếc vì bài đăng này không hữu ích cho bạn!

Hãy để chúng tôi cải thiện bài đăng này!

Hãy cho chúng tôi biết làm thế nào chúng tôi có thể cải thiện bài đăng này?

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button