Kết quả. Excel xóa tất cả các hàng có cùng Họ và Quốc gia [màu xanh] ngoại trừ các trường hợp đầu tiên được tìm thấy [màu vàng]
Chúng ta hãy xem thêm một tính năng Excel thú vị giúp loại bỏ các bản sao. Bạn có thể sử dụng Bộ lọc nâng cao để trích xuất các hàng duy nhất [hoặc các giá trị duy nhất trong một cột]
6. Trên tab Dữ liệu, trong nhóm Sắp xếp & Lọc, bấm vào Nâng cao
Hộp thoại Bộ lọc nâng cao xuất hiện
7. Nhấp vào Sao chép sang vị trí khác
8. Nhấp vào hộp Danh sách phạm vi và chọn phạm vi A1. A17 [xem hình bên dưới]
9. Nhấp vào hộp Sao chép vào và chọn ô F1 [xem hình ảnh bên dưới]
10. Chỉ kiểm tra các bản ghi duy nhất
11. Nhấp vào OK
Kết quả. Excel loại bỏ tất cả các họ trùng lặp và gửi kết quả đến cột F
Ghi chú. ở bước 8, thay vì chọn phạm vi A1. A17, chọn dãy A1. D17 để trích xuất các hàng duy nhất
Bảng tính Excel tiếp tục đại diện cho một công cụ chính để lưu trữ và trực quan hóa dữ liệu. Các chức năng như Tìm & Thay thế hoặc Sắp xếp giúp người dùng tăng tốc các tác vụ lặp đi lặp lại mà nếu không sẽ tốn thời gian và không hiệu quả. Cũng giống như làm việc trên bảng tính với các hàng hoặc ô trống cản trở việc áp dụng đúng các quy tắc và công thức, dữ liệu trùng lặp có thể gây ra các sự cố tương tự
Trong bài viết này, bạn sẽ tìm hiểu các cách khác nhau để tìm các giá trị trùng lặp nhằm làm nổi bật thông tin này hoặc xóa bao nhiêu giá trị trùng lặp nếu cần. Từ các tính năng tô sáng cơ bản hơn đến các tùy chọn lọc nâng cao hơn, bạn sẽ học cách làm việc với toàn bộ tiềm năng của phiên bản Excel trên máy tính
Bắt đầu với lớp ngay hôm nay
Chia sẻ các phần của Google Trang tính của bạn, theo dõi, xem xét và phê duyệt các thay đổi cũng như đồng bộ hóa dữ liệu từ các nguồn khác nhau – tất cả chỉ trong vài giây
Giá trị trùng lặp trong dữ liệu của bạn có thể là một vấn đề lớn. Nó có thể dẫn đến sai sót đáng kể và đánh giá quá cao kết quả của bạn
Nhưng việc tìm và xóa chúng khỏi dữ liệu của bạn thực sự khá dễ dàng trong Excel
Trong hướng dẫn này, chúng ta sẽ xem xét 7 phương pháp khác nhau để định vị và xóa các giá trị trùng lặp khỏi dữ liệu của bạn
Video hướng dẫn
Giá trị trùng lặp là gì?
Giá trị trùng lặp xảy ra khi cùng một giá trị hoặc tập hợp giá trị xuất hiện trong dữ liệu của bạn
Đối với một tập hợp dữ liệu nhất định, bạn có thể xác định các bản sao theo nhiều cách khác nhau
Trong ví dụ trên, có một bộ dữ liệu đơn giản với 3 cột là Hãng, Kiểu và Năm cho danh sách ô tô
- Hình ảnh đầu tiên làm nổi bật tất cả các bản sao chỉ dựa trên Kiểu dáng của chiếc xe
- Hình ảnh thứ hai làm nổi bật tất cả các bản sao dựa trên Kiểu dáng và Kiểu dáng của ô tô. Điều này dẫn đến một bản sao ít hơn
- Hình ảnh thứ hai làm nổi bật tất cả các bản sao dựa trên tất cả các cột trong bảng. Điều này dẫn đến thậm chí ít giá trị hơn được coi là trùng lặp
Kết quả từ các bản sao dựa trên một cột duy nhất so với toàn bộ bảng có thể rất khác nhau. Bạn phải luôn biết mình muốn phiên bản nào và Excel đang làm gì
Tìm và loại bỏ các giá trị trùng lặp bằng lệnh Remove Duplicates
Loại bỏ các giá trị trùng lặp trong dữ liệu là một nhiệm vụ rất phổ biến. Điều này rất phổ biến, có một lệnh chuyên dụng để thực hiện điều đó trong dải băng
Chọn một ô bên trong dữ liệu mà bạn muốn loại bỏ các bản sao và chuyển đến tab Dữ liệu và nhấp vào lệnh Xóa bản sao
Khi đó Excel sẽ chọn toàn bộ tập hợp dữ liệu và mở cửa sổ Remove Duplicates
- Sau đó, bạn cần cho Excel biết nếu dữ liệu chứa tiêu đề cột ở hàng đầu tiên. Nếu điều này được chọn, thì hàng dữ liệu đầu tiên sẽ bị loại trừ khi tìm và xóa các giá trị trùng lặp
- Sau đó, bạn có thể chọn các cột sẽ sử dụng để xác định các cột trùng lặp. Ngoài ra còn có các nút Chọn tất cả và Bỏ chọn tất cả tiện dụng ở trên mà bạn có thể sử dụng nếu bạn có một danh sách dài các cột trong dữ liệu của mình
Khi bạn nhấn OK, Excel sau đó sẽ xóa tất cả các giá trị trùng lặp mà nó tìm thấy và cung cấp cho bạn số liệu tóm tắt về số lượng giá trị đã bị xóa và số lượng giá trị còn lại
Lệnh này sẽ thay đổi dữ liệu của bạn, vì vậy tốt nhất bạn nên thực hiện lệnh trên bản sao dữ liệu của mình để giữ nguyên dữ liệu gốc
Tìm và loại bỏ các giá trị trùng lặp với bộ lọc nâng cao
Ngoài ra còn có một cách khác để loại bỏ bất kỳ giá trị trùng lặp nào trong dữ liệu của bạn khỏi dải băng. Điều này có thể từ các bộ lọc nâng cao
Chọn một ô bên trong dữ liệu và chuyển đến tab Dữ liệu và nhấp vào lệnh Bộ lọc nâng cao
Điều này sẽ mở ra cửa sổ Bộ lọc nâng cao
- Bạn có thể chọn Lọc danh sách tại chỗ hoặc Sao chép sang vị trí khác. Lọc danh sách tại chỗ sẽ ẩn các hàng chứa bất kỳ bản sao nào trong khi sao chép sang vị trí khác sẽ tạo bản sao của dữ liệu
- Excel sẽ đoán phạm vi dữ liệu, nhưng bạn có thể điều chỉnh nó trong phạm vi Danh sách. Có thể để trống phạm vi Tiêu chí và trường Sao chép vào sẽ cần được điền nếu tùy chọn Sao chép vào vị trí khác được chọn
- Chọn hộp cho Chỉ bản ghi duy nhất
Nhấn OK và bạn sẽ loại bỏ các giá trị trùng lặp
Bộ lọc nâng cao có thể là một tùy chọn hữu ích để loại bỏ các giá trị trùng lặp của bạn và đồng thời tạo một bản sao dữ liệu của bạn. Nhưng các bộ lọc nâng cao sẽ chỉ có thể thực hiện việc này trên toàn bộ bảng
Tìm và loại bỏ các giá trị trùng lặp bằng bảng tổng hợp
Bảng tổng hợp chỉ để phân tích dữ liệu của bạn, phải không?
Bạn thực sự có thể sử dụng chúng để loại bỏ dữ liệu trùng lặp
Bạn sẽ không thực sự xóa các giá trị trùng lặp khỏi dữ liệu của mình bằng phương pháp này, bạn sẽ sử dụng bảng tổng hợp để chỉ hiển thị các giá trị duy nhất từ tập dữ liệu
Đầu tiên, tạo một bảng tổng hợp dựa trên dữ liệu của bạn. Chọn một ô bên trong dữ liệu của bạn hoặc toàn bộ phạm vi dữ liệu ➜ chuyển đến tab Chèn ➜ chọn PivotTable ➜ nhấn OK trong hộp thoại Tạo PivotTable
Với bảng tổng hợp trống mới, hãy thêm tất cả các trường vào khu vực Hàng của bảng tổng hợp
Sau đó, bạn sẽ cần thay đổi bố cục của bảng tổng hợp kết quả để nó ở định dạng bảng. Với bảng tổng hợp được chọn, chuyển đến tab Thiết kế và chọn Bố cục Báo cáo. Có hai tùy chọn bạn sẽ cần phải thay đổi ở đây
- Chọn tùy chọn Hiển thị ở dạng bảng
- Chọn tùy chọn Lặp lại Tất cả Nhãn Mục
Bạn cũng sẽ cần xóa mọi tổng phụ khỏi bảng tổng hợp. Vào tab Design ➜ chọn Subtotals ➜ chọn Do Not Show Subtotals
Bây giờ bạn có một bảng tổng hợp bắt chước một bộ dữ liệu dạng bảng
Bảng tổng hợp chỉ liệt kê các giá trị duy nhất cho các mục trong khu vực Hàng, vì vậy bảng tổng hợp này sẽ tự động xóa mọi giá trị trùng lặp trong dữ liệu của bạn
Tìm và loại bỏ các giá trị trùng lặp với Power Query
Power Query hoàn toàn là về chuyển đổi dữ liệu, vì vậy bạn có thể chắc chắn rằng nó có khả năng tìm và loại bỏ các giá trị trùng lặp
Chọn bảng giá trị mà bạn muốn loại bỏ các giá trị trùng lặp khỏi ➜ chuyển đến tab Dữ liệu ➜ chọn truy vấn Từ bảng/Phạm vi
Loại bỏ các bản sao dựa trên một hoặc nhiều cột
Với Power Query, bạn có thể loại bỏ các mục trùng lặp dựa trên một hoặc nhiều cột trong bảng
Bạn cần chọn những cột để loại bỏ trùng lặp dựa trên. Bạn có thể giữ Ctrl để chọn nhiều cột
Nhấp chuột phải vào tiêu đề cột đã chọn và chọn Remove Duplicates từ menu
Bạn cũng có thể truy cập lệnh này từ tab Trang chủ ➜ Xóa hàng ➜ Xóa trùng lặp
= Table.Distinct[#"Previous Step", {"Make", "Model"}]
Nếu bạn nhìn vào công thức đã được tạo, thì nó đang sử dụng Bảng. Hàm khác biệt với tham số thứ hai tham chiếu cột nào sẽ sử dụng
Loại bỏ các bản sao dựa trên toàn bộ bảng
Để loại bỏ các cột trùng lặp dựa trên toàn bộ bảng, bạn có thể chọn tất cả các cột trong bảng sau đó loại bỏ các cột trùng lặp. Nhưng có một phương pháp nhanh hơn mà không yêu cầu chọn tất cả các cột
Có một nút ở góc trên cùng bên trái của phần xem trước dữ liệu với một loạt các lệnh có thể được áp dụng cho toàn bộ bảng
Bấm vào nút bảng ở góc trên cùng bên trái ➜ rồi chọn Remove Duplicates
= Table.Distinct[#"Previous Step"]
Nếu bạn nhìn vào công thức được tạo, nó sẽ sử dụng cùng một Bảng. Hàm riêng biệt không có tham số thứ hai. Không có tham số thứ hai, hàm sẽ hoạt động trên toàn bộ bảng
Giữ các bản sao dựa trên một cột hoặc trên toàn bộ bảng
Trong Power Query còn có các lệnh giữ trùng lặp cho cột đã chọn hoặc cho toàn bộ bảng
Thực hiện theo các bước tương tự như loại bỏ các bản sao, nhưng thay vào đó hãy sử dụng lệnh Keep Rows ➜ Keep Duplicates. Điều này sẽ hiển thị cho bạn tất cả dữ liệu có giá trị trùng lặp
Tìm và loại bỏ các giá trị trùng lặp bằng công thức
Bạn có thể sử dụng một công thức để giúp bạn tìm các giá trị trùng lặp trong dữ liệu của mình
Trước tiên, bạn sẽ cần thêm một cột trợ giúp kết hợp dữ liệu từ bất kỳ cột nào mà bạn muốn dựa trên định nghĩa trùng lặp của mình
= [@Make] & [@Model] & [@Year]
Công thức trên sẽ nối cả ba cột thành một cột duy nhất. Nó sử dụng toán tử dấu và để nối từng cột
= TEXTJOIN["", FALSE , CarList[@[Make]:[Year]]]
Nếu bạn có một danh sách dài các cột cần kết hợp, bạn có thể sử dụng công thức trên để thay thế. Bằng cách này, bạn có thể chỉ cần tham chiếu tất cả các cột dưới dạng một phạm vi
Sau đó, bạn sẽ cần thêm một cột khác để đếm các giá trị trùng lặp. Điều này sẽ được sử dụng sau này để lọc ra các hàng dữ liệu xuất hiện nhiều lần
= COUNTIFS[$E$3:E3, E3]
Sao chép công thức trên xuống cột và nó sẽ đếm số lần giá trị hiện tại xuất hiện trong danh sách các giá trị trên
Nếu số đếm là 1 thì đây là lần đầu tiên giá trị xuất hiện trong dữ liệu và bạn sẽ giữ giá trị này trong tập hợp các giá trị duy nhất của mình. Nếu số lượng là 2 hoặc nhiều hơn thì giá trị đã xuất hiện trong 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ủa bạn
- Chuyển đến tab Dữ liệu và chọn lệnh Bộ lọc
- Sử dụng phím tắt Ctrl + Shift + L
Bây giờ bạn có thể lọc trên cột Count. Lọc trên 1 sẽ tạo ra tất cả các giá trị duy nhất và loại bỏ mọi giá trị trùng lặp
Sau đó, bạn có thể chọn các ô hiển thị từ bộ lọc kết quả để sao chép và dán vào nơi khác. Sử dụng phím tắt Alt + ;
Tìm và loại bỏ các giá trị trùng lặp với định dạng có điều kiện
Với định dạng có điều kiện, có một cách để làm nổi bật các giá trị trùng lặp trong dữ liệu của bạn
Cũng giống như phương pháp công thức, bạn cần thêm một cột trợ giúp kết hợp dữ liệu từ các cột. Định dạng có điều kiện không hoạt động với dữ liệu trên các hàng, vì vậy, bạn sẽ cần cột kết hợp này nếu muốn phát hiện các mục trùng lặp dựa trên nhiều cột
Sau đó, bạn cần chọn cột dữ liệu kết hợp
Để tạo định dạng có điều kiện, hãy chuyển đến tab Trang đầu ➜ chọn Định dạng có điều kiện ➜ Đánh dấu các quy tắc ô ➜ Nhân đôi giá trị
Điều này sẽ mở ra cửa sổ Giá trị trùng lặp định dạng có điều kiện
- Bạn có thể chọn đánh dấu các giá trị Trùng lặp hoặc Duy nhất
- Bạn cũng có thể chọn trong số các định dạng ô được xác định trước để đánh dấu các giá trị hoặc tạo định dạng tùy chỉnh của riêng bạn
Cảnh báo. Các phương pháp trước đây để tìm và loại bỏ các giá trị trùng lặp coi lần xuất hiện đầu tiên của một giá trị là trùng lặp và sẽ giữ nguyên giá trị đó. Tuy nhiên, phương pháp này sẽ làm nổi bật lần xuất hiện đầu tiên và sẽ không tạo ra bất kỳ sự khác biệt nào
Với các giá trị được đánh dấu, giờ đây bạn có thể lọc các giá trị trùng lặp hoặc duy nhất bằng tùy chọn lọc theo màu. Đảm bảo thêm bộ lọc vào dữ liệu của bạn. Chuyển đến tab Dữ liệu và chọn lệnh Bộ lọc hoặc sử dụng phím tắt Ctrl + Shift + L
- Nhấp vào chuyển đổi bộ lọc
- Chọn Lọc theo màu trong menu
- Lọc màu được sử dụng trong định dạng có điều kiện để chọn các giá trị trùng lặp hoặc lọc trên Không nền để chọn các giá trị duy nhất
Sau đó, bạn chỉ có thể chọn các ô hiển thị bằng phím tắt Alt + ;
Tìm và loại bỏ các giá trị trùng lặp bằng VBA
Có một lệnh tích hợp trong VBA để loại bỏ các bản sao trong các đối tượng danh sách
Sub RemoveDuplicates[]
Dim DuplicateValues As Range
Set DuplicateValues = ActiveSheet.ListObjects["CarList"].Range
DuplicateValues.RemoveDuplicates Columns:=Array[1, 2, 3], Header:=xlYes
End Sub
Quy trình trên sẽ xóa các bản sao khỏi bảng Excel có tên CarList
Columns:=Array[1, 2, 3]
Phần trên của quy trình sẽ đặt cột nào để phát hiện trùng lặp dựa trên. Trong trường hợp này, nó sẽ nằm trên toàn bộ bảng vì cả ba cột đều được liệt kê
Header:=xlYes
Phần trên của quy trình cho Excel biết hàng đầu tiên trong danh sách của chúng ta chứa tiêu đề cột
Bạn sẽ muốn tạo một bản sao dữ liệu của mình trước khi chạy mã VBA này, vì không thể hoàn tác sau khi mã chạy